CS145 Midterm Solutions

Note: Problems 1 and 2 were graded by Jeff. Problems 3 and 4 were done by Himanshu. Reza did 5, 6(a), and 6(b); Chen did 6(c) through 6(f). If, after reading the solutions and error keys, you want to discuss the grading, you should give your exam, with a note, to the relevant person. Ms. Lambeth can deliver these notes and exams if you like.


Problem 1

(a) In diagram II, a student can only take a course once; not so in I.

(b) Now, in addition, a student can only take one course per quarter.

(c) I sort of botched this one. I had two points in mind, but they turned out to be restatements of the same basic idea: you can't tell what quarter a student took a course in. Thus, I gave 4 points to those who said this in two different ways. To get a full 5 points, you needed to say it again in a way that looks different, e.g., ``a student can't be enrolled twice in a course,'' or ``you can't tell how many students are enrolled in a given quarter.''

Error Codes

1A: In (b), many people interpreted the diagram as if an entity set with an arrow was determined only by the entity sets without arrows, and you lost 3 points if so. Put in FD terms, it is an error to assume S->CQ; the real FD's are SC->Q and SQ->C.

1B: You lost 2 points if I felt your expression was basically correct, but in abstract terms, like ``a student and course determine a quarter.''

Problem 2

     interface People {
         attribute string name;
         attribute string address;
         relationship Set exesOf inverse Singles::exes;

     interface Singles:People {
         relationship Set exes inverse People::exesOf;

     interface Marrieds:People {
         relationship Marrieds spouse inverse spouse;

Error Codes

2A: Declaring a key for People. There is no need for a key, and no reason to believe that name and address form a key. (-1)

2B: Many people tried to add an enumeration ``status'' to tell whether a single person was never married, divorced, or widowed. This is almost certain to introduce redundancy, since you can tell whether they were never married by their having an empty set of exes. Moreover, before you protest, please make sure you correctly handled the case where a person is both widowed and divorced. (-1)

2C: Omitting an inverse. (-1 each instance)

2D: Creating a pair of relationship where one self inverse relationship would do. Most common: husband and wife. I know I used that as an example, but I think we thoroughly trounced the idea. Anyway, this problem stipulation says nothing about husbands and wives, and doesn't rule out same-sex marriage. (-1)

2E: Putting spouse or exes in People rather than in the subclass where it belongs. (-2)

2F: Assuming that a previous spouse cannot be married now, or that they must be married now. (-2)

2G: Using an attribute where a relationship is appropriate, usually a string for spouse name. (-4)

2H: Not using any subclasses at all, i.e., putting everything in People. (-7)

2I: Declaring keys for subclasses. (-1 each instance)

Problem 3


A->B, B->C, C->A, A->C, B->A, C->B, AB->C, BC->A, AC->B.

-2 for not including AB->C, BC->A, and AC->B
-3 if you miss the above AND miss some more.
-4 if you miss EVEN the "cycle", A->B, B->C, C->A


A, B, C. Each is a key.

-2 for missing one key, -3 for missing two keys.


10 (All are in BCNF).

-5 if your answer is 0
-2 if it is between 5 and 10.
-3 if it is less than 5.

Problem 4


PROJECT_{ipAddr}(SELECT_{name = Shalmaneser}(Computers))

-2 for one mistake
-3 for two mistakes


PROJECT_{ipAddr,ipAddr1}(SELECT_{ipAddr > ipAddr1}(Computers NATURAL JOIN RENAME_{ipAddr1,location}(PROJECT_{ipAddr,location}(Computers))))

-2 if the selection (ipaddr1 > ipaddr2) is missing (to exclude repeated pairs)
-1 if renaming is done improperly or the join condition (same location) is missing
-1 for each additional mistake


PROJECT_{manf}(SELECT_{model <> model2 <> model3}(Computers NATURAL JOIN RENAME_{manf, model1}(PROJECT_{manf, model}(Computers)) NATURAL JOIN RENAME_{manf, model2}(PROJECT_{manf, model}(Computers))))

-2 if the renaming is done improperly or the join condition (same manufacture) is missing
-1 for each additional mistake

Problem 5


        ipAddr char(30) primary key,
        name   char(30) unique,
        location char(30) NOT NULL

Errors Codes

Many people forgot to make ipAddr the primary key. Remember that this needs to be the case because of the foreign key in Comp2. (-1)

Name needs to be unique since it also uniquely identifies tuples in Comp1. (-1)

Location cannot be a NULL. (-1)


        ipAddr char(30) PRIMARY KEY REFERENCES Comp1(ipAddr), 
        manf char(5) CHECK (manf in ('SUN', 'HP', 'Apple')),
        model int 

Error Codes

Many of you forgot that ipAddr is also the primary key for Comp2. (-1)

Some of you forgot to add the constraint on manf. (-2)

Some of you put a FOREIGN KEY statement without declaring ipAddr itself. (-1)

Some of you put the FOREIGN KEY declaration in Comp1 instead. (-2)

I didn't take points off for this; however, notice that you only need a string of size five for 'manf'. Anything more is a waste of space.

Problem 6


FROM Comp1, Comp2
WHERE Comp1.ipAddr = Comp2.ipAddr AND
      Comp1.name = 'Shalmaneser';
Most of you did this correctly. The most common mistakes were forgetting to do the join (-3) or to do the selection (-2).


SELECT name, ipAddr
ORDER BY name;
Some of you did not use ORDER BY (-3). Also note that you do not to join with Comp2 (-1).


     select manf, count(distinct model)
     from Comp2
     group by manf;

Error Codes

6C1: Missing the distinct in count(distinct model). (-1)

6C2: Missing the manf in select. (-1)

6C3: Doing a self-join on Comp2 and making the solution too complicated. (-1)

6C4: Using "group by model". (-1)


     select manf, count(ipAddr)
     from Comp2
     group by manf
     having count(distinct model) >= 2;

Error Codes

6D1: Missing the distinct in count(distinct model). (-1)

6D2: Using "group by(manf, ipAddr)". (-1)

6D3: Missing one selection attribute. (-1)

6D4: Doing a self-join on Comp2 using the manf attribute, but missing the distinct in "select manf, count(distinct ipAddr)". Since manfis not a key attribute, one ipAddr can appear many times in the self-join. That's why you need to use distinct to eliminate the duplicates. (-1)

6D5: Missing group by while using count(ipAddr) in the select. (-1)

6D6: Using distinct model in the selection. (-1)

6D7: Putting the count(distinct model) >=2 in the where. (-1)


     create view NameManfLoc as
     select name, manf, location
     from Comp1, Comp2
     where Comp1.ipAddr = Comp2.ipAddr;

Error Codes

6E1: Missing the manf in the selection. (-1)

6E2: Missing the Comp1.ipAddr = Comp2.ipAddr in the join. (-1)


     create assertion NotTooMany check
     ( 10 >= all
       (select count(ipAddr)
        from Comp1, Comp2
        where Comp1.ipAddr = Comp2.ipAddr
        group by (manf, location)));

Error Codes

6F1: Missing the join operation. (-2)

6F2: Not using 10 >= ... properly, even if using not exists or other operations. (-1)

6F3: Missing group by or using the wrong attributes. (-1)

6F4: Missing Comp1.ipAddr = Comp2.ipAddr. (-1)

6F5: Missing count(*) or count(ipAddr). (-1)

6F6: Missing all or using 10 >= ANY .... (-1)

6F7: Using a trigger, but not covering all the cases. (-1)

6F8: Missing create assertion NotTooMany check. (-1)

6F9: Using an attribute-based check to specify the constraint. Please refer to the second paragraph on page 338 in the text book for the reason. (-1)

6F10: Using count(..) in where. (-1)