We are here with you hands in hands to facilitate your learning & don't appreciate the idea of copying or replicating solutions. Read More>>

Looking For Something at vustudents.ning.com? Click Here to Search

www.bit.ly/vucodes

+ Link For Assignments, GDBs & Online Quizzes Solution

www.bit.ly/papersvu

+ Link For Past Papers, Solved MCQs, Short Notes & More


Dear Students! Share your Assignments / GDBs / Quizzes files as you receive in your LMS, So it can be discussed/solved timely. Add Discussion

How to Add New Discussion in Study Group ? Step By Step Guide Click Here.

CS403 Assignment No 03 Fall 2019 Solution & Discussion Due Date: 20-01-2020

CS403 Database Management System Assignment 3 Solution & Discussion Fall 2019


Assignment No. 03
SEMESTER Fall 2019

CS403- Database Management System

 Total Marks: 20

Due Date: January 20, 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_IDOrder_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.

 

  1. 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.

 

  1. 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.

 

  1. After creation of all tables, you are required to add the following records into the OrderCustomer 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

cust001@domain.com

cust002

Customer 02

M.A Jinnah Campus, Defence Road,Off Raiwind Road, Lahore

3001234567

cust002@domain.com

cust003

Customer 03

M.A Jinnah Campus, Defence Road,Off Raiwind Road, Lahore

3001234567

cust003@domain.com

 

                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

        

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

 

  1. 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:

  1. You have to provide the snapshot of each task; otherwise your assignment will be graded zero.
  2. Only SQL Queries are allowed to perform the given tasks. Wizard usage is not allowed.
  3. Every Query and its execution snapshots are required in each task.
  4. 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.

The SQL Query window will open and you can write different SQL queries in the highlighted area.

Sample output:

For example, a select query is applied on the Orders table to retrieve all the records of the table, then:

Query: SELECT * FROM orders;

The execution snapshot of the query is given below:

+ How to Follow the New Added Discussions at Your Mail Address?

+ How to Join Subject Study Groups & Get Helping Material?

+ How to become Top Reputation, Angels, Intellectual, Featured Members & Moderators?

+ VU Students Reserves The Right to Delete Your Profile, If?


See Your Saved Posts Timeline

Views: 1501

.

+ http://bit.ly/vucodes (Link for Assignments, GDBs & Online Quizzes Solution)

+ http://bit.ly/papersvu (Link for Past Papers, Solved MCQs, Short Notes & More)

+ Click Here to Search (Looking For something at vustudents.ning.com?)

+ Click Here To Join (Our facebook study Group)

Replies to This Discussion

Please Discuss here about this assignment.Thanks

Our main purpose here discussion not just Solution

Students having same subject can start discussion here to solve assignment, GDB & Quiz and can clear their concepts until solution is provided. 

 

P.S:    Please always try to add the discussion in proper format title like “CS101 Assignment / GDB No 01 Solution & Discussion Due Date: ___________”

Then copy Questions from assignment file and paste in Discussion.

 

http://bit.ly/vucodes (For Assignments, GDBs & Online Quizzes Solution)

http://bit.ly/papersvu (For Past Papers, Solved MCQs, Short Notes & More)

Click Here to Search (Looking For something at vustudents.ning.com?)

Click Here to Join (Our facebook study Group)

CS403 Solution Assignment No 03 Fall 2019

below link 

CS403_Assignmnet_03_Solution_Fall_2019_vustudents.ning.com.pdf

How to install SQL SERVer

2nd part share soon

------- Subscribe my Youtube Channel for more Articles and tutorails
--- Learning School
---- Mani Siddiqui

Create Database BS140201235 --BS142123131313 ----------------- mani ke jaga ap k jis name sa database create krna us k name likhan
use BS140201235 -- 1st task complete

--table 1 Select * from Ordertable
-- jo feild miss ho agr tu ap wo feild call krwa laina its jxt given u idea ok

Create Table Ordertable (orderID Varchar(50),Orderstatus varchar(20),DeliveryDate Date,Manager varchar(50),CustomerID Varchar(50))
insert into Ordertable (orderId,Orderstatus,DeliveryDate,Manager,CustomerID )Values('ord0001','Pending','2020-01-01','mang001','Cus001')
insert into Ordertable (orderId,Orderstatus,DeliveryDate,Manager,CustomerID )Values('ord0004','Pending','2020-01-01','mang001','Cus002')
insert into Ordertable (orderId,Orderstatus,DeliveryDate,Manager,CustomerID )Values('ord0002','Complete','2020-01-10','mang002','Cus002')
insert into Ordertable (orderId,Orderstatus,DeliveryDate,Manager,CustomerID )Values('ord0003','Complete','2020-01-14','mang002','Cus003')

--table 2 Select * from Customer
Create Table Customer(customer_Id varchar(50),CusName Varchar(50),Contact Varchar(50),Email varchar(50))
insert into Customer(customer_Id ,CusName,Contact ,Email)values('Cus001','Adil Shah','0321-1234567','abc@gmail.com')
insert into Customer(customer_Id ,CusName,Contact ,Email)values('Cus002','AmirShah','0333-1234567','ced@gmail.com')
insert into Customer(customer_Id ,CusName,Contact ,Email)values('Cus002','AmirShah','0333-1234567','ced@gmail.com')

--table 3 Select * from Bill


Create Table Bill (BillId Varchar(50),OrderID varchar(50),BillDate date,Amount int)
insert into bill(BillId,OrderID,BillDate,Amount )values('Bill001','ord0001','2020-01-1','50000')
insert into bill(BillId,OrderID,BillDate,Amount )values('Bill001','ord0002','2020-01-14','550000')
insert into bill(BillId,OrderID,BillDate,Amount )values('Bill001','ord0003','2020-01-14','100000')
insert into bill(BillId,OrderID,BillDate,Amount )values('Bill001','ord0003','2020-01-14','100000')


--- ap 2nd task b complete ho gya 3no table create b ho gya and data b insert ho gya
ab ap na 3no j join krwana hai us k lai 3no table ma sa jo common feild hai wo pick kra 1st table customer id or 2nd table k custmr id same same hai in k apas ma join lga ga then
3rd table order id and 1st table k order id common hai in k aps ma join krnga

lets see

----------- 3 tables join
Select ordertable.orderID OID,ordertable.Orderstatus Status,Ordertable.DeliveryDate 'Date',Customer.CusName Name,Bill.Amount from Ordertable

-- its join
inner join customer on customer.customer_Id=ordertable.CustomerID
inner join bill on bill.OrderID=Ordertable.orderId
where ordertable.Orderstatus='Pending'


--- hope its helpful for you
duao ma yad rkhna
ALLAH hafiz

CS403 Assignment 3 Solution 2020 part 2 (Database Management System)

CS403 Assignment 3 Solution 2020 (Database Management System part 1)

CS403 Solution Assignment No 03 Fall 2019

below link 

CS403_Assignmnet_03_Solution_Fall_2019_vustudents.ning.com.pdf

RSS

Today Top Members 

© 2020   Created by +M.Tariq Malik.   Powered by

Promote Us  |  Report an Issue  |  Privacy Policy  |  Terms of Service

.