ER to Relational MappingSlide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16ER to Relational Mapping Strong Entity Relation: ssnonamesalaryemployeeEmployee(ssno name salary)Key : ssnoWeak Entity Relation:acct# customer balance transaction(acct#,trans#, amount)accountlogtransactiontrans#amountKey: acct# trans#IND:transactional[acct#] account[acct#]ER to Relational Mappingssno name salary Relation: works_on(ssno,proj#,startdate) Key: ssno,proj#employeeWorks onprojectStartdateMNproj projmgrER to Relational Mapping]employee[]workson[]#project[]#workson[ssnossnoprojprojInd:ssno name salary Relation:works_on(ssno,proj#,startdate)Key:ssnoInd: workson[proj#] project[proj#] workson[ssno] employee[ssno]employeeWorks onprojectStartdateM1proj projmgrEmployee works on atmost 1 projectER to Relational Mappingssno name salary Relation:works_on(ssno,proj#,startdate)Key:ssno,proj#Ind: workson[proj#] project[proj#] workson[ssno] employee[ssno] employee[ssno] workson[ssno]employeeWorks onprojectStartdateNproj projmgrMEach employ must work on aprojectER to Relational Mappingssno name salary Relation : worksonusing(ssno,proj#,toolid,startdate) Key : ssno,toolid IND : worksonusing[proj#] project[proj#] worksonusing[ssno] employee[ssno] worksonusing[toolid] tools[toolid] employee[ssno] worksonusing[ssno]employeeWorkson usingprojecttoolsstartdatetoolidtoolspecsM1NProj# projmgrEach employeemust work on a projusing a tool. Employee uses a given tool works on a single projER to Relational Mappingssno name salaryRelation:staff(ssno, name, salary, position)faculty(ssno, name, salary, rank)student_assistant(ssno, name, salary, percentage_time)Key: ssno for all the relationscannot use if partial:cannot represent employees who are neither staff, nor faculty, not student assistants!Cannot use if overlap:if staff could also be a student assistant, then redundancyrequires a union to construct list of all employeesdstafffacultyStudent assistantemployeeIf no overlap,and totalparticipationER to Relational Mappingssno name salary Relation:employee(ssno, name, salary, jobtype,position, rank, percentage-time) Key : ssnojob type can be used to specify whether an employee is a staff, a faculty, or a student assistanta lot of null values will be used.If an employee does not belong to any subclass, use null value for job typecannot be used if overlaptotal participation can be represented by preventing null in jobtypedoes not require union to construct the list of employeesdstafffacultyStudent assistantemployeepositionrankPercenttage timeIf no overlap,participationcan be partialER to Relational Mappingssno name salary Relation:employee (ssno, name, salary)staff(ssno, position)faculty(ssno, rank)student_assistant(ssno, percentage_time)Key : ssno for all the relationsINDstaff[ssno] employee[ssno]faculty[ssno] employee[ssno]student_assistant[ssno] employee[ssno]cannot represent total constraintostafffacultyStudent assistantemployeepositionrankPercenttage timeIf overlappingER to Relational Mappingssno name salary Relation:employee(ssno, name, salary, Isstaff,position, Isfaculty, rank, Isstudentassistant,percentage-time)Key : ssnoIsstaff, Isfaculty, Isstudent_assistant are boolean values which are either true or false. The relation will contain lot of null valuescannot represent total constraint.ostafffacultyStudent assistantemployeepositionrankPercenttage timeanother mechanism ifoverlappingER to Relational MappingER Diagrams can be mapped to relational model (except sometimes total participation in a superclass/ subclass relationship is difficult to model)Recall that at times during the design cycle we wish to do the reverse--- that is, map relational schema to ER model.Can this always be done ?So far the mapping to be correct, we should be able to represent the constraints over a relational schema in the ER model.Constraints in relational schema -- functional dependencies, inclusion dependencies.Constraints in ER model: key constraints, cardinality constraints, participation constraints.Can we model fds and INDs using the constraints in ER model?ER to Relational MappingExample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 uniquely determines zip-zip uniquely determines cityThis can be modelled using the following two FDs in the relational model:-street city zip-zip cityCan the same be modelled in ER using the set of constraints present?ER to Relational MappingExamplestreet city zipAssume we create a single entity with the three attributes.The only constraints that can be applied are the key constraints{street, city} and {street, zip} are keys.This however, does not prevent presence of two catalog objects:(kirby, champaign, 61801)(florida, urbana, 61801)which should be prevented since zip uniquely determines a citycatalogER to Relational MappingExample street zip codeLets 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 zip.Similarly, street zip uniquely determine city, so relationship is functional wrt city.But how can we model a constraint zip determines the city which involves only two entities using a ternary relation?This shema will also not prevent the catalog objects:(kirby, champaign, 61801)(florida, urbana, 61801)which should be prevented since a zip uniquely determines a city !!streetcatalogzipcity cityn11ER to Relational MappingExampleWill 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 formally shown that no ER schema can be used to represent the constraints in this example(you should try other possibilities at home to convince yourself)streetcatalogzipcityn11cityofn1street Zip codeER to Relational MappingConceptual Modelling -- ER diagramsER schema transformed to relational schema (ER