Test 1 Review B

No, it is not a valid FD, because teams sometimes switch cities keeping the same mascot; thus, knowing the mascot, I cannot determine the city. An example would be the Oakland Raiders, who moved to Los Angeles in the 1982 while remaining the Raiders, and then they moved back to Oakland in 1995. In fact, they actually did win the Super Bowl both as the Oakland Raiders and as the Los Angeles Raiders; although even if they did not, such a circumstance could happen in the future. (Another example is the Colts, who moved from Baltimore to Indianapolis in 1984, preserving their mascot. They won both before (1970) and after (2006) that move.)

(However, both the year and the mascot would be enough to determine the city, so that would be a valid FD: The NFL ensures that no two teams have the same mascot.)

A functional dependency A → B applies to a relation if for any two rows agreeing on the attributes of A, those rows must necessarily agree on the attributes of B.

Examples for the given table include:

cust_id → name city state zip
zip → city state

One FD is invoice_id → customer_name, since only one customer receives each invoice.

Another potential example is the FD item_id → cost, asserting that each item has a fixed cost charged to all customers. This FD may not apply: Items' costs may change over time, and it may be that special discounts are negotiated for some particular customers. In that case, a reasonable alternative is invoice_id item_id → cost, asserting that on any invoice, only one cost for an item appears.

discipline → department
discipline title → number description

(I'd accept discipline number → title, too, but actually the titles vary on some topics courses (as CSCI 490).)

latitude longitude → elevation would likely apply: The latitude and longitude identify a particular location on the earth's surface, which would have an unique elevation associated with it.

1. { a, b, c, e }

2. { a, b, d }, { a, c, d }, { a, d, e }

1. {a, c, e}

2. The keys are {a, b, c} and {a, b, e}.

{ b, d }, { a, c, d }

Every nontrivial function dependency must have a left side that is a superkey (i.e., the left side of the functional dependency must actually determine all attributes of the relation).

rooms(student_id, last_name, hall, room_no, floor_area)

This is not in BCNF because hall room_no → floor_area is a valid FD: After all, hall and room_no together specify the exact room, so we know the floor area just from that information. However, its LHS {hall, room_no} is not a superkey, since some rooms have multiple students assigned to them and so student_id cannot be determined from just these two attributes.

books(call_number, title, author, birth_date)

It would be reasonable to suppose that the FD author → birth_date would apply to this relation, as each author can only be born in on one day (ignoring the issue of multiple authors with identical names). However, author is not itself a superkey, since the library may contain multiple books by some authors, so this relation is not in BCNF.

directory(name, address, phone)

If multiple people may be listed for the same telephone number, then phone is not itself a superkey, since it is not enough to identify a single row of the relation. But the FD phone → address might also apply to this relation, in which case the relation is not in BCNF, since the FD's left side (phone) is not itself a superkey.

It is not in BCNF since the FD's left side isn't a superkey — in fact, it is a proper subset of the key.

The relation would be decomposed into the two BCNF relations S(a, b, d) and T(a, c).

It is not in BCNF due to the a → b FD. We can decompose the relation into two BCNF relations, S(a, b) and T(a, c, d).

It is not in BCNF because the FD b c → d does not have a superkey on its left side: {b, c} is not a superset of {a, b}. (The FD c d → e has the same problem.)

To decompose, we might first start with the violating FD b c → d. Note that the closure of {b, c} is {b, c, d, e}, so we decompose R into (b, c, d, e) and (a, b, c). We'd then observe that the first relation isn't in BCNF due to the c d → e FD, so we'd split this relation further into (c, d, e) and (b, c, d). We thus end up with three relations: (a, b, c), (c, d, e), and (b, c, d).

1. Some non-BCNF relations cannot be broken into BCNF relations without breaking apart some FDs applying to the original non-BCNF relation.

2. With 3NF, an FD is also acceptable if its right side consists entirely of attributes appearing in some key for the relation.

The violating FDs are type → price and author → affiliation. The relation synthesizes into three 3NF relations.

writers(author, affiliation)
prices(type, price)
books(author, title, type)