CH 3- Database Processing Flashcards
Entity
• An entity is some identifiable thing that users want to track:
– Customers
– Computers
– Sales
Relation
-Relational DBMS products store data about entities in relations, which are a special type of table
Characteristic of relations
-Is a two-dimensional table
characteristics:
1) Rows contain data about an entity.
2) Columns contain data about attributes of the entities.
3) All entries in a column are of the same kind.
4) Each column has a unique name.
5) Cells of the table hold a single value.
6) The order of the columns is unimportant.
7) The order of the rows is unimportant.
8) No two rows may be identical.
Domain Integrity Constraint
-The requirement that all of the values in a column are of the same kind
domain= grouping of data that meets a specific type definition.
**Columns in different relations may have the same name.
Relations as defined by Codd
1) The rows of a relation must be unique.
2) There is no requirement for a designated primary key.
- The requirement for unique rows implies that a primary key can be designated.
- In the “real world,” every relation has a primary key.
Functional Dependencies
-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
-The attribute on the left side of the functional dependency is called the determinant
Functional dependencies may be based on equations:
ExtendedPrice = Quantity X UnitPrice
(Quantity, UnitPrice) → ExtendedPrice
• Function dependencies are not equations!
Composite Determinants
-determinant of a functional dependency that consists of more than one attribute
(StudentNumber, ClassNumber) → (Grade)
Functional Dependency Rules
If A → (B, C), then A → B and A→ C.
– This is the decomposition rule.
• If A → B and A → C, then A → (B, C).
– This is the union rule.
• However,
– if (A , B) → C, then neither A nor B determines C by
itself.
What Makes Determinant Values Unique
-determinant is unique in a relation if and only if it determines every other column in the relation.
keys
-a combination of one or more columns that are used to identify particular rows in a relation
composite key
key that consists of two or more columns.
candidate key
key that determines ALL of the other columns in a relation
primary key
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
Entity Integrity Constraint
- the primary key must have unique data values inserted into every row of the table.
- The phrase unique data values implies that this column is NOT NULL, and does not allow a NULL value in any row.
Surrogate Keys
-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 Keys
- is 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
Note: The primary keys of the relations are underlined and any foreign keys are in italics in the relations below:
Referential Integrity Constraint
-is a statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation:
Database Integrity
3 constraints:
1) The domain integrity constraint- Columns with values of the same kind
2) The entity integrity constraint- No null values in PK, unique values in PK
3) The referential integrity constraint- Limit values of FK to those existing in the corresponding PK.
• The purpose of these three constraints, taken as a whole, is to create database integrity, which means that the data in our database will be useful, meaningful data.
Types of Modification Anomalies
1) Insertion anomaly -The structure of the table forces us to enter facts about two entities when we just want to enter facts about one.
2) Deletion anomaly -When we delete one row, the structure of this table forces us to lose facts about two different things
3) Update anomaly -changes can produce data inconsistencies.
First Normal Form (1NF)
criteria:
1) Meets the set of conditions for a relation
2)Has a defined primary key- expand the PK to represent the repeating group.
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.
- Remove Partial Dependencies.
- For example, consider the STUDENT_ACTIVITY relation:
STUDENT_ACTIVITY (StudentID, StudentName, Activity,
ActivityFee, AmountPaid)
StudentID → StudentName
Activity → ActivityFee
StudentID,Activity →AmountPaid
Third Normal Form (3NF)
-A relation is in 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 a Transitive Dependency.
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.
Eliminating Anomalies from Functional Dependencies with BCNF
- Identify every functional dependency.
- Identify every candidate key.
- If there is a functional dependency that has a determinate that is not a candidate key:Note: In step 3, if there is more than one such
functional dependency, start with the one with the
most columns.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.
Multivalued Dependencies
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 be a primary key.
Fourth Normal Form (4NF)
Eliminating Multivalued Anomalies
• Multivalued dependencies are not a problem if they are in a separate relation, therefore:
– Always put multivalued dependencies into their own
relation
