Library design

Set up book, member and loan tables and write skeleton queries.

In this project you will build a library database. First step: create books and authors tables.

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT
);

Skeleton of the books table.

The table books is created.

Library design — details

Each table needs a primary key. author_id will later become a foreign key.

You first create the books and authors tables, then a loans table for borrowing records; each table has an id INT PRIMARY KEY.

Create books with id, title and author_id columns.

Show example solution

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT
)

Add PRIMARY KEY

CREATE TABLE books (
  id INT ____,
  title VARCHAR(100)
);

Show answer

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100)
);