1 / 12

Fig 3.12 Relation Movie with new attributes representing the owning information

Fig 3.12 Relation Movie with new attributes representing the owning information Fig 3.13 The relation Movie with star information. 3.3 From E/R Diagram to Relational Design

howard
Download Presentation

Fig 3.12 Relation Movie with new attributes representing the owning information

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Fig 3.12 Relation Movie with new attributes representing the owning information Fig 3.13 The relation Movie with star information

  2. 3.3 From E/R Diagram to Relational Design When creating a database, we begin with a design phase, in which we answer question about what information will be stored , how information elements will be related to one another, and so on. The design phase is followed by an implementation phase using a real database system. Since the great majority of commercial database systems use the relational model, we might suppose that the design phase should use this model. However, in practice it is often easier to use the E/R model to make our design, because E/R model is more intuitive and easier to understand both for designers and end users than the relational model. If so, one of our task is to see how to translate a E/R design to the relational design. In this section, we discuss the conversion from the E/R model to the relational model. 3.3.1 From Entity Sets to Relations Let us first consider entity sets that are not weak. We shall take up the modifications needed to accommodate weak entity sets in Section 3.3.3. For each non-weak entity set, we shall create a relation of the same name and with the same set of attributes. This relation will not have any indication of the relationships in which the entity set participates; we’ ll handle relationships with separate 3.3 从E/R图到关系模型设计 当创建一个数据库时，我们从设计阶段开始，在这个阶段我们要确定哪些信息要存贮，信息元相互之间如何建立联系等方案。此后，就是建立一个具体数据库的实现阶段，因为绝大多数商品化的数据库系统采用关系模型，我们或许会想设计阶段也是采用关系模型来设计。但是实践中，使用E／R模型来设计更容易一些，因为E／R模型无论对设计者或者是最终用户都更直观和更易于理解。如果我们采用E／R模型来进行设计，我们的一个任务就是要搞清楚如何将E／R设计转换为关系模型的设计。 3.3.1 从实体集到关系 我们首先考虑非弱实体集。我们将在3.3.3小节中做一调整，使这里讨论的方法对弱实体集也适用。

  3. relations. Example 3.10: Consider the three entity sets Movies, Stars and Studios from Fig. 3.15. The attributes for the Movies entity set are title, year, length, and filmType. As a result, the relation Movies looks like the relation Movie of Fig.3.1 with which we began Section 3.1. Figure 3.15 E/R diagram for the movie database 对于非弱实体集，我们将建立一个关系，使用与E／R模型中的实体集相同的名称和属性。在这个关系中没有任何与该实体有关的联系的指示信息。我们将单独用一个关系来处理实体间的联系。

  4. 3.3.2 From E/R Relationships to Relations Relationships in the E/R model are also represented by relations. The relation for a given relationship R has the following attributes: 1. For each entity set involved in relationship R, we take its key attribute or attributes as part of the schema of the relation for R. 2. If the relationship has attributes, then these are also attributes of relation R. If one entity set is involved several times in a relationship, then we must rename the attributes to avoid name duplication. Similarly, should the same attribute name appear twice or more among the attributes of R itself and the entity sets involved in relationship R, then we need to rename to avoid duplication. Example 3.12: Consider the relationship Owns of Fig.3.15. This relationship connects entity sets Movies and Studios. Thus,for the relation schema of relation Owns we use the key for Movies, which is title and year, and the key of Studios, which is name. A sample of this relation is 3.3.2 E/R联系到关系的转换 E／R模型中的联系也可以用关系来表示。对于给定的联系R，它所对应的关系具以下属性： 1. 联系R涉及到的每一个实体集的键码属性应该是R对应的关系模式的一部分； 2. 如果R有属性，这些属性也应该是R对应的关系的属性； 如果一个实体集在联系R中出现多次，必须为每次出现的属性改名，以免出现重名属性。同样，如果同名属性在R本身或R涉及的实体集的属性中出现两次或两次以上，也必须改名， 以免重名。 例3.12 考虑图3.15中的联系Owns。它把实体集Movies和Studios联系起来。因此，我们使用Movies的键码，即title和year，和Studios的键码，即name，来表示Owns的

  5. We have chosen the attribute studioName for clarity; it corresponds to the attribute name of Studios. Notice how the relation above, plus the relation of Example 3.10 that we constructed for the entity set Movies(and which is shown in Fig. 3.1), contains exactly the information in the relation of Fig 3.12 that we constructed for the class Movie,excluding its stars property. Example 3.13: Similarly, the relationship Stars-In of Fig. 3.15 can be transformed into a relation with the attributes title and year (the key for Movie) and attribute starName, which is the key for entity set Stars. Figure 3.16 shows a sample relation Stars-In. Notice how this relation plus Fig. 3.1 contains the information of Fig. 3.13, but they do so without the repetition of nonkey attributes of the Movie class (attributes length and filmType) that flaws the schema of Fig. 3.13. 关系模式：该关系的一个例子如左图所示。 为了清楚起见，我们采用属性studioName，它对应于Studio中的name属性。 请注意：上面的关系加上例3.10中为实体集Movie构造的关系（见图3.12），完全包含了为例3.6的类Movie构造的关系（见图3.12）中的信息。只是把其stars特性排除在外。 例3.13 同样，图3.15中的联系Star-In也可以转换为具有属性title和year（Movie的键码）以及属性starName（实体集Star的键码）的关系。图3.16 是关系Stars-In的一个例子。注意，该关系加上图3.1包含了图3.13中的信息，但它们却没有重复的Movie类的非键码属性（length和filmType），而没有非键码属性将有损图3.13中的关系模式。

  6. Fig 3.16 A relation for relationship Stars-In It seems that the year is redundant in Fig. 3.16. However, that is only because these movie titles are unique. Had there been several movies of the same title, like “King Kong”, we would see that the year was essential to sort out which stars appear in which version of the movie. Example 3.14: Multiway relationships are also easy to convert to relations. Consider the four-way relationship Contracts of Fig. 3.17, involving a star, a movie, and two studios - the first holding the star’s contract and the second contracting for that star’s services in that movie. We represent this relationship by a relation Contracts whose 似乎年份year在图3.16中是冗余的。不过，这只是由于这些电影都不重名的缘故。如果有几部电影重名，比如都叫“King Kong”，我们将会看到year对找出哪些影星出演某部电影的哪个版本有决定性的作用。 例3.14 多向联系也容易转换为关系，让我们来考虑图2.12（即这里的图3.17）中的四向联系Contracts（签约），它包括影星、电影和两个制片公司－第一个拥有影星，第二个制作电影，并约定了影星在电影中演什么角色，我们用关系Contracts来表示这个多向联系，其关系模式的属性由来自下列四个实体集的键码组成： 1. 键码starName（影星名）代表影星。 2. 由属性title（名称）和year（年份）组成的键码代表一部电影。

  7. schema consists of the attributes from the keys of thefollowing four entity sets: 1. The key starName for the star. 2. The key consisting of title and year for the movie. 3.The key studioOfStar indicating the name of the first studio; recall we assume the studio name is a key for Studio. 4. The key producingStudio indicating the name of the studio that will produce the movie using that star. We have been inventive in choosing attribute names for our schema, avoiding “name” for any attribute,since it would be unobvious whether that referred to a star’s name or studio’s name, and in the latter case, which studio. Fig 3.17 The relationship Contracts 3. 键码studioOfStar（影星所在的制片公司）表示第一个制片公司的名字；请记住，我们假定制片公司名是实体集Studio的键码。 4. 键码producingStudio（制作电影的制片公司）表示制作该影星所主演的电影的制片公司名。 注意：我们为关系模式中的属性取名时比较注意，创新，没有一个属性叫“Name”，因为它会引会歧义，我们不知道它是指影星的名字还是指制片公司的名字，或者，对于后者－制片公司，究竟是指哪一个制片公司。

  8. 3.3.3 Handling Weak Entity Sets When a weak entity set appears in an E/R diagram, we need to do three things differently. 1. The relation for the weak entity set W itself must include not only the attributes of W but also the key attributes of the other entity sets that help form the key of W. These helping entity sets are easily recognized because they are reached by a double-diamond many-one relationship from W. 2. Any relationships in which the weak entity set W appears must use as a key for W all of its key attributes, including those of other entity sets that contribute to W’s key. 3. However, as we shall see, the double-diamond relationships, from the weak entity set W to other entity sets that help provide the key for W, need not be converted to a relation at all. The justification is that the attributes for such a relationship will always be a subset of the attributes for the weak entity set W itself, and thus these relationship provide no additional information, beyond the fact that they help W find its key. Of course, when introducing these additional attributes to 3.3.3 处理弱实体集 如果E／R图中出现弱实体集，我们需要做各不相同的三件事。 1. 弱实体集W本身所对应的关系模式必须既包含W的属性，也包含有助于构成W的键码的其他实体集的键码属性。这些辅助实体集是很容易识别的，因为它们通过双菱形表示的“多对一”的联系与W相联。 2.有弱实体集W出现的联系可以把W的所有键码属性（包括为W提供键码的其他实体集的键码属性）作为键码。 3.然而，正如我们将要看到的，从弱实体集W到有助于为W提供键码的其他实体集的双菱形联系，其实并不需要转换成关系，理由是这种联系的属性必然是弱实体集W自身属性集的子集， 这样的话，这些联系除了帮助W找到它的

  9. build the key of a weak entity set, we must be careful not to use the same name twice. If necessary, we rename some or all of these attributes. • Example 3.15: Let us consider the weak entity set Crews from Fig. 2.27. From this diagram we get three relations, whose schemas are: • Studios(name, adrr) • Crews(number, studioName) • Unit-of(number, studioName, name) • 码外，并不有提供附加信息。 • 当然，在引入附加的属性来 • 构成弱实体集的键码时， • 我们必须注意属性不能重名。 • 必要时，我们应为其中的一 • 些属名改名。 The first relation, Studios, is constructed in a straightforward manner from the entity set of the same name. The second, Crews, comes from the weak entity set Crews. The attributes of this relation are the key attributes of Crews; if there were any nonkey attributes for Crews, they would be included in the relation schema as well. We have chosen studioName as the attribute in relation Crews that corresponds to the attribute name in the entity set Studios. The third relation, Unit-of, comes from the relationship of the same name. As always, we represent an E/R relationship in the relational model by a relation whose schema has the key attributes of the related entity sets. In this case, Unit-of has attributes number and studioName, the ke for weak entity set Crews, and attribute name, the

  10. key for entity set Studios. However, notice that since Unit-of is a many-one relationship, the studio studioName is surely the same as the studio name. For instance, suppose Disney crew #3 is one of the crews of the Disney studio. Then the relationship set for Unit-of includes the pair (Disney crew #3, Disney) This pair gives rise to the Unit-of tuple (3, Disney, Disney) As a consequence, we can “merge” the attributes studioName and name of Unit-of, giving us the simpler schema: Unit-of (number, name) However, now we can dispense with the relation Unit-of altogether, since its attributes are a subset of the attributes of relation Crews. Example 3.16: Now consider the weak entity set Contracts from Example 2.31 and Fig. 2.28 in Section 2.6.1. The schema for relation Contracts is Contracts (starName, studioName, title, year, salary) These attributes are the key for Stars, suitably renamed, the key for Studios, suitably renamed, the two attributes that form the key for Movies, and the lone attribute, salary, 例如，假设Disney 员工#3是Disney制片厂的一个员工，那么Uint-of的关系集包括这样的关系配对： （Disney crew#3，Disney） 由这组关系可以得到Unit-of的一个元组： （3，Disney，Disney） 最后，合并Unit-of的studioName和name属性，得到简单的模式： Unit-of（number，name） 然而，我们完全可以省去Unit-of这个关系，因为它的属性是关系Crews属性集的一个子集。 例3.16：考虑例2.31中的弱实体集Contracts和Section2.6.1中的Fig.2.28，关系Contracts的模式是： Contracts（straName，studioName，title，year，salary） 分别构成Stars和Studios的关键字的两个属性组合构成了Movies的关键字，而孤立的属性salary属于实体集Contracts本身。联系Star-of，Studio-of和Movie-of之间没有关系，但却分别有一个属于Contracts的模式的子集固定的模式。

  11. belonging to the entity set Contracts itself. There are no relation constructed for the relationships Star-of, Studio-of, or Movie-of. Each would have a schema that is a proper subset of that for Contracts above. Incidentally, notice that the relation we obtain is exactly the same as what we would obtain had we started form the E/R diagram of Fig. 2.13. Recall that figure treats contracts as a three way relationship among stars, movies, and studios, with a salary attribute attached to Contracts. The phenomenon observed in Example 3.15 and 3.16--that the double-diamond relationship needs no relation -- is universal for weak entity sets. The relation for the weak entity set E has a schema that includes the schema of the relations constructed from any of the “double diamond” relationships R that are many-one from E to one of the other entity sets that help form E’s key. The reason is that the relation for E includes the key attributes for E, and these include all the key attributes for the two entity sets connected by R. Thus, we can state the following modified rule for weak entity sets. If E is a weak entity set, construct for E a relation whose schema consists of all the key attributes for E, including those attributes that are keys of “helping” entity sets related to E by a may-one relationship. 顺便提一下，如果我们从fig.2.13的E/R图做起的话，我们会得到同样的结果。根据Contracts的属性salary可以使我们想到数字描述的Contract可以作为stars，movies和studios的第三种关系。 例3.15、3.16描述的现象─双菱形关系没有联系─是所有弱实体集的共性。（弱实体集E的关系模式包含由所有“双边菱形“联系R组成的关系的模式，而从E得到的多对一的关系又属于形成E关键字一个实体集。）原因是E的关系包括它本身的关键字属性，并且包括通过R联接起来的两个实体集的所有关键字属性。我们可以对实体集做以下规定： 如果E是一个弱实体集，构造一个关系E，它由其本身的所有关键字属性组成它的模式，同时包括一切靠多对一的关系与E有联系的

  12. .Do not construct a relation for any relationship that is many-one from a weak entity set to another entity set, provided that relationship is a “double-diamond”relationship that helps provide the key for the weak entity set. Exercise 3.3.3 Convert the E/R diagram in Fig. 2.28 to relational database schemas. 实体集的关键字属性。 如果一个联系有助于弱实体集提供关键字的联系，那么就不能为其构造一个多对一的关系

More Related