Data Modeling and ERD
Engineering and Technology Management
Business rules
Business rule is a statement that defines or
A
constraints some aspects of the business.
are derived from policies, procedures, events, functions,
and other business objects, and state constraints on the
organization.
govern how data are handled and stored.
govern creating, updating, and removing data in an
information processing and storage system.
Engineering and Technology Management
Examples
Every student in the university must have a
faculty advisor.
A student may register for a section of a
course only if s/he has successfully completed
the prerequisities for that course.
A preferred customer qualifies for a 10
percent discount, unless he has an overdue
account balance.
Engineering and Technology Management
Business rules
Assert business structure
Control/influence business behavior
Expressed in terms familiar to end users
Automated through DBMS software
Engineering and Technology Management
Business rules
Declarative what, not how
Precise clear, agreed-upon meaning
Atomic one statement
Consistent internally and externally
Expressible structured, natural language
Distinct non-redundant
Business-oriented understood by business
people
Engineering and Technology Management
Entity-Relationship Model
First proposed by Chen 1976
Several times expanded
Different notations available, most notably
Chen original
Crow foot
Engineering and Technology Management
Models and Modeling
Model is always a simplified depiction of reality
Modeling always has a certain purpose
What might be important in one context might be
uninteresting in another one
In creating a model, we are abstracting from
the full complexity of real life
We need to decide on which facts and relations to
keep, and which special cases to strip away
Engineering and Technology Management
E-R model constructs
Entity Type collection of entities that share common
properties or characteristics (often corresponds to a
table)
Entity instance a single occurence of an entity type
(often corresponds to a row in a table)
Attribute - property or characteristic of an entity type
that is of interest to the organization (often corresponds
to a field in a table)
Relationship type link (association) between entity
types (corresponds to primary key-foreign key equivalencies
in related tables)
Relationship instance association between entity
instances
Engineering and Technology Management
Sample E-R diagram
Engineering and Technology Management
Basic E-R notation
Entity
symbols
Relationship
symbols
Engineering and Technology Management
A special
entity that is
also a
relationship
Attribute
symbols
10
Entities
Person: EMPLOYEE, STUDENT, PATIENT
Place: STORE, WAREHOUSE, STATE
Object: MACHINE, BUILDING, AUTOMOBILE
Event: SALE, REGISTRATION, RENEWAL,
RETURN
Concept: ACCOUNT, COURSE, WORK CENTER
Engineering and Technology Management
11
What should an entity be?
SHOULD BE:
An object that will have many instances in the
database
An object that will be composed of multiple
attributes
An object that we are trying to model
SHOULD NOT BE:
A user of the database system
An output of the database system (e.g. a report)
Engineering and Technology Management
12
Inappropriate entities
Systemuser
Systemoutput
Appropriate entities
Engineering and Technology Management
13
Entities
Exercise
Identify possible entities in the following description.
An Airline company wants to store and manage their
data on flights. They own several airplanes, each
with a certain number of seats, and have a number
of employees. Also they have customers in their
frequent flyer program, each has a certain mileage in
his/her account. The planes are assigned to certain
flights, as well as employees, and customers can buy
seats on the flights. Each flight has a destination, a
time and date, and a number of miles.
Engineering and Technology Management
14
Attributes
Attribute - property or characteristic of
an entity type
Classifications of attributes:
Simple versus Composite Attribute
Single-Valued versus Multivalued Attribute
Stored versus Derived Attributes
Identifier Attributes
Engineering and Technology Management
15
Identifiers (Keys)
Identifier (Key) - An attribute (or combination
of attributes) that uniquely identifies individual
instances of an entity type
Simple Key versus Composite Key
Candidate Key an attribute that could be a
keysatisfies the requirements for being a key
Engineering and Technology Management
16
Characteristics of identifiers
Will not change in value
Will not be null
No intelligent identifiers (e.g. containing
locations or people that might change)
Substitute new, simple keys for long,
composite keys
Engineering and Technology Management
17
A composite attribute
Anattribute
brokeninto
componentparts
Engineering and Technology Management
18
Simple key attribute
Thekeyisunderlined
Engineering and Technology Management
19
Composite key attribute
Thekeyiscomposed
oftwosubparts
Engineering and Technology Management
20
Entity with a multivalued attribute (Skill) and
derived attribute (Years_Employed)
Derived from date
employed and current date
Engineering and Technology Management
Multivalued:
an employee can have
more than one skill
21
An attribute that is both multivalued and composite
Engineering and Technology Management
22
Attributes
Exercise
Identify possible attributes in the example from last
exercise. Find additional possible attributes. Decide
on which attributes would be multi-valued, and which
would be candidate keys.
An Airline company wants to store and manage their
data on flights. They own several airplanes, each
with a certain number of seats, and have a number
of employees. Also they have customers in their
frequent flyer program, each has a certain mileage in
his/her account. The planes are assigned to certain
flights, as well as employees, and customers can buy
seats on the flights. Each flight has a destination, a
time and date, and a number of miles.
Engineering and Technology Management
23
Relationships
Relationship Types vs. Relationship Instances
The relationship type is modeled as the diamond and
lines between entity typesthe instance is between
specific entity instances
Relationships can have attributes
These describe features pertaining to the association
between the entities in the relationship
Two entities can have more than one type of
relationship between them (multiple
relationships)
Engineering and Technology Management
24
Degree of relationships
Degree of a Relationship is the number of
entity types that participate in it
Unary Relationship
Binary Relationship
Ternary Relationship
Engineering and Technology Management
25
Degree of relationships
One entity
related to
another of
the same
entity type
Entities of two
different
types related
to each other
Engineering and Technology Management
Entities of
three
different
types related
to each other
26
Unary relationships
Engineering and Technology Management
27
Binary relationships
Engineering and Technology Management
28
Ternary relationships
Note: a relationship can have attributes of its own
Engineering and Technology Management
29
Cardinality of relationships
One to One
Each entity in the relationship will have exactly one
related entity
One to Many
An entity on one side of the relationship can have
many related entities, but an entity on the other side
will have a maximum of one related entity
Many to Many
Entities on both sides of the relationship can have
many related entities on the other side
Engineering and Technology Management
30
Cardinality constraints
Cardinality Constraints - the number of instances of
one entity that can or must be associated with each
instance of another entity.
Minimum Cardinality
If zero, then optional
If one or more, then mandatory
Maximum Cardinality
The maximum number
Engineering and Technology Management
31
Cardinality
Engineering and Technology Management
32
Basic relationship with only maximum cardinalities showing
Mandatory minimum cardinalities
Engineering and Technology Management
33
Optional cardinalities with unary degree, one-to-one
relationship
Engineering and Technology Management
34
Relationship type
Entity and Relationship instances
Engineering and Technology Management
35
A binary relationship with an attribute
Here,thedatecompletedattributepertainsspecifically
totheemployeescompletionofacourseitisan
attributeoftherelationship.
Engineering and Technology Management
36
A ternary relationship with attributes
Engineering and Technology Management
37
A unary relationship with an attribute. This has
a many-to-many relationship
Representingabillofmaterialsstructure
Engineering and Technology Management
38
Examples of multiple relationships entities can
be related to one another in more than one way
Employees and departments
Engineering and Technology Management
39
Professors and courses (fixed upon constraint)
Here,max
cardinality
constraint is 4
Engineering and Technology Management
40
Cardinalities
Exercise
Find the right cardinalities...
Id
Name
Student
Grade
takes
advises
Professor
Engineering and Technology Management
Course
uses
Textbook
41
Cardinalities
Exercise
Infer the business rule difference(s) to Bogazici.
Professor
Course
gives
What does the first company not support compared to the
second one?
Customer
Date
Customer
Product
buys
Invoice
Product
buys
Invoice
Engineering and Technology Management
42
Strong vs. Weak Entities, and
identifying relationships
Strong entities
exist independently of other types of entities
has its own unique identifier
represented with single-line rectangle
Weak entity
dependent on a strong entitycannot exist on its own
does not have a unique identifier
represented with double-line rectangle
Identifying relationship
links strong entities to weak entities
represented with double line diamond
Engineering and Technology Management
43
Strong and weak entities
Strong entity
Identifying
relationship
Engineering and Technology Management
Weak entity
44
Supertype / Subtype and Subset
Supertype / Subtype
Implements generalisation
All attributes of Supertype pertain to Subtypes
Normally split on one attribute
Subset
Similar, but non-excluse
One entity instance can belong to more than one class
Engineering and Technology Management
45
Supertype/subtyperelationships
Engineering and Technology Management
46
ERD
Exercise
Model the following situation using ERD. Include
appropriate attributes.
A consulting company maintains offices in several
countries. Each office can have several employees
assigned, each employee is assigned to one office.
Customers offer projects to the company, each
project is from one customer. Employees work on the
project for a given time and intensity, and can work
on more than one project at the same time. A project
will have several employees assigned. Each
employee has a list of skills. An employee can be
either a senior or junior consultant (who has a date
for promotion set), a senior one manages several
junior one, each junior is assigned to one senior. For
each project, there can be several invoices with
different amounts and dates.
Engineering and Technology Management
47
