CS340 · Chapter 5 · Annotated SlidesER-to-Relational Mapping and DDL
Original
Overview

ER-to-Relational Mapping and DDL

The 7-step ER-to-relational mapping algorithm (strong and weak entities, 1:1, 1:N and M:N relationships, multivalued attributes, n-ary relationships), basic DDL (CREATE TABLE, keys, foreign keys, data types, constraints), and step 8 options for mapping specialization.

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 76 slides
  1. 1 ER-to-Relational Mapping and DDL
  2. 2 LEARNING GOALS
  3. 3 GOALS DURING MAPPING
  4. 4 Slide 4
  5. 5 Slide 5
  6. 6 ER-TO-RELATIONAL MAPPING ALGORITHM
  7. 7 ER-TO-RELATIONAL MAPPING ALGORITHM
  8. 8 strong
  9. 9 REGULAR ENTITY TYPES WITH COMPOSITE ATTRIBUTES
  10. 10 REGULAR ENTITY TYPES (EMPLOYEE)
  11. 11 REGULAR ENTITY TYPES (DEPARTMENT)
  12. 12 REGULAR ENTITY TYPES (PROJECT)
  13. 13 ER-TO-RELATIONAL MAPPING ALGORITHM 595
  14. 14 ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
  15. 15 WEAK ENTITY TYPES
  16. 16 DDL BASICS
  17. 17 DDL BASICS (PRIMARY KEY)
  18. 18 DDL BASICS (COMPOSITE KEYS AND NAMING CONSTRAINTS)
  19. 19 DDL BASICS (FOREIGN KEYS)
  20. 20 ACTIVITY
  21. 21 ACTIVITY (SOLUTION)
  22. 22 A BIT MORE DDL
  23. 23 SOME SQL DATA TYPES (MYSQL)
  24. 24 DDL CONSTRAINTS
  25. 25 ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
  26. 26 MAPPING OF BINARY 1:1 RELATIONSHIP TYPES
  27. 27 1:1 FOREIGN KEY APPROACH
  28. 28 1:1 FOREIGN KEY APPROACH
  29. 29 1:1 FOREIGN KEY APPROACH
  30. 30 MAPPING OF BINARY 1:1 RELATIONSHIP TYPES
  31. 31 1:1 MERGED RELATION APPROACH
  32. 32 MAPPING OF BINARY 1:1 RELATIONSHIP TYPES
  33. 33 1:1 RELATIONSHIP RELATION APPROACH
  34. 34 ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
  35. 35 1:N FOREIGN KEY APPROACH
  36. 36 1:N FOREIGN KEY APPROACH
  37. 37 ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
  38. 38 N:M MAPPING TO RELATIONAL
  39. 39 ACTIVITY
  40. 40 ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
  41. 41 MAPPING MULTI-VALUED ATTRIBUTES
  42. 42 ACTIVITY
  43. 43 ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
  44. 44 MAPPING OF N-ARY RELATIONSHIPS
  45. 45 MAPPING OF N-ARY RELATIONSHIPS
  46. 46 UNARY RELATIONSHIPS
  47. 47 1:N UNARY RELATIONSHIPS
  48. 48 1:N UNARY RELATIONSHIPS (PARTIAL BOTH SIDES)
  49. 49 M:N UNARY RELATIONSHIPS
  50. 50 1:N UNARY RELATIONSHIPS (PARTIAL ONE SIDE)
  51. 51 SUMMARY OF MAPPING CONSTRUCTS AND CONSTRAINTS
  52. 52 ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
  53. 53 MAPPING EER MODEL CONSTRUCTS TO RELATIONS
  54. 54 MAPPING EER MODEL CONSTRUCTS TO RELATIONS
  55. 55 EXAMPLE: EMPLOYEE AND JOB TYPE
  56. 56 OPTION 8A: MULTIPLE RELATIONS-SUPERCLASS AND SUBCLASSES
  57. 57 OPTION 8C: EXTRA ATTRIBUTES
  58. 58 EXAMPLE: VEHICLE, CAR OR TRUCK
  59. 59 OPTION 8B: SUB-CLASSES ONLY
  60. 60 EXAMPLE: PART ENTITY
  61. 61 OPTION 8D: BOOLEAN FLAGS
  62. 62 DIFFERENT OPTIONS FOR MAPPING
  63. 63 Slide 63
  64. 64 MAPPING OTHER EER MODEL CONSTRUCTS TO RELATIONS
  65. 65 Slide 65
  66. 66 RELATIONAL MAPPING
  67. 67 ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
  68. 68 Slide 68
  69. 69 Slide 69
  70. 70 Slide 70
  71. 71 ACTIVIT Y
  72. 72 SOLUTION
  73. 73 SOLUTION
  74. 74 FINAL SOLUTION
  75. 75 ACTIVIT Y
  76. 76 CHAPTER SUMMARY
Slide 1 / 76

ER-to-Relational Mapping and DDL

Slide 1: ER-to-Relational Mapping and DDL
Slide 2 / 76

LEARNING GOALS

Slide 2: LEARNING GOALS
Slide 3 / 76

GOALS DURING MAPPING

Slide 3: GOALS DURING MAPPING
Slide 4 / 76

Slide 4

Slide 4: Slide 4
Slide 5 / 76

Slide 5

Slide 5: Slide 5
Slide 6 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM

Slide 6: ER-TO-RELATIONAL MAPPING ALGORITHM
Slide 7 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM

Slide 7: ER-TO-RELATIONAL MAPPING ALGORITHM
Slide 8 / 76

strong

Slide 8: strong
Slide 9 / 76

REGULAR ENTITY TYPES WITH COMPOSITE ATTRIBUTES

Slide 9: REGULAR ENTITY TYPES WITH COMPOSITE ATTRIBUTES
Slide 10 / 76

REGULAR ENTITY TYPES (EMPLOYEE)

Slide 10: REGULAR ENTITY TYPES (EMPLOYEE)
Slide 11 / 76

REGULAR ENTITY TYPES (DEPARTMENT)

Slide 11: REGULAR ENTITY TYPES (DEPARTMENT)
Slide 12 / 76

REGULAR ENTITY TYPES (PROJECT)

Slide 12: REGULAR ENTITY TYPES (PROJECT)
Slide 13 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM 595

Slide 13: ER-TO-RELATIONAL MAPPING ALGORITHM 595
Slide 14 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Slide 14: ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
Slide 15 / 76

WEAK ENTITY TYPES

Slide 15: WEAK ENTITY TYPES

Notes

  1. Step 2 weak entity: relation with its attributes + the owner’s primary key as a foreign key; PK = owner PK + partial key.

Exam angle

PredictionQ1

The primary key of a relation mapped from a weak entity is:

Slide 16 / 76

DDL BASICS

Slide 16: DDL BASICS

Notes

  1. CREATE TABLE name (col type constraints, …, PRIMARY KEY (…), FOREIGN KEY (…) REFERENCES other(…));

Slide 17 / 76

DDL BASICS (PRIMARY KEY)

Slide 17: DDL BASICS (PRIMARY KEY)
Slide 18 / 76

DDL BASICS (COMPOSITE KEYS AND NAMING CONSTRAINTS)

Slide 18: DDL BASICS (COMPOSITE KEYS AND NAMING CONSTRAINTS)
Slide 19 / 76

DDL BASICS (FOREIGN KEYS)

Slide 19: DDL BASICS (FOREIGN KEYS)

Notes

  1. FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) ON DELETE CASCADE / SET NULL.

Exam angle

PredictionQ1

Which defines a foreign key?

Slide 20 / 76

ACTIVITY

Slide 20: ACTIVITY
Slide 21 / 76

ACTIVITY (SOLUTION)

Slide 21: ACTIVITY (SOLUTION)
Slide 22 / 76

A BIT MORE DDL

Slide 22: A BIT MORE DDL
Slide 23 / 76

SOME SQL DATA TYPES (MYSQL)

Slide 23: SOME SQL DATA TYPES (MYSQL)
Slide 24 / 76

DDL CONSTRAINTS

Slide 24: DDL CONSTRAINTS
Slide 25 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Slide 25: ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
Slide 26 / 76

MAPPING OF BINARY 1:1 RELATIONSHIP TYPES

Slide 26: MAPPING OF BINARY 1:1 RELATIONSHIP TYPES

Notes

  1. 1:1: three choices. Foreign-key approach (best): put the PK of one side as FK in the side with TOTAL participation. Merged relation (both total). Relationship relation.

Exam angle

PredictionQ1

For 1:1 MANAGES where DEPARTMENT participates totally, the FK goes in:

Slide 27 / 76

1:1 FOREIGN KEY APPROACH

Slide 27: 1:1 FOREIGN KEY APPROACH
Slide 28 / 76

1:1 FOREIGN KEY APPROACH

Slide 28: 1:1 FOREIGN KEY APPROACH
Slide 29 / 76

1:1 FOREIGN KEY APPROACH

Slide 29: 1:1 FOREIGN KEY APPROACH
Slide 30 / 76

MAPPING OF BINARY 1:1 RELATIONSHIP TYPES

Slide 30: MAPPING OF BINARY 1:1 RELATIONSHIP TYPES
Slide 31 / 76

1:1 MERGED RELATION APPROACH

Slide 31: 1:1 MERGED RELATION APPROACH
Slide 32 / 76

MAPPING OF BINARY 1:1 RELATIONSHIP TYPES

Slide 32: MAPPING OF BINARY 1:1 RELATIONSHIP TYPES
Slide 33 / 76

1:1 RELATIONSHIP RELATION APPROACH

Slide 33: 1:1 RELATIONSHIP RELATION APPROACH
Slide 34 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Slide 34: ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Notes

  1. Memory trick 1:N → the FK goes on the MANY side.

Exam angle

PredictionQ1

EMPLOYEE (N) works for DEPARTMENT (1). Map it by:

Slide 35 / 76

1:N FOREIGN KEY APPROACH

Slide 35: 1:N FOREIGN KEY APPROACH
Slide 36 / 76

1:N FOREIGN KEY APPROACH

Slide 36: 1:N FOREIGN KEY APPROACH
Slide 37 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Slide 37: ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Notes

  1. M:N: a NEW relation with both PKs as FKs; its PK is the combination; relationship attributes go here.

Exam angle

PredictionQ1

WORKS_ON (M:N, attribute Hours) becomes:

Slide 38 / 76

N:M MAPPING TO RELATIONAL

Slide 38: N:M MAPPING TO RELATIONAL
Slide 39 / 76

ACTIVITY

Slide 39: ACTIVITY
Slide 40 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Slide 40: ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
Slide 41 / 76

MAPPING MULTI-VALUED ATTRIBUTES

Slide 41: MAPPING MULTI-VALUED ATTRIBUTES
Slide 42 / 76

ACTIVITY

Slide 42: ACTIVITY
Slide 43 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Slide 43: ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
Slide 44 / 76

MAPPING OF N-ARY RELATIONSHIPS

Slide 44: MAPPING OF N-ARY RELATIONSHIPS
Slide 45 / 76

MAPPING OF N-ARY RELATIONSHIPS

Slide 45: MAPPING OF N-ARY RELATIONSHIPS
Slide 46 / 76

UNARY RELATIONSHIPS

Slide 46: UNARY RELATIONSHIPS

Notes

  1. Unary 1:N: FK to the same table (Super_ssn → EMPLOYEE.Ssn). Unary M:N: new relation with two FKs to the same table.

Slide 47 / 76

1:N UNARY RELATIONSHIPS

Slide 47: 1:N UNARY RELATIONSHIPS
Slide 48 / 76

1:N UNARY RELATIONSHIPS (PARTIAL BOTH SIDES)

Slide 48: 1:N UNARY RELATIONSHIPS (PARTIAL BOTH SIDES)
Slide 49 / 76

M:N UNARY RELATIONSHIPS

Slide 49: M:N UNARY RELATIONSHIPS
Slide 50 / 76

1:N UNARY RELATIONSHIPS (PARTIAL ONE SIDE)

Slide 50: 1:N UNARY RELATIONSHIPS (PARTIAL ONE SIDE)
Slide 51 / 76

SUMMARY OF MAPPING CONSTRUCTS AND CONSTRAINTS

Slide 51: SUMMARY OF MAPPING CONSTRUCTS AND CONSTRAINTS
Slide 52 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Slide 52: ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Notes

  1. Step 8 specialization options: 8A superclass + subclass relations (any case); 8B subclass relations only (total, disjoint); 8C one relation + type attribute (disjoint); 8D one relation + Boolean flags (overlapping).

Exam angle

PredictionQ1

Mapping option that uses ONE relation with a Boolean flag per subclass suits:

Slide 53 / 76

MAPPING EER MODEL CONSTRUCTS TO RELATIONS

Slide 53: MAPPING EER MODEL CONSTRUCTS TO RELATIONS
Slide 54 / 76

MAPPING EER MODEL CONSTRUCTS TO RELATIONS

Slide 54: MAPPING EER MODEL CONSTRUCTS TO RELATIONS
Slide 55 / 76

EXAMPLE: EMPLOYEE AND JOB TYPE

Slide 55: EXAMPLE: EMPLOYEE AND JOB TYPE
Slide 56 / 76

OPTION 8A: MULTIPLE RELATIONS-SUPERCLASS AND SUBCLASSES

Slide 56: OPTION 8A: MULTIPLE RELATIONS-SUPERCLASS AND SUBCLASSES
Slide 57 / 76

OPTION 8C: EXTRA ATTRIBUTES

Slide 57: OPTION 8C: EXTRA ATTRIBUTES
Slide 58 / 76

EXAMPLE: VEHICLE, CAR OR TRUCK

Slide 58: EXAMPLE: VEHICLE, CAR OR TRUCK
Slide 59 / 76

OPTION 8B: SUB-CLASSES ONLY

Slide 59: OPTION 8B: SUB-CLASSES ONLY
Slide 60 / 76

EXAMPLE: PART ENTITY

Slide 60: EXAMPLE: PART ENTITY
Slide 61 / 76

OPTION 8D: BOOLEAN FLAGS

Slide 61: OPTION 8D: BOOLEAN FLAGS
Slide 62 / 76

DIFFERENT OPTIONS FOR MAPPING

Slide 62: DIFFERENT OPTIONS FOR MAPPING
Slide 63 / 76

Slide 63

Slide 63: Slide 63
Slide 64 / 76

MAPPING OTHER EER MODEL CONSTRUCTS TO RELATIONS

Slide 64: MAPPING OTHER EER MODEL CONSTRUCTS TO RELATIONS
Slide 65 / 76

Slide 65

Slide 65: Slide 65
Slide 66 / 76

RELATIONAL MAPPING

Slide 66: RELATIONAL MAPPING
Slide 67 / 76

ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)

Slide 67: ER-TO-RELATIONAL MAPPING ALGORITHM (CONTD.)
Slide 68 / 76

Slide 68

Slide 68: Slide 68
Slide 69 / 76

Slide 69

Slide 69: Slide 69
Slide 70 / 76

Slide 70

Slide 70: Slide 70
Slide 71 / 76

ACTIVIT Y

Slide 71: ACTIVIT Y
Slide 72 / 76

SOLUTION

Slide 72: SOLUTION
Slide 73 / 76

SOLUTION

Slide 73: SOLUTION
Slide 74 / 76

FINAL SOLUTION

Slide 74: FINAL SOLUTION
Slide 75 / 76

ACTIVIT Y

Slide 75: ACTIVIT Y
Slide 76 / 76

CHAPTER SUMMARY

Slide 76: CHAPTER SUMMARY