ER & Relational:
Digging Deeper
R &G - Chapters 2 & 3
Databases Model the Real World
•“Data Model” a llows us to translate real world
things into structure s computers can store
•Many models: Relat ional, E-R, O-O, XML ,
Network, Hierarchic al, etc.
•Relational
– Rows & Columns
– Keys & Foreign Keys to link R elations
sid name login age gpa
53666 Jones jones@cs 18 3.4
53688 Smith smith@eecs 18 3.2
53650 Smith smith@mat h 19 3.8
sid cid grade
53666 Carnatic101 C
53666 Reggae203 B
53650 Topology112 A
53666 History105 B
Enrolled Students
Aggregation
Used to model a
relationship
involving a
relationship set
.
Allows us to treat a
relationship set
as an entity set
for purposes of
participation in
(other)
relationships.
Aggregation vs . ternary relationship?
Monitors is a distinct r elationship, with a
descriptive attribute.
Also, can say that each sponsorship is
monitored by at most one employee.
until
Employees
Monitors
lot
name
ssn
budget
did
pid
started_on
pbudget
dname
Departments
Projects Sponsors
since
Conceptual Design Using the ER Model
•ER modeling
can
get tricky!
•Design choices:
– Should a concept be modele d as an entity or an att ribute?
– Should a concept be modele d as an entity or a rela tionship?
– Identi fying relationships: B inary or ternary? Ag gregation?
•Note constraints of the ER Model:
– A lot o f data semantics can ( and should) be capture d.
– But so me constraints cannot be captured in ER diag rams.
• We’ll refine things in our logical (relational) design
Entity vs. Attribute
•Should
address
be an attribute of Employees or an
entity (related to Em ployees)?
•Depends upon how we want to use add ress
information, and th e semantics of the d ata:
• If we have several addr esses per employee,
address
must be an entity (sinc e attributes cannot be set-
valued).
• If the structure (city, st reet, etc.) is impo rtant,
address
must be modeled as a n entity (since attribute values
are atomic).
• If the lifetime of th e address differs from the entity,
address
must be modeled as an entity (s ince attributes
are deleted with their entity).
Entity vs. Attribute (Cont.)
•Works_In2 does not
allow an employee to
work in a department
for two or more periods.
–(why not?)
•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.
name
Employees
ssn lot
Works_In2
from to
dname
budget
did
Departments
dname
budget
did
name
Departments
ssn lot
Employees Works_In3
Duration
from to
