-
Requirements to be a relation (6)
- Every relation has a unique name
- Every attribute value is atomic
- Every row is unique
- Attributes in a given table have unique names
- The order of the columns is irrelevant
- The order of the rows is irrelevant
-
Normalization
- A process for evaluating and correcting table tructures to minimze data redundancies, thereby reducing the likelihood of data anomalies
- decomposing relations to produce smaller, well strucuted relations
- "check" against potential mistakes that might be in the structure of the database tables
-
What is considered a well structured relation
- a relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies
- A table should not pertain to more than one entity type
-
Any relation that does not have multi valued attributes
First Normal Form, atomic, any valid relation is in 1NF by definition
-
Normalization processes
- Eliminate repeating groups (multivalued attributes)
- Identify the primary key
- Identify all dependencies
-
Relation that every key attribute is fully functionally dependent on the primary key
Second normal form, no partial functional dependencies
-
Any relation in 1NF that has a single attribute primary key
is already in 2NF
-
A relation that contains no transitive dependencies
3NF
-
Converting to 3NF
- Make new tables to eliminate transitive dependencies
- Reassign corresponding dependent attributes
-
Converting to 2NF
- Make new tables to eliminate partial dependencies
- Reassign the corresponding dependent attributes
-
BCNF
Boyce-Codd Normal form, a table is in this form if every determinant in the table is a candidate key
-
Criteria for a well designed database
- No redundancy
- No partial dependencies
- No transitive dependencies
-
How to create a good database design
- Identify entities involved and their relevant attribute and identifiers
- Define relationships between entities
- Draw an ER / EER diagram to model the problem
- Transform the ER / EER model to a reltional schema
- Normalize the relations up to BCNF
|
|
