Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    CZ2007 Q1SS5 PDF

    Uploaded by

    Lee Han Ni

    Copyright:

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

    CZ2007 Q1SS5 PDF

    Uploaded by

    Lee Han Ni
    4 views3 pages

    Original Title

    CZ2007_Q1SS5.pdf

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    4 views3 pages

    CZ2007 Q1SS5 PDF

    Uploaded by

    Lee Han Ni

    Copyright:

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

    QUIZ 1

    Name: ___________________________________________________

    Matric Number: ___________________________________________________

    Tutorial Group: ___________________________________________________

    Question 1 (24 marks)

    Consider the following schema containing bank account information. Primary Keys are in

    m
    er as
    bold.

    co
    eH w
    CUSTOMERS(customer_name, address)
    ACCOUNTS(account_number, balance)

    o.
    ACCOUNT_OWNERS(customer_name, account_number)
    rs e
    ou urc
    Express the following queries using Relational Algebra (RA). You may use the following
    operators:
    σ (selection), Π (projection), ∪ (union), ∩ (intersection), − (difference),
    o

    γ (grouping and aggregation), δ (duplicate elimination), ÷ (division), := (assignment),


    ρ (rename), ⋈ (join), ⋈L (left outerjoin), ⋈R (right outerjoin), ⋈ (full outerjoin)
    aC s
    vi y re

    (i) Find the owner of the account with the highest balance.

    R1 := γMAX(balance)MaxBal ACCOUNTS
    ed d

    R2 := Πaccount_num (ACCOUNT ⋈balance = MaxBal R1)


    ar stu

    Result := Πcustomer_name (R2 ⋈ ACCOUNT_OWNERS)


    is
    Th
    sh

    CSC206-CPE303/Quiz 1/2013 1

    This study source was downloaded by 100000789567793 from CourseHero.com on 02-28-2023 23:12:20 GMT -06:00
    https://www.coursehero.com/file/17102913/QUIZ-1-SSP5/

    https://www.coursehero.com/file/34456309/CZ2007-Q1SS5pdf/
    (ii) Find the customer(s) who does not have any account with balance > 0.

    R1 := σbalance > 0 ACCOUNTS


    R2 := Πcustomer_name (R1 ⋈ ACCOUNT_OWNERS)
    Result := Πcustomer_name (CUSTOMERS) – R2

    m
    er as
    co
    eH w
    o.
    rs e
    ou urc
    o
    aC s
    vi y re

    (iii) Find the 100 accounts whose balances are the lowest.

    R1 := ρR1(anum, bal) ACCOUNTS


    ed d

    R2 := ACCOUNT ⋈ balance>=bal R1
    ar stu

    R3 := γaccount_num, COUNT(bal) ACNT R2


    Result := Πaccount_name (σACNT <= 100 R3)
    is
    Th
    sh

    CSC206-CPE303/Quiz 1/2013 2

    This study source was downloaded by 100000789567793 from CourseHero.com on 02-28-2023 23:12:20 GMT -06:00
    https://www.coursehero.com/file/17102913/QUIZ-1-SSP5/

    https://www.coursehero.com/file/34456309/CZ2007-Q1SS5pdf/
    Question 2 (26 marks)

    Consider a relation R(A,B,C,D,E) and FD’s ABC, BCD, ABD, and EC.

    (i) Find all candidate keys.

    {ABE}

    m
    er as
    co
    eH w
    o.
    rs e
    ou urc
    o
    aC s
    vi y re

    (ii) Is this relation in 3NF? Why or why not? If it's not, decompose the relation into
    collections of relations that are in 3NF.

    The relation is not in 3NF, since each given FD violates the requirements of 3NF.
    ed d

    Minimal basis: {ABC, BCD, EC}


    ar stu

    3NF Decomposition: R1(A, B, C), R2(B, C, D), R3(E, C), R4(A, B, E)


    is
    Th
    sh

    CSC206-CPE303/Quiz 1/2013 3

    https://www.coursehero.com/file/17102913/QUIZ-1-SSP5/
    This study source was downloaded by 100000789567793 from CourseHero.com on 02-28-2023 23:12:20 GMT -06:00

    https://www.coursehero.com/file/34456309/CZ2007-Q1SS5pdf/
    Powered by TCPDF (www.tcpdf.org)

    You might also like