建议表格之间的设计关系 [英] Advice on design relations between tables
问题描述
我有关于音乐专辑的信息，我想在RDBMS表格中组织它们之间的关系。我有每个专辑的以下信息：艺术家，专辑名称，年份，标签，流派，风格，评级。到目前为止，我认为要制作4个表格 - 艺术家，专辑（名称，年份，标签，评级），流派1和流派2（每个流派都有自己的风格）。在图上看起来如下：
但是还不知道我怎样才能在专辑和其他三张桌子之间建立联系呢？也就是说，当我运行一个查询从艺术家中选择姓名我想收到一个相应的艺术家和流派风格的专辑
在这种情况下，我应该如何建立表之间的关系？ 您需要阅读关系数据库表格&查询。
您所说的表格之间的关系（原文如此）是FK（外键）。 FK表示表中列的列表的值显示为表中列的一些其他列的值，其形成PK（主键）或UNIQUE。您不需要声明或使用FK进行查询。像所有的限制，包括PK& UNIQUE，它们是为DBMS排除错误的数据库状态。
$ b
（基本或查询结果）表表示一个（业务/应用程序）关系（船）/关联。一个表包含来自关联的谓词（语句模板）的一些真正的命题（语句）的行。基表的谓词由DBA给出。查询结果表的谓词遵循基表，关系运算符&逻辑运算符在用户的查询表达式中。即JOIN的谓词是其谓词的AND; UNION OR;除AND NOT外; ON和WHERE条件和条件与JOIN谓词; etc 。
- 艺术家A有名字N
艺术家（A，N）
- 专辑A有名字N和...
相册（A，N，...）
- 流派G有名字N
类型（G，N）
- 艺术家创作的相册A2
ArtistAlbum（A，A2）
- 相册A是类型G
AlbumGenre（A，G）
SELECT DISTINCT ...
FROM
- album ag.A是一个流派ag.G和流派gG有名字gN ...
- AND ag.G = gG ...
AlbumGenre ag JOIN类型g ON aG = gG ...
注意不重要 专辑可以有多少种类型，或者一个类型可以有多少张专辑，或者一个类型是否可以有多个ID /或名称，查询仍会返回满足该谓词的行。 约束（包括FKs）不需要查询或更新。
请注意，我们可以应用相同的谓词变换加上其他变量来写约束。 （我为作者和专辑使用了 A ，所以我必须在这里给出一个重命名的例子。）
- 对于所有A& A2，如果艺术家A创作的相册A2，则艺术家A具有一些名称
- 对于所有A& A2，如果艺术家A为某个N创作了专辑A2，则艺术家A的名字为N
- 对于所有A& A2，如果（A，A2）在ArtistAlbum中，然后对于某个N，艺术家（A，N）
- 选择A从ArtistAlbum⊆选择A从艺术家
对外关键词ArtistAlbum（A）参考艺术家）
- 所有A& A2，如果艺术家A创作的专辑A2然后专辑A2具有某个名称
- 对于所有A& A2，如果艺术家A创作的相册A2然后对于某个N，...，相册A2具有名称N和...
- 对于所有A& A2，如果（A，A2）在ArtistAlbum中，然后对于相册
中的某个N，...，（A2，N，...），则选择A2从ArtistAlbum中⊆选择A作为A2从相册$ b $ （A2）参考资料（A）
- 所有A& G，如果专辑A是流派G，则专辑A具有一些名称和...
- 对于所有A& G，如果专辑A是流派G，那么对于某些N，...，专辑A具有名称N和...
- 对于所有A& G，如果AlbumGenre中的（A，G）然后对于专辑
中的某个N，...，（A，N，...），则选择G从AlbumGenre⊆SELECT A FROM Album
FOREIGN （A）参考专辑（A）
- 所有A& G，如果专辑A是流派G，则流派G具有一些名称
- 对于所有A& G，如果专辑A是流派G，则对于某些N，流派G的名称为N
- 对于所有A& G，如果（A，G）在AlbumGenre然后为某些N，（G，N）在类型
- 选择G从ArtistAlbum⊆选择G从风格
外国关键字AlbumGenre（G）参考类型G）
而不是有两个表专辑& AlbumGenre和他们的FK，我们可以只是他们的连接Album2，谓词是他们的谓词的AND /连词专辑A有名字N和...和专辑A是流派G 与 FOREIGN KEY专辑2（G）参考类型（G）。然后 normalization 会告诉我们，如果每个专辑有一个类型，那么这是一个好的设计，否则，原来更好。同样，Artist2将ArtistAlbum结合到Artist中（如果艺术家创作一个专辑，则是合理的）。或者两者ArtistAlbum& AlbumGenre到Album3（合理的，如果一个专辑有一个作者和一个流派）。但是，不管所有重要的查询&更新是谓词，而不是基数或约束。因此，您的设计缺少类似ArtistAlbum& AlbumGenre。 （你可能想把它与上面的其他表结合起来。）
PS你的问题不清楚genre，genre1& genre2。
I have information about music albums that I want to organise in RDBMS tables with relations between them. I have the following info for each album: artist, album name, year, label, genre, style, rating. So far I think to make 4 tables - artists, albums (name, year, label, rating), genre1 and genre2 (each genre with its styles). On the diagram it looks as follows:
But don't know yet how can I establish a connection between albums and the other three tables? I.e., when I will run a query select name from artists I would like to receive an album with corresponding artist and genre-style.
How should I establish a relation between the tables in this case?
You need to read an introduction to relational database tables & querying.
The "relations" [sic] between tables that you are talking about are FKs (foreign keys). A FK says values for a list of columns in a table appear as values for some other list of columns in a table that form a PK (primary key) or UNIQUE set there. You don't need to declare or use FKs to query. Like all constraints, including PK & UNIQUE, they are for the DBMS to exclude erroneous database states.
A (base or query result) table represents a (business/application) relation(ship)/association. A table holds the rows that make some true proposition (statement) from an associated predicate (statement template). The predicate of a base table is given by the DBA. The predicate of a query result table follows from the base tables, relation operators & logic operators in the user's query expression. Ie the predicate of a JOIN is the AND of its tables' predicates; UNION the OR; EXCEPT the AND NOT; ON and WHERE of a condition AND that condition in with the JOIN predicate; etc.
-- artist A has name N
Artist(A, N)
-- album A has name N and ...
Album(A, N, ...)
-- genre G has name N
Genre(G, N)
-- artist A authored album A2
ArtistAlbum(A, A2)
-- album A is of genre G
AlbumGenre(A, G)
SELECT DISTINCT ...
FROM
-- album ag.A is of genre ag.G AND genre g.G has name g.N ...
-- AND ag.G = g.G ...
AlbumGenre ag JOIN Genre g ON a.G = g.G ...
Notice that it does not matter how many genres an album can have or how many albums a genre can have or whether a genre can have multiple ids and/or names, the query still returns the rows that satisfy that predicate. Constraints (including FKs) are not needed to query or update.
Notice that we can apply the same predicate transforms plus others to write constraints. (I used A for both authors & albums so I'd have to give a renaming example here.)
-- for all A & A2, if artist A authored album A2 then artist A has some name
-- for all A & A2, if artist A authored album A2 then for some N, artist A has name N
-- for all A & A2, if (A, A2) in ArtistAlbum then for some N, Artist(A, N)
-- SELECT A FROM ArtistAlbum ⊆ SELECT A FROM Artist
FOREIGN KEY ArtistAlbum (A) REFERENCES Artist (A)
-- for all A & A2, if artist A authored album A2 then album A2 has some name
-- for all A & A2, if artist A authored album A2 then for some N, ..., album A2 has name N and ...
-- for all A & A2, if (A, A2) in ArtistAlbum then for some N, ..., (A2, N, ...) in Album
-- SELECT A2 FROM ArtistAlbum ⊆ SELECT A AS A2 FROM Album
FOREIGN KEY ArtistAlbum (A2) REFERENCES Album (A)
-- for all A & G, if album A is of genre G then album A has some name and ...
-- for all A & G, if album A is of genre G then for some N, ..., album A has name N and ...
-- for all A & G, if (A, G) in AlbumGenre then for some N, ..., (A, N, ...) in Album
-- SELECT G FROM AlbumGenre ⊆ SELECT A FROM Album
FOREIGN KEY AlbumGenre (A) REFERENCES Album (A)
-- for all A & G, if album A is of genre G then genre G has some name
-- for all A & G, if album A is of genre G then for some N, genre G has name N
-- for all A & G, if (A, G) in AlbumGenre then for some N, (G, N) in Genre
-- SELECT G FROM ArtistAlbum ⊆ SELECT G FROM Genre
FOREIGN KEY AlbumGenre (G) REFERENCES Genre (G)
Instead of having two tables Album & AlbumGenre and their FK we could have just Album2 that is their join, with predicate that is the AND/conjunction of their predicates album A has name N and ... and album A is of genre G with FOREIGN KEY Album2 (G) REFERENCES Genre (G). Then normalization would tell us that if there is one genre per album then that's an OK design but otherwise that the original is better. Similarly for Artist2 combining ArtistAlbum into Artist (reasonable if an artist authours one album). Or both ArtistAlbum & AlbumGenre into Album3 (reasonable if an album has one author and one genre). But regardless all that matters to query & update are the predicates, not the cardinalities or constraints.
So your design is missing appropriate predicates/columns/tables like those of ArtistAlbum & AlbumGenre. (Which you might want to combine with other tables as above.)
PS Your question is not clear about "genre", "genre1" & "genre2".
这篇关于建议表格之间的设计关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
