Chapter 3 Data Modeling Using the Entity Relationship ER Model Overview of Database Design Process Two main activities Database design Applications design Focus in this chapter on database design To design the conceptual schema for a database application Applications design focuses on the programs and interfaces that access the database Generally considered part of software engineering 3 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 6 3 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 8 3 3 Entity Types Entity Sets Attributes and Keys Entity Types Entity Sets Attributes and Keys ER model describes data as Entities Relationships Attributes 1 0 Entities and Attributes Entity Attributes Thing in real world with independent existence Particular properties that describe entity 1 1 Entity Types Entity Sets Keys and Value Sets Entity type Collection or set of entities that have the same attributes 1 2 Types of Attributes Several types of attributes occur in the ER model Simple versus composite Single valued versus multivalued Stored versus derived 1 3 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 1 4 Attribute Constraints Key or uniqueness constraint Attributes whose values are distinct for each individual entity in entity set 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 1 5 Initial Conceptual Design of the COMPANY Database 1 6 3 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 1 8 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 1 9 Relationship Degree Degree of a relationship type Number of participating entity types Binary ternary n ary Example Binary relationship EMPLOYEE DEPENDENTS OF DEPENDENT 1 N 2 0 Example SUPPLIES Ternary Relationship Type SUPPLIER SUPPLIES PROJECT SUPPLIER SUPPLIES PROJECT PART PART 2 1 Role Names and Recursive Relationships Role names Role name signifies the role that a participating entity plays in each relationship instance Usually not needed since Entity Type names Relationship Type names implies the semantic role 2 2 Recursive Relationship Recursive relationships Defined by the scenario where the same Entity Type participates more than once in a relationship type in different roles Must specify role name since it is not possible to imply role when both sides of the Relationship has the same Entity Type Relationship Type 2 3 Recursive Relationship SUPERVISES EMPLOYEE e5 1 r1 2 e1 1 1 r3 2 e3 r2 e2 2 1 r4 e4 1 2 r5 e6 2 2 4 1 r6 e7 2 SUPERVISES 1 supervisor 2 supervisee Constraints on Binary Relationship Types Cardinality ratio for a binary relationship Specifies maximum number of relationship instances in which that entity can participate One to one One to many Many to many Participation constraint Specifies whether each entity in an entity type must participate in a relationship set or not Types total and partial 2 5 3 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 participation constraint If the parent entity is deleted all related weak entities are deleted too 2 7 3 7 ER Diagrams Naming Conventions and Design Issues Proper Naming of Schema Constructs Choose names that convey meanings attached to different constructs in schema Nouns give rise to entity type names Verbs indicate names of relationship types Choose binary relationship names to make ER diagram readable from left to right and from top to bottom 2 9 min max Alternative Notation for ER Diagrams Specifies structural constraints on relationships Replaces cardinality ratio 1 1 1 N M N and single double line notation for participation constraint Cardinality Participation and min max are two mutually exclusive options for encoding this info Mixing or combining these in a homework or exam will be considered incorrect Associate a pair of integer numbers min max with each participation of an entity type E in a relationship type R where 0 min max and max 1 3 0 ER Diagram for Company Schema min max 3 1 Example of Other Notation UML Class Diagrams UML methodology Used extensively in software design Many types of diagrams for various software design purposes UML class diagrams Entity in ER corresponds to an object in UML 3 3 Example of Other Notation UML Class Diagrams Class includes three sections Top section gives the class name Middle section includes the attributes Last section includes operations that can be applied to individual objects 3 4 Example of Other Notation UML Class Diagrams Associations relationship types Binary association Represented as a line connecting participating classes May optionally have a name Link attribute Placed in a box connected to the association s line by a dashed line 3 5 Example of Other Notation UML Class Diagrams Multiplicities min max asterisk indicates no maximum limit on participation Types of relationships association and aggregation 3 6 3 9 Relationship Types of Degree Higher than Two Choosing between Binary and Ternary or Higher Degree Relationships Some database design tools permit only binary relationships Ternary