CMPS 160: Databases
Introduction
In this module, we will learn how to design a new database. This is useful information to understand how to gather requirements and develop a practical database. Database design consists of important steps of designing a data structure, identify data elements, design tables and assign columns, identify the primary and foreign key relationships, understand how to normalize a structure and identify indexes that are required.
We will learn how to create a database by writing DDL (Data Definition Language) to create scripts that will generate a database. Understand the different methods of adding tables and indexes.
Module Objectives
- Learn the specifications for a database, identify the tables, columns, keys, relationships, and indexes for the database.
- Learn the tables for an unnormalized database, normalize the structure to the third normal form.
- Use MySQL Workbench to create or work with an EER(Enhanced Entity-Relationship) model for a database and any EER (Enhanced Entity Relationship) diagrams that are associated with the model.
- Learn the design for a database, write DDL (Data Definition Language) statements to create tables, constraints, and indexes that are required.
- Learn how different types of constraints restrict values that can be stored in a table. ( not null, unique, primary key and foreign key)
- Understand the difference between column level constraints and table-level constraint.
- Learn the use of an index
- Learn the use of a script for creating the tables of a database.
Learning Resources
- Module 8 Readings: Chapter 10
- Module 8 Slides: Chapter 10
Learning Activities
- Module 8 Quiz: Chapter 10
Video
How to create a database design: