Download Entity-Relationship Diagrams and more Exams Design in PDF only on Docsity!
Entity-Relationship Diagrams
Fall 2017, Lecture 3
There is nothing worse than a
sharp image of a fuzzy concept.
Ansel Adams
1
Recall: Relational Database Management
Relational DataBase Management Systems were
invented to let you use one set of data in
multiple ways, including ways that are
unforeseen at the time the database is built
and the 1
st
applications are written.
(Curt Monash, analyst/blogger)
3
Software to be used in this Chapter…
- MySQL Workbench which can be downloaded
from http://www.mysql.com/products/workbench/
Phases of DB Design
§ Transforms data requirements to conceptual model
§ Conceptual model describes data entities, relationships,
constraints, etc. on high-level
- Does not contain any implementation details
- Independent of used software and hardware
- Logical Design
§ Maps the conceptual data model to the logical data model used by
the DBMS
- e.g. relational model, hierarchical model, …
- Technology independent conceptual model is adapted to the used DBMS
software
§ Creates internal structures needed to efficiently store/manage
data
- Table spaces, indexes, access paths, …
- Depends on used hardware and DBMS software
7
Phases of DBMS Design
- While modeling the data, three design phases have
to be completed
§ The result of a phase serves as input to the next phase
§ Often, automatic transition is possible with some
additional designer feedback
conceptual
design
logical
design physical
design
ER-
diagram
UML,…
tables,
columns,…
tablespaces,
Indexes,…
Example: DBA for Bank of America
- Requirements Specification
§ Determine the requirements of clients (Database to
store information about customers, accounts, loans,
branches, transactions, …)
§ Express client requirements in terms of E/R model.
§ Confirm with clients that requirements are correct.
§ Specify required data operations
§ Convert E/R model to relational, object-based, XML-
based,…
§ Specify file organizations, build indexes
9
ER Modeling
- Traditional approach to Conceptual Modeling
§ Entity-Relationship Models (ER-Models)
- Also known as Entity-Relationship Diagrams (ERD)
- Introduced in 1976 by Peter Chen
- Graphical representation
- Top-Down-Approach for modeling
§ Entities and Attributes
§ Relationships
§ Constraints
- Some derivates became popular
§ ER Crow’s Foot Notation (Bachman Notation)
§ ER Baker Notation
§ Later: Unified Modeling Language (UML)
E-R Diagram as Wallpaper
- Very common for them to be wall-sized
13
ER Model Basics
§ Real-world object, distinguishable from other
objects.
§ Described using a set of attributes.
§ Has its own identity and represents just one thing
- Entity Set : A collection of similar entities. E.g., all
employees.
§ All entities in an entity set have the same set of
attributes. (Until we consider hierarchies,
anyway!)
§ Each entity set has a key (underlined).
§ Each attribute has a domain.
Employees
ssn
name
lot
ER Model Basics
Employees
ssn
name
lot
15
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))
ssn name lot
123 - 22 - 3666 Attishoo 48
231 - 31 - 5368 Smiley 22
131 - 24 - 3650 Smethurst 35
E/R Diagrams
- In an entity-relationship diagram:
§ Entity set = rectangle.
§ Attribute = oval, with a line to the rectangle
representing its entity set.
19
Example:
- Entity set Beers has two attributes, name and
manf (manufacturer).
- Each Beers entity has values for these two
attributes, e.g. (Corona, Grupo Modelo)
Beers
name manf
Entity Relationship Diagram Symbols
An entity is represented by a rectangle which contains
the entity’s name.
An entity that cannot be uniquely identified by its
attributes alone. The existence of a weak entity is
dependent upon another entity called the owner entity.
In the Chen notation, each attribute is represented by an
oval containing atributte’s name
An attribute whose value is calculated (derived) from
other attributes.
An attribute that can have many values (there are many
distinct values entered for it in the same column of the
table).
21
Entity Relationship Diagram Symbols
A relationship where entity is existence-independent of
other entities, and PK of Child doesn’t contain PK
component of Parent Entity
A relationship where Child entity is existence-
dependent on parent, and PK of Child Entity contains
PK component of Parent Entity.
Default
ename
children
seniority
Multivalued
Derived
Employee Works_At
essn
Branch
ename
phone
children
since seniority
bname bcity
Works_For
manager
worker
E/R Data Model: Types of Attributes
Business Rules
• A business rule is “a statement that
defines or constrains some aspect of the
business. It is intended to assert business
structure or to control or influence the
behavior of the business...rules prevent,
cause, or suggest things to happen”
• Entity-relationship diagrams are used to
document rules and policies of an
organization
25
In fact, documenting rules and
policies of an organization that
govern data is exactly what
data modeling is all about.
Relationships
- Relationship : Association among two or more entities.
E.g., Attishoo works in Pharmacy department.
§ relationships can have their own attributes.
- Relationship Set : Collection of similar relationships.
§ An n- ary relationship set R relates n entity sets E
1
... E
n
; each
relationship in R involves entities e
1
Î E
1
, ..., e
n
Î E
n
lot
name
Employees
ssn
Works_In
since
dname
budget did
Departments
27
Relationships
CREATE TABLE Works_In(
ssn CHAR( 11 ),
did INTEGER,
since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn)
REFERENCES Employees,
FOREIGN KEY (did)
REFERENCES Departments)
ssn did since
123 - 22 - 3666 51 1/ 1 /
123 - 22 - 3666 56 3/ 3 /
231 - 31 - 5368 51 2/ 2 /
lot
name
Employees
ssn
Works_In
since
dname
budget did
Departments
ER Model Basics (Cont.)
- Same entity set can participate in different
relationship sets, or in different “roles” in the
same set.
subor-
dinate
super-
visor
Reports_To
since
Works_In
dname
budget did
Departments
lot
name
Employees
ssn
31
Key Constraints
An employee can
work in many
departments; a
dept can have
many employees.
1 - to- 1
1 - to Many
Many-to-
Many
since
Manages
dname
did budget
Departments
since
Works_In
lot
name
ssn
Employees
In contrast, each dept
has at most one
manager, according
to the key constraint
on Manages.
32
since
Manages
33
CREATE TABLE Manages(
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY
(did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments
)
Key Constraints
An employee can
work in many
departments; a
dept can have
many employees.
In contrast, each dept
has at most one
manager, according
to the key constraint
on Manages.
since
Manages
dname
did budget
Departments
since
Works_In
lot
name
ssn
Employees
Participation Constraints
- Does every employee work in a department?
- If so, this is a participation constraint
§ the participation of Employees in Works_In is said to be total
(vs. partial )
§ What if every department has an employee working in it?
- Basically means “one or more”
lot
name dname
did budget
since
name dname
did budget
since
Manages
since
Employees Departments
ssn
Works_In
Means: “one or more” Means: “exactly one”
Binary vs. Ternary Relationships
If each policy is owned by
just 1 employee:
Bad design
Beneficiary
age
pname
Dependents
policyid cost
Policies
Purchaser
name
Employees
ssn lot
Better design
the constraints in
the 2nd diagram!
Policies
policyid cost
age
pname
Dependents
Covers
name
Employees
ssn lot
Key constraint on
Policies would
mean policy can
only cover 1
dependent!
37
Binary vs. Ternary Relationships
(Contd.)
3
The key
constraints allow
us to combine
Purchaser with
Policies and
Beneficiary with
Dependents.
Participation
constraints lead to
NOT NULL
constraints.
What if Policies is
a weak entity set?
CREATE TABLE Policies (
policyid INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (policyid).
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE)
CREATE TABLE Dependents (
pname CHAR(20),
age INTEGER,
policyid INTEGER,
PRIMARY KEY (pname, policyid).
FOREIGN KEY (policyid) REFERENCES Policies,
ON DELETE CASCADE)
Binary vs. Ternary Relationships (Contd.)
- Previous example illustrated a case when two binary
relationships were better than one ternary.
- An example in the other direction: a ternary relation
Contracts relates entity sets Parts, Departments and
Suppliers, and has descriptive attribute quantity.
§ No combination of binary relationships is an
adequate substitute.
Suppliers
quantity
Departments
Contract
Parts
39
Binary vs. Ternary Relationships (Contd.)
§ S “can-supply” P, D “needs” P, and D “deals-with” S does not
imply that D has agreed to buy P from S.
§ How do we record qty?
Suppliers
quantity
Departments Contract
Parts
VS.
Suppliers
Departments
deals-with
Parts
can-supply
needs
