1 / 21

Mapping ER modeling to Relationships

Mapping ER modeling to Relationships. 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.

vasanti-lakshmi
Download Presentation

Mapping ER modeling to Relationships

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

More Related