Mapping ER modeling to Relationships

1. Mapping ER modeling to Relationships

2. Review of Relation Properties • Relation • Every relation has a unique name. • Every attribute value is atomic. • Every row is unique. • Attributes in tables have unique names. • The order of the columns is irrelevant. • The order of the rows is irrelevant.

3. Review of Relational Keys and Structures • Primary Key • Candidate Key • Composite Key • Foreign Key: an attribute (or a composite attribute) in a relation that serves as the primary key of another relation • One-to-Many Relationship • Many-to-Many Relationship

4. Schema for four relations (Pine Valley Furniture)

5. Review of Integrity Constraints • Domain Constraints • Allowable values for an attribute. • Entity Integrity • No primary key attribute may be null. • Referential Integrity: maintains consistency among related relations • Foreign Key value in one relation must match a primary key value in other relation • For example: Delete Rules • Restrict, Cascade, Set-to-Null • Operational Constraints: Business rules

6. Referential integrity constraints (Pine Valley Furniture)

7. Well-Structured Relations • Is a relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies

8. Transforming E-R Diagrams Into Relations 3. Map Binary Relationships • One-to-Many - Primary key on the one side becomes a foreign key on the many side • Many-to-Many - Create a new relation with the primary keys of the two entities as its primary key • One-to-One - Primary key on the mandatory side becomes a foreign key on the optional side

9. Translating an ER diagram into Relationships (one to many) • Create one table for each entity • For each entity that is only at the “one” side a one to many relationship (not many end), create a single column primary (use an arbitrary unique number if no natural key exists) • For each entity that is at the many side of a one to many relationship, use the primary key of the parent (one side) in the table as the foreign key • Entity at the many side of one or more relationship has a natural key, use that single column as the primary key. Else, concatenate the primary key of the one side with any columns needed for uniqueness

10. Example of mapping a 1:M relationship (a) Relationship between customers and orders 1 (0, M)

11. (b) Mapping the relationship

12. Example of mapping an M:N relationship (a) Requests relationship (M:N) (0, M) (1, M)

13. (b) Three resulting relations

14. Mapping a binary 1:1 relationship (a) Binary 1:1 relationship 1 (0, 1)

15. (b) Resulting relations

16. Transforming E-R Diagrams Into Relations 4. Map Associative Entities • Primary Keys • Default primary key for the association relation is the primary keys of the two entities

17. Mapping an associative entity with an identifier (a) Associative entity (SHIPMENT)

18. (b) Three relations

19. Mapping Supertype/subtype relationships to relations

20. Map Composite and Multi-valued Entities to Relations • Composite attributes: Use only their simple, component attributes • Multi-valued Attribute - Becomes a separate relation with a foreign key taken from the superior entity

21. Mapping a composite attribute (a) CUSTOMER entity type with composite attribute