超级键，候选键和首要的关键 [英] Superkey, candidate key & primary key

问题描述

任何善良的灵魂都可以通过下面的一个简单示例来澄清我的疑问，并确定超级键，候选键和主键吗?

我知道有很多帖子和网站解释了它们之间的区别.但看起来所有都是通用定义.

示例:

Student (StudentNumber, FamilyName, Degree, Major, Grade, PhoneNumber)

因此从上面的示例中，我可以知道StudentNumber是主键.

但是对于超级键，我有点困惑可以将哪些属性组合组合到超级键中?

对于候选密钥，我对给出的定义感到困惑，因为任何候选密钥都可以用作主密钥.

这是否意味着诸如PhoneNumber之类的属性是候选键并且可以是主键? (假设PhoneNumber仅属于一个学生)

感谢您的澄清！

解决方案

由于您不希望使用教科书定义，所以从广义上讲，超级键是一组唯一定义一行的列.

此集合可以具有一个或多个元素，并且一个表可以有多个超级键.通常，您可以通过功能依赖来完成此操作.

在您的示例中，我假设:

StudentNumber    unique
FamilyName     not unique
Degree     not unique
Major      not unique
Grade      not unique
PhoneNumber    not unique

在这种情况下，超级键是包含学生编号的任意组合.

因此，以下是超级键

StudentNumber
StudentNumber, FamilyName
StudentNumber, FamilyName, Degree
StudentNumber, FamilyName, Degree, Major
StudentNumber, FamilyName, Degree, Major, Grade
StudentNumber, FamilyName, Degree, Major, Grade, PhoneNumber
StudentNumber, Degree
StudentNumber, Degree, Major
StudentNumber, Degree, Major, Grade
StudentNumber, Degree, Major, Grade, PhoneNumber
StudentNumber, Major
StudentNumber, Major, Grade
StudentNumber, Major, Grade, PhoneNumber
StudentNumber, Grade
StudentNumber, Grade, PhoneNumber
StudentNumber, PhoneNumber

现在假设，如果PhoneNumber是唯一的(这些天谁共享电话)，那么以下也是超级键(除了上面列出的内容之外).

PhoneNumber
PhoneNumber, Grade, 
PhoneNumber, Major, Grade
PhoneNumber, Degree, Major, Grade
PhoneNumber, FamilyName, Degree, Major, Grade
PhoneNumber, Major
PhoneNumber, Degree, Major
PhoneNumber, FamilyName, Degree, Major
PhoneNumber, StudentNumber, FamilyName, Degree, Major
PhoneNumber, Degree
PhoneNumber, FamilyName, Degree
PhoneNumber, StudentNumber, FamilyName, Degree
PhoneNumber, FamilyName
PhoneNumber, StudentNumber, FamilyName

候选键只是最短"超键.返回第一个超级键列表(即电话号码不是唯一的)，最短的超级键是StudentNumber.

主键通常只是候选键.

Can any kind soul clarify my doubts with a simple example below and identify the superkey, candidate key and primary key?

I know there are a lot of posts and websites out there explaining the differences between them. But it looks like all are generic definitions.

Example:

Student (StudentNumber, FamilyName, Degree, Major, Grade, PhoneNumber)

So from the above example, I can know StudentNumber is a primary key.

But as for superkey, I'm a bit confused what combination of attributes could be grouped into the superkey?

As for candidate key, I'm confused by the definition given as any candidate key can qualify as a primary key.

Does it mean that attributes such as PhoneNumber are a candidate key and can be a primary key? (Assuming that a PhoneNumber only belongs to one student)

Thanks for any clarification!

解决方案

Since you don't want textbook definitions, loosely speaking, a super key is a set of columns that uniquely defines a row.

This set can have one or more elements, and there can be more than one super key for a table. You usually do this through functional dependencies.

In your example, I'm assuming:

StudentNumber    unique
FamilyName     not unique
Degree     not unique
Major      not unique
Grade      not unique
PhoneNumber    not unique

In this case, a superkey is any combination that contains the student number.

So the following are superkeys

StudentNumber
StudentNumber, FamilyName
StudentNumber, FamilyName, Degree
StudentNumber, FamilyName, Degree, Major
StudentNumber, FamilyName, Degree, Major, Grade
StudentNumber, FamilyName, Degree, Major, Grade, PhoneNumber
StudentNumber, Degree
StudentNumber, Degree, Major
StudentNumber, Degree, Major, Grade
StudentNumber, Degree, Major, Grade, PhoneNumber
StudentNumber, Major
StudentNumber, Major, Grade
StudentNumber, Major, Grade, PhoneNumber
StudentNumber, Grade
StudentNumber, Grade, PhoneNumber
StudentNumber, PhoneNumber

Now assume, if PhoneNumber is unique (who shares phones these days), then the following are also superkeys (in addition to what I've listed above).

PhoneNumber
PhoneNumber, Grade, 
PhoneNumber, Major, Grade
PhoneNumber, Degree, Major, Grade
PhoneNumber, FamilyName, Degree, Major, Grade
PhoneNumber, Major
PhoneNumber, Degree, Major
PhoneNumber, FamilyName, Degree, Major
PhoneNumber, StudentNumber, FamilyName, Degree, Major
PhoneNumber, Degree
PhoneNumber, FamilyName, Degree
PhoneNumber, StudentNumber, FamilyName, Degree
PhoneNumber, FamilyName
PhoneNumber, StudentNumber, FamilyName

A candidate key is simply the "shortest" superkey. Going back to the 1st list of superkeys (i.e. phone number isn't unique), the shortest superkey is StudentNumber.

The primary key is usually just the candidate key.

这篇关于超级键，候选键和首要的关键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！