Conceptual Modeling and Entity-Relationship Diagrams Chapter 3 - - PowerPoint PPT Presentation

3753 X1

Conceptual Modeling and Entity-Relationship Diagrams

Chapter 3 & 4: Elmasri/Navathe

3753 X1 2

Outline

• Phases of Database Design
• Conceptual Modeling
• Abstractions in Conceptual Design
• Example Database Requirements
• Deconstructing the E-R Diagram

– Entities, Attributes and Relationships – Participation, Cardinality and Keys

3753 X1 3

Phases of Database Design

Requirements Analysis Conceptual Design Data Model Mapping Physical Design Application Domain Database Requirements Conceptual Schema Implementation Schema Physical Schema DBMS Independent DBMS Dependent

3753 X1

Conceptual Design

• Similar to the analysis phase in software

development

– produce a description of the data – capture the semantics of the data

• Description in a high-level model

– close to the user’s view of the world – abstract concepts – means of communication between the user and the developer

4

3753 X1

Reasons for Conceptual Modeling

• Independent of DBMS.
• Allows for easy communication between

end-users and developers.

• Has a clear method to convert from high-

level model to relational model.

• Conceptual schema is a permanent

description of the database requirements.

5

3753 X1 6

Abstractions in Conceptual Design

• An abstraction is a mental process where

we select some set of properties of an

• bject and exclude others.
• 3 types of abstractions

– classification – aggregation – generalization

3753 X1 7

Classification

• Define a class of real-world objects with

common properties

Month December February January

…

3753 X1 8

Aggregation

• Define a new class from a set of other

classes that represent component parts

Car Tires Steering Wheel Engine Gas pedal

3753 X1 9

Generalization

• Defines a subset relationship between

elements of 2 or more classes

Faculty Staff Employee Person Student

3753 X1 10

Entity-Relationship Model

• Most popular conceptual model for

database design

• Basis for many other models
• Describes the data in a system and how

that data is related

• Describes data as entities, attributes

and relationships

3753 X1 11

Database requirements

• We must convert the written database

requirements into an E-R diagram

• Need to determine the entities, attributes

and relationships.

– nouns = entities – adjectives = attributes – verbs = relationships

3753 X1 12

Acadia Teaching Database

Design an E-R schema for a database to store info about professors, courses and course sections indicating the following:

• The name and employee ID number of each professor
• The salary and email address(es) for each professor
• How long each professor has been at the university
• The course sections each professor teaches
• The name, number and topic for each course offered
• The section and room number for each course section
• Each course section must have only one professor
• Each course can have multiple sections

3753 X1

Name

Visual View of the Database

13

Professor Name First Last Course Employee ID Start Date Years Teaching Room Topic teaches

1 N

Section Section ID Part

• f

1 N

Number Email Salary

3753 X1 14

The Pieces

• Objects

– Entity (including weak entities) – Attribute – Relationship

• “Structural” Constraints

– Cardinality – Participation

3753 X1 15

Entities

• Entity – basic object of the E-R model

– Represents a “thing” with an independent existence – Can exist physically or conceptually

• a professor, a student, a course
• Entity type – used to define a set of

entities with the same properties.

3753 X1 16

Entity and Entity Types

Entity

Number: 3753 Name: Database Management Systems Topic: Introduction to DBMSs

Entity Type

Course Number

Topic

Name

3753 X1 17

Attributes

• Each entity has a set of associated properties

that describes the entity. These properties are known as attributes.

• Attributes can be:

– Simple or Composite – Single or Multi-valued – Stored or Derived – NULL

3753 X1 18

Attributes (cont’d)

Simple

Professor Start Date

Composite

Professor Name First Last

3753 X1 19

Attributes (cont’d)

Single

Professor Employee ID#

Multi-Valued

Professor Email

3753 X1 20

Attributes (cont’d)

Stored

Professor Start Date

Derived

Professor Years Teaching

3753 X1 21

Attributes (cont’d)

• NULL attributes have no value

– not 0 (zero) – not a blank string

• Attributes can be “nullable” where a null

value is allowed, or “not nullable” where they must have a value.

3753 X1 22

Primary Keys

• Employee ID is the primary key
• Primary keys must be unique for the

entity in question

Professor Employee ID

3753 X1 23

Relationships

• defines a set of associations between

various entities

• can have attributes to define them
• are limited by:

– Participation – Cardinality Ratio

3753 X1 24

Relationships (cont’d)

Section Course

part of

3753 X1 25

Participation

• Defines if the existence of an entity depends on

it being related to another entity with a relationship type.

– Partial – Total

Section Course

part of

3753 X1 26

Cardinality

• The number of relationships that an entity

may participate in.

– 1:1, 1:N, N:M, M:1

Section Course

part of

1 N

3753 X1 27

Weak entity

• Weak entities do not have key attributes of their
• wn.
• Weak entities cannot exist without another a

relationship to another entity.

• A partial key is the portion of the key that comes

from the weak entity. The rest of the key comes from the other entity in the relationship.

• Weak entities always have total participation as

they cannot exist without the identifying relationship.

3753 X1 28

Weak Entity (cont’d)

Section

Course

part of

Number

Section ID

Identifying Relationship

3753 X1

Review of the ER Diagram

29

Professor Name First Last Course Employee ID Start Date Years Teaching Name Room Topic teaches

1 N

Section Section ID Part

• f

1 N

Number Email Salary

3753 X1 30

University DB Case Study

• Maintain the following information about

undergrad students:

– Name, address, student number, date of birth, year of study, degree program (BA, BSc, BCS), concentration (Major, Honours, etc) and department of concentration.

• Note: An address is composed of a street, city,

province and postal code; the student number is unique for each student

3753 X1 31

University Case Study (cont’d)

• Maintain information about departments

– Name, code (CS, Phy), office phone, and faculty members

• Maintain information about courses:

– Course number (3753), title, description, prerequisites.

• Maintain information about course sections:

– Section (A, B, C), term (X1), slot #, instructor

3753 X1 32

University Case Study (cont’d)

• Maintain information about faculty:

– Name, rank, employee number, salary, office number, phone number and email address. – Note: employee number is unique

• Maintain a program of study for the current

year for each student:

– i.e. courses that each student is enrolled in

3753 X1 33

Extended E-R Model

• E-R model is sufficient for traditional

database applications

• Nontraditional applications (CAD,

multimedia) have more complex requirements

• Can extend traditional E-R diagrams with

semantic data modeling concepts

3753 X1 34

IS-A Relationship (cont’d)

Employee Staff Faculty Teaching Assistant S.I.N. Student # Rank d Position Name

3753 X1 35

Specialization & Generalization

• Specialization

– process of taking an entity and creating several specialized subclasses

• Generalization

– process of taking several related entities and creating a general superclass

• We will talk mainly of specialization, but

most information will also apply to generalization

3753 X1 36

Specialization constraints

• Specializations can be predicate-defined
• r attribute-defined (otherwise called user-

defined)

• Disjointness constraint – specialization is

disjoint or overlapping

• Completeness constraint – specialization

is total or partial

3753 X1 37

Predicate-defined subclass

• An attribute value is used to determine the

members of a subclass

• Not all members of every subclass can be

determined by the attribute value

• In the following example, the Pension Plan type

can be used to determine faculty from staff, but has no effect on students or those who opted out

• f the pension plan.

3753 X1 38

Predicate-defined subclass

Employee Staff Faculty S.I.N. Rank d Position Pension Plan Type Note: not all employees included

3753 X1 39

Attribute-defined subclass

• There is one defining attribute for all

subclasses

• Each member of the superclass can be

assigned to the appropriate subclass based on this one attribute

3753 X1 40

Attribute-defined subclass

Employee Staff Faculty S.I.N. Rank d Rank Jobtype Students Year Jobtype “Staff” “Faculty” “Student”

3753 X1 41

User-defined subclass

• When there is no condition to

automatically determine membership in a subclass, it must be done at the discretion

• f the user.

3753 X1 42

Disjointness constraint

• Specifies that an entity can be a member
• f at most one subclass
• There can be no overlap between the

subclasses

• We use the notation of a d in a circle to

symbolize that the subclasses are disjoint

3753 X1 43

Disjoint constraint

Employee Staff Faculty Teaching Assistant S.I.N. Student # Rank d Position Name

3753 X1 44

Overlap

• Entities are able to belong to more than
• ne subclass
• Notation is an o inside of a circle

3753 X1 45

Overlap

Employee Staff Faculty S.I.N. Rank

• Rank

Jobtype Students Year A staff member may also be a student

3753 X1 46

Completeness Constraint

• May be total or partial
• for total, every entity in the superclass

must belong to a subclass

• for partial, entities in the superclass do not

need to be part of any subclass

• notation for total and partial are the same

as in a regular E-R diagram – single and double lines

3753 X1 47

Partial

Employee Staff Faculty S.I.N. Rank

• Rank

Jobtype Students Year

3753 X1 48

Total

Employee Staff Faculty S.I.N. Rank

• Rank

Jobtype Students Year

3753 X1 49

Hierarchies and Lattices

• Hierarchies

– a tree-like structure where each subclass belongs to only one superclass – everything we have seen so far is a hierarchy

• Lattices

– a graph-like structure where a subclass can belong to more than one superclass

3753 X1 50

Lattice

Person Teaching Assistant Student Employee

• name

course student # salary