SQL Database Schema: Library Data Model

Posted on Mar 7, 2025 in Computing and Communications

Exercise 1: Library Database Schema

This document outlines the SQL code for creating a library database, including tables for publishers, distributors, customers, articles (books and journals), and rentals. It also includes sample queries for data insertion and retrieval.

Database Initialization

DROP DATABASE IF EXISTS library;
CREATE DATABASE IF NOT EXISTS library;
USE library;

Table Creation

Publisher Table

Stores information about publishers.

CREATE TABLE publisher (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phoneNumber INT NOT NULL
) ENGINE=INNODB;

Distributor Table

Stores information about distributors.

CREATE TABLE distributor (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL,
  phoneNumber INT NOT NULL
) ENGINE=INNODB;

Distributor_Publisher Table

A linking table to represent the many-to-many relationship between distributors and publishers.

CREATE TABLE distributor_publisher (
  distributor_id INT,
  publisher_id INT,
  FOREIGN KEY (publisher_id) REFERENCES publisher(ID),
  FOREIGN KEY (distributor_id) REFERENCES distributor(ID),
  PRIMARY KEY (publisher_id, distributor_id)
) ENGINE=INNODB;

Customer Table

CREATE TABLE customer (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(100) NOT NULL
) ENGINE=INNODB;

Article Table

Stores information about articles, which can be books or journals. Includes foreign keys referencing the publisher, customer, and distributor tables.

CREATE TABLE article (
  id INT AUTO_INCREMENT PRIMARY KEY,
  price INT NOT NULL,
  amount INT NOT NULL,
  distributed_by INT NOT NULL,
  bought_by INT NOT NULL,
  published_by INT NOT NULL,
  FOREIGN KEY (published_by) REFERENCES publisher(ID),
  FOREIGN KEY (bought_by) REFERENCES customer(ID),
  FOREIGN KEY (distributed_by) REFERENCES distributor(ID)
) ENGINE=INNODB;

Book Table

Stores information specific to books. It inherits from the article table.

CREATE TABLE book (
  id INT PRIMARY KEY,
  ISBN VARCHAR(20) UNIQUE NOT NULL,
  FOREIGN KEY (id) REFERENCES article(ID)
) ENGINE=INNODB;

Journal Table

Stores information specific to journals. Also inherits from the article table.

CREATE TABLE journal (
  id INT PRIMARY KEY,
  ISSN VARCHAR(24) UNIQUE NOT NULL,
  issue INT NOT NULL,
  FOREIGN KEY (id) REFERENCES article(ID)
) ENGINE=INNODB;

Rent Table

Stores information about article rentals, linking customers and articles.

CREATE TABLE rent (
  customer_id INT,
  article_id INT,
  begin TIMESTAMP,
  end TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customer(ID),
  FOREIGN KEY (article_id) REFERENCES article(ID),
  PRIMARY KEY (customer_id, article_id)
) ENGINE=INNODB;

Sample Queries

The following are sample SQL queries demonstrating common database operations.

B. Insert

Inserts data into the ‘moto’ table (Note: This table is not defined in the provided schema. It’s assumed to exist in a different context, possibly ‘part2022’).

INSERT INTO `part2022`.`moto` (`id`, `matricula`, `marca`, `modelo`,`vehiculo_id`) VALUES ('1', '2342ZZZ', 'Honda', 'CBR600','5');
INSERT INTO `part2022`.`moto` (`id`, `matricula`, `marca`, `modelo`,`vehiculo_id`) VALUES ('2', '3443XXX', 'Honda', 'CBR125','6');
INSERT INTO `part2022`.`moto` (`id`, `matricula`, `marca`, `modelo`,`vehiculo_id`) VALUES ('3', '4943XXX', 'Kawasaki', 'Ninja600','7');

C. Where

Selects the registration number and model from the ‘coche’ table where the brand is “Seat”. (Note: ‘coche’ table is not defined in the library schema.)

SELECT matricula, modelo
FROM coche
WHERE marca = "Seat";

D. Between OR AND

Selects the name and location from the ‘comprador’ table where the budget is between 2000 and 5000, or greater than 2000 and greater than 5000. (Note: The second query should likely use >= 5000, and ‘comprador’ is not in the library schema.)

SELECT nombre, localizacion
FROM comprador
WHERE presupuesto BETWEEN 2000 AND 5000;

SELECT nombre, localizacion
FROM comprador
WHERE presupuesto > 2000 AND presupuesto > 5000;

E. Order By and Limit

Selects the name and budget from the ‘comprador’ table, ordered by budget in ascending order, and limits the results to the top 2. (Note: ‘comprador’ is not in the library schema.)

SELECT nombre, presupuesto
FROM comprador
ORDER BY presupuesto ASC
LIMIT 2;

F. Group By and Count

Selects the city and the count of each city from the ‘concesionario’ table, ordered by the count in descending order. (Note: ‘concesionario’ is not in the library schema.)

SELECT ciudad, COUNT(ciudad)
FROM concesionario
GROUP BY ciudad
ORDER BY COUNT(ciudad) DESC;

G. Inner Join (Distinct)

Selects the distinct names of sellers (‘vendedor’) who work in Barcelona, using an inner join with the ‘concesionario’ table. (Note: ‘vendedor’ and ‘concesionario’ are not in the library schema.)

SELECT DISTINCT vendedor.nombre
FROM vendedor INNER JOIN concesionario
ON vendedor_id = concesionario.vendedor_id
WHERE concesionario.ciudad = "Barcelona";

H. Max and Inner Join

Selects the maximum price (‘precio_maximo’) and all columns from the ‘coche’ table, joining with the ‘vehiculo’ table. (Note: ‘coche’ and ‘vehiculo’ are not in the library schema.)

SELECT MAX(precio) AS precio_maximo, coche.*
FROM coche
INNER JOIN vehiculo
ON coche.id = vehiculo.id;

I. Subquery and Inner Join

Selects the distinct names and locations of buyers (‘comprador’) whose purchase price is less than the average price of all vehicles. (Note: ‘comprador’ and ‘vehiculo’ are not in the library schema.)

SELECT DISTINCT comprador.nombre, comprador.localizacion
FROM vehiculo
INNER JOIN comprador
ON comprador.id = vehiculo.comprador_por
WHERE precio < (SELECT AVG(vehiculo.precio) FROM vehiculo);

J. Nested Joins

Selects the registration number, brand, and model of cars bought by a buyer named “Marina”. Two versions of the query are provided. (Note: ‘comprador’, ‘coche’, and ‘vehiculo’ are not in the library schema.)

SELECT B.matricula, B.marca, B.modelo
FROM comprador A
INNER JOIN
(SELECT vehiculo.comprador_por, coche.matricula, coche.marca, coche.modelo
FROM vehiculo
INNER JOIN coche
ON vehiculo.id = coche.vehiculo_id) B
ON A.id = B.comprador_por
WHERE A.nombre = "Marina";

SELECT co.matricula, co.marca, co.modelo
FROM vehiculo v
INNER JOIN coche co
ON v.id = co.vehiculo_id
INNER JOIN comprador cm
ON cm.id = v.comprador_por
WHERE cm.nombre = "Marina";

K. Nested Joins (Alternative)

Selects the name and location of buyers who have dealt with an intermediary named “María”. Two versions of the query are provided. (Note: ‘intermediario’, ‘comprador’, and ‘vehiculo’ are not in the library schema.)

SELECT B.nombre, B.localizacion
FROM intermediario A
INNER JOIN
(SELECT comprador.nombre, comprador.localizacion, vehiculo.distribuido_por
FROM vehiculo
INNER JOIN comprador
ON vehiculo.comprador_por = comprador.id) B
ON A.id = B.distribuido_por
WHERE A.nombre = "María";

SELECT C.nombre, C.localizacion
FROM intermediario A
INNER JOIN vehiculo V
ON A.id = V.distribuido_por
INNER JOIN comprador C
ON C.ID = V.comprador_por
WHERE A.nombre = "María";

L. Add Index

Adds an index to the ‘precio’ column of the ‘VEHICULO’ table to improve query performance. (Note: ‘VEHICULO’ is not in the library schema.)

ALTER TABLE VEHICULO ADD INDEX (precio);