Question

The Scenario:

A company is transitioning from a spreadsheet model to a database to track customers and purchases.

The Request:

• You need to normalize the data up to N3.

Requirements and Information:

• Normalize the below spreadsheet:
  • Normalize_Assignment2.csv

Need help as the following:

Upload an excel spreadsheet wit the normalized data

Customer Name	Customer Address	Phone Number(s)	Products Purchased	Distribution Warehouse	Distribution Warehouse Address	Distribution Warehouse Phone Number	Date of Purchase
Matt Smith	11 Kevlar Ln., Grand Rapid, MI 49508	616-610-2718, 616 473-2813	Blu Ray Player, HDMI cable	East Mountain Distribution Center	23 Hangster St. Brooklyn, NY 13867	212202-4321	10/31/18
Keith Jones	66 Buthers Point, Carson City, Nebraska 89403	775-246-3712	U2 Album	West Coast Distribution Center	3245 Lovers Ln, Lexington, KY 21482	414.321.4326	11/1/18
Brian Adams	63 Lovingtin Cir., Newark DE 19701	410-428-2367	Retro Pi, MicroSD Card, Raspberry Pi Case	Tri-State Distribution Center	1397 S. Main Street, Middletown, DE 19709	302-434-5566	10/31/18
Julia Townsend	5 Pennywise Ln., Yardley, PA 19067	972-274-6289	Blu Ray Player	Tri-State Distribution Center	1397 S. Main Street, Middletown, DE 19709	302-434-5566	11/5/18
Mo Rocca	871 Eagle Ct., Newark, NJ 21908	Unknown	Mini NES, DaVinci Code Book	East Mountain Distribution Center	23 Hangster St. Brooklyn, NY 13867	212202-4321	10/31/18
Steven James	4 Autumn Ter., Boulder CO 80301	970-273-1856	White glue, glitter, saline solution, baking soda	West Coast Distribution Center	3245 Lovers Ln, Lexington, KY 21482	414.321.4326	11/7/18
Rick Ford	333 33rd St NY, NY 10027	347-383-8844	Paint Set, Canvas, Paint Brushes	East Mountain Distribution Center	23 Hangster St. Brooklyn, NY 13867	212202-4321	11/5/18
Harrison Wells	12534 Silver Lake Ave., Houston TX 77027	269-226-1599, 267-227-4891	Harry Potter Book Series	West Coast Distribution Center	3245 Lovers Ln, Lexington, KY 21482	414.321.4326	11/3/18
Jen Cusak	8 Washington Ln., Baton Rouge LA 70808	645-378-2889	LOL Surprise Dolls	West Coast Distribution Center	3245 Lovers Ln, Lexington, KY 21482	414.321.4326	11/7/18
Brian Adams	63 Lovingtin Cir., Newark DE 19701	302-338-6279, 410-428-2367	Cat Puzzle	East Mountain Distribution Center	23 Hangster St. Brooklyn, NY 13867	212202-4321	11/3/18
Matt Smith	11 Kevlar Ln., Grand Rapid, MI 49508		Sweater	Tri-State Distribution Center	1397 S. Main Street, Middletown, DE 19709	302-434-5566	11/3/18
Mo Rocca	33 Patriot Dr., Santa Monica, CA 90405	310-268-9921	Knife set	East Mountain Distribution Center	23 Hangster St. Brooklyn, NY 13867	212202-4321	11/1/18
Brian Adams	63 Lovingtin Cir., Newark DE 19701	302-338-6279, 410-428-2367	Paint Brushes	West Coast Distribution Center	3245 Lovers Ln, Lexington, KY 21482	414.321.4326	11/3/18

Expert Answer

An expert answer will be posted here

Answer 1

ORIGINAL DATABASE-

Customer Name

Customer Address

Phone Number(s)

Products Purchased

Distribution Warehouse

Distribution Warehouse

Distribution Warehouse Phone Number

Date of Purchase

To normalize this data base into 3NF-

What is normalization?

It is the process of simplifying the database or breaking the large table into small multiple tables to reduce data redundancy and to remove certain anomalies sucha as update , insert, delete anomaly.

There are 6 Normal Forms of the normalization, But here we have to do Upto 3rd Normal Form.

1. 1st NORMAL FORM-

In first normal form each attribute of the table should be atomic in value that is it should not have multiple values.

2. 2nd NORMAL FORM-

In second normal form it should follow these two rules-

a- It should be in 1st Normal Form.

b- And all the non-key attributes should be fully functional dependent on the primary key.
It should not have Partial Dependency.

3. 3rd NORM FORM-

In third normal form it should follow these two rules-

a- It should be in 2nd Normal Form.

b- And it can not contain any transitive partial dependency.

Therefore i have divided this data into 3 different tables-

Table 1- CUSTOMER DETAILS TABLE

(This table will hold the customer's personal details and the puchase_ID of the puchased products)

Customer_ID

Customer Name

Customer Address

Phone Number(s)

Purchase_ID

Table 2- PURCHASE DETAILS TABLE

(This table will hold the products details that have been purchased by the customer and the product distribution warehouse unique ID and also the date these products were purchased by the customer.)

Purchase_ID

Products Purchased

Distribution Warehouse_ID

Date of Purchase

Table 3- DISTRIBUTION WAREHOUSE TABLE

(This table will hold the details of the product distributiuon warehouse)

Distribution Warehouse_ID

Distribution Warehouse

Distribution Warehouse Address

Distribution Warehouse Phone Number

Here in these tables, The Primay key of each table is in bold and the foriegn keys are underlined.

E-R Diagram-

This is the ER diagram of the database for the 3rd Normal Form representing the relations-

1. This Er model has has been converted into 3NF
2. Here each entity and its attributes are in tabular form
3. Data types of each attribute has been mentioned with it
4. The primary key as PK and foriegn key as FK has been mentioned there.

If any doubt, please feel free to ask and comment, i would like to answer them.
Thank you