Scribd Logo
Upload

Welcome to Scribd!

  • 167 views

    Lab 6

    Uploaded by

    Vishal Ponugoti
    The document provides instructions for a lab assignment in 6 parts: 1) Students must complete the lab in groups and submit one file through Blackboard with a declaration of original work. 2) Specify each group member's contributions. 3) Normalize a sample relation to third normal form. 4) Analyze a sample student table to determine dependencies and create 2NF and 3NF databases. 5) Create 2NF and 3NF databases based on a dependency diagram of authors, books, and royalties. 6) Analyze another sample ERD, identify dependencies, and convert to 3NF.

    Copyright:

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

    Lab 6

    Uploaded by

    Vishal Ponugoti
    167 views6 pages
    The document provides instructions for a lab assignment in 6 parts: 1) Students must complete the lab in groups and submit one file through Blackboard with a declaration of original work. 2) Specify each group member's contributions. 3) Normalize a sample relation to third normal form. 4) Analyze a sample student table to determine dependencies and create 2NF and 3NF databases. 5) Create 2NF and 3NF databases based on a dependency diagram of authors, books, and royalties. 6) Analyze another sample ERD, identify dependencies, and convert to 3NF.

    Original Title

    Lab6.docx

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides instructions for a lab assignment in 6 parts: 1) Students must complete the lab in groups and submit one file through Blackboard with a declaration of original work. 2) Specify each group member's contributions. 3) Normalize a sample relation to third normal form. 4) Analyze a sample student table to determine dependencies and create 2NF and 3NF databases. 5) Create 2NF and 3NF databases based on a dependency diagram of authors, books, and royalties. 6) Analyze another sample ERD, identify dependencies, and convert to 3NF.

    Copyright:

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

    Lab 6

    Uploaded by

    Vishal Ponugoti
    The document provides instructions for a lab assignment in 6 parts: 1) Students must complete the lab in groups and submit one file through Blackboard with a declaration of original work. 2) Specify each group member's contributions. 3) Normalize a sample relation to third normal form. 4) Analyze a sample student table to determine dependencies and create 2NF and 3NF databases. 5) Create 2NF and 3NF databases based on a dependency diagram of authors, books, and royalties. 6) Analyze another sample ERD, identify dependencies, and convert to 3NF.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 6
    At a glance
    Powered by AI
    The document discusses database normalization and provides content to work on normalization problems.

    The document is structured with multiple pages providing text about database normalization lab questions.

    First, second and third normal forms are discussed in the context of normalization problems provided.

    BTD210- Lab 6

    Please work in groups to complete this lab. This lab is worth 2% of the total course grade and will be
    evaluated through your written submission.

    Please submit the following files through Blackboard. Only one person must submit for the team.

    - Lab6.docx

    1- Add this declaration on the top of your file.

    We, Sahil Patel, Vishal Ponugoti, Saeed Fitrat Khuram, declare that the attached assignment is our
    own work in accordance with the Seneca Academic Policy. No part of this assignment has been
    copied manually or electronically from any other source (including web sites) or distributed to other
    students.

    2- Specify what each member has done towards the completion of this work:

    Name Task(s)
    1 Sahil patel 3,
    2 Vishal Ponugoti
    3 Saeed Fitrat Khuram 3,

    3-

    Normalize the following relation up to third normal form.

    BTD210- Lab 6 Page 1 of 6


    1NF:

    OrderID Order Date Customer Customer Customer ProductID Product Product Product Ordered
    ID Name Address Description Finish Standard Quantity
    Price
    1006 10/24/2010 2 Value Plano, TX 7 Dining Table Natural 800.00 2
    Furniture Ash
    1006 10/24/2010 2 Value Plano, TX 5 Writers Desk Cherry 325.00 2
    Furniture
    1006 10/24/2010 2 Value Plano, TX 4 Entertainment Natural 650.00 1
    Furniture Center Maple
    1007 10/25/2010 6 Furniture Boulder, 11 4 Door Oak 500.00 4
    Gallery CO Dresser
    1007 10/25/2010 6 Furniture Boulder, 4 Entertainment Natural 650.00 3
    Gallery CO Center Maple

    2NF:

    OrderID Order Date Customer ID Customer Name Customer


    Address
    1006 10/24/2010 2 Value Furniture Plano, TX
    1006 10/24/2010 2 Value Furniture Plano, TX
    1006 10/24/2010 2 Value Furniture Plano, TX
    1007 10/25/2010 6 Furniture Gallery Boulder, CO
    1007 10/25/2010 6 Furniture Gallery Boulder, CO

    ProductID Product Description Product Finish Product Standard Price

    7 Dining Table Natural Ash 800.00


    5 Writers Desk Cherry 325.00
    4 Entertainment Center Natural Maple 650.00
    11 4 Door Dresser Oak 500.00
    4 Entertainment Center Natural Maple 650.00

    OrderID ProductID Ordered


    Quantity
    1006 7 2
    1006 5 2
    1006 4 1
    1007 11 4
    1007 4 3

    BTD210- Lab 6 Page 2 of 6


    4- Consider the STUDENT table (On the last page)
    a. Determine the partial dependencies.

    CLG ST_ ST_LN ST_M CLG_N DPT_C DPT_N DPT_PH ADV_LN ADV_O ADV_B ADV_P ST_G ST_HO ST_LE
    _ID ID AME AJOR AME ODE AME ONE AME FFICE LDG HONE PA URS VEL

    b. Create a database whose tables are at least in 2NF, showing the dependency diagram
    for each table.

    DPT_CODE DPT_NAME DPT_PHONE ST_ID ST_LNAME ST_MAJOR ST_GPA ST_HOURS ST_LEVEL


    ACCT Accounting 4356 100 Stephanos Accounting 3.87 75 L1
    ACCT Accounting 4356 101 Smith Accounting 2.78 45 L0
    MKTG Marketing 4378 200 Jones Marketing 2.31 117 L3
    MKTG Marketing 4378 201 Ortiz Marketing 3.45 113 L3
    MATH Mathematics 3420 100 Stephanos Accounting 3.87 75 L1
    MATH Mathematics 3420 202 Johnson Graph 3.42 115 L3

    CLG_ID CLG_NAME ADV_LNAME ADV_OFFICE ADV_BLDG ADV_PHONE


    1 Business Admin Grastrand T201 TEL Building 2115
    1 Business Admin Grastrand T201 TEL Building 2115
    1 Business Admin Gentry T228 TEL Building 2123
    1 Business Admin Tillery T356 TEL Building 2159
    2 Arts & Sciences Chen J331 Jones Building 3209
    2 Arts & Sciences Jackson J332 Jones Building 3210

    DPT_CODE CLG_ID ADV_LNAME ST_ID


    ACCT 1 Grastrand 100
    ACCT 1 Grastrand 101
    MKTG 1 Gentry 200
    MKTG 1 Tillery 201
    MATH 2 Chen 100
    MATH 2 Jackson 202

    BTD210- Lab 6 Page 3 of 6


    c. Create a database whose tables are at least in 3NF, showing the dependency diagram
    for each table.

    5- [Coronel and Russel- Q6.7] The dependency diagram in Figure Q6.7 indicates that authors are
    paid royalties for each book that they write for a publisher. The amount of the royalty can vary
    by author, by book, and by edition of the book.
    a) Based on the dependency diagram, create a database whose tables are at least in 2NF,
    showing the dependency diagram for each table.
    b) Create a database whose tables are at least in 3NF, showing the dependency diagram for
    each table.

    6- [Coronel and Russel- P6.2] Using the descriptions of the attributes given in Figure P6.2,
    a. Show the dependency diagram of the ERD shown (the initial dependency diagram).
    Label partial and transitive dependencies.
    b. Convert into a dependency diagram that is in at least 3NF.

    Note: A session is defined as a set of presentations that are given on a specific date and in a specific
    room. Therefore, all presentations that are scheduled for the same date and in the same room have the
    same session number.

    BTD210- Lab 6 Page 4 of 6


    BTD210- Lab 6 Page 5 of 6
    Table for Question 4:

    STUDENT
    CLG_ID ST_ID ST_LNAME ST_MAJOR CLG_NAME DPT_CODE DPT_NAME DPT_PHONE ADV_LNAME ADV_OFFICE ADV_BLDG ADV_PHONE ST_GPA ST_HOURS ST_LEVEL
    1 100 Stephanos Accounting Business ACCT Accounting 4356 Grastrand T201 TEL 2115 3.87 75 L1
    Admin Building
    1 101 Smith Accounting Business ACCT Accounting 4356 Grastrand T201 TEL 2115 2.78 45 L0
    Admin Building
    1 200 Jones Marketing Business MKTG Marketing 4378 Gentry T228 TEL 2123 2.31 117 L3
    Admin Building
    1 201 Ortiz Marketing Business MKTG Marketing 4378 Tillery T356 TEL 2159 3.45 113 L3
    Admin Building
    2 100 Stephanos Accounting Arts & MATH Mathematics 3420 Chen J331 Jones 3209 3.87 75 L1
    Sciences Building
    2 202 Johnson Graph Arts & MATH Mathematics 3420 Jackson J332 Jones 3210 3.42 115 L3
    Sciences Building

    BTD210- Lab 6 Page 6 of 6

    You might also like