Module 8: Database Design I

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 8 Readings: Chapter 5 (first half)
  • Module 8 Slides: Chapter 5 (first half)

Learning Activities

  • Module 8 Quiz: Chapter 5


How to create a database design:

For Further Study

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

  • 5.1 – 5.6
  • 5.9 – 5.27

Leave a Reply

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