Ch 3 Flashcards
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
Types of Modification Anomalies
Deletion anomaly; Insertion anomaly; Update anomaly
First Normal Form (1NF)
Meets the set of conditions for a relation; has a defined primary key
Second Normal Form (2NF)
A relation is in 2NF if and only if it is in 1NF and all non-key attributes are determined by the entire primary key. This is also known as Partial Dependency. For Example:
A relation R(A,B,N,O,P) with the composite key (A,B) means that none of the non-key attributes N,O,or P can be determined by just A or just B. (A and B are underlined in the above relation)
(StudentID, Activity) -> ActivityFee
Activity -> ActivityFee
Third Normal Form (3NF)
If and only if it is in 2NF and there are no non-key attributes determined by another non-key attribute. This is also known as Transitive Dependency. For Example:
A relation R(A,B,N,O,P) wth the composite key (A,B) means that none of the non-key attributes N,O,or P can be determined by N, O, or P. (A and B are underlined in the above relation. StudentID is underlined in the below relation)
STUDENT_HOUSING (StudentID, Building, BuildingFee)
Building -> BuildingFee
Boyce-Codd Normal Form (BCNF)
A relation is in BCNF if, and only if, it is in 3NF and every determinant is a candidate key
Process for putting a relation into BCNF
- Identify every functional dependency.
- Identify every candidate.
- If there is a functional dependency that has a determinate that is not a candidate key:
A) Move the column of that functional dependency into a new relation
B) Make the determinant of that functional dependency the primary key of the new relation.
C) Leave a copy of the determinant as a foreign key in the original relation.
D) Create a referential integrity constraint between the original relation and the new relation. - Repeat step 3 until every determinant of every relation is a candidate key.
NOTE: In step 3, if there is more than one such functional dependency, start with the one with the most columns.
Multivalued Dependency
Occurs when a determinant is matched with a particular set of values:
Employee ->-> Degree
Employee ->-> Sibling
PartKit ->-> Part
The determinant of a multivalued dependency can never by a primary key
Fourth Normal Form (4NF)
Multivalued dependencies are not a problem if they are in a separate relation, therefore:
Always put multivalued dependencies into their own relation.
This is known as Fourth Normal Form (4NF)
