1 / 8

Database Design Principles – Lecture 6

Database Design Principles – Lecture 6. Moving to a Logical Model. Lecture Objectives. How to convert from the conceptual model to the logical model Refining the key structure of an entity Refining relationships between entities based on Business Rules. Converting Entities.

janna
Télécharger la présentation

Database Design Principles – Lecture 6

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 Principles– Lecture 6 Moving to a Logical Model

  2. Lecture Objectives • How to convert from the conceptual model to the logical model • Refining the key structure of an entity • Refining relationships between entities based on Business Rules

  3. Converting Entities • Add attributes to each entity • Verify PK defined in conceptual model – is it appropriate for table or does another unique (generic) PK need to be defined • Remove derived values

  4. Converting Relationships • For 1:M connectivity, take the PK of the ‘1’ table and make it an FK in the ‘M’ table • For 1:1 connectivity, take the PK of the ‘main’ table and make it an ‘FK’ in the destination table • For M:N connectivity, create a bridge table • Include the PK from each of the original tables as composite PK in the bridge table. These will also be FKs in the bridge table • Add additional attributes to the bridge table as required • For Strong – Weak Entity relationships, take the PK of the Strong Entity and include it as part of the PK of the Weak Entity. It will also be an FK in the Weak Entity

  5. Example Conversion • Given the following ERD, convert to a logical model using the techniques provided

  6. Example Conversion Note relationship appropriate now between librarian and borrow transactions • Add attributes • Refine PK’s (this may involve creating a new, more meaningful PK) • Remove derived values (none in this example) • All relationships revised (1:1, 1:M, M:N) Note new entity created to handle M:N relationship between book and customer

  7. In Summary • A conceptual model can have a few attributes or many attributes depending on the level of detail provided in a narrative • Most often, we will add as many attributes as are known to the initial conceptual model to get it to an adequate level of detail (or as close to logical as possible)

  8. Example Logical Design SAS (Support A Student) Window Cleaning wants to keep track of its students and the jobs they are assigned. For an employee information such as name, address, email and phone number are required. Students are grouped into job categories as their salary is calculated by the category they are in. The category includes code, description and hourly rate. A student can on have one job category at a time but each job category can have many students. All jobs are tracked by an id number, a start date, an end date and a cost. More than one employee will work on a job and an employee will have many jobs assigned to them.

More Related