1 / 18

Logical Database Design and Entity-Relationship Mapping

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.

viveca
Download Presentation

Logical Database Design and Entity-Relationship Mapping

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. Logical Database Design andEntity-Relationship Mapping “To Err is Human; to Forgive, Divine.” ~ Alexander Pope ~

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

  3. Concept Map: Conceptual vs. Logical Important: Rows (sample data) MUST be considered in the Logical Model !!!

  4. Students CreditCards SUID PK Lname Fname Ht Wt CardNoPK SUID FK CardType ExpDate 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.

  5. Mapping Mapping is the process of transforming a conceptual model into a logical model.

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

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

  8. Mapping Multi-Valued Attributes • Similar to 1-M:Place [m] attributes in new table. PK from original table is FK in the new table. Conceptual Logical

  9. Mapping Multi-Valued Attributes • Similar to 1-M:Place [m] attributes in new table. PK from original table is FK in the new table. Conceptual Logical

  10. 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. Conceptual Logical

  11. Mapping M-M Relationships • Make a “bridge table” place FKs in bridge table and set as composite PK Conceptual Logical

  12. Mapping Weak Entities • Make sure the foreign key does not allow null Conceptual Logical

  13. Advanced Mapping Edge cases which are variations on the same themes. Examples provided for reference.

  14. Employee Employee EmpID EName Phone HireDate EmpIDPK EName Phone HireDate ManagerEmpID FK Ex: Mapping a Unary 1-M Relationship Manages Conceptual The FK inside the table forms the recursive relationship Logical

  15. Item Item Component ItemNoPK ItmName UnitCost ItemNo ItmName UnitCost ItemNoPK FK ComponentItemNoPK FK Qty Ex: Mapping a Unary N-M Relationship Contains Conceptual Bridge Table with both FK’s relating back to the same table PK Logical

  16. Employee Employee EmpNo EName HireDate EmpNoPK EName HireDate EmpType EmpHourly EmpSalary EmpHourly EmpSalary HourlyRate AnnualRate StockOptns EmpNoPK FK HourlyRate EmpNoPK FK AnnualRate StockOptns Ex: Mapping Sub/Super Type Logical Conceptual Discriminator

  17. Logical Database Design andEntity-Relationship Mapping Questions?

More Related