15/02/2010
Exam
- ERD
- Normalization-single themed
- Anomalies
- Anomalies
Update | Repetition |
Insert | NULL Multi value in one cell |
Delete | NULL Valuable information missing |
Read
http://www.ischool.utexas.edu/~wyllys/DMPAMaterials/normover.html
Exercise
Single theme tables
Confining the entries in any one table to data related to a single set of related attributes
Formal Definition of the Normal Forms
1st, 2nd, 3rd, 4th, 5th
*up to 3rd industry standard
1st Normal Form (1NF)
Definition
- There are no duplicated rows in the table.
- Each cell is single-valued
- Entries in a column (attribute, field) are of the same kind.
Rule
- Eliminate Repeating Groups.
- Make a separate table for each set of related attributes.
- Give each table a primary key.
*non-key: it has to be unique per row.
2nd Normal Form (2NF)
Definition
A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.
Rule
- Eliminate Redundant Data.
- If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3rd Normal Form (3NF)
Definition
A table is in 3NF if it is in 2NF and if it has no transitive dependencies.
Rule
- Eliminate Columns Not Dependent on Key.
- If attributes do not contribute to a description of the key, remove them to a separate table.
Transitive dependency
A→B and B→C = A→C
Example
Author Last Name
Author First Name
Book Title
Subject
Collection or Library
Building
Book Title→Subject Subject→Library Library→Building
Thus, Book Title→Building
Boyce-Codd Normal Form (BCNF)
Definition
A table is in BCNF if it is in 3NF and if every determinant is a candidate key.
4th Normal Form (4NF)
Definition
A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.
Rule
- Isolate Independent Multiple Relationships.
- No table may contain two or more l:n or n:m relationships that are not directly related.
5th Normal Form (5NF)
Definition
A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.
Rule
- Isolate Semantically Related Multiple Relationships.
- There may be practical constraints on information that justify separating logically related many-to-many relationships.
Domain-Key Normal Form (DKNF)
Definition
A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains.
0 件のコメント:
コメントを投稿