Homework 1 -- Modeling Teams and Their Ratings
Question 1 -
Why not use a
ternary administers relationship
involving Professors, GTAs, and Sections, and putting the rating attribute on administers?
Answer:
Consider the relational table
generated for the administers
relationship. It would have a
schema (omitting domains) of:
administers(ProfSSN,
GTASSN, Dept, Course#, Sec #, rating).
Each tuple would consist of a
foreign key to Professors, a foreign key to GTAs, a foreign key (with three
fields) to Sections, and a rating field.
Thus, a typical tuple might be:
(Prof
Smith_SSN, Judy_SSN, INFS, 614, 02, Great)
A problem arises when more
than one professor or GTA is involved. Pretend there is a second GTA named Robert. Two tuples are now required:
(Prof
Smith_SSN, Judy_SSN, INFS, 614, 02, Great)
(Prof
Smith_SSN, Robert_SSN, INFS, 614, 02, Great)
What guarantee do we have
that the team of Prof Smith, Judy,
and Robert get a single rating? If
someone updates the second tuple from Great to Poor,
this team has two ratings! The
problem here is that rating attribute should be assigned to a team of arbitrary
size, not just to one Professor and one GTA, as the ternary relationship forces
us to do.
Furthermore,
what if there were a second professor, Professor Jones, involved. We could add two more tuples, giving
us:
(Prof
Smith_SSN, Judy_SSN, INFS, 614, 02, Great)
(Prof
Smith_SSN, Robert_SSN, INFS, 614, 02, Great)
(Prof
Jones_SSN, Judy_SSN, INFS, 614, 02, Great)
(Prof
Jones_SSN, Robert_SSN, INFS, 614, 02, Great)
Do we need the 4th tuple,
since all we want to do is add the fact that Prof Jones is on the team? However, if we add just one tuple, how
do we fill in its GTA field? (With
Judy or with Robert?). Try to
imagine what tuples you would use for a team with 2 Professors and 5 GTAs; this
problem gets worse!
In
general, it is very awkward to represent an arbitrarily sized team this way!
Question 2 -
OK, why not put
the rating attribute on Sections
instead?
Answer:
Since
a team gets a single rating for each section it administers, this addresses the
first problem (having a single team rating) as long as each Section has only
one team. This is true if we
assume a) the database only covers this semester or b) we add a semester attribute to distinguish INFS614-02 Fall 2003 from
INFS614-02 Spring 2004.
This
approach is awkward from a modeling point of view because the rating really is
an attribute of a team, not the section.
What if teams got a single rating for all sections they administered (similar to the manager in
2.5.2 in your book who got one budget for all depts he manages)? In this case, rating must be associated with a team, somehow, not the
section.
We
are also still left with the second problem above: this is a very awkward way to represent an arbitrarily-sized
team.
Question 3 -
OK, lets add a
Teams entity set which administers Sections, and lets put rating on administers. Can we now use a ternary on team relationship involving Professors, GTAs, and Teams?
Answer:
This
is a good answer for placing the rating attribute, and now we have an actual Team ID, something that would have been nice before.
However,
we have the same problem as above for team composition. The ternary relationship results in a
relational table on_team with a
TeamID, ProfID, and GTAID. What if
there are 2 Professors and 5 GTAs on team 5? How many tuples would I put into on_team for team 5?
The best overall answer is
to link Professors and GTAs to a Teams entity by two separate (N-M) on_team
relationships, as in the solution shown in class. Teams then administer Sections, and rating goes on the administration relationship between
them. Modeling-wise, this is a
very general solution. (It will result
in quite a few joins, but that is part of physical design, which usually comes
later.)