1、COMP2311COMP231ER DiagramCOMP2312Entity-Relationship ModelEntity-Relationship (ER) model is a popular conceptual data model. This model is used in the design of database applicationsThe model describes data to be stored and the constraints over the data.E-R model views the real world as a collection

2、 of entities and relationships among entities.COMP2313OutlineEntityRelationshipBinary relationshipWeak Entity/Strong EntityClass HierarchyRelationshipNon-Binary relationshipCOMP2314Entities and attributesAn entity is an object in the real world that is distinguishable from other objectsE.g.,A classr

3、oomA teacherThe address of the teacherCOMP2315Entities and attributesAn entity is described using a set of attributes whose values are used to distinguish one entity from another of same typename = Chan Tai Manaddress = 25, Siu Road, Shatinage = 55phone = 1234-5667COMP2316Entities and attributesAn e

4、ntity set is a collection of entities of the same typee1e2e3(Chan Tai Man, 55, )(Cheung Tin, 20, )(Wong King, 33, )COMP2317Entities and attributesAll entries in a given entity set have the same attributes (the values may be different).employee = (name, address, age, phone)name = Chan Tai Manaddress

5、= 25, Siu Road, Shatinage = 55phone = 1234-5667employee 1name = Cheung Tinaddress = 25, Big Street, Shatinage = 20phone = 2338-7779employee 2COMP2318ER DiagramThe ER model can be presented graphically by an ER diagramageEmployeenameaddressphoneentityattributeCOMP2319Different attribute typesSimple a

6、ttributeComposite attributeMulti-valued attributeDerived attributeCOMP23110Simple attributeSimple attributecontains a single valueageEmployeenameaddressphoneentityattributeCOMP23111Composite attributeComposite attributeContains several componentsageEmployeenameaddressphoneattributestreetcitycountrye

7、ntityCOMP23112Multi-valued attributeMulti-valued attributeContains more than one valueageEmployeenameaddressphoneattributestreetcitycountryDouble ovalsentityCOMP23113Derived attributeDerived attributeComputed from other attributese.g., age can be computed from data of birth and the current dateageEm

8、ployeenameaddressphoneattributestreetcitycountryOne dashed ovalentityDate of BirthCOMP23114Key attributesKey 數據庫表中對儲存數據對象予以唯一和完整標識的數據列或屬性的組合。A set of attributes that can uniquely identity an entityE.g., HKIDEmployeenameHKIDunderlineCOMP23115Key attributesComposite KeyTwo or more attributes are used

9、to serve as a keyE.g., Name or Address alone cannot uniquely identify an employee, but together they canEmployeeAddressNameCOMP23116Key attributesAn entity may have more than one keyE.g., HKID and Name, Address both are two keysEmployeeAddressNameHKIDIs HKID, Name, Address a key?COMP23117Key attribu

10、tesA minimal set of attributes that uniquely identifies an entity is called a candidate key.E.g., HKID and Name, Address both are two candidate keysIs HKID, Name, Address a candidate key?If there are many candidatekeys, we should chooseone candidate key asthe primary key.COMP23118Key attributesSomet

11、imes, artificial keys can be createdE.g., if there is no HKID stored in Employee, we can create a new attribute called “EmpID”EmployeeGenderNameEmpIDCOMP23119OutlineEntityRelationshipBinary relationshipWeak Entity/Strong EntityClass HierarchyRelationshipNon-Binary relationshipCOMP23120RelationshipA

12、relationship is an association among several entitiesThe degree refers to the number of entity sets that participate in a relationship set.Relationship sets that involve two entity sets are binary (or degree two).Relationships among more than two entity sets are rare. (We will discuss later in detai

13、ls)COMP23121Binary RelationshipEmployees work in departments“Work_in” is a relationship between Employees and DepartmentsIt means that an employee works in multiple departments and vice versaEmpIDWork_inEmployeesDepartmentsNamediddnameCOMP23122Binary RelationshipA relationship can also have attribut

14、es which are used to describe the record information about the relationship (instead of the information of each individual entity).EmpIDWork_inEmployeesDepartmentsNamediddnameStart_dateStart date of the employmentCOMP23123Recursive RelationshipRecursive RelationshipEntity sets of a relationship need

15、 not be distinctSometimes, a relationship might involve two entities in the same entity setE.g., Employees related to employeesEmpIDEmployeesNameReports_tosupervisorSubordinate下級COMP23124Recursive RelationshipSince employees report to other employeesEvery relationship in “Reports_To” is of form (emp

16、1, emp2) where both emp1 and emp2 are entities in employees.However, they play different roles.emp1 reports to emp2, which is reflect in the role indicators supervisor and subordinate in the diagramEmpIDEmployeesNameReports_tosupervisorsubordinateCOMP23125ConstraintsThe model describes data to be st

17、ored and the constraints over the dataCOMP23126ConstraintsThe mapping of a binary relationship can be classified into the following cases:Many-to-Many1-to-11-to ManyMany-to-1COMP23127One-to-many relationshipOne-to-many (1-to-Many) relationshipAn entity in B can be associated with at most one entity

18、in A1-to ManyABCOMP23128One-to-many relationshipA loan is associated with at most one customer via borrowerA customer is associated with several (including 0) loans via borrower1-to ManyA cusB loanKey = customer-id, loan-numberNot a candidate key!Candidate key = ?COMP23129Many-to-one relationshipMan

19、y-to-one (Many-to-1) relationshipSimilar to 1-to-manyMany-to-1ABCOMP23130Many-to-one relationshipA loan is associated with several (including 0) customers via borrower, A customer is associated with at most one loan via borrowerMany-to-1ABKey = customer-id, loan-numberNot a candidate key!Candidate k

20、ey = ?COMP23131One-to-one relationshipOne-to-one (1-to-1) relationshipAn entity in A is related to at most one entity in BAn entity in B is related to at most one entity in A.1-to-1ABCOMP23132One-to-one relationshipA customer is associated with at most one loan via the relationship borrowerA loan is

21、 associated with at most one customer via borrower1-to-1ABKey = customer-id, loan-numberNot a candidate key!Candidate key = ?COMP23133Many-to-many RelationshipMany-to-many RelationshipAn entity in A is associated with any number of entities in B An entity in B is associated with any number of entiti

22、es in AThat is, there is no restriction in the mapping.Many-to-ManyABCOMP23134Many-to-many RelationshipA customer is associated with several (possibly 0) loans via borrowerA loan is associated with several (possibly 0) customers via borrowerMany-to-ManyABKey = customer-id, loan-numberCandidate key!C

23、OMP23135Participation ConstraintThe above constraints (e.g., 1-to-many) tells us that a customer borrows some loans. A natural question to ask is to whether every loan is borrowed by at least one customer.Suppose that each loan is borrowed by at least one customer. Such a constraint is called a part

24、icipation constraint.1-to ManyABCOMP23136Participation ConstraintWe can classify participation in relationships as follows.Total Each entity in the entity set must be associated in at least one relationshipPartialEach entity in the entity set may (or may not) be associated in a relationship1-to Many

25、ABCOMP23137Participation ConstraintThe participation of loan in borrower is totalEvery loan must be borrowed by a customerThe participation of customer in borrow is partialSome customers may (or may not) borrow loans COMP23138OutlineEntityRelationshipBinary relationshipWeak Entity/Strong EntityClass

26、 HierarchyRelationshipNon-Binary relationshipCOMP23139Weak EntitiesStrong EntityAn entity can be uniquely identified by some attributes related to this entityE.g., Employee has an attribute EmpID (which can be used to uniquely identify each employee)Weak EntityAn entity cannot be uniquely identified

27、 by all attributes related to this entityCOMP23140Weak EntitiesExampleSuppose employees can purchase insurance policies to cover their dependents.The attribute of the dependents entity set are pname and ageThe attribute pname cannot uniquely identify a dependentDependent is a weak entity set.A depen

28、dent can only be identified by considering some of its attributes in conjunction with the primary key of employee (identifying entity set).The set of attributes that uniquely identify a weak entity for a given owner entity is called a discriminator or partial key.COMP23141Weak EntitiesA dependent ca

29、nnot be uniquely identified by “pname”.Note: A child may not be old enough to have a HKID numberEven if he/she has a HKID number, the company may not be interested in keeping it in the database.EmpIDpnamePolicyageEmployeeDependentIdentifying entity set = EmployeeDiscriminator = pnameKey = EmpID, pna

30、meCOMP23142Weak EntitiesDefinition: If a weak entity set W is dependent on a strong entity set E, we say that E owns W.E.g., Employee owns DependentEmpIDpnamePolicyageEmployeeDependentIdentifying entity set = EmployeeDiscriminator = pnameKey = EmpID, pnameCOMP23143Weak EntitiesA payment itself canno

31、t be identified by “payment-number”loan-numberpayment-numberloan-paymentamountloanpaymentamountIdentifying entity set = loanDiscriminator = payment-numberKey = loan-number, payment-numberloan owns paymentCOMP23144OutlineEntityRelationshipBinary relationshipWeak Entity/Strong EntityClass HierarchyRel

32、ationshipNon-Binary relationshipCOMP23145Class HierarchySometimes, it is natural to classify the entities in an entity set into subclassesHour_wagesContract_IDContract_EmpsISAEmployeesnameIDaddressHourly_EmpsHour_workedCOMP23146Class HierarchyAttributes are inherited by the entity set in the subclas

33、s.E.g., the attributes defined for an Hourly_Emps entity are the attributes for Employees plus that of Hourly_EmpsA class hierarchy can be viewed in one of the two ways.A class is specialized into subclasses.The subclasses are generalized by a superclass.COMP23147Class HierarchyWe can specify two kinds of constraints with respect to ISA hierarchiesOverlap constraintsDetermine whether two subclasses are allowed to contain the same entitye.g., Can an employee be an Hourly_Emps as well as a Contract_emps entity?Covering constraintsDetermine whether the entities in th