海南大学：《数据库原理及应用》课程PPT教学课件（英文版）Chapter 2 Entity-Relationship Model

Chapter 2: Entity-Relationship Model Basic Concepts Constraints Keys Design Issues E-R Diagram Weak Entity Sets Extended E-R Features Design of an E-R Database Schema Reduction of an E-R Schema to Tables 标 Database System Concepts 2.1 @Silberschatz, Korth and Sudarshan

Database System Concepts 2.1 ©Silberschatz, Korth and Sudarshan Chapter 2: Entity-Relationship Model Basic Concepts Constraints Keys Design Issues E-R Diagram Weak Entity Sets Extended E-R Features Design of an E-R Database Schema Reduction of an E-R Schema to Tables

Basic Concepts Entity Sets a database can be modeled as a collection of entities relationship among entities An entity is an object that exists and is distinguishable from other objects Example: specific person, company, event, plant Entities have attributes Example: people have names and addresses An entity set is a set of entities of the same type that share the same properties Example: set of all persons, companies, trees holidays Database System Concepts 2.2 OSilberschatz. Korth and Sudarshan

Database System Concepts 2.2 ©Silberschatz, Korth and Sudarshan Basic Concepts Entity Sets A database can be modeled as: a collection of entities, relationship among entities. An entity is an object that exists and is distinguishable from other objects. Example: specific person, company, event, plant Entities have attributes Example: people have names and addresses An entity set is a set of entities of the same type that share the same properties. Example: set of all persons, companies, trees, holidays

Entity Sets customer and loan customer-id customer- customer- customer- loan- amount name street city number 321-12-3123|ones Main Harrison L-171000 019-28-3746 Smith North Rye 2320 677-89-9011 Haves Main Harrison L-151500 555-55-5555 Jackson Dupont Woodside L-141500 244-66-8800 Curry North Rye L-195 963-96-3963 Williams Nassau Princeton L-11900 335-57-7991 Adams SpringPittsfield L-161300 customer loan Database System Concepts 2.3 @Silberschatz, Korth and Sudarshan

Database System Concepts 2.3 ©Silberschatz, Korth and Sudarshan Entity Sets customer and loan customer-id customer- customer- customer- loan- amount name street city number

Basic Concepts(Cont) Attributes An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set Domain- the set of permitted values for each attribute Attribute types Simple and composite attributes Single-valued and multi-valued attributes E.g. multivalued attribute: phone-numbers Derived attributes Can be computed from other attributes E.g. age, given date of birth Database System Concepts 2 @Silberschatz, Korth and Sudarshan

Database System Concepts 2.4 ©Silberschatz, Korth and Sudarshan Basic Concepts (Cont.) Attributes An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set. Domain – the set of permitted values for each attribute Attribute types: Simple and composite attributes. Single-valued and multi-valued attributes – E.g. multivalued attribute: phone-numbers Derived attributes – Can be computed from other attributes – E.g. age, given date of birth

Composite Attributes Composite 11e address Attributes first-name middle-initial last-name street city state postal-code Component Attributes street-number street-name apartment-mumber Database System Concepts 2.5 OSilberschatz. Korth and Sudarshan

Database System Concepts 2.5 ©Silberschatz, Korth and Sudarshan Composite Attributes

Basic Concepts(Cont) Relationship sets A relationship is an association among several entities Example: Hayes depositor A-102 customer entity relationship set account entity A relationship set is a mathematical relation among n2 2 entities, each taken from entity sets {(e1e2,……en)le1∈E n Eny where (e1, e2, ., en)is a relationship Example: (Hayes, A-102)E depositor 标 Database System Concepts 2.6 @Silberschatz, Korth and Sudarshan

Database System Concepts 2.6 ©Silberschatz, Korth and Sudarshan Basic Concepts (Cont.) Relationship Sets A relationship is an association among several entities Example: Hayes depositor A-102 customer entity relationship set account entity A relationship set is a mathematical relation among n  2 entities, each taken from entity sets {(e1 , e2 , … en ) | e1  E1 , e2  E2 , …, en  En } where (e1 , e2 , …, en ) is a relationship Example: (Hayes, A-102)  depositor

Relationship Set borrower 321-12-3123 Jones MainHarrison L-171000 019-28-3746 Smith North Rye L232000 677-89-9011 HayesMainHarrison L-151500 555-55-5555 Jackson Dupont Woodside -141500 24466-8800Cury North Rve L-19|500 963-96-3963 Williams Nassau Princeton L-11|900 335-57-7991 Adams Spring Pittsfield L-16|1300 customer loan Database System Concepts 2.7 OSilberschatz. Korth and Sudarshan

Database System Concepts 2.7 ©Silberschatz, Korth and Sudarshan Relationship Set borrower

Basic Concepts(Cont An attribute can also be property of a relationship set For instance, the depositor relationship set between entity sets customer and account may have the attribute access-date depositor(access-date) account(account-number) customer(customer-name) 24May1996 3 June 1996 A-101 Johnson 21June1996 A-215 Smith 10June1996 A-102 H aves A305 Turner 17June1996 28May1996 A-201 Jones 28May1996 A-222 Lindsay 24June1996 A-217 23May1996 Database System nd Sudarshan

Database System Concepts 2.8 ©Silberschatz, Korth and Sudarshan Basic Concepts(Cont.) An attribute can also be property of a relationship set. For instance, the depositor relationship set between entity sets customer and account may have the attribute access-date

Basic Concepts(Cont Begree of a Relationship Set Refers to number of entity sets that participate in a relationship set Relationship sets that involve two entity sets are binary (or degree two). Generally, most relationship sets in a database system are binary Relationship sets may involve more than two entity sets E.g. Suppose employees of a bank may have jobs (responsibilities) at multiple branches, with different jobs at different branches. Then there is a ternary relationship set between entity sets employee, /gb and branch Relationships between more than two entity sets are rare. Most relationships are binary. (More on this later 5 Database System Concepts 2.9 OSilberschatz. Korth and Sudarshan

Database System Concepts 2.9 ©Silberschatz, Korth and Sudarshan Basic Concepts(Cont.) Degree of a Relationship Set Refers to number of entity sets that participate in a relationship set. Relationship sets that involve two entity sets are binary (or degree two). Generally, most relationship sets in a database system are binary. Relationship sets may involve more than two entity sets. Relationships between more than two entity sets are rare. Most relationships are binary. (More on this later.) E.g. Suppose employees of a bank may have jobs (responsibilities) at multiple branches, with different jobs at different branches. Then there is a ternary relationship set between entity sets employee, job and branch

Constraints Mapping Cardinalities Express the number of entities to which another entity can be associated via a relationship set. Most useful in describing binary relationship sets For a binary relationship set the mapping cardinality must be one of the following types: One to one One to many Many to one Many to many Database System Concepts 2.10 OSilberschatz. Korth and Sudarshan

Database System Concepts 2.10 ©Silberschatz, Korth and Sudarshan Constraints Mapping Cardinalities Express the number of entities to which another entity can be associated via a relationship set. Most useful in describing binary relationship sets. For a binary relationship set the mapping cardinality must be one of the following types: One to one One to many Many to one Many to many