chp4 - Chapter 4 Entity Relationship(ER Modeling Chapter 4...

  • Homework Help
  • preethichopra
  • 64
  • 85% (20) 17 out of 20 people found this document helpful

Info icon This preview shows pages 1–4. Sign up to view the full content.

Chapter 4 Entity Relationship (ER) Modeling Chapter 4 Entity-Relationship (ER) Modeling Discussion Focus We have used Microsoft Visio Professional to create the ERDs in the text and in this manual. Note that ERDs are done at the conceptual level. However, MS Visio has an implementation focus. Therefore, M:N relationships are not supported. Instead, the designer is limited to modeling 1:1 and/or 1:M relationships. Although M:N relationships may properly be viewed in a relational database model at the conceptual level, such relationships should not be implemented , because their existence creates undesirable redundancies. Therefore, M:N relationships must be decomposed into 1:M relationships to fit into the ER framework. For example, if you were to develop an ER model for a video rental store, you would note that tapes can be rented more than once and that customers can rent more than one tape. To make the discussion more interesting and to address several design issues at once, explain to the students that it seems reasonable to keep in mind that newly arrived tapes that have just been entered into inventory have not yet been rented and that some tapes may never be rented at all if there is no demand for them. Therefore, CUSTOMER is optional to TAPE. Assuming that the video store only rents videos and that a CUSTOMER entry will not exist unless a person coming into the video store actually rents that first tape, TAPE is mandatory to CUSTOMER. Note that this discussion includes a very brief description of the video store's operations and some business rules. The relationship between customers and tapes would thus be M:N, as shown in Figure IM4.1. Figure IM4.1 The M:N Relationship 89
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Chapter 4 Entity Relationship (ER) Modeling As you discuss the presentation in Figure IM4.1, note that the ERD reflects two business rules: 1. A CUSTOMER may rent many TAPEs. 2. A TAPE can be rented by many CUSTOMERs. If you are using Visio Professional, you can use the ERD shown in Figure IM4.1A. Note that Visio Professional does not permit you to create the 1:M relationship directly … look at the choices below the ERD and note that the M:N relationship is conspicuously missing. Figure IM4.1A The M:N Relationship in Visio Format The M:N relationship depicted in Figure IM4.1 must be broken up into two 1:M relationships through the use of a bridge entity, also known as a composite entity. The composite entity, named RENTAL in the example shown in Figure IM4.2, must include at least the primary key components (CUS_NUM and TAPE_CODE) of the two entities it bridges, because the RENTAL entity’s foreign keys must point to the primary keys of the two entities CUSTOMER and TAPE. Figure IM4.2 Decomposition of the M:N Relationship 90
Image of page 2
Chapter 4 Entity Relationship (ER) Modeling Several points about Figure IM4.2 are worth emphasizing: The RENTAL entity’s PK could have been the combination TAPE_CODE + CUS_NUM. This composite PK would have created strong relationships between RENTAL and CUSTOMER and between RENTAL and TAPE. Because this composite PK was not used, it is a candidate key.
Image of page 3

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Image of page 4
This is the end of the preview. Sign up to access the rest of the document.
  • Fall '15
  • Entity-relationship model, entity

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern