Database Design and the E-R Model

1 C H A P T E R7 Database Design and the E-RModelPractice :TheE-Rdiagram is shown in Figure Payments are modeledas weak entities since they are related to a specific that the participation of accident in the relationshipparticipatedisnot total, since it is possible that there is an accident report where theparticipating car is customer_id name * license_no modelpolicy policy_idcoverspremium_payment payment_no due_date amount received_onpaymentaccident report_id date placeparticipatedFigure diagram for a car insurance 7 Database Design and the E-R Modelstudent student_id name dept_name tot_credsection sec_id semester yearcourse course_id title creditssec_courseexam exam_id name place timeexam_marksmarksFigure diagram for marks student_id name dept_name tot_credsection sec_id semester yearcourse course_id title creditssec_courseexam_marks{exam_marks exam_id marks}Figure E-R diagram for marks :Note.

2 The name of the relationship "course offering" needs to bechanged to "section".a. TheE-Rdiagram is shown in Figure Note that an alterantive is tomodel examinations as weak entities related to a section, rather thanas a strong entity. The marks relationship would then be a binaryrelationship betweenstudentandexam, without directly TheE-Rdiagram is shown in Figure Note that here we havenot modeled the name, place and time of the exam as part of therelationship attributes. Doing so would result in duplication of theinformation, once per student, and we would not be able to recordthis information without an associated student. If we wish to rep-resent this information, we would need to retain a separate entitycorresponding to each match_id date stadium opponent own_score opp_scoreplayer player_id name age season_score()playedscoreFigure diagram for favourite team :The diagram is shown in Figure :The different occurrences of an entity may have different setsof attributes, leading to an inconsistent diagram.

3 Instead, the attributes ofan entity should be specified only once. All other occurrences of the entityshould omit attributes. Since it is not possible to have an entity without anyattributes, an occurrence of an entity without attributes clearly indicatesthat the attributes are specified :a. If a pair of entity sets are connected by a path in anE-Rdiagram,the entity sets are related, though perhaps indirectly. A disconnectedgraph implies that there are pairs of entity sets that are unrelated toeach other. In an enterprise, we can say that the two departmentsare completely independent of each other. If we split the graph intoconnected components, we have, in effect, a separate Database corre-sponding to each connected As indicated in the answer to the previous part, a path in the graphbetween a pair of entity sets indicates a (possibly indirect) relationshipbetween the two entity sets. If there is a cycle in the graph then everypair of entity sets on the cycle are related to each other in atleasttwo distinct ways.

4 If theE-Rdiagram is acyclic then there is a uniquepath between every pair of entity sets and, thus, a unique relationshipbetween every pair of entity :a. LetE= {e1,e2},A= {a1,a2},B= {b1},C= {c1},RA= {(e1,a1),(e2,a2)},RB= {(e1,b1)}, andRC= {(e1,c1)}. We see that because of the tuple(e2,a2), no instance ofRexists which corresponds toE,RA, 7 Database Design and the E-R Model A B C E R R BR ACFigure diagram for Exercise A B C E R R BR ACFigure diagram for Exercise See Figure The idea is to introduce total participation constraintsbetweenEand the relationshipsRA,RB,RCso that every tuple inEhas a relationship withA, SupposeAtotally participates in the relationhipR, then introduce atotal participation constraint ConsiderEas a weak entity set andRA,RBandRCas its identifyingrelationship sets. See Figure :The primary key of a weak entity set can be inferred from itsrelationship with the strong entity set.

5 If we add primary key attributesto the weak entity set, they will be present in both the entityset andthe relationship set and they have to be the same. Hence therewill :In this example, the primary key ofsectionconsists of the at-tributes (courseid,semester,year), which would also be the primary key ofseccourse, whilecourseidis a foreign key constraints ensure that a particularsectioncan only correspond toonecourse, and thus the many-to-one cardinality constraint is , these constraints cannot enforce a total participation constraint,since a course or a section may not participate in :Exercises5In addition to declaringsIDas primary key foradvisor, we declareiIDas asuper key foradvisor(this can be done inSQLusing theuniqueconstraintoniID). :The foreign key attribute inRcorresponding to primary key ofBshould be madenot null. This ensures that no tuple ofAwhich is notrelated to any entry inBunderRcan come inR. For example, sayais atuple inAwhich has no corresponding entry inR.

6 This means whenRiscombined withA, it would have foreign key attribute corresponding toBasnullwhich is not :a. For the many-to-many case, the relationship must be represented as aseparate relation which cannot be combined with either participatingentity. Now, there is no way inSQLto ensure that a primary key valueoccurring in an entityE1 also occurs in a many-to-many relationshipR, since the corresponding attribute inRis not unique;SQLforeignkeys can only refer to the primary key or some other unique , for the one-to-many case, there is no way to ensure that anattribute on the one side appears in the relation corresponding to themany side, for the same Let the relationRbe many-to-one from entityAto entityBwithaandbas their respective primary keys, repectively. We can put thefollowing check constraints on the "one" side relationB:constrainttotalpartcheck(bin (selectbfromA));set constraintstotalpartdeferred;Note that the constraint should be set to deferred so that it is onlychecked at the end of the transaction; otherwise if we insertabvalueinBbefore it is inserted inAthe above constraint would be violated,and if we insert it inAbefore we insert it inB, a foreign key violationwould :Ainherits all the attributes ofXplus it may define its ownattributes.

7 SimilarlyCinherits all the attributes ofYplus its own the attributes of bothXandY. If there is some attributenamewhich belongs to bothXandY, it may be referred to inBby the :a. TheE-Rdiagram is shown in Figure The primary key attributesstudentidandinstructoridare assumed to be immutable, that is theyare not allowed to change with time. All other attributes areassumedto potentially change with that the diagram uses multivalued composite attributes suchasvalidtimesorname, with sub attributes such 7 Database Design and the E-R Modelstudent student_id {valid_times start_time end_time } {name value start_time end_time } {dept_name value start_time end_time } {tot_cred value start_time end_time }advisor{valid_time start_time end_time}instructor instructor_id {valid_times start_time end_time } {name value start_time end_time } {dept_name value start_time end_time } {salary value start_time end_time }Figure diagram for Exercise is a subattribute of several attributes such asname,totcredandsalary, and refers to the name, total credits or salary duringa particular interval of The generated relations are as shown below.

8 Each multivalued at-tribute has turned into a relation, with the relation name consistingof the original relation name concatenated with the name of the mul-tivalued attribute. The relation corresponding to the entity has onlythe primary key (studentid)studentvalidtimes(studentid, starttime, endtime)studentname(studentid, value, starttime, endtimestudentdeptname(studentid, value, starttime, endtimestudenttotcred(studentid, value, starttime, endtimeinstructor(instructorid)instructo rvalidtimes(instructorid, starttime, endtime)instructorname(instructorid, value, starttime, endtimeinstructordeptname(instructorid, value, starttime, endtimeinstructorsalary(instructorid, value, starttime, endtimeadvisor(studentid, intructorid, starttime, endtime)The primary keys shown are derived directly from we add the additional constraint that time intervals cannot overlap(or even the weaker condition that one start time cannot havetwoend times), we can remove theendtimefrom all the above primarykeys.))))))