CIS5500 Fall 2024
CIS 5500: Database and Information Systems
Homework 2: Relational DB Design
This homework is about relational database design, from ER diagrams to relational schemas and
their normalization.
Submission : Create one .pdf file with your answers, with the answer to each question on a
separate page (s), and submit using Gradescope. Be sure to label your responses as numbered
here.
Question 1 (30 points)
A. (20 points)
Model the application below using an ER diagram. Use the notation from class: cardinality
constraints on edges are n..m annotations, where n and m are integers or m is * (e.g. 0..1, 1..1,
25..30, 1..*, etc); weak entity sets use bold or double boxes and the defining relationship is bold
or double edged; etc. Create an electronic version of each diagram using draw.io, Powerpoint,
Word, Visio, OpenOffice Draw, OmniGrae, Google Drive Document or any other software that you
are familiar with, and export it to PDF format. Do not turn in handwritten diagrams as they are
hard to read!
You are the IT manager of the National Football League (NFL). To prepare for a potential visit of
Taylor Swift in the Superbowl LVIII, you want to impress her by showing her the awesome ER
diagram you designed and hopefully score a few free concert tickets. However, you realize you
accidentally deleted the file. You need to re-draw the ER diagram based on the following
specification:
● A team has a unique ID, name, city, and homecourt.
● A player has a unique ID, name, position, and salary (annual).
● A staff member has a unique ID, name, and salary (annual).
● A team must have at least 53 and at most 55 players, and each player can belong to at
most one team. There could be players who do not currently have a team.
● A team may employ zero or more staff members, and each staff member must belong to
exactly one team.
● A Super Bowl championship has a unique year and score.
● An award has a unique ID and award name (e.g. Most Valuable Player).
● A team can win zero or more Super Bowl championships, and each Super Bowl
championship belongs to one team.
● A player may receive zero or more awards, and each award is given to one or more players
(there might be shared awards among players). You should also include the year in which
players received the awards. It is possible for a player to receive the same award in
different years.
1
CIS5500 Fall 2024
B. (4 points)
Now suppose that staff do not have an ID (only name and salary) , and that no team has two staff
with the same name. However, there may be staff members with the same name on different
teams. How would you modify your ER diagram to reflect this? (You can just show the modified
portion of the ER diagram.)
C. (6 points)
Please use your answer from Part A for this question, in which staff have a unique ID:
Suppose there are three types of staff: coach, executive, and trainer. Only coaches have wins and
total games, and only executives have a title. Furthermore, each trainer can train zero or more
players, and each player can receive training from zero or multiple trainers. How would you
modify your ER diagram to reflect this?
2
CIS5500 Fall 2024
Question 2 (20 points)
The following ER diagram models an application where users can create groups for which they
become the owner and to which users can become members. Any member of a group can add an
expense , which is defined as an amount which the user has paid and would like to split with
others in a group.
A. (15 points)
Translate the ER diagram to the relational model by specifying the resulting relations, their
attributes, keys, foreign keys and NOT NULL attributes. Attributes with the annotation (O) may
be null (e.g. “picture” in expense), whereas those with no annotation must have some value (i.e.
are NOT NULL). Keys are underlined.
You may use the abbreviated notation used in class (e.g. “R( A , B, C) B foreign key referencing
S(B), C NOT NULL”) or SQL DDL (using whatever is natural for the domains of attributes).
B. (5 points)
Suppose now that you wish to enforce the fact that expenses can only be added by users who are
members of the group. Discuss how this could be done, or say why your previous answer enforces
it already.
3
CIS5500 Fall 2024
Question 3 (38 points)
You are given a schema containing information about employees and a minimal set of functional
dependencies F:
R(EId, Name, Salary, DptId, Manager, ProjectID, PayGrade)
F = {EId → Name, DptId, ProjectId, Paygrade;
DptId, ProjectId → Manager;
PayGrade → Salary}
Please (briefly, in 1-2 sentences) explain your answer for all parts of this question.
A. (4 points)
What are the candidate keys for R? Prove your answer.
B. (4 points)
Which (if any) of the functional dependencies in F violate 3NF?
C. (5 points)
Does the following decomposition have a lossless join? Prove your answer using the technique
covered in class.
Employee(EId, Name, DptId, ProjectId, PayGrade, Salary)
Department(DptId, Manager)
D. (5 points)
Does the following decomposition have a lossless join? Again, prove your answer using the
technique covered in class.
Employee(EId, Name, DptId, ProjectId, PayGrade, Salary)
Manages(DptId, ProjectId, Manager)
E. (15 points)
Using the algorithm given in class, give a decomposition of R into 3NF. For each decomposed
relation Ri, give the set of functional dependencies that are preserved in Ri and its candidate
keys.
G. (5 points)
Is each of the Ri in your decomposition in BCNF?
4
CIS5500 Fall 2024
Question 4 (12 points)
Continuing with the question above, s uppose that in F there is one additional functional
dependency, Manager → ProjectId.
R(EId, Name, Salary, DptId, Manager, ProjectID, PayGrade)
F’ = {EId → Name, DptId, ProjectId, Paygrade;
DptId, ProjectId → Manager;
Manager → ProjectId;
PayGrade → Salary}
Please (briefly, in 1-2 sentences) explain your answer for all parts of this question.
A. (4 points)
Is Manager → ProjectId something “new”, i.e. is it derivable from F using Armstrong’s Axioms?
B. (4 points)
Suppose that in your 3NF decomposition of R using the original set of functional dependencies F
you have a relation Ri(DptId, ProjectId, Manager). How does the addition of Manager → ProjectId
in F’ affect Ri in terms of the functional dependencies that are preserved and the candidate keys for
Ri?
C. (4 points)
Is Ri in 3NF? Is it in BCNF?