SOLVED CS403 Assignment 2 Solution and Discussion

[zareen]

EDU302 Assignment No. 02
SEMESTER Spring 2019
CS403- Database Management System
Total Marks: 20

Due Date: 02/12/2019
Instructions
Please read the following instructions carefully before solving & submitting assignment:
It should be clear that your assignment will not get any credit if:
• The assignment is submitted after due date.
• The submitted assignment does not open or file corrupt.
• The assignment is full or partially copied from (other student or ditto copy from handouts or internet).
• Student ID is not mentioned in the assignment File or name of file is other than student ID.
• The assignment is not submitted in .doc or .docx format.
Uploading instructions
Your submission must include:

• Assignment should be in .doc or .docx format.
• Save your assignment with your ID (e.g. bx020200786.doc).
• Assignment submission through email is NOT acceptable.
Objectives
Objectives
The objective of this assignment is to able the Students:
• To understand the relational database model
• To understand the Conversion of ERD into relational data model
• To understand the Cartesian product of two relations
• To understand the degree and cardinalities of relations

Note:
Your answer must follow the below given specifications.
• Font style: “Times New Roman”
• Font color: “Black”
• Font size: “12”
• Bold for heading only.
• Font in Italic is not allowed at all.
• No formatting or bullets are allowed to use.
• Your answer should be precise and to the point, avoid irrelevant detail.

Lectures Covered: This assignment covers Lecture # 10 - 17
Deadline
Your assignment must be uploaded/submitted on or before 02/12/2019.

Question Statement

Our second assignment is the continuation of the first assignment. In the first assignment an ERD was required for the given scenario, which is given below:

You’re required to perform the following tasks:

1. Convert the above Entity Relationship Diagram into Relational data model.

You have to convert all the components of the ERD into Relational Data model.

2. Consider the following two tables:

Order:

order_id order_status delivery_date btag_no cust_id sman_id
od001 Completed 10/30/2019 bt001 cust001 sm001
od002 Completed 10/30/2019 bt002 cust002 sm001
od003 Completed 10/30/2019 bt003 cust003 sm001

Bill:
bill_id cust_id order_id net_amount
b001 cust001 od001 500
b002 cust002 od002 550
b003 cust001 od003 600

And write the output of the following relational algebra operations:

• ∏ order_id, cust_id, sman_id (Order)
• σ net_amount > 500 (Bill)
• ∏ order_status, delivery_date (σ btag_no= ‘bt002’ (Order))

3. Find the degree and cardinality of the relations (Order and Bill) given in the 2nd question.

====================================Ended=======================================

For any query about the assignment, contact at [email protected]

GOOD LUCK

[zareen]

You have to convert all the components of the ERD into Relational Data model.

Solution:
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)
SalesArea(Area_ID, Area_Name, Sman_ID)

2. Consider the following two tables:

Order:

order_id	order_status	delivery_date	btag_no	cust_id	sman_id
od001	Completed	10/30/2019	bt001	cust001	sm001
od002	Completed	10/30/2019	bt002	cust002	sm001
od003	Completed	10/30/2019	bt003	cust003	sm001

Bill:

bill_id	cust_id	order_id	net_amount
b001	cust001	od001	500
b002	cust002	od002	550
b003	cust001	od003	600

And write the output of the following relational algebra operations:

• ∏ order_id, cust_id, sman_id (Order)

order_id	cust_id	sman_id
od001	cust001	sm001
od002	cust002	sm001
od003	cust003	sm001

• σ net_amount > 500 (Bill)

bill_id	cust_id	order_id	net_amount
b002	cust002	od002	550
b003	cust001	od003	600

• ∏ order_status, delivery_date, (σ btag_no=’bt002’ (Order))

order_status	delivery_date
Completed	10/30/2019

3. Find the degree and cardinality of the relations (Order and Bill) given in the 2nd question.

Table	Degree	Cardinality
Order	6	3
Bill	4	3

[zareen]

Idea Solution from book

[zareen]

[zareen]

[zareen]

[zareen]

You have to convert all the components of the ERD into Relational Data model.

Solution:
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)
SalesArea(Area_ID, Area_Name, Sman_ID)

2. Consider the following two tables:

Order:

order_id	order_status	delivery_date	btag_no	cust_id	sman_id
od001	Completed	10/30/2019	bt001	cust001	sm001
od002	Completed	10/30/2019	bt002	cust002	sm001
od003	Completed	10/30/2019	bt003	cust003	sm001

Bill:

bill_id	cust_id	order_id	net_amount
b001	cust001	od001	500
b002	cust002	od002	550
b003	cust001	od003	600

And write the output of the following relational algebra operations:

• ∏ order_id, cust_id, sman_id (Order)

order_id	cust_id	sman_id
od001	cust001	sm001
od002	cust002	sm001
od003	cust003	sm001

• σ net_amount > 500 (Bill)

bill_id	cust_id	order_id	net_amount
b002	cust002	od002	550
b003	cust001	od003	600

• ∏ order_status, delivery_date, (σ btag_no=’bt002’ (Order))

order_status	delivery_date
Completed	10/30/2019

3. Find the degree and cardinality of the relations (Order and Bill) given in the 2nd question.

Table	Degree	Cardinality
Order	6	3
Bill	4	3

[zareen]

Fall 2019_CS403_2_SOL.docx

Question No 2:
Order:
order_id order_status delivery_date btag_no cust_id sman_id
od001 Completed 10/30/2019 bt001 cust001 sm001
od002 Completed 10/30/2019 bt002 cust002 sm001
od003 Completed 10/30/2019 bt003 cust003 sm001
Bill:
bill_id cust_id order_id net_amount
b001 cust001 od001 500
b002 cust002 od002 550
b003 cust001 od003 600

And write the output of the following relational algebra operations:
• ∏ order_id, cust_id, sman_id (Order)
•
∏ Operator name is projection this is used to select all the colum in the table
It will select three colums, order_id, sman_id and cust_id. It also delete the unwanted colums on the basis of condition
• σ net_amount > 500 (Bill)
σ is used for selection. it select the subset of rows from table answer will be second and third row from bill table answer will be (550,600)
• ∏ order_status, delivery_date (σ btag_no= ‘bt002’ (Order))
In this it will first perform the selection and select the btag no row from order table then projection will be performed and in projection it will selct the colum order-status value and delivery date value where btag no is bt002.
Answer will be order status delivery date
Completed 10/30/2019

Question no 3:

1. Find the degree and cardinality of the relations (Order and Bill) given in the 2nd question.
   (a)
   Degree = number of attributes or colums in the table is his degree
   Order table have 6 colums/attributes so its degree will be 6
   Bill table have 4 colums/attributes so its degree will be 4
   Degree of the (Order and Bill) will be
   Degree -> 6+4=10
   (b)
   Cardinality = numbers of tuples/rows in the table
   Number of rows in order table = 3
   Number of rows in bill table =3
   Cardinality of order and bill (rowsrows)
   Cardinality -> 33 =9