## Question

1. Given a relation R(V,W,X,Y,Z) and its functional dependencies:

V → W

WX → Z

ZY → V

provide answers to the following:

a. List all keys for R.

b. Is R in 3NF?

c. Is R in BCNF?

2. Suppose you are given a relation R with four attributes, R(A,B,C,D) and its FDs:

AB → C

AB → D

C → A

D → B

Assuming these are the only FDs that hold in R, answers the following:

a. Identify the candidate key(s) for R.

b. Identify all the normal forms that R satisfied (1NF, 2NF, 3NF, or BCNF).

c. If R is not in BCNF, decompose it into a set of BCNF relations that preserve the dependencies.

3. Suppose you are given a relation R(A,B,C,D). For each of the following sets of FDs (labelled as (i), (ii), (iii) below), assuming they are the only dependencies that hold in R, do the following:

a. Identify the candidate key(s) in R.

b. State whether the proposed decomposition of R is good or not, and briefly explain why or why not.

i. B → C, D → A; decompose into BC and AD.

ii. AB → C, C → A, C → D; decompose into ACD and BC.

iii. A → BC, C → AD; decompose into ABC and AD.

4. Consider the relation R(A, B, C, D, E) with the set of FDs: F = {AB → E, CD →E,

A → C, C → A}. Decompose R into BCNF. Write and explain all steps in your decomposition.

5. Given the following two relations:

Team(TeamID: string, TeamName: string, RPI: real, Ranking: integer, ConferenceID: integer)

Conference(ConferenceID: integer, ConferenceName: string)

write relational algebra for the following queries:

a. Display TeamName and Ranking for all the teams belonging to the conference named ‘Big 10’.

b. Display TeamName, RPI and ConferenceName for all the teams.

## Solution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Question 1The given relational schema is, R(V, W, X,Y,Z) and the given set of functional dependencies are,

V → W

WX → Z

ZY → V

a. Now, the closure for each of the attributes of the given relational schema are calculated as,

V+ = {V, W} as V -> W

Now, the closure is updated as, VX+ = { V, W, X, Z} as V -> W and WX -> Z

Now, another closure is, ZYX+ = (Z, Y, X, V, W}

So, one of the keys for R is (ZYX).

As the attributes X and Y is not present on the right side of any of the functional dependencies, hence, all keys must have these two attributes. Pairing the remaining attributes with XY the rest of the keys will be calculated.

WXY+ = {W, X, Y, Z, V} as...