data modeling using the entity-relationship (er) model

Kingdom of Saudi ArabiaMinistry of Higher Education

Al-Imam Muhammad Ibn Saud Islamic UniversityCollege of Computer and Information Sciences

Data Modeling Using the Entity-Relationship (ER) Model

IS 320: Introduction to Database

Hatoon AlSagri

IS Department

IS Department

Database Design

Steps in building a database for an application:

1. Understand real-world domain being captured

2. Specify it using a database conceptual model (ER)

3. Translate specification to model of DBMS (relational)

4. Create schema using DBMS commands (DDL)

5. Load data (DML)

2

Real-world domain

Conceptualmodel

DBMS data model

Create Schema

(DDL)

Load data(DML)

IS Department

Entity Sets

• A database can be modeled as:• a collection of entities,• relationship among entities.

• An entity is an object that exists and is distinguishable from other objects.• Example: specific person, company, event, plant

• Entities have attributes• Example: people have names and addresses

• An entity set is a set of entities of the same type that share the same properties.• Example: set of all persons, companies, trees, holidays

3

IS Department

Example COMPANY Database

• Simple example database application, called COMPANY, that

serves to illustrate the basic ER model concepts and their use in

schema design.

• The COMPANY database keeps track of a company’s

employees, department, and project.

• After the requirements collection and analysis phase, the

database designers provided the following description of the

“miniworld”-part of the company

4

IS Department

COMPANY database- Requirements

The company is organized into departments. each department has a name,

number and an employee who manages the department. We keep track of the

start date of the department manager. A department may have several

location.

Each department controls a number of projects. each project has a name,

number and is located at a single location.

We store each employee’s name, social security number, address, salary,

sex, and birth date. Each employee works for one department but may work

on several projects. We keep track of the number of hours per week that an

employee currently works on each project. We also keep track of the direct

supervisor of each employee.

Each employee may have a number of dependents. For each dependent,

We keep track of their name, sex, birth date, and relationship to employee.5

IS Department 6

The ER Data model for the COMPANY database.

IS Department

Entity sets: attributes

• An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set.

• Domain – the set of permitted values for each attribute • Attribute types:

• Simple and composite attributes.• Single-valued and multi-valued attributes

• E.g. multivalve attribute: phone-numbers• Derived attributes

• Can be computed from other attributes• E.g. age, given date of birth

7

Example:

customer = (customer-id, customer-name, customer-street, customer-city)loan = (loan-number, amount)

IS Department

Employee

Ssn Bdatename sexaddress

salary

ER Model - Entity and Attributes

8

IS Department

Types of Attributes (1)

• Composite versus Simple (Atomic) Attributes

• Simple attribute: Each entity has a single atomic value for the attribute. For example, SSN or Sex.

• Composite attribute: The attribute may be composed of several components.

For example, Address (House#, Street, City, State, ZipCode) Name (Fname, Mname, Lname).

• Composition may form a hierarchy where some components are

themselves composite.

9

IS Department

Example of a composite attribute

10

IS Department

Employee

Ssn BdateName

Fname Mname Lname

SexAddress

Salary

Composite versus Simple (Atomic) Attributes

11

IS Department

Types of Attributes (2)

• Single-Value versus Multivalued Attributes

• Most attributes have a single value for a particular entity; such attributes are called Single-valued

For example, Age of a PERSON

• An entity may have multiple values for that attribute. such attributes are called Multivalued

For example, Color of a CAR or Tel_num of a STUDENT.

Denoted as {Color} or {Tel_num}.

• A multivalued attribute may have lower and upper bounds to constrain the number of values allowed for each individual entity.

12

IS Department

STUDENT

St_no Tel_no

FnameMname

Single-Value versus Multivalued Attributes

Name DOB

Lname

13

IS Department

Types of Attributes (3)

• Stored versus Derived Attributes

• Derived attribute is an attribute that represents a value that is derived

from the value of a related attribute, not necessarily in the same entity

type.

For example, Age and BirthDate of a PERSON

The value of Age can be determined from the current date and the value

of that person’s BirthDate. The Age attribute called a derived attribute

is said to be derivable from the BirthDate attribute which is called a

stored attribute.

Other example, Total_cost is derived from quantity*unit_price

14

IS Department

Age

Stored versus Derived Attributes

STUDENT

St_no Tel_no

FnameMname

Name DOB

Lname

15

IS Department

Types of Attributes (4)

• Null value is a special value, In some cases a particular entity may not have an applicable value for an attribute.

For example, The ApartmentNumber of an Address

Null value cases: Not applicable for an attribute; or exist but missing; or not known

• In general, composite and multi-valued attributes may be nested arbitrarily to any number of levels although this is rare. Such attributes are called Complex Attributes

For example, PreviousDegrees of a STUDENT is a composite multi-valued attribute denoted by

{PreviousDegrees (College, Year, Degree, Field)}.

16

IS Department

STUDENT

name DOB

Tel_no

LNameinitial FName

Area_cdNo

EX

Complex Attributes

St_no

17

IS Department

Key Attributes

• An attribute of an entity type for which each entity must have a unique value is called a key attribute of the entity type.

For example,

SSN of EMPLOYEE.

• Candidate key (CK) is the minimal set of attributes that uniquely identifies an

entity. It cannot contain null.

For example, student_no, social_security_no, branch_no…

• Primary Key (PK) is a candidate key that is selected to uniquely identify each

entity.

• Alternate Key (AK) is a candidate key that is NOT selected to be the primary key.18

IS Department

• Candidate key (CK) is the minimal set of attributes that uniquely

identifies an entity. It cannot contain null.

For example, student_no, social_security_no, branch_no…

• Primary Key (PK) is a candidate key that is selected to uniquely

identify each entity.

• Alternate Key (AK) is a candidate key that is NOT selected to be the

primary key.

19

Keys

IS Department

Keys Example

EMPLOYEE (Id, SSN, Full_name, DOB, Dept_no)

20

Primary Key Alternate Keys

Candidate Key

IS Department

Keys

A key can be:• Simple key is a candidate key of one attribute For example, student_no, branch_no…

• Composite key is a candidate key that consists of two or more attributes For example, STUDENT (Fname, Mname, Lname) CLASS (crs_code, section_no) ADVERT (property_no, newspaperName, dateAdvert)

21

IS Department

Choice of PK

Choice of Primary Key (PK) is based on:

• Attribute length

• Number of attributes required

• Certainty of uniqueness

Each Primary key is underlined

22

IS Department 23

Primary Key in ERD

AgeSTUDENT

St_ID

DOB

LNameinitial FName

Area_cdNo

EX

CLASS

Crs_code

Section_no Name

Hours

Composite KeySimple Key

Name

Tel_no

IS Department

COMPANY database

The company is organized into DEPARTMENTs. Each department has a name, number and an employee who manages the department. We keep track of the start date of the department manager. A department may have several location. Each department controls a number of PROJECTs. Each project has a name, number and is located at a single location. We store each EMPLOYEE’s name, social security number, address, salary, sex, and birth date. Each employee works for one department but may work on several projects. We keep track of the number of hours per week that an employee currently works on each project. We also keep track of the direct supervisor of each employee. Each employee may have a number of DEPENDENTs. For each dependent, we keep track of their name, sex, birth date, and relationship to employee.

24

IS Department

EMPLOYEE Entity

25

EMPLOYEESsn

Bdate

name

Fname Mname Lname

Sex

Address

Salary

IS Department 26

DEPARTMENTS Entity

• Both name and number are unique for a department.

• A department may be spread over many locations.

• The number of employees in a department is derivable from the Works-for relationship.

DEPARTMENT

Name Number

LocationsNumberOfEmployees

IS Department

PROJECT Entity

27

PROJECT

NumberName Location

* Number of hours per week that an employee currently works on each project ???

IS Department 28

DEPENDENT Entity

• Dependents are only

uniquely identifiable in the

context of an employee

• weak entity type

• partial key is name

• Note the standard pattern

for weak entities.

EMPLOYEE

DEPENDENT

dept. of

Ssn

NameSex

BdateRelationship

IS Department

Initial Design of Entity Types:

29

EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT

IS Department

Relationships and Relationship Types

• A relationship relates two or more distinct entities with a specific meaning.

For example, EMPLOYEE John Smith works on the ProductX PROJECT or

EMPLOYEE Franklin Wong manages the Research DEPARTMENT.

• Relationships of the same type are grouped or typed into a relationship

type.

For example, the WORKS_ON relationship type in which EMPLOYEEs and

PROJECTs participate, or the MANAGES relationship type in which

EMPLOYEEs and DEPARTMENTs participate.

• The degree of a relationship type is the number of participating entity types.

30

IS Department

Example relationship instances of the WORKS_FOR relationship between EMPLOYEE and DEPARTMENT

31

IS Department

Example relationship instances of the WORKS_ON relationship between EMPLOYEE and PROJECT

32

IS Department

Degree of Relationship Type

Degree of relationship refers to number of participating entity types in a relationship.• A relationship of degree two (2 entity types) are binary.

• A relationship of degree three (3 entity types) are ternary.

33

COURSESTUDYSTUDENT

COURSEREGISTERSTUDENT

STAFF

IS Department

Relationships and Relationship Types

• More than one relationship type can exist with the same participating entity types.

For example,

MANAGES and WORKS_FOR are distinct relationships between EMPLOYEE and DEPARTMENT, but with different meanings and different relationship instances.

34

EMPLOYEE

WORKS-FOR

DEPARTMENT

MANAGES

IS Department

Weak Entity Types

• An entity that does not have a key attribute

• A weak entity must participate in an identifying relationship type with an owner or identifying entity type

• Entities are identified by the combination of:

• A partial key of the weak entity type

• The particular entity they are related to in the identifying entity type

35

IS Department

DEPENDENTdept. ofEMPLOYEE

Ssn

Bdatename

Fname Mname Lname

Sex Address

Salary

36

Weak Entity Types

Name Sex Bdate

Relationship

IS Department

Recursive Relationship Type

• We can also have a recursive relationship type.

• Recursive relationship is a relationship type where the same entity type

participates more than once in a different role. It is a unary relationship.

• Both participations are same entity type in different roles.

For example, SUPERVISION relationships between EMPLOYEE (in role

of supervisor or boss) and (another) EMPLOYEE (in role of subordinate or

worker).

• In ER diagram, need to display role names to distinguish participations.

37

IS Department 38

A Recursive Relationship Supervision

COURSE

REQUIRE

EMPLOYEE

SUPERVISION

SuperviseeSupervisor requester prerequisite

Role indicates the purpose that each participating entity type plays

in a relationship

IS Department 39

Roles

Role can be used when two entities are associated through more

than one relationship to classify the purpose of each relationship

IS Department

Attributes of Relationship types

• A relationship type can have attributes;

For example,

HoursPerWeek of WORKS_ON; its value for each relationship

instance describes the number of hours per week that an

EMPLOYEE works on a PROJECT.

40

WORKS-ON

Hours

IS Department

Constraints on Relationships

( Also known as ratio constraints )

Cardinality Ratio (specifies maximum participation) One-to-one (1:1)

One-to-many (1:N) or Many-to-one (N:1)

Many-to-many

Existence Dependency Constraint (specifies minimum

participation) (also called Participation Constraint) Zero (optional participation, not existence-dependent)

One or more (mandatory, existence-dependent)

41

IS Department 42

Many-to-one (N:1) RELATIONSHIP

IS Department 43

Many-to-many (M:N) RELATIONSHIP

IS Department

Structural Constraints one way to express semantics of relationships

Structural constraints on relationships:

Cardinality ratio (of a binary relationship): 1:1, 1:N, N:1, or M:N

Shown by placing appropriate number on the link.

Participation constraint (on each participating entity type): total (called existence dependency) or partial.

Shown by double lining the link

NOTE: These are easy to specify for Binary Relationship Types.

44

IS Department

EMPLOYEE DEPARTMENTMANAGES11

EMPLOYEE DEPARTMENTWORKS_FOR

EMPLOYEE PROJECTWORKS_ONMN

EMPLOYEE DEPENDENTDEPENDENTS_OFN1

DEPARTMENT PROJECTCONTROLSN1

EMPLOYEE EMPLOYEESUPERVISIONN1

1N

45

IS Department 46

The ER Data model for the COMPANY database

IS Department

Alternative (min, max) notation for relationship structural constraints:

• Specified on each participation of an entity type E in a relationship type R• Specifies that each entity e in E participates in at least min and at most max

relationship instances in R• Default(no constraint): min=0, max=n• Must have minmax, min0, max 1• Derived from the knowledge of mini-world constraintsExamples:• A department has exactly one manager and an employee can manage at most one

department. Specify (0,1) for participation of EMPLOYEE in MANAGES Specify (1,1) for participation of DEPARTMENT in MANAGES

• An employee can work for exactly one department but a department can have any number of employees.

Specify (1,1) for participation of EMPLOYEE in WORKS_FOR Specify (1,N) for participation of DEPARTMENT in WORKS_FOR

47

IS Department 48

The (min,max) notation relationship constraints

IS Department 49

COMPANY ER Schema Diagram using (min, max) notation

Kingdom of Saudi ArabiaMinistry of Higher Education

Al-Imam Muhammad Ibn Saud Islamic UniversityCollege of Computer and Information Sciences

The Enhanced Entity-Relationship (EER) Model

IS 320: Introduction to Database

IS Department

IS Department

Enhanced-ER (EER) Model Concepts

• Includes all modeling concepts of basic ER

• Additional concepts: subclasses/superclasses,

specialization/generalization, categories, attribute inheritance

• The resulting model is called the enhanced-ER or Extended ER

(E2R or EER) model

• It is used to model applications more completely and accurately if

needed

51

IS Department

Subclasses and Superclasses (1)

• An entity type may have additional meaningful sub-groupings of its entities.

Example: EMPLOYEE may be further grouped into SECRETARY, MANAGER, ENGINEER, TECHNICIAN, SALARIED_EMPLOYEE, HOURLY_EMPLOYEE,…

• Each of these groupings is a subset of EMPLOYEE entities

• Each is called a subclass of EMPLOYEE

• EMPLOYEE is the superclass for each of these subclasses

• These are called superclass/subclass relationships.

Example: EMPLOYEE/SECRETARY, EMPLOYEE/TECHNICIAN

52

IS Department

Subclasses and Superclasses

53

IS Department

Attribute Inheritance in Superclass / Subclass Relationships

• An entity that is member of a subclass inherits all attributes of the

entity as a member of the superclass

• It also inherits all relationships

• The subclass can has its own specific (local) attributers and

relationships together with all the attributes and relationships it

inherits from the superclass.

For example, TypingSpeed of SECRETARY

For example, BELONGS_TO relationship types of

HOURLY_EMPLOYEE

54

IS Department

Specialization

Two main reasons for including class/subclass relationship and

specializations in a data model:

• First: is that certain attributes may apply to some but not all entities

of the superclass.

• Second: is that some relationship types may be participated in only

by entities that are members of the subclass.

In summary, the specialization process allows us to do the following:

• Define a set of subclasses of an entity type.

• Establish additional specific attributes with each subclass.

• Establish additional specific relationship types between each

subclass and other entity types or other subclasses.

55

IS Department

Generalization

• The reverse of the specialization process

• Several classes with common features are generalized into a

superclass; original classes become its subclasses

Example: CAR, TRUCK generalized into VEHICLE; both CAR, TRUCK

become subclasses of the superclass VEHICLE.

• We can view {CAR, TRUCK} as a specialization of VEHICLE

• Alternatively, we can view VEHICLE as a generalization of CAR and

TRUCK

56

IS Department

Generalization (a) Two entity types, CAR and TRUCK. (b) Generalizing CAR and TRUCK into the superclass VEHICLE.

57

IS Department

Inheritance

58

STAFF

SALESPERSONNEL

UnsharedAttributes

Superclass

AddressDobName

SalesArea

CarAllowance

SharedAttributes

Subclass REQUIRE CAR

UnsharedRelationship

CONTRACT COMPANY

SharedRelationship

IS Department

• Disjointness Constraint: • Specifies that the subclasses of the specialization must be disjointed

(an entity can be a member of at most one of the subclasses of the specialization). Specified by d (inside the circle) in EER diagram

• If not disjointed, overlap; that is the same entity may be a member of more than one subclass of the specialization. Specified by o (inside the circle) in EER diagram

• Completeness Constraint: • Total specifies that every entity in the superclass must be a member

of some subclass in the specialization/ generalization. Shown in EER diagrams by a double line

• Partial allows an entity not to belong to any of the subclasses. Shown in EER diagrams by a single line

59

Constraints on Specialization and Generalization (1)

IS Department

Constraints on Specialization and Generalization (3)

• Hence, we have four types of specialization/generalization:

• Disjoint, total

• Disjoint, partial

• Overlapping, total

• Overlapping, partial

• Note: Generalization usually is total because the superclass is

derived from the subclasses.

60

IS Department

Example of disjoint partial Specialization

61

IS Department

Example of overlapping total Specialization

62

IS Department

Case Study: Requirements for the part of the UNIVERSITY database

• The database Keeps track of three types of people: employees, alumni, and students, A person can belong to one, two, or all three of these types. Each person has a name, SSN, sex, address, and birth date.

• Every employee has a salary, and there are three types of employees: faculty, staff, and student assistants. Each employee belongs to exactly one of these types. For each alumnus, a record of the degree or degrees that he or she earned at the university is kept. Including the name of the degree, the year granted, and the major department. Each student has a major department.

• Each faculty has a rank, whereas each staff member has a staff position. Student assistants are classified further as either research assistants of teaching assistants, and the percent of time that they work is recorded in the database. Research assistants have their research project stored, whereas teaching assistants have the current course they work on.

• Students are further classified as either graduate or undergraduate, with the specific attributes degree program (M.S.,PH.D) and class (freshman, sophomore, and so on), respectively.

63

IS Department

Specialization / Generalization Lattice Example (UNIVERSITY)

64

IS Department

Summary of ERD notations (1)

65

ENTITY

WEAK ENTITY

RELATIONSHIP

IDENTIFYING RELATIONSHIP

ATTRIBUTE

KEY ATTRIBUTE

MULTI-VALUED

COMPOSITE

DERIVED

IS Department

Summary of ERD notations (2)

66

CARDINALITYRATION

PARTICIPATIONCONSTRAINTS

1 M

(min,max)

E1 E2R TOTAL PARTICIPATIONOF E2 IN R

IS Department

Disjoint constraintd

o Non-Disjoint constraint (Overlap)

Total Participation

Optional Participation

Summary of EER notations (2)

67