SQL and MySQL course Β· Module 4: Creating tables (DDL)
The Great Work of the scriptorium - the biblioteka shelf project
In this lesson4
The time has come to design a full-fledged shelf on your own, combining everything you have learned in the scriptorium. We will build the heart of the lending desk - the wypozyczenia (loans) table, which links books with readers.
What does the wypozyczenia shelf need?
Each loan is one row. We must record:
- id - the primary key, numbered automatically,
- ksiazka_id - which book was borrowed (a required field),
- czytelnik_id - who borrowed it (a required field),
- data_wypozyczenia - when (a required field),
- data_zwrotu - when it was returned (may be empty if the book is still with the reader).
Assembling the project step by step
- Primary key - we start with
id INT AUTO_INCREMENT PRIMARY KEY. It is a pattern you already know. - Required fields -
ksiazka_id,czytelnik_idanddata_wypozyczeniamust always have a value, so we add NOT NULL. - Optional field - we leave
data_zwrotuwithout NOT NULL, because while the book is on loan it stays empty (NULL).
The finished pattern
CREATE TABLE wypozyczenia (
id INT AUTO_INCREMENT PRIMARY KEY,
ksiazka_id INT NOT NULL,
czytelnik_id INT NOT NULL,
data_wypozyczenia DATE NOT NULL,
data_zwrotu DATE
);Why is this a good design?
- Each row has a unique identifier thanks to the primary key.
- The required fields protect against incomplete records (a loan without a book makes no sense).
- The optional field (
data_zwrotu) faithfully reflects reality - until the return happens, it stays empty.
Build this shelf and you will have the foundation of the entire lending desk. This is your Great Work of the scriptorium!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. When designing a new table, which pair of constraints do we combine with the id column to make it an auto-numbered primary key?
Hands-on tasks in the game
- Code editor
Write a CREATE TABLE statement that creates the wypozyczenia table with columns: id of type INT with AUTO_INCREMENT and PRIMARY KEY, ksiazka_id of type INT NOT NULL, czytelnik_id of type INT NOT NULL, data_wypozyczenia of type DATE NOT NULL and data_zwrotu of type DATE.