SQL and MySQL course Β· Module 4: Creating tables (DDL)

The Great Work of the scriptorium - the biblioteka shelf project

2 min read Β·
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

  1. Primary key - we start with id INT AUTO_INCREMENT PRIMARY KEY. It is a pattern you already know.
  2. Required fields - ksiazka_id, czytelnik_id and data_wypozyczenia must always have a value, so we add NOT NULL.
  3. Optional field - we leave data_zwrotu without 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. 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.

  1. QuestionPrimary key in the project
  2. Coding taskBuild the wypozyczenia shelf

Useful articles