多媒体测试系统
 
STUDENT
 
FACULTY
 
SCHOOL
 
SUPPORT
 
PUBLIC
 
SIGNUP
DAILY QUIZ
 
     
  B U L L E T I N    B O A R D

641 Week 3 Outline

(Subject: Database Management/Authored by: Liping Liu on 9/6/2024 4:00:00 AM)/Views: 1948
Blog    News    Post   

Review: Course and Sections (why splits), Course and Prerequisites (Two entities or one?), Grade (where to store it), minimum grade required for prerequisites

Lecture: What about entities that are different but similar? (Chapter 3 of LIU)

Super and sub type entities: How to handle different types of entities that have many attributes in common, e.g., part-time and full-time students and instructors? patient or visit as super type for inpatient and outpatient? Customer or Transaction as super type for Buy and Sell

Lecture: Can two or more relationships exist simultaneously? (Chapter 3 of LIU)

Exclusive Relationships: two or more relationships that cannot hold simultaneously. Examples: Offices for part-timers and full-timers, claims made by patients and clinics

Lecture: Forward Engineering: ERD to Relational Model: (Remember these rules, Chapter 6 of LIU)

  • Each entity set is converted into a table with its attributes as the columns of the table
  • Depending on the mapping cardinality, you modify the tables to make them related
    • If the relationship is 1:m or m:1, you put the primary key of the table for one-side entity set into the table for the many-side entity set
    • If the relationship is m:m, you build a junction table which has the primary keys of both entity sets as columns
    • If the relationship is 1:1, you can put the primary key of one entity set into the table for another entity set.
  • Foreign keys are optional if and only the receiving side is optional. Foreign keys in a junction table is always required.

Example 1: Convert the ERD for Customer – Order – Product problem into the relational model

Additional Transformation Exercises:

  1. Transform the following E-R diagram into a relational model. Then create the database using Oracle.

  2. The following relational model was converted from an ERD that has been lost (Bold fields indicate the primary key). Please recover the ERD based on all the information given:

            PartPartNo, Description, Quantity, UnitPrice

                      BinBINNO, Capacity, Location, PartNo

                      Department: Name, Phone, Contact, Account, Balance

                      Dept_PartDepartment, Part

                      SupplierName, Address, City, State, Zip, Phone

                      SPL_PartFK_Name, FK_Partno

Lecture: Convert ERD into relational models: conversion rules on Weak entities, gerunds, exclusive relationships, recursive relationships, and super/sub type entities (Chapter 6 of Liu)

Example 2:  Convert the ERD involving courses, perquisites, classes, instructors, and students, and enrollment into a relational model.

Additional Conversion Examples: Convert the following ERD into a relational model:  

 

Reading Assignments: Chapters 3 and 6 of LIU

Writing Assignments:

  • Correctness Questions: online at course.org
  • Hands-on Questions:  
  • Convert the following ERD into relational model manually on paper:  


           Register

Blog    News    Post
 
     
 
Blog Posts    News Digest    Contact Us    About Developer    Privacy Policy

©1997-2024 ecourse.org. All rights reserved.