CSE 444 Midterm

Autumn 1999

Name___________________________________

 

 

 

Please define the following terms in a three sentences or less:
  1. [10 points] Referential integrity constraints

    2. Asserts that exactly one value exists in the role specified
  2. [10 points] Correlated queries

A query that contains a subquery where the subquery refers to tuple variables or attributes of the outer relation.

 

 

[20 points] Decompose R(A,B,C,D,E) with functional dependencies Aà B, Bà C, Cà D into BCNF. Justify your answer, show your work, and please circle the final relations in your decomposition

 

A+= ABCD
B+= BCD
C+= CD
D+= D
E+= E
AB+= ABCD
AC+= ABCD
AD+= ABCD
AE+= ABCDE KEY
BC+= BCD
BD+= BCD
BE+= BCDE
CD+= CD
CE+= CDE
DE+= DE
ABC+= ABCDE
ABD+= ABCD
ABE+= ABCDE
ACD+= ABCD
ACE+= ABCDE
ADE+= ABCDE
BCD+= BCD
BCE+= BCDE
BDE+= BCDE
CDE+= CDE
ABCD+= ABCD
ABCE+= ABCDE
ACDE+= ABCDE
BCDE+= BCDE

Now we choose a functional dependency to split on.

I chose Aà B:

R1(A,B), R2(A,C,D,E)

R2 is not in BCNF, split on Cà D

R3(A,C,E), R4(C,D); R4 is in BCNF

R3 is not in BCNF; split on Aà C

R5(A,C), R6(A,E)

So our final decomposition is R1(A,B), R4(C,D), R5(A,C), R6(A,E)

 

Given the following schema for a library, give queries for the questions below. Note that the last question asks for a query in relational algebra.

Book(ISBN, author, title, subject)

Borrowed(book_ISBN, borrowers_card_number, due_date)

Library_Patron(card_number, first_name, last_name, phone_number)

Book_Request(book_ISBN, requesters_card_number)

  1. [15 points] For each book that has been both borrowed and requested, give the phone number of the borrower and the requester

    2. Select b.phone_number, r.phone_number

    From Library_Patron as b, Library_Patron as r, Book_Request as br, Borrowed as bo

    Where b.card_number = borrowers_card_number and

    r.card_number = requesters_card_number and

    bo.book_ISBN = br.book_ISBN
  2. [10 points] Give a SQL query for how many times have books titled A First Course in Database Systems been borrowed?

    3. Select count(*)

    From Borrowed, Book

    Where Borrowed.book_ISBN = Book.ISBN

    And Book.title = ‘A First Course in Database Systems’
  3. [15 points] Give a SQL query that produces the set of pairs (a, n), where n is the number of times that Alon Levy has borrowed a book authored by a.

    4. Select count(book_ISBN), author

    From Borrowed, Book, Library_Patron

    Where Borrowed.book_ISBN = Book.ISBN

    And Library_Patron.first_name = ‘Alon’

    And Library_Patron.last_name = ‘Levy’

    And Borrowed.borrowers_card_number = Library_Patron.card_number

    Group by author

  4. [5 points] Give a SQL query that produces the set of pairs (borrower, number) such that "number" is the number of books borrowed by the "borrower" and that satisfy the following conditions:

    5. Select card_number count(book_ISBN)

    From Library_patron, Borrowed, Book

    Where card_number = borrowers_card_number and

    author > last_name and book_ISBN = ISBN and

    book_ISBN not in (

    Select book_ISBN

    From Borrowed as borrower1, Borrowed as borrower2, book

    Where book_ISBN = ISBN and

    Borrower1.borrowers_card_number <> borrower2.borrowers_card_number)

    Group by card_number

  5. [15 points] Give a relational algebra query to list the titles of all books borrowed by people whose last name is Pottinger

P title (Book |><| ISBN = book_ISBN(Borrowed |><| borrowers_card_number = card_number(s last_name = ‘Pottinger’ Library_Patron))