Problem Carnegie Mellon Univ. Dept. of Computer Science Develop an - - PowerPoint PPT Presentation

problem carnegie mellon univ dept of computer science
SMART_READER_LITE
LIVE PREVIEW

Problem Carnegie Mellon Univ. Dept. of Computer Science Develop an - - PowerPoint PPT Presentation

Faloutsos - Pavlo CMU SCS 15-415/615 CMU SCS CMU SCS Problem Carnegie Mellon Univ. Dept. of Computer Science Develop an application for U.G. admin: 15-415/615 - DB Applications Student info Who-takes-what class Class rosters C.


slide-1
SLIDE 1

Faloutsos - Pavlo CMU SCS 15-415/615 1

CMU SCS

Carnegie Mellon Univ.

  • Dept. of Computer Science

15-415/615 - DB Applications

  • C. Faloutsos - A. Pavlo

Lecture#2: E-R diagrams

CMU SCS

Problem

  • Develop an application for U.G. admin:

– Student info – Who-takes-what class – Class rosters – Transcripts

  • How do you proceed?

– (Which role(s) are you playing?)

Faloutsos - Pavlo CMU SCS 15-415/615 2

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 3

Database Design

  • Requirements Analysis
  • Conceptual Design
  • Logical Design
  • Schema Refinement
  • Physical Design
  • Security Design

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 4

Database Design

  • Requirements Analysis
  • Conceptual Design
  • Logical Design
  • Schema Refinement
  • Physical Design
  • Security Design

user’s needs high level (ER) Tables Normalization Indices etc Access controls

slide-2
SLIDE 2

Faloutsos - Pavlo CMU SCS 15-415/615 2

CMU SCS

Problem’

  • Develop an application for U.G. admin:

– Student info – Who-takes-what class – Class rosters – Transcripts

  • If you are the *new* DBA, what would you

rather inherit:

Faloutsos - Pavlo CMU SCS 15-415/615 5

Maintain

CMU SCS

This or this ?

drop table if exists student; create table student (ssn fixed, name char(20)); drop table if exists takes; create table takes (ssn fixed, cid char(10), grade fixed);

Faloutsos - Pavlo CMU SCS 15-415/615 6

Student Course Takes

CMU SCS

True story

  • Health insurance company
  • Wants to catch (some of the abundant) fraud
  • Schema:

– patients, visit doctors, get medicine, – Doctors perform operations, … – Nurses monitor patients, … – etc etc

  • Q: How many tables do you think it spans?

Faloutsos - Pavlo CMU SCS 15-415/615 7

CMU SCS

True story

  • Schema:

– patients, visit doctors, get medicine, – Doctors perform operations, … – Nurses monitor patients, … – etc etc

  • Q: How many tables do you think it spans?

10? 20? 30?

Faloutsos - Pavlo CMU SCS 15-415/615 8

slide-3
SLIDE 3

Faloutsos - Pavlo CMU SCS 15-415/615 3

CMU SCS

True story

  • Schema:

– patients, visit doctors, get medicine, – Doctors perform operations, … – Nurses monitor patients, … – etc etc

  • Q: How many tables do you think it spans?

10? 20? 30?

  • A: 120 PAGES of schema

Faloutsos - Pavlo CMU SCS 15-415/615 9

CMU SCS

Motivation & upcoming conclusion:

  • E-R diagrams are

excellent documentation tools

Faloutsos - Pavlo CMU SCS 15-415/615 10

Student Course Takes

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 11

Overview

  • concepts

– Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 12

Tools

Entities (‘entity sets’) Relationships (‘rel. sets’) and mapping constraints attributes

N M P

Student Course Takes

slide-4
SLIDE 4

Faloutsos - Pavlo CMU SCS 15-415/615 4

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 13

Example

Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 14

Example

Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 15

Example

Students, taking courses, offered by instructors; a course may have multiple sections; one instructor per section nouns -> entity sets verbs -> relationship sets

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 16

STUDENT name ssn ... INSTRUCTOR issn primary key = unique identifier -> underline

slide-5
SLIDE 5

Faloutsos - Pavlo CMU SCS 15-415/615 5

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 17

STUDENT name ssn ... COURSE c-id c-name but: sections of course (with different instructors)? INSTRUCTOR issn

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 18

COURSE c-id INSTRUCTOR issn STUDENT ssn SECTION s-id but: s-id is not unique... (see later)

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 19

COURSE c-id INSTRUCTOR issn STUDENT ssn SECTION s-id Q: how to record that students take courses?

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 20

COURSE c-id INSTRUCTOR issn STUDENT ssn SECTION s-id takes N M

slide-6
SLIDE 6

Faloutsos - Pavlo CMU SCS 15-415/615 6

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 21

COURSE c-id INSTRUCTOR STUDENT SECTION s-id takes N M N 1 teaches

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 22

COURSE c-id INSTRUCTOR STUDENT SECTION s-id takes N M N 1 has N 1 teaches

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 23

Cardinalities

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

1 1 1 N N M

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 24

Cardinalities

1 1 1 N N M

COUNTRY

CAPITAL PERSON STUDENT SECTION has CAR

  • wns

takes

slide-7
SLIDE 7

Faloutsos - Pavlo CMU SCS 15-415/615 7

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 25

Cardinalities

PERSON STUDENT SECTION CAR

  • wns

takes

Book’s notation:

COUNTRY

has CAPITAL

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 26

Cardinalities

1 1 1 N N M PERSON STUDENT SECTION CAR

  • wns

takes

Book’s notation vs 1 to N notation

CAPITAL

COUNTRY

has

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 27

Cardinalities

1 1 1 N N M PERSON STUDENT SECTION CAR

  • wns

takes CAPITAL

COUNTRY

has

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 28

‘Total/partial’ participation

1:1 1:1 ?:1 ?:N ?:N ?:M PERSON STUDENT SECTION CAR

  • wns

takes total, total ?? ??

COUNTRY

has CAPITAL

slide-8
SLIDE 8

Faloutsos - Pavlo CMU SCS 15-415/615 8

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 29

‘Total/partial’ participation

1:1 1:1 1:1 0:N ?:N ?:M PERSON STUDENT SECTION CAR

  • wns

takes total, total ?? partial, total

COUNTRY

has CAPITAL

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 30

‘Total/partial’ participation

1:1 1:1 1:1 0:N 1:N 0:M PERSON STUDENT SECTION CAR

  • wns

takes total, total partial, total partial, total

COUNTRY

has CAPITAL

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 31

‘Total/partial’ participation

1:1 0:N PERSON CAR

  • wns

partial, total Is it ‘legal’?

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 32

‘Total/partial’ participation

1:1 0:N PERSON CAR

  • wns

partial, total Is it ‘legal’? NO! why not?

slide-9
SLIDE 9

Faloutsos - Pavlo CMU SCS 15-415/615 9

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 33

Subtle concept: Weak entities

  • ‘section’ has no unique-id of its own!(?)

COURSE c-id SECTION s-id has N 1

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 34

Weak entities

  • ‘weak’ entities: if they need to borrow a unique id

from a ‘strong entity - thick box.

  • ‘c-id’ + ‘s-id’: unique id for SECTION
  • partial key (eg., ‘s-id’) - dashed-underline
  • identifying relationship (eg., ‘has’)

COURSE c-id SECTION s-id has N 1

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 35

Weak entities

  • Other example(s) of weak entities?

??? ??id ??? ?? ?? N 1

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 36

Weak entities

  • Other example(s) of weak entities?

Employee e-id Dependent name has N 1

slide-10
SLIDE 10

Faloutsos - Pavlo CMU SCS 15-415/615 10

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 37

More details

  • self-relationships - example?

?? ?? ?? ??

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 38

More details

  • self-relationships - example?

EMPLOYEE manages 1 N

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 39

More details

  • self-relationships - example?

FB user Has-friend ?? ??

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 40

More details

  • 3-way and k-way relationships?
slide-11
SLIDE 11

Faloutsos - Pavlo CMU SCS 15-415/615 11

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 41

More details

  • 3-way and k-way relationships? Rare, but

possible:

EMPLOYEE uses PROJECT TOOL N M P

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 42

More details

  • 3-way and k-way relationships? Rare, but

possible:

?? ?? ?? ?? N M P Other cases?

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 43

More details

  • 3-way and k-way relationships? Rare, but

possible:

user reviews app keyword N M P App-store/amazon reviews

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 44

Overview

  • concepts

– Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions

slide-12
SLIDE 12

Faloutsos - Pavlo CMU SCS 15-415/615 12

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 45

More details - attributes

  • key (or primary key): unique identifier
  • underlined, in the ER diagram
  • [not in textbook - FYI:

– multivalued or set-valued attributes (eg., ‘dependents’ for EMPLOYEE) – derived attributes (eg., 15% tip) ]

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 46

Overview

  • concepts

– Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions

Basic Advanced/ rare

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 47

Specialization

  • eg., students: part

time (#credit- hours) and full time (major)

STUDENT name ssn PT-STUDENT #credits FT-STUDENT major IS-A

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 48

Observations

  • Generalization: exact reverse of

‘specialization’

  • attribute inheritance
  • could have many levels of an IS-A

hierarchy

slide-13
SLIDE 13

Faloutsos - Pavlo CMU SCS 15-415/615 13

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 49

More details

  • Overlap constraints
  • Covering constraints

A B C

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 50

More details

  • Overlap constraints

– can an entity belong to both ‘B’ and ‘C’?

  • Covering constraints

– can an ‘A’ entity belong to neither ‘B’ nor ‘C’?

A B C

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 51

More details

  • Overlap constraints -

examples?

A B C A B C No overlap A B C with overlap

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 52

More details

  • Covering constraints -

examples?

A B C A B C Total coverage A B C Partial coverage

slide-14
SLIDE 14

Faloutsos - Pavlo CMU SCS 15-415/615 14

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 53

Overview

  • concepts

– Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 54

Aggregation

CPU HD

  • computer model (w/ CPU and HD)
  • and Maker (eg., Dell, HP)

N M MAKER ?

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 55

Aggregation

  • treat a relationship as an entity
  • used to express a relationship among

relationships

CPU HD N M MAKER

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 56

Overview

  • concepts

– Entities – Relationships – Attributes – Specialization/Generalization – Aggregation – ER modeling questions

slide-15
SLIDE 15

Faloutsos - Pavlo CMU SCS 15-415/615 15

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 57

Conceptual design

  • Entity vs attribute
  • Entity vs relationship
  • Binary or ternary relationships?
  • Aggregation?

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 58

Entity vs. attribute

  • Entity EMPLOYEE (w/ emp#, name,

job_code, ...)

  • Q: How about ‘spouse’ - entity or attribute?
  • Q: How about ‘dependents’?

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 59

Entity vs. attribute

  • Entity EMPLOYEE (w/ emp#, name,

job_code, ...)

  • Q: How about ‘spouse’ - entity or attribute?
  • A: probably, ‘attribute’ is enough
  • Q: How about ‘dependents’?
  • A: Entity - we may have many dependents

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 60

Entity vs. Relationship

STUDENT SECTION takes N M STUDENT SECTION TAKES 1 N 1 N OR

slide-16
SLIDE 16

Faloutsos - Pavlo CMU SCS 15-415/615 16

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 61

Binary vs Ternary Relationships

  • usually, binary relationships are ‘cleaner’:

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 62

Binary vs. Ternary Relationships

If each policy is

  • wned by just 1

employee:

Policies policyid cost age pname Dependents Covers name Employees ssn lot

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 63

Binary vs. Ternary Relationships

If each policy is

  • wned by just 1

employee:

Bad design

Policies policyid cost age pname Dependents Covers name Employees ssn lot

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 64

Binary vs. Ternary Relationships

If each policy is

  • wned by just 1

employee:

Bad design

Policies policyid cost age pname Dependents Covers name Employees ssn lot

Key constraint on

Policies would mean policy can only cover 1 dependent!

slide-17
SLIDE 17

Faloutsos - Pavlo CMU SCS 15-415/615 17

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 65

Binary vs. Ternary Relationships

If each policy is

  • wned by just 1

employee:

Bad design

Beneficiary age pname Dependents policyid cost Policies Purchaser name Employees ssn lot

Better design What are the additional constraints in the 2nd diagram?

Policies policyid cost age pname Dependents Covers name Employees ssn lot

Key constraint on

Policies would mean policy can only cover 1 dependent!

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 66

Binary vs Ternary Rel.

  • But sometimes ternary rel. can not be

replaced by a set of binary rel’s:

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 67

Binary vs. Ternary Relationships (Contd.)

Suppliers qty Departments Contract Parts Suppliers Departments deals-with Parts can-supply

VS. why is it bad?

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 68

Binary vs. Ternary Relationships (Contd.)

– S “can-supply” P, D “needs” P, and D “deals-with” S does not imply that D has agreed to buy P from S. – How do we record qty?

Suppliers qty Departments Contract Parts Suppliers Departments deals-with Parts can-supply

VS.

slide-18
SLIDE 18

Faloutsos - Pavlo CMU SCS 15-415/615 18

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 69

Binary vs. Ternary Relationships (Contd.)

Suppliers qty Departments Contract Parts Suppliers qty Departments Contract Parts

Not in textbook: in practice, often:

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 70

Binary vs. Ternary Relationships (Contd.)

Suppliers qty Departments Contract Parts c-id

Not in textbook: in practice, often:

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 71

Binary vs. Ternary Relationships (Contd.)

Suppliers qty Departments Contract Parts c-id

Not in textbook: in practice, often: N M 1 N 1 N

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 72

Ternary vs. aggregation

  • use aggregation, if we want to attach a

relationship to a relationship

  • (see book for example)
  • (in practice, again we create a unique-id and

resort to binary relationships)

slide-19
SLIDE 19

Faloutsos - Pavlo CMU SCS 15-415/615 19

CMU SCS

Ternary vs. aggregation

  • How would you handle this case?

Faloutsos - Pavlo CMU SCS 15-415/615 73

CPU HD N M MAKER

CMU SCS

Ternary vs. aggregation

  • How would you handle this case?

Faloutsos - Pavlo CMU SCS 15-415/615 74

CPU HD MAKER

  • COMP. MODEL

CMU SCS

Ternary vs. aggregation

  • How would you handle this case?

Faloutsos - Pavlo CMU SCS 15-415/615 75

CPU HD ? ? MAKER

  • COMP. MODEL

? ?

CMU SCS

Ternary vs. aggregation

  • How would you handle this case?

Faloutsos - Pavlo CMU SCS 15-415/615 76

CPU HD N M MAKER

  • COMP. MODEL

N M HAS_CPU

slide-20
SLIDE 20

Faloutsos - Pavlo CMU SCS 15-415/615 20

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 77

Summary

  • E-R Diagrams: a powerful, user-friendly

tool for data modeling:

– Entities (strong, weak) – Attributes (primary keys, discriminators, derived, multivalued) – Relationships (1:1, 1:N, N:M; multi-way) – Generalization/Specialization; Aggregation

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 78

Summary

  • E-R Diagrams: a powerful, user-friendly

tool for data modeling:

– Entities (strong, weak) – Attributes (primary keys, discriminators, derived, multivalued) – Relationships (1:1, 1:N, N:M; multi-way) – Generalization/Specialization; Aggregation

POPULAR

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 79

Summary - cont’d

(strong) entity set weak entity set relationship set identifying rel. set for weak entity attribute primary key A partial key A

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 80

Summary - cont’d

cardinalities N M cardinalities with limits l:h l’:h’ cardinalities partial/total (not in textbook - FYI)

slide-21
SLIDE 21

Faloutsos - Pavlo CMU SCS 15-415/615 21

CMU SCS

Faloutsos - Pavlo CMU SCS 15-415/615 81

Summary - cont’d

IS-A aggregation