=========================preview======================
(IELM230)asgt3m.pdf
Back to IELM230 Login to download
======================================================
IELM 230 Industrial Data Systems, Fall 2005
Assignment 3. MODEL

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).

AccountNumber {Type, Amount, CustomerID} {customerID} { Name, Address}.
Therefore AccountNumber is a superkey; it is minimal; therefore AccountNumber is a key.


(b) Which alternative is better?

Alternative 1 is better. In alternative 2, we have a transitive FD (e.g. AccountNumber CustomerID Name) in which the non-prime attribute Name is transitively dependent on the primary key. Hence the schema is not in 3NF.

(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).

Insertion anomaly:

AcctNo
CustID
Type
Amount
Name
Address

123
abc
saving
100
Jack
RoadSide

124
abc
check
150
Jacky
RoadSide




The system allows entry of the second row, even though it violates the FD {CustID} Name.

Update Anomaly

AcctNo
CustID
Type
Amount
Name
Address

123
abc
saving
100
Jack
RoadSide

124
abc
check
150
Jack
RoadSide StreetSide




Jacks address was changed from RoadSide StreetSide in row 2; This is allowed by the design, but violates the FD {CustID} Address

Deletion anomaly

AcctNo
CustID
Type
Amount
Name
Address

123
abc
saving
100
Jack
RoadSide

124
pqr
saving
150
Jimmy
BigHouse




Suppose Jack closed AcctNo 123; that record (first row) is deleted from the table; however, if Jack only had one account, then we also lose Jacks name and Address. However, the bank may have wanted to keep the name and Address of past customers (possibly to send them promotional materials).


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

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.


(ii) If P Q and AQ R, then AP R

TRUE.
P Q (1) [given]
AP AQ (2) [augmentation of (1) by A]
AQ R (3) [given]
AP R QED [(2) and (3) and transitive law]