CS411 Database Systems

1
CS411Database Systems
02 The Entity-Relationship Model

• Kazuhiro Minami

2
Steps in building a DB application
Pick application domain
Conceptual design
What data do I need for my application domain?
How can I describe that data?
3
Steps in building a DB application
Pick application domain
Conceptual design
SQL Java/C/etc user interface
ER diagram
Convert ER diagram to the data model of your DBMS
product
Implement application code user interface
4
The ER model is very simple
name
name
category
price
makes
Company
Product
stockprice
buys
employs
Person
name
ssn
address
5

• Entity
• real-world object distinguishable from other
  objects
• described by its attributes
• Attribute
• Has an atomic domain string, integers, date,
• Entity set all have the same set of attributes

name
category
price
Company
name
Product
?
stockprice
boardOfDirectors
6
Relationships

• If A, B are sets, then a relation R is a subset
  of A x B.
• A 1, 2, 3 B a, b, c, d
• R (1,a), (1,c), (3,b)
• makes is a subset of Product x Company

7
name
category
name
price
makes
Company
Product
stockprice
buys
employs
Person
name
ssn
address
8
Exercises 4.1.1 and 4.1.2

• Design a database for a bank, including
  information about
• customers and their accounts. Information about a
  customer
• includes their name, address, phone, and Social
  Security
• number. Accounts have numbers, types (e.g.,
  saving, checking)
• and balances. Also record the customer(s) who own
  an account.
• Draw the E/R diagram for this database.
• Change your diagram so an account can have only
  one customer.
• Further change your diagram so a customer can
  have only one account
• Change your original diagram in (1) so that a
  customer have a set of addresses.

9
Constraint assertion about the DB that must
always be true
Key social security number uniquely
identifies a person. Single-value constraint
a person can have only one father.
Referential integrity if a person works for a
company, the
company must also be in the DB. Domain
constraint peoples ages are between 0 and
150. General constraint all others
(at most 45 students
in this room)
10
Constraints are very important

• Help us to come up with efficient storage, query
  processing, etc.
• Help us keep garbage out of the DB
• Garbage in, garbage out!

11
Referential integrity if you refer to something,
it actually exists

• More or less built into the ER model
• But NOT automatic in other models
• Examples
• Erbana, IL 61801
• Brittany Speers
• The DB equivalent of a dangling pointer
• Garbage in, garbage out!

12
Underline the key for each entity set
name
category
multi-attribute keys are okay!
price
Product
Multiple candidate keys? Pick just one to be
the key.
Person
Is this a good key?
name
ssn
address
13
Sometimes your entity might not seem to have a key
Weak entity set some or all of its key
attributes come from other classes to which it is
related.
affiliation
University
Team
)
record
sport
name
14
We can show the cardinality of a relationship
E F

• one-one
• many-one
• many-many

15
How do we model an n-way relationship?
Can still model as a mathematical set (how?)
16
What do arrows mean in n-way relationships?

• If I know the store, person, and invoice, then
  there is only one possible movie.
• VideoStore, Invoice, and Person determines
  Movie

17
What if there are several arrows?
Invoice
VideoStore
Rental
Movie
Person

• store, person, invoice determines movie
• store, invoice, movie determines person

18
How do I say invoice determines store?

• No good way best approximation
• Why is this incomplete?

19
What if we need an entity set twice in one
relationship?
the role
Product
Purchase
Store
salesperson
buyer
Person
Person
20
What if we need an entity set twice in one
relationship?
Product
Purchase
Store
buyer
salesperson
Person
21
Some versions of the ER model allow attributes on
relationships
22
You can upgrade a relationship to be an entity
set





ProductOf
date
Product
Purchase
StoreOf
Store
BuyerOf
Person
23
Exercises 4.4.1 and 4.4.2 (Weak Entity Sets)
1. One way to represent students and the grades
they get in course is to use entity sets
corresponding to students, to courses, and to
enrollments. Enrollment entities form a
connecting entity set between students and
courses and can be used to represent not only the
fact that a student is takeing a certain course,
but the grade of the student in the course. Draw
an E/R diagram for this situation, indicating
weak entity sets and the keys for the entity set.
Is the grade part of the key for enrollments? 2.
Modify your solution so that we can record grades
of the student for each of several asssignments
within a course.
24
Degree Constraints

• Constraints on degree of a relationship

lt 5
takes
Courses
Students
25
Subclasses

• Subclass special case fewer entities more
  properties.
• Example Ales are a kind of beer.
• Not every beer is an ale, but some are.
• Let us suppose that in addition to all the
  properties (attributes and relationships) of
  beers, ales also have the attribute color.

26
Example
Beers
name
manufacturer
isa
Ales
color
27
ER subclasses are different from object oriented
subclasses

• In the object-oriented world, objects are in one
  class only.
• Subclasses inherit properties from superclasses.
• In contrast, E/R entities have components in all
  subclasses to which they belong.
• Matters when we convert to relations.

28
Example
Beers
name
manf
isa
Ales
color
29
ER Design Principle 1 Model your domain
faithfully
Purchase
Product
Person
President
Person
Country
Teaches
Course
Instructor
30
Principle 2 Avoid redundancy

• Dont say the same thing in two different ways.
• Redundancy wastes space and (more importantly)
  encourages inconsistency
• The two instances of the same fact may become
  inconsistent if we change one and forget to
  change the other, related version.

31
Good
name
name
addr
ManufBy
Beers
Manufacturers
This design gives the address of each
manufacturer exactly once.
32
Bad
name
name
addr
ManufBy
Beers
Manufacturers
manf
This design states the manufacturer of a beer
twice as an attribute and as a related entity.
33
Bad
name
manf
manfAddr
Beers
This design repeats the manufacturers address
once for each beer loses the address if there
are temporarily no beers for a manufacturer.
34
Good
name
name
addr
ManfBy
Beers
Manufacturers

• Manufacturers deserves to be an entity set
  because of the nonkey attribute addr.
• Beers deserves to be an entity set because it is
  the many of the many-one relationship ManfBy.

35
Principle 3 Dont overuse entity sets

• An entity set should satisfy at least one of the
  following conditions
• It is more than the name of something it has at
  least one non-key attribute.
• or
• It is the many in a many-one or many-many
  relationship.

36
Good
name
manufacturer
Beers
No need to make the manufacturer an entity set,
because we only remember its name.
37
Bad
name
name
ManfBy
Beers
Manufacturer
Since the manufacturer is nothing but a name, and
is not at the many end of any relationship, it
should not be an entity set.
38
Principle 4 Dont Overuse Weak Entity Sets

• Beginning database designers often make most
  entity sets weak, supported by all other entity
  sets to which they are linked.
• Instead, we create unique IDs for entity sets.
• Social-security numbers, drivers license
  numbers, automobile VINs,
• Only use weak entity sets when necessary.
• Example unique player numbers across all
  football teams in the world.

39
Exercise 4.2.5 (Multiway relationships)
At a birth, there is one baby, one mother, any
number of nurses, and any number of doctors. For
each, tell how to add arrows or other elements to
the E/R diagram. a) For every baby, there is a
unique mother b) For every combination of a baby,
nurse, and doctor, there is a unique mother c)
For every combination of a baby and a mother
there is a unique doctor.
Mothers
Births
Babies
Nurses
Doctors
40
Exercise 4.2.6
Births
Doctors
Nurses
Mothers
Babies

• Use arrows to represent the following conditions
• Every baby is a result of a unique birth, and
  every birth is of a unique baby.
• In addition to (a), every baby has a unique
  mother.
• In addition to (a) and (b), for every birth there
  is a unique doctor.
• In each case, what design flows do you see?