=========================preview======================
(IELM230)asgt3.pdf
Back to IELM230 Login to download
======================================================
IELM 230 Industrial Data Systems, Fall 2005
Assignment 3. Max score: 20. Due Date: Oct 26, 2005

Q1. DB Design [1+1+3]

Suppose we are storing data for customer accounts, with the following information:

Customer data: CustomerID, Name, Address
Account data: AccountNumber, Type, Amount

Each customer has a unique customerID; a customer may have one or more accounts, and each account has a unique AccountNumber.

It is known that CustomerID {Name, Address}, and AccountNumber {Type, Amount, CustomerID}


Consider the following two designs to store this data:
Alternative 1:
ACCOUNT(AccountNumber, Type, Amount, CustomerID) AND
customer(CustomerID, Name, Address)

Alternative 2:
CUSTOMER_ACCOUNTS(CustomerID, Name, Address, AccountNumber, Type, Amount)


(a) Suggest a key for Alternative 2 (prove it is a key).

(b) Which alternative is better?

(c) Give examples of three different types of anomalies that can occur in the bad design (you must construct example tuples and point out the problem).


Q1. FDs and Normalization [3+3+3+3+3]
(a) Prove/Disprove the following statements abouf FDs:
(i) If AB C and AC B, then A B
(ii) If P Q and AQ R, then AP R

(b)In a schema R( A, B, C, D, E, F), the following FDs are true: { AC DE, BC EF, AF BC}.

(i) Find all the possible keys for the schema.
(ii) Select one key from part (i) and state whether the schema R is in 3NF using this key.
(iii) Is R in general 3NF ?