CS403 Assignment 2 Solution and Discussion


  • Cyberian's Gold

    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:
    262aed16-698e-443d-a680-5959bf8d3808-image.png

    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.

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

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


  • Cyberian's Gold

    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)

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

  • Cyberian's Gold

    Idea Solution from book
    e003ca23-416d-440a-a397-af7c0e8b7c3a-image.png


  • Cyberian's Gold


  • Cyberian's Gold


  • Cyberian's Gold


  • Cyberian's Gold

    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)

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

  • Cyberian's Gold

    Fall 2019_CS403_2_SOL.docx

    30c67624-0f8f-4e9e-a873-7569122d5280-image.png

    d82ce349-489c-4700-b32b-fc0b41809b24-image.png
    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 -> 3
      3 =9


Quiz 100% Result Quiz 100% Result
| |