清华大学数据库原理课件第二章

数 据 库 原 理王 建 民清 华 大 学 软 件 学 院2003年/秋 Chapter 2Entity-Relationship Model(c1)What Is in E-R Model?1.Static Information2.Dynamic InformationlData EntitieslAssociations Relationships among entitieslProcesses Operations/transactionslIntegrity constraints Business rules/regulations and data meanings2Main ContentslWeak Entity SetslExtended E-R FeatureslDesign of an E-R Database SchemalReduction of an E-R Schema to Tables 3Weak Entity SetslAn entity set that does not have a primary key is referred to as a weak entity setStrong entity setDepicted using double rectanglelThe existence of a weak entity set depends on the existence of a identifying entity setIdentifying relationship depicted using a double diamond4Keys for Weak Entity SetslThe discriminator(or partial key)of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity setlThe primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent,plus the weak entity sets discriminator5Existing DependencylExample 1Loans and PaymentslExample 2Courses and Course-offerings6Identifying RelationshiplWeak entity set must relate to the identifying entity set via a total relationship setA weak entity cant exist independentlylThe identifying relationship must be one-to-many relationship set from the identifying to the weak entity set7Questions about WESslCan the entity set participate in other than the identifying relationship?lCan the WES have more than one owners?Give a examplelCan the owners primary keys be explicitly stored in WES?lWES or Multi-valued Attributes?Entities vs.AttributesIf loan-number were explicitly stored,payment could be made a strong entity,but then the relationship between payment and loan would be duplicated by an implicit relationship defined by the attribute loan-number common to payment and loan 8Main ContentslWeak Entity SetslExtended E-R FeatureslDesign of an E-R Database SchemalReduction of an E-R Schema to Tables 9RelationshiplA relationship is an association among several entitiesThe nature of their inter-connectionsThe way they are connectedThe required integrity rules and constraintslIs there relationship types other than the verb?10Scenario 1personStudentStaffUndergraduateGraduateEntity sets of a same kind form a hierarchyPeoples in our school11Is-A RelationshiplHigher-level entitySupertypes or SuperclassesCombine the common characteristics of subtypesllower-level entity Subtypes or SubclassesExhibit unique characteristicslIs-A RelationshipRefer to superclass-subclass relationshipDepicted by a triangle component labeled ISA12SpecializationlDesignate subgroupings within an entity set that are distinctive from other entities in the setTop-down design processClassification processStudentStaffpersonIDNameAddMajorGPASalaryTitleISAISA13GeneralizationlCombine a number of entity sets that share the same features into a higher-level entity setBottom-up design processStudentStaffMajorGPASalaryTitlepersonIDNameAddrISAISAIDNameAddrIDAddrName14InheritancelAttribute inheritanceA lower-level entity set inherits all the attributeslRelationship inheritanceA lower-level entity set inherits all the relationship participation of the higher-level entity set to which it is linkedlA subtype inherits the attributes&relationships of its supertype and may have its own properties15Inheritance ExampleHow Many Attributes?16Specialization and GeneralizationlSpecialization and generalization are simple inversions of each otherlthey are represented in an E-R diagram in the same waylThe terms specialization and generalization are used interchangeably17Some QuestionslCan we make multiple specializations of an entity set based on different features?lCan an entity belong to more than one subtypes?E.g.permanent-employee vs.temporary-employee,in addition to officer vs.secretary vs.tellerA particular employee could be la member of one of permanent-employee or temporary-employee,land also a member of one of officer,secretary,or teller18Is-A ConstraintslSingle Inheritance vs.Multiple InheritancelUser-defined vs.Condition-definedlDisjoint vs.OverlappinglCompleteness constraint19User-defined vs.Condition-definedlConstraint on which entities can be members of a given lower-level entity setcondition-definedlE.g.all customers over 65 years are members of senior-citizen entity set;senior-citizen ISA personuser-definedlE.g.employees dispatching20Disjoint vs.OverlappinglConstraint on whether or not entities may belong to more than one lower-level entity set within a single generalizationDisjointlan entity can belong to only one lower-level entity setlNoted in E-R diagram by writing disjoint next to the ISA triangleOverlappinglan entity can belong to more than one lower-level entity set21Completeness constraintlspecifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalizationTotal lan entity must belong to one of the lower-level entity setsPartiallan entity need not belong to one of the lower-level entity sets22Scenario 2BodyWheelEngineWe need treat the relationship as an entityExample 1 Cars and the registration marksAssemblelicense plateRegister23Scenario 2We need treat the relationship as an entityExample 2 Manager and work-onn Consider the ternary relationship works-on,which we saw earliern Suppose we want to record managers for tasks performed by an employee at a branch24AggregationlAn abstraction through which relationships are treated as higher-level entitiesPart-of RelationshipBodyWheelEngineAssembleRegistration markRegister25Relationship in EER ModellRelationship TypesAssociationsISA RelationshiplClassificationRelationship Aggregation26Symbols in EER27Symbols in EER(1)28Alternative E-R Notations29Main ContentslWeak Entity SetslExtended E-R FeatureslDesign of an E-R Database SchemalReduction of an E-R Schema to Tables 30Database Design ApproachFeasibility studyRequirements Analysis&SpecificationData ModelingProcess Modeling31Database Design PhasesRequirements Collection&Analysis RequirementRequirement CollectionMethodsConceptual DesignE-R ModelLogical DesignPhysical DesignApplication DesignDatabase SystemDBMSHPLs,Tools,OSRequirement SpecificationConceptual Database ModelLogical ModelsPerformance RequirementFinal SchemaApplication RequirementRequirement SpecificationApplication Programs32Conceptual DesignlCharacterize fully the data needs of the prospective database userslTranslate these requirements into a conceptual schema of the databaselReview the schema to ensure it meets functional requirements33Design DecisionslD1attributes vs.entities(section 2.2.1)lD2entity sets vs.relationship sets(section 2.2.2)lD3N-ary relationships vs.binary relationships(section 2.2.3)34Design Decisions(1)lD4strong entities vs.weak entities(section 2.6)lD5The use of specialization/generalization contributes to modularity in the design(section 2.7.2)lD6The use of aggregation can treat the aggregate entity set as a single unit without concern for the details of its internal structure(section 2.7.5)35Steps for E-R Modeling1.(Data)Requirement Collection(Data)Requirement Specification2.Entity Sets DesignationEntity NamingAdding AttributesAssigning Keys3.Relationship Sets DesignationDistinguishing typesAdding ConstraintsAdding Attributes4.Drawing E-R Diagrams36E-R Diagram for a Bank37Main ContentslWeak Entity SetslExtended E-R FeatureslDesign of an E-R Database SchemalReduction of an E-R Schema to Tables 38Conceptual Model to Logical ModellReduction of an E-R Schema to TablesConverting an E-R diagram to a table format is the basis for deriving a relational database design from an E-R diagramA database which conforms to an E-R diagram can be represented by a collection of tables39General RuleslFor each entity set and relationship set there is a unique table which is assigned the name of the corresponding entity set or relationship setlEach table has a number of columns(generally corresponding to attributes),which have unique nameslPrimary keys allow entity sets and relationship sets to be expressed uniformly as tables which represent the contents of the database40Strong Entity SetslA strong entity set reduces to a table with the same attributes.41Composite AttributeslComposite attributes are flattened out by creating a separate attribute for each component attributeE.g.given entity set customer with composite attribute name with component attributes first-name and last-name the table corresponding to the entity set has two attributes name.first-name name.last-nameCustomernameLast-nameFirst-name42Multi-valued AttributeslA multi-valued attribute M of an entity E is represented by a separate table EMTable EM has attributes corresponding to the primary key of E and an attribute corresponding to multi-valued attribute MlE.g.Multi-valued attribute dependent-names of employee is represented by a table employee-dependent-names(employee-id,dname)Each value of the multi-valued attribute maps to a separate row of the table EMlE.g.,an employee entity with primary key John and dependents Johnson and Mary maps to two rows:(John,Johnson)and(John,Johndotir)Employee-iddnameJohnJohnsonJohnMary43Weak Entity SetsnA weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set44M-M Relationship SetslA many-to-many relationship set is represented as a table with columns for the primary keys of the two participating entity sets,and any descriptive attributes of the relationship set.lE.g.:table for relationship set borrower45M-O/O-M Relationship SetsnMany-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the many side,containing the primary key of the one sidenE.g.:Instead of creating a table for relationship account-branch,add an attribute branch to the entity set account46M-O/O-M Relationship Sets(c1)lFor one-to-one relationship sets,either side can be chosen to act as the“many”sideThat is,extra attribute can be added to either of the tables corresponding to the two entity sets lIf participation is partial on the many side,replacing a table by an extra attribute in the relation corresponding to the“many”side could result in null values47Identifying RelationshiplThe table corresponding to a relationship set linking a weak entity set to its identifying strong entity set is redundantE.g.The payment table already contains the information that would appear in the loan-payment table(i.e.,the columns loan-number and payment-number).48Entity Sets in ISA RelationshiplMethod 1Form a table for the higher level entity Form a table for each lower level entity set,include primary key of higher level entity set and local attributes table table attributespersonname,street,city customername,credit-ratingemployeename,salaryDrawbackl getting information about,e.g.,employee requires accessing two tables49Entity Sets in ISA Relationship(c1)lMethod 2Form a table for each entity set with all local and inherited attributestable table attributespersonname,street,citycustomername,street,city,credit-ratingemployee name,street,city,salary50Entity Sets in ISA Relationship(c2)If specialization is total,table for generalized entity(person)not required to store informationlCan be defined as a“view”relation containing union of specialization tablesDrawbacklstreet and city may be stored redundantly for persons who are both customers and employees51Aggregation RelationshipTo represent aggregation,create a table containingn primary key of the aggregated relationship,nthe primary key of the associated entity setnAny descriptive attributes52Example for AggregationnE.g.to represent aggregation manages between relationship works-on and entity set manager,create a table manages(employee-id,branch-name,title,manager-name)nTable works-on is redundant provided we are willing to store null values for attribute manager-name in table manages53SummarylWhy We need WES?lHow many relationship set types in EER?lDoes the reduction of an E-R Model to tables be unique?lCan we build a E-R Model from the tables?54HomeworkExercise 2.12(c)Read and Extend a E-R diagram55