Problem 1

Consider the following ER diagram

(a). Answer the following True or False Questions

Statement	True/False
If we add the number of B entities with the number of C entities, it will always be equal to the number of A entities.	False
The number of A entities is equal to or greater than the number of D entities.	True
The number of B entities is the same as the number of D entities.	False
It is possible for us to remove an entity from entity sets C and D without affecting the number of relationships in relationship set V.	True

(b). Translate the Er Diagram according to the Rules Discussed in Class

When filling in the blanks on the following table, if you create one relation to represent both an entity and a relationship, put the combined relation in the row for the entity and put "N/A" in each box for the row for the relation. If a relation does not have a foreign key and/or a non-null attribute, put "N/A".

A blank table cell means that you do not want to submit an answer and will automatically be considered as incorrect.

Relation Containing	Attributes	Primary Keys	Foreign Keys	NOT NULL Constraints	UNIQUE Constraints
Entity Set A	m, n	m	N/A	N/A	N/A
Entity Set B	m1, m2, o, p	m1	m1, m2, o	m2, o	N/A
Entity Set C	N/A	N/A	N/A	N/A	N/A
Entity Set D	o, m1, m2	o	m1, m2	m1, m2	m1
Relationship Set R	N/A	N/A	N/A	N/A	N/A
Relationship Set T	m, o	m, o	m, o	m, o	N/A
Relationship Set V	N/A	N/A	N/A	N/A	N/A

Problem 2

Consider with the following set of functional dependencies:

• FD1:
• FD2:
• FD3:
• FD4:

Decompose R, if necessary, so that all the resultant relations are in BCNF. For the decomposition, pick in ascending order of the FDs, i.e., consider FD1, FD2, FD3, etc. in that order. For each BCNF relation, underline its primary key.

• FD1 is already in BCNF
• FD2 is not in BCNF as is not a candidate key so we decompose into and
• FD3 is already in BCNF
• FD4 no longer holds for either or so we ignore it, instead using because of FD2, so we decompose into and .

We've now exhausted all functional dependencies so we get the final solution , , .

Problem 3

One of the problems with BCNF is that it can lose functional dependencies after the decomposition is complete. In roughly 1 to 3 sentences, explain what it means to lose a functional dependency.

Losing a functional dependency means that through the process of decomposition the attributes of the functional dependency are split up into multiple relations so no one relation contains the constituting attributes anymore.

Problem 4

Consider and the following functional dependencies:

• FD1:
• FD2:
• FD3:
• FD4:
• FD5:

Follow the 3-step process discussed in class to find a minimal cover of the set of FDs.

Step 1:

Step 2:

Step 3:

can be removed because we obtain it from the closure through and . can be removed because we obtain it from the closure through and .