Pro recursive recursive relationships tend to be

This preview shows page 5 - 7 out of 21 pages.

PRO Recursive:Recursive relationships tend to be simpler because you areusing only one entity. Your diagram will be less “busy.” However, they are lessspecific – you cannot have mandatory attributes or relationships unless they aremandatory in all instances of the entity.Another thing to consider when modeling these types of relationships is howoften the structure changes. If it changes often, then a recursive relationship iseasier to maintain. If it is fairly fixed, then you may consider the more explicithierarchy.A structure that doesn’t change very much would be a building with suites andfloors and rooms. This is a good case for using an explicit hierarchy. On the otherhand, a company that reorganizes frequently (going from a fairly flatorganizational structure to one with many levels and vice versa) would probablybe better modeled with a recursive relationship.ConnectionsGo back to the ERD with ROOM, SUITE, FLOOR, and BUILDING.Askabout thetransferability of the relationships. Can a floor be moved from one building toanother? How should the ERD be modified?Answer:These are fairly permanent physical structures. Although walls could betorn down or built up to reorganize rooms into different suites, a suite is not likelyto move up or down a floor. Similarly, a floor cannot physically relocate toanother building. The relationships between BUILDING and FLOOR andbetween FLOOR and SUITE are nontransferable and should be represented witha diamond. The business rules will determine whether the relationship betweenROOM and SUITE is transferable or not.
Slide 2: ObjectivesNo instructor notes for this slideCopyright © 2013, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may betrademarks of their respective owners.
Database DesignInstructor Resource GuideSlide 3: PurposeAsk students to name other hierarchical structures.Answers could include: government, military, biology (species, genus ….).Slides 4-5: Relationships in an Organizational ChartExplain that one way to model a hierarchy would be to create an entity for eachlevel with a relationship to the next level.The UIDs would probably be an employee ID or number for each entity.Ask students: how would the ERD change if the business was reorganized to addor remove a level?Slide 6: Another Relationship ExampleHere you have a case of the cascading UIDs: the UID of FLOOR is thecombination of FLOOR number and the BUILDING id, the UID of SUITE is thecombination of SUITE number and the FLOOR number and the BUILDING id,and the UID of ROOM is the combination of ROOM id and SUITE number andFLOOR number and the BUILDING id. It is important to have the students realizethis.This may be convenient in the sense that the UID of a ROOM will also tell youthe SUITE and FLOOR and BUILDING that it’s in. However, this makes for arather long UID. Unique independent, artificial codes may be more practical.Also, if the hierarchical structure changes often, use artificial identifiers. (If we

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 21 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Winter
Professor
toni
Tags
Entity relationship model, Instructor Resource Guide

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture