Database Design in SQL Server

Good design keeps data correct and easy to query. You will design tables with common sense and normalization, write the scripts that create them, and draw database diagrams in SSMS.

Updated

Foundations of Database Design

  1. Common Sense Design (Journeyman): Design tables from the questions the data must answer. Learn from Northwind's design - one subject per table, columns at their smallest useful size, a primary key in every table, and foreign keys to link them.
  2. Create Database Scripts in T-SQL (Journeyman): Turn a design into a script that builds it - CREATE DATABASE, CREATE TABLE with types, NOT NULL, IDENTITY and DEFAULT, and constraints for primary keys, foreign keys, UNIQUE and CHECK.
  3. Normalization (Journeyman): Normalization splits data into tables so each fact is stored once. Spot the problems repeated data causes, and apply the first three normal forms.
  4. Database Diagrams in SSMS (Journeyman): SSMS draws a database's tables and the foreign keys between them. Make a diagram of Northwind, read its keys and relationship lines, and know when to draw one.

Exercise: Build-It Pizzeria

  1. Exercise: Build-It Pizzeria (Journeyman): Design a database for a build-your-own pizzeria from its menu and order slips, write the script that builds and fills it, and answer the owner's questions with queries.