# 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;
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)

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

#### (B)

A, B, C. Each is a key.

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

#### (C)

10 (All are in BCNF).

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

### Problem 4

#### (A)

-2 for one mistake
-3 for two mistakes

#### (B)

-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

#### (C)

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

### Problem 5

#### (A)

```CREATE TABLE Comp1 (
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)

#### (B)

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

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

Error Codes

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

(F)
create assertion NotTooMany check
( 10 >= all
from Comp1, Comp2
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)

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)

```