Homework 6

There were a total of 20 points. Except Q4 and Q12 (2pt each), other questions are 1pt each. Give half of the points if they mentioned the "Key point" but didn't explain it right.

1. (1pt) Generally, if you update a column on the right-hand side of an FD, and there can be dups in the columns on the left-hand side, you get an anomaly which can be resolved only by multiple updates or by rejection of the update. This is exactly what can happen if you update the TA field in one tuple.
Key point: anomaly resulting from an update in the TA field

2. (1pt) Generally, you don't know what values to associate on with some columns if you only have info for other columns. So, for example if you want to insert a tuple and the Lab_Section value is not in the database, you won't know if your TA is correct. (In principle, the choice of columns you use in your example matters: e.g. if you know the TA, Date, Lab_Section, and Location, but there are no SIDs yet, you could still insert a tuple with NULLs in the SID and Computer_Num fields. But we didn't take off any points for answers that used strange combinations of columns.)
Key point: insertion anomaly

3. (1pt) If everybody drop the lab section given by David, there would be a deletion anomaly: information about the association of David, Lab_Section LAB02, Location Soda 211 and Wed 4PM would be lost.
Key point: deletion anomaly

4. (2pt) {Date, TA} is not a superkey, and {Location} is not a subset of {Date, TA}. To prove that DA is not a superkey, we compute its attribute closure via the algorithm in the textbook, and find that {Date, TA}+ = {Date, TA, Location, Lab_Section}, so Date, TA does not determine SID, and hence is not a superkey.
1pt for the conclusion, 1pt for proof of the superkey.

5. (1pt) Base2 is missing the Location attribute.

6. (1pt) They are the attributes in the FD on which we chose to decompose.

7. (1pt) yes.

8. (1pt) There are 2 acceptable solutions:

a) This is the solution we were hoping for: show that Base1 intersection Base2 -> Base1 (or Base2) is in F+. We know DA->Th, hence DATh->DATh.

b) as in the book (pg. 426), note that the kind of decomposition we did is lossless (DA->Th, and DA and Th are disjoint, so the decomp is lossless).

Note that it was not sufficient to say, "The algorithm gives us a lossless decomposition." Some indication of the fact that the left and right hand sides are disjoint was necessary.

9. (1pt) yes.

10. (1pt) Any of the two approaches is acceptable. Once they mentioned something in one of the solution below, give them full credit for this question.

a) Given: F = {Date, TA->Loc}. They show that (F_Base1 U F_Base2)+ = F+. The simplest thing to do is to compute the two closures.

b) {Date, TA}->Loc can be enforced in Base1, which has all three attributes {Date, TA, Loc}. To enforce the FD we need to declare {Date, TA} as a candidate key of Base1. -- Give full credit if they mentioned something similar like "In Base1, for the same {Date, TA}, the tuples should have the same Location".

11. (1pt) {Lab_Section}->{TA}

12. (2pt) If they give the answer with 5 tables, like add a table (Date, Location, Lab_Section), they should point out {Date, Location} as the primary key. For "adding table" approach, give 0 pt if any mistake made in one of the 5 tables.

-- foreign key constraints are a good idea here but were not required. All other part except (1)(2) for .25pt

CREATE TABLE Base1 (
    Date Datetime,
    TA Char(20),
    Location Char(10),
    PRIMARY KEY (Date, TA)
);

CREATE TABLE Base21 (
    Lab_Section Char(20),
    TA Char(20),
    PRIMARY KEY (Lab_Section)
);

CREATE TABLE Base221 (
    Lab_Section Char(20),
    Computer_Num Char(2),
    SID Int,
    PRIMARY KEY (Lab_Section, SID)
);

CREATE TABLE Base222 (
    Lab_Section Char(20),
    Date Datetime,
    SID Int
    PRIMARY KEY (Date, SID)   -- (1) take off .25 if without this 
);

-- (2) (.5 point for doing an assertion, .5 more point for getting it right,
can give .25 pt if partially right. )
CREATE ASSERTION nonDP 
 CHECK (NOT EXISTS
        (SELECT A.Date, A.Location
           FROM Base222 D, Base221 C, Base21 B, Base1 A
          WHERE D.Lab_Section = C.Lab_Section
            AND D.SID = C.SID
            AND D.Lab_Section = B.Lab_Section
            AND D.Date = A.Date
            AND B.TA = A.TA
         GROUP BY A.Date, A.Location
         HAVING COUNT(B.Lab_Section) > 1));

13. (1pt) The same (possibly with different table names).

14. (1pt) yes

15. (1pt) yes

16. (1pt) one table added: Location, Lab_Section, Date (Base2)

17. (1pt)

NOTE: solutions different from those given below for (17) and (18) were checked for lossless-join and dependency-preservation properties using designview.

Minus a half point if the SQL was ok, but "Lab_Section" was added to the primary key of the "added" tables. There's a {Date,Location}->Lab_Section FD that makes having Lab_Section in the PK wrong.

CREATE TABLE Base1 (
    Date Datetime,
    TA Char(20),
    Location Char(10),
    PRIMARY KEY (Date, TA)
);

CREATE TABLE Base2 (
    Lab_Section Char(20),
    Date Datetime,
    Location Char(10)
    PRIMARY KEY (Date, Location) -- "added" .5 pt
);

CREATE TABLE Base21 (
    Lab_Section Char(20),
    TA Char(20),
    PRIMARY KEY (Lab_Section)
);

CREATE TABLE Base221 (
    Lab_Section Char(20),
    Computer_Num Char(2),
    SID Int,
    PRIMARY KEY (Lab_Section, SID)
);

CREATE TABLE Base222 (
    Lab_Section Char(20),
    Date Datetime,
    SID Int
    PRIMARY KEY (Date, SID) -- "added" .5 pt
);

18. (1pt) Same as 17.