Practice Problems for Exam 2
CSc 4710/6710; Spring 2001
Due: 10 April, 2001 (in class)
------------------------------

(1) Consider the following relational database scheme:

    s(SNO,sname,street,city)
    p(PNO,pname,color)
    sp(SNO,PNO,qty)

    where the s relation records information relating to suppliers
    the p relation records information about parts, and
    the sp relation records information about which supplier
    supplies which part and in what quantity. The primary keys are shown
    in upper-case and the sp relation has two foreign keys: sno
    referring to the s relation and pno referring to the p relation.

    Express the following queries in Datalog:

    (a) Find the names of suppliers who supply at least one red part.
    (b) Find the names of suppliers who do not supply any red part.
    (c) Find the names of suppliers who supply all red parts.
    (d) Find the names of suppliers who supply only red parts.

(2) Are the following two sets of FDs equivalent? Show your work and
    give reasons why or why not.

    F = { A --> BC, B --> C, A --> B, AB --> C }
    G = { A --> B, B --> C }

(3) The three rules of inferences for functional dependencies are
    given below:

    IR1 (Reflexivity):  if Y is a subset of X then X --> Y
    IR2 (Augmentation): { X --> Y } implies { XZ --> YZ }
    IR3 (Transitivity): { X --> Y, Y --> Z } implies { X --> Z }

    Prove or disprove the following:

    (a) { X --> YZ } implies { X --> Y, X --> Z }
    (b) { X --> Y, X --> Z } implies { X --> YZ }
    (c) { X --> Y, U --> V } implies { XU --> YV }
    (d) { X --> Y, YZ --> W } implies { XZ --> W }