DBS201: Normalization Review

1. DBS201: Normalization Review Lecture 6

2. Agenda • Steps of Normalization

3. Bottom Up Database Design • Bottom Up • Provided with views of data • Views can be screen shots or reports (printouts) • Views contain fields (data) • Need to groups fields together – find fields that are in common • Result is tables

4. Normalization • Works through a series of stages called normal forms: • Normal form (1NF) • Second normal form (2NF) • Third normal form (3NF)

5. First Normal Form • Eliminate repeating groups • Present data in a tabular format, where each cell has a single value and there are no repeating groups • Eliminate repeating groups by eliminating nulls, making sure that each repeating group attribute contains an appropriate data value • Identify candidate primary key • Primary key must uniquely identify attribute values (a row) • State table structure (will show functional dependencies)

6. First Normal Form • Identify candidate primary key • Identify an attribute who’s value is different in each row, or • Look for any pair of attribute which when concatenated produce a unique value

7. First Normal Form • Identify candidate primary key • Primary key must uniquely identify attribute values (a row) • What is a candidate for primary key in the following? AttribAAttribBAttribCAttribDAttribEAttribF QQQO WWWO EEEO RRRO TTJO YVYO QQOQ WWVO FEFO RRPO TJTO YYVO QOQQ WVWO FEEO RPRO JTTO VYYO QOOQ VWWO EFEO PRRO JJTO VYVO QOQO WWWO FEFO PPRO JTJO YYYO QQQO VWVO EEFO PPRO TJJO VYVO

8. First Normal Form • Identify candidate primary key • What is a candidate for primary key in the following? AttribAAttribBAttribCAttribDAttribEAttribF QQQO WWWO EEEO RRRO TTJO YVYO QQOQ WWVOFEFORRPO TJTO YYVOQOQQWVWOFEEORPROJTTOVYYO QOOQVWWOEFEOPRRO JJTOVYVO QOQOWWWO FEFO PPRO JTJO YYYO QQQOVWVOEEFOPPRO TJJOVYVO • Only atttribE is a candidate – because it’s value is different in each row

9. First Normal Form • Identify candidate primary key • Primary key must uniquely identify attribute values (a row) • What is a candidate for primary key in the following? ABCDE Horse 2 years $500,000 Mohawk Race Track Lightening Rod GreyHound 3 years $125,000 Tampa Florida Speckled Speed Horse 1 year $80,000 Blue Bonnet Raceway Slow Harry GreyHound 4 years $125,000 Hialia Atlantic City All Leggs

10. First Normal Form • Identify candidate primary key • Primary key must uniquely identify attribute values (a row) • What is a candidate for primary key in the following? ABCDE Horse 2 years $500,000 Mohawk Race Track All Leggs GreyHound 3 years $125,000 Tampa Florida Speckled Speed Horse 1 year $80,000 Blue Bonnet Raceway Slow Harry GreyHound 4 years $125,000 Hialia Atlantic City All Leggs

11. First Normal Form • What is a candidate for primary key in the following?

12. First Normal Form • What is a candidate for primary key in the following?

13. First Normal Form • What is a candidate for primary key in the following?

14. Second Normal Form • Identify partial primary keys • Determine which attributes are dependent on the partial primary key • State table structures (will show partial dependencies) • Write each table structure on a separate line with it’s primary key • Write the original (composite) key on the last line with attributes removed that have been moved to the new table structures

15. Third Normal Form • For every transitive dependency, identify the determinant – it will be taken from a non-key attribute (will become the PK for a new table) • Determine which non-key attributes are dependent on this new primary key • State table structures (will show transitive dependencies) • Write each table structure on a separate line with it’s primary new key • Write the partial dependency table structures • Write the original (composite) key on the last line with attributes removed that have been moved to the new table structures

16. Third Normal Form • For every transitive dependency, identify the determinant – it will be taken from a non-key attribute (will become the PK for a new table) • Determine which non-key attributes are dependent on this new primary key • State table structures (will show transitive dependencies) • Write each table structure on a separate line with it’s primary new key • Write the partial dependency table structures • Write the original (composite) key on the last line with attributes removed that have been moved to the new table structures