《数据库系统概念 Database System Concepts》原书教学资源（第七版，PPT课件讲稿，英文版）Chapter 6 Database Design Using the E-R Model

Design Phases Initial phase--characterize fully the data needs of the prospective database users. Second phase--choosing a data model Applying the concepts of the chosen data model Translating these requirements into a conceptual schema of the database. A fully developed conceptual schema indicates the functional requirements of the enterprise. Describe the kinds of operations (or transactions)that will be performed on the data. Database System Concepts-7th Edition 6.4 @Silberschatz,Korth and Sudarshan

Database System Concepts - 7 6.4 ©Silberschatz, Korth and Sudarshan th Edition Design Phases ▪ Initial phase -- characterize fully the data needs of the prospective database users. ▪ Second phase -- choosing a data model • Applying the concepts of the chosen data model • Translating these requirements into a conceptual schema of the database. • A fully developed conceptual schema indicates the functional requirements of the enterprise. ▪ Describe the kinds of operations (or transactions) that will be performed on the data

Design Phases(Cont.) Final Phase--Moving from an abstract data model to the implementation of the database 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-7th Edition 6.5 ©Silberscha乜，Korth and Sudarshan

Database System Concepts - 7 6.5 ©Silberschatz, Korth and Sudarshan th Edition Design Phases (Cont.) ▪ Final Phase -- Moving from an abstract data model to the implementation of the database • 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

Design Alternatives In designing a database schema,we must ensure that we avoid two major pitfalls: Redundancy:a bad design may result in repeat information. Redundant representation of information may lead to data inconsistency among the various copies of information 。 Incompleteness:a bad design may make certain aspects of the enterprise difficult or impossible to model. Avoiding bad designs is not enough.There may be a large number of good designs from which we must choose. Database System Concepts-7th Edition 6.6 ©Silberscha乜，Korth and Sudarshan

Database System Concepts - 7 6.6 ©Silberschatz, Korth and Sudarshan th Edition Design Alternatives ▪ In designing a database schema, we must ensure that we avoid two major pitfalls: • Redundancy: a bad design may result in repeat information. ▪ Redundant representation of information may lead to data inconsistency among the various copies of information • Incompleteness: a bad design may make certain aspects of the enterprise difficult or impossible to model. ▪ Avoiding bad designs is not enough. There may be a large number of good designs from which we must choose

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 7) Formalize what designs are bad,and test for them Database System Concepts-7th Edition 6.7 ©Silberscha乜，Korth and Sudarshan

Database System Concepts - 7 6.7 ©Silberschatz, Korth and Sudarshan th Edition 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 7) • Formalize what designs are bad, and test for them

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,salary) course=(course_id,title,credits) A subset of the attributes form a primary key of the entity set;i.e., uniquely identifying each member of the set. Database System Concepts-7th Edition 6.10 ©Silberscha乜，Korth and Sudarshan

Database System Concepts - 7 6.10 ©Silberschatz, Korth and Sudarshan th Edition 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, salary ) course= (course_id, title, credits) ▪ A subset of the attributes form a primary key of the entity set; i.e., uniquely identifying each member of the set

Representing Entity sets in ER Diagram Entity sets can be represented graphically as follows: Rectangles represent entity sets. Attributes listed inside entity rectangle Underline indicates primary key attributes instructor student D D name name salary tot cred Database System Concepts-7th Edition 6.12 @Silberschatz,Korth and Sudarshan

Database System Concepts - 7 6.12 ©Silberschatz, Korth and Sudarshan th Edition Representing Entity sets in ER Diagram ▪ Entity sets can be represented graphically as follows: • Rectangles represent entity sets. • Attributes listed inside entity rectangle • Underline indicates primary key attributes

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∈Enl where (e1,e2,...,e)is a relationship ·Example: (44553,22222)∈advisor Database System Concepts-7th Edition 6.13 ©Silberscha乜，Korth and Sudarshan

Database System Concepts - 7 6.13 ©Silberschatz, Korth and Sudarshan th Edition 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 Sets (Cont.) Example:we define the relationship set advisorto denote the associations between students and the instructors who act as their advisors. ■ Pictorially,we draw a line between related entities. 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-7th Edition 6.14 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 7 6.14 ©Silberschatz, Korth and Sudarshan th Edition Relationship Sets (Cont.) ▪ Example: we define the relationship set advisor to denote the associations between students and the instructors who act as their advisors. ▪ Pictorially, we draw a line between related entities

Representing Relationship Sets via ER Diagrams Diamonds represent relationship sets. instructor student ID advisor D name name salary tot_cred Database System Concepts-7th Edition 6.15 @Silberschatz,Korth and Sudarshan

Database System Concepts - 7 6.15 ©Silberschatz, Korth and Sudarshan th Edition Representing Relationship Sets via ER Diagrams ▪ Diamonds represent relationship sets

Relationship Sets (Cont.) An attribute can also be associated with a relationship set. ■ For instance,the advisorrelationship set between entity sets instructor and student may have the attribute date which tracks when the student started being associated with the advisor 76766 Crick 98988 Tanaka 45565 Katz 3May2008 12345 Shankar 10June2007 10101 Srinivasan 00128 12June2006 Zhang 98345 Kim 6 June 2009 76543 Brown 76543 Singh 30June2007 76653Aoi 31May2007 22222 Einstein 23121 Chavez 4May2006 instructor 44553 Peltier student Database System Concepts-7th Edition 6.16 @Silberschatz,Korth and Sudarshan

Database System Concepts - 7 6.16 ©Silberschatz, Korth and Sudarshan th Edition Relationship Sets (Cont.) ▪ An attribute can also be associated with a relationship set. ▪ For instance, the advisor relationship set between entity sets instructor and student may have the attribute date which tracks when the student started being associated with the advisor instructor student 76766 Crick Katz Srinivasan Kim Singh Einstein 45565 10101 98345 76543 22222 98988 12345 00128 76543 44553 Tanaka Shankar Zhang Brown Aoi Chavez Peltier 3 May 2008 10 June 2007 12 June 2006 6 June 2009 30 June 2007 31 May 2007 4 May 2006 76653 23121