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.

2 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. 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.

3 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. 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.

4 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. 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.

5 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.

6 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.

7 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.

8 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.

9 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. 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?

10 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. 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.