Question

This is in MySQL

*Construct a diagram that shows the table in 3rd Normal Form using the MySQL Workbench Data Modeler.

*Provide a summary of the steps you took to achieve 3rd Normal form. Include your rationale for new table creation, key selection and grouping of attributes.

Table Details:

-The Anita Wooten Art Gallery wishes to maintain data on their customers, artists and paintings.
-They may have several paintings by each artist in the gallery at one time.
-Paintings may be bought and sold several times.
-In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer.
Note that you only can model table and column names and data types. The data shown is for your reference to determine functional, partial, and transitive dependencies.

ArtistName	ArtistID	PurchaseDate	CustomerName	CustomerAddress	CustomerPhone	PurchasePrice	ArtTitle
Carol Channing	3	Sep 17 2012	Mark Evans	123 This Street Orlando, FL	[phone]	$         7,000.00	Laugh with Teeth
Dennis Frings	15	May 11 2013	Mark Evans	123 This Street Orlando, FL	[phone]	$         1,800.00	South Toward Emerald Sea
Carol Channing	3	Feb 14 2014	Mark Evans	123 This Street Orlando, FL	[phone]	$         5,550.00	At the Movies
Dennis Frings	15	Jul 15 2015	Mark Evans	123 This Street Orlando, FL	[phone]	$         2,200.00	South Toward Emerald Sea

Answer 1

To create the 3NF data model in MYSQL Workbench , let’s first normalize the given dataset. Below are the steps to normalize the given data :

Step 1: Analyse the data:

The given dataset has below attributes:

ArtistName: The name of the artists

ArtistID: Possibly a unique ID to identify the ARtist

PurchaseDate: Purchase Date for an art from a customer as an art can be purchased many times

CustomerName: Name of the customer from whom the art is purchased

CustomerAddress: Address of the customer

CustomerPhone: Phone of the customer

PurchasePrice: Price of the purchase

ArtTitle: Title of the art which is actually purchased

Step2: Normalize to 1NF

A DB table is in 1NF if:

i. It has atomic (single) value for all cells.

ii. It has a primary key in tables.

Considering the CustomerAddress attributes, it is obtained that address includes multiple values i.e. Street address, city, state and zipcode. Let’s break CustomerAddress as below:

CustomerAddress -> StreetAddress, City, State, Zipcode

Consider the CustomerPhone number column, it is a combinate of phone code and phone number. Let’s break it as below:

CustomerPhone -> CustomerPhoneCode, CustomerPhoneNumber

Identifying the primary key:

As description says that an art can have many Purchase records, the primary key will be:

(ArtTitle, CustomerName, PurchaseDate) as an art can be purchases once on a date from a customer.

Final 1NF table is as below:

Art_Purchase(ArtTitle, CustomerName, PurchaseDate, ArtistName, ArtistID, CustomerStreetAddress, CustomerCity, CustomerState, CustomerZipcode, CustomerPhoneCode, CustomerPhoneNumber, PurchasePrice)

Step 3: Normalize to 2NF

A DB table is in 2NF if:

i. Table is in 1NF.

ii. The non key attributes are depending on key attributes that the non key attributes are functionally dependent on key attribute.

Please find below the functional dependencies in the table obtained in step 2:

i. ArtTitle -> ArtistName, ArtistID

ii. CustomerName-> CustomerStreetAddress, CustomerCity, CustomerState, CustomerZipcode, CustomerPhoneCode, CustomerPhoneNumber,

iii. ArtTitle, CustomerName, PurchaseDate -> PurchasePrice

But key identified on table is (ArtTitle, CustomerName, PurchaseDate).

As per rule in 2NF, all non key attributes should be dependent on full key (combination key).

Now the table needs to be decomposed and have foreign keys. To make the foreign keys, let’s add a unique ID to each table to make it a foreign key for other table:

i. Art(ArtID, ArtTitle, ArtistName, ArtistID)

ii. Customer(CustomerID, CustomerName, StreetAddress, City, State, Zipcode, PhoneCode, PhoneNumber) **Customer is removed from all attribute names as table name will indicate the meaning of attribute

iii. ArtPurchase(ArtID, CustomerID, PurchaseDate, PurchasePrice)

Step 4: Normalize to 3NF

A DB table is in 3NF if:

i. Table is in 2NF.

ii. The non key attribute only depends on key attribute. There is no transitive dependency in non key attribute in such a way that a non key attribute depends on another non key attribute which further depends on key attribute.

The tables have below transitive dependencies:

i. Art Table: It has below transitive dependency

ArtID -> ArtistID -> ArtistName. This can be resolved by adding another table for Artist

ii. Customer table: It has below transitive dependency:

CustomerID -> PhoneCode and PhoneCode -> PhoneNumber

iii. ArtPurchase table: There is no transitive dependency in the table

Final 3NF tables are as below:

**Primary key is Bold and foreign key is italic

i. Artist(ArtistID, ArtistName)

ii. Art(ArtID, ArtName, ArtistID)

iii. Customer(CustomerID, CustomerName, StreetAddress, City, State, Zipcode, PhoneNumber

iv. PhoneCode(PhoneNumber, PhoneCode)

v. ArtPurchase(ArtID, CustomerID, PurchaseDate, PurchasePrice)

Step 5: Modelling the database in MYSQL Workbench: