Carnegie Mellon Univ. Dept. of Computer Science 15-415 - Database Applications

1
Carnegie Mellon Univ.Dept. of Computer
Science15-415 - Database Applications

• C. Faloutsos
• E-R diagrams

2
Overview

• concepts
• Entities
• Relationships
• Attributes
• Specialization/Generalization
• Aggregation
• turning E-R diagrams to tables

3
Tools
Entitie s (entity sets)
N
M
Relationships (rel. sets) and mapping
constraints
P
attributes
4
Example

• Students, taking courses, offered by instructors
  a course may have multiple sections one
  instructor per course
• nouns -gt entity sets
• verbs -gt relationships

5
...
name
STUDENT
ssn
INSTRUCTOR
issn
6
...
name
STUDENT
c-id
ssn
c-name
COURSE
INSTRUCTOR
issn
but sections of course (with different
instructors)?
7
STUDENT
c-id
COURSE
SECTION
s-id
but s-id is not unique...
INSTRUCTOR
issn
8
STUDENT
N
c-id
takes
M
COURSE
s-id
SECTION
INSTRUCTOR
issn
9
STUDENT
N
c-id
takes
M
s-id
COURSE
SECTION
N
teaches
1
INSTRUCTOR
10
Cardinalities

• 1 to 1 (example?)
• 1 to N
• N to M

11
STUDENT
N
c-id
takes
M
1
N
has
s-id
COURSE
SECTION
N
teaches
1
INSTRUCTOR
12
More details

• weak entities if they need to borrow a unique
  id from a strong entity - DOUBLE box.
• c-id s-id unique id for SECTION
• discriminator (eg., s-id)

c-id
1
N
s-id
has
SECTION
COURSE
13
More details

• self-relationships - example?

14
manages
1
EMPLOYEE
N
15
More details

• 3-way and k-way relationships?

N
M
EMPLOYEE
TOOL
uses
P
PROJECT
16
More details - attributes

• candidate key (eg., ssn employee)
• primary key (a cand. key, chosen by DBA)
• superkey (eg., (ssn, address) )
• multivalued or set-valued attributes (eg.,
  dependents for EMPLOYEE)
• derived attributes (eg., 15 tip)

17
More details

• in the text (eg., total participation)

SECTION
0N
teaches
11
INSTRUCTOR
18
Overview

• concepts
• Entities
• Relationships
• Attributes
• Specialization/Generalization
• Aggregation
• turning E-R diagrams to tables

19
Specialization

• eg., students part time (credit-hours) and full
  time (major)

name
STUDENT
ssn
IS-A
PT-STUDENT
FT-STUDENT
major
credits
20
Observations

• Generalization exact reverse of specialization
• attribute inheritance
• could have many levels of an IS-A hierarchy

21
Aggregation

• treat a relationship as an entity
• rarely used

M
N
MAKER
HD
CPU
22
Overview

• concepts
• Entities
• Relationships
• Attributes
• Specialization/Generalization
• Aggregation
• turning E-R diagrams to tables

23
STUDENT
N
c-id
grade
takes
M
1
N
has
s-id
SECTION
COURSE
N
teaches
1
INSTRUCTOR
24
Strong entities

• just list the attributes, and underline the
  primary key, eg.
• STUDENT(ssn, name, address)

25
Multivalued attributes

• Eg., EMPLOYEE with many dependents
• a new table, with (ssn, dependent-name)

26
Relationships

• get primary keys all involved entities
• primary key - depends on cardinality
• 1 to 1 either eg EMPLOYEE( ssn, empno, name,
  ...)
• 1 to N the key of the N part eg.
• TEACHES( issn, c-id, s-id)
• N to M both keys - eg
• TAKES( ssn, c-id, s-id, grade)

27
Relationships

• 1 to N no need for separate table - eg.,
• SECTION( issn, room-num, c-id, s-id)
• instead of
• SECTION1(c-id, s-id, room-num)
• TEACHES(issn, c-id, s-id)
• for rel. between strong and corresponding weak
  entity, no need for table, at all!

28
Generalization/Spec.

• Two solutions
• - one table for each or
• - no table for super-entity
• (pros and cons?)

29
Generalization/Special.

• Eg.,
• STUDENT(ssn, name, address)
• PT-STUDENT(
• FT-STUDENT(

30
Generalization/Special.

• Eg.,
• STUDENT(ssn, name, address)
• PT-STUDENT( ssn, num-credits)
• FT-STUDENT( ssn, major)

31
Generalization/Special.

• no super-entity
• STUDENT(ssn, name, address)
• PT-STUDENT( ssn, num-credits
• FT-STUDENT( ssn, major

32
Generalization/Special.

• no super-entity
• STUDENT(ssn, name, address)
• PT-STUDENT( ssn, num-credits, name, address)
• FT-STUDENT( ssn, major, name, address)

33
Aggregation

• make table, with primary keys of all involved
  entities

34
Overview

• concepts
• Entities
• Relationships
• Attributes
• Specialization/Generalization
• Aggregation
• turning E-R diagrams to tables

35
Summary

• E-R Diagrams a powerful, user-friendly tool for
  data modeling
• Entities (strong, weak)
• Attributes (primary keys, discriminators,
  derived, multivalued)
• Relationships (11, 1N, NM multi-way)
• Generalization/Specialization Aggregation

36
Summary - contd
multivalued attribute
derived attribute
37
Summary - contd
(e.t.c. - see book for alternative notations)
38
Conclusions

• E-R Diagrams a powerful, user-friendly tool for
  data modeling.