确定超级密钥 [英] Determining Super Key
问题描述
- 表中的每一行代表一个网球俱乐部的法院预订，该网球俱乐部有一个硬场（法院1）和一个草场（法院2）
- 预订是由法院及其保留法院的期限定义的。
- 此外，每个预订都有与之相关的房价类型。共有四种不同的费率类型：
- SAVER，对于会员的第一法院预订
- STANDARD，对于非会员的第一法院预订-成员
- PREMIUM-A，针对会员的预订2
- PREMIUM-B，针对非会员的Court 2预订
表的超键为：
- S1 = {法院，开始时间}
- S2 = {法院，结束时间}
- S3 = {费率类型，开始时间}
- S4 = {费率类型，结束时间}
- S5 = {法院，开始时间，结束时间}
- S6 = {费率类型，开始时间，结束时间}
- S7 = {法院，费率类型，开始时间}
- S8 = {法院，费率类型，开始时间}
- ST = {法院，费率类型，开始时间，结束时间}，琐碎的超级键
请注意，即使在上表中的开始时间和结束时间中，
属性的每个值都没有重复的值，我们仍然必须
承认在另一些日子，法院1和法院
的两个不同预订可能同时开始或同时结束。这就是
不能将{Start Time}和{End Time}视为
表的超键的原因。
如何是S1 = {法院，开始时间}，是超级键吗？
在第1天说，一个成员从11:00到12:00预订法院1，在第2天，非会员从11:00到12:00到第1法院。
表中的记录为
{1,11：00,12：00，SAVER}， {1,11：00,12：00，STANDARD}
显然S1 = {法院，开始时间}，不是超键。还是我错了？
这个示例是一个糟糕的选择，因为要了解表是什么尽管常识性的假设，但仍应包含未陈述的假设。它希望您看到该表仅适用于一天（今天），并推断在任何一天都不会有重复的预订。即，同一法院的开始时间段与重叠时间段不会重叠。 （文本中提到的不同日期代表不同的表值；但是在示例中，是否必须在不同的日期使用不同的值并不重要。）
在第1天说，一名会员从1 11:00至12:00，以及第2天，非会员从11:00至12:00预定法院1。
当我们说时，表值满足约束（例如FD）或受约束或具有约束或约束存放在表 value 中，表示该值使约束成立。当我们说表 variable （基本表）时，是指每种数据库状态下的变量值都是如此。对于此表，它描述了今天的当前预订情况，任何特定的预订情况都将在一天左右-今天。因此，涉及报价中不同日期的重叠类型与约束条件无关。同样，无论预订如何更改，同一天不同时间的每个表值都将满足约束条件本身。
在这种情况下，，对于表的任何状态，四个指定的列集为CK（候选键）：
- S1 = {法院，开始时间}
- S2 = {法院，结束时间}
- S3 = {费率类型，开始时间}
- S4 = {费率类型，结束时间}
因为预订不重叠，每个预订的子行价值在这些列下，这些列集的唯一性。因此，它们是超级键。由于对于每个子集的较小子集都是如此，因此它们是CK。由于没有其他列集适用，因此没有其他CK。由于一个超键的每个超集都是一个超键，因此列出的其他集合是其他（非CK）超键。
PS 关于网球/预订示例，该条目的对话页面的几部分和页面上的混乱。该页面还有其他不良示例。例如，它将非BCNF 3NF设计重组为BCNF设计，但是没有通过标准的无损分解将其投影到原始投影（重新连接到原始投影）。 （它引入了一个新的专栏。）例如，然后它还讨论了保留依赖关系，但这仅在分解为原始的投影时才有意义。
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?
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.
这篇关于确定超级密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
