Transcript Slide 1

AS 504
Entity Relationship (ER)
Modeling
1
The Entity Relationship (ER) Model

ER model forms the basis of an ER
diagram

ERD represents the conceptual
database as viewed by end user

ERDs depict the ER model’s three main
components:

Entities

Attributes

Relationships
2
Entities

Refers to the entity set and not to a single
entity occurrence

Person, place, thing, or event

sales reps, customers, orders, and parts

Corresponds to a table and not to a row in the
relational environment

In both the Chen and Crow’s Foot models, an
entity is represented by a rectangle containing
the entity’s name

Entity name, a noun, is usually written in
capital letters
3
Attributes

Property of an entity

Customer has name, street, city, et cetera

May also be called a field or column

In Chen model, attributes are represented by
ovals and are connected to the entity
rectangle with a line
Each oval contains the name of the attribute it
represents
In the Crow’s Foot model, the attributes are
simply written in the attribute box below the
entity rectangle


4
Entities and Attributes
5
Relationships: The Basic Chen ERD
6
Relationships:
The Basic Crow’s Foot ERD
7
One-to-one Relationship
Each occurrence of data in one entity is
represented by only one occurrence of
data in the other entity
 Example: Each individual has just one
Social Security Number (SSN) and each
SSN is assigned to just one person

8
One-to-many Relationship
Each occurrence of data in one entity can
be represented by many occurrences of
the data in the other entity
 Example: A class has only one instructor,
but each instructor can teach many
classes

9
Many-to-many Relationship
Data can have multiple occurrences in
both entities
 Example: A student can take many classes
and each class is composed of many
students
 Can not be included in the physical
database

10
Example E-R Model
11
Keys

Consists of one or more attributes that
determine other attributes

Primary key (PK) is an attribute (or a
combination of attributes) that uniquely
identifies any given entity (row)

Key’s role is based on determination


If you know the value of attribute A, you
can look up (determine) the value of
attribute B
A  B : A determine B
12
Primary Key (PK)


Knowing the STU_NUM in the STUDENT table means that
you are able to look up (determine) that student’ last name,
grade point average, phone number, and so on.
Short notation: STU_NUM  STU_LNAME, STU_FNAME,
STU_INIT, …
Primary Key
13
Functional Dependence


The attribute B is functionally dependent on the
attribute A if each value in column A determines
one and only on value in column B.
STU_PHONE is functionally dependent on
STU_NUM.


STU_NUM is not functionally dependent on
STU_PHONE.


STU_NUM value 321452 determines the STU_PHONE
value 2134
STU_PHONE value 2267 is associated with two
STU_NUM values: 324273 and 324291.
If all attributes in table are functionally
dependent on A, A is the primary key.
14
Questions
Is STU_NUM functionally dependent on
STU_LNAME?
 IS PROF_NUM is functionally dependent on
STU_NUM?
 Is STU_CLASS is functionally dependent
on STU_HRS?

15
Keys (continued)

Composite key


Composed of more than one attribute to
define functional dependence
Key attribute

Any attribute that is part of a key

STU_LNAME, STU_FNAME, STU_PHONE 
STU_HRS, STU_CLASS, STU_GPA
16
Keys (continued)

Superkey

Any key that uniquely identifies each entity

Functionally determines all of the entity’s
attributes

STU_NUM

STU_NUM, STU_LNAME

STU_NUM, STU_LNAME, STU_INIT
17
Keys (continued)

Candidate key

A superkey without redundancies

STU_NUM, STU_LNAME is a super key, but it is
not a candidate key b/c STU_NUM by itself is a
candidate key

STU_LNAME, STU_FNAME, STU_INIT,
STU_PHONE might also be a candidate key.

The candidate keys that are not chosen as the
primary key are often referred to as alternate
keys (AK).
18
Null Values

No data entry

Not permitted in primary key

Should be avoided in other attributes

Can represent


An unknown attribute value

A known, but missing, attribute value

A “not applicable” condition
Can create problems in logic and using
formulas
19
Keys (continued)

Foreign key (FK)


An attribute whose values match primary
key values in the related table
Secondary key (SK)

Key used strictly for data retrieval purposes
20
Integrity Rules
21
An Illustration of Integrity Rules
22
Integrity Rules

Entity integrity



The CUSTOMER primary key (CUS_CODE) has no null
entries, and all entries are unique.
The AGENT primary key (AGENT_CODE) is free of null
entries.
Referential integrity



The CUSTOMER table contains a foreign key
(AGENT_CODE), links to AGENT table
AGENT_CODE in the CUSTOMER table may contain a null
entry (CUS_CODE = 10013)
The remaining AGENT_CODE entries in the CUSTOMER
table all match the AGENT_CODE entries in the AGENT
table.
23
Avoiding NULL
24
DBDL Notation

Table name followed by columns in parentheses

Primary key column(s) underlined

AK identifies alternate keys

SK identifies secondary keys

FK identifies foreign keys
25
Comparison of E-R Modeling Symbols
26
Entity-Relationship Diagrams – IDEF1X

Diagram that visually represents database structure


Rectangle represents each entity in the E-R diagram
Primary key for each entity appears above the line
in the rectangle for each entry
27
Entity-Relationship Diagrams

Other columns that comprise each entity appear
below the line within each rectangle

The letters AK, SK, and FK appear in parentheses
following the alternate key, secondary key, and
foreign key respectively

For each foreign key, there is a line leading from
the rectangle that corresponds to the table being
identified to the rectangle that corresponds to the
table containing the foreign key
28
Entity-Relationship Diagrams
29
User Requirements



For a sales rep, store the sales rep’s number,
name, address, total commission, and
commission rate.
For a customer, store the customer’s number,
name, address, balance, and credit limit. In
addition, store the number and name of the sales
rep who represents this customer. A sales rep can
represent many customers, but a customer must
have exactly one sales rep (a customer must
have a sales rep and can not have, more than
one sales rep).
For a part, store the part’s number, description,
units on hand, item class, the number of the
warehouse in which the part is located, and the
price. All units of a particular part are stored in
the same warehoues.
30
User Requirements (con’t)

For an order, store the order number, the
order date, the number, name, and
address of the customer that placed the
order; and the number of the sales rep
who represents that customer. In addition,
for each line item within the order, store
the part number, and description, the
number of the part that was ordered, and
the quoted price.
31
User Requirements (con’t)

Constraints for an order :




Each order must be placed by a customer.
There is only one customer per order.
On a given order, there is at most one line
item for a given part.
The quoted price might match the current
price, allowing the company to sell the same
parts to different customers at different prices.
The user wants to be able to change the price
for a part without affecting orders that are
currently on file.
32
View #1: Sales Rep View
View #2: Customer View
33
View #3: Part View
34
Final View
View #4: Order View
35
Entities

Independent entity - an entity that does not require
a relationship to another entity for identification


CUSTOMER table
Dependent entity or weak entity - an entity that
does require a relationship to another entity for
identification

ORDERLINE table

Identifying relationship is a relationship that is
necessary for identification (solid line).

Nonidentifying relationship is a relationship that is
not necessary (dashed line).
36
Exercise: Draw E-R Diagram
For each publisher, list the publishers
code, publisher name, and the city in
which the publisher is located.
 For each branch, list the number, name,
location, and the number of employees.
 For each book, list its code, title, publisher
code and name, price, and whether it is a
paperback.

37
Exercise: Draw E-R Diagram (addition)



For each book, list its code, title, price, and type.
In addition, list the book’s authors and their
names. If a book has more than one author, all
names must appear in the order in which they are
listed on the book’s cover. The author order is not
always alphabetical.
For each branch, list its number and name. In
addition, list the code and title of each book
currently in the branch as well as the number of
copies the branch has available.
For each book, list its code and title. In addition,
for each branch that currently has the book in
stock, list the number and name of the branch
along with the number of copies available.
38
Standard Entity-Relationship Model

An approach to representing data in a database

Entities are drawn as rectangles

Relationships are drawn as diamonds with lines
connecting the entities involved in relationships
39
One-to-many relationship
40
Many-to-Many Relationship
41
Many-to-Many Relationship
42
One-to-Many Relationship
43
Many-to-Many Relationship with
Attributes
44
Composite Entity

Composite entity - an entity that exists to
implement a many-to-many relationship

Essentially both an entity and a relationship

Represented in an E-R diagram by a diamond within
a rectangle
45
Composite Entity
46
Complete E-R Diagram
47
E-R Diagram with an Existence
Dependency and Weak Entity

Existence dependency - when the existence of one
entity depends on the existence of another related
entity

Indicate an existence dependency by placing an “E”
in the relationship diamond

Weak entity - an entity that depends on another
entity for its own existence

A double rectangle encloses a weak entity
48
Diagram with an Existence Dependency
and Weak Entity
49
Cardinality

Cardinality - the number of items that must be
included in a relationship

An entity in a relationship with minimum cardinality
of zero plays an optional role in the relationship

An entity with a minimum cardinality of one plays a
mandatory role in the relationship
50
E-R Diagram that Represents Cardinality
1 = sales rep must have 1 customer
(minimum)
N = sales rep can have many
customers (maximum)
1 = customer must have
1 sales rep
1 = customer can not have
more than 1 sales rep
Rep entity is mandatory to
customer and customer is
mandatory to Rep
51
E-R Diagram that Represents Cardinality
1 = sales rep must have 1 customer
(minimum)
N = sales rep can have many
customers (maximum)
0 = customer not require
sales rep
1 = customer can not have
more than 1 sales rep
Customer is optional to Rep, Rep is mandatory to Customer
52
Normalization
Determines required tables and columns
for each table
 Multi-step process
 Used to reduce or control data redundancy

53
Unnormalized Data
Contains repeating groups in the Author
column in the BOOKS table
54
First-Normal Form (1NF)
Primary key is identified
 Repeating groups are eliminated

55
First-Normal Form (1NF)
ISBN and Author columns together create a
composite primary key
56
Second-Normal Form (2NF)
 Partial
dependency must be
eliminated
Fields contained within a record are only
dependent upon one portion of the
primary key
 Break the composite primary key into
two parts, each part representing a
separate table

57
Second-Normal Form (2NF)

BOOKS table in 2NF
58
Third-Normal Form (3NF)

Transitive dependency

At least one of the values in the record is not
dependent upon the primary key, but upon
another field in the file
Publisher contact name has been removed
59
Summary of Normalization Steps
1NF: eliminate repeating groups, identify
primary key
 2NF: table is in 1NF and partial
dependencies eliminated
 3NF: table is in 2NF and transitive
dependencies eliminated

60
Exercise: Normalization
61