relational design -- 1Relational DesignER to relational MappingRelational to ER MappingGood vrs Bad Relational DesignNormalization Processrelational design -- 3ER toRelation MappingemployeessnonamesalaryRelation:Employee(ssno name salary)Key:ssnoStrong Entityrelational design -- 4ER to Relation MappingWeak Entityaccountacct# customer balancetransactiontrans# amountlogRelation: transaction(acct#,trans#,amount)Key:acct# trans#IND:transaction[acct#] account[acct#]relational design -- 5ER to Relational Mappingemployeessno name salaryprojectproj# projmgrworks onstartdateMNRelation: works_on(ssno, proj#, startdate)Key:ssno, proj#INDworkson[proj#] project[proj#]workson[ssno] employee[ssno]relational design -- 6ER to Relational Mappingemployeessno name salaryprojectproj# projmgrworks onstartdateM1Relation: works_on(ssno, proj#, startdate)Key:ssnoINDworkson[proj#] project[proj#]workson[ssno] employee[ssno]employeeworks onatmost 1projectrelational design -- 7ER to Relational Mappingemployeessno name salaryprojectproj# projmgrworks onstartdateMNRelation: works_on(ssno, proj#, startdate)Key:ssno, proj#INDworkson[proj#] project[proj#]workson[ssno] employee[ssno]employee[ssno] workson[ssno]each employeemust work on aprojectrelational design -- 8ER to Relational Mappingemployeessno name salaryprojectproj# projmgrworkson usingstartdateM1Relation: worksonusing(ssno, proj#, toolid,startdate)Key:ssno, toolidINDworksonusing[proj#] project[proj#]worksonusing[ssno] employee[ssno]worksonusing[toolid] tools[toolid]employee[ssno] worksonusing[ssno]each employeemust work on aproject using atool. Employeeuses a given toolworks on a singleprojecttoolstoolid toolspecsNrelational design -- 9ER to Relational Mappingemployeessno name salaryRelation:staff(ssno, name, salary, position)faculty(ssno,name, salary, rank)student_assistant(ssno, name, salary,percetage_time)Key: ssno for all the relationsdstafffacultystudent assistantposition rank percenttage timecannot use if partial: cannot representemployees who are neither staff, norfaculty, not student assistants!cannot use if overlap: if staff could also bea student assistant, then redundancyrequires a union to construct list of allemployeesif no overlap,and totalparticipationrelational design -- 10ER to Relational Mappingemployeessno name salaryRelation:employee(ssno, name, salary, jobtype,position, rank, percentage-time)Key: ssnodstafffacultystudent assistantposition rank percenttage timejobtype can be used to specify whether anemployee is a staff, a faculty, or a studentassistant lot of null values will be used.if an employee does not belong to anysubclass, use null value for jobtypecannot be used if overlaptotal participation can be represented bypreventing null in jobtypedoes not require union to construct the list ofemployeesif no overlap,participationcan be partialrelational design -- 11ER to Relational Mappingemployeessno name salaryRelation:employee(ssno, name, salary)staff(ssno, position)faculty(ssno, rank)student_assistant(ssno, percetage_time)Key: ssno for all the relationsINDstaff[ssno] employee[ssnofaculty[ssno] employee[ssno]student_assistant[ssno] employee[ssno]ostafffacultystudent assistantposition rank percenttage timecannot represent total constraint.if overlappingrelational design -- 12ER to Relational Mappingemployeessno name salaryRelation:employee(ssno, name, salary,Isstaff,position, Isfaculty, rank, Isstudentassistant,percentage-time)Key: ssnoostafffacultystudent assistantposition rank percenttage timeIsstaff, Isfaculty, Isstudent_assistant are booleanvlaues which are either true or false. The relation willcontain lot of null values.cannot represent total constraiint.anothermechanism ifoverlappingrelational design -- 13Relation to ER Mapping•ER diagrams can be mapped to relational model (except sometimestotal participation in a superclass/subclass relationship is difficult tomodel)•Recall that at times during the design cyle we wish to do the reverse--that is, map relational schema to ER model.•Can this always be done?•So for the mapping to be correct, we should be able to represent theconstraints over a relational schema in the ER model.•Constraints in relational schema-- functional dependencies, inclusiondependencies.•Constraints in ER model: key constraints, cardinality constraints,participation constraints.•Can we model fds and INDs using the constraints in ER model?relational design -- 14Example•Consider we wish to build a catalog with three fields:street, city, zip•So least we need to do is create an entity with the three attributes:street, city and zip, where the entites satisfy the following:–street city uniquely determines zip–zip uniquely determines city•This can be modelled using the following two FDs in the relationalmodel:–street city zip–zip city•Can the same be modelled in ER using the set of constraints present?relational design -- 15Examplecatalogstreet city zipAssume we create a single entity with the three attributes.The only constraints that can be applied are the keyconstraints.{street, city} and {street, zip} are keysThis, however, does not prevent presence of two catalogobjects:(kirby, champaign, 61801)(florida, urbana, 61801)which should be prevented since a zip uniquely determines acity!!relational design -- 16Examplestreetcityzipcatalogstreetzip codecityLets try creating an entity for each attribute and a relationship involving each entity.We can now use cardinality constraints to get the required constraints?Notice that street city uniquely determine zip, so relationship is functional wrt zipSimilarly, street zip uniquely determine city, so relationship functional wrt cityBut how can we model a constraint zip determines the city which involves only twoentities using a ternary relation?This schema will also not prevent the catalog objects:(kirby, champaign, 61801)(florida, urbana, 61801)which should be prevented since a zip uniquely determines a city!!11nrelational design -- 17Examplestreetcityzipcatalogstreetzip codecityWill this do?No ! since city-of may be an empty relationship and will thus not prevent(kirby, champaign, 61801)(florida, urbana, 61801)which should be prevented since a zip uniquely determines a city!!Actually, it can be