Solutions to CS145 5/8/97 Midterm

Problem 1

I had expected that people would use subclasses to represent the condition that express trains don't stop at local stops. However, that solution is hard, and few got it completely correct. There is a simpler solution that ignores this constraint and just treats the problem as two entity sets and a relationship. I had to take off a point, because this solution omits a part of the problem statement. However, since simplicity is part of good design, I feel guilty. Anyway, the simpler approach presented fewer pitfalls, so those who adopted it tended to get a good score.

Here are the Solution with subclasses and the Solution without subclasses.

For part (b), suitable relations are:

     Train(number, engineer)
     Station(name, address)
     ExpressStop(number, name, time)
     Stop(number, name, time)
If you don't like these, I don't blame you. You have to guess whether a train is local or express before you can find when it stops in Palo Alto. However, that is what the E/R-to-relations crank gives you.

Note that the subclasses don't have any attributes, so there is no need to create relations. If you do, you get relations whose schemas are contained in another schema. That's not illegal. However, in this case there is no justification. For example, listing all the express trains is not useful. E.g., we can find out which trains are express by looking at the number component of ExpressStop.

Error Codes

A. Fails to represent the condition that an express train can only stop at an express stop (-1).

B. Wrong key (-2).

C. Created a subclass that is not distinguished in any way from its superclass (-2).

D. Not following the E/R-to-Relations rules (-4).

E. Making Stop a connecting (weak) entity set (-3).

F. Creating a relation whose schema is contained in another's (-1).

G. Making the Stop relationship more than 2-way (-2). Remember that a relationship relates all the entity sets it connects. There is no ``or'' option.

H. Failure to represent a subclass fact, e.g., an express train is a kind of train, and the attributes of trains belong with Train, not Express Train (-3).

Problem 2

interface Employee
    (keys ID, ssNo)
{
    attribute integer ID;
    attribute integer ssNo;
    attribute string name;
    attribute string phone;
    attribute Set projects;
    relationship Employee manager inverse manages;
    relationship Set manages inverse manager;
}

For part (b), the relation is:

     Employee(ID, ssNo, name, phone, projects, manager)

For part (c): 

     ID -> ssNo name phone manager
     ssNo -> ID

Error Codes

A. Failure to follow ODL-to-relations strategy (-4).

B. False FD or missing FD (-2, each occurrence).

C. Making project or phone a class (-2 each). Note that the fact that employees can share phones was an intentional red herring. Employees can share names, but no one thought to make name a class, and thank goodness for that.

D. Wrong key (-2).

E. No Set where appropriate, e.g., set of managed employees or projects (-1 each occurrence).

F. Representing ``manages'' in both directions in the relation (-2).

G. Not representing ``manages'' as a relationship (-3). Since managers are employees, the ``manages'' relationship is between classes, so an attribute is not appropriate, although technically legal. Also, note that there is no purpose in making managers a subclass, since they have no special properties.

Problem 3

a. (10 points)

b. (3 points) 

c. (4 points)

d. (3 points)

Two possibilities

  1. E->B  gives BE  and ACDE
  2. D->A gives AD  and BCDE


e. (10 points)

The answer depends on your answer to d

  1. BE       :  E->B
    ACDE :  C->D, C->E, D->A  and DE->C
  2. AD       :  A->D
    BCDE :  C->D, C->E, E->B  and DE->C

Grading - The dependency DE->C was the tricky one and the others were straightforward and you were penalised 4 points for missing it..

Problem 4

Each part was worth 5 points.

a) 

  SELECT bc#
  FROM Babies
  WHERE dob = '5/8/97'
  ORDER BY DESC bc#

-1 if forgot to use bc# after ORDER BY.
-2 if did not use ORDER BY.

b) 

  SELECT roomNo
  FROM Babies, Rooms
  WHERE Babies.bc# = Rooms.bc# AND ln = 'Aiken'

c) 

SELECT B1.fn, B2.fn
  FROM Babies as B1, Babies as B2
  WHERE B1.ln = B2.ln 
    AND B1.dob = B2.dob
    AND B1.bc# > B2. bc#

-1 if used B1.fn > B2.fn instead.
-2 for any missing clause in WHERE.   
-1 for illegal use of rename.

d) 

(SELECT bc# FROM Babies)
          EXCEPT
     (SELECT bc# from Rooms)

e) 

SELECT dob, COUNT(bc#) 
  FROM Babies
  GROUP BY dob
  HAVING COUNT(bc#) >= ALL
                (SELECT COUNT(bc#)
                 FROM Babies
                 GROUP BY dob) 

A -2 SELECT in FROM clause.
B -4 using SELECT dob, MAX(COUNT (*)) construct where dob is not related to 
   the value MAX(COUNT (*)) as discussed in class.
C -2 for aggregation on aggregate value, as in MAX(COUNT *)
D -2 using COUNT(*) > ALL in WHERE clause instead of HAVING clause.

f) 

  DELETE FROM Rooms
  WHERE date <
    (SELECT dob
     FROM Babies
     WHERE Babies.bc# = Rooms.bc#
    )

A number of solutions included Rooms in the FROM clause of the subquery. That has the effect of deleting too many tuples, because Rooms.bc# then refers to any Rooms tuple, rather than the birth certificate of the Rooms tuple that is being considered for deletion. The message ``date is not a key'' appears on these exams.