Chapter 6:Entity-Relationship Model Design Process Modeling Constraints E-R Diagram Design Issues Weak Entity Sets Extended E-R Features Design of the Bank Database Reduction to Relation Schemas Database Design UML Database System Concepts-5th Edition,Oct 5,2006 6.2 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 6.2 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Chapter 6: Entity-Relationship Model Design Process Modeling Constraints E-R Diagram Design Issues Weak Entity Sets Extended E-R Features Design of the Bank Database Reduction to Relation Schemas Database Design UML
Modeling 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-5th Edition,Oct 5,2006 6.3 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 6.3 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Modeling 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-17 1000 019-28-3746 Smith North Rye L-23 2000 677-89-9011 Hayes Main Harrison L-15 1500 555-55-5555 Jackson Dupont Woodside L-14 1500 244-66-8800 Curry North Rye 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-5th Edition,Oct 5,2006 6.4 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 6.4 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Entity Sets customer and loan customer_id customer_ customer_ customer_ loan_ amount name street city number
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)le1∈E1,e2∈E2,…,en∈En} where(e1,e2,...,e)is a relationship Example: (Hayes,A-102)E depositor Database System Concepts-5th Edition,Oct 5,2006 6.5 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 6.5 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 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 Tones Main Harrison L-17 1000 019-28-3746 Smith North Rye L-23 2000 677-89-9011 Hayes Main Harrison L-15 1500 555-55-5555 Jackson Dupont Woodside L-14 1500 244-66-8800 Curry North Rye 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-5th Edition,Oct 5,2006 6.6 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 6.6 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Relationship Set borrower
Relationship Sets (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) 24May2005 3 June 2005 A-101 lohnson 21June2005 A-215 Smith 10June2005 A-102 Hayes 17june2005 A-305 Turner 28May2005 A-201 Tones 28May2005- A-222 Lindsay 24June2005 A-217 23May2005 Database System Concepts-5th Edition,Oct 5,2006 6.7 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 6.7 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Relationship Sets (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
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. Example: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 Relationships between more than two entity sets are rare.Most relationships are binary.(More on this later. Database System Concepts-5th Edition,Oct 5,2006 6.8 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 6.8 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 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.) Example: 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
Attributes An entity is represented by a set of attributes,that is descriptive properties possessed by all members of an entity set. Example: customer=(customer id,customer_name, customer street,customer city) loan =(loan number,amount) Domain-the set of permitted values for each attribute Attribute types: Simple and composite attributes. Single-valued and multi-valued attributes Example:multivalued attribute:phone_numbers Derived attributes Can be computed from other attributes Example:age,given date_of_birth Database System Concepts-5th Edition,Oct 5,2006 6.9 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 6.9 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 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 Example: multivalued attribute: phone_numbers Derived attributes Can be computed from other attributes Example: age, given date_of_birth Example: customer = (customer_id, customer_name, customer_street, customer_city ) loan = (loan_number, amount )
Composite Attributes Composite name address Attributes first_name middle_initial last_name street city state postal_code Component Attributes street_number street_name apartment_number Database System Concepts-5th Edition,Oct 5,2006 6.10 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 6.10 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Composite Attributes
Mapping Cardinality Constraints 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-5th Edition,Oct 5,2006 6.11 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 6.11 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Mapping Cardinality Constraints 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