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
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?
5
51作业君版权所有
