Description

Question 2 Part A)

i)                     Unnormalized form

Customer(customerName,gender,address,dob,creditLimit, artistID, artistName, artistTitle, artType, purchaseDate, purchasePrice)

ii)                  Removing repeating groups to transform to 1NF

Based on the customer purchase history shown on the figure above, there exists repeating groups for the following attributes

(artistID, artistName, artTitle, artType, purchaseDat, purchasePrice)

To normalize the table in UNF to 1NF, the repeating groups are eliminated. This will result to the following tables.

·         Customer (customerName,gender,address,dob,creditLimit )

·         Art (artistID, artistName, artTitle, artType, purchaseDate, purchasePrice)

The tables listed above do not have repeating thus are in 1NF.

iii)                 Eliminating partial dependencies to normalize tables to 2NF.

Taking the tables achieved as a result of normalizing to 1NF in step ii) above, partial dependencies are eliminated to make sure that each of the tables has one candidate key.

•     Customer (customerName,gender,address,dob,creditLimit )

•     Art (artistID, artistName, artTitle, artType, purchaseDate, purchasePrice)

In the art table the artistID is the primary key and identifies all the attributes. However, one artists can have two arts thus to uniquely identify each art a composite candidate key compromising of artistID and artTitle.

In the customer table, two customers can have the same name thus the customerName candidate key is not guaranteed to be unique for all records. Thus, we can introduce a primary key called customerID where each customer will be assigned a unique customerID making it the new candidate key.

Thus, the new tables in 2NF are;

Customer (customerID, customerName, gender, address, dob, creditLimit)

Art (artistID, artTItle , artistName,  artType, purchaseDate, purchasePrice)

iv) Identifying and eliminating transitive dependencies.

Taking the relations achieved in step iv) above eliminating transitive dependencies will mean that each table will have primary key on which all the other non-key attributes depend on. The customer table does not have any transitive dependencies as the customerID is the only key attribute. Thus, this can be translated as;

customerID  customerName, gender, address, dob, creditLimit)

The art table has the following dependencies

Art (artistID, artTItle , artistName,  artType, purchaseDate, purchasePrice)

artistID, artTitle  artistName, artType, purchaseDate, purchasePrice

artistID  artistName

artTitle  artType, purchaseDate, purchasePrice

Thus, eliminating the transitive dependencies will result to the following tables;

Artist (artistID, artistName)

Art (artTitle, artType, purchaseDate, purchasePrice)

Because two arts can have the same name, we can introduce a unique key that is unique for each art.

Art (artID, artTitle, artType, purchaseDate, purchasePrice

Thus, final set of tables in 3NF are.

Artist (artistID, artistName)

Customer (customerID, customerName, gender, address, dob, creditLimit)

Art (artID, artTitle, artType, purchaseDate, purchasePrice)