Scribd Logo
Upload

Welcome to Scribd!

  • 22 views

    CS614-Assignment 1 Solution Spring 2024

    Uploaded by

    khalid Bhatti

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    CS614-Assignment 1 Solution Spring 2024

    Uploaded by

    khalid Bhatti
    22 views4 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    22 views4 pages

    CS614-Assignment 1 Solution Spring 2024

    Uploaded by

    khalid Bhatti

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 4

    Total marks = 20

    Bc210425789
    Khalid Lateef
    CS614 Assignment No.1
    Solution
    .

    Table 1: StaffBranch given below reflects a specific form of normalization.

    Table 1: StaffBranch
    StaffNo. Name position Salary BranchNo. BranchAddress TelNo.
    S17005 Rizwan Manager 300000 B001 7 — st. Al Anayat Co. G- 302-3332-2
    Ahmed 11 Islamabad.
    S15002 Arifa Javed Assistant 80000 B001 7th — st. Al Anayat Co. G- 302-3332-2
    11 Islamabad.
    S23361 Ahmed Ali Manager 300000 B002 City Center Plaza, 420-5847-5
    Karachi
    S40021 Salinan Ahmed Assistant 750000 B002 City Center Plaza, 420-5847-5
    Karachi
    S00129 Hafza Khan Supervisor 90000 B003 Naz Digital Plaza, 205-2584-3
    Lahore.
    S01352 Ameer Ali Manager 200000 B004 Yusif plaza Rawalpindi 305-4561-9
    You are required to identify the normalization form(NF) of the given table and also transform the given table
    into the 3rd normalization form (3NF) and provide the resultant tables.

    Note: To convert in 3NF, first you need to convert it in 2NF, and resultant tables of 3NF are only required.

    Solution:

    Table 1.1: StaffDetaiIs


    StaffNo Name Position Salary
    S17005 Rizwan Ahmed Manager 300000
    S15002 Arifa Javed Assistant 80000
    S23361 Ahmed Ali Manager 300000
    S40021 Salman Ahmed Assistant 750000
    S00129 Hafza Khan Supervisor 90000
    S01352 Ameer Ali Manager 200000

    Table 1.2: BranchDetaiIs


    BranchNo BranchAddress TelNo

    B001 7th — st. Al Anayat Co. G-11 Islamabad. 302-3332-2

    B002 City Center Plaza, Karachi 420-5847-5

    B003 Naz Digital Plaza, Lahore. 205-2584-3

    B004 Yusif plaza Rawalpindi 305-4561-9

    Now, let's further normalize the tables into 3NF. Since there are no transitive dependencies, both tables are
    already in 3NF.

    Resultant Tables:
    Table 1.1: StaffDetails
    StaffNo | Name | Position | Salary

    Table 1.2:
    BranchDetails
    BranchNo | BranchAddress | TelNo
    So, the tables after normalization are already in 3NF, and the decomposition has resulted in the StaffDetails
    and BranchDetails tables.

    Question No.2

    Table1 Figure 1: CUSTOMER_ORDER Relationship


    (Master):
    Header Size of the CUSTOMER table: = 60
    Bytes Number of records stored in CUSTOMER
    table: = 70000 Table2 (Detail):
    Header size ORDER table: = 70 Bytes
    Number of records stored in ORDER table: = 5000000

    Suppose you have applied the pre-joining De-Normalization technique on the ‘CUSTOMER’
    and ‘ORDER’ tables.
    Calculate the size of the resultant table in Megabytes (MB), Gigabytes (GB), and Terabytes
    (TB). The reference column in these two tables is 8 bytes.

    Hint: There would be 5000000 records in the De-normalized table.

    Answer:
    The header of resultant de-normalization = 60+70 =130 bytes
    Subtract the reference column in these two tables bytes is = 130-8 =122 bytes
    According to the records in de-normalized table the size of de-normalization is =
    122*5000000
    =61,000,000 bytes
    For Convert bytes into MB divided by (1000) 2 = 61,000,0000 /(1000) 2 = 61 MB
    For Convert bytes into GB divided by (1000) 3 61,000,0000 /(1000) 3 =0.61 GB
    For Convert bytes into TB divided by (1000)’ = 61,000,0000 /(1000)’ = 0.00061TB

    You might also like