Database Normalization: A Guide to 1NF, 2NF, and 3NF

Agenda and Objectives Agenda Examine normalized and non-normalized ERDs/Tables Objectives Given an ERD, you will be able to determine whether or not it is in 3NF. Given a non-normalized ERD (or database table), you will be able to revise it to make it 3NF compliant. Resource Normalized_ERDs.pptx

Normalizing Your Database Model IS 310 Dr. Jean A. Pratt

Related Terminology Normalization First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF) 1NF: No repeating groups; 1 value/cell 2NF: No partial dependencies 3NF: No transitive dependencies

First Normal Form (1NF) Only those attributes that describe the entity are included in that entity Each entity has a primary key (PK) Each attribute is the smallest unit that would be included in a query Data is not repeated in different entities Each attribute would contain a single value in the database table cell Redundant data is minimized

Multivalued Attributes: AVOID Multivalued attribute An attribute that may take on more than one value for each entity instance Examples Skill is a multivalued attribute of Employee Color may be a multivalued attribute of ToyBlock Dependent is a multivalued attribute of Employee Phone/fax number is a multivalued attribute of Employee Repeating group A set of multivalued attributes that are logically related associated with the same entity instance Dependent address for each dependent of employee

Example of (almost) 1NF ERD Note that we will still have redundant data (although not multivalued) if students live at the same address. We ll want to remove that redundancy later.

Resulting 1NF Table STUDENT Student_ID 12345 First Name Joe Last Name Johnson Street 1234 Main City Eau Claire State WI Zip 54703 Phone 715-123-1234 23435 Sally Shue 7484 Main Altoona WI 54720 715-333-2111 23424 Molly Mae 2342 Main Mondovi WI 54755 715-324-7878 12346 Jill Johnson 1234 Main Eau Claire WI 54703 715-123-1234 12347 Joe Johnson 9898 Grande Elk Mound WI 54739 715-999-8888

How to Correct Multivalued or Repeating Attributes 1. Create new entity 2. New entity could be weak/dependent/identifying 3. New entity could independent

Multivalued Attributes: Table Add rows, not columns PHONE Emp ID 10 10 14 14 14 14 16 Phone Number 715-123-1234 715-123-4567 715-123-1234 715-123-6789 715-123-1111 715-123-2222 715-123-4545 Type H C H C O F O

Identify & Name the 1NF Violations

Resulting Table STUDENT Student Name Joe Johnson Address 1234 Main, Eau Claire, WI 54703 Emergency Contact Info Herbert and Gertrude Johnson, 1234 Main, Eau Claire, WI 54703 715-123- 1234 Simon and Susan Shue, 7484 Main, Altoona, WI 54720 715-333-2111 Albert and Alfreda Mae, 2342 Main, Mondovi, WI 54755, 715-324-7878 Herbert and Gertrude Johnson, 1234 Main, Eau Claire, WI 54703 715-123- 1234 Louise Johnson, 9898 Grande, Elk Mound, WI 54739 715-999-8888 Sally Shue 7484 Main, Altoona, WI 54720 Molly Mae 2342 Main, Mondovi, WI 54755 Jill Johnson 1234 Main, Eau Claire, WI 54703 Joe Johnson 9898 Grande, Elk Mound, WI 54739

Identify & Name the 1NF Violations

Resulting Table STUDENT Student_IDFirst Name Last Name Street City State Zip Emerg Phone Classes Grades Sports & Activities Position 12345 Joe Johnson 1234 Main Eau Claire WI 54703 715-123- 1234 Biology, English, Algebra A, B, B Football, Basketball, Baseball, Choir, Tennis QB, Bass, #1 Doubles 23435 Sally Shue 7484 Main Altoona WI 54720 715-333- 2111 Biology, English, Algebra B, B, A Debate, Band, Tennis Parliament ary, 1st Flute, #1 Doubles 23424 Molly Mae 2342 Main Mondovi WI 54755 715-324- 7878 Chemistry, Art, Math A, B, A, B Speech Impromptu 12346 Jill Johnson 1234 Main Eau Claire WI 54703 715-123- 1234 Algebra, English, Art A, A, A, A, Chess, Tennis President, #1 Singles 12347 Joe Johnson 9898 Grande Elk Mound WI 54739 715-999- 8888 Math, Trigonomet ry, Chemistry A, A, A Chess, VP, #1 Singles

Is This a Good Solution?

Resulting Table STUDENT Student_IDFirst Last Name Johnson Class 1 Grade 1 Class 2 Grade 2 Class 3 Grade 3 Sport/A ctivity 1 Football Position Sport/A ctivity 2 Choir Position Sport/A ctivity 3 Tennis Position Name Joe 12345 Biology A English B Algebra B QB Bass #1 Doubles #1 Doubles 1st Flute 23435 Sally Shue Biology B English B Algebra A Debate Parliam entary Improm ptu Preside nt VP Band Tennis 23424 Molly Mae Chemist ry Algebra A Art B Math A Speech 12346 Jill Johnson A English A Art A Chess Tennis #1 Single 12347 Joe Johnson Math A Trigono metry A Chemist ry A Chess

How would you Solve This Problem? EMPLOYEE Emp_ID 1 2 3 4 Fname Joe Mary Joey Ellen Lname Johnson Zabowski Elizabeth Cho Maffey Dependent1 Johnny Dependent2 Jane Madison Dependent3 Logan Annie Robert Blaine What about employees with more than 3 children?

Second Normal Form (2NF) Entities meet 1NF requirements PLUS All attributes are functionally dependent upon the entire primary key.

Identify & Name the 2NF Violations

Resulting Table STUDENT Student_IDFirst Name Last Name Class Grades Sport or Position Activity Football Basketball Baseball Choir Tennis Speech 12345 12345 12345 12345 12345 23424 23424 23424 Joe Joe Joe Joe Joe Molly Molly Molly Johnson Johnson Johnson Johnson Johnson Mae Mae Mae Biology English Algebra A B B QB Guard Catcher Bass #1 Doubles Impromptu Chemistry Art Math A B A

Third Normal Form (3NF) Violation Entities meet 2NF requirements BUT One non-key attribute determines another non- key attribute

Completed 3NF ERD

What are the Values for the Associative Entities?

GUARDIAN Guardian_ID 1 2 3 4 Resulting Relations First Name Herbert Simon Albert Louise Last Name Johnson Shue Mae Johnson Add_ID 1 2 3 1 Phone 715-123-1234 715-333-2111 715-324-7878 715-999-8888 STUDENT Student_ID 12345 23435 23424 12346 12347 First Name Joe Sally Molly Jill Joe Last Name Johnson Shue Mae Johnson Johnson Add_ID 1 2 3 1 4 Phone 715-123-1234 715-333-2111 715-324-7878 715-123-1234 715-999-8888 Guardian_ID 1 2 3 1 4 CLASS Class_ID 10 20 30 40 50 60 70 80 90 ENROLLMENT Enrollment_ID 11111 11112 11113 11114 11115 11115 Name Biology English Algebra Chemistry Math Art Trigonometry History Geography Year 2007 2007 2007 2008 2008 20008 Semester Fall Fall Fall Spring Spring Spring Grade B- F A B+ C B Class_ID 30 30 60 80 30 90 Student_ID 23424 12346 12346 12345 12346 12345 SPORT/ ACTIVITY Event_ID 100 101 102 103 104 105 106 107 108 109 PARTICIPATION PartID 222 223 224 225 226 227 228 229 Position 2nd Singles Guard Alto QB Forensics Forensics 1st Doubles 1st Doubles Student_ID 23424 12346 12346 12345 12346 12345 23435 23424 Event_ID 107 106 104 100 101 101 107 107 Name Football Debate Speech Chess Choir Band Basketball Tennis Baseball Hockey ADDRESS Add_ID 1 2 3 4 Zip 54703 54720 54755 54739 State WI WI WI WI City Eau Claire Altoona Mondovi Elk Mound Street 1234 Main 7484 Main 2342 Main 9898 Grande

Review Know the difference between 1NF, 2NF and 3NF Ensure your ERD (and resulting database) is in at least 2NF 3NF is the standard (with purposeful violations based on performance)

Application to Your Project Define the necessary tables from every DFD data store Create an ERD to model the data storage Normalize the ERD to 3NF Create mock-up relations to test your model Include the ERD and mock-up relations in your final project documentation

Data Modeling Practice 1. 2. A vendor supplies many products. A product is supplied by many vendors. 3. 4. A customer buys one or more products. Each product can be purchased by many customers. 5. 6. An employee can help many customers. A customer is helped by one or more employees. 7. 8. Each employee works in one department. A department has many employees (working in it). 9. 10. Each store contains many departments. Each department is part of many stores. Scenario: Chain stores (e.g., Target) that contain several departments and sell many products to many customers.