CS145 Midterm Solutions

Index

• Problem 1
• Problem 2
• Problem 3
• Problem 4
• Problem 5
• Problem 6

Problem 1

(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

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

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)

-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

(B)

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

(C)

Problem 4

(A)

(B)

(C)

Problem 5

(A)

CREATE TABLE Comp1 (
        ipAddr char(30) primary key,
        name   char(30) unique,
        location char(30) NOT NULL
);

Errors Codes

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

Location cannot be a NULL. (-1)

(B)

CREATE TABLE Comp2 (
        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

(A) 

SELECT manf
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).  


(B)

SELECT name, ipAddr
FROM COMP1
ORDER BY name;


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


(C) 
 
     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)


(D) 
 
     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)



(E) 
 
     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)


(F) 
 
     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)