logical database design and entity-relationship mapping
DESCRIPTION
Logical Database Design and Entity-Relationship Mapping. “T o Err is Human; to Forgive, Divine.” ~ Alexander Pope ~. What exactly is a Logical Model?. A Blueprint of your database. It is DBMS independent , and does not rely on an specific product. It is a technical communications tool. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/1.jpg)
Logical Database Design andEntity-Relationship Mapping
“To Err is Human; to Forgive, Divine.”~ Alexander Pope ~
![Page 2: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/2.jpg)
![Page 3: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/3.jpg)
What exactly is a Logical Model?
• A Blueprint of your database.• It is DBMS independent, and does not rely on
an specific product.• It is a technical communications tool.• Concepts from relational theory are re-
introduced (PK, FK, Table, Column, data type)• Any person knowledgeable in SQL should be
able to read a logical model and create a real database implementation from it.
![Page 4: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/4.jpg)
Concept Map: Conceptual vs. Logical
CONCEPTUAL LOGICAL
ERD Relational Diagram
Entity Relation (Table)
Attribute Column
Relationship Foreign Key (FK)
N/A PK, Constraints
Important: Rows (sample data) MUST be considered in the Logical Model !!!
![Page 5: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/5.jpg)
Relational Notation is Simple
• Table or relation is a box. Name of relation is at the top.
• Columns / Attributes are placed below the relation name in the box.
• Primary Key columns are Underlined and labeled with PK
• Foreign Key columns are labeled with FK
• A PK/FK Relationship between tables are established with an arrow (), which always POINTS TO THE PRIMARY KEY of the relationship.
SUID PKLnameFnameHtWt
Students
CardNo PKSUID FKCardTypeExpDate
CreditCards
![Page 6: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/6.jpg)
Mapping
Mapping is the process of transforming a conceptual model
into a logical model.
![Page 7: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/7.jpg)
Basic Attribute Mapping• Table name is plural• Use Naming
conventions• Assign data types• PK for each table• Break down composite
into simple• Set required to not null• Set Unique and check
constraints
Conceptual
Logical
![Page 8: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/8.jpg)
Mapping 1-M Relationships• FK goes on the
many side• If required on
the one side (aka Weak Entity), then set the FK to not allow null
Conceptual
Logical
![Page 9: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/9.jpg)
Mapping Multi-Valued Attributes
• Similar to 1-M:Place [m] attributes in new table. PK from original table is FK in the new table.C
onceptual
Logical
![Page 10: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/10.jpg)
Mapping Multi-Valued Attributes
• Similar to 1-M:Place [m] attributes in new table. PK from original table is FK in the new table.C
onceptual
Logical
![Page 11: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/11.jpg)
Mapping 1-1 Relationships• Similar to 1-M:
Since the FK goes to the optional side of the relationship, but the FK is set to PK.C
onceptual
Logical
![Page 12: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/12.jpg)
Mapping M-M Relationships
• Make a “bridge table” place FKs in bridge table and set as composite PK
Conceptual
Logical
![Page 13: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/13.jpg)
Mapping Weak Entities
• Make sure the foreign key does not allow null
Conceptual
Logical
![Page 14: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/14.jpg)
Advanced Mapping
Edge cases which are variations on the same themes. Examples provided
for reference.
![Page 15: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/15.jpg)
Ex: Mapping a Unary 1-M RelationshipConceptual
Logical
EmpIDENamePhoneHireDate
Employee
The FK inside the table forms the recursive relationship
Manages
EmpID PKENamePhoneHireDateManagerEmpID FK
Employee
![Page 16: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/16.jpg)
Ex: Mapping a Unary N-M RelationshipConceptual
Logical
ItemNo PKItmNameUnitCost
Item
Bridge Table with both FK’s relating back to the same table PK
ContainsItemNoItmNameUnitCost
Item
ItemNo PK FKComponentItemNo PK FKQty
Component
![Page 17: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/17.jpg)
Ex: Mapping Sub/Super Type
Conceptual Logical
EmpNoENameHireDate
Employee
Discriminator
HourlyRate
EmpHourly
AnnualRateStockOptns
EmpSalary
EmpNo PKENameHireDateEmpType
Employee
EmpNo PK FKHourlyRate
EmpHourly
EmpNo PK FKAnnualRateStockOptns
EmpSalary
![Page 18: Logical Database Design and Entity-Relationship Mapping](https://reader035.vdocuments.us/reader035/viewer/2022062301/56815a91550346895dc8065b/html5/thumbnails/18.jpg)
Logical Database Design andEntity-Relationship Mapping
Questions?