Entity Relationship Model - WBUTHELP.COM

1 CHAPTER 2 Entity Relationship ModelThis chapter introduces the Entity - Relationship Model in detail. The chapter coversnumerous features of the Model , several of which can be omitted depending on theplanned coverage of the course. Weak Entity sets (Section ), design constraints(Section ) and aggregation (Section ), and the corresponding subsections ofSection (Reduction of an E-R Schema to Tables) can be omitted if time is short. Werecommend covering specialization (Section ) at least in some detail, since it isan important concept for object-oriented databases (Chapter 8).TheE-Rmodel itself andE-Rdiagrams are used often in the text. It is importantthat students become comfortable with them. TheE-Rmodel is an excellent contextfor the introduction of students to the complexity of database design. For a givenenterprise there are often a wide variety ofE-Rdesigns. Although some choices arearbitrary, it is often the case that one design is inherently superior to another.

2 Severalof the exercises illustrate this point. The evaluation of the goodness of anE-Rdesignrequires an understanding of the enterprise being modeled and the applications tobe run. It is often possible to lead students into a debate of the relative merits ofcompeting designs and thus illustrate by example that understanding the applicationis often the hardest part of database emphasis is placed on the construction of tables serves to build intuition for the discussion of the relational Model in the subse-quent chapters. It also serves to ground abstract concepts of entities and relationshipsinto the more concrete concepts of relations. Several other texts places this materialalong with the relational data Model , rather than in theE-Rmodel chapter. Our mo-tivation for placing this material here is help students to appreciate howE-Rdatamodels get used in reality, while studying theE-Rmodel rather than later for a Car-insurance the distinctions among the terms primary key, candidate key, and :Asuperkeyis a set of one or more attributes that, taken collectively, al-lows us to identify uniquely an Entity in the Entity set.

3 A superkey may containextraneous attributes. IfKis a superkey, then so is any superset which no proper subset is also a superkey is called acandidate that several distinct sets of attributes could serve as candidate keys. Theprimary keyis one of the candidate keys that is chosen by the database designeras the principal means of identifying entities within an Entity anE-Rdiagram for a car-insurance company whose customers ownone or more cars each. Each car has associated with it zero to any number ofrecorded :See Figure anE-Rdiagram for a hospital with a set of patients and a set of medi-cal doctors. Associate with each patient a log of the various tests and examina-tions :See Figure university registrar s office maintains data about the following entities: (a)courses, including number, title, credits, syllabus, and prerequisites; (b) courseofferings, including course number, year, semester, section number, instructor(s),timings, and classroom; (c) students, including student-id, name, and program;and (d) instructors, including identification number, name, department, and ti-tle.

4 Further, the enrollment of students in courses and grades awarded to stu-dents in each course they are enrolled for must be appropriately anE-Rdiagram for the registrar s office. Document all assumptionsthat you make about the mapping :See Figure the answer given here, the main Entity sets arestudent, course, course-offering, #namenamepatientsDr Patientinsurancedate admitteddate checked outdss#test logtesttest_idperformed_byFigure for a offeringsdepttitlecoursecoursenotitlecre ditssyllabusprerequisitemaincourserequir essecnoisofferedstudentnamegradeteachesy earsemesterroomtimeenrolssidinstructorna meiidFigure a The Entity setcourse-offeringis a weak Entity set dependent oncourse. The assumptions made are class meets only at one particular place and time. ThisE-Rdiagram cannotmodel a class meeting at different places at different is no guarantee that the database does not have two classes meetingat the same place and a database used to record the marks that students get in different ex-ams of different course offeringssecnocoursenoexamnameplacetimem arksprogrameidstudentnameyearsemesterroo mtimetakessidFigure for marks anE-Rdiagramthat models exams as entities, and uses a ternaryrelationship, for the above an alternativeE-Rdiagram that uses only a binary relationshipbetweenstudentsandcourse-off erings.

5 Make sure that only one relationshipexists between a particular student and course-offering pair, yet you canrepresent the marks that a student gets in different exams of a course Figure Figure appropriate tables for each of theE-Rdiagrams in Exercises insurance tables:person (driver-id,name, address)car (license,year, Model )accident (report-number,date,location)participate d(driver-id,license,report-number,damage -amount) tables:patients (patient-id,name, insurance, date-admitted, date-checked-out)doctors (doctor-id,name, specialization)test (testid,testname, date, time, result)doctor-patient (patient-id,doctor-id)test-log (testid,patient-id)performed-by (testid,doctor-id) offeringssecnocoursenoprogramexamnamepla cetimeexamofmarksstudentnameyearsemester roomtimetakessidFigure for marks registrar s tables:student (student-id,name,program)course (courseno,title, syllabus, credits)course-offering (courseno,secno,year,semester,time, room)instructor (instructor-id,name, dept, title)enrols (student-id,courseno,secno,semester,year ,grade)teaches (courseno,secno,semester,year,instructor -id)requires (maincourse,prerequisite) for keeping track of the exploits of your favourite sportsteam.

6 You should store the matches played, the scores in each match, the playersin each match and individual player statistics for each match. Summary statis-tics should be modeled as derived :See Figure theE-Rdiagram of the previous question to track the same informationfor all teams in a :See Figure Note that a player can stay in only one team during the difference between a weak and a strong Entity :A strong Entity set has a primary key. All tuples in the set are distin-guishable by that key. A weak Entity set has no primary key unless attributes ofthe strong Entity set on which it depends are included. Tuples in a weak entityset are partitioned according to their Relationship with tuples in a strong datematchplayernameageplayedseason_score opponent own _scoreopp_scorescoreFigure for favourite team datematchidstadiumscoreteamteam_playedpl ayer_ofscoreresultnamerankingFigure for all teams Tuples within each partition are distinguishable by a discriminator, whichis a set of attributes.

7 Why, then, do we have weak Entity sets?Answer:We have weak entities for several reasons: We want to avoid the data duplication and consequent possible inconsis-tencies caused by duplicating the key of the strong Entity . Weak entities reflect the logical structure of an Entity being dependent onanother Entity . Weak entities can be deleted automatically when their strong Entity is deleted. Weak entities can be stored physically with their strong the concept of aggregation. Give two examples of where this concept inprojectdeadlinerequiresmachineryFigure Example 1 of upnamenametie up dateproductnamedistributeFigure Example 2 of :Aggregation is an abstraction through which relationships are treatedas higher-level entities. Thus the Relationship between entitiesAandBis treatedas if it were an work for projects. An employee working for a particular projectuses various machinery. See Figure have tie-ups with distributors to distribute products. Eachtie-up has specified for it the set of products which are to be distributed.

8 SeeFigure for Exercise theE-Rdiagram in Figure , which models an online the Entity sets and their primary the bookstore adds music cassettes and compact disks to its col-lection. The same music item may be present in cassette or compact diskformat, with differing prices. Extend theE-Rdiagram to Model this addi-tion, ignoring the effect on shopping extend theE-Rdiagram, using generalization, to Model the case wherea shopping basket may contain any combination of books, music cassettes,or compact anE-Rdiagram in which the same Entity set appears several is allowing this redundancy a bad practice that one should avoid wheneverpossible?Answer:By using one Entity set many times we are missing relationships #classtakesnamess#deptstudentstudentplay ssportcoursenoteamnameFigure with Entity Model . For example, in theE-Rdiagram in Figure : the students takingclasses are the same students who are athletes, but this Model will not a university database for the scheduling of classrooms for final database could be modeled as the single Entity setexam, with attributescourse-name,section-number,roo m-number,andtime.

9 Alternatively, one or moreadditional Entity sets could be defined, along with Relationship sets to replacesome of the attributes of theexamentity set, as coursewith attributesname,department,andc-number sectionwith attributess-numberandenrollment, and dependent as a weakentity set oncourse roomwith attributesr-number,capacity, anE-Rdiagram illustrating the use of all three additional Entity what application characteristics would influence a decision to in-clude or not to include each of the additional Entity Figure additional Entity sets are useful if we wish to store their attributes aspart of the database. For thecourseentity set, we have chosen to includethree attributes. If only the primary key (c-number) were included, and ifcourses have only one section, then it would be appropriate to replace thecourse(andsection) Entity sets by an attribute (c-number) is undesirable to have multiple attributes ofcourseas attributes ofexamisthat it would then be difficult to maintain data on the courses, particularlyif a course has no exam or several exams.

10 Similar remarks apply to theroomentity ofs-numberenrollmentcourseexaminroomr-nu mbercapacitybuildingexam-idFigure for exam designing anE-Rdiagramfor a particular enterprise, you have severalalternatives from which to criteria should you consider in making the appropriate choice? three alternativeE-Rdiagrams to represent the university registrar soffice of Exercise List the merits of each. Argue in favor of one of criteria to use are intuitive design, accurate expression of the real-worldconcept and efficiency. A Model which clearly outlines the objects and rela-tionships in an intuitive manner is better than one which does not, becauseit is easier to use and easier to change. Deciding between an attribute andan Entity set to represent an object, and deciding between an Entity set andrelationship set, influence the accuracy with which the real-world conceptis expressed. If the right design choice is not made, inconsistency and/orloss of information will result. A Model which can be implemented in anefficient manner is to be preferred for obvious three different alternatives for the problem in Exercise See Figure See Figure See Figure alternative has merits, depending on the intended use of the has been seen earlier.