Example of a 1:M or M:1 Relationship (Assumes No Team Teaching of a Class)

1. Example of a 1:M or M:1 Relationship(Assumes No Team Teaching of a Class) M 1 Faculty Enroll Classes Course ID PK Section PK Bldg Room Days Time Instructor SSN FK SSN PK Name Office Location Office Phone Please Note: The PK of the “1” must be included in the “M” as a FK

2. M:M Relationships • A M:M relationship CANNOT be in the final database design • Resolve M:M relationships: • Create a new entity between the M:M relationships • Name the new entity a hyphenated name using the original two entity names • The relationship by the new entity is M • The relationship by the original entity is “1” • The PK of the new entity is a composite PK consisting of the PK of the original entities

3. Resolution of M:M RelationshipExample 1 Slide 1 M M Classes Enroll Students SSN PK Name Major Gender Marital Course ID PK Section PK Bldg Room Days Time Instructor SSN

4. Resolution of M:M RelationshipExample 1 Slide 2 SSN PK Name Major Gender Marital Students Course ID PK Section PK Bldg Room Days Time Instructor SSN 1 Enroll M taken by Classes Student-Classes 1 M SSN PK Course ID PK Section PK

5. Example 2: Fix this ERD! Customer ID PK Name Address Supplier ID PK Name Address Phone Customers Supplier 1 1 Place Refurbished by M M M Order ID PK Order Date Ship VIA Are for items from Orders Inventory M 1 Part NO PK Description Numb in Inventory Price

6. Resolution of M:M RelationshipExample 2 Slide 2 Customer ID PK Name Address Supplier ID PK Name Address Phone Customers Supplier 1 1 Place M Refurbished by Order ID PK Order Date Ship VIA Customer ID FK Orders 1 M M Inventory Consist of Parts on Order Stored in M 1 Part NO PK Description Numb in Inventory Price Supplier ID FK Order ID PK Part No PK Quantity

7. Database Development • Third, analyze the data structure by applying the Normalization process • Method that reduces a relational database to its most streamlined form • Helps achieve • minimum redundancy • maximum data integrity • best processing performance

8. Normalization A technique that organizes data attributes (or fields) such that they are grouped to form stable, flexible and adaptive entities.

9. Normal Forms • First Normal Form (1NF) • There are no attributes (fields) that have more than one value for a single instance (record) of the entity. • There are no repeating fields • Second Normal Form (2NF) • 1NF • The value of all non-primary key fields are dependent on the full primary key - not just part of it

10. Normal Forms • Third Normal Form (3NF) • 2NF • The values of its non-primary key fields are not dependent on any other non-primary key field

11. Keys • Primary • A field whose values identify one and only one record in a table • Every ACCESS table must have EXACTLY one primary key • Foreign • A primary key of one table that is included in another table • Links the records in one database table to another table

12. Steps Of Normalization 1.Remove repeating groups of data fields. • Remove partial dependencies: If any data fields are dependent upon only part of the primary key defined for your core business transaction, split those fields out into a separate table, using the field(s) upon which they are dependent as the primary key.

13. Steps Of Normalization 3.Remove transitive dependencies: If any fields are dependent upon a non-key field, split those fields out into a separate table, using the non-key field as the primary key. 4. Remove any field which can be easily derived from other field(s): If any fields can be derived from others, remove from database. 5. Check by drawing/comparing to E-R diagram: is this model correct/logical?

14. Non-normalized table • In this example, we want to design an order database. • Identify all fields needed in this database • Which attribute above uniquely identifies each order? • Order Number is the starting primary key

15. Non-normalized table • An order can have many parts. This is a repeating field. What fields above are more closely related to a part than an order? Part Number, Number of Parts [quantity ordered], Part Description, Unit Cost, Supplier Number [for the part], supplier name, supplier address. • Remove these fields and put them into a separate table. Which one of these fields uniquely identifies the part information? [Part Number] • The Primary Key to the new table will be the original primary key combined with the new unique identifier (Part Number) • When a primary key has two or more parts, it is called a composite primary key. Original Primary Key: Order Number

16. First Normal Form Primary Key: Order Number Composite Primary Key: Order Number plus Part Number

17. Checking Second Normal Form • Look at the second table • If you know a part of the primary key, could you derive any of the non-keyed fields? • For example: If you know the part number [part of the primary key], could you derive the part description, the unit cost and the supplier information for that part? • Violation of second normal form Primary Key: Order Number Composite Primary Key: Order Number plus Part Number

18. Checking Second Normal Form • Remove the non-keyed fields from the second table that are dependent on only part of the primary key and put them in a new table [Part Description. Unit Cost, Supplier Information] • Copy the part of the primary key from which these fields can be determined into the new table. This field will become the primary key of the new table. Primary Key: Order Number Composite Primary Key: Order Number plus Part Number

19. Second Normal Form Primary Key: Order Number Composite Primary Key: Order Number plus Part Number Primary Key: Part Number Notice: I moved the PART table name to the table that had the most information about the part. The ordered part table name came from the rules for the ERD. One order can have many parts on it and one part [product line] can be on many orders.

20. Checking Third Normal Form • Look at the third table: If you know the Supplier Number [a non-keyed field], you can determine the supplier name and address [non-keyed fields] • Violation of third normal form Primary Key: Order Number Composite Primary Key: Order Number plus Part Number Primary Key: Part Number

21. Checking Third Normal Form • Remove the fields that can be determined from the other field from this table. Put them [supplier name and address] in a new table. • Copy the field that determines them from the original table into the new table [supplier number]. • This field will be the primary key of the new table. Primary Key: Order Number Composite Primary Key: Order Number plus Part Number Primary Key: Part Number

22. Part 1 of Third Normal Form Primary Key: Order Number Composite Primary Key: Order Number plus Part Number Primary Key: Part Number Primary Key: Supplier Number

23. Checking Third Normal Form • The top table has the same violation as before but with the customer information. • Customer name and address can be determined from the customer number • Violation of 3rd Normal Form Primary Key: Supplier Number Primary Key: Order Number Composite Primary Key: Order Number plus Part Number Primary Key: Part Number

24. CheckingThird Normal Form • Remove the fields that can be determined from the other field from this table. Put them [cust name, address] in a new table. • Copy the field that determines them from the original table into the new table [customer number]. • This field will be the primary key of the new table. Primary Key: Supplier Number Primary Key: Order Number Composite Primary Key: Order Number plus Part Number Primary Key: Part Number

25. Third Normal Form Primary Key: Customer Number Primary Key: Order Number Primary Key: Supplier Number Composite Primary Key: Order Number plus Part Number Primary Key: Part Number

26. Draw the ERD for Validation Orders Order No Order Date Delivery Date Order Total Customer No Customers Cust No Cust Name Cust Address m Place 1 Supplier Supplier No Supplier Name Supplier Address 1 1 Contain Locate M M Parts [Inventory] Part Number Part Description Unit Cost Supplier No Ordered Part Order No Part Number Number of Parts 1 M Locate

27. ERD Validation • In a M:1 relationship: The PK of the entity by the “M” label, must be contained in the entity by the “1” label. • The actual rule says: The PK of the “1” must must be contained in the “M” • A customer can place many orders. But one order can be placed by only ONE customer. So the PK of the customer table must be in the order table [it already is!]

28. Draw the ERD for Validation Orders Order No Order Date Delivery Date Order Total Customer No FK Customers Cust No Cust Name Cust Address M Place 1 Supplier Supplier No Supplier Name Supplier Address 1 1 Contain Locate M M Parts [Inventory] Part Number Part Description Unit Cost Supplier No FK Ordered Part Order No FK Part Number FK Number of Parts 1 M Locate

29. Foreign Keys • A customer can place many orders. But one order can be placed by only ONE customer. So the PK of the customer table must be in the order table • An order can have many parts on it. But an ordered part is related to one order. So the PK of the order table must be in the ordered parts table. • A part can be on contained on many ordered parts. A specific ordered parts is related to exactly one part in inventory. [Example: Widgets can be on many orders but widgets on a particular order relates exactly to one part in inventory]. So the part number must be contained in the ordered parts. • Note ordered parts has two foreign keys. One for the order table and one for the parts table. • A supplier can supplier many parts. But one part is supplied by only one supplier. So the supplier number (PK) must be contained in the parts file as a FK.

30. Normalized Relation