The Entity-Relationship Model课件.pptVIP

FALL 2003 CENG 351 The Entity-Relationship Model Overview of Database Design Conceptual design: (ER Model is used at this stage.) What are the entities and relationships in the enterprise? What information about these entities and relationships should we store in the database? What are the integrity constraints or business rules that hold? A database “schema” in the ER Model can be represented pictorially (ER diagrams). We can map an ER diagram into a relational schema. Schema Refinement: (Normalization) Check relational schema for redundancies and anomalies. Physical Database Design and Tuning: Consider typical workloads and further refine the db design. ER Model Basics Entity: Real-world object distinguishable from other objects. An entity is described (in DB) using a set of attributes. Entity Set: A collection of similar entities. E.g., all employees. All entities in an entity set have the same set of attributes. (Until we consider ISA hierarchies, anyway!) Each entity set has a key. Each attribute has a domain. Logical DB Design: ER to Relational Entity sets to tables: ER Model Basics (Contd.) Relationship: Association among two or more entities. e.g., Attishoo works in Pharmacy department. Relationship Set: Collection of similar relationships. An n-ary relationship set R relates n entity sets E1 ... En; each relationship in R involves entities e1 ? E1, ..., en ? En Same entity set could participate in different relationship sets, or in different “roles” in same set. Relationship Sets to Tables In translating a relationship set to a relation, attributes of the relation must include: Keys for each participating entity set (as foreign keys). This set of attributes forms a superkey for the relation. All descriptive attributes. Key Constraints Consider Works_In: An employee can work in many departments; a dept can have many employees. In contrast, each dept has at most one manager, according to the key constraint on Manages. Translating ER Diagr