Module 9: Database Design II

CMPS 160: Databases


A data model captures the essential entities and relationships in a model, as we have seen in the previous module. But a data model cannot be directly turned into a database and has to be transformed into a database design first. This step is sometimes confusing because superficially a data model and database design look much alike, but there are several important differences. The most important differences are that a database design requires the definition of primary and foreign keys, and the introduction of intermediate tables for many-to-many relationships. After finishing the database design the next step would be to implement it in an actual DBMS, such as MySQL.

Module Objectives

  • Learn how to transform E-R data models into relational designs
  • Practice applying the normalization process
  • Understand the need for denormalization
  • Learn how to represent weak entities with the relational model
  • Know how to represent 1:1, 1:N, and N:M binary relationships
  • Know how to represent 1:1, 1:N, and N:M recursive relationships
  • Learn SQL statements for creating joins over binary and recursive relationships

Learning Resources

  • Module 9 Readings: Chapter 5
  • Module 9 Slides: Chapter 5

Learning Activities

  • Module 9 Assignment: Chapter 5 San Juan Sailboat Charters Case Questions
  • Module 9 Project: Progress Report

For Further Study

It is highly recommended to work through the exercises at the end of the chapter, specifically:

  • 5.7 – 5.8
  • 5.28 – 5.38
  • Washington State Patrol Case Questions

Leave A Reply

Your email address will not be published. Required fields are marked *