database project

Un-normalized Form:

· S_Order_T(Order_ID,Order_Date,Customer_ID,Customer_Name,Customer_Address, Customer_City,Customer_State,Postal_Code,Product_ID,Product_Description, Produt_Finish,Standard_Price,Order_Quantity)

1NF:

Remove Repeating Groups. The customer details will be the same for each Product ordered. They are separated from the Order details relation.

· S_Order_T(Order_ID,Order_Date,Customer_ID,Customer_Name,Customer_Address, Customer_City,Customer_State,Postal_Code)

· S_Order_Line_T(Order_ID,Product_ID,Product_Description,Produt_Finish,Standard_Price,Order_Quantity)

Second Normal Form

Remove attributes only partially dependent on primary key. The Product_Description, Produt_Finish,Standard_Price are depends only on Product_ID (not on Order_ID) and so that are removed to new relation.

· S_Order_T(Order_ID,Order_Date,Customer_ID,Customer_Name,Customer_Address, Customer_City,Customer_State,Postal_Code)

· S_Order_Line_T(Order_ID,Product_ID,Order_Quantity)

· S_Product_T(Product_ID,Product_Description,Produt_Finish,Standard_Price)

Third Normal Form:

Remove attributes dependent on a non-key other than the primary. The customer details depend only on the Customer_ID and so are removed.

· S_Customer_T(Customer_ID,Customer_Name,Customer_Address,Customer_City,Customer_State,Postal_Code)

· S_Product_T(Product_ID,Product_Description,Produt_Finish,Standard_Price)

· S_Order_T(Order_ID,Order_Date,Customer_ID)

· S_Order_Line_T(Order_ID,Product_ID,Order_Quantity)

Question Two

2.5 Marks

Learning Outcome(s):

LO 3

a. Write the SQL commands to create each table after normalization.

b. Write insert command to add 5 rows in each of the table.

c. Write a delete command to delete row from each table.

d. Write the commands to query following values.

1. Show Product id and finish date for all Products

2. Show Product id and finish for all Products, sort by Product_Line_ID by ascending order

3. Show Product id and finish for all Products, sort by Product_Line_ID by descending order

4. Show each Product_Line_ID and the total number of Products for the Product_Line_ID

Answer

a-

Create Table S_Customer_T(

Customer_Id int PRIMARY KEY NOT NULL,

Customer_Name VARCHAR(40),

Customer_Address VARCHAR(40) ,

Customer_City VARCHAR(40),

Customer_State VARCHAR(2) ,

Postal_Code VARCHAR(5) );

Create Table S_Product_T (

Product_Id INTEGER PRIMARY KEY NOT NULL,

Product_Description VARCHAR(70) ,

Product_Finish Date ,

Standard_Price Decimal(6,2)

);

Create Table S_Order_T (

Order_Id INTEGER PRIMARY KEY NOT NULL,

Order_Date DATE ,

Customer_Id Integer REFERENCES S_Customer_T(Customer_Id)

);

Create Table S_Order_Line_T (

Order_Id int NOT NULL REFERENCES S_Order_T(Order_Id),

Product_Id int NOT NULL REFERENCES S_Product_T(Product_Id),

Ordered_Quantity int,

PRIMARY KEY (Order_Id, Product_Id));

b-

INSERT  INTO  S_Customer_T  VALUES  (1,'Ali', 'Exit11', 'Riyadh', 'RH', '12345');

INSERT INTO S_Customer_T  VALUES  (2, 'omar', 'exit12', 'Riyadh', 'RH', '12345');

INSERT INTO S_Customer_T  VALUES  (3, 'fahad', 'exit13', 'Riyadh', 'RH', '12345');

INSERT INTO S_Customer_T  VALUES  (4, 'saleh', 'exit14', 'Riyadh', 'RH', '12345');

INSERT INTO S_Customer_T  VALUES  (5, 'ahmad', 'exit15', 'Riyadh', 'RH', '12345');

INSERT INTO S_Product_T   VALUES  (11, 'yogurt', '2021/4/20',10);
INSERT INTO S_Product_T   VALUES (22,'Milk','2021/4/15',10);
INSERT INTO S_Product_T   VALUES(33,'ketchup', '2021/4/15',30);
INSERT INTO S_Product_T   VALUES(44,'jam', '2021/04/15',15);
INSERT INTO S_Product_T   VALUES(55,'juice', '2020/04/15', 30);
INSERT INTO S_Order_T   VALUES  (101,'2021/03/29', 1);
INSERT INTO  S_Order_T  VALUES  (102, '2021/03/29', 2);
INSERT INTO S_Order_T  VALUES  (103, '2021/03/31', 3);
INSERT INTO S_Order_T  VALUES  (104, '2021/04/01', 4);
INSERT INTO S_Order_T  VALUES  (105, '2021/04/01', 5);
INSERT INTO S_Order_Line_T  vALUES  (101, 1, 2);
INSERT INTO S_Order_Line_T  VALUES  (102, 4, 5);
INSERT INTO  S_Order_Line_T  VALUES  (103, 3, 3);
INSERT INTO  S_Order_Line_T  VALUES  (104, 5, 2);
INSERT INTO  S_Order_Line_T  VALUES  (105, 5, 2);

c-
Delete from S_Order_Line_T
Where Order_ID=101
And Product_ID=1;

Delete from S_Order_T
Where Order_ID=101;

Delete from S_Product_T
where Product_ID=44;

Delete from S_Customer_T
Where Customer_ID=1;

d-

1-
Select Product_ID, Product_finish 
From  S_Product_T

2-
Select  Product_ID, Product_Finish 
From S_Product_T 
Order By Product_ID  ASC

3-
Select Product_ID, Product_Finish 
From s_Product_T 
Order BY Product_ID  DESC

4-
Select Order_ID, Count(Product_ID) 
From S_Order_Line_T 
Group By Order_ID