Exercises - web.cs.ucla.edu

1 Exercises9personownscarparticipatedaccid entaddressdamage-amountmodelyearlicensen amereport-numberdatelocationdriver-iddri verFigure for a Car-insurance the distinctions among the terms primary key, candidate key, and :Asuperkeyis a set of one or more attributesthat, taken collectively, al-lows us to identify uniquelyan entityin the entity set. A superkeymay containextraneous attributes. IfKis a superkey, then so is any no proper subsetis also a superkeyis called that severaldistinct sets of attributescould serve as candidatekeys. Theprimary keyis one of the candidate keys that is chosen by the database designeras the principal means of identifying entitieswithin an entity anE-Rdiagram for a car-insurance company whose customers ownone or more cars each. Eachcar 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.

2 Associate with each patient a log of the various tests and examina-tions :See Figure university registrar s of ce maintains data about the followingentities: (a)courses,including number, title, credits, syllabus, and prerequisites;(b) courseofferings,including coursenumber, year, semester, sectionnumber, instructor(s),timings,and classroom;(c) students, including student-id, name, and program;and (d) instructors,including identi cation number, name, department,and ti-tle. Further, the enrollment of students in courses and gradesawarded to stu-dents in each course they are enrolled for mustbe appropriately anE-Rdiagram for the registrar s of all assumptionsthat you make about the mapping :See Figure the answergivenhere, the main entitysets arestudent, course,course-offering,10 Chapter 2 entity relationship Modelspecializationdoctorstest_namedatet imeresultss#namenamepatientsDr Patientinsurancedate admitteddate checked outdss#test logtesttest_idperformed_byFigure for a offeringsdepttitlecoursecoursenotitlecre ditssyllabusprerequisitemaincourserequir essecnoisofferedstudentnamegradeteachesy earsemesterroomtimeenrolssidinstructorna meiidFigure a The entitysetcourse-offeringis a weak entity set dependent oncourse.

3 The assumptionsmade are class meets only at one particular place and time. ThisE-Rdiagram cannotmodel a class meeting at different placesat 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. Make sure that only one relationshipexists between a particularstudent 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.

4 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)12 Chapter 2 entity relationship Modelcourse offeringssecnocoursenoprogramexamnamepla cetimeexamofmarksstudentnameyearsemester roomtimetakessidFigure for marks 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.

5 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 shouldbe modeled as :See Figure of the previousquestionto track the same informationfor all teams in a :See Figure Note that a player can stay in only one team during difference between a weak and a strong entity :A strong entity set has a primary key. All tuplesin the set are distin-guishable by that key. A weak entityset has no primary key unlessattributesofthe strong entity set on which it depends are in a weak entityset are partitionedaccording to their relationshipwith tuplesin a strong entityExercises13stadiummatchid datematchplayernameageplayedseason_score opponent own _scoreopp_scorescoreFigure datematchidstadiumscoreteamteam_playedpl ayer_ofscoreresultnamerankingFigure all teams distinguishableby a discriminator, whichis a canconvertanyweakentitysettoastrongentit ysetbysimplyaddingap-propriate , then,dowehaveweak entitysets?

6 Answer:We haveweakentitiesfor severalreasons: We wanttoavoidthedataduplicationandconseque ntpossibleinconsis-tenciescausedby duplicatingthekeyof thestrongentity. Weakentitiesre ectthe logicalstructure of an entitybeing dependent onanother entity . Weakentitiescanbe deletedautomaticallywhentheirstrong entityis deleted. Weak entitiescan be stored netheconcept of of where thisconcept 2 entity relationship Modelnamenamenameemployeeworks inprojectdeadlinerequiresmachineryFigure Example1 of upnamenametie up dateproductnamedistributeFigure Example2 of :Aggregation is an abstraction through which relationshipsare treatedas higher-levelentities. Thus the relationship between entities and is treatedas if it were an entity . work for projects. An employee working for a particularprojectuses variousmachinery.

7 See Figure have tie-ups with distributorsto has speci edfor it the set of productswhichare to be Exercise in Figure , whichmodels an the entitysets and theirprimary bookstore adds musiccassettes and compact disksto its musicitemmay be presentin cassetteor compact diskformat, with model this addi-tion,ignoring the effect on shopping , using generalization, to model the case wherea shopping basket may contain any combinationof books,musiccassettes,or compact which the same entity set is allowing this redundancy a bad practice that one should avoid wheneverpossible?Answer:By usingone entityset many timeswe are missingrelationshipsin16 Chapter 2 EntityRelationship Modelnamess#classtakesnamess#deptstudent studentplayssportcoursenoteamnameFigure example,intheE-Rdiagramin : thestudentstakingclasses are thesame studentswhoare athletes,butthismodel university database for the scheduling of classrooms for modeledas thesingleentitysetexam, withattributescourse-name,section-number ,room-number,andtime.

8 Alternatively, oneormoreadditionalentitysetscouldbe de ned,along withrelationship setsto replacesomeoftheattributesoftheexamentit yset,as coursewithattributesname,department,andc -number sectionwithattributess-numberandenrollme nt, anddependent as a weakentitysetoncourse roomwithattributesr-number,capacity, threeadditional what applicationcharacteristicswouldin uencea decisiontoin-cludeor nottoincludeeach of theadditional Figure entitysetsare useful if we wishto store theirattributesaspartof the thecourseentityset,wehavechosentoinclude three onlytheprimary key(c-number) were included,and ifcourses have onlyonesection,then it wouldbe appropriate to replace thecourse(andsection) entitysetsby an attribute(c-number) is undesirableto have multipleattributesofcourseasattributesof examisthatit would then be dif cult to maintain data on the courses, particularlyif a coursehas noexamorseveral remarksapplyto ofs-numberenrollmentcourseexaminroomr-nu mbercapacitybuildingexam-idFigure a particularenterprise,you haveseveralalternatives fromwhich to criteriashouldyouconsider in makingtheappropriate choice?

9 AlternativeE-Rdiagrams torepresent the universityregistrar sof ceof useare intuitivedesign,accurate expressionofthereal-worldconceptand ef ciency. Amodelwhichclearlyoutlinesthe objectsand rela-tionships in an intuitivemanner is better than onewhich does not,becauseit iseasiertouse and betweenanattributeandan entitysetto representan object,anddecidingbetweenan entitysetandrelationshipset,in uencetheaccuracywithwhichthereal-worldco nceptisexpressed. If therightdesignchoiceisnotmade, whichcan be implementedinanef cientmanner is to be preferred for differentalternativesfor theproblemin See Figure See Figure See Figure hasmerits, depending on the intendeduse of the beenseenearlier. Scheme require a separateentityforprerequisites. However, it willbedif culttostore all theprereq-uisites(beinga multi-valuedattribute).

10 Treats prerequisitesaswellas classroomsas separate entities,makingit useful forgatheringdataabout prerequisitesand in betweenthe others,inthat it treats prerequisitesas separate s of ce probably hasto answergeneralquestions about the num-ber of classes a student is takingor what are all theprerequisites of a course,or where a speci c class meets, scheme probablythe best choice.