Ch 3 Flashcards
(32 cards)
Relational Model
Introduced in a paper published in 1970. Created by E.F. Codd, IBM Engineer. Uses math known as relational algebra. the standard for commercial DBMS products.
Relation
Two dimensional table that has the following characteristics:
Rows contain data about an entity;
Columns contain data about attributes of the entities;
All entries in a column are of the same kind;
Each column has a unique name;
Cells of the table hold a single value;
the order of the columns is unimportant;
the order of the rows is unimportant;
no two rows may be identical
Relational DBMS Products
Store data about entities in relations, which are a special type of table.
Entity
Some identifiable thing that users want to track: Customers, Computers, Sales.
Functional Dependency
Occurs when the value of one (set of) attribute(s) determines the value of a second (set of) attribute(s):
CookieCost = NumberOfBoxes x $5
NumberOfBoxes –> CookieCost;
Functional Dependencies are not equations
Determinant
The attribute on the left side of the functional dependency; may be based on equations:
ExtendedPrice = Quantity x UnitPrice
(Quantity, UnitPrice) –> ExtendedPrice
Domain Integrity Constraint
The requirement that all of the values in a column are of the same kind
Domain
A grouping of data that meets a specific type definition.
Composite Determinant
A determinant of a functional dependency that consists of more than one attribute:
(StudentNumber, ClassNumber) –> (Grade)
Decomposition Rule
If A -> (B, C), then A -> B and A -> C
Union Rule
If A -> B, and A -> C, then A -> (B,C)
If (A,B) -> C, then neither A nor B determines C by itself
If (A,B) -> C, then neither A nor B determines C by itself
Candidate Key
A key that determines all of the other columns in a relation
Composite Key
A key that consists of two or more columns
Primary Key
A candidate key selected as the primary means of identifying rows in a relation. There is only one primary key per relation. The primary key may be a single key or a composite key. (Underlined in code)
Surrogate Key
An artificial column added to a relation to serve as a primary key. DBMS supplied; short, numeric, and never changes - an ideal primary key; has artificial values that are meaningless to users; normally hidden in forms and reports
Foreign Key
A column or composite of columns that is the primary key of a table other than the one in which it appears. The term arises because it is a key of a table foreign to the one in which it appears as the primary key. (In italics in code)
Referential Integrity Constraint
A statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation:
SKU in ORDER_ITEM must exist in SKU and SKU_DATA
What makes determinant values unique?
A determinant is unique in a relation if and only if it determines every other column in the relation; You can’t find the determinants of all functional dependencies simply by looking for unique values in one column: data set limitations, must be logically determinant.
Key
A combination of one or more columns that is used to identify particular rows in a relation
Entity Integrity Constraint
The requirement that, in order to function properly, the primary key must have unique data values inserted into every row of the table.
Unique Data Values
This phrase implies that this column is NOT NULL, and does not allow a NULL value in any row.
The purpose of the three constraints (Domain Integrity, Entity Integrity, and Referential Integrity)
The purpose of these three constraints, taken as a whole, is to create database integrity, which means that that data in our database will be useful, meaningful data.
Three Constraints for database integrity
Domain Integrity Constraint; Entity Integrity Constraint; Referential Integrity Constraint
