Entities and Relationships

1
Entities and Relationships
2
A bit of Database History

• Early 1960s, data models were hierarchies
  (Hierarchical Data Model)
• Models data as stored in database (ie, not
  conceptual)
• Arrows represent record-to-record physical
  pointers
• Programming Navigating via pointers up and down
  hierarchies.

Department
Project
Employee
3
A bit of Database History (cont)

• All programs compiled
• Only users big operational clients like payroll.
• Very inflexible must follow pointers

Department
Project
Employee
4
Exercise

• Create a hierarchy for a University Course
  Registration Database using Course, Professor,
  Student, Section.
• Imagine how you would answer the query Does Prof
  X teach Student Y?

5
More History

• Mid 60s, early 70s Data Model became a Graph
  (Network Data Model)
• Still models data in database (ie, not
  conceptual)
• Arrows still physical, record-to-record pointers
• Could ask more complicated questions

Course
Section
Professor
advisor
Student
6
Modern Era

• Mid 70s Edgar Codd, Relational Data Model
• Everything modeled as tables with columns
• No longer use pointers to relate different things
• Relatedness implemented with replicated columns

7
Now try our Query

• Does Prof X teach Student Y?

Student.StdName Y? where Professor.Name
X AND Professor.Name Section.Prof AND
Section.SecNum StudentEnrollment.SecNum AND
Section.CrsNun StudentEnrollment.CrsNum AND
StudentEnrollment.StdNum Student.StdNum
X
?Y
8
The Network Guys said
Ha-Ha-Ha
FAST?
NOT!
Ha-Ha-Ha
Ha-Ha-Ha
9
Relational Data Model vs Older Models

• 1970s Not fast but flexible needed to prove
  itself
• Today Fast and Flexible Older models have
  disappeared
• Lesson Flexible wins every time
• Exercise
• In each model, network and relational,
  assuming people names are unique, find out if any
  professor is also a student

10
Relational Answer
Find Professor ? where Professor.Name
Section.Prof AND Section.SecNum
StudentEnrollment.SecNum AND Section.CrsNum
StudentEnrollment.CrsNum AND
StudentEnrollment.StdNum Student.StdNum AND
Student.StdName Professor.name
11
Network Answer
For each course
Course
For each section
Section
Professor
Find Prof Name
advisor
For each student, check name against prof name
Student
12
1976
13
Modern Era But Even Better

• Peter Chen published his PhD thesis
• His position
• RDM guys said, No way, we dont need it!
• Chens paper became the most sited paper in the
  history of computing, so we know who won that
  argument.

Relational Data Model is a model of data in the
database good for programmers. What we need is
a conceptual model of the real world to help
designers do their jobs of understanding peoples
needs
14
ER Homework, Part A

• Find the citation for Chens original paper.
• What does Chen mean when he calls his model
  unifying? Alternatively, in what way is Chens
  model unifying?
• Note It is not necessary to read the paper to
  answer this question. The answer comes from
  understanding Chens approach in the context of
  our previous class lecture.

15
Before Chen
16
Chens Proposal
17
Chens Idea

• You can model the real world with two things
• entities and relationships
• Is that enough?

18
What is an Entity?

• Something of interest, capable of independent
  existence
• A thing or a concept
• A type of thing such as Automobile and not my
  car.
• Something with properties.
• EMPLOYEE is an entity with property SALARY
• SALARY is not an entity because if I say SALARY
  you say, Whose? In other words, a SALARY only
  makes sense if we are talking a some EMPLOYEE so
  it is not capable of independent existence.
• In sentences, entities show up often as nouns
  unfortunately, so do properties.
• Check out Wikipedia and what it says about
  entities.

19
What are Properties

• The fact that an entity has a property often
  represents a specific business rule.
• A business or organization is run by rules. It is
  a well-known secret among database designers that
  the process of designing a database is that of
  capturing, in an unambiguous way, the rules of a
  business or organization.
• The above is a business rule captured in the
  Entity-Relationship language by saying that
  Salary is a property of the entity, EMPLOYEE.

Every employee earns exactly one salary
20
Exercise

• A database designer is in a unique position to
  understand how a business is run. Discuss

21
How do we picture an Entity?
EntityName
List of properties
22
Was it always this way? No

• Chens early notation, called a Bubble Diagram.

. . .
Property 2
EntityName
Property 1
23
Exercise

• Discuss the pros and cons of each approach to
  drawing an entity.

24
Notational Confusion

• Chen called EMPLOYEE an entity type and Andrew
  Pletch an entity.
• Other people said, if there is no confusion lets
  drop the type and call EMPLOYEE an entity and
  Andrew Pletch an entity instance (or just an
  instance).
• So Chens original approach should have been
  called the
• We will use the terms entity and entity instance.
• In some sense, an entity represents the set of
  its instances.
• Entities are usually identified in the singular

Entity-Type Relationship-Type Approach
25
Just to make it clear

• Exercise Why call it EMPLOYEE and not EMPLOYEES?

26
Exercise

• Work in small groups.
• In the following paragraph identify things you
  think are entities and things you think are
  properties of entities. You can also consider
  things that are implicitly referred to in the
  paragraph.

The College at New Paltz is one of the campuses
of the State University of New York. It is a
liberal arts college with programs in arts, fine
arts, business, science and engineering. Its
faculty and students come from all over the
state, the United States and the world.
27
Whats in a Name?

• A rose, by any other name
• It turns out, in database at least, a name is not
  enough.
• Both these sets of instances can legitimately be
  called instances an entity called CAR but they
  are NOT instance of the same thing, are they?

CAR
What are this entitys instances?
My car, My sons car, My wifes car, Your car,
. . .
VW Passat, Honda Accord, Toyota Corolla, Ford
Escort, . . .
28
Attributes

• Some properties of entities are called
  attributes.
• Among the attributes of an entity there are some
  that identify the entity. These are called key
  attributes.
• No two different instances of an entity have the
  same values for their key attributes.

Make Model
VIN
29
The Key to Power the Power of the Key

• Keys are also called unique identifiers.
• What is an identifier? What is a unique
  identifier?
• In the Earthsea Trilogy by Ursula Le Guin, a
  story about a mage called Ged, people or things
  would be under your control if you knew their
  true name.
• In database you control/know an entity if you
  know its key attributes. If you dont, you
  dont!!
• This is the first hint that in Entity-Relationship
  we are going to insist that we know what we are
  talking about.
• It turns out that as a language,
  Entity-Relationship is well-defined.
• What does well-defined mean?

30
Keys vs Entities

• There is a one-to-one correspondence between the
  distinct values of a key attribute and the
  instances of the corresponding entity.
• Exercise Identify the keys for the following
  entities

BOOK CAR MEDICAL PROCEDURE PROFESSOR PROCESS APART
MENT COURSE
ISBN,Author,Title VIN,Make,Model Name,P
rocedure Number Name, SSN ProcessID Buildi
ngName,ApartmentNum Name,Crs,Crs.Sec,Sem

31
A Final Word on Keys

• Things with the same key are the same thing!
• Exercise Find examples of things with the same
  key but that on the surface appear to be
  different things. Explain why they are really the
  same thing.

32
A final word on attributes

• Although it is not a hard-and-fast rule, in this
  course we will insist that attributes be
  single-valued. In other words, not records and
  not lists.

EMPLOYEE
EmployeeID FName LName Address Street
City, State, Zip List_of_Jobs
key attribute
good examples of attributes
bad examples of attributes
33
Exercise

• Lets revisit the exercise we worked on a while
  ago.
• Suppose these are our entities Add some suitable
  attributes and underscore the key attributes.

The College at New Paltz is one of the campuses
of the State University of New York. It is a
liberal arts college with programs in arts, fine
arts, business, science and engineering. Its
faculty and students come from all over the
state, the United States and the world.
34
What is a Relationship?

• An association between or among entities. Can be
  binary, ternary, quartic, etc.
• Because entities are types of things,
  relationships also have to be types of
  associations.
• Entities have instances and relationships have
  occurrences. Entities are while relationships
  happen.
• In sentences, relationships often show up as
  verbs.
• Is a relationship between the entities PERSON and
  AUTOMOBILE.
• is an occurrence of this relationship.

People own Automobiles
Andrew Pletch owns a VW Passat with VIN
1234567890987654321
35
How do we picture a Relationship?
?
As a line joining the related entities with a
diamond in the middle
The name of the relationship goes in or on top of
the diamond
The name tends to make grammatical sense in one
direction only
36
Just to make it clear
37
Can Relationships have Properties?

• Yes!
• They are also called attributes.
• They appear in the diagram underneath the
  diamond.
• Why cant PurchaseDate be an attribute of
  AUTOMOBILE?
• Reason 1 Because the same car can be sold
  several times and so have a list of PurchaseDates
• Reason 2 Because PurchaseDate only makes sense
  in the context of what car? and who bought
  it?

?
38
Attributes Dont Get Copied.

• The fact that a vehicle has an owner who has a
  name is modeled by the ltownsgt realtionship.
• We do NOT replicated this fact by also putting an
  extra attribute in the VEHICLE entity.

SSN FName LName DOB
VIN OwnerName Make Model Year
NOT permitted
39
Does a Relationship have a Key?

• Yes!
• Typically it is the combined keys of the
  participating entities.
• The key to ltownsgt is the pair (PersonID,VIN).
• We do NOT duplicate these atrributes anywhere
  else in the diagram.
• No two occurrences of ltownsgt have the same key
  values.

OWNS
PERSON
AUTOMOBILE
PersonID
VIN
purchase_date
40
Exercise

• What does ltownsgt mean?
• What are some occurrences of ltownsgt?
• Describe a general occurrence of ltownsgt.
• Can the same person ltowngt the same vehicle more
  than once? Why not?
• What would it mean to ltowngt the same vehicle more
  than once? Give a couple of examples.

41
What if you CAN ltowngt the same car more than once?

• Thats possible!
• But you need the purchase date to tell these two
  events apart
• Key to ltownsgt now (PersonID,VIN,purchase_date).
• No two occurrences of ltownsgt have the same key
  values.
• We underscore any attribute of a relationship
  that is part of the relationship key.

OWNS
PERSON
AUTOMOBILE
PersonID
VIN
purchase_date
42
Exercise

• How do the two versions of ltownsgt differ?
• What are some occurrences of the new ltownsgt?
• Describe a general occurrence of the new ltownsgt.
• Can the same person ltowngt the same vehicle more
  than once? Why not?
• What would it mean to ltowngt the same vehicle more
  than once? Give a couple of examples.

43
An Example
A library keeps records of current loans of books
to borrowers. Each borrower has a borrower and
each copy of a book has an accession (there may
be several copies of the same book). The
library keeps the name and address of each
borrower so that overdue reminders can be sent
if necessary. For each book, the library keeps
the title, authors, publisher, publication date,
ISBN, purchase price and current list price.
Borrowers can have one of two statuses - junior
and senior. There are restrictions on the number
of books a borrower may take out at one time
depending on his/her status. Books which are
out on loan may be reserved by other borrowers.
The library does not buy paperbacks. When a new
edition of a book is acquired, all copies of
earlier editions are removed from the shelves.
44
List all Nouns
Library Loan Book Borrower Borrowerid Copy Accessi
on_no B_name B_address Reminder Title Author Publi
sher Pub_date ISBN P_price C_price B_status Loan_l
imit Reservation
A library keeps records of current loans of books
to borrowers. Each borrower has a borrower and
each copy of a book has an accession (there may
be several copies of the same book). The
library keeps the name and address of each
borrower so that overdue reminders can be sent
if necessary. For each book, the library keeps
the title, authors, publisher, publication date,
ISBN, purchase price and current list price.
Borrowers can have one of two statuses - junior
and senior. There is a loan limit on the number
of books a borrower may take out at one time
depending on his/her status. Books which are
out on loan may be reserved by other
borrowers. When a book is returned the person
who made the earliest Reservation for he book is
notified.
45
Which Nouns are Entities and Which are Attributes?
Library Loan Book Borrower Borrowerid Copy Accessi
on_no B_name B_address Reminder Title Author Publi
sher Pub_date ISBN P_price C_price B_status Loan_l
imit Reservation
Why is Author not an entity? (i) It certainly
is a property of Book. (ii) All we know about
authors are their name. (iii) Authors do nothing
but author books. (iv) In a Publishing House
database Authors would be entities.
(v) In a Library database, Authors only exist as
authors of Books so do not have
independent existence.
46
List Entities and their Attributes

Loan l_date
-- how else do we know if it is
overdue Book ISBN Author Title
Pub_date C_price Borrower Borrowerid
B_name B_address B_status
Loan_limit Publisher Pub_name Reminder Copy
Accession_no P_price Reservation R_date
-- how else do we send a notice to
the earliest reservation
Why is Library dropped altogether? (i)
Although certainly an entity, there is only one
instance. (ii) All other entities have multiple
instances. (iii) No properties exist for Library
other than its name. (iv) If our application was
a library system with many libraries
then it would make sense to make Library
an entity.
47
First Look at a List of Entities
NOTES 1 Things that have keys that belong to
them are likely to be entities. 2 Nouns that are
missing keys and seem to relate to other things
are likely to be relationships and not entities.
48
Now Add Some Relationships
1 Reminder is more of a transaction or activity
than a thing. All the info needed for a
Reminder is found in other things. 2 Pub_name
can be an attribute of Book if we drop the
Publisher entity
49
Final Version (without mMpn)
50
Final Version (with mMpn)
Does every Cardholder reserve a book? (n0) Does
any Cardholder reserve more than one book?
(yn) Is every Book reserved by at least one
Cardholder? (n0) Is any Book reserved by more
than one Cardholder? (yn) Does every
Cardholder borrow a book? (n0) Does any
Cardholder borrow more than one book? (yn) Is
every Copy borrowed by at least one
Cardholder? (n0) Is any Cardholder borrowed by
more than one Cardholder? (n1) current
loans only Does every Book exist as a copy in
the Library? (y1) Does any Book have more
than one copy in the Library? (yn) Is every
Copy a copy of some Book? (y1) Is any Copy a
copy of more than one Book?(n1)
1 Cardholder not Borrower (more accurate)
51
A Class Example Part A
An Airline has planes that are used to fly
scheduled flights. Flight segments are between
two airports departure and arrival. They are
scheduled to depart at a certain time and arrive
at a certain time. The type of plane to be used
is also known. Scheduled flights are a sequence
of flight segments. Actual flights occur on
particular dates using a particular plane flown
by a particular pilot and co-pilot. They take
off at a given time (perhaps) different from
the scheduled departure time and have an ETA.
Pilots have names, SSNs, addresses,
DOB. Planes have unique numbers, capacities
both1st class and economy This database doesnt
deal with passengers.
52
A Class Example Part B
An Airline reservation system keeps information
about customers, their reservations, tickets,
seat assignments for scheduled flights.
Scheduled flights are between two airports
departure and arrival. They are scheduled to
depart at a certain time and arrive at a certain
time. The type of plane to be used is also
known. Scheduled flights have flight
numbers. Customer info includes name, address,
email address, phone number A reservation is for
a certain customer on a sequence of scheduled
flights. It has a locator number. Each
reservation has a single source and single
destination airport. A ticket is a paid
reservation. It has a ticket number and a payment
method Including credit card info
53
Weak Entities

• An entity is weak if it depends on another entity
  for part of its key.
• Remember, we cant copy keys around an ER
  diagram.
• We cant do the following
• But because the key to an Order Line is really
  the combination of (OrderNumber,LineNumber), Line
  instances depend on the key to Order for part of
  their key.

54
Weak Entities (cont)

• Instead we draw a double line around the weaker
  (dependent) entity and the relationship that it
  depends on.
• This picture says that Line is a weak entity
  whose key consists of the pair (OrderNumber,
  LineNumber).
• Weak entities always have a (1,1) participation
  number pair linking to the entity they depend
  upon. Why?

55
Weak Entity Another Example

• Some times a relationship from one departments
  point a view is an entity in some other
  department.
• For the Registrars Office, Enrollment as a
  relationship between two entities Student and
  Course
• For the Bursars Office, Enrollment is a
  Chargeable Item that associates weakly with a
  Students Account.
• Why are ltenrollsgt and Enrollment are the same
  thing???

56
How to merge the two diagrams?
NOTE We converted a relationship into a
weak- entity relationship
become
Registrars View
Bursars View
57
A New Model for an Old Idea

• The previous example used to be called
  aggregation. It used to be modeled as
• The box labeled Enrollment turned the ltenrollsgt
  relationship into an entity with the same key.
• We dont use this method any more because most
  modeling tools dont support it.

58
IS_A

• Before inheritance became popular because of OOP
  there was IS_A.
• Both Professor and Student possess the attributes
  ID, Name and DOB by inheritance.
• The key to Professor and the key to Student is
  the key to Person.

59
Modeling History

• The ltborrowsgt relationship in the Library model
  only models current loans and not past loans.
• This is clear for two reasons
• The same Copy can not be loaned more than once
• Reason?
• The same Cardholder can not borrow the same Copy
  more than once.
• Reason?

60
Modeling History 2

• Suppose we wanted to model a complete loan
  history of all loans past and present.
• To allow two loans of the same copy we would
  first need to change the copy Max PN to n.
• To allow the same Cardholder to borrow the same
  Copy more than once (but on different occasions)
  we need to add time to the key. To show that this
  is part of the key to ltborrowsgt and not just
  another attribute we underline it.

61
Self-Related Entities

• Some entities are related to themselves.
• This models what kind of a data structure?