Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Chapter 7 Data Modeling Using the Entity-Relationship (ER) ModelCopyright © 2011 Ramez Elmasri and Shamkant Navathe Chapter 7 Outline Using High-Level Conceptual Data Models for Database Design A Sample Database Application Entity Types, Entity Sets, Attributes, and Keys Relationship Types, Relationship Sets, Roles, and Structural Constraints Weak Entity TypesCopyright © 2011 Ramez Elmasri and Shamkant Navathe Chapter 7 Outline (cont’d.) Refining the ER Design for the COMPANY Database ER Diagrams, Naming Conventions, and Design Issues Example of Other Notation: UML Class Diagrams Relationship Types of Degree Higher than TwoCopyright © 2011 Ramez Elmasri and Shamkant Navathe Data Modeling Using the Entity-Relationship (ER) Model Entity-Relationship (ER) model Popular high-level conceptual data model ER diagrams Diagrammatic notation associated with the ER model Unified Modeling Language (UML)Copyright © 2011 Ramez Elmasri and Shamkant Navathe Using High-Level Conceptual Data Models for Database Design Requirements collection and analysis Database designers interview prospective database users to understand and document data requirements Result: data requirements Functional requirements of the applicationCopyright © 2011 Ramez Elmasri and Shamkant Navathe Using High-Level Conceptual Data Models (cont’d.) Conceptual schema Conceptual design Description of data requirements Includes detailed descriptions of the entity types, relationships, and constraints Transformed from high-level data model into implementation data modelCopyright © 2011 Ramez Elmasri and Shamkant Navathe Using High-Level Conceptual Data Models (cont’d.) Logical design or data model mapping Result is a database schema in implementation data model of DBMS Physical design phase Internal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files specifiedCopyright © 2011 Ramez Elmasri and Shamkant Navathe A Sample Database Application COMPANY Employees, departments, and projects Company is organized into departments Department controls a number of projects Employee: store each employee’s name, Social Security number, address, salary, sex (gender), and birth date Keep track of the dependents of each employeeCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant Navathe Entity Types, Entity Sets, Attributes, and Keys ER model describes data as: Entities Relationships AttributesCopyright © 2011 Ramez Elmasri and Shamkant Navathe Entities and Attributes Entity Thing in real world with independent existence Attributes Particular properties that describe entity Types of attributes: • Composite versus simple (atomic) attributes • Single-valued versus multivalued attributes • Stored versus derived attributes • NULL values • Complex attributesCopyright © 2011 Ramez Elmasri and Shamkant Navathe Entities and Attributes (cont’d.)Copyright © 2011 Ramez Elmasri and Shamkant Navathe Entity Types, Entity Sets, Keys, and Value Sets Entity type Collection (or set) of entities that have the same attributesCopyright © 2011 Ramez Elmasri and Shamkant Navathe Entity Types, Entity Sets, Keys, and Value Sets (cont’d.) Key or uniqueness constraint Attributes whose values are distinct for each individual entity in entity set Key attribute • Uniqueness property must hold for every entity set of the entity type Value sets (or domain of values) Specifies set of values that may be assigned to that attribute for each individual entityCopyright © 2011 Ramez Elmasri and Shamkant Navathe Initial Conceptual Design of the COMPANY DatabaseCopyright © 2011 Ramez Elmasri and Shamkant Navathe Relationship Types, Relationship Sets, Roles, and Structural Constraints Relationship When an attribute of one entity type refers to another entity type Represent references as relationships not attributesCopyright © 2011 Ramez Elmasri and Shamkant Navathe Relationship Types, Sets, and Instances Relationship type R among n entity types E1, E2, ..., En Defines a set of associations among entities from these entity types Relationship instances ri Each ri associates n individual entities (e1, e2, ..., en) Each entity ej in ri is a member of entity set EjCopyright © 2011 Ramez Elmasri and Shamkant Navathe Relationship Degree Degree of a relationship type Number of participating entity types Binary, ternary Relationships as attributes Think of a binary relationship type in terms of attributesCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant Navathe Role Names and Recursive Relationships Role names and recursive relationships Role name signifies role that a participating entity plays in each relationship instance Recursive relationships Same entity type participates more than once in a relationship type in different roles Must specify role nameCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant Navathe Constraints on Binary Relationship Types Cardinality ratio for a binary relationship Specifies maximum number of relationship instances that entity can participate in Participation constraint Specifies whether existence of entity depends on its being related to another entity Types: total and partialCopyright © 2011 Ramez Elmasri and Shamkant Navathe Attributes of Relationship Types Attributes of 1:1 or 1:N relationship types can be migrated to one entity type For a 1:N relationship type Relationship attribute can be migrated only to entity type on N-side of relationship For M:N relationship types Some attributes may be determined by combination of participating entities Must be specified as relationship attributesCopyright © 2011 Ramez Elmasri and Shamkant Navathe Weak Entity Types Do not have key
View Full Document