!" #Chris Irwin Davis, Ph.D.Email: [email protected]: (972) 883-3574Office: ECSS 4.705Chapter 7: Data Modeling Using the Entity-Relationship (ER) ModelCS-6360 Database Design!" #Chapter 7 Outline•7.1 –!Using High-Level Conceptual Data Models for Database Design•7.2 –!A Sample Database Application•7.3 –!Entity Types, Entity Sets, Attributes, and Keys•7.4 –!Relationship Types, Relationship Sets, Roles, and Structural Constraints•7.5 –!Weak Entity Types2!" #Chapter 7 Outline•7.6 –!Refining the ER Design for the COMPANY Database •7.7 – ER Diagrams, Naming Conventions, and Design Issues•7.8 –!Example of Other Notation: UML Class Diagrams•7.9 –!Relationship Types of Degree Higher than Two37.1 –!Using High-Level Conceptual Data Models for Database Design!" #Data Modeling Using theEntity-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)5!" #Using High-Level Conceptual Models•Requirements collection and analysis°Database designers interview prospective database users to understand and document data requirements. Result: °Data requirements°Functional requirements of the application6!" #Using High-Level Conceptual Models•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 model7!" #Logical and Physical Design•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 specified87.2 –!A Sample Database Application!" #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 employee10!" #117.3 –!Entity Types, Entity Sets, Attributes, and Keys!" #Entity Types, Entity Sets, Attributes, and Keys•ER model describes data as: °Entities°Relationships°Attributes13!" #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•Complex attributes •Stored versus derived attributes•NULL values14!" #Entities and Attributes15!" #Entity Types, Entity Sets, Keys, and Value Sets•Entity type °Collection (or set) of entities that have the same attributes16!" #Types of Attributes• Several types of attributes occur in the ER model °Simple versus composite°Single-valued versus multivalued°Stored versus derived17!" #Composite versus Simple (Atomic) Attributes•Composite attributes can be divided into smaller subparts, which represent more basic attributes with indepen- dent meanings•Attributes that are not divisible are called simple or atomic attributes.18!" #Single-Valued versus Multivalued Attributes• Most attributes have a single value for a particular entity; such attributes are called single-valued°For example, Age is a single-valued attribute of a person• An attribute can have a set of values for the same entity°A multivalued attribute may have lower and upper bounds to constrain the number of values allowed for each individual entity19!" #Stored versus Derived Attributes• Two (or more) attribute values are related°e.g. AGE and BIRTH_DATE°BIRTH_DATE may be a stored attribute, and°AGE can be derived from BIRTH_DATE•Can AGE be a multi-valued attribute?•Can AGE be a composite attribute?20!" #Entity Types, Entity Sets, Keys, and Value Sets•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 entity21!" #Initial Conceptual Design of the COMPANY Database227.4 –!Relationship Types, Relationship Sets, Roles, and Structural Constraints!" #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 attributes24!" #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 Ej25!" #Relationship Degree•Degree of a relationship type °Number of participating entity types°Binary, ternary, n-ary•Relationships as attributes°Possible to think of a binary relationship type in terms of attributes (useful in certain scenarios, e.g. works_on)26!" #“Supply” Relationship27!" #Role Names and Recursive Relationships•Role names°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 name28!" #Recursive Relationship SUPERVISION29!" #Recursive Relationship SUPERVISION30e5r1r4e1e4r2r3r5r6e2e3e6e7EMPLOYEEEMPLOYEEEMPLOYEESUPERVISIONSUPERVISION!" #Constraints on Binary Relationship Types•Cardinality ratio for a binary relationship °Specifies maximum number of relationship instances in which that entity can participate•Participation constraint °Specifies whether existence of entity depends on its being related to another entity°Types: total and partial31!" #Attributes of Relationship Types•Relationships (like entities) can also have attributes•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
View Full Document
Unlocking...