Database Systems, CSCI 4380-01 Homework # 2

Database Systems, CSCI 4380-01
Homework # 2

Homework Statement. This homework is worth 5% of your total grade. If you choose to skip
it, Midterm #1 will be worth 5% more. Remember, practice is extremely important to do well in
this class. I recommend that not only you solve this homework, but also work on homeworks from
past semesters. Link to those is already provided in Teams, which I am repeating here:
http://cs.rpi.edu/~sibel/DBS_Past_Materials/
This homework aims to test normalization theory.
Question 1. You are given the following relation:
EventInformation(eventname, edate, starttime, duration, URL, description, host,
panelistname, panelistemail, participantid, participantname, participantemail,
participantaddress, ticketprice)
This is a relation containing information about different events. Each event can have multiple
names, panelists and participants. There can be multiple events on a given date, but only one
event can occur on a given edate and starttime. For such an event, there is a unique duration,
URL, description and host.
Two panelists from different events can have the same panelistname or panelistemail, but for
panelist in a specific event, panelistname is unique and given their panelistname for an event,
their panelistemail is fixed. (This means that however unlikely, two participants in the same
event may share an email but not name).
participantname, participantemail, participantaddress are not guaranteed to be unique in
the database, but participantid is unique for the whole relation. Given a unique participantid,
their participantname, participantemail and participantaddress is fixed. The ticketprice
value is unique for a unique participant and an event as different people can be charged different
amounts for the same event (in the same way Amazon charges different people different amounts
for the same product!).
List all relevant functional dependencies for this relation based on the above description.
Based on your functional dependencies, check if this relation is in BCNF or in 3NF. Show your
work.
Question 2. You are given the following set of functional dependencies for relation R(A, B, C, D, E, F, G).
F = {AC → D, AC → E, BE → F, AF G → B}
Is the decomposition of R into R1(A, B, C, F, G) and R2(A, B, C, D, E) a dependency preserving
decomposition?
To do this, find the projection of these functional dependencies to decomposed relations R1 and
R2 below as F1 and F2. Show some details of your work.
1
The find if the union of these functional dependencies and check if they are equivalent to the original
set F.
Question 3. You are given the following set of functional dependencies for relation R(A, B, C, D, E, F, G):
F = {AC → BD, BC → E, BE → DF, AG → EB}
R1(A, C, B, D)
R2(A, B, C, E, G)
R3(B, E, F)
R4(A, G, E)
Is the following decomposition lossless? Show your work with Chase decomposition algorithm.
Question 4. You are given the following set of functional dependencies for relation R(A, B, C, D, E, F, G, H):
F = {AD → CE, C → D, BEF → G, AG → C}
(a) Find keys, check if it is in 3NF or not.
(b) If it is not in 3NF, use 3NF decomposition to find relations in 3NF.
(c) For each decomposed relation, the find the functional dependencies that are projected into the
relation. Check if it is in BCNF or not.
Question 5. Convert the following set of functional dependencies to minimal basis. Show only
the main steps:
F = {AC → BD, BC → BE, ABC → E}
Question 6. You are given the following relation and the set of functional dependencies. In this
model, clubs can have multiple offiers but a person can be the offier of only one club.
Use BCNF decomposition to find a set of relations that are in BCNF.
Clubs(clubname, datefounded, url, contactemail, memberid, membername, officername,
officerposition)
We will shorten the attributes for simplicity to:
Clubs(cname, df, url, email, mid, mname, oname, oposition)
cname → df url email
cname mid → mname
oname → oposition
oname → cname
SUBMISSION INSTRUCTIONS. Submit a PDF document or a Text document for this homework using Submitty. No other format and no hand written homeworks please. No late submissions
will be allowed.
If the Submitty for homework submissions is not immediately available, we will announce it on
Teams when it becomes available.
2