如何设计带有主键和多值属性的表?[英] How to design table with primary key and multivalued attribute?

本文是小编为大家收集整理的关于如何设计带有主键和多值属性的表?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我对数据库设计感兴趣,现在阅读相应的文献. 通过这本书,我面对了一个奇怪的例子,使我感到不确定. 有一个关系

在此处输入图像说明

在此表中,我们有一个复合主键(StudentId,活动).但是ActivityFee部分取决于表的键(活动 - > ActivityFee),因此作者建议将此关系分为另外两个关系:

在此处输入图像说明

现在,如果我们看一下student_activity,活动将成为外键,关系仍然具有复合主键.

我们有整个列定义复合主键的表,可以吗?

如果不是,在这种情况下我们该怎么办? (可能定义一个替代键?)

如何处理多估属性(在我们的情况下)以消除可能的数据异常的好方法?

推荐答案

复合候选密钥没有错. (如果您的参考不会以候选密钥来讨论,则在任何其他情况下都在谈论主要键,而不是碰巧只有一个候选密钥,请获取新的参考.)

您的文字将告诉您什么是好是坏设计.对于您注意到的每个财产可能是"不好的",就没有必要担心.它目前正在解决的"良好"是由"归一化"给出的.

" Activity"不是"多相关属性". "多相关"属性是一个非关系概念.该术语经常但错误地用来表示非依赖"表"中的"属性",该术语以某种方式(从未解释过)每个条目都不超过一个,或者对于具有的关系表中的一列具有多个相似零件的值(设置,列表,包,表等),以某种方式(从未解释过)不适用于说明,字符串和数字,或对于具有多个值的关系表中的列以某种方式(从未解释过)的不同零件(记录,元组等)不适用于日期. (有时甚至被误用,是指具有相似名称和值的一堆属性,应由每个原始名称的行替换为单个属性.)(这些只是不需要的设计的情况.)"多估" GET用作类似滥用/滥用术语的反词

在列或表中,具有相同的(值或)值的(值或)子行出现不止一次.同样,您的参考会告诉您什么是好设计.

其他推荐答案

仅由复合密钥组成的表与您的业务需求符合您的业务.

.

活动不是多价属性.每个元组的活动都有一个值.

本文地址:https://www.itbaoku.cn/post/597320.html

问题描述

I'm interested in database design and now reading the corresponding literature. Through the book, i have faced a strange example that makes me feel uncertain. There is a relation

enter image description here

In this table we have a composite primary key (StudentID, Activity). But ActivityFee is partially dependent on the key of the table (Activity -> ActivityFee), so the author suggests to divide this relation into two other relations:

enter image description here

Now if we take a look at the STUDENT_ACTIVITY, Activity becomes a foreign key and relation still has a composite primary key.

We've got the table in which the whole columns defines a composite primary key, is it OK?

If it is not, what should we do in this case? (probably define a surrogate key?)

What is a good way to deal with multivalued attribute (Activity in our case) in order eliminate possible data anomalies?

推荐答案

There is nothing wrong with a composite candidate key. (If your reference doesn't talk in terms of candidate keys, ie if it talks about primary keys in any other case than when there just happens to be only one candidate key, get a new reference.)

Your text will tell you what is good and bad design. There is no point in worrying about every property you notice about a relation that it might be "bad". The kind of "good" it is currently addressing is that given by "normalization".

"Activity" is not a "multivalued attribute". A "multivalued" attribute is a non-relational notion. The term is frequently but incorrectly used to mean either an "attribute" in a non-relational "table" that somehow (which is never explained) has more than one entry per "row", or for a column in a relational table that has a value with multiple similar parts (set, list, bag, table, etc) that somehow (which is never explained) doesn't apply to say, strings & numerals, or for a column in a relational table that has a value with multiple different parts (record, tuple, etc) that somehow (which is never explained) doesn't apply to, say, dates. (Sometimes it is even misapplied to mean a bunch of attributes with similar names and values, which ought to be replaced by a single attribute with a row for each original name.) (These are just cases of unwanted designs.) "Multivalued" gets used as an antonym of the similarly misused/abused term "atomic".

Having the same (value or) subrow of values appear more than once in a column or table is, again, neither good or bad per se. Again, your reference will tell you what is good design.

其他推荐答案

A table which consists only of a composite key is perfectly OK if that matches your business requirement.

Activity is not a multivalued attribute. There is a single value for activity for each tuple.