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

324 Week 3 Outline

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

Review on Business Data Modeling:  

  • How do you handle composite attributes? 
  • How do you handle multi-valued attributes?
  • Verb describing relationships and Need to Track Related Records
  • data on actions may be recorded and so to turn action verbs into objects
    • Customer buys products
    • Student reserve books
    • Orders contains products, direct or indirect relationships?
    • Employees balance inventories, direct or indirect relationships?
    • Employee manage accounts
    • Doctors prescribe tests
    • Professors teach students
    • Customers wish-list products
    • Customers rate Products
    • Professors advise Students
    • Patrons checkout Books

Additional Advanced Entities and Relationships: 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 2: Can two or more relationships exist simultaneously? (Chater 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

Additional Modeling Exercises

  1. Create an ERD for the following problem using Oracle Data Modeler: BizbyOrder Books is specialized in ordering books for two types of customers: individuals and businesses in lower Manhattan. This is how these two different customers are different.  When an individual customer orders books, he or she has to pay 20% down payment.  A business customer can establish a credit line with BizbyOrder and pays 50% down if only if the order amount exceeds its credit limit. Business orders often purchase multiple copies of the same title while individual customers do so rarely. Often, when an order containing multiple books is not completely fulfilled, one or more back orders may be created. 
  2. PartDepot wants to develop a system to handle its inventory, products, categories, replacement orders including quantity and discounts, shipments, backorders (when an order is not received completely, a backorder will be created), vendors, bills, and payments. Note that some locations may have multiple branches or stations to take orders. Identify weak entity (or entities), associative entity (entities), and recursive relationship (relationships). Then draw the complete ERD to model the data requirements.
  3. Library System: Create an ERD for a library to keep track of its readers, books, physical book copies, reservations, checkouts, returns, due dates, late fees. Note that different reader categories often come with different borrowing privileges such as types of books to check out and how long to keep the books. Identify weak entity (or entities), associative entity (entities), and recursive relationship (relationships). Then draw the complete ERD to model the data requirements.

Lecture 1: 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

Example 2.  Convert the entity-relationship diagram into a relational model manually. 

Example 3: Transform the following E-R diagram into a relational model. 

Additional Transformation Exercises:

  1. 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 2: 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 3: Convert the ERD involving courses, perquisites, classes, instructors, and students, and enrollment into a relational model.

Additional Conversion Examples

 

Review of Forward Engineering Rules

  • Where do you create foreign keys for 1:m relationships? Are FK values required?
  • Where do you create foreign keys for m:m relationships? Are FK values required?
  • Where do you create foreign keys for 1:1 relationships? Are FK values required?
  • How do you handle weak entities and associative entities?

Reading Assignments: Chapters 3 and 6 of LIU

Writing Assignments:

  • Correctness Questions: online at course.org
  • Hands-on Questions:  Covert the following ERD into the relational model manually on paper and submit a copy of your relational model. 

           Register

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

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