Database Test 3
Data Definition Language (DDL)
Commands that define a database, including creating, altering, and dropping tables and establishing constraints
Data Manipulation Language (DML)
Commands that maintain and query a database
Materialized view
Copy or replication of data Data actually stored Must be refreshed periodically to match corresponding base tables
Mapping many-to-many
Create a new relation with the primary keys of the two entities as its primary key
Second normal form
1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key; Every non-key attribute must be defined by the entire key, not by only part of the key; No partial functional dependencies
Third normal form
2NF PLUS no transitive dependencies (functional dependencies on non-primary-key attributes)
Dynamic View
A "virtual table" created dynamically upon request by a user No data actually stored; instead data from base table made available to user Based on SQL SELECT statement on base tables or other views
physical file
A named portion of secondary memory allocated for the purpose of storing physical records
Catalog
A set of schemas that constitute the description of a database
Candidate key
A unique identifier. One of the candidate keys will become the primary key; Each non-key field is functionally dependent on every candidate key.
boolean operators
AND, OR, and NOT Operators With parentheses...these override the normal precedence
Dynamic SQL
Ability for an application program to generate SQL code on the fly, as the application is running
Domain Constraints
Allowable values for an attribute
Data Control Language (DCL)
Commands that control a database, including administering privileges and committing data
SQL commands for transactions
BEGIN TRANSACTION/END TRANSACTION Marks boundaries of a transaction COMMIT Makes all updates permanent ROLLBACK Cancels updates since the last COMMIT
list partitioning
Based on predefined lists of values for the partitioning key
Mapping Weak entities
Becomes a separate relation with a foreign key taken from the superior entity
Multivalued Attribute
Becomes a separate relation with a foreign key taken from the superior entity
Field data Integrity
Default value-assumed value if no explicit value Range control-allowable value limitations (constraints or validation rules) Null value control-allowing or prohibiting empty fields Referential integrity-range control (and null value allowances) for foreign-key to primary-key match-ups
vertical partitioning
Distributing the columns of a logical relation into several separate physical tables
horizontal partitioning
Distributing the rows of a logical relation into several separate tables
nonunique (secondary) index
Done for fields that are often used to group individual entities (e.g. zip code, product category)
Simple Attributes
E-R attributes map directly onto the relation
types of file organizations
Heap - no particular order Sequential Indexed Hashed
clustering files
In some relational DBMSs, related records from different tables can be stored together in the same disk area Useful for improving performance of join operations Primary key records of the main table are stored adjacent to associated foreign key records of the dependent table
Embedded SQL
Including hard-coded SQL statements in a program written in another language such as C or Java
Data integrity
Mechanisms for implementing business rules that maintain integrity of manipulated data
First Normal form
No multivalued attributes Every attribute value is atomic All relations are in this form
Entity Integrity
No primary key attribute may be null. All primary key fields MUST contain data values.
types of subqueries
Noncorrelated-executed once for the entire outer query Correlated-executed once for each row returned by the outer query
Database
Organized collection of logically related data
query optimization
Parallel query processing-possible when working in multiprocessor systems Overriding automatic query optimization-allows for query writers to preempt the automated optimization
range partitioning
Partitions defined by range of field values Could result in unbalanced distribution of rows Like-valued fields share partitions
hash partitioning
Partitions defined via hash functions Will guarantee balanced distribution of rows Partition could contain widely varying valued fields
Data manipulation
Powerful SQL operations for retrieving and modifying data
Data Normalization
Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data; The process of decomposing relations with anomalies to produce smaller, well-structured relations
Mapping one-to-one
Primary key on mandatory side becomes a foreign key on optional side
Mapping one-to-many
Primary key on the one side becomes a foreign key on the many side
routines
Program modules that execute on demand
advantages to database approach
Program-data independence Planned data redundancy Improved data consistency Improved data sharing Increased application development productivity Enforcement of standards Improved data quality Improved data accessibility and responsiveness Reduced program maintenance Improved decision support
Physical database design
Purpose-translate the logical description of data into the technical specifications for storing and retrieving data Goal-create a design for storing data that will provide adequate performance and ensure database integrity, security, and recoverability
Referential Integrity
Rules that maintain consistency between the rows of two related tables. rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null)
clauses of the select statement
SELECT List the columns (and expressions) to be returned from the query FROM Indicate the table(s) or view(s) from which data will be obtained WHERE Indicate the conditions under which a row will be included in the result GROUP BY Indicate categorization of results HAVING Indicate the conditions under which a category (group) will be included ORDER BY Sorts the result according to specified criteria
tablespace components
Segment - a table, index, or partition Extent-contiguous section of disk space Data block - smallest unit of storage
Purpose of SQL Standard
Specify syntax/semantics for data definition and manipulation Define data structures and basic operations Enable portability of database definition and application modules Specify minimal (level 1) and complete (level 2) standards Allow for later growth/enhancement to standard
indexed file organization
Storage of records sequentially or nonsequentially with an index that allows software to locate individual records
SQL
Structured Query Language - often pronounced "Sequel" The standard for relational database management systems (RDBMS)
User-defined data types (UDT)
Subclasses of standard types or an object type
data dictionary facilities
System tables that store metadata Users usually can view some of these tables Users are restricted from updating them
Data structure
Tables (relations), rows, columns
file organization
Technique for physically arranging records of a file on secondary storage
Schema
The structure that contains descriptions of objects created by a user (base tables, views, constraints)
Functional dependency
The value of one attribute (the determinant) determines the value of another attribute
Denormalization
Transforming normalized relations into non-normalized physical record specifications
unique (primary) index
Typically done for primary keys, but could also apply to other unique fields
Composite Attributes
Use only their simple, component attributes
outer join
a join in which rows that do not have matching values in common columns are nonetheless included in the result table
equi-join
a join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table
join
a relational operation that causes two or more tables with a common domain to be combined into a single table or view
Database Management System (DBMS)
a software system that is used to create, maintain, and provide controlled access to user databases
index
a table or other data structure used to determine in a file the location of records that satisfy some condition
insertion anomaly
adding new rows forces user to create duplicate data
insert statement
adds one or more rows to a table
count
aggregate function used to find totals
alias
an alternative column or table name
natural join
an equi-join in which one of the duplicate columns is eliminated in the result table
design tools
automated tools used to design databases and application programs
repository
centralized storehouse of metadata
Modification anomaly
changing data in a row forces changes to other rows because of duplication
CREATE TABLE LIKE
create a new table similar to an existing one
metadata
data that describes the properties and context of user data
relational database
database technology involving tables (relations) representing entities and primary/ foreign keys representing relationships
deletion anomaly
deleting rows may cause a loss of data that would be needed for other future rows
advantages of partitioning
efficiency: Records used together are grouped together Local optimization: Each partition can be optimized for performance Security: data not relevant to users are segregated Recovery and uptime: smaller files take less time to back up Load balancing: Partitions stored on different disks, reduces contention
data model
graphical diagram capturing nature and relationship of data
Foreign Keys
identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship).
union join
includes all data from each table that was joined
disadvantages of partitioning
inconsistent access speed: Slow retrievals across partitions Complexity: Non-transparent partitioning Extra space or update time: Duplicate data; access from multiple partitions
update statement
modifies data in existing rows
Vector aggregate
multiple values returned from SQL query with aggregate function (via GROUP BY)
tablespace
named logical storage unit in which data from multiple tables/views/objects can be stored
relation
named, two-dimensional table of data; Columns correspond with attributes. Rows correspond with entity instances and with many-to-many relationship instances.
entity
noun describing a person, place, object, event, or concept; composed of attributes
database administrators
personnel responsible for maintaining the database
subquery
placing an inner query (SELECT statement) inside an outer query
disadvantages of file processing
program-data dependence, duplication of data, limited data sharing, lengthy development times, excessive program maintenance
views
provide users controlled access to tables
well-structured relation
relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies; goal is to avoid anomalies
Procedures
routines that do not return values and can take input or output parameters
Triggers
routines that execute in response to a database event (INSERT, UPDATE, or DELETE)
functions
routines that return values and take input parameters
scalar aggregate
single value returned from SQL query with aggregate function
Field
smallest unit of application data recognized by system software
application programs
software using the data
ALTER TABLE
statement allows you to change column specifications
DROP TABLE
statement allows you to remove tables from your schema
base table
table containing the raw data
user interface
text, graphical displays, menus, etc. for the user
Primary Keys
unique identifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique.
inner join
will only return rows from each table that have matching rows in the other.