Database Systems, CSCI 4380-01

Homework # 2

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