The Relational Model and Normalization Questions and Answers

Answer: D

The necessary and sufficient combination is Augmentation and Transitivity.

A proof of the Union rule from X → Y and X → Z:

1. Augment X → Y with X: XX → XY, that is X → XY.
2. Augment X → Z with Y: XY → YZ.
3. Transitivity on X → XY and XY → YZ gives X → YZ.

So augmentation and transitivity suffice, and reflexivity is not needed. Both are necessary: transitivity alone can only chain dependencies that already exist and can never put Y and Z together on one right-hand side, and without transitivity the two augmented dependencies cannot be joined. Reflexivity and augmentation alone cannot link X → XY to XY → YZ. All three rules work, but the question asks for the combination that is also necessary.

Answer: B

The attribute set that is NOT a superkey is \(VWXZ\).

A superkey is any attribute set that contains a candidate key. The primary key is \(VY\), so any set containing both \(V\) and \(Y\) is a superkey.

\(VXYZ\), \(VWXY\) and \(VWXYZ\) all contain \(V\) and \(Y\). \(VWXZ\) is missing \(Y\); with no other candidate key given, it cannot be assumed to identify tuples uniquely, so it is not a superkey.

Answer: A

The correct query is { p.pname | p ∈ players ∧ ∃t (t ∈ teams ∧ p.tid = t.tid ∧ t.tname = 'MI')}.

The result must range over players, so p must be a tuple of players. A player qualifies if there exists a team tuple t that is the player's own team (p.tid = t.tid, the join condition) and whose name is 'MI'. Option A states exactly this.

Options B and D take p from teams, which has no pname attribute, so they do not even return player names. Option C drops the join condition p.tid = t.tid: as long as some team called 'MI' exists, it returns the name of every player in the league, whichever team they play for.

Answer: B

The equivalent expression is \(\Pi_{P}(S \bowtie_{S.X=R.Q} R)\).

E returns each u such that some tuple \(\langle u, v\rangle\) is in R and some tuple \(\langle v, w\rangle\) is in S. The shared variable v is R's second attribute Q and S's first attribute X, so the join condition is R.Q = S.X, and the result keeps u, which is R.P.

Joins are commutative, so writing S first does not matter. The other options join on R.P, S.Y or a different pairing, each linking the wrong attributes: R.P = S.X matches u with S's first attribute, and S.Y = R.Q links v to S's second attribute.

Answer: B

The minimum is 2 cross products.

Without aggregation, 'at least 3 persons' must be expressed by finding three distinct persons in the same city. Rename gives three copies of Person, P1, P2 and P3, and two cross products combine them:

\[ \pi_{P1.city}(\sigma_{\theta}(P1 \times P2 \times P3)) \]

where \(\theta\) requires equal cities and pairwise different pids (P1.pid \(\lt\) P2.pid \(\lt\) P3.pid will do).

One cross product only compares two tuples at a time, which can show a city with at least 2 persons but not 3. Three or four cross products would work but are not the minimum.

Answer: C

The query that is NOT equivalent is \(r\ LOJ\ (\sigma_{B \lt 5}(s))\).

Q keeps only the r tuples whose B value is below 5, joined with their s tuple. Because r.B is a foreign key into s.B and there are no nulls, every r tuple has exactly one matching s tuple.

The left outer join of r with the filtered s keeps every r tuple: those with B below 5 join normally, but those with B of 5 or more find no partner and are kept with C padded with null. Those extra rows are not in Q.

The others match Q. Selecting \(B \lt 5\) after the join gives the same rows. With the foreign key, r LOJ s equals the plain join, so selecting after it is the same again. And \(\sigma_{B \lt 5}(r)\ LOJ\ s\) joins only the small r tuples, each of which has its partner in s.

Answer: A

The true statement is A relation with only two attributes is always in BCNF.

For a relation R(A, B), the only possible non-trivial dependencies are A → B and B → A. If A → B holds, A is a key; if B → A holds, B is a key; if neither holds, the key is AB and there is no non-trivial dependency to violate BCNF. In every case each determinant is a superkey.

The others are false. R(A, B, C) with AB → C and C → B has keys AB and AC, so every attribute is prime, yet C → B violates BCNF because C is not a superkey. A relation whose key is all of its attributes has no non-prime attribute at all. And BCNF decomposition can lose dependencies: decomposing that same relation into BCNF loses AB → C.

Answer: A

The degree (arity) is the number of attributes of its relation schema.

A relation schema R(A1, A2, ..., An) has degree n: a binary relation has two attributes, a ternary one three. The degree is fixed by the schema and does not change as rows are inserted or deleted.

The number of tuples stored in the relation is its cardinality, which changes with every insert and delete, and 'number of entries' describes the same thing. The number of distinct domains is not a defined property: several attributes can share one domain (two date columns, say), so it can be smaller than the degree.

Answer: D

Correct answer: Domain/key normal form

Fagin proved that a relation in domain/key normal form has no modification anomalies of any kind, and conversely that any relation free of them is in DK/NF. It is defined by requiring that every constraint on the relation follow from its domain and key definitions alone. The difficulty is that there is no general procedure for putting a relation into it.

Answer: A

Correct answer: True

First normal form forbids repeating groups and multi-valued entries, requiring each cell to hold a single indivisible value. A column storing a comma-separated list of phone numbers violates it.