Skip to content

The dataset: Aurora Bookshop

The test server exposes the catalog of a made-up online bookshop, the Aurora Bookshop (Libreria Aurora). Titles, authors, customers and reviews are generated by a deterministic program (api/app/seed.py): no real data, and every sandbox always starts from the same numbers. The content is in Italian (titles, names, cities, category names); table and column names are in English.

authors ──< books >── categories ──┐ (parent_id: una categoria
1 N N 1 │ può avere una categoria madre)
│ └──────────────────────────────┘
│ N
book_tags >── tags (N-N: un libro ha più tag, un tag più libri)
│
│ 1
customers ──< orders ──< order_items >── books
1 N 1 N N 1 (N-N ordini↔libri, con quantità e prezzo)
│
└──< reviews >── books (un cliente recensisce più libri)

──< reads “one to many”: an author has many books; each book has one author (the FK books.author_id).

Counts of the public dataset (in a sandbox they change with your writes). The information on keys and constraints is what GET /api/v1/schema returns.

Column Type Notes
id INTEGER PK
name TEXT not null
country TEXT 2-letter ISO code (IT, ES, NO, …)
born_year INTEGER
bio TEXT
Column Type Notes
id INTEGER PK
name TEXT
slug TEXT UNIQUE; used in URLs (/categories/gialli/books)
parent_id INTEGER FK → categories.id (2-level hierarchy: Narrativa (fiction) → Gialli (crime), Fantascienza (sci-fi), Storici (historical); Saggistica (non-fiction) → Storia (history), Scienza (science), Informatica (computing))
Column Type Notes
id INTEGER PK
title TEXT not null; also indexed in the full-text table books_fts
isbn TEXT UNIQUE
author_id INTEGER FK → authors.id, index idx_books_author
category_id INTEGER FK → categories.id, index idx_books_category
price REAL CHECK(price >= 0), index idx_books_price
pages, published_year INTEGER
language TEXT it (most), en, es
in_stock INTEGER 0/1, exposed as a boolean
attributes TEXT (JSON) `{“format”: “brossura
created_at TEXT ISO 8601 UTC (2024-08-12T00:00:00Z)

tags(id PK, name UNIQUE); book_tags(book_id FK → books.id, tag_id FK → tags.id) with a composite primary key (book_id, tag_id) and ON DELETE CASCADE on both FKs. Tags: bestseller, novità (new), premio (award), classico (classic), illustrato (illustrated), serie (series), esordio (debut), tascabile (pocket), audiolibro (audiobook), tradotto (translated).

Column Type Notes
id INTEGER PK
name TEXT
email TEXT UNIQUE, example.com domain
city TEXT ten Italian cities
created_at TEXT ISO 8601
Column Type Notes
id INTEGER PK
customer_id INTEGER FK → customers.id, indexed
status TEXT CHECK in pending, paid, shipped, delivered, cancelled; transitions: pending → paid → shipped → delivered; cancelled from pending or paid
shipping_address TEXT (JSON) {"street", "city", "zip", "country"}
total REAL sum of quantity × unit_price of the lines
created_at TEXT ISO 8601
Column Type Notes
id INTEGER PK
order_id INTEGER FK → orders.id, ON DELETE CASCADE
book_id INTEGER FK → books.id (a book that appears here cannot be deleted: 409 book_in_orders)
quantity INTEGER CHECK(quantity > 0)
unit_price REAL price at the time of the order (copied from books.price)
Column Type Notes
id INTEGER PK
book_id INTEGER FK → books.id, ON DELETE CASCADE, indexed
customer_id INTEGER FK → customers.id
rating INTEGER CHECK(rating BETWEEN 1 AND 5)
comment TEXT
created_at TEXT ISO 8601
  • The book_stats view (book_id, reviews_count, avg_rating): reviews and average per book; it is what adds reviews_count and avg_rating to the /books responses.
  • The full-text table books_fts (FTS5 on title and description): it powers GET /search.
  • Prices: min €7.13, average €19.90, max €34.49.
  • Categories with the most books: Saggistica (14), Ragazzi (children, 12), Cucina (cooking, 9).
  • Author with the most books: Matteo Rinaldi (8).
  • Orders by status: delivered 48, paid 26, cancelled 18, shipped 16, pending 12.
  • Cities with the highest revenue: Torino (€1,715.02), Padova, Firenze.
GET/api/v1/schema— schema with PKs, FKs and counts
GET/api/v1/categories?tree=true— the category tree