Agenda and Objectives

1. 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

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

3. 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

4. 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 First Normal Form (1NF)

5. Multivalued Attributes: AVOID • Multivalued attribute • An attribute that may take on more than one value for each entity instance • Examples • Skillis a multivaluedattribute 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

6. 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.

7. Resulting 1NF Table

8. How to Correct Multivalued or Repeating Attributes • Create new entity • New entity could be weak/dependent/identifying • New entity could independent

9. Multivalued Attributes: Table • Add rows, not columns

10. Identify & Name the 1NF Violations

11. Resulting Table

12. Identify & Name the 1NF Violations

13. Resulting Table

14. Is This a Good Solution?

15. Resulting Table

16. How would you Solve This Problem? What about employees with more than 3 children?

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

18. Identify & Name the 2NF Violations

19. Resulting Table

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

21. Completed 3NF ERD

22. What are the Values for the Associative Entities?

23. Resulting Relations

24. 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) Review

25. 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

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