CS340 · Chapter 2 · Annotated SlidesConcepts, Architecture and Conceptual Design (ER Model)
Original
Overview

Concepts, Architecture and Conceptual Design (ER Model)

Levels of abstraction, schemas and instances, logical and physical data independence, then the ER model: entities and attribute types, keys, relationships, degree and cardinality ratios, total vs. partial participation, recursive relationships, weak entities, (min, max) notation and ternary relationships.

How to read this page

Every original slide is shown unchanged. Yellow marks on a slide point to a numbered note: hover or tap a mark to jump to its note, and hover a note to see its marks. Questions tagged Past exam appeared on a real paper; Prediction questions are likely angles, not confirmed. Pick an answer to each question: a wrong pick tells you why it is wrong, so you can try again.

All 69 slides
  1. 1 Conceptual Design (Entity–Relationship Model)
  2. 2 DATABASES SO FAR
  3. 3 LEARNING GOALS
  4. 4 PRELIMINARIES
  5. 5 Data abstraction
  6. 6 LEVELS OF ABSTRACTION
  7. 7 SCHEMAS AND INSTANCES
  8. 8 A database that stores student and course information.
  9. 9 SCHEMAS
  10. 10 DATA INDEPENDENCE
  11. 11 DATA INDEPENDENCE
  12. 12 DATA INDEPENDENCE
  13. 13 CONCEPTUAL DESIGN
  14. 14 ER COMPONENTS: ENTITIES AND ATTRIBUTES
  15. 15 ER COMPONENTS: ATTRIBUTE TYPES
  16. 16 EXAMPLE OF A COMPOSITE ATTRIBUTE
  17. 17 TYPES OF ATTRIBUTES: MULTI-VALUED
  18. 18 TYPES OF ATTRIBUTES: STORED AND DERIVED
  19. 19 TYPES OF ATTRIBUTES: COMPLEX ATTRIBUTES
  20. 20 ER COMPONENTS: KEYS
  21. 21 ER COMPONENTS: KEYS
  22. 22 MODELING ENTITIES IN ER DIAGRAMS
  23. 23 Slide 23
  24. 24 EXAMPLE: CAR ENTITY AND CAR ENTITY SET
  25. 25 ACTIVITY
  26. 26 EXAMPLE: COMPANY DATABASE
  27. 27 EXAMPLE COMPANY DATABASE (CONTINUED)
  28. 28 INITIAL CONCEPTUAL DESIGN OF ENTITY TYPES FOR THE COMPANY DATABASE SCHEMA
  29. 29 INITIAL DESIGN OF ENTITY TYPES
  30. 30 INITIAL DESIGN OF ENTITY TYPES
  31. 31 ER COMPONENTS: RELATIONSHIPS
  32. 32 RELATIONSHIP TYPE AND RELATIONSHIP SET
  33. 33 RELATIONSHIP DEGREES OR ARITY
  34. 34 RELATIONSHIP EXAMPLES
  35. 35 RELATIONSHIP CARDINALITY CONSTRAINTS
  36. 36 ONE TO ONE (1:1) RELATIONSHIPS
  37. 37 MANY TO ONE (N:1) RELATIONSHIPS
  38. 38 MANY TO MANY (M:N) RELATIONSHIPS
  39. 39 OTHER CONSTRAINTS ON RELATIONSHIPS
  40. 40 TOTAL AND PARTIAL PARTICIPATION
  41. 41 RECURSIVE RELATIONSHIP TYPE
  42. 42 DISPLAYING A RECURSIVE (UNARY) RELATIONSHIP
  43. 43 RECURSIVE (UNARY) RELATIONSHIP
  44. 44 DISCUSSION ON RELATIONSHIP TYPES
  45. 45 WEAK ENTITY TYPES
  46. 46 WEAK ENTITY TYPE
  47. 47 ATTRIBUTES OF RELATIONSHIP TYPES
  48. 48 REFINING THE COMPANY DATABASE SCHEMA BY INTRODUCING RELATIONSHIPS
  49. 49 Slide 49
  50. 50 many to one
  51. 51 ER DIAGRAM CONVENTIONS
  52. 52 ACTIVITY
  53. 53 CASE STUDY (SBS BANK)
  54. 54 ONE SOLUTION
  55. 55 ALTERNATIVE (MIN, MAX) NOTATION FOR RELATIONSHIP STRUCTURAL CONSTRAINTS:
  56. 56 THE (MIN,MAX) NOTATION FOR RELATIONSHIP CONSTRAINTS
  57. 57 COMPANY ER SCHEMA DIAGRAM USING
  58. 58 SUMMARY OF
  59. 59 RELATIONSHIPS OF HIGHER DEGREE
  60. 60 TERNARY RELATIONSHIPS
  61. 61 ALTERNATIVES TO
  62. 62 DISCUSSION OF N-ARY RELATIONSHIPS (N > 2)
  63. 63 DISCUSSION OF N-ARY RELATIONSHIPS (N > 2)
  64. 64 ANOTHER EXAMPLE OF A TERNARY RELATIONSHIP
  65. 65 DISPLAYING CONSTRAINTS ON HIGHER-DEGREE RELATIONSHIPS
  66. 66 ANOTHER EXAMPLE: A UNIVERSITY DATABASE
  67. 67 UNIVERSITY
  68. 68 CHAPTER SUMMARY
  69. 69 EXTENDED ENTITY-RELATIONSHIP (EER) MODEL (IN THE NEXT CHAPTER)
Slide 1 / 69

Conceptual Design (Entity–Relationship Model)

Slide 1: Conceptual Design (Entity–Relationship Model)
Slide 2 / 69

DATABASES SO FAR

Slide 2: DATABASES SO FAR
Slide 3 / 69

LEARNING GOALS

Slide 3: LEARNING GOALS
Slide 4 / 69

PRELIMINARIES

Slide 4: PRELIMINARIES

Notes

  1. Three levels: physical (how data is stored), logical / conceptual (what data and relationships), view (part of the database for one user group).

Slide 5 / 69

Data abstraction

Slide 5: Data abstraction
Slide 6 / 69

LEVELS OF ABSTRACTION

Slide 6: LEVELS OF ABSTRACTION

Notes

  1. Conceptual level hides the storage details and describes the whole database’s entities, data types, relationships and constraints.

Exam angle

PredictionQ1

What is TRUE about the conceptual level?

Slide 7 / 69

SCHEMAS AND INSTANCES

Slide 7: SCHEMAS AND INSTANCES

Exam angle

PredictionQ1

The database schema changes every time a row is inserted.

Slide 8 / 69

A database that stores student and course information.

Slide 8: A database that stores student and course information.
Slide 9 / 69

SCHEMAS

Slide 9: SCHEMAS
Slide 10 / 69

DATA INDEPENDENCE

Slide 10: DATA INDEPENDENCE

Notes

  1. Data independence: change the schema at one level without changing the next higher level.

Exam angle

Past exam · Midterm exam

Q3: changing structures without breaking programs

PredictionQ1

Which lets you modify data structures without affecting existing programs?

Slide 11 / 69

DATA INDEPENDENCE

Slide 11: DATA INDEPENDENCE

Notes

  1. Logical data independence: change the conceptual schema (add a column) without changing external views/apps. Harder to achieve.

Slide 12 / 69

DATA INDEPENDENCE

Slide 12: DATA INDEPENDENCE

Exam angle

PredictionQ1

Adding an index to speed up a query without changing the tables is an example of:

Slide 13 / 69

CONCEPTUAL DESIGN

Slide 13: CONCEPTUAL DESIGN
Slide 14 / 69

ER COMPONENTS: ENTITIES AND ATTRIBUTES

Slide 14: ER COMPONENTS: ENTITIES AND ATTRIBUTES
Slide 15 / 69

ER COMPONENTS: ATTRIBUTE TYPES

Slide 15: ER COMPONENTS: ATTRIBUTE TYPES

Notes

  1. Attribute types: simple vs. composite, single- vs. multi-valued, stored vs. derived, complex.

Slide 16 / 69

EXAMPLE OF A COMPOSITE ATTRIBUTE

Slide 16: EXAMPLE OF A COMPOSITE ATTRIBUTE
Slide 17 / 69

TYPES OF ATTRIBUTES: MULTI-VALUED

Slide 17: TYPES OF ATTRIBUTES: MULTI-VALUED

Notes

  1. Multi-valued attribute (double oval): several values, e.g. Colors of a car, Degrees.

Slide 18 / 69

TYPES OF ATTRIBUTES: STORED AND DERIVED

Slide 18: TYPES OF ATTRIBUTES: STORED AND DERIVED

Notes

  1. Derived attribute (dashed oval): computed from another, e.g. Age from BirthDate.

Exam angle

PredictionQ1

Age computed from BirthDate is a:

Slide 19 / 69

TYPES OF ATTRIBUTES: COMPLEX ATTRIBUTES

Slide 19: TYPES OF ATTRIBUTES: COMPLEX ATTRIBUTES
Slide 20 / 69

ER COMPONENTS: KEYS

Slide 20: ER COMPONENTS: KEYS

Notes

  1. Key attribute (underlined): unique for every entity in the set. An entity type may have several keys.

Slide 21 / 69

ER COMPONENTS: KEYS

Slide 21: ER COMPONENTS: KEYS
Slide 22 / 69

MODELING ENTITIES IN ER DIAGRAMS

Slide 22: MODELING ENTITIES IN ER DIAGRAMS
Slide 23 / 69

Slide 23

Slide 23: Slide 23
Slide 24 / 69

EXAMPLE: CAR ENTITY AND CAR ENTITY SET

Slide 24: EXAMPLE: CAR ENTITY AND CAR ENTITY SET
Slide 25 / 69

ACTIVITY

Slide 25: ACTIVITY
Slide 26 / 69

EXAMPLE: COMPANY DATABASE

Slide 26: EXAMPLE: COMPANY DATABASE
Slide 27 / 69

EXAMPLE COMPANY DATABASE (CONTINUED)

Slide 27: EXAMPLE COMPANY DATABASE (CONTINUED)
Slide 28 / 69

INITIAL CONCEPTUAL DESIGN OF ENTITY TYPES FOR THE COMPANY DATABASE SCHEMA

Slide 28: INITIAL CONCEPTUAL DESIGN OF ENTITY TYPES FOR THE COMPANY DATABASE SCHEMA
Slide 29 / 69

INITIAL DESIGN OF ENTITY TYPES

Slide 29: INITIAL DESIGN OF ENTITY TYPES
Slide 30 / 69

INITIAL DESIGN OF ENTITY TYPES

Slide 30: INITIAL DESIGN OF ENTITY TYPES
Slide 31 / 69

ER COMPONENTS: RELATIONSHIPS

Slide 31: ER COMPONENTS: RELATIONSHIPS
Slide 32 / 69

RELATIONSHIP TYPE AND RELATIONSHIP SET

Slide 32: RELATIONSHIP TYPE AND RELATIONSHIP SET
Slide 33 / 69

RELATIONSHIP DEGREES OR ARITY

Slide 33: RELATIONSHIP DEGREES OR ARITY
Slide 34 / 69

RELATIONSHIP EXAMPLES

Slide 34: RELATIONSHIP EXAMPLES
Slide 35 / 69

RELATIONSHIP CARDINALITY CONSTRAINTS

Slide 35: RELATIONSHIP CARDINALITY CONSTRAINTS

Notes

  1. Cardinality ratio: 1:1, 1:N, M:N, the max number of relationship instances an entity can join.

Exam angle

PredictionQ1

Students take many courses and courses have many students. The cardinality is:

Slide 36 / 69

ONE TO ONE (1:1) RELATIONSHIPS

Slide 36: ONE TO ONE (1:1) RELATIONSHIPS
Slide 37 / 69

MANY TO ONE (N:1) RELATIONSHIPS

Slide 37: MANY TO ONE (N:1) RELATIONSHIPS
Slide 38 / 69

MANY TO MANY (M:N) RELATIONSHIPS

Slide 38: MANY TO MANY (M:N) RELATIONSHIPS
Slide 39 / 69

OTHER CONSTRAINTS ON RELATIONSHIPS

Slide 39: OTHER CONSTRAINTS ON RELATIONSHIPS
Slide 40 / 69

TOTAL AND PARTIAL PARTICIPATION

Slide 40: TOTAL AND PARTIAL PARTICIPATION

Notes

  1. Total participation (double line): every entity must take part (existence dependency). Partial (single line): some may not.

Exam angle

PredictionQ1

"Every department must have a manager" is shown with:

Slide 41 / 69

RECURSIVE RELATIONSHIP TYPE

Slide 41: RECURSIVE RELATIONSHIP TYPE
Slide 42 / 69

DISPLAYING A RECURSIVE (UNARY) RELATIONSHIP

Slide 42: DISPLAYING A RECURSIVE (UNARY) RELATIONSHIP
Slide 43 / 69

RECURSIVE (UNARY) RELATIONSHIP

Slide 43: RECURSIVE (UNARY) RELATIONSHIP
Slide 44 / 69

DISCUSSION ON RELATIONSHIP TYPES

Slide 44: DISCUSSION ON RELATIONSHIP TYPES
Slide 45 / 69

WEAK ENTITY TYPES

Slide 45: WEAK ENTITY TYPES

Notes

  1. Weak entity (double rectangle): no key of its own; identified by its owner through an identifying relationship (double diamond) + its partial key (dashed underline).

  2. Trap A weak entity ALWAYS has total participation in its identifying relationship.

Exam angle

PredictionQ1

A weak entity type always has ____ participation in its identifying relationship.

Slide 46 / 69

WEAK ENTITY TYPE

Slide 46: WEAK ENTITY TYPE
Slide 47 / 69

ATTRIBUTES OF RELATIONSHIP TYPES

Slide 47: ATTRIBUTES OF RELATIONSHIP TYPES
Slide 48 / 69

REFINING THE COMPANY DATABASE SCHEMA BY INTRODUCING RELATIONSHIPS

Slide 48: REFINING THE COMPANY DATABASE SCHEMA BY INTRODUCING RELATIONSHIPS
Slide 49 / 69

Slide 49

Slide 49: Slide 49
Slide 50 / 69

many to one

Slide 50: many to one
Slide 51 / 69

ER DIAGRAM CONVENTIONS

Slide 51: ER DIAGRAM CONVENTIONS
Slide 52 / 69

ACTIVITY

Slide 52: ACTIVITY
Slide 53 / 69

CASE STUDY (SBS BANK)

Slide 53: CASE STUDY (SBS BANK)
Slide 54 / 69

ONE SOLUTION

Slide 54: ONE SOLUTION
Slide 55 / 69

ALTERNATIVE (MIN, MAX) NOTATION FOR RELATIONSHIP STRUCTURAL CONSTRAINTS:

Slide 55: ALTERNATIVE (MIN, MAX) NOTATION FOR RELATIONSHIP STRUCTURAL CONSTRAINTS:

Notes

  1. (min, max) notation: each entity takes part in at least min and at most max instances; min = 0 → partial, min ≥ 1 → total.

Exam angle

PredictionQ1

(1, N) next to EMPLOYEE on WORKS_FOR means each employee works for:

Slide 56 / 69

THE (MIN,MAX) NOTATION FOR RELATIONSHIP CONSTRAINTS

Slide 56: THE (MIN,MAX) NOTATION FOR RELATIONSHIP CONSTRAINTS
Slide 57 / 69

COMPANY ER SCHEMA DIAGRAM USING

Slide 57: COMPANY ER SCHEMA DIAGRAM USING
Slide 58 / 69

SUMMARY OF

Slide 58: SUMMARY OF
Slide 59 / 69

RELATIONSHIPS OF HIGHER DEGREE

Slide 59: RELATIONSHIPS OF HIGHER DEGREE
Slide 60 / 69

TERNARY RELATIONSHIPS

Slide 60: TERNARY RELATIONSHIPS

Notes

  1. Ternary relationship: three entity types at once (SUPPLIER supplies PART to PROJECT). Not equivalent to three binary ones.

Slide 61 / 69

ALTERNATIVES TO

Slide 61: ALTERNATIVES TO
Slide 62 / 69

DISCUSSION OF N-ARY RELATIONSHIPS (N > 2)

Slide 62: DISCUSSION OF N-ARY RELATIONSHIPS (N > 2)
Slide 63 / 69

DISCUSSION OF N-ARY RELATIONSHIPS (N > 2)

Slide 63: DISCUSSION OF N-ARY RELATIONSHIPS (N > 2)
Slide 64 / 69

ANOTHER EXAMPLE OF A TERNARY RELATIONSHIP

Slide 64: ANOTHER EXAMPLE OF A TERNARY RELATIONSHIP
Slide 65 / 69

DISPLAYING CONSTRAINTS ON HIGHER-DEGREE RELATIONSHIPS

Slide 65: DISPLAYING CONSTRAINTS ON HIGHER-DEGREE RELATIONSHIPS
Slide 66 / 69

ANOTHER EXAMPLE: A UNIVERSITY DATABASE

Slide 66: ANOTHER EXAMPLE: A UNIVERSITY DATABASE
Slide 67 / 69

UNIVERSITY

Slide 67: UNIVERSITY
Slide 68 / 69

CHAPTER SUMMARY

Slide 68: CHAPTER SUMMARY
Slide 69 / 69

EXTENDED ENTITY-RELATIONSHIP (EER) MODEL (IN THE NEXT CHAPTER)

Slide 69: EXTENDED ENTITY-RELATIONSHIP (EER) MODEL (IN THE NEXT CHAPTER)