Lecture 07 Erd PDF

CS3200 – Database Design Spring 2018 Derbinsky
Entity-Relationship (ER) Diagrams
Lecture 7
February 11, 2018 1

Outline
1. Context
– Design & Implementation Process
2. Goals of Conceptual Design
3. Entity-Relationship (ER) Model
4. One ER Diagrammatic Notation
5. Requirements Elicitation
6. Approaches to Conceptual Design
February 11, 2018 2

Database Design and Implementation Process
February 11, 2018 3

Goal of Conceptual Design

Description of data requirements that is…
Comprehensive
• Entity types, relationships, and constraints
• Sanity check of data & functional requirements
• Reference for [unit/integration] testing/analysis
Concise/High-level
• Easy to understand technically
• Easy to communicate with non-technical users
• Facilitates focus on data (vs. storage/implementation details)
Algorithmically Transformable
• Improves application development efficiency, reduces errors
February 11, 2018 4

Entity-Relationship (ER) Model

Entity
• Thing in the real world
Attribute
• Property of an entity
• Most of what we store in the database
Relationship
• Association between sets of entities
• Possibly with attribute(s)
February 11, 2018 5

ER Diagrams
• Graphical depiction
of an ER model
• Many notations, this
class…
All cars have a year, age, make, model,
registration (unique), vehicle number
(vin; unique), some number of colors…
Specialization/Generalization
February 11, 2018 6

Entity Sets
Set of entities that have Make Year Model
the same attributes
All cars have a year, CAR

make, and model.
February 11, 2018 7

Composite Attributes
Can be subdivided into Make Year Model
smaller subparts
All cars have a year, CAR

make, model, and
registration. Registration
State Number
February 11, 2018 8

Multivalued Attributes
Can take a [possibly Make Year Model
specified] number of
values.
Color CAR
All cars have a year,
make, model, Registration
registration, and some
number of colors. State Number
February 11, 2018 9

Key Attributes
The value uniquely Make Year Model
identifies each entity
All cars have a year, Color CAR VIN
make, model,
registration (unique), Registration
vehicle number (vin;
unique), some number State Number
of colors.
February 11, 2018 10

Potential Pitfall
• In relational schema, underlining multiple
attributes indicates that for all rows, the
combination is unique
• In ERDs, underlining multiple attributes

indicates that each individually can
uniquely identify an entity

Derived Attributes
Age
The value can be Make Year Model

computed
All cars have a year, Color CAR VIN
age, make, model,

registration (unique), Registration
vehicle number (vin;
unique), some number State Number
of colors.

Exercise
Draw an ERD for the following description:
Each department has a unique name, a

unique number, and a particular employee
who manages the department. We keep
track of the start date when that employee
began managing the department. A
department may have several locations.

Answer
Name Number
Location DEPARTMENT Manager
Manager_
Start_Date

Exercise
A department controls a number of projects,

each of which has a unique name, a unique
number, and a single location.

Answer
Name Number
Location PROJECT
Controlling_
Department

Exercise
We store each employee’s name (first, last, MI),

Social Security number (SSN), street address, salary,
sex (gender), and birth date. An employee is
assigned to one department, but may work on
several projects, which are not necessarily controlled
by the same department. We keep track of the
current number of hours per week that an employee
works on each project. We also keep track of the
direct supervisor of each employee (who is another
employee).

Answer
Salary
SSN Sex
Department Birthdate
EMPLOYEE
Supervisor Address
FName Name Works_On Project
MI LName Hours

Exercise
We want to keep track of the dependents of

each employee for insurance purposes. We
keep each dependent’s first name, sex, birth
date, and relationship to the employee.

Answer
Sex
Employee
DEPENDENT DBirthdate
Relationship
DName

Relationships
Associates one or more sets of entities
– One = recursive (role is important)
STUDENT DEPT
CHAIR_F
All departments have a faculty

member who serves as the chair. A
faculty member can only chair one FACULTY
department.

Relationships
MAJOR_D
STUDENT DEPT
CHAIR_F
All students must have a department

in which they major.
FACULTY

Relationships
MAJOR_D
STUDENT DEPT
MINOR_D
CHAIR_F
Students may have any number of

departments in which they minor.
FACULTY

Relationships
MAJOR_D
STUDENT DEPT
MINOR_D
Tutor Tutee
TUTORS CHAIR_F
Students can tutor other student(s).
FACULTY

Cardinality Ratios
Constrains the number of entities that can
participate in each role of the relationship
MAJOR_D
STUDENT DEPT
MINOR_D
1
Tutor Tutee
TUTORS CHAIR_F
All departments have a faculty 1

department.

Cardinality Ratios
N MAJOR_D 1
STUDENT DEPT
MINOR_D
1
Tutor Tutee
TUTORS CHAIR_F
All students must have a department 1

FACULTY

Cardinality Ratios
N MAJOR_D 1
STUDENT DEPT
N M
MINOR_D
1
Tutor Tutee
TUTORS CHAIR_F
Students may have any number of 1

departments in which they minor.
FACULTY

Cardinality Ratios
N MAJOR_D 1
STUDENT DEPT
N M
MINOR_D
1
Tutor Tutee
N M
TUTORS CHAIR_F
Students can tutor other student(s). 1
FACULTY

Structural Constraints
If an entity does not exist unless it appears with an entity in a
relationship, the participation is total (existence dependency).
Else, partial.
N MAJOR_D 1
STUDENT DEPT
N M
MINOR_D
1
Tutor Tutee
N M
TUTORS CHAIR_F
All departments have a faculty 1

department.

Structural Constraints
If an entity does not exist unless it appears with an entity in a
relationship, the participation is total (existence dependency).
Else, partial.
N MAJOR_D 1
STUDENT DEPT
N M
MINOR_D
1
Tutor Tutee
N M
TUTORS CHAIR_F
All students must have a department 1

FACULTY

Attributes of Relationships
1->1, can go to either entity
1->N, can go to (1) entity
N MAJOR_D 1
STUDENT DEPT
N M
MINOR_D
1
Tutor Tutee
N M
TUTORS Office CHAIR_F
1
Each department chair has an office.
FACULTY

Done
N MAJOR_D 1
STUDENT DEPT
N M
MINOR_D
1
Tutor Tutee
N M
TUTORS Office CHAIR_F
1
It is important to know whether or
not a student has completed his/her FACULTY
major.

Done
N MAJOR_D 1
STUDENT DEPT
N M
MINOR_D
1
Tutor Tutee
N M
TUTORS Done Office CHAIR_F
1
It is important to know whether or
not a student has completed each of FACULTY
his/her minor(s).

Done
N MAJOR_D 1
STUDENT DEPT
N M
MINOR_D
1
Tutor Tutee
N M
TUTORS Done Office CHAIR_F
Subject
1
It is important to know the subject(s)
in which a tutee is being tutored by FACULTY
each tutor.

Weak Entities
Entity types that do not have key attributes
of their own are weak; instead identified by
relation to specific entity of another type
(the identifying type)
Prof
1 COURSE N
COURSE SEC SECTION
NAME Number Book
DEPT NUM

Revise!
We store each employee’s
name (first, last, MI), Social
Security number (SSN), street
address, salary, sex (gender),
and birth date. An employee is Salary)
assigned to one department, SSN) Sex)
but may work on several

projects, which are not Department) Birthdate)
necessarily controlled by the EMPLOYEE)

same department. We keep Supervisor) Address)
track of the current number of

hours per week that an FName) Name) Works_On) Project)
employee works on each

project. We also keep track MI) LName) Hours)
of the direct supervisor of

each employee (who is
another employee).

Answer
Salary
SSN Sex Department
1
Supervisor Birthdate
Supervision
EMPLOYEE
Address
Supervisee
N
MI LName Hours

Revise!
Salary)
We want to keep track 1)

SSN) Sex) Department)
of the dependents of Supervisor)
Supervision)
Birthdate)
EMPLOYEE)
each employee for N)

Supervisee)
Address)
insurance purposes. We FName) Name) Works_On) Project)
keep each dependent’s MI) LName) Hours)
first name, sex, birth

date, and relationship to Sex(
the employee.
Employee(
DEPENDENT( DBirthdate(
Rela7onship(
DName(

Answer
Salary
SSN Sex Department
Supervisor
Birthdate
Supervision
EMPLOYEE
Address
Supervisee
1
N
Sex
MI LName Hours
DEPENDENT N
DEPENDENT DBirthdate
_OF
Relationship
DName

Revise!
A department controls Name' Number'
a number of projects, PROJECT'

each of which has a
Loca%on'
unique name, a unique Controlling_'

Department'
number, and a single

location. Name' Number'
Loca%on' DEPARTMENT' Manager'
Manager_'
Start_Date'

Answer
Name Number
Location PROJECT
Name Number
N
1
CONTROLS DEPARTMENT Manager
Manager_
Location
Start_Date

Revise!
Name' Number'
Each department has a …

particular employee who Loca%on' PROJECT'
manages the department. N' Name' Number'
1'
An employee is assigned
CONTROLS' DEPARTMENT' Manager'
to one department, but Manager_'
may work on several

Loca%on'
Start_Date'
projects, which are not Salary)
necessarily controlled by
SSN) Sex) Department)
1)
the same department. We

Supervisor)
Birthdate)
Supervision)
EMPLOYEE)
keep track of the current

Address)
Supervisee)
1)
N)
FName) Name) Works_On) Project)
number of hours per MI) LName) Hours)

Sex)
week that an employee DEPENDENT N)
works on each project.

DEPENDENT) DBirthdate)
_OF)
RelaIonship)
DName)

Answer
Supervisor Supervisee
1 N
Hours Supervision
M
WORKS_ON EMPLOYEE SSN
1 N
Name
Start
_Date
N
Location PROJECT Manages
Number
1
1 1
CONTROLS DEPARTMENT Works_For
Name Number
Location

All Together Now!

Specialization/Generalization
Only a subset of entities within a type have
certain attributes or participate in certain
relationships
ID
STUDENT
GRAD_STUDENT Degrees

Multiple Subtypes: Disjointedness

(o)verlap: may be more than one
(d)isjoint: entities may only be one subtype
SSN
A person can be an
employee, an
PERSON
alumnus, and/or a
student
o
U
U
EMPLOYEE ALUMNUS STUDENT

Multiple Subtypes: Disjointedness

(o)verlap: may be more than one
(d)isjoint: entities may only be one subtype
SSN
A person can be
either an employee,
PERSON
an alumnus, or a
student
d
U
U

Multiple Subtypes: Completeness

Similar to relationships; can be total (must
belong to subtypes) or partial (can belong)
SSN
A person must be
exactly one: an
PERSON
employee, an
alumnus, or a student
d
U
U

Exercise
• The database keeps track of three types of persons: 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 or 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., M.B.A., and so on) for graduate students and
class (freshman, sophomore, and so on) for under- graduates.

Answer

Alternative Notation (1)

Figure A. 1

Alternative Notation (2)

Figure A. 1

Requirements Elicitation
The conceptual model should inform requirements elicitation questions:
• What are the main kinds of objects to be stored in the database

(entity types)?
• For each object, what information should be stored (attributes,

relationships)? What information distinguishes one object of a type
from another (keys, weak entities)? Are there different
kinds/categories of objects (specialization /generalization )?
• For each piece of information, what characterizes a valid value

(composite/multi-valued , structural , etc.)?
• For related objects x and y, can x exist without y (participation )?

How many x’s can a y have, and vice-versa (cardinality )?

Approaches to Conceptual Design

Centralized
– Single authority responsible for merging
requirements into schema
– Reasonable for smaller applications
View Integration
– Each stakeholder implements local view
– Individual views integrated into global
schema
– Individual views can be reconstructed as
external schemas after integration

View Integration (1)

1. Identify correspondences and conflicts
– Conflicts: names, types, domain, constraints
2. Modify views to conform
3. Merge
4. Restructure

View Integration (2)

Summary
• The goal of conceptual design is to develop a set
of data requirements that are comprehensive,
clear & easy to understand, and algorithmically
transformable
• ER Diagrams (ERDs) are one such design model

that visually represent the entities, attributes,
and relationships of a system
• Requirements elicitation and conceptual design is

an iterative process that is a necessary
prerequisite to implementing a database

Lecture 07 Erd PDF

Uploaded by

Copyright:

Available Formats

Lecture 07 Erd PDF

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lecture 07 Erd PDF

Uploaded by

Copyright:

Available Formats

CS3200 – Database Design Spring 2018 Derbinsky

Entity-Relationship (ER) Diagrams

Entity-Relationship (ER) Diagrams

February 11, 2018 1

Entity-Relationship (ER) Diagrams

February 11, 2018 2

Database Design and Implementation Process

Entity-Relationship (ER) Diagrams

February 11, 2018 3

Goal of Conceptual Design

Entity-Relationship (ER) Diagrams

February 11, 2018 4

Entity-Relationship (ER) Model

Entity-Relationship (ER) Diagrams

February 11, 2018 5

Entity-Relationship (ER) Diagrams

February 11, 2018 6

All cars have a year, CAR

Entity-Relationship (ER) Diagrams

February 11, 2018 7

All cars have a year, CAR

Entity-Relationship (ER) Diagrams

February 11, 2018 8

Entity-Relationship (ER) Diagrams

February 11, 2018 9

All cars have a year, Color CAR VIN

Entity-Relationship (ER) Diagrams

February 11, 2018 10

• In ERDs, underlining multiple attributes

Entity-Relationship (ER) Diagrams

February 11, 2018 11

The value can be Make Year Model

All cars have a year, Color CAR VIN

age, make, model,

Entity-Relationship (ER) Diagrams

February 11, 2018 12

Each department has a unique name, a

Entity-Relationship (ER) Diagrams

February 11, 2018 13

Location DEPARTMENT Manager

Entity-Relationship (ER) Diagrams

February 11, 2018 14

A department controls a number of projects,

Entity-Relationship (ER) Diagrams

February 11, 2018 15

Entity-Relationship (ER) Diagrams

February 11, 2018 16

We store each employee’s name (first, last, MI),

February 11, 2018 17

FName Name Works_On Project

Entity-Relationship (ER) Diagrams

February 11, 2018 18

We want to keep track of the dependents of

Entity-Relationship (ER) Diagrams

February 11, 2018 19

Entity-Relationship (ER) Diagrams

February 11, 2018 20

All departments have a faculty

Entity-Relationship (ER) Diagrams