《數(shù)據(jù)庫系統(tǒng)》教學(xué)課件

《數(shù)據(jù)庫系統(tǒng)》教學(xué)課件,數(shù)據(jù)庫系統(tǒng),數(shù)據(jù)庫,系統(tǒng),教學(xué),課件 The Entity-Relationship ModelJianlin FengSchool of SoftwareSUN YAT-SEN UNIVERSITYcourtesy of Joe Hellerstein for some slidesThe Entity-Relationship Model Toward a Unified View of DatanBy Peter Pin-Shan Chen.qIn ACM Transaction on Database Systems(TODS),Vol.1(1),1976.n Also by Peter,a retrospect of ERqEntity-Relationship Modeling:Historical Events,Future Trends,and Lessons Learned Concept of Entity and Relationship:from the retrospectSteps in Database DesignnRequirements Analysisq user needs;what must database do?nConceptual(Database)Designq high level(or semantic)description from users daily view.qoften done with the ER modelnLogical(Database)Designq translate ER into DBMS data model(e.g.,Relational Model)nSchema Refinement q consistency,normalizationnPhysical(Database)Design-indexes,disk layoutnSecurity Design-who accesses what,and howConceptual DesignnWhat are the entities and relationships in the enterprise?nWhat information about these entities and relationships should we store in the database?nWhat are the integrity constraints or business rules that hold?nA database schema in the ER Model can be represented pictorially(ER diagrams).nCan map an ER diagram into a relational schema.ER Model BasicsnEntity:qReal-world object distinguishable from other objects.qAn entity is described(in DB)using a set of attributes.nEntity Set:A collection of similar entities.qE.g.,all employees.qAll entities in an entity set have the same attributes.qEach entity set has a key(underlined).qEach attribute has a domain.KeysnA key is a minimal set of attributes whose values uniquely identify an entity in some entity set.qFor each entity set,we choose a key.nCandidate keyqAn entity set can have more than one key.nPrimary keyqWe designate one of the candidate keys as the primary key.Representation of an Entity SetnEntity setqRepresented by a rectangle.nAttribute:qRepresented by an oval.nKey:qEach attribute in the primary key is underlined.EmployeesssnnamelotER Model Basics(Cont.)nRelationship:Association among two or more entities.qRepresented by a diamond.qrelationships can have their own attributes.nA relationship must be uniquely identified by the participating entities,without reference to its own attributes.nRelationship Set:Collection of similar relationships.qAn n-ary relationship set R relates n entity sets E1.En;each relationship in R involves entities e1 E1,.,en Enthe Works_In Relationship SetlotnameEmployeesssnWorks_InsincednamebudgetdidDepartmentsAn Instance of the Works_In Relationship SetA Ternary Relationship Set:Works_In2ER Model Basics(Cont.)n Same entity set can participate in different relationship sets,or in different“roles”in the same relationship set.subor-dinate super-visorReports_TosinceWorks_IndnamebudgetdidDepartmentslotnameEmployeesssnKey Constraints An employee can work in many departments;a dept can have many employees.1-to-1M-to-NsinceManagesdnamebudgetdidDepartmentssinceWorks_InlotnamessnEmployeesIn contrast,each dept has at most one manager,according to the key constraint on Manages.1-to-NAn Instance of the Manages Relationship Setviolates the key constraint on MANAGES:A dept has at most one manager.MANAGES is a kind of WORKS_INParticipation ConstraintsnThe participation of the entity set Departments in the relationship set Manages is said to be total.qConnect Departments and Manages by a thick line.nThe participation of the entity set Employees in Manages is partial.Weak EntitiesnA weak entity can be identified uniquely only by relationship with of another(owner)entity.qOwner entity set and weak entity set must participate in a 1-N relationship set(1 owner,N weak entities).qWeak entity set must have total participation in this identifying relationship set.lotnameagepnameDependentsEmployeesssnPolicycostWeak entities have only a“partial key”(dashed underline)Class Hierarchies:Organizing Entities into ISA (is a)HierarchiesOverlap constraints:Can Joe be an Hourly_Emps as well as a Contract_Emps entity?(Allowed/disallowed)Covering constraints:Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity?(Yes/no)AggregationnAs defined so far,a relationship set is an association between entity sets.nAggregation allows us to model a relationship set between entities and relationships.qi.e.,treat a relationship set as an entity set for purposes of participation in(other)relationships.An Example of AggregationMonitors is a relationship between entity Employees and relationship Sponsors.A project is sponsored by at least 1 department.A department may assign employees to monitor a sponsorship.Aggregation vs.Ternary RelationshipSome attributes are not easily expressed without aggregation,such as“until”.Here,the Monitors relationship is not clearly expressed.Conceptual Design Using the ER ModelnER modeling can get tricky!nDesign choices:qEntity or attribute?qEntity or relationship?qRelationships:Binary or ternary?Aggregation?nER Model goals and limitations:qLots of semantics can(and should)be captured.qSome constraints cannot be captured in ER.nWell refine things in our logical(relational)designEntity vs.Attributen“Address”:qattribute of Employees?qEntity of its own?nIt depends!Semantics and usage.q Several addresses per employee?nmust be an entitynatomic attribute types(no set-valued attributes!)q Care about structure?(city,street,etc.)nmust be an entity!natomic attribute types(no tuple-valued attributes!)Entity vs.RelationshipnSeparate discretionary budget(dbudget)for each dept.nWhat if managers dbudget is a SUM that covers all managed deptsqCould repeat valuenBetter design:Associate dbudget with the appointment of the employee as manager of a group of depts.Manages2namednamebudgetdidEmployeesDepartmentsssnlotdbudgetsinceEmployeessincenamednamebudgetdidDepartmentsssnlotMgr_Apptsis_managerdbudgetapptnummanaged_bySummary of the ER ModelnEntities and Entity Set(boxes)nRelationships and Relationship sets(diamonds)qbinaryqn-arynKey constraints(1-1,1-N,M-N,arrows)nParticipation constraints(bold for Total)nWeak entities-require strong entity for key