Determining Super Key

Question

According to Wikipedia

Today's Court Bookings

• Each row in the table represents a court booking at a tennis club that has one hard court (Court 1) and one grass court (Court 2)
• A booking is defined by its Court and the period for which the Court is reserved
• Additionally, each booking has a Rate Type associated with it. There are four distinct rate types:
  • SAVER, for Court 1 bookings made by members
  • STANDARD, for Court 1 bookings made by non-members
  • PREMIUM-A, for Court 2 bookings made by members
  • PREMIUM-B, for Court 2 bookings made by non-members

The table's superkeys are:

• S1 = {Court, Start Time}
• S2 = {Court, End Time}
• S3 = {Rate Type, Start Time}
• S4 = {Rate Type, End Time}
• S5 = {Court, Start Time, End Time}
• S6 = {Rate Type, Start Time, End Time}
• S7 = {Court, Rate Type, Start Time}
• S8 = {Court, Rate Type, End Time}
• ST = {Court, Rate Type, Start Time, End Time}, the trivial superkey

Note that even though in the above table Start Time and End Time attributes have no duplicate values for each of them, we still have to admit that in some other days two different bookings on court 1 and court 2 could start at the same time or end at the same time. This is the reason why {Start Time} and {End Time} cannot be considered as the table's superkeys.

How is S1 = {Court, Start Time}, a super key?

Say on day 1, a member books court 1 from 11:00 to 12:00, and on day 2, a non member books court 1 from 11:00 to 12:00.

the records in the table would be {1,11:00,12:00, SAVER} and {1,11:00,12:00, STANDARD}

Clearly S1 = {Court, Start Time}, is not superkey. Or am I wrong?

Answer 1

This example is a poor choice because to understand what the table is supposed to hold involves unstated, although common sense, assumptions. It expects you to see that the table is only for one day--"Today"--and infer that on any day there will be no overlapping bookings. Ie no start-end time period for a court overlaps another one for the same court. (The text mentions different days when they mean different table values; but it doesn't matter to the example whether different values have to be on different days.)

It is also a poor choice for 3NF vs BCNF in particular. Of course it is subject to certain FDs (functional dependencies) and their associated JDs (join dependencies) relevant to 3NF vs BCNF. But the non-overlap of bookings is a separate constraint irrelevant to 3NF vs BCNF.

Say on day 1, a member books court 1 from 11:00 to 12:00, and on day 2, a non member books court 1 from 11:00 to 12:00.

When we say that a table value "satisfies" a constraint (eg FD) or "is subject to" a constraint or "has" a constraint or that a constraint "holds in" a table value we mean that the value makes the constraint true. When we say this about a table variable (base table) we mean that it is so for the variable's value in every database state. For this table, describing the current booking situation for "Today", any particular booking situation will be about one day--Today. So the kind of overlapping involving different days in your quote is not relevant to the constraints. Similarly each table value from different times in the same day will satisfy the constraints itself regardless of how the bookings have changed.

Under those circumstances, for any state of the table the four specified sets of columns are CKs (candidate keys):

• S1 = {Court, Start Time}
• S2 = {Court, End Time}
• S3 = {Rate Type, Start Time}
• S4 = {Rate Type, End Time}

Because bookings don't overlap, a subrow value for each of these column sets is unique under those columns. So they are superkeys. Since that's true for no smaller subsets of each, they are CKs. Since its true for no other column sets, there are not other CKs. Since every superset of a superkey is a superkey, the other listed sets are the other (non-CK) superkeys.

PS There are a few sections on that entry's talk page about the Tennis/Booking example and confusions on the page. The page has other poor examples. Eg it restructures the non-BCNF 3NF design to a BCNF design, but not by standard lossless decomposition to projections of the original (that join back to it). (It introduces a new column.) Eg it then also talks about preserving dependencies but that only makes sense when decomposing to projections of the original.