数据库管理系统概述英文版课件：4 Relational Model

1、COMP231Relational ModelCOMP2311OutlineIntroductionTerminologyER-to-Relational MappingCOMP2312IntroductionThe relational model was first introduced by Ted Codd of IBM Research in 1970and attracted due to its simplicity, elegance and mathematical foundationsThe model uses the concept of a mathematical

2、 relation which looks like a table of valuesCOMP2313OutlineIntroductionTerminologyER-to-Relational MappingCOMP2314TerminologyRelation table; denoted by R(A1, A2, ., An) where R is a relation name and (A1, A2, ., An) is the relation schema of RAttribute (column) denoted by AiTuple (Record) rowAttribu

3、te value value stored in a table cellDomain legal type and range of values of an attributedenoted by dom(Ai)Attribute: AgeDomain: 0-100Attribute: EmpNameDomain: 50 alphabetic charsAttribute: SalaryDomain: non-negative integerCOMP2315TerminologyAttributes/Columns (collectively as a schema)Tuples/Rows

4、Relation Name/Table NameCOMP2316SchemaThe relation schema is (Name, Student-id, Age, CGA)ORThe primary key is underlined in the aboveNameStudent-idAgeCGACOMP2317Foreign KeyA foreign key is a set of attributes in one relation r that is used to refer to a tuple in another relation s. (it must correspo

5、nd to the primary key of the second relation)COMP2318Foreign KeyStudent(Student-id, Student-Name)Take(Student-id, Course-id, semesterNo)Student-id in relation Student is a primary keyStudent-id in relation Take is a foreign key COMP2319Foreign KeyStudent(Student-id, Student-Name)Take(Student-id, Cou

6、rse-id, semesterNo)Course(Course-id, Course-Name)Course-id in relation Course is a primary keyCourse-id in relation Take is a foreign keyCOMP23110OutlineIntroductionTerminologyER-to-Relational MappingCOMP23111ER-to-Relational MappingTypically, database designers begin with the ER model, which is ver

7、y expressive and user-friendly to humanThen, the ER model is mapped to the relational model for DBMS manipulationsDatabase queries and updates will be written according to the relational modelCOMP23112ER-to-Relational MappingTranslating traditional ER diagramsTranslating Class HierarchyCOMP23113Depe

8、ndentDependent_ofDependentnamesexbdaterelationshipControlsProjectpnamepnumberplocationsupervisorsupervisionsuperviseeworks_onDependent_ofhoursDepartmentEmployeesexnameeidbdateaddrsalaryworks_forManagesstart_datenumberdnameCOMP23114ControlsProjectpnamepnumberplocationworks_onhoursDepartmentEmployeese

9、xnameeidbdateaddrsalaryworks_forManagesstart_datenumberdnameCOMP23115StepsStep 1 (Strong Entity Set) Step 2 (Weak Entity Set)Step 3 (1-to-1 Relationship)Step 4 (1-to-many Relationship)Step 5 (Many-to-many Relationship)Step 6 (Non-binary Relationship)COMP23116Step 1 (Strong Entity)For each strong ent

10、ity set E in the ER schema, create a relation schema R that includes all the attributes of E. choose one set of key attributes of E as a primary key for R.COMP23117DependentDependent_ofDependentnamesexbdaterelationshipControlsProjectpnamepnumberplocationsupervisorsupervisionsuperviseeworks_onDepende

11、nt_ofhoursDepartmentEmployeesexnameeidbdateaddrsalaryworks_forManagesstart_datenumberdnameCOMP23118ProjectpnamepnumberplocationDepartmentEmployeesexnameeidbdateaddrsalarynumberdnameCOMP23119Step 1 (Strong Entity)ExampleWe create the relation schemas EMPLOYEE, DEPARTMENT and PROJECT.nameEMPLOYEEDEPAR

12、TMENTPROJECTeidbdateaddrsexsalarydnamednumberpnamepnumberplocationCOMP23120If there is a derived attribute, what should we do?We have two choices.Choice 1: Include this derived attribute Adv: We can directly obtain the value of the derived attribute Disadv: We may encounter some data inconsistencies

13、Choice 2: NOT include this derived attribute Adv: We can avoid data inconsistency Disadv: We need to perform some operations to obtain the value of the derived attributeCOMP23121If there is a composite attribute, what should we do?We have two choices.Choice 1: Include the high-level attribute only (

14、e.g., address)Choice 2: Include all low-level attributes (e.g., street, city, country)EmployeeaddressstreetcitycountryCOMP23122If there is a multi-valued attribute, what should we do?We have two choices.Choice 1: Include one attribute only (e.g., phone)Choice 2: Create another table containing the p

15、rimary key of the entity set and the multi-valued attributee.g., create a schema PhoneTable (eid, phone)EmployeephoneeidCOMP23123Step 2 (Weak Entity)For each weak entity set W in the ER model, create a relation schema R, and include all attributes.In addition, include the primary key(s) of the owner

16、(s). The primary key of R is the combination of the primary key(s) of the owner(s) and the discriminator of the weak entity set W.COMP23124DependentDependent_ofDependentnamesexbdaterelationshipControlsProjectpnamepnumberplocationsupervisorsupervisionsuperviseeworks_onDependent_ofhoursDepartmentEmplo

17、yeesexnameeidbdateaddrsalaryworks_forManagesstart_datenumberdnameCOMP23125DependentDependent_ofDependentnamesexbdaterelationshipDependent_ofEmployeesexnameeidbdateaddrsalaryCOMP23126Dependenteidsexdependent-namebdaterelationshipCOMP23127Step 3 (1-to-1 Relationship)For each binary one-to-one (1:1) re

18、lationship set R T - SChoose one of the 2 relation schemas, say S,get primary key of T, include it as foreign keys in S. Better if S has total participation in RInclude the attributes of the relationship set R as attributes of S.COMP23128DependentDependent_ofDependentnamesexbdaterelationshipControls

19、Projectpnamepnumberplocationsupervisorsupervisionsuperviseeworks_onDependent_ofhoursDepartmentEmployeesexnameeidbdateaddrsalaryworks_forManagesstart_datenumberdnameCOMP23129DepartmentEmployeesexnameeidbdateaddrsalaryManagesstart_datenumberdnameCOMP23130We include the primary key of EMPLOYEE as forei

20、gn key in DEPARTMENT and rename it mgr_id. We include the attribute startdate of MANAGES and rename it mgr_start_date.DEPARTMENTdnamednumbermgr_idmgr_start_dateCOMP23131Compare the following two choicesto include MANAGES:Add information to EMPLOYEEAdd to DEPARTMENTEMPLOYEEMANAGESDEPARTMENTCOMP23132I

21、n the above, the NULL value is a special value meaning that the value is either unknown or not applicable.Notice that an alternative mapping of a one-to-one relationship set is possible by merging the two entity sets and the relationship into a single relation. This is appropriate when both particip

22、ations are total.Step 3:Advantage: The total number of relations remain unchangedDisadvantage: It may store NULL valuesif there is no total participationCOMP23133DepartmentEmployeesexnameeidbdateaddrsalaryManagesstart_datenumberdnameCan we create a new relation Manages (eid, number, start_date) OrMa

23、nages (eid, number, start_date) for this relationship?Yes. It can be used if there are onlya few relationship instancesAdvantage: It can avoid storing NULL valuesif there is no total participationDisadvantage: There is one additionalrelationCOMP23134Step 4 (1-to-many Relationship)For each binary one

24、-to-many relationship set T - SInclude as foreign key in S the primary key that represents the other entity set T participating in R. Include any attributes of the one-to-many relationship set as attributes of S.COMP23135DependentDependent_ofDependentnamesexbdaterelationshipControlsProjectpnamepnumb

25、erplocationsupervisorsupervisionsuperviseeworks_onDependent_ofhoursDepartmentEmployeesexnameeidbdateaddrsalaryworks_forManagesstart_datenumberdnameCOMP23136ControlsProjectpnamepnumberplocationsupervisorsupervisionsuperviseeDepartmentEmployeesexnameeidbdateaddrsalaryworks_fornumberdnameCOMP23137The p

26、rimary key dnumber of the DEPARTMENT relation schema is included as foreign key in the EMPLOYEE relation schema. We rename it as dno. (The renaming is not necessary but makes the name more meaningful.)nameEMPLOYEEeidbdateaddrsexsalarydnoEMPLOYEEWORKS_FORDEPARTMENTCOMP23138Compare the following 2 cho

27、ices:EMPLOYEEDEPARTMENTAdd employees to departmentAdd department to employeeCOMP23139For SUPERVISON, include the primary key of the EMPLOYEE as foreign key in the EMPLOYEE, and call it super_EMPLOYEEeidbdateaddrsexsalarydnosuper_idpnamePROJECTpnumberplocationdnum For CONTROLS relationship, in

28、clude dnum as foreign key in PROJECT, which references the primary key dnumber of DEPARTMENT.COMP23140ControlsProjectpnamepnumberplocationsupervisorsupervisionsuperviseeDepartmentEmployeesexnameeidbdateaddrsalaryworks_fornumberdnameCan we create a new relation works_for (eid, number) for this relati

29、onship?Yes. COMP23141Step 5 (Many-to-many Relationship)For each binary many-to-many relationship set R, create a new relation schema S to represent R.Include as foreign key attributes in S the primary keys of the relation schemas for the participating entity sets in Rtheir combination will form the

30、primary key of S. Also include attributes of the many-to-many relationship set as attributes of S.COMP23142DependentDependent_ofDependentnamesexbdaterelationshipControlsProjectpnamepnumberplocationsupervisorsupervisionsuperviseeworks_onDependent_ofhoursDepartmentEmployeesexnameeidbdateaddrsalarywork

31、s_forManagesstart_datenumberdnameCOMP23143Projectpnamepnumberplocationworks_onhoursEmployeesexnameeidbdateaddrsalaryCOMP23144Map the many-to-many relationship setsWORKS_ON by creating the relation schema WORKS_ON.Include the primary keys of PROJECT and EMPLOYEE as foreign keys.eidWORKS_ONpnumberhour

32、sCOMP23145Compare the following three choices to include works_onpnumberhoursNullNullC7789A0110A01101A0122C7757nameidbdateaddrsexsalarydnoYeung7080370F20K3Chan3031060M30K4Chan3031060M30K4Wong4010280F10K7Cheung8220985M24K1Cheung8220985M24K1Add to EMPLOYEE Add to PROJECTNew relation WORKS_ONEMPLOYEEWO

33、RKS_ONPROJECTCOMP23146Step 6 (Non-binary Relationship)For each non-binary relationship set, create a new relation schema S to represent R. Include as foreign key attributes in S the primary keys of the participating entity sets. Also include any attributes of the non-binary relationship set as attri

34、butes of S.COMP23147For non-binary relationships,The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity sets. However if any entity set has a key constraint on the relationship (e.g., 1-to-many relationship), the prima

35、ry key of the entity set can be used as a key for the relationship.COMP23148DEPARTMENTdnamemgr_iddnumbermgr_start_datenameEMPLOYEEeidbdateaddrsexsalarydnopnamePROJECTpnumberplocationdnumeidWORKS_ONpnumberhoursResulting relation schemas:COMP23149DEPARTMENTdnamemgr_iddnumbermgr_start_datenameEMPLOYEEeidbdateaddrsexsalarydnosuper_idpnamePROJECTpnumberplocationdnumDEPENDENTeidsexdependent-namebdaterelationshipeidWORKS_ONpnumberhoursResult