Solutions to CS145 5/8/97 Midterm |
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.
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).
interface Employee (keys ID, ssNo) { attribute integer ID; attribute integer ssNo; attribute string name; attribute string phone; attribute Setprojects; 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
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.
a. (10 points)
The keys are AB, C, DE, AE, BD
Grading - Two points for every key
b. (3 points)
D -> A
E -> B
c. (4 points)
No 3NF violations
d. (3 points)
Two possibilities
e. (10 points)
The answer depends on your answer to d
Grading - The dependency DE->C was the tricky one and the others were straightforward and you were penalised 4 points for missing it..
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.