《数据库系统概念 Database System Concepts》原书教学资源（第六版，PPT课件讲稿，英文版）Chapter 7 Database Design - The Entity-Relationship Approach

Chapter 7: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 7.2 @Silberschatz,Korth and Sudarshan

Database System Concepts 7.2 ©Silberschatz, Korth and Sudarshan Chapter 7: 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

Design Phases The initial phase of database design is to characterize fully the data needs of the prospective database users. Next,the designer chooses a data model and,by applying the concepts of the chosen data model,translates these requirements into a conceptual schema of the database. A fully developed conceptual schema also indicates the functional requirements of the enterprise.In a "specification of functional requirements",users describe the kinds of operations (or transactions)that will be performed on the data. Database System Concepts 7.3 @Silberschatz,Korth and Sudarshan

Database System Concepts 7.3 ©Silberschatz, Korth and Sudarshan Design Phases The initial phase of database design is to characterize fully the data needs of the prospective database users. Next, the designer chooses a data model and, by applying the concepts of the chosen data model, translates these requirements into a conceptual schema of the database. A fully developed conceptual schema also indicates the functional requirements of the enterprise. In a “specification of functional requirements”, users describe the kinds of operations (or transactions) that will be performed on the data

Design Phases(Cont.) The process of moving from an abstract data model to the implementation of the database proceeds in two final design phases. Logical Design-Deciding on the database schema. Database design requires that we find a "good"collection of relation schemas. Business decision -What attributes should we record in the database? Computer Science decision-What relation schemas should we have and how should the attributes be distributed among the various relation schemas? Physical Design-Deciding on the physical layout of the database Database System Concepts 7.4 ©Silberschat乜，Korth and Sudarshan

Database System Concepts 7.4 ©Silberschatz, Korth and Sudarshan Design Phases (Cont.) Logical Design – Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas. Business decision – What attributes should we record in the database? Computer Science decision – What relation schemas should we have and how should the attributes be distributed among the various relation schemas? Physical Design – Deciding on the physical layout of the database The process of moving from an abstract data model to the implementation of the database proceeds in two final design phases

Design Approaches Entity Relationship Model(covered in this chapter) Models an enterprise as a collection of entities and relationships Entity:a“thing”or“object'”in the enterprise that is distinguishable from other objects -Described by a set of attributes Relationship:an association among several entities Represented diagrammatically by an entity-relationship diagram: Normalization Theory(Chapter 8) Formalize what designs are bad,and test for them Database System Concepts 7.5 @Silberschatz,Korth and Sudarshan

Database System Concepts 7.5 ©Silberschatz, Korth and Sudarshan Design Approaches Entity Relationship Model (covered in this chapter) Models an enterprise as a collection of entities and relationships  Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects – Described by a set of attributes  Relationship: an association among several entities Represented diagrammatically by an entity-relationship diagram: Normalization Theory (Chapter 8) Formalize what designs are bad, and test for them

Outline of the ER Model Database System Concepts 7.6 @Silberschatz,Korth and Sudarshan

Database System Concepts 7.6 ©Silberschatz, Korth and Sudarshan Outline of the ER Model

ER model--Database Modeling The ER data mode was developed to facilitate database design by allowing specification of an enterprise schema that represents the overall logical structure of a database. The ER model is very useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema. Because of this usefulness,many database-design tools draw on concepts from the ER model. The ER data model employs three basic concepts: entity sets, relationship sets, attributes. The ER model also has an associated diagrammatic representation,the ER diagram,which can express the overall logical structure of a database graphically. Database System Concepts 7.7 @Silberschatz,Korth and Sudarshan

Database System Concepts 7.7 ©Silberschatz, Korth and Sudarshan ER model -- Database Modeling The ER data mode was developed to facilitate database design by allowing specification of an enterprise schema that represents the overall logical structure of a database. The ER model is very useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema. Because of this usefulness, many database-design tools draw on concepts from the ER model. The ER data model employs three basic concepts: entity sets, relationship sets, attributes. The ER model also has an associated diagrammatic representation, the ER diagram, which can express the overall logical structure of a database graphically

Entity Sets An entity is an object that exists and is distinguishable from other objects. Example:specific person,company,event,plant 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 An entity is represented by a set of attributes;i.e.,descriptive properties possessed by all members of an entity set. Example: instructor =(ID,name,street,city,salary course=(course_id,title,credits) A subset of the attributes form a primary key of the entity set;i.e.,uniquely identifiying each member of the set. Database System Concepts 7.8 @Silberschatz,Korth and Sudarshan

Database System Concepts 7.8 ©Silberschatz, Korth and Sudarshan Entity Sets An entity is an object that exists and is distinguishable from other objects. Example: specific person, company, event, plant 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 An entity is represented by a set of attributes; i.e., descriptive properties possessed by all members of an entity set. Example: instructor = (ID, name, street, city, salary ) course= (course_id, title, credits) A subset of the attributes form a primary key of the entity set; i.e., uniquely identifiying each member of the set

Entity Sets--instructor and student instructor ID instructor name student-ID student name 76766 Crick 98988 Tanaka 45565 Katz 12345 Shankar 10101 Srinivasan 00128 Zhang 98345 Kim 76543 Brown 76543 Singh 76653 Aoi 22222 Einstein 23121 Chavez instructor 44553 Peltier student Database System Concepts 7.9 @Silberschatz,Korth and Sudarshan

Database System Concepts 7.9 ©Silberschatz, Korth and Sudarshan Entity Sets -- instructor and student instructor_ID instructor_name student-ID student_name

Relationship Sets A relationship is an association among several entities Example: 44553(Peltier) advisor 22222(Einstein） student entity relationship set instructor 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: (44553,22222)∈advisor Database System Concepts 7.10 @Silberschatz,Korth and Sudarshan

Database System Concepts 7.10 ©Silberschatz, Korth and Sudarshan Relationship Sets A relationship is an association among several entities Example: 44553 (Peltier) advisor 22222 (Einstein) student entity relationship set instructor 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: (44553,22222)  advisor

Relationship Set advisor 76766 Crick 98988 Tanaka 45565 Katz 12345 Shankar 10101 Srinivasan 00128 Zhang 98345 Kim 76543 Brown 76543 Singh 76653 Aoi 22222 Einstein 23121 Chavez instructor 44553 Peltier student Database System Concepts 7.11 ©Silberscha乜，Korth and Sudarshan

Database System Concepts 7.11 ©Silberschatz, Korth and Sudarshan Relationship Set advisor