CS 403 Assignment No 3 Solution Fall 2019

In this post I am sharing link of CS 403 Assignment No 3 Solution Fall 2019 of Virtual University (VU). Keep visiting StudySolution for updated assignment solutions.

Assignment No. 03
SEMESTER Fall 2019

CS403- Database Management System

Total Marks: 20

Due Date:January 20, 2020

Instructions

Please read the following instructions carefully before solving & submitting the assignment:

It should be clear that your assignment will not get any credit if:

The assignment is submitted after the due date.
The submitted assignment does not open or file corrupt.
The assignment is full or partially copied. (from other student or ditto copy of the handouts or internet).
The assignment is not submitted in .doc or .docx

Uploading instructions

Assignment should be in .doc or .docx
Save your assignment with your ID (e.g. BC123456789.doc).
Assignment submission through email is NOT

Objectives

The objective of this assignment is to able the Students:

To understand the conversion of relational data model into physical database
To implement the physical database by using the SQL Sever
To understand and execute different SQL queries

Lectures Covered: Lecture # 26 – 31

Deadline

Your assignment must be uploaded/submitted on or before January 20, 2020 (20/01/2020).

Question Statement

Our third assignment is the continuation of the second assignment. In the second assignment, we convert the ERD into a relational data model. The relational data model is given below:

Customer(Customer_ID, Name, Address, Contact_No, email)

Order(Order_ID, Order_Status, Delivery_Date, Customer_ID, Manager_ID,Sman_ID)

Bill(Bill_ID, Order_ID, Amount, date)

Bottle(Bottle_Tag_No, Fillling_Date, Expiry_Date, Capacity, Order_ID)

Manager(Manager_ID, Name, Address)

Salesman(Sman_ID, Name, Contact_No)

You’re required to perform the following tasks according to the above relational data model.

Convert the above relational data model into a physical database. For which you have to first create a database. So, write a SQL statement which creates a database. The database name should be as your student id. For example, if your student id is BC123456789, then the database name should also be BC123456789.

*If database name is different from your student id, then your assignment will be graded as zero.

After the database creation, you are required to write the SQL queries in order to create all tables in the database. You also have to apply different constraints like primary key constraint and foreign key constraints where applicable.

After creation of all tables, you are required to add the following records into the Order, Customer and Bill tables by writing the appropriate SQL Queries.

Order Table:

Order_ID	Order_Status	Delivery_Date	Manager_ID	Customer_ID	Sman_ID
od001	Completed	01/01/2020	mg001	cust001	sm001
od002	Completed	05/01/2020	mg002	cust002	sm001
od003	Pending	null	mg003	cust001	sm001

Customer Table:

Customer_ID	Name	Address	Contact_no	email
cust001	Customer 01	M.A Jinnah Campus, Defence Road,Off Raiwind Road, Lahore	3001234567	[email protected]
cust002	Customer 02	M.A Jinnah Campus, Defence Road,Off Raiwind Road, Lahore	3001234567	[email protected]
cust003	Customer 03	M.A Jinnah Campus, Defence Road,Off Raiwind Road, Lahore	3001234567	[email protected]

Bill Table:

Bill_ID	Order_ID	date	Amount
b001	od001	01/01/2020	500
b002	od002	05/01/2020	550
b003	od003	09/01/2020	500

Write a query that retrieves all records from the Order table where Order_Status is “Pending”.

Finally retrive the “Completed Orders” in a inner joined table such as:

Order_ID as OID from order table
Order_Status as Status from order table
Delivery_Date as Date from order table
Customer_name as Name from customer table
Amount as Bill Amount from Bill table

The expected outcome of the query is given bellow:

OID	Status	Name	Date	Bill Amount
od001	compeleted	Customer 01	01/01/2020	500
od002	compeleted	Customer 02	05/01/2020	550

Note:

You have to provide the snapshot of each task; otherwise your assignment will be graded zero.
Only SQL Queries are allowed to perform the given tasks. Wizard usage is not allowed.
Every Query and its execution snapshots are required in each task.
Only SQL Server and SQL Management Studio are allowed to use for the solution of assignment.

Solution Guidelines:

First open the SQL Management Studio and connect to your SQL Server.

Then in the Object Explorer, right click and select the New Query in order to write different SQL Queries.