1 / 15

Database Design – Lecture 15

This lecture details the essential steps for converting a class diagram into a relational model. Key methodologies include transforming simple classes into tables while identifying primary keys, creating bridge tables for intersection classes, and handling association relationships effectively. Special attention is given to weak and strong entity relationships, inheritance, and many-to-many associations. Real-world examples, such as the enrolment scenario involving supertype entities, illustrate the conversion process, including table creation and normalization techniques.

neal
Télécharger la présentation

Database Design – Lecture 15

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. Database Design– Lecture 15 Converting a Class Diagram to a Relational Model

  2. Steps to Convert from an Class Diagram to Relational • Simple classes become tables. Identify a unique identifier and make this a primary key. • Intersection class becomes a bridge table. • Remove derived attributes. • For association relationship of 1:M, add the PK of the 1 table as an FK of the M table.

  3. Steps to Convert from an Class Diagram to Relational • For aggregation/composition relationships: • Consider weak/strong entity relationships: • If a weak relationship (aggregation), add the PK of the aggregate as a FK in the ‘part’ (it will not be part of the PK). • If a strong relationship (composition), add the PK of the aggregate as part of a composite primary key of the ‘part’. It will also be a FK.

  4. Steps to Convert from an Class Diagram to Relational • Inheritance relationship: • Create a table for the superclass and a table for each of the subclasses (subtype tables). Unique identifier (primary key) for both tables will be the same, or • Create a table for each subclass (include attributes from superclass in each table), or • Create one table for the entire structure and create a new attribute to denote which type of subclass it is

  5. Steps to Convert from an Class Diagram to Relational • Many-to-many associations: • Create two base tables and a bridge table. The PKs of the base tables will be PK/FKs of the bridge table. • Do normalization.

  6. Steps to Convert from an Class Diagram to Relational • Enrolment Example

  7. Steps to Convert from an Class Diagram to Relational • Enrolment Example Continued

  8. Steps to Convert from an Class Diagram to Relational • Person is a supertype entity of Employee, Dependent and Beneficiary • Create primary key for each table and convert • Tables (option a from rules): PERSON (PERSON_ID (pk), FNAME, LNAME) EMPLOYEE (EMPL_PERSON_ID (pk), SEX, DATEOFBIRTH, ANNUAL_SALARY, ADDRESS) BENEFICIARY (BEN_PERSON_ID (pk)) DEPENDENT (DEP_PERSON_ID (pk), DATEOFBIRTH, RELATIONSHIP)

  9. Steps to Convert from an Class Diagram to Relational • Connectivity between Person supertype and each subtype (Employee, Dependent and Beneficiary is 1:1) • Additional Relationships: • EMPLOYEE:BENEFICIARY is M:N (we already identified an intersection table – EMPLOYEE_BENEFICIARY) • EMPLOYEE:DEPENDENT is 1:M and is a strong/weak relationship (therefore add PK of 1 as a PK,FK of weak)

  10. Steps to Convert from an Class Diagram to Relational • Revised Tables: PERSON (PERSON_ID (pk), FNAME, LNAME) EMPLOYEE (EMPL_PERSON_ID (pk), SEX, DATEOFBIRTH, ANNUAL SALARY, ADDRESS) BENEFICIARY (BEN_PERSON_ID (pk)) DEPENDENT (DEP_PERSON_ID (pk), EMPL_PERSON_ID (pk, fk), DATEOFBIRTH, RELATIONSHIP) EMPLOYEE_BENEFICIARY (EMPL_PERSON_ID (pk, fk), BEN_PERSON_ID (pk, fk), PERCENT)

  11. Steps to Convert from an Class Diagram to Relational • More Tables: PLAN (PLAN_ID (pk), TITLE) CLIENT (CLIENT_ID (pk), ACCOUNT, TITLE, ADDRESS, TELEPHONE, FAX) BENEFIT (BENEFIT_ID (pk), TITLE, PREMIUM_RATE, DESCRIPTION) • Relationships: PLAN: BENEFIT M:N (no intersection class on our OODM) • Revised Tables: PLAN_BENEFIT (PLAN_ID (pk, fk), BENEFIT_ID (pk, fk))

  12. Steps to Convert from an Class Diagram to Relational • Enrolment Bridge Table: ENROLMENT (EMPL_PERSON_ID (pk, fk)), BENEFIT_ID (pk, fk), ENROLMENT_DATE)

  13. Steps to Convert from an OODM to a Rose Model • Final Relationships: CLIENT: EMPLOYEE 1:M PLAN: CLIENT 1:M • Revised Tables: CLIENT (CLIENT_ID (pk), ACCOUNT, TITLE, ADDRESS, TELEPHONE, FAX, PLAN_ID (fk)) EMPLOYEE (EMPL_PERSON_ID (pk), SEX, DATEOFBIRTH, ANNUAL SALARY, ADDRESS, CLIENT_ID (fk))

  14. Object Model vs. Relational Model - Similarities

  15. Object Model vs. Relational Model - Similarities

More Related