Question

Normalize the following table:

ProductId         

ProductName   

ProductPrice    

OrderId

OrderDate       

ShippingDate   

CustomerID      

CustomerFirstName           

CustomerLastName

Once normalized, insert enough data into the tables.

Use foreign keys to link the tables accordingly.

Use Excel to show the steps in you how you created the three normal forms.

Create the resulted tables into MySQL with the data you selected

Add the functionality of JOIN and build a FULLTEXT search to this normalized database.

Use PHP and MySQL to add a new record to each table in this database and display them in a website.

Build a small login system that will access some data from this database and show the retrieved information in an HTML document.

Create a signup form registration with a secured PHP login system on a website.

Answer 1

Normalization :

Normalization helps to decompose large and complex table into simple and smaller form.
After normalization redundancy will be removed and data consistency will maintained.

Example :Consider schema given in the question

First Normal Form (1NF) :

1NF says all the columns in the table should be automic in nature.
No duplicate columns and repeating group of columns are allowed in 1NF.

Given table is in the 1NF because all columns are automic in nature and no duplicate and multivalued columns exists.

Second Normal Form (2NF) :

2NF says table should be in the 1NF.
In 2NF partial dependency is not allowed.
Partial dependency means non key column should depends upon primary key column.

Here above schema needs to normalize into 2NF to remove partial dependency.Need to identify new tables like

Customer :This table stores customer details.
Product:This table stores product details
Order :This table stores order details.

Below are tables in 2NF.

1.Table Name :Customer

Description :This table stores customer details like CustomerID ,CustomerFirstName and CustomerLastName.

Schema :Customer (CustomerID ,CustomerFirstName ,CustomerLastName)

FD:CustomerID ==> CustomerFirstName and CustomerLastName

2.Table Name :Product

Description :This table stores product details like ProductID ,ProductName and ProductPrice.

Schema :Product(ProductID ,ProductName ,ProductPrice)

FD:ProductID ==>ProductName ,ProductPrice

3.Table Name :Order

Description :This table stores order details like OrderId,OrderDate and ShippingDate .

Schema :Order(OrderId,OrderDate ,ShippingDate)

FD:OrderId ==>OrderDate ,ShippingDate

Third Normal Form (3NF):

3NF says table should be in the 2NF.
In 3NF transitive dependency is not allowed.
Transitive dependency means non key column in the table should depends on non key column in the table.

Here above table needs to normalize into 3NF to remove transitive dependency.Below are tables in 3NF.

1.Table Name :Customer

Description :This table stores customer details like CustomerID ,CustomerFirstName and CustomerLastName.

Schema :Customer (CustomerID ,CustomerFirstName ,CustomerLastName)

FD:CustomerID ==> CustomerFirstName and CustomerLastName

2.Table Name :Product

Description :This table stores product details like ProductID ,ProductName and ProductPrice.

Schema :Product(ProductID ,ProductName ,ProductPrice)

FD:ProductID ==>ProductName ,ProductPrice

3.Table Name :Order

Description :This table stores order details like OrderId,OrderDate and ShippingDate , along with customer details.

Schema :Order(OrderId,OrderDate ,ShippingDate,CustomerID)

FD:OrderId,CustomerID ==>OrderDate ,ShippingDate

4.Table Name :OrderDetails

Description :This table stores order details like OrderId and ProductID.

NOTE :This table is formed because an order can have any number of product and orderID is primary key in order table so which can not be duplicate in order table but as orderID is foreign key in OrderDetails which can be duplicate.

Schema :OrderDetails(OrderId,ProductID)

***********************************

Tables in MYSQL :

1.Table Name :Customer

create TABLE customer(
CustomerID int primary key,
CustomerFirstName varchar(20) not null,
CustomerLastName varchar(20) not null
);

2.Table Name :Product

create TABLE product(
ProductID int primary key,
ProductName varchar(20) not null,
ProductPrice decimal(6,2) not null
);

3.Table Name :Order

create TABLE `order`(
OrderID int primary key,
OrderDate date not null,
ShippingDate date not null,
customerID int not null,
foreign key (customerID) REFERENCES customer (customerID)
);

4.Table Name :OrderDetails

create TABLE orderDetails(
OrderID int,
ProductID int not null,
primary key (OrderID,ProductID),
foreign key (OrderID) REFERENCES `order` (OrderID),
foreign key (ProductID) REFERENCES product (ProductID)
);