Designing E/R Diagrams

1
Designing E/R Diagrams
Updated January 25. 2005
2
Conceptual Data Models

• Conceptual data models provide languages to
  describe conceptual schemas.
• Conceptual schemas are used to describe the
  classes of objects that occur in an application
  area, their properties, their relationships, and
  the constraints that hold with respect to those
  classes of objects.
• Center on what kind of objects a database
  contains and not on how these objects are
  stored (? Internal Schema) and not on how these
  objects are represented / displayed to a person
  that accesses the database (? External Schema).

3
Conceptual Data Models ---What are they good for?

• As a database design tool formalizing the
  information requirements of the end users
• As a documentation tool for databases (to help
  programmers, especially those that have to update
  the database)
• As a data model of a database management system
  (only very few experimental systems exist)
• As a tool to describe domain ontologies
  (terminology and concepts in a UoD)
• As a tool of system analysis

4
X
(n,m)
R
Person
Entity Type
Entities of type X participate at least n, at
most m times in relationship R indicates ?.
Attribute
name
?
X
Is-insured
Relationship Type
Y
Entity type X is a subtype of type Y
Wedding
Weak Entity Type
T1
T2
Type T1 and T2 are overlapping an entity
can belong to both T1 and T2 default is disjoint
occurred
Identifying Relationship
phone
Multi-valued Attribute
ssn
Key Attribute
to
Derived Attribute
from -------
Partial Key
T1
?
T3
T3T2? T1
Optional Attribute
amount
?
?
T2
E/R Model Symbols for COSC 3480
5
UGrad
(1,1)
ssn
?
gre
?
mentor
salary
Student
?
name
(0,25)
(1,1))
Grad
(0,2)
employs
(0,)
home
took
(0,30)
(0,)
Department
(0,)
(0,)
Course
Semester
name
grade
semesterid
title
Cou
took-not-a-set -2.5 Other Errors -1.5 (or -3-4
if major)
University Problem Final03 COSC 3480
6
Exam1 Fall 2005 Problem

• Design a good entity-relationship diagram that
  describes the following objects in a university
  application students, departments, sections
  taught in the present and future, and courses.
  Departments have a name that uniquely identifies
  the department. Students are identified by a
  unique social security number, zero, one or
  multiple e-mail addresses, and an optional gpa
  (new students do not have a gpa yet). Courses
  have a unique course number and a course title.
  Courses are offered in one or more sections at a
  particular time. Sections are identified by the
  time they are offered (e.g. 1030-noon TUTH) and
  by the course they are associated with.
  Additionally sections are characterized by the
  class room the section is taught in. Only
  information concerning sections that are taught
  in the present or in the future is stored in the
  database. Students take a course in a particular
  semester and receive a grade for their
  performance. Sometimes students take the same
  course again in a different semester. There are
  no limits on how many courses a student can
  complete, and on how many students completed a
  particular course. Each student is associated
  with a least one department. Some students are
  graduate students that are additionally
  characterized by their most recent GRE-score.
  Some graduate students work for a department and
  receive a salary for their services. Each
  department employs at most 75 graduate students
  graduate students are not allowed to work for
  multiple departments.

7
University Problem Exam105
E-mail
ssn
gre
salary
Student
name
?
Grad
(0,1)
(1,)
employs
gpa
(0,)
assoc
took
(0.)
title
(0,75)
Cou
Department
(0,)
(0,)
Course
Semester
name
grade
(0,)
semesterid
offered
(1,1)
Section
took-not-a-set -1.5 Section not weak -2 Other
Errors -0.5--1 if minor
-23 if major
Time-offered
classroom
8
Sal
ssn
Month
name
name
birthd
(0,)
name
from
to
(0,)
contr
(0,)
Player
Team
(25,)
city
Home
Visit
(0,)
(0,)
(0,)
Contract set viol 3, Other -2
pos
play
played-in.
pos
(1,1)
(18,)
score
Game
Date
Game
Solution Problem6 Exam0 Spring 2003
9
Identifying Keys and Relationshipsfor Entity
Types

• Each entity type that is not subtype of another
  entity type needs
• Case1 Normal Entity Type (single rectangle)
• A single attribute (straight line) or
• A set of attributes or
• Case2 Weak Entity Type (double rectangle)
• A set of relationships (double diamond) or
• A set of relationships or a single attribute
  (dotted line) or
• A set of relationships and a set of attributes
  (dotted line)
• that uniquely identifies the instances of the
  entity type
• Remark min-max cardinalities for weak entity
  types for their participation in identifying
  relationships have to be (1,1)!!

10
name
ssn
(0,)
Male
?
occurred
husband
Person
?
?
wife
Female
(0,)
(1,1)
Wedding
Is-insured
(0,)
(0,)
Company
location
name
amount
from
to
E/R Diagram for Multi-Weddings
11
Valid E/R Diagrams

• An E/R diagram is valid if and only if
• It is syntactically correct (e.g. specifies all
  key constraints,)
• It specifies the entity types, relationship
  types, attribute types, and subtype relationships
  necessary to satisfy all information
  requirements.
• It does not specify any invalid constraints.

12
Priorities when Choosing Between Valid E/R
Diagrams

1. Express all constraints (you can express!)
2. Use and do not change terminology and class
   structure of the application domain
3. Keep it simple (avoid defining entity types that
   do not serve any purpose)
4. Avoid redundancy (but derived attributes are
   okay)!

13
A Quite Bad E/R Diagrams
?
gpa
Name
department
(0,)
works- for
(0,)
Company
Person
gender
wife
husband
(0,)
ssn
takes
(0,)
salary
(0,)
is-married- to
(0,)
Section
S
?
time
Course
C
14
Example Too many Entity Types /Dont use
Foreign Keys
Example Persons as well as animals can be
insured
P
Name
is- insured
(0,)
(0,)
Company
Person
name
ssn
(0,)
Boss-ssn
Bad E/R Diagram!
Animal
A
15
E/R Diagram Design Typical Errors

1. Missing Constraints
2. Unexpressed Constraints due to bad design
3. Every entity type needs a key
4. Attribute associated with the wrong entity type
   (relationship type)
5. Relationships are sets!
6. No partial participation in relationships!
7. Missing existence dependencies (use subclasses)
8. Invalid constraints
9. Using Subtypes for n1 relationships using
   relationships when subtypes should be used.
10. When defining relationships Too general entity
    types for participating entities
11. Too many entity types
12. Using foreign keys instead of relationships

16
Other Issues in E/R Design

1. No relationships of relationships --- solution
   create an entity type that represent instances of
   the relationship (or use aggregation as discussed
   in the textbook)
2. value or entity type --- solution choose entity
   type if it helps expressing constraints
   otherwise, use value-type.

17
University E/R Design Problem

• Design a good entity-relationship diagram that
  describes the following objects in an university
  application students, instructors, professors,
  and courses. Students are subdivided into
  graduate and undergraduate students. Students
  take a course in a particular semester and
  receive a grade for their performance. Sometimes
  students take the same course again in a
  different semester. There are no limits on how
  many courses a student can take, and on how many
  students completed a particular course. Each
  graduate student has exactly one advisor, who
  must be a professor, whereas each professor is
  allowed to be the advisor of at most 20 students.
  Courses have a unique course number and a course
  title. Students and professors have a name and a
  unique ssn students additionally have a gpa
  moreover, graduate students have a GRE-score, and
  undergraduate students have a single or multiple
  majors. Professors can be students and take
  courses, but graduate students cannot be
  undergraduate students.
• Indicate the cardinalities for each relationship
  type assign roles (role names) to each
  relationship if there are ambiguities! Use
  sub-types, if helpful to express constraints!

18
gre
advises
Professor
ssn
Grad
(0,1)
(0,20)
?
name
?
major
?
?
Student
Ugrad
?
Person
(0,)
took
grade
gpa
(0,)
(0,)
Course
Semester
title
Cou
semesterid
Enrolls-not-a-set -4 Student must be ugrad or
grad -1 Other Errors -2 (or 3 if quite major)
University Problem (slightly different from
Exam003)
19
number
ssn
Cred-Card
phone
Problem 1 Exam1 Fall03
addr
company
(0,)
Client
(0,300)
A
discount
B
?
(0,1)
tr
(1,1)
Gold_Cl.
(0,)
C
(1,1)
Hotel
Transaction
day_ made
G
(1,50)
D
address
day
Ho
(1,1)
Grading Minor Error -1 Medium Error -2 Major
Error -3 or 4 0-4 points if too many errors
(1,)
(1,)
Date
Reservation
F
total
G
avail
(1,1)
(0,)
(0,)
Category
from
rate
to
Res
E
(0,)
room-type
Aguaranteed Bhas_transaction Cfor_hotel
Dconsists_of Efor_category Favail-rooms
Gtotal-rooms modified on Feb. 3, 2004
20
Aggregation
name
lot
ssn

• Used when we have to model a relationship
  involving (entity 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.

21
NFL E/R Design ---Ungraded Homework --- due
Th., Jan. 27,2005

• Design an Entity-Relationship Diagram that models
  the following objects and relationships in the
  world of football (NFL) teams, players, games,
  managers and contracts. Each (NFL-) team has a
  unique team name, and a city it plays in. Each
  person being part of the NFL-world has a unique
  ssn and a name. Additionally, for players their
  weight, height, position and birth dates are of
  importance. Players have a contract with at most
  one team and receive a salary for their services,
  and teams have at least 24 and at most 99 players
  under contract. Each team has one to three
  managers managers can work for at most 4 teams
  and receive a salary for each of their
  employments. Players cannot be managers. A game
  involves a home-team and visiting-team
  additionally, the day of the game, and the score
  of the game are of importance teams play each
  other several times in a season (not on the same
  day!). Moreover, for each game played we like to
  know which players participated in the game and
  how many minutes they played.
•  
• Indicate the cardinalities for each relationship
  type assign roles (role names) to each
  relationship if there are ambiguities! Use
  sub-types, if helpful to express constraints!