=========================preview======================
(IELM230)exam02fm.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)


E( e1, e2, e3, e2), FK(e2) refers E(e2)
R( e2, a1, a2), FK( e2) refers E(e2)

(b)


Employee( Name, IDNo, PhoneNo)
Course( C-No, Title)
TRAINS( IDno1, IDno2, C-no, Date)
FK( IDno1) refers Employee( IDNo), FK( IDno1) refers Employee( IDNo)
FK( C-no) refers Course( C-no)

3. Functional Dependencies [6+6+6]



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

FALSE:
Let A = State, B = LicenseNo, and C = VehicleID from our CAR schema example. A -/-> B, though AB C and AC B are both true.

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

AB Y [given]
ABW WY [augmentation]
WY C [given]
ABW C [transitive]

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

FALSE.
Let X = State, Z = VehicleID, Y = LicenseNo in our CAR schema. XZ YZ, but 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.



D and E are not part of any Key;
A must be in every key.
A alone cannot functionally determine R.
Therefore each key is of the form {A} union some subset of {B, C, F}
AB cannot determine C.
AC cannot determine B.
AF AF, AF BC, AF AFBC; AC DE, so AF AFBC DE. AF is a candidate key
ABC ABC; AC DE, ABC ABCDE; BC F; so ABC ABCDEF. ABC is candidate key.
ABF not minimal
ACF not