WIXLIB

CS3200 – Database DesignSpring 2018DerbinskyEntity-Relationship (ER) DiagramsLecture 7Entity-Relationship (ER) DiagramsFebruary 11, 20181

CS3200 – Database DesignSpring 2018DerbinskyOutline1. Context– Design & Implementation Process2.3.4.5.6.Goals of Conceptual DesignEntity-Relationship (ER) ModelOne ER Diagrammatic NotationRequirements ElicitationApproaches to Conceptual DesignEntity-Relationship (ER) DiagramsFebruary 11, 20182

CS3200 – Database DesignSpring 2018DerbinskyDatabase Design and Implementation ProcessEntity-Relationship (ER) DiagramsFebruary 11, 20183

CS3200 – Database DesignSpring 2018DerbinskyGoal of Conceptual DesignDescription of data requirements that is Comprehensive Entity types, relationships, and constraints Sanity check of data & functional requirements Reference for [unit/integration] testing/analysisConcise/High-level Easy to understand technically Easy to communicate with non-technical users Facilitates focus on data (vs. storage/implementation details)Algorithmically Transformable Improves application development efficiency, reduces errorsEntity-Relationship (ER) DiagramsFebruary 11, 20184

CS3200 – Database DesignSpring 2018DerbinskyEntity-Relationship (ER) ModelEntity Thing in the real worldAttribute Property of an entity Most of what we store in the databaseRelationship Association between sets of entities Possibly with attribute(s)Entity-Relationship (ER) DiagramsFebruary 11, 20185

CS3200 – Database DesignSpring 2018DerbinskyER Diagrams Graphical depictionof an ER model Many notations, thisclass All cars have a year, age, make, model,registration (unique), vehicle number(vin; unique), some number of colors Specialization/GeneralizationEntity-Relationship (ER) DiagramsFebruary 11, 20186

CS3200 – Database DesignSpring 2018DerbinskyEntity SetsSet of entities that havethe same attributesAll cars have a year,make, and model.MakeYearModelCAREntity-Relationship (ER) DiagramsFebruary 11, 20187

CS3200 – Database DesignSpring 2018DerbinskyComposite AttributesCan be subdivided intosmaller subpartsMakeAll cars have a year,make, model, rEntity-Relationship (ER) DiagramsFebruary 11, 20188

CS3200 – Database DesignSpring 2018DerbinskyMultivalued AttributesCan take a [possiblyspecified] number ofvalues.MakeModelCARColorAll cars have a year,make, model,registration, and somenumber of hip (ER) DiagramsFebruary 11, 20189

CS3200 – Database DesignSpring 2018DerbinskyKey AttributesThe value uniquelyidentifies each entityAll cars have a year,make, model,registration (unique),vehicle number (vin;unique), some numberof mberEntity-Relationship (ER) DiagramsFebruary 11, 201810

CS3200 – Database DesignSpring 2018DerbinskyPotential Pitfall In relational schema, underlining multipleattributes indicates that for all rows, thecombination is unique In ERDs, underlining multiple attributesindicates that each individually canuniquely identify an entityEntity-Relationship (ER) DiagramsFebruary 11, 201811

CS3200 – Database DesignSpring 2018DerbinskyDerived AttributesAgeThe value can becomputedAll cars have a year,age, make, model,registration (unique),vehicle number (vin;unique), some numberof mberEntity-Relationship (ER) DiagramsFebruary 11, 201812

CS3200 – Database DesignSpring 2018DerbinskyExerciseDraw an ERD for the following description:Each department has a unique name, aunique number, and a particular employeewho manages the department. We keeptrack of the start date when that employeebegan managing the department. Adepartment may have several locations.Entity-Relationship (ER) DiagramsFebruary 11, 201813

CS3200 – Database DesignSpring agerManagerStart DateEntity-Relationship (ER) DiagramsFebruary 11, 201814

CS3200 – Database DesignSpring 2018DerbinskyExerciseDraw an ERD for the following description:A department controls a number of projects,each of which has a unique name, a uniquenumber, and a single location.Entity-Relationship (ER) DiagramsFebruary 11, 201815

CS3200 – Database DesignSpring llingDepartmentEntity-Relationship (ER) DiagramsFebruary 11, 201816

CS3200 – Database DesignSpring 2018DerbinskyExerciseDraw an ERD for the following description:We store each employee’s name (first, last, MI),Social Security number (SSN), street address, salary,sex (gender), and birth date. An employee isassigned to one department, but may work onseveral projects, which are not necessarily controlledby the same department. We keep track of thecurrent number of hours per week that an employeeworks on each project. We also keep track of thedirect supervisor of each employee (who is anotheremployee).Entity-Relationship (ER) DiagramsFebruary 11, 201817

CS3200 – Database DesignSpring EMPLOYEESupervisorAddressFNameNameWorks OnMILNameHoursProjectEntity-Relationship (ER) DiagramsFebruary 11, 201818

CS3200 – Database DesignSpring 2018DerbinskyExerciseDraw an ERD for the following description:We want to keep track of the dependents ofeach employee for insurance purposes. Wekeep each dependent’s first name, sex, birthdate, and relationship to the employee.Entity-Relationship (ER) DiagramsFebruary 11, 201819

CS3200 – Database DesignSpring elationshipDNameEntity-Relationship (ER) DiagramsFebruary 11, 201820

CS3200 – Database DesignSpring 2018DerbinskyRelationshipsAssociates one or more sets of entities– One recursive (role is important)STUDENTDEPTCHAIR FAll departments have a facultymember who serves as the chair. Afaculty member can only chair onedepartment.FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201821

CS3200 – Database DesignSpring 2018DerbinskyRelationshipsAssociates one or more sets of entities– One recursive (role is important)MAJOR DSTUDENTDEPTCHAIR FAll students must have a departmentin which they major.FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201822

CS3200 – Database DesignSpring 2018DerbinskyRelationshipsAssociates one or more sets of entities– One recursive (role is important)MAJOR DSTUDENTDEPTMINOR DCHAIR FStudents may have any number ofdepartments in which they minor.FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201823

CS3200 – Database DesignSpring 2018DerbinskyRelationshipsAssociates one or more sets of entities– One recursive (role is important)MAJOR DSTUDENTTutorDEPTTuteeTUTORSMINOR DCHAIR FStudents can tutor other student(s).FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201824

CS3200 – Database DesignSpring 2018DerbinskyCardinality RatiosConstrains the number of entities that canparticipate in each role of the relationshipMAJOR DSTUDENTTutorDEPTTuteeTUTORSAll departments have a facultymember who serves as the chair. Afaculty member can only chair onedepartment.MINOR D1CHAIR F1FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201825

CS3200 – Database DesignSpring 2018DerbinskyCardinality RatiosConstrains the number of entities that canparticipate in each role of the relationshipNMAJOR DSTUDENTTutor1DEPTTuteeTUTORSAll students must have a departmentin which they major.MINOR D1CHAIR F1FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201826

CS3200 – Database DesignSpring 2018DerbinskyCardinality RatiosConstrains the number of entities that canparticipate in each role of the relationshipNSTUDENTTutorNTuteeTUTORSStudents may have any number ofdepartments in which they minor.MAJOR D1MMINOR DDEPT1CHAIR F1FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201827

CS3200 – Database DesignSpring 2018DerbinskyCardinality RatiosConstrains the number of entities that canparticipate in each role of the relationshipNSTUDENTTutorNTUTORSNTuteeMStudents can tutor other student(s).MAJOR D1MMINOR DDEPT1CHAIR F1FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201828

CS3200 – Database DesignSpring 2018DerbinskyStructural ConstraintsIf an entity does not exist unless it appears with an entity in arelationship, the participation is total (existence dependency).Else, partial.NSTUDENTTutorNTUTORSNTuteeMAll departments have a facultymember who serves as the chair. Afaculty member can only chair onedepartment.MAJOR D1MMINOR DDEPT1CHAIR F1FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201829

CS3200 – Database DesignSpring 2018DerbinskyStructural ConstraintsIf an entity does not exist unless it appears with an entity in arelationship, the participation is total (existence dependency).Else, partial.NSTUDENTTutorNTUTORSNTuteeMAll students must have a departmentin which they major.MAJOR D1MMINOR DDEPT1CHAIR F1FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201830

CS3200 – Database DesignSpring 2018DerbinskyAttributes of Relationships1- 1, can go to either entity1- N, can go to (1) entityNSTUDENTTutorNTUTORSNTuteeM1MAJOR DMMINOR DDEPT1OfficeCHAIR F1Each department chair has an office.FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201831

CS3200 – Database DesignSpring 2018DerbinskyAttributes of Relationships1- 1, can go to either entity1- N, can go to (1) entityNSTUDENTTutorNTUTORSNTuteeMDone1MAJOR DMMINOR DDEPT1OfficeCHAIR F1It is important to know whether ornot a student has completed his/hermajor.FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201832

CS3200 – Database DesignSpring 2018DerbinskyAttributes of Relationships1- 1, can go to either entity1- N, can go to (1) entityNSTUDENTTutorNTUTORSNTuteeMDone1MAJOR DMMINOR DDoneDEPT1OfficeCHAIR F1It is important to know whether ornot a student has completed each ofhis/her minor(s).FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201833

CS3200 – Database DesignSpring 2018DerbinskyAttributes of Relationships1- 1, can go to either entity1- N, can go to (1) entityNSTUDENTTutorNTUTORSDone1MAJOR DMNTuteeMMINOR DDoneDEPT1OfficeCHAIR FSubject1It is important to know the subject(s)in which a tutee is being tutored byeach tutor.FACULTYEntity-Relationship (ER) DiagramsFebruary 11, 201834

CS3200 – Database DesignSpring 2018DerbinskyWeak EntitiesEntity types that do not have key attributesof their own are weak; instead identified byrelation to specific entity of another type(the identifying kNUMEntity-Relationship (ER) DiagramsFebruary 11, 201835

CS3200 – Database DesignSpring 2018DerbinskyRevise!We store each employee’sname (first, last, MI), SocialSecurity number (SSN), streetaddress, salary, sex (gender),and birth date. An employee isassigned to one department,but may work on severalprojects, which are notnecessarily controlled by thesame department. We keeptrack of the current number ofhours per week that anemployee works on eachproject. We also keep trackof the direct supervisor ofeach employee (who isanother OYEE)Supervisor)Address)FName)Name)Works On)MI)LName)Hours)Project)Entity-Relationship (ER) DiagramsFebruary 11, 201836

CS3200 – Database DesignSpring ameNameWorks OnMILNameHoursProjectEntity-Relationship (ER) DiagramsFebruary 11, 201837

CS3200 – Database DesignSpring pervisor)Supervision)We want to keep trackof the dependents ofeach employee forinsurance purposes. Wekeep each dependent’sfirst name, sex, birthdate, and relationship tothe Name)Name)Works Birthdate(Rela7onship(DName(Entity-Relationship (ER) DiagramsFebruary 11, 201838

CS3200 – Database DesignSpring NameNameWorks hdateRelationshipDNameEntity-Relationship (ER) DiagramsFebruary 11, 201839

CS3200 – Database DesignSpring 2018DerbinskyRevise!A department controlsa number of projects,each of which has aunique name, a uniquenumber, and a ling 'Manager 'Start Date'Entity-Relationship (ER) DiagramsFebruary 11, 201840

CS3200 – Database DesignSpring t DateEntity-Relationship (ER) DiagramsFebruary 11, 201841

CS3200 – Database DesignSpring 2018DerbinskyRevise!An employee is assignedto one department, butmay work on severalprojects, which are notnecessarily controlled bythe same department. Wekeep track of the currentnumber of hours perweek that an employeeworks on each umber'DEPARTMENT'Manager'Manager 'Start r)Supervision)Each department has a particular employee whomanages the see)1)N)FName)Name)Works ip (ER) DiagramsFebruary 11, 201842