=========================preview======================
(IELM230)exam02f.pdf
Back to IELM230 Login to download
======================================================
IEEM 230 NAME:
Exam I, Fall 2002, Oct 26 SID:
Time: 2 Hours, Max Score: 100

1. ER Models [20]


The Macau Football Betting Co. will open several betting offices in a city. Each office has an address, one or more telephone numbers, and a unique branch number. A customer can place a bet at any of the branches. The payment may be made either by cash, cheque or credit-card. If by cheque, the customers name and ID number are recorded; if by credit-card, the customers name and card-number are recorded. For each bet over $10,000, the customers ID number and name must be recorded, even if the payment is made in cash.
The following data is stored about the matches: Each participating team has a home location, and a unique name. Each match is between two distinct teams, and we record the match location, date, and result. Each team has played zero or more matches.
Each bet has a unique number; we also store the match, the bet-type (indicates which team will win), the odds, the amount of the bet, and the date and branch where the bet was made. The odds offered on the same bet-type of the same match may be different for different bets.

Draw an ER diagram, indicating clearly a key for each entity, the relationships and their cardinality and participation constraints. If you need to make any assumptions to complete the diagram, state them clearly.
2. Mapping ER-models to Relational models [6+6]


Using the informal rules, map the following ER diagrams into schemas. For each schema, mark the primary key, and show the foreign keys.
(a)










(b)










3. Functional Dependencies [6+6+6]



(a) Prove/Disprove: If AB C and AC B, then A B








(b) Prove/Disprove: If AB Y and WY C, then ABW C








(c) Prove/Disprove: If XZ YZ, then X Y

4. Keys [15]



In a schema R( A, B, C, D, E, F), the following FDs are true: { AC DE, BC EF, AF BC}. Find all the candidate keys for the schema.
5. Database Design Problems [2+2+4+4+4+4]


Consider the Supplier-Parts-Projects-Quantity example from the lecture notes. The following schema shows the FDs that are known for this database.



A DB designer uses the following two tables to store the data:
SUPPLIER-PARTS( SupplierNo, SupplierName, PartNo, PartName, Qty)
PROJECT-PART( ProjNo, ProjName, PartNo)

Answer the following questions about the proposed design.

(a) Suggest a primary key for the table SUPPLIER-PARTS







(b) Suggest a primary key for table PROJECT-PARTS







(c) Write some possible values in the following table, to demonstrate possibility of insertion anomalies.



SupplierNo
SupplierName
PartNo
PartName
Qty






























(d) Is it possible for PROJECT-PARTS to have update anom