=========================preview======================
(IELM230)asgt2m.pdf
Back to IELM230 Login to download
======================================================
IELM 230 Industrial Data Systems, Fall 2005
Assignment 2. Model

Q1. ER diagrams [2+2+3=7 pts]
Figure 1 below shows a ternary relationship (SUPPLY) between SUPPLIER, PART and PROJECT entities. Figure 2 shows an incomplete ER diagram to represent the same information using binary relationships.
Assume that suppliers may have supplied multiple parts, each project uses several parts, and some parts may be used in different projects.



Figure 1. Ternary relationship SUPPLY


Figure 2. Binary relationships

(a) What needs to be added to Figure 2 to make it complete?

(i) A primary key of TheSupply, (ii) cardinality ratios of relationships (iii) participation constraints. (The primary key can be considered as a supply contractID.)

(b) Show the cardinality constraints on the binary relationships.




(c) Show that the two ER diagrams are equivalent [Hint: to do so, you must show that each situation that can be represented by one diagram can also be represented by the other.]

. For each tuple <v1,v2,v3,v4> in the original ternary relationship SUPPLY (ProjName, SName, PartNo, Quantity), assign a unique ContractID v5 to it and add 4 tuples into the new diagram:

i. <v1, v5> into SupplyProj (ProjectName, ContractID)

ii. <v2, v5> into Supplies (SName, ContractID)

iii. <v3, v5> into SupplyPart (PartNo, ContractID)

iv. <v4, v5> into TheSupply (Quantity, ContractID)


In this way, the original diagram can be represented by the second diagram.


. For each tuple <v4, v5> in the relationship TheSupply, there must exist a tuple <v1, v5> in the relationship SupplyProj (ProjectName, ContractID) because of its 1: N cardinality ratio and total participation constraint. Similarly, there exist <v2, v5> and <v3, v5> in the Supplies and SupplyPart relationships. Thus, adding their combination <v1, v2, v3, v4> into the original relationship SUPPLY can represent the second diagram.



Q2. Informal design [8 pts]
Use the model solution for Assignment 1, with the ER diagram for facilities booking. Using the informal guidelines given in the lecture notes, convert this ER diagram into DB schema designs. Mark the primary key and foreign keys for each schema.

Regular entities:
User (user_id, name, user_type, NumBooked, NumUnused),
SportsFacilities (court_no, name, location, restriction),
TimeSlot (day, time),
Binary relation:
OpenIn (court_no, day, time);
FK: court_no SportsFacilities (court_no), day, time TimeSlot (day, time)
Ternary relation:
Book (user_id, court_no, day, time, UsedOrNot)
FK: user_id User (user_id); court_no SportsFacilities (court_no);
day, time TimeSlot (day, time)

Acknowledgements: Model prepared by Mabel Xu Jing