AI InterviewSearch…Search problems, topics, or pages…Ctrl KGet started
Skip to content

12 Schema design / Checkpoint

Checkpoint — Schema design

25 min · +25 XP · 3 exercises · 12 questions

Exercises0/3
Quiz · 70% to clear12 q

This checkpoint covers the whole module: CREATE TABLE and choosing types (integers, DECIMAL for money, VARCHAR and CHAR, DATE, DATETIME and TIMESTAMP, BOOLEAN), CREATE TABLE ... AS SELECT; the constraints NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY; ALTER TABLE and DROP; normalization from 1NF to BCNF; one-to-many, many-to-many and self-referencing relationships with their ON DELETE actions; and views.

How it works. Twelve questions and three scripts. You need 70% on the questions and every script accepted to clear the module. You can retake it as often as you like; your best score counts. Each script builds or changes a schema and ends with the SELECT that shows the result.

Before you start, make sure you can answer these from memory:

  • Why is money stored as DECIMAL and not FLOAT, and what does BOOLEAN really mean in MySQL?
  • What does CREATE TABLE ... AS SELECT copy, and what does it leave behind?
  • Primary key vs unique key: how many per table, and which may hold NULL?
  • How do you add a NOT NULL column to a table that already has rows?
  • What do 1NF, 2NF, 3NF and BCNF each forbid?
  • Where does the foreign key go in a one-to-many relationship, and how is many-to-many stored?
  • What is the difference between a view and a table, and how do you change a view's definition?

The three scripts are a library schema built from a flat loan log, a migration that adds, backfills and renames columns, and a reporting view over a many-to-many relationship.

Common questions

What is the difference between a primary key and a unique key?

A table has one primary key, which is never NULL and in InnoDB is the clustered index ordering the rows. It can have many unique keys, which also reject duplicates but may hold NULLs, any number of them in MySQL.

Which normal form does a partial dependency break?

Second normal form. A partial dependency is a non-key column that depends on only part of a composite key, such as a product name in an order-lines table keyed by order and product. Moving it to a products table fixes it.

Does a view store data?

No. A view stores its SELECT, and each read runs that query on the current base tables, so it is always up to date and takes no space. A materialized view, available in PostgreSQL and Oracle but not MySQL, is the kind that stores its result.

AI InterviewGet started

SQL Schema Design Quiz: Keys, Normal Forms and Views Practice

Test yourself: 12 questions and three scripts on CREATE TABLE, data types, constraints, ALTER TABLE, normalization from 1NF to BCNF, foreign keys and views.

  • Course: SQL study plan
  • Module: Schema design
  • Kind: Checkpoint — cleared at 70%
  • Reading time: 25 min
  • Runtime: MySQL 8 syntax on SQLite 3.49

Checkpoint — Schema design is the checkpoint that closes the Schema design module: a graded quiz and whole-program exercises, passed at 70%.

Instructions

This checkpoint covers the whole module: CREATE TABLE and choosing types (integers, DECIMAL for money, VARCHAR and CHAR, DATE, DATETIME and TIMESTAMP, BOOLEAN), CREATE TABLE ... AS SELECT; the constraints NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY; ALTER TABLE and DROP; normalization from 1NF to BCNF; one-to-many, many-to-many and self-referencing relationships with their ON DELETE actions; and views.

How it works. Twelve questions and three scripts. You need 70% on the questions and every script accepted to clear the module. You can retake it as often as you like; your best score counts. Each script builds or changes a schema and ends with the SELECT that shows the result.

Before you start, make sure you can answer these from memory:

  • Why is money stored as DECIMAL and not FLOAT, and what does BOOLEAN really mean in MySQL?
  • What does CREATE TABLE ... AS SELECT copy, and what does it leave behind?
  • Primary key vs unique key: how many per table, and which may hold NULL?
  • How do you add a NOT NULL column to a table that already has rows?
  • What do 1NF, 2NF, 3NF and BCNF each forbid?
  • Where does the foreign key go in a one-to-many relationship, and how is many-to-many stored?
  • What is the difference between a view and a table, and how do you change a view's definition?

The three scripts are a library schema built from a flat loan log, a migration that adds, backfills and renames columns, and a reporting view over a many-to-many relationship.

Common questions

What is the difference between a primary key and a unique key?

A table has one primary key, which is never NULL and in InnoDB is the clustered index ordering the rows. It can have many unique keys, which also reject duplicates but may hold NULLs, any number of them in MySQL.

Which normal form does a partial dependency break?

Second normal form. A partial dependency is a non-key column that depends on only part of a composite key, such as a product name in an order-lines table keyed by order and product. Moving it to a products table fixes it.

Does a view store data?

No. A view stores its SELECT, and each read runs that query on the current base tables, so it is always up to date and takes no space. A materialized view, available in PostgreSQL and Oracle but not MySQL, is the kind that stores its result.

Exercises

A library schema from a loan log

loan_log(id, member_email, member_name, isbn, title, borrowed_on) is a flat record of every loan. Normalize it into three tables:

  • members(id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, name VARCHAR(50) NOT NULL);
  • books(isbn VARCHAR(13) PRIMARY KEY, title VARCHAR(100) NOT NULL);
  • loans(member_id INT NOT NULL, isbn VARCHAR(13) NOT NULL, borrowed_on DATE NOT NULL) with a primary key on all three columns and foreign keys to members and books.

Fill members in email order (so ids are numbered alphabetically by email), then books, then loans (translating emails to member ids with a join). Print the members as id, email ordered by id, then each member's loan count as name, loans ordered by loans descending, then name.

idmember_emailmember_nameisbntitleborrowed_on
1ravi@lib.ioRavi9780132350884Clean Code2024-03-01
2asha@lib.ioAsha9780596007126Head First Design Patterns2024-03-02
3ravi@lib.ioRavi9780596007126Head First Design Patterns2024-03-10
4meera@lib.ioMeera9780132350884Clean Code2024-03-12
5ravi@lib.ioRavi9780132350884Clean Code2024-04-01
id | email
1 | asha@lib.io
2 | meera@lib.io
3 | ravi@lib.io

name | loans
Ravi | 3
Asha | 1
Meera | 1

Migrate the price column

products stores prices as integer cents and carries a dead column. Migrate it, in order:

  1. add price DECIMAL(10,2);
  2. backfill it as price_cents / 100;
  3. drop price_cents;
  4. rename title to name;
  5. drop old_sku;
  6. add currency CHAR(3) NOT NULL DEFAULT 'INR'.

Then print SELECT * FROM products ORDER BY id — the columns are id, name, price, currency.

idtitleprice_centsold_sku
1Pen150X1
2Notebook1999X2
3Bag120000NULL
id | name | price | currency
1 | Pen | 1.5 | INR
2 | Notebook | 19.99 | INR
3 | Bag | 1200 | INR

A course roster view

Create a view course_roster with the columns code, student, enrolled_on — one row per enrolment, joining enrolments to courses and students.

The starter then enrols Ben (student 2) in course 1 and reads the view twice: the roster of course DB101 as student ordered by name, and the number of students per course as code, students ordered by code. Leave those statements as they are.

students: (1, Asha), (2, Ben), (3, Chen). courses: (1, DB101), (2, OS201). enrolments(student_id, course_id, enrolled_on): (1, 1, 2024-01-10), (3, 1, 2024-01-12), (1, 2, 2024-01-15), (2, 2, 2024-01-20).

student
Asha
Ben
Chen

code | students
DB101 | 3
OS201 | 2

In this module: Schema design

← Views — saved queries as tables · How indexes work — B+trees, clustered and secondary →