1 / 26

Agenda and Objectives

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

carolinet
Download Presentation

Agenda and Objectives

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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.

More Related