3 Data Modeling Using the Entity Relationship Model

3. Data Modeling Using the Entity -Relationship Model Chapter 7 Course Coordinator: Dr. Deema Alathel Lecturer: Ms. Arwa Alturki

Introduction n Entity-Relationship (ER) Model ¡ ¡ Popular high-level conceptual data model Describes data as n n n entities, relationships, and attributes External View … mapping External View mapping Conceptual Schema mapping Internal Schema

3. 1 Using High-Level Conceptual Data Models for Database Design Miniworld Requirements Collection and Analysis Functional Requirements Database Requirements Functional Analysis DBMS Independent Conceptual Design Conceptual Schema High-Level Transaction Specification (In a high-level data model) Logical Design (Data Model Mapping) DBMS Dependent Logical (Conceptual) Schema Application Program Design Physical Design Transaction Implementation Internal Schema Application Programs

3. 2 Entities and Attributes n Entities ¡ ¡ Basic objects in ER model A “thing” in the real world with independent existence 1. 2. ¡ Physical existence: person, car, house, employee, … Conceptual existence: company, job, course, … Each entity must have a set of attributes

3. 2 Entities and Attributes n Attributes ¡ Properties used to describe entities n ¡ A particular entity will have a value for each of its attributes n ¡ Entity Name: EMPLOYEE Attributes: name, age, address, salary, job name: “John Smith” age: 55 address: 2311 Kirby, Houston, TX 77001 salary: $2000 job: Accountant The values are the actual data stored in the database

3. 2 Entities and Attributes n Attributes Entity Attributes ¡ Relationship Types: 1. 2. 3. Simple vs. composite Single-valued vs. multi-valued Stored vs. derived

3. 2 Entities and Attributes 1. Simple (Atomic) Vs. Composite Attributes ¡ Composite attributes can be divided into smaller subparts n n ¡ Simple attributes cannot be divided n ¡ Ex. City, Zip. Code, First. Name, Last. Name, … If the composite attribute is always referred to as a whole unit, then there’s no need to decompose it n ¡ Subparts represent basic attributes with independent meanings Ex. Address, Full. Name, … The whole attribute is considered a simple attribute If the user may refer to specific components of the composite attribute, then it must be subdivided

3. 2 Entities and Attributes 2. Single-valued Vs. Multi-valued Attributes ¡ Single-valued attributes have a single value for a particular entity n ¡ Ex. Age, Hair. Color, Name, … Multi-valued attributes may have a number of values for a particular entity n Ex. Car. Color, College. Degrees, …

3. 2 Entities and Attributes 3. Stored Vs. Derived Attributes ¡ In some cases, one attribute (or more) is related to another n ¡ A derived attribute can be determined from a stored attribute n ¡ Ex. Age and Birth. Date Ex. Birth. Date is a stored attribute, while Age is a derived attribute In some cases, an attribute can be derived from related entities n Ex. Number. Of. Employees in Department entity type is derived from counting the number of employees in the Employees entity type

3. 2 Entities and Attributes 4. Null Values ¡ Used in cases where attributes may not have values for a specific entity 1. Because the value is not applicable n 2. Ex. Apartment. No, College. Degrees, … Or because the value is unknown n n Ex. Phone. Number (may not exist) Ex. Height of a person (exists)

3. 2 Entities and Attributes 5. Complex Attributes ¡ ¡ A combination of composite and multivalued attributes ( ) are used to represent components of a composite attribute { } are used to represent multivalued attributes Ex. A person has more than one residence, and each residence can have multiple phone numbers n Attribute Phone. Address is used to represent a residence as follows: { Phone. Address ( { Phone (Area. Code, Phone. No) } , Address (Street. Add (No, St, Apt. No), City, State, Zip ) ) }

3. 3 Entity Types, Entity Sets, Keys, and Value Sets n Entity Types and Entity Sets ¡ A DB contains several different entities n Each group of entities share the same attributes but each has its own different values EMPLOYEE COMPANY Name, Age, Salary Name, Headquarters, President • e 1 (John Smith, 55, 80 k) • e 2 Entities • c 1 (Sunco Oil, Houston, John Smith) • c 2 (Fred Brown, 40, 30 k) (Fast Computer, Dallas, Bob King) • e 3 • . . (Judy Clark, 25, 20 k) • . .

3. 3 Entity Types, Entity Sets, Keys, and Value Sets n Entity Type (Intention) ¡ ¡ Defines a set of entities that have the same attributes Described by its name and attributes n Describes the schema (intention) for a set of entities EMPLOYEE COMPANY Name, Age, Salary Name, Headquarters, President • e 1 (John Smith, 55, 80 k) • e 2 Entity Types (Intention) • c 1 (Sunco Oil, Houston, John Smith) • c 2 (Fred Brown, 40, 30 k) (Fast Computer, Dallas, Bob King) • e 3 • . . (Judy Clark, 25, 20 k) • . .

3. 3 Entity Types, Entity Sets, Keys, and Value Sets n Entity Set (Extension) ¡ ¡ The collection of all entities of a particular entity type at any point in time Referred to using the same name as the entity type EMPLOYEE COMPANY Name, Age, Salary Name, Headquarters, President • e 1 (John Smith, 55, 80 k) • e 2 Entity Sets (Extension) • c 1 (Sunco Oil, Houston, John Smith) • c 2 (Fred Brown, 40, 30 k) (Fast Computer, Dallas, Bob King) • e 3 • . . (Judy Clark, 25, 20 k) • . .

3. 3 Entity Types, Entity Sets, Keys, and Value Sets n Key Attributes of an Entity Type ¡ An entity type usually has an attribute whose values are distinct (unique) for each entity in the entity set Key Attribute n ¡ The value of the key attribute is used to identify each entity uniquely Sometimes, the combination of several attributes forms a key Composite Key n n The combination of the key attributes’ values must be distinct for each entity A composite key must be minimal

3. 3 Entity Types, Entity Sets, Keys, and Value Sets n Key Attributes of an Entity Type ¡ ¡ Two entities in the same set cannot have the same value(s) for the key attribute(s) at the same time Some entities may have more than one key attribute n Each is a key by itself CAR Registration(Registration. Number, State), Vehicle. ID, Make, Model, Year, {Color} • car 1 ((ABC 123, TEXAS), TK 629, Ford Mustang, convertible, 1999, {red, black}) • car 2 ((ABC 123, NEW YORK), WP 9872, Nissan 300 ZX, 2 -door, 2002, {blue}) • car 3 ((VSY 720, TEXAS), TD 729, Buick Le. Sabre, 4 -door, 2003, {white, blue}) …

3. 3 Entity Types, Entity Sets, Keys, and Value Sets n Value Sets (Domains) of Attributes ¡ Each simple attribute of an entity type has a domain of legal values ¡ Ex. The Age attribute of EMPLOYEE is allowed to have values between 16 and 70 only n Value set for Age attribute of EMPLOYEE is the set of integer numbers between 60 and 70

3. 4 Relationship Types, Sets and Instances n Relationship ¡ ¡ ¡ n relates two or more distinct entities with a specific meaning EMPLOYEE John Smith works on the Product. X PROJECT EMPLOYEE Franklin Wong manages the Research DEPARTMENT Relationship Type ¡ ¡ ¡ Relationships of the same type that are grouped together The WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate The MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate

3. 4 Relationship Types, Sets and Instances n Relationship Instance ¡ ¡ An association of entities The association includes exactly one entity from each participating entity type

3. 5 Relationship Degree, Role Names, and Recursive Relationships n Degree of a Relationship Type ¡ The number of participating entity types n n n ¡ Binary (two participating entity types) (of degree 2) Ternary (three participating entity types) (of degree 3) … Ex. MANAGES and WORKS_ON are binary relationships

3. 5 Relationship Degree, Role Names, and Recursive Relationships n Relationships as Attributes ¡ A relationship type can be thought of as an attribute in one of the participating entity types ¡ The value set of this attribute would be the set of all entities from the other entity type associated with the entities in the current entity type ¡ Ex. WORKS_FOR relationship type between EMPLOYEE and DEPARTMENT entity types n Either add Department as attribute in EMPLOYEE ¡ n Single-valued attribute Or add Employee as attribute in DEPARTMENT ¡ Multi-valued attribute

3. 5 Relationship Degree, Role Names, and Recursive Relationships n Role Names and Recursive Relationships ¡ Each participating entity type in a relationship type plays a certain role in the relationship ¡ Assist in understanding the relationship n Not significant when participating entity types are distinct ¡ Necessary when the same entity type participates more than once in a relationship type in different roles ¡ Ex. EMPLOYEE (Supervisor) SUPERVISES EMPLOYEE (Worker)

3. 6 Constraints on Relationship Types n Cardinality Ratio for Binary Relationships ¡ ¡ The number of entities to which another entity can be associated via a relationship set In a binary relationship types, the possible cardinality ratios: n 1: 1 (one-to-one) ¡ An entity in one set is associated with at most one entity in another ¡ Ex. EMPLOYEE MANAGES DEPARTMENT n EMPLOYEE: DEPARTMENT has cardinality 1: 1 EMPLOYEE MANAGES DEPARTMENT e 1 d 1 e 2 d 2 e 3 d 3 … d 4 …

3. 6 Constraints on Relationship Types n 1: N (one-to-many) ¡ An entity in the first set is associated with 0 or more entities in the second set, but an entity in the second set can be associated with at most one entity in the first ¡ Ex. DEPARTMENT HAS_WORKERS EMPLOYEE n DEPARTMENT: EMPLOYEE has cardinality 1: N EMPLOYEE WORKS_FOR n DEPARTMENT e 1 d 1 e 2 d 2 e 3 d 3 e 4 d 4 … … N: 1 (many-to-one) ¡ The reverse of the 1: N cardinality ratio n Just a matter of which set is the first and which is the second

3. 6 Constraints on Relationship Types n N: M (many-to-many) ¡ entities of either set may be associated with any number of entities in the other ¡ Ex. EMPLOYEE WORKS_ON PROJECT n EMPLOYEE: PROJECT has cardinality N: M EMPLOYEE WORKS_ON PROJECT e 1 p 1 e 2 p 2 e 3 p 3 e 4 p 4 e 5 … …

3. 6 Constraints on Relationship Types n Participation Constraints ¡ Indicate if an entity in one set must be related to an entity in another 1. 2. ¡ ¡ If so, there is total participation in the relationship If not, there is partial participation in the relationship Ex. Participation of EMPLOYEE in WORKS_FOR with DEPARTMENT is total participation Ex. Participation of EMPLOYEE in MANAGES with DEPARTMENT is partial participation

3. 6 Constraints on Relationship Types n Attributes of Relationship Types ¡ A relationship type can have attributes ¡ Ex. Hours. Per. Week of WORKS_ON between EMPLOYEE and PROJECT n The attribute’s value for each relationship instance describes the number of hours per week that an EMPLOYEE works on a PROJECT

3. 7 Weak Entity Types n A weak entity type is an entity type which has no key of its own ¡ n Regular entity types (with keys) may be referred to as strong entity types Weak entities are identified by being related to specific entities from another entity type ¡ ¡ The other entity type is referred to as the owner entity type The relationship between a weak entity type and its owner entity type is known as the identifying relationship

3. 7 Weak Entity Types n Weak entities belonging to the same owner are uniquely identified using a partial key n A weak entity type always has total participation with respect to its identifying relationship n Weak entities can be represented as complex attributes (composite, multivalued) in their owning entities

3. 7 Weak Entity Types n Ex. EMPLOYEE HAS_DEPENDENTS DEPENDENT ¡ ¡ Has a 1: N cardinality ratio DEPENDENT has total participation in HAS_DEPENDENTS A dependent cannot exist unless there is an identifying employee The attributes of DEPENDENT are: FName, BDate, Gender, Relationship n n ¡ Two dependents of two different employees may be identical They become distinct after associating them with their owners The partial key of DEPENDENT is FName

3. 8 ER Diagram Notations … Entity Composite Attribute Weak Entity Relationship Derived Attribute Identifying Relationship E 1 R E 2 Total Participation of E 2 in R, Partial Participation of E 1 in R E 2 Cardinality Ratio x: y for E 1, E 2 in R E Structural Constraint (min, max) on Participation of E in R Attribute Key Attribute E 1 x R y Partial Key Multivalued R (min, max)

3. 8 ER Diagram Notations

3. 9 Proper Naming of Schema Constructs n Names should convey the meaning attached to different constructs n Use singular nouns for entity types and attributes n Use verbs for relationship type names n Name writing style depends on construct: ¡ ¡ ¡ n Entity type and relationship type names UPPERCASE Attribute names Initial Letter Capitalized Role names lowercase letters Use binary relationship names to make the ER diagram readable from left to right, top to bottom

3. 10 Alternative Notations for ER Diagrams n Many alternative notations for displaying ER diagrams n In one alternative notation, constraints on relationships are represented as a pair of integer numbers (min, max) ¡ n Replaces the cardinality ratio and the single/double line notation The notation means that an entity e in E must participate in at least min and at most max relationship instances n n min = 0 implies partial participation min > 0 implies total participation

3. 10 Alternative Notations for ER Diagrams n Example n n The MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate A DEPARTMENT has exactly one manager and an EMPLOYEE can manage at most one department

3. 10 Alternative Notations for ER Diagrams n Example n n The WORKS_FOR relationship type in which EMPLOYEEs and DEPARTMENTs participate An EMPLOYEE can work for exactly one DEPARTMENT but a DEPARTMENT may have any number of EMPLOYEEs

3. 11 Relationship Types of Degree Higher than Two n A relationship with degree n > 2 is called an n-ary relationship ¡ n In general, an n-ary relationship is not equivalent to n binary relationships Constraints are harder to specify for n-ary relationships than for binary relationships.

3. 11 Relationship Types of Degree Higher than Two n A ternary relationship SUPPLY

3. 11 Relationship Types of Degree Higher than Two n Three binary relationships that are not equivalent to the ternary relationship

3. 11 Relationship Types of Degree Higher than Two n CAN_SUPPLY includes an instance (s, p) whenever a supplier s can supply part p (to any project) n USES includes an instance (j, p) whenever a project j uses part p (from any supplier) n SUPPLIES includes an instance (s, j) whenever a supplier s supplies (some part) to project j n The existence of the three instances does not necessarily imply that an instance (s, j, p) exists because the meaning is different!

3. 11 Relationship Types of Degree Higher than Two n n n If both meanings are desired, then use a ternary relationship type along with the needed binary relationship types Ex. Ternary relationship type OFFERS between INSTRUCTOR, COURSE, and SEMESTER In addition, we could add the following binary relationship types ¡ ¡ ¡ CAN_TEACH between INSTRUCTOR and COURSE TAUGHT_DURING between INSTRUCTOR and SEMSTER OFFERED_DURING between COURSE and

3. 11 Relationship Types of Degree Higher than Two n Constraints on Ternary Relationships ¡ To determine the cardinality ratio, consider each combination of two as one entity type ¡ To determine the (min, max) constraint, think of how many entities can the current entity participate in

3. 11 Relationship Types of Degree Higher than Two n The (min, max) constraints can be displayed on the edges but they do not fully describe the constraints n Displaying a 1, M, or N indicates additional constraints: ¡ ¡ n An M or N indicates no constraint A 1 indicates that an entity can participate in at most one relationship instance that has a particular combination of the other participating entities In general, both (min, max) and 1, M, or N are needed to describe fully the constraints

Any Questions?