空的SQL表是否具有超键?每个SQL表都有一个吗? [英] Does an empty SQL table have a superkey? Does every SQL table have one?
问题描述
我知道超键在SQL中的含义，但是我有以下问题:
- 空的SQL表是否总是具有超键?
- 每个SQL表是否总是有一个超键?
TL; DR 超级键"是 RM(数据关系模型)术语.SQL中没有标准用法.SQL表的超键可以合理地非正式地称为您可以声明主键或 unique不为空的列集，以及当表位于以下位置时可以声明的{}最多一行(尽管您无法声明).合理地非正式地"因为SQL表不是RM关系.但是，如果一个表不包含重复的行且不包含空值，那么我们可以合理地说它是一个关系，并且像每个关系一样，它具有一个或多个超键.基本关系或关系表达式的超键的定义考虑了它可以保存的所有可能值，因此其当前值不影响其超键是什么.根据超键的定义，在空关系 value 中，每个属性子集都是一个超键.
关系超级键"
在数学中，关系"的一种含义是关系".是一组表格状的行状元组"，是值列表.它表示关系(船)/协会-也称为关系".在数学上.那就是"R"的位置.以"RM"表示来源，这是术语关系数据库"的来源.来自.(科德，1970年) (Chen 1976)在RM上下文中，关系"指的是关系".也是类似表格的内容，但通常包含一组元组"它们是成对的属性"对的集合.姓名&价值观.(或者它可能是数学关系或混合关系.)关系值&的两个RM感觉是超级键".关系变量或表达式.关系值的超键是一组属性，其中一个关系不包含具有该子单元的两行.关系变量或表达式的超键是一组属性，其中在每种情况/状态下，它都不包含具有该子单元的两行.因此，当变量可以容纳的所有值都具有该超级键时，该变量便具有该超级键.(在已出版的学术教科书中查找定义.请注意，当定义说出名称的对于每个"或对于所有"值时，它们表示在没有该值时满足该条件.对于某些"和存在"是指命名值，它们并不意味着这些名称必须命名为不同值.)
一个空的 value 恰好具有每个属性子集作为一个超键.包含变量的变量或表达式的超键的定义考虑了它可以求值的所有可能值，因此其当前值不会影响其超键是什么./p>
每个关系都有一个或多个超键:一个关系包含一组元组，因此一个元组值最多出现一次，因此所有属性上的一个子元组的值最多出现一次，因此所有属性集都是一个超级键.
SQL与关系式
SQL表不是关系.这让人想起数学和数学的混乱属性关系，允许重复和空值.因此，SQL数据库被称为关系"数据库.但是它们无法很好地体现RM.
由于SQL表与关系的相似性，涉及关系的术语被随意地应用于表.但是，尽管您可以借用术语并赋予它们SQL含义-值，表，FD(功能依赖项)，超键，CK(候选键)，PK(主键)，FK(外键)，联接和谓词NF(规范形式)，规范化，1NF等-您不能仅将这些SQL含义替换为RM定义，定理或算法中的那些单词，并获得明智或正确的信息.此外，RM概念几乎从不的SQL表示实际上告诉您如何正确地将RM概念应用于SQL数据库.他们只是模仿RM演示文稿，而忽略了对术语使用SQL含义是否会使事情变得毫无意义或无效.(几乎"是因为我<希望> 有一些.)
如果您替换关系"，通过表"(在某些情况下) RM超键 definitions 中(允许重复和/或空值)，您将获得SQL超键的定义，作为满足主键的列集>或唯一不为空的约束.对于某些 other RM超键定义，当一个表最多包含一行时，您将获得这些集合加{}.(因为它标识"了任何行.)(您可能只会发现使用第二种样式的人，但认为定义了第一种样式的用途.他们不会知道它们通过误解术语来滥用定义.)有些可能只是使用约束定义.您可能会发现"UK"(唯一键)用于这三个中的任何一个.
当表既不包含重复的行也不包含null时，我们可以将其解释为一种关系，其中行作为元组&列作为属性.然后我们可以合理地说表的超键是关系的超键.
"1NF"没有单一含义.归一化"也没有或未归一化"或"UNF"或"0NF"或与此有关的关系".
在建模和规范化时如何处理空值?
PS:"CK" 请勿将超键与CK混淆.CK是一个超级键，其中不包含更小的超级键.(因此，我们说CK是最小"或不可约"超键.)一个关系可以具有多个超键&.CKs.PK是一些被选作PK的CK.SQL 主键& unique not null 声明我们可以称为SQL超键的内容，但不一定是最小的，即我们可以称为SQL CK的内容.因此，当您听到"PK"在SQL上下文中，您必须找出它的意思是通过主键(也许不是SQL CK)声明的((SQL超键)列列表")和/或可分辨的SQL超键(可能通过或不通过 primary key 声明")"和/或专有SQL CK(可能通过或不通过主键声明")".而且您总是必须问什么钥匙"?方法.通常，SQL超级键-意味着什么.
PS:关系(船)" &关系"-关联?桌子?FK?在RM数据库中，每个(变量或表达式的)关系值表示关系/关系.但是关系"是指(有时，关系")也(以固定的方式)用于FK-不是在RM或ERM中，而是在 FK ，PK，CK，超键&其他约束是不需要的查询和更新.(它们是为了诚信.)
I know what a superkey means in SQL but I have the following questions:
- Does an empty SQL table always have a superkey?
- Does every SQL table always have a superkey?
TL;DR "Superkey" is a RM (Relational Model of Data) term. There's no standard use in SQL. The superkeys of an SQL table might reasonably informally be said to be the column sets that you could declare primary key or unique not null, plus maybe {} when a table holds at most one row (although you can't declare it). "Reasonably informally" because SQL tables are not RM relations. But if a table holds no duplicate rows and no nulls then we can reasonably say that it is a relation, and that, like every relation, it has one or more superkeys. The definition of superkey of a base relation or relation expression takes into account all possible values it can hold, so its current value doesn't affect what its superkeys are. Per the definition of superkey, in an empty relation value every subset of attributes is a superkey.
Relational "superkey"
In mathematics one meaning of "relation" is a table-like set of row-like "tuples" that are lists of values. It represents a relation(ship)/association--also called a "relation" in mathematics. That is where the "R" in "RM" comes from, which is where the term "relational database" comes from. (Codd 1970) (Date 2015) Similarly "ERM" (Entity-Relationship Model) comes from "relationship" as relation/association. (Chen 1976) In a RM context a "relation" is also table-like but usually holds a set of "tuples" that are sets of pairs of "attribute" names & values. (Or it might be a math relation or a mix.) There are two RM senses of "superkey"--of a relation value & of a relation variable or expression. A superkey of a relation value is a set of attributes where a relation does not contain two rows with that subtuple. A superkey of a relation variable or expression is a set of attributes where in every situation/state it does not contain two rows with that subtuple. So a variable has a certain superkey when all the values it can hold have that superkey.
(Find a definition in a published academic textbook. Note that when definitions say "for every" or "for all" values for a name they mean that such a condition is satisfied when there is no such value. Similarly when "for some" & "there exist(s)" refer to named values they don't mean that the names necessarily name different values.)
An empty value happens to have every subset of attributes as a superkey. The definition of superkey of a variable or expression involving variables takes into account all possible values it can evaluate to, so its current value doesn't affect what its superkeys are.
Every relation has one or more superkeys: A relation holds a set of tuples, so a tuple value appears at most once, so a value for a subtuple on all attributes appears at most once, so the set of all attributes is a superkey.
SQL vs Relational
An SQL table is not a relation. It is reminiscent of a jumble of math & attribute relations with duplicates and nulls allowed. So SQL databases are called "relational" but they poorly embody the RM.
Because of the resemblance of SQL tables to relations, terms that involve relations get sloppily applied to tables. But although you can borrow terms and give them SQL meanings--value, table, FD (functional dependency), superkey, CK (candidate key), PK (primary key), FK (foreign key), join, and, predicate, NF (normal form), normalize, 1NF, etc--you can't just substitute those SQL meanings for those words in RM definitions, theorems or algorithms and get something sensible or true. Moreover SQL presentations of RM notions almost never actually tell you how to soundly apply RM notions to an SQL database. They just parrot RM presentations, oblivious to whether their use of SQL meanings for terms makes things nonsensical or invalid. ("Almost" because I hope there are some.)
If you replace "relation" by "table" (duplicates and/or nulls allowed) in certain RM superkey definitions then you get a definition for SQL superkey as a column set that satisfies a primary key or unique not null constraint. For certain other RM superkey definitions you get those sets plus {} when a table holds at most one row. (Since it "identifies" any row.) (You will probably only find people who use a 2nd-style phrasing yet think it defines what a 1st-style phrasing does. And they won't know they are misusing definitions by misinterpreting terms.) Some might just use the constraint definition. You might find "UK" (unique key) used per any of the three.
When a table holds neither duplicate rows nor nulls we can interpret it as a relation, with rows as tuples & columns as attributes. Then we can reasonably say the table's superkeys are the relation's superkeys.
"1NF" has no single meaning. Nor does "normalized" or "unnormalized" or "UNF" or "0NF" or for that matter "relation".
What to do with null values when modeling and normalizing?
PS: "CK" Don't confuse superkeys with CKs. A CK is a superkey that contains no smaller superkey. (Hence, we say that a CK is a "minimal" or "irreducible" superkey.) A relation can have multiple superkeys & CKs. A PK is some CK chosen to be distinguished as PK. SQL primary key & unique not null declare what we could call a SQL superkey, but not necessarily a minimal one, what we could call an SQL CK. So when you hear "PK" in an SQL context you have to find out whether it means "(SQL superkey) column list declared via primary key (maybe or maybe not a SQL CK)" and/or "distinguished SQL superkey (maybe or maybe not declared via primary key)" and/or "distinguished SQL CK (maybe or maybe not declared via primary key)". And you always have to ask what "key" means. Usually, SQL superkey--whatever that means.
PS: "Relation(ship)" Get straight what you mean by each of "relation" & "relationship"--association? table? FK? In a RM database every relation value (of a variable or expression) represents a relation(ship)/association. But "relationship" (sometimes, "relation") is also (mis)used (in an entrenched way) for FK--not in the RM or ERM, but in pseudo-RM & -ERM methods that misinterpret/misunderstand/misrepresent them, whose roots predate them. (Unfortunately there is very poor RM education in the database industry.) FKs, PKs, CKs, superkeys & other constraints are not needed to query & update. (They are for integrity.)
这篇关于空的SQL表是否具有超键?每个SQL表都有一个吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
