Chapter 7 Data Modeling Using the Entity Relationship ER Model CS 6360 Database Design Chris Irwin Davis Ph D Email cid021000 utdallas edu Phone 972 883 3574 O ce ECSS 4 705 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 Types 2 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 Two 3 7 1 Using High Level Conceptual Data Models for Database Design 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 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 application 6 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 model 7 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 specified 8 7 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 employee 10 11 7 3 Entity Types Entity Sets Attributes and Keys Entity Types Entity Sets Attributes and Keys ER model describes data as Entities Relationships Attributes 13 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 values 14 Entities and Attributes 15 Entity Types Entity Sets Keys and Value Sets Entity type Collection or set of entities that have the same attributes 16 Types of Attributes Several types of attributes occur in the ER model Simple versus composite Single valued versus multivalued Stored versus derived 17 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 entity 19 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 entity 21 Initial Conceptual Design of the COMPANY Database 22 7 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 attributes 24 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 Ej 25 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 Relationship 27 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 di erent roles Must specify role name 28 Recursive Relationship SUPERVISION 29 Recursive Relationship SUPERVISION EMPLOYEE EMPLOYEE EMPLOYEE e5 r1 r4 e1 e4 SUPERVISION r2 r3 r5 r6 e2 e3 e6 e7 30 SUPERVISION 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 partial 31 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 attributes 32 7 5 Weak Entity Types Weak Entity Types Weak Entities do not have key attributes of their own Identified by being related to specific entities from another entity type This does not mean they don t have a key but the parent entity s key is part of it Weak Entities have an identifying relationship Relates a weak entity type to its owner Always has a total
View Full Document
Unlocking...