1 er modeling buad/american university mapping er modeling to relationships
TRANSCRIPT
![Page 1: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/1.jpg)
1ER ModelingBUAD/American University
Mapping ER modeling to Relationships
![Page 2: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/2.jpg)
2ER ModelingBUAD/American University
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.
![Page 3: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/3.jpg)
3ER ModelingBUAD/American University
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
![Page 4: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/4.jpg)
4ER ModelingBUAD/American University
Schema for four relations (Pine Valley Furniture)
![Page 5: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/5.jpg)
5ER ModelingBUAD/American University
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
![Page 6: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/6.jpg)
6ER ModelingBUAD/American University
Referential integrity constraints (Pine Valley Furniture)
![Page 7: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/7.jpg)
7ER ModelingBUAD/American University
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
![Page 8: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/8.jpg)
8ER ModelingBUAD/American University
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
![Page 9: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/9.jpg)
9ER ModelingBUAD/American University
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
![Page 10: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/10.jpg)
10ER ModelingBUAD/American University
Example of mapping a 1:M relationship
(a) Relationship between customers and orders
(0, M)
1
![Page 11: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/11.jpg)
11ER ModelingBUAD/American University
(b) Mapping the relationship
![Page 12: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/12.jpg)
12ER ModelingBUAD/American University
Example of mapping an M:N relationship
(a) Requests relationship (M:N)
(0, M) (1, M)
![Page 13: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/13.jpg)
13ER ModelingBUAD/American University
(b) Three resulting relations
![Page 14: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/14.jpg)
14ER ModelingBUAD/American University
Mapping a binary 1:1 relationship
(a) Binary 1:1 relationship
(0, 1)
1
![Page 15: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/15.jpg)
15ER ModelingBUAD/American University
(b) Resulting relations
![Page 16: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/16.jpg)
16ER ModelingBUAD/American University
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
![Page 17: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/17.jpg)
17ER ModelingBUAD/American University
Mapping an associative entity with an identifier
(a) Associative entity (SHIPMENT)
![Page 18: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/18.jpg)
18ER ModelingBUAD/American University
(b) Three relations
![Page 19: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/19.jpg)
19ER ModelingBUAD/American University
Mapping Supertype/subtype relationships to relations
![Page 20: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/20.jpg)
20ER ModelingBUAD/American University
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
![Page 21: 1 ER Modeling BUAD/American University Mapping ER modeling to Relationships](https://reader035.vdocuments.us/reader035/viewer/2022070414/5697c0111a28abf838ccb710/html5/thumbnails/21.jpg)
21ER ModelingBUAD/American University
(a) CUSTOMER entity type with composite attribute
Mapping a composite attribute