The EntityRelationship Model

1
The Entity-Relationship Model

• Chapter 2

2
Participation Constraints

• Does every department have a manager?
• If so, this is a participation constraint the
  participation of Departments in Manages is said
  to be total (vs. partial).
• Every did value in Departments table must appear
  in a row of the Manages table (with a non-null
  ssn value!)

since
since
name
dname
name
dname
ssn
lot
budget
did
budget
did
Departments
Employees
Manages
Works_In
since
3
Weak Entities

• A weak entity can be identified uniquely only by
  considering the primary key of another (owner)
  entity.
• Owner entity set and weak entity set must
  participate in a one-to-many relationship set
  (one owner, many weak entities).
• Weak entity set must have total participation in
  this identifying relationship set.

name
cost
pname
age
ssn
lot
Dependents
Policy
Employees
4
Aggregation
name
lot
ssn

• Used when we have to model a relationship
  involving (entitity sets and) a relationship set.
• Aggregation allows us to treat a relationship set
  as an entity set for purposes of participation
  in (other) relationships.

Monitors
until
since
started_on
dname
pid
pbudget
did
budget
Sponsors
Departments
Projects

• Aggregation vs. ternary relationship
• Monitors is a distinct relationship,
• with a descriptive attribute.
• Also, can say that each sponsorship
• is monitored by at most one employee.

5
Conceptual Design Using the ER Model

• Design choices
• Should a concept be modeled as an entity or an
  attribute?
• Should a concept be modeled as an entity or a
  relationship?
• Identifying relationships Binary or ternary?
  Aggregation?
• Constraints in the ER Model
• A lot of data semantics can (and should) be
  captured.
• But some constraints cannot be captured in ER
  diagrams.

6
Entity vs. Attribute

• Should address be an attribute of Employees or an
  entity (connected to Employees by a
  relationship)?
• Depends upon the use we want to make of address
  information, and the semantics of the data
• If we have several addresses per employee,
  address must be an entity (since attributes
  cannot be set-valued).
• If the structure (city, street, etc.) is
  important, e.g., we want to retrieve employees in
  a given city, address must be modeled as an
  entity (since attribute values are atomic).

7
Entity vs. Attribute (Contd.)
to
from

• Works_In2 does not allow an employee to
  work in a department for two or more
  periods.
• Similar to the problem of wanting to record
  several addresses for an employee we want to
  record several values of the descriptive
  attributes for each instance of this
  relationship.

budget
Departments
Works_In2
name
ssn
lot
Works_In3
Departments
Employees
8
Entity vs. Relationship

• First ER diagram OK if a manager gets a separate
  discretionary budget for each dept.
• What if a manager gets a discretionary budget
  that covers all managed depts?
• Redundancy of dbudget, which is stored for each
  dept managed by the manager.

since
dbudget
name
dname
ssn
did
lot
budget
Employees
Departments
Manages2
Misleading suggests dbudget tied to managed
dept.
9
Binary vs. Ternary Relationships

• A ternary relation Contracts relates entity sets
  Parts, Departments and Suppliers, and has
  descriptive attribute qty. No combination of
  binary relationships is an adequate substitute
• 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?

10
Summary of Conceptual Design

• Conceptual design follows requirements analysis,
• Yields a high-level description of data to be
  stored
• ER model popular for conceptual design
• Constructs are expressive, close to the way
  people think about their applications.
• Basic constructs entities, relationships, and
  attributes (of entities and relationships).
• Some additional constructs weak entities, and
  aggregation.
• Note There are many variations on ER model.

11
Summary of ER (Contd.)

• Several kinds of integrity constraints can be
  expressed in the ER model key constraints,
  participation constraints, and overlap/covering
  constraints for ISA hierarchies. Some foreign
  key constraints are also implicit in the
  definition of a relationship set.
• Some constraints (notably, functional
  dependencies) cannot be expressed in the ER
  model.
• Constraints play an important role in determining
  the best database design for an enterprise.

12
Summary of ER (Contd.)

• Symbol Meaning

Entity
Weak Entity
Relationship
Identifying Relationship
Attribute
Key Attribute
13
Summary of ER (Contd.)

• Symbol Meaning

Multi-valued
Composite Attribute
Derived Attribute
14
Summary of ER (Contd.)

• Symbol Meaning

Total Participation of E2 In R
R
E2
E1
1
N
Cardinality Ratio 1N For E1E2 In R
E2
E1
R
Structural Constraint (Min, Max) On
participation Of E in R
(Min, Max)
E
R
15
DNumber
Name
Salary
Addr
1
No-Emp
1
Supervision
N
department
Works For
Employee
1
Name
1
SSN
N
1
location
Controls
M
M
1
manages
Dependents
N
Works-on
Project
Location
N
Number
Hours
N
Name

Name
Dependent
Relationship
Bdate
Sex
16
Summary of ER (Contd.)

• ER design is subjective. There are often many
  ways to model a given scenario! Analyzing
  alternatives can be tricky, especially for a
  large enterprise. Common choices include
• Entity vs. attribute, entity vs. relationship,
  binary or n-ary relationship, whether or not to
  use, and whether or not to use aggregation.
• Ensuring good database design resulting
  relational schema should be analyzed and refined
  further. FD information and normalization
  techniques are especially useful.

17
EER

•   EERERsub class/super class
  (specialization/generalization)
•  Specialization Defines a set of subclasses of
  an entity setsuperclass
•  Generalization Result of computing the union
  of two or more entity sets to produce a
  higher-level entity set

18
Summary of ER (Contd.)

•  Employee (superclass) entity set can be grouped
  into secretary, engineer, manager,
  technician,(subclass)

JobType
Addr
BirthDate
Employee
Name
SSN
d
EngType
Typing Speed
Tgrade
Secretary
Engineer
Technician
19
Summary of ER (Contd.)

•  Vehicle (superclass) entity set can be
  categorized into car, truck, (subclass)

Price
VehicleId
LicencePlateNo
Vehicle
NoOfAxles
d
NoOfPassengers
Tonnage
MaxSpeed
Truck
Car
20
EER

• An entity cannot merely exist by being a member
  of a subclass but no superclass however, it is
  not essential that every entity in a superclass
  be a member of some subclass
•  Inheritance
• An entity of subclass inherits
• All attributes of superclass
• All relationships in which superclass
  participates plus its own specific attributes and
  relationships together
• Why specialization
•         Define a set of subclasses of an entity
  set
•         Associate additional specific attributes
  with each subclass
•         Establish additional specific
  relationship sets between each subclass and
  other entity sets
•   

21
EER

• 4 Possible constraints on specialization
• 1) Disjoint, total 2) Disjoint, partial
• 3) Overlapping, total 4) Overlapping, partial
•          Disjointness
•         DisjointAn entity can be a member of at
  most one of the subclasses
•         OverlapWhen sub-classes are not
  disjoint 
•          Completeness
•         Total Every entity in super class must
  be a member of some subclass
•         PartialAn entity might not belong to
  any subclass

22
EER

• Tree-structured All subclasses have only one
  parent
•          Graph-structured A subclass may have
  multiple superclasses
•         Engineering_Manger inherits attributes
  and relationships from multiple superclasses
•          Multiple Inheritance If same
  attributes can be inherited from more than one
  superclass
•         Bank employee Instead of defining
  attribute salary for superclass employee, we
  define attribute pay for each of full-time,
  part-time, teller, and secretary as follows
•    Full-time pay (0-100k), Part-time pay
  (0-30/hr)
•    Teller pay (0-30k), Secretary pay (0-35k)

23
EER
Employee
Secretary
Pay
PartTime
FullTime
Teller
Pay
Pay
Pay
FullTime-Secretary
24
Employee
d
d
Engineer
Secretary
Technician
Hourly Employee
Manager
Salaried Employee
Engineering Manager
A specialization lattice with the shared subclass
Engineering_Manager
25
EER

• Solutions
•  Keep all and use aliaspart-time.pay and
  secretary.pay
•  Choose one based on implementation
•  Force user to choose
•  Error report
• No ambiguity
•    If salary attribute is retained in only one
  superclass Employee all subclasses share the
  same definition of salary
• An attribute originating in the same superclass
  is inherited more than once via different paths
•    Attribute will be included once in subclasses
  
•  

26
Name
Address
SSN
Person
Birth-date
Major-Dept
o
Salary
Student
Alumnus
Degrees
Employee
d
Major
Year
d
Degree
Under-Grad- Student
Faculty
Student- Assistant
Graduate- Student
Staff
Rank
Class
d
Degree-Prog
Project
Research-Assistant
Teaching-Assistant
Course
Specialization lattice (with multiple
inheritance) for a University Database
27
EER

• Types of specialization
• Predicate-defined Entities will become member of
  a subclass by satisfying condition explicit or
  predicate--Can automatically handled by the
  system
• User Defined