CS340 · Chapter 4 · Annotated SlidesThe Relational Model and DBMS Architecture
Original
Overview

The Relational Model and DBMS Architecture

DBMS languages (DDL, DML, DCL, TCL), relations, tuples, attributes and domains, characteristics of relations, domain, key, NULL, entity-integrity and referential-integrity constraints, update operations and their violations, DBMS modules, and centralized, two-tier and three-tier architectures.

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 70 slides
  1. 1 The Relational Model
  2. 2 PROJECT NEXT STEPS (SPECIFICATIONS)
  3. 3 LEARNING GOALS
  4. 4 BUILDING AN APPLICATION WITH A DBMS
  5. 5 DATABASE DESIGN
  6. 6 LOGICAL DESIGN
  7. 7 RELATIONAL MODEL
  8. 8 DBMS LANGUAGES
  9. 9 DBMS LANGUAGES
  10. 10 DDL COMMANDS
  11. 11 DIE
  12. 12 DML COMMANDS
  13. 13 DBMS LANGUAGES
  14. 14 RELATIONAL MODEL COMPONENTS
  15. 15 RELATION EXAMPLE
  16. 16 FORMAL DEFINITIONS
  17. 17 ACTIVITY
  18. 18 DEFINITIONS SUMMARY
  19. 19 CHARACTERISTICS OF RELATIONS
  20. 20 SAME STATE AS PREVIOUS FIGURE (BUT WITH DIFFERENT ORDER OF
  21. 21 CHARACTERISTICS OF RELATIONS
  22. 22 CHARACTERISTICS OF RELATIONS
  23. 23 RELATIONAL MODEL CONSTRAINTS
  24. 24 INHERENT MODEL-BASED OR IMPLICIT CONSTRAINTS
  25. 25 SCHEMA-BASED CONSTRAINTS
  26. 26 SCHEMA-BASED CONSTRAINTS
  27. 27 DOMAIN CONSTRAINTS
  28. 28 CONSTRAINTS ON NULL VALUES
  29. 29 sjw
  30. 30 KEY CONSTRAINTS (CONTINUED)
  31. 31 set
  32. 32 CAR TABLE WITH TWO CANDIDATE KEYS
  33. 33 ACTIVITY
  34. 34 RELATIONAL DATABASE SCHEMA
  35. 35 Slide 35
  36. 36 RELATIONAL DATABASE STATE
  37. 37 POPULATED DATABASE STATE
  38. 38 Slide 38
  39. 39 Eat
  40. 40 o
  41. 41 REFERENTIAL INTEGRITY
  42. 42 REFERENTIAL INTEGRITY CONSTRAINTS
  43. 43 REFERENTIAL INTEGRITY (OR FOREIGN KEY) CONSTRAINT
  44. 44 DISPLAYING A RELATIONAL DATABASE SCHEMA AND ITS CONSTRAINTS
  45. 45 Slide 45
  46. 46 OTHER TYPES OF CONSTRAINTS
  47. 47 UPDATE OPERATIONS ON RELATIONS
  48. 48 UPDATE OPERATIONS ON RELATIONS
  49. 49 cases
  50. 50 POSSIBLE VIOLATIONS FOR EACH OPERATION
  51. 51 THE DELETE OPERATION
  52. 52 POSSIBLE VIOLATIONS FOR EACH OPERATION
  53. 53 t
  54. 54 ACTIVITY
  55. 55 NEXT..
  56. 56 TYPICAL DBMS COMPONENT MODULES
  57. 57 DBMS COMPONENT MODULES
  58. 58 DBMS COMPONENT MODULES
  59. 59 CENTRALIZED AND CLIENT- SERVER DBMS ARCHITECTURES
  60. 60 A PHYSICAL CENTRALIZED ARCHITECTURE
  61. 61 BASIC 2-TIER CLIENT- SERVER ARCHITECTURES
  62. 62 PHYSICAL TWO-TIER CLIENT SERVER ARCHITECTURE
  63. 63 CLIENTS
  64. 64 DBMS SERVER
  65. 65 LOGICAL TWO-TIER CLIENT SERVER ARCHITECTURE
  66. 66 THREE-TIER CLIENT-SERVER ARCHITECTURE
  67. 67 THREE TIER CLIENT-SERVER ARCHITECTURE
  68. 68 THREE TIER CLIENT-SERVER ARCHITECTURE
  69. 69 THREE TIER CLIENT-SERVER ARCHITECTURE
  70. 70 SUMMARY
Slide 1 / 70

The Relational Model

Slide 1: The Relational Model
Slide 2 / 70

PROJECT NEXT STEPS (SPECIFICATIONS)

Slide 2: PROJECT NEXT STEPS (SPECIFICATIONS)
Slide 3 / 70

LEARNING GOALS

Slide 3: LEARNING GOALS
Slide 4 / 70

BUILDING AN APPLICATION WITH A DBMS

Slide 4: BUILDING AN APPLICATION WITH A DBMS
Slide 5 / 70

DATABASE DESIGN

Slide 5: DATABASE DESIGN
Slide 6 / 70

LOGICAL DESIGN

Slide 6: LOGICAL DESIGN
Slide 7 / 70

RELATIONAL MODEL

Slide 7: RELATIONAL MODEL
Slide 8 / 70

DBMS LANGUAGES

Slide 8: DBMS LANGUAGES

Notes

  1. DDL defines structure (CREATE, ALTER, DROP). DML queries and changes data (SELECT, INSERT, UPDATE, DELETE). DCL: GRANT/REVOKE. TCL: COMMIT/ROLLBACK.

Exam angle

Past exam · Midterm exam

Q8: the language that queries and manipulates data

PredictionQ1

Which language queries and manipulates data?

PredictionQ2

ALTER TABLE belongs to:

Slide 9 / 70

DBMS LANGUAGES

Slide 9: DBMS LANGUAGES
Slide 10 / 70

DDL COMMANDS

Slide 10: DDL COMMANDS
Slide 11 / 70

DIE

Slide 11: DIE
Slide 12 / 70

DML COMMANDS

Slide 12: DML COMMANDS
Slide 13 / 70

DBMS LANGUAGES

Slide 13: DBMS LANGUAGES
Slide 14 / 70

RELATIONAL MODEL COMPONENTS

Slide 14: RELATIONAL MODEL COMPONENTS

Notes

  1. Relation = table; tuple = row; attribute = column; domain = set of allowed values; degree = number of attributes; cardinality = number of tuples.

Exam angle

PredictionQ1

The number of attributes in a relation is its:

Slide 15 / 70

RELATION EXAMPLE

Slide 15: RELATION EXAMPLE
Slide 16 / 70

FORMAL DEFINITIONS

Slide 16: FORMAL DEFINITIONS
Slide 17 / 70

ACTIVITY

Slide 17: ACTIVITY
Slide 18 / 70

DEFINITIONS SUMMARY

Slide 18: DEFINITIONS SUMMARY
Slide 19 / 70

CHARACTERISTICS OF RELATIONS

Slide 19: CHARACTERISTICS OF RELATIONS

Notes

  1. Tuples are unordered; values are atomic (1NF); NULL means unknown or not applicable.

Slide 20 / 70

SAME STATE AS PREVIOUS FIGURE (BUT WITH DIFFERENT ORDER OF

Slide 20: SAME STATE AS PREVIOUS FIGURE (BUT WITH DIFFERENT ORDER OF
Slide 21 / 70

CHARACTERISTICS OF RELATIONS

Slide 21: CHARACTERISTICS OF RELATIONS
Slide 22 / 70

CHARACTERISTICS OF RELATIONS

Slide 22: CHARACTERISTICS OF RELATIONS
Slide 23 / 70

RELATIONAL MODEL CONSTRAINTS

Slide 23: RELATIONAL MODEL CONSTRAINTS

Notes

  1. Constraint families: inherent (model-based), schema-based (DDL), application-based (business rules).

Slide 24 / 70

INHERENT MODEL-BASED OR IMPLICIT CONSTRAINTS

Slide 24: INHERENT MODEL-BASED OR IMPLICIT CONSTRAINTS
Slide 25 / 70

SCHEMA-BASED CONSTRAINTS

Slide 25: SCHEMA-BASED CONSTRAINTS
Slide 26 / 70

SCHEMA-BASED CONSTRAINTS

Slide 26: SCHEMA-BASED CONSTRAINTS

Exam angle

PredictionQ1

A primary-key attribute may be NULL if it is unknown.

Slide 27 / 70

DOMAIN CONSTRAINTS

Slide 27: DOMAIN CONSTRAINTS
Slide 28 / 70

CONSTRAINTS ON NULL VALUES

Slide 28: CONSTRAINTS ON NULL VALUES
Slide 29 / 70

sjw

Slide 29: sjw
Slide 30 / 70

KEY CONSTRAINTS (CONTINUED)

Slide 30: KEY CONSTRAINTS (CONTINUED)

Notes

  1. Superkey: any set of attributes that is unique. Key (candidate key): a MINIMAL superkey. Primary key: the chosen candidate key (underlined).

  2. Trap Every key is a superkey, but a superkey is a key only if no attribute can be removed.

Exam angle

PredictionQ1

A minimal superkey is a:

Slide 31 / 70

set

Slide 31: set
Slide 32 / 70

CAR TABLE WITH TWO CANDIDATE KEYS

Slide 32: CAR TABLE WITH TWO CANDIDATE KEYS
Slide 33 / 70

ACTIVITY

Slide 33: ACTIVITY
Slide 34 / 70

RELATIONAL DATABASE SCHEMA

Slide 34: RELATIONAL DATABASE SCHEMA
Slide 35 / 70

Slide 35

Slide 35: Slide 35
Slide 36 / 70

RELATIONAL DATABASE STATE

Slide 36: RELATIONAL DATABASE STATE
Slide 37 / 70

POPULATED DATABASE STATE

Slide 37: POPULATED DATABASE STATE
Slide 38 / 70

Slide 38

Slide 38: Slide 38
Slide 39 / 70

Eat

Slide 39: Eat
Slide 40 / 70

o

Slide 40: o
Slide 41 / 70

REFERENTIAL INTEGRITY

Slide 41: REFERENTIAL INTEGRITY

Notes

  1. Referential integrity: a foreign key value must match an existing primary key in the referenced relation, or be NULL.

Exam angle

PredictionQ1

The primary function of a foreign key is to:

Slide 42 / 70

REFERENTIAL INTEGRITY CONSTRAINTS

Slide 42: REFERENTIAL INTEGRITY CONSTRAINTS
Slide 43 / 70

REFERENTIAL INTEGRITY (OR FOREIGN KEY) CONSTRAINT

Slide 43: REFERENTIAL INTEGRITY (OR FOREIGN KEY) CONSTRAINT
Slide 44 / 70

DISPLAYING A RELATIONAL DATABASE SCHEMA AND ITS CONSTRAINTS

Slide 44: DISPLAYING A RELATIONAL DATABASE SCHEMA AND ITS CONSTRAINTS
Slide 45 / 70

Slide 45

Slide 45: Slide 45
Slide 46 / 70

OTHER TYPES OF CONSTRAINTS

Slide 46: OTHER TYPES OF CONSTRAINTS
Slide 47 / 70

UPDATE OPERATIONS ON RELATIONS

Slide 47: UPDATE OPERATIONS ON RELATIONS

Notes

  1. Insert, delete, update can violate constraints. Insert: domain, key, entity, referential. Delete: only referential.

Exam angle

PredictionQ1

Deleting a DEPARTMENT row that employees still reference violates:

Slide 48 / 70

UPDATE OPERATIONS ON RELATIONS

Slide 48: UPDATE OPERATIONS ON RELATIONS
Slide 49 / 70

cases

Slide 49: cases
Slide 50 / 70

POSSIBLE VIOLATIONS FOR EACH OPERATION

Slide 50: POSSIBLE VIOLATIONS FOR EACH OPERATION
Slide 51 / 70

THE DELETE OPERATION

Slide 51: THE DELETE OPERATION
Slide 52 / 70

POSSIBLE VIOLATIONS FOR EACH OPERATION

Slide 52: POSSIBLE VIOLATIONS FOR EACH OPERATION
Slide 53 / 70

t

Slide 53: t
Slide 54 / 70

ACTIVITY

Slide 54: ACTIVITY
Slide 55 / 70

NEXT..

Slide 55: NEXT..
Slide 56 / 70

TYPICAL DBMS COMPONENT MODULES

Slide 56: TYPICAL DBMS COMPONENT MODULES
Slide 57 / 70

DBMS COMPONENT MODULES

Slide 57: DBMS COMPONENT MODULES
Slide 58 / 70

DBMS COMPONENT MODULES

Slide 58: DBMS COMPONENT MODULES
Slide 59 / 70

CENTRALIZED AND CLIENT- SERVER DBMS ARCHITECTURES

Slide 59: CENTRALIZED AND CLIENT- SERVER DBMS ARCHITECTURES
Slide 60 / 70

A PHYSICAL CENTRALIZED ARCHITECTURE

Slide 60: A PHYSICAL CENTRALIZED ARCHITECTURE
Slide 61 / 70

BASIC 2-TIER CLIENT- SERVER ARCHITECTURES

Slide 61: BASIC 2-TIER CLIENT- SERVER ARCHITECTURES

Notes

  1. Two-tier: client (UI + application) talks directly to the DB server.

Slide 62 / 70

PHYSICAL TWO-TIER CLIENT SERVER ARCHITECTURE

Slide 62: PHYSICAL TWO-TIER CLIENT SERVER ARCHITECTURE
Slide 63 / 70

CLIENTS

Slide 63: CLIENTS
Slide 64 / 70

DBMS SERVER

Slide 64: DBMS SERVER
Slide 65 / 70

LOGICAL TWO-TIER CLIENT SERVER ARCHITECTURE

Slide 65: LOGICAL TWO-TIER CLIENT SERVER ARCHITECTURE
Slide 66 / 70

THREE-TIER CLIENT-SERVER ARCHITECTURE

Slide 66: THREE-TIER CLIENT-SERVER ARCHITECTURE

Notes

  1. Three-tier: client → application (web) server with business logic → database server. Typical for web apps.

Exam angle

Past exam · Midterm exam

Q5: the architecture used by web apps

PredictionQ1

In a three-tier architecture, business logic lives in:

Slide 67 / 70

THREE TIER CLIENT-SERVER ARCHITECTURE

Slide 67: THREE TIER CLIENT-SERVER ARCHITECTURE
Slide 68 / 70

THREE TIER CLIENT-SERVER ARCHITECTURE

Slide 68: THREE TIER CLIENT-SERVER ARCHITECTURE
Slide 69 / 70

THREE TIER CLIENT-SERVER ARCHITECTURE

Slide 69: THREE TIER CLIENT-SERVER ARCHITECTURE
Slide 70 / 70

SUMMARY

Slide 70: SUMMARY