ER图实现Actors数据库 [英] ER Diagram that implements Actors Database

问题描述

注意：这是一个粗糙的副本我没有包括约束，弱实体，...，等。我仍然需要对这个问题有充分的了解。

问题：

1. 为了跟踪剧院公司管理表演者的情况，两个戏剧公司中的表演者必须为其他实体集中的每个实体设置唯一的代码以跟踪他们吗？




2. can start_Location只需指向戏剧公司实体的地点？




3. 演员可以在某个地方出生




4. 到目前为止我的关系是否有意义？




5. 我可以创建一个称为城镇，州/部门/地区的属性。

省？

请注意：如果我要编辑和更新我的图表有更多的问题和这样的...

我会感激任何建议或提示。

p>

问题信息：

演员出生在某个地方，此信息是强制性的）。

我们在数据库中只存储演员生活的最后知道的地方。

我们需要演员的以下信息：演员号，演员姓名，演员出生时的日期和演员死亡的日期（检查是否死亡>出生）。

演员是演员，或/和剧院导演。
我们为表演者存储他/她开始表演的日期。

我们为剧院导演存放他/她最后一次作为戏剧导演时的日期

DBActors有以下类型的戏剧：戏剧，喜剧和悲剧。

我们喜欢存储以下数据：play's number，play's title，play的简短描述，写作时的年份， p_date_p，date）。

对于戏剧，我们还存储戏剧类型，主要正字符的名称和主要负字符的名称。

戏剧类型是以下之一：
classical，medieval，renaissance，十九世纪，modern和
contemporary

对于喜剧，我们存储喜剧类型，主
字符的名称和第二个字符的名称

喜剧类型是以下之一：古代mroman，古希腊语，闹剧，幽默喜剧，礼节喜剧 $ bcommedia dell'arte和荒诞剧场;

对于悲剧，我们存储悲剧类型（t_type，varchar（20））， main
character

悲剧类型是以下之一：Greek，Roman，Renaissance，Neo
-classical 和现代

一个剧本是由一个或多个剧作家写的
我们可能不知道某些戏剧的剧作家。

我们在数据库中存储所有已知的剧本，即使他们没有被执行（衣柜剧）

一些演员也是剧作家。

我们将所有已知的mdramatists存储在数据库中。

演员由独特的戏剧公司

他/她将在一年内留在同一家公司。我们在数据库中存储他/她被剧院公司雇用的年份
（小整数）

这可能是演员改变剧院公司在那里他/她是
在他/她的生活中工作很多次。一个演员可能在不同年份被同一家公司多次雇用。他/她可以在
中演出一个或多个戏剧（至少一个）

由戏剧公司呈现。

演员可能由戏剧公司录用，并由另一家戏剧公司演出。

这是不寻常的，但可能同样的表演者在不同的戏剧公司提出的同样的戏剧
。

相同的戏剧可以由一个或多个不同的戏剧公司进行。

我们喜欢在数据库中存储戏剧公司开始播放的日期。

我们需要为剧作家存储他/她的剧作家编号，他/她的名字

剧作家写了一个或多个剧本（至少一个）。

每个剧院公司
的数据库是：剧院公司编号，剧院公司名称，
剧院公司开始的日期。

我们存储在数据库
剧院公司开始的第一个位置（地点）

在同一地点可能有多家剧院公司。

剧院公司必须至少聘请一位演员。

每家戏剧公司都有独特的戏剧导演。
他/她在特定日期开始工作。

同一家剧院公司可能有不同的戏剧导演，但在不同的时间，同一个剧院导演在不同的时间管理不同的bbb剧团公司

同一家戏院总监可能会在不同的日期管理相同的
剧团公司。

要存储的地方信息是：地点号码，城镇和州/部门/省，地方国家

解决方案

以下是我对您的问题的回答：

1. 每当您查看两个表格并看到多对多关系时，这个问题很容易使用链接器表。也称为联结表是包含来自同一数据库中的两个或更多其他数据库表的公共字段的数据库表。它与其他表中的一对多关系的许多方面。连接表有许多名称，其中包括交叉引用表，桥接表，连接表，映射表，交集表，链接表，多对多解析器，链接表，配对表，转换表，交叉实体或关联表。维基百科示例您在之前的问题中看到我使用这些表。在这种情况下，你说的演员可以管理我的剧院公司和剧院公司，也管理许多演员。这是一个许多人，所以如果你在这两个表之间创建一个链接表之间的每个relastionship之间，你将添加一个新行在链接表中只包含剧院公司id和演员id。如果一个演员是由许多戏剧公司管理，那么你会添加几行到链接表，每个持有相同的演员ID，但每一行有不同的剧院公司的id。


2. 是的，你可以直接把start_Location点放到地方。这意味着Start_Location属性必须是将剧院公司指向相关地方记录的主键（PK）的外键（FK）。


3. 一切都可以诞生在一个地方，但就像上面，你需要一个列在Actor，这是一个FK到地方表的PK。您可以将此列称为Birth_Place，它所拥有的是与该演员出生地点相关的记录的PK。此列也需要为NOT NULL，因为所有actor都需要一个Birth_Place。


4. 到目前为止，看起来你的图将工作来解决这个问题，是的。



5. 您正在擅长移除冗余。你的图表看起来不错。唯一的建议，我会做的是为什么你有一个游戏桌，然后3个单独的播放类型表？为什么不把它们一起添加在称为Play的表上。它会坐在你的图表中Play当前所在的位置，并包含它已经具有的相同属性，但是你也添加了以下内容：

   
   
   

   a。类型 - 将是一个字符串，你可以放置戏剧，喜剧或贸易，所以你会知道什么类型的游戏它是。此外，这将允许您将未来的播放类型添加到播放表，而不必添加一个新的表到数据库。

   
   
   

   b。 Sub_Type - 也可以是一个字符串，并保存您目前在单独的表下的类型。它们在每个表中基本上是相同的属性，并且根据父类型只保存不同的类型描述符。

   
   
   

   c。 Main_Character - 将是一个包含主要字符的字符串，因为在您的三个独立的表中，您有主要字符。你只是打电话给他们3个独立的东西。 （得到我要去的方向？）

   
   
   

   d。 Secondary_Character - 将是一个包含辅助字符的字符串。你在你的戏剧和喜剧中有一个次要角色，但在你的贸易中不是这样，所以在贸易记录中，这个列将被清空。看看我在那里做了什么？你现在有一个表，你曾经有4，在一个表中，你可以检索所有相同的信息，你在这4个单独的表。



6. 您可以随心所欲，但我假设您的意思是最佳做法，通常会被视为最佳做法将这个单一属性分成它的简单属性子部分。 I.E.使其成为组合属性。

Note: This is a rough copy i didnt include constraints, weak entities, ..., etc yet. I still need to have a solid understanding of this question.

Questions:

1. To keep track of what theater company manages performer, what performer is in two theatre companies do i have to make a unique code for each entity set in other entity sets to keep track of them?

2. Can start_Location simply point to Place for the theatre company entity?

3. Can an Actor be Born in a place or does it have to have a attribute that points to place?

4. Do my relationships so far make sense?

5. Are there any redundant attributes such as Short_Descript in Plays?

6. Can i make an attribute in Place called "Town, State/Department/Province"? Or does it have to be a composed attribute?

Please note: I will be editing and updating my diagram if I have more questions and such...

I would appreciate any suggestions or hints.

ERD:

Question Information:

An actor is born in a place and he/she lives presently in a place (this information is mandatory).

We store in the database only the last known place where the actor lives.

We need the following information for an actor: actor number, actor name , date when actor was born, and date when actor died (check if died > born).

An actor is a performer, or/and a theater director. We store for performer the date when he/she started to perform.

We store for theater director the date when starts his/her last employment as theater director

We consider in DBActors the following types of plays: drama, comedy and tragedy.

For each we like to store the following data: play’s number , play’s title , play’s short description , year when it was written ,date when it was first presented on stage(p_date_p, date).

For dramas we store also the drama type,name of the main positive character, and name of main negative character.

The drama type is one of the following: "classical", "medieval", "renaissance", "nineteenth-century", "modern", and "contemporary"

For comedies we store the comedy type, the name of main character , and the name of the second character

The comedy type is one of the following: "ancient mroman", "ancient greek", "farce", "comedy of humors", "comedy of manners", "commedia dell’arte", and "theater of absurd";

For tragedies we store the tragedy type(t_type, varchar(20)),and name of main character

The tragedy type is one of the following: "Greek", "Roman", "Renaissance", "Neo -classical", and "Modern"

A play is written by one or many dramatists It is possible that we do not know the dramatist for certain plays.

We store in the database all known plays even if they were not performed ("closet plays")

Some actors are also dramatists.

We store in the database all known mdramatists.

An actor is hired by a unique theater company at any timestamp

He/she will stay in the same company the whole year when he/she was hired.

We store in the database the year when he/she was hired by the theater company (small integer)

It is possible that the actor changes the theater company where he/she is working during his/her life many times. It is possible that an actor is hired by the same company many times in different years. He/she can perform in one or many plays (at least one)

which are presented by theater companies.

It is possible that an actor is hired by a theater company and performs in a play presented by another theater company.

It is unusual but possible that the same performer plays in the same play presented by different theater companies. A theater company performs/presents one or many plays every year.

Same play can be performed by one or many distinct theater companies.

We like to store in the database the date when the play starts to be performed by a theater company.

It is possible that the same play is performed by different theater companies starting at same date.

We need to store for a dramatist his/her dramatist number,his/her name.

A dramatist wrote one or many plays(at least one).

The information to be stored in the database for each theater company is: theater company number,theater company name , date when the theater company started.

For each theater company we store in the database the first location (place) where the theater company started

There might be more than one theater company starting in the same place.

A theater company must hire at least one actor.

Each theater company has a unique theater director. He/she starts his/her work at a specific date.

It is possible that the same theater company has different theater directors but at distinct times and the same theater director manages different theater companies in distinct times(never at the same date).

It is possible that the same theater director manages the same theater company at different dates.

The information to be stored for place is: place number, town and state/department/province, place country

解决方案

Here are my responses to your questions:

1. Whenever you look at two tables and see a Many to Many relationship, you can solve the problem easily using a linker table. Also known as a junction table "is a database table that contains common fields from two or more other database tables within the same database. It is on the many side of a one-to-many relationship with each of the other tables. Junction tables are known under many names, among them cross-reference table, bridge table, join table, map table, intersection table, linking table, many-to-many resolver, link table, pairing table, transition table, crosswalk, associative entity or association table." Wikipedia example You saw me use these tables in your previous question. In this case you are stating that an actor can be managed my many Theater Companies and A Theater Company and also manage many Actors. This is a many to many so if you created a link table in between those tables for every relastionship between the two you’d add a new row in the link table that only contains a theater Company id and an actor id. If an actor was managed by many theater companies then you’d add several rows to the link table each holding the same actor id but each row having a different theater company’s id.
2. Yes, you can have start_Location point directly to place. This means that that Start_Location attribute must be a Foreign Key (FK) pointing the theater company to the Primary Key (PK) of the related Place record.
3. By all means an actor can be born in a place, but just like above, you need a column in Actor, that is a FK to the Place Table’s PK. You could call this column Birth_Place and all it’d hold is the PK of the record in Place that relates to the actor’s birth place. This column would also need to be NOT NULL because all actor’s need a Birth_Place.
4. So far it seems like your diagram will work to solve this problem, yes. Just see question 1’s answer for that follow up addition.

5. You’re getting good at removing redundancies. Your diagram looks good. The only suggestion, I’d make is why do you have a play table and then 3 separate play type tables? Why not add them together in on Table called Play. It’d sit exactly where Play currently sits in your diagram and contain the same attributes it already does, but you also add the following:

   a. Type – Would be a string that you could place "Drama", "Comedy", or "Tradegy" in so you’d know exactly what type of play it is. Also this would allow you to add future play types to the plays table and not have to add a whole new table to the DB.

   b. Sub_Type – Would also be a string and hold the type that you currently have under the separate tables. They are all essentially the same attribute in each table and would just hold different type descriptors depending on the parent Type.

   c. Main_Character – Would be a string that holds the main character, because in your three separate tables, you have main characters. You’re just calling them 3 separate things. (get the direction I’m going in here? )

   d. Secondary_Character – Would be a string that holds the secondary character. You have a secondary character in your dramas and comedies, but non in your tradegies so in tradegy records this column would wind up being null. See what I did there? You now have one table where you used to have 4, and in that one table you can retrieve all the same information you had in those 4 separate tables. Hopefully that’ll make your life easier.

6. You can do whatever you like, but I’m assuming you mean by best practices and it would be generally considered best practice to separate this single attribute into it’s Simple attribute sub parts. I.E. make it a composed attribute.

这篇关于ER图实现Actors数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！