DDL

Enums

CREATE TYPE GENDER AS ENUM ('male', 'female', 'other');
CREATE TYPE COVER AS ENUM ('hardcover', 'paperback', 'ebook');

Location

CREATE TABLE Location (
id BIGSERIAL PRIMARY KEY,
address VARCHAR(128) NOT NULL,
city VARCHAR(128) NOT NULL
);

Library

CREATE TABLE Library (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL,
location_id BIGINT,
CONSTRAINT FKLibrary_Location FOREIGN KEY (location_id)
REFERENCES Location(id)
ON DELETE SET NULL ON UPDATE CASCADE
);

User

CREATE TABLE "User" (
id BIGSERIAL PRIMARY KEY,
first_name VARCHAR(128) NOT NULL DEFAULT '[deleted_user]',
last_name VARCHAR(128) NOT NULL DEFAULT '[deleted_user]',
password VARCHAR(255) NOT NULL DEFAULT '[deleted]',
email VARCHAR(128) NOT NULL DEFAULT '[deleted_user@example.com]',
date_of_birth DATE,
verified BOOLEAN,
gender GENDER DEFAULT 'other',
deleted BOOLEAN NOT NULL DEFAULT FALSE,
deleted_at TIMESTAMP,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now(),
CONSTRAINT chk_user_dob CHECK (date_of_birth <= CURRENT_DATE),
CONSTRAINT chk_user_email CHECK (email LIKE '%@%')
);

Employee

CREATE TABLE Employee (
id BIGINT PRIMARY KEY,
start_date TIMESTAMP NOT NULL,
salary NUMERIC NOT NULL,
manager_id BIGINT,
library_id BIGINT NOT NULL,
CONSTRAINT FKEmployee_User FOREIGN KEY (id)
REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FKEmployee_Manager FOREIGN KEY (manager_id)
REFERENCES Employee(id) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT FKEmployee_Library FOREIGN KEY (library_id)
REFERENCES Library(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT chk_employee_salary CHECK (salary > 0)
);

MembershipPlan

CREATE TABLE MembershipPlan (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL,
max_rent_period_days INT4 NOT NULL,
max_concurrent_rentals INT4 NOT NULL,
price NUMERIC(8, 2) NOT NULL,
duration_days INT NOT NULL,
CONSTRAINT chk_plan_limits CHECK (max_rent_period_days > 0 AND max_concurrent_rentals > 0 AND duration_days > 0),
CONSTRAINT chk_plan_price CHECK (price >= 0)
);

Membership

CREATE TABLE Membership (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
membership_plan_id BIGINT,
started_at TIMESTAMP NOT NULL,
expires_at TIMESTAMP NOT NULL,
cancelled_at TIMESTAMP,
created_at TIMESTAMP NOT NULL,
CONSTRAINT FKMembership_User FOREIGN KEY (user_id)
REFERENCES "User"(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKMembership_Plan FOREIGN KEY (membership_plan_id)
REFERENCES MembershipPlan(id) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT chk_membership_dates CHECK (expires_at > started_at),
CONSTRAINT chk_membership_cancelled CHECK (cancelled_at >= started_at)
);

AttributeType

CREATE TABLE AttributeType (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL
);

AttributeValue

CREATE TABLE AttributeValue (
id BIGSERIAL PRIMARY KEY,
value VARCHAR(64) NOT NULL
);

AttributePair

CREATE TABLE AttributePair (
id BIGSERIAL PRIMARY KEY,
type_id BIGINT NOT NULL,
value_id BIGINT NOT NULL,
CONSTRAINT FKAttributePair_Type FOREIGN KEY (type_id)
REFERENCES AttributeType(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKAttributePair_Value FOREIGN KEY (value_id)
REFERENCES AttributeValue(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

ResourceType

CREATE TABLE ResourceType (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL
);

Genre

CREATE TABLE Genre (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL
);

Resource

CREATE TABLE Resource (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(256) NOT NULL,
description TEXT ,
resource_type_id BIGINT NOT NULL,
CONSTRAINT FKResource_Type FOREIGN KEY (resource_type_id)
REFERENCES ResourceType(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

Edition

CREATE TABLE Edition (
isbn VARCHAR(20) PRIMARY KEY,
name VARCHAR(512) NOT NULL,
resource_id BIGINT NOT NULL,
publisher VARCHAR(128) NOT NULL,
publish_year INT4 NOT NULL,
format VARCHAR(32) NOT NULL,
language VARCHAR(32) NOT NULL,
num_pages INT NOT NULL,
cover_type COVER NOT NULL DEFAULT 'paperback',
CONSTRAINT FKEdition_Resource FOREIGN KEY (resource_id)
REFERENCES Resource(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT chk_publish_year CHECK (publish_year <= EXTRACT(YEAR FROM CURRENT_DATE))
);

ResourceCopy

CREATE TABLE ResourceCopy (
id BIGSERIAL PRIMARY KEY,
edition_isbn VARCHAR(20) NOT NULL,
library_id BIGINT NOT NULL,
status INT4 NOT NULL,
barcode VARCHAR(64) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
CONSTRAINT FKResourceCopy_Edition FOREIGN KEY (edition_isbn)
REFERENCES Edition(isbn) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FKResourceCopy_Library FOREIGN KEY (library_id)
REFERENCES Library(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT chk_copy_status CHECK (status IN (1,2,3,4))
);

Rental

CREATE TABLE Rental (
id BIGSERIAL PRIMARY KEY,
resource_copy_id BIGINT NOT NULL,
rented_at TIMESTAMP NOT NULL,
due_at TIMESTAMP NOT NULL,
returned_at TIMESTAMP,
membership_id BIGINT NOT NULL,
penalty NUMERIC(19, 0),
employee_id BIGINT,
CONSTRAINT FKRental_Copy FOREIGN KEY (resource_copy_id)
REFERENCES ResourceCopy(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKRental_Membership FOREIGN KEY (membership_id)
REFERENCES Membership(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKRental_Employee FOREIGN KEY (employee_id)
REFERENCES Employee(id) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT chk_rental_due_date CHECK (due_at > rented_at),
CONSTRAINT chk_rental_returned_date CHECK (returned_at >= rented_at),
CONSTRAINT chk_rental_penalty CHECK (penalty >= 0)
);

EventType

CREATE TABLE EventType (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(128) NOT NULL
);

Room

CREATE TABLE Room (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(128),
library_id BIGINT NOT NULL,
CONSTRAINT FKRoom_Library FOREIGN KEY (library_id)
REFERENCES Library(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

Event

CREATE TABLE Event (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(128) NOT NULL,
description TEXT NOT NULL,
library_id BIGINT NOT NULL,
event_type_id BIGINT NOT NULL,
capacity INT4 NOT NULL,
start_time TIMESTAMP NOT NULL,
duration INT4 NOT NULL,
event_room_id BIGINT NOT NULL,
CONSTRAINT FKEvent_Type FOREIGN KEY (event_type_id)
REFERENCES EventType(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKEvent_Library FOREIGN KEY (library_id)
REFERENCES Library(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKEvent_Room FOREIGN KEY (event_room_id)
REFERENCES Room(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT chk_event_capacity CHECK (capacity > 0),
CONSTRAINT chk_event_duration CHECK (duration > 0)
);

EventRegistration

CREATE TABLE EventRegistration (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
event_id BIGINT NOT NULL,
registered_at TIMESTAMP NOT NULL,
CONSTRAINT FKEventReg_User FOREIGN KEY (user_id)
REFERENCES "User"(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKEventReg_Event FOREIGN KEY (event_id)
REFERENCES Event(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

Review

CREATE TABLE Review (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
resource_edition_isbn VARCHAR(20) NOT NULL,
rating INT4 NOT NULL,
review_text TEXT NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
CONSTRAINT FKReview_User FOREIGN KEY (user_id)
REFERENCES "User"(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKReview_Edition FOREIGN KEY (resource_edition_isbn)
REFERENCES Edition(isbn) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT chk_review_rating CHECK (rating BETWEEN 1 AND 5)
);

Reply

CREATE TABLE Reply (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
review_id BIGINT NOT NULL,
text TEXT NOT NULL,
create_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL,
deleted BOOLEAN NOT NULL,
parent_id BIGINT,
CONSTRAINT FKReply_User FOREIGN KEY (user_id)
REFERENCES "User"(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKReply_Review FOREIGN KEY (review_id)
REFERENCES Review(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKReply_Parent FOREIGN KEY (parent_id)
REFERENCES Reply(id) ON DELETE SET NULL ON UPDATE CASCADE
);

Author

CREATE TABLE Author (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(64),
surname VARCHAR(64),
artistic_name VARCHAR(64),
date_of_birth DATE,
CONSTRAINT chk_author_dob CHECK (date_of_birth <= CURRENT_DATE)
);

Wishlist

CREATE TABLE Wishlist (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
CONSTRAINT FKWishlist_User FOREIGN KEY (user_id)
REFERENCES "User"(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

Shelf

CREATE TABLE Shelf (
id BIGSERIAL PRIMARY KEY,
room_id BIGINT NOT NULL,
genre_id BIGINT NOT NULL,
CONSTRAINT FKShelf_Room FOREIGN KEY (room_id)
REFERENCES Room(id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT FKShelf_Genre FOREIGN KEY (genre_id)
REFERENCES Genre(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

Chapter

CREATE TABLE Chapter (
id BIGSERIAL PRIMARY KEY,
edition_isbn VARCHAR(20) NOT NULL,
name VARCHAR(128),
number INT4,
CONSTRAINT FKChapter_Edition FOREIGN KEY (edition_isbn)
REFERENCES Edition(isbn) ON DELETE RESTRICT ON UPDATE CASCADE
);

Resource_Genres

CREATE TABLE Resource_Genres (
ResourceId BIGINT NOT NULL,
GenreId BIGINT NOT NULL,
PRIMARY KEY (ResourceId, GenreId),
FOREIGN KEY (ResourceId) REFERENCES Resource(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (GenreId) REFERENCES Genre(id) ON DELETE CASCADE ON UPDATE CASCADE
);

Edition_Attributes

CREATE TABLE Edition_Attributes (
edition_id VARCHAR(20) NOT NULL,
attribute_pair_id BIGINT NOT NULL,
PRIMARY KEY (edition_id, attribute_pair_id),
FOREIGN KEY (edition_id) REFERENCES Edition(isbn) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (attribute_pair_id) REFERENCES AttributePair(id) ON DELETE CASCADE ON UPDATE CASCADE
);

Wishlist_entry

CREATE TABLE Wishlist_entry (
edition_isbn VARCHAR(20) NOT NULL,
wishlist_id BIGINT NOT NULL,
PRIMARY KEY (edition_isbn, wishlist_id),
FOREIGN KEY (edition_isbn) REFERENCES Edition(isbn) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (wishlist_id) REFERENCES Wishlist(id) ON DELETE CASCADE ON UPDATE CASCADE
);

Chapter_authors

CREATE TABLE Chapter_authors (
chapter_id BIGINT NOT NULL,
author_id BIGINT NOT NULL,
PRIMARY KEY (chapter_id, author_id),
FOREIGN KEY (chapter_id) REFERENCES Chapter(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (author_id) REFERENCES Author(id) ON DELETE CASCADE ON UPDATE CASCADE
);

Edition_authors

CREATE TABLE Edition_authors (
edition_id VARCHAR(20) NOT NULL,
author_id BIGINT NOT NULL,
PRIMARY KEY (edition_id, author_id),
FOREIGN KEY (edition_id) REFERENCES Edition(isbn) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (author_id) REFERENCES Author(id) ON DELETE CASCADE ON UPDATE CASCADE
);

Views

vw_edition_copy_count

CREATE VIEW vw_edition_copy_count AS
SELECT
e.isbn,
e.name,
COUNT(rc.id) AS total_copies
FROM Edition e
LEFT JOIN ResourceCopy rc
ON rc.edition_isbn = e.isbn
GROUP BY e.isbn, e.name;

vw_active_memberships

CREATE VIEW vw_active_memberships AS
SELECT
m.id,
m.user_id,
u.first_name,
u.last_name,
u.email,
m.membership_plan_id,
mp.name AS membership_name,
m.started_at,
m.expires_at
FROM Membership m
JOIN "User" u ON m.user_id = u.id
JOIN membershipplan mp ON mp.id = m.membership_plan_id
WHERE m.cancelled_at IS NULL
AND m.expires_at > NOW();

vw_active_rentals

CREATE VIEW vw_active_rentals AS
SELECT
r.id,
r.resource_copy_id,
e.name,
r.membership_id,
u.email,
r.rented_at,
r.due_at
FROM Rental r
JOIN resourcecopy rc ON r.resource_copy_id = rc.id
JOIN edition e ON e.isbn = rc.edition_isbn
JOIN membership m ON m.id = r.membership_id
JOIN "User" u ON u.id = m.user_id
WHERE r.returned_at IS NULL;

vw_sorted_by_rentals_rented_editions

CREATE VIEW vw_sorted_by_rentals_rented_editions AS
SELECT
e.isbn,
e.name,
COUNT(r.id) AS rental_count
FROM Rental r
JOIN ResourceCopy rc
ON r.resource_copy_id = rc.id
JOIN Edition e
ON rc.edition_isbn = e.isbn
GROUP BY e.isbn, e.name;

vw_edition_ratings

CREATE VIEW vw_edition_ratings AS
SELECT
e.isbn,
e.name,
AVG(r.rating) AS avg_rating,
COUNT(r.id) AS review_count
FROM Edition e
LEFT JOIN Review r
ON r.resource_edition_isbn = e.isbn
GROUP BY e.isbn, e.name;

vw_user_activity

CREATE VIEW vw_user_activity AS
SELECT
u.id AS user_id,
u.email,
COUNT(DISTINCT r.id) AS total_rentals,
COUNT(DISTINCT rev.id) AS total_reviews,
COUNT(DISTINCT er.id) AS total_event_registrations
FROM "User" u
LEFT JOIN Membership m
ON m.user_id = u.id
LEFT JOIN Rental r
ON r.membership_id = m.id
LEFT JOIN Review rev
ON rev.user_id = u.id
LEFT JOIN EventRegistration er
ON er.user_id = u.id
GROUP BY u.id;

vw_overdue_rentals

CREATE VIEW vw_overdue_rentals AS
SELECT
r.id,
r.resource_copy_id,
e.name,
r.membership_id,
u.email,
r.due_at
FROM Rental r
JOIN resourcecopy rc ON rc.id = r.resource_copy_id
JOIN edition e ON e.isbn = rc.edition_isbn
JOIN membership m ON m.id = r.membership_id
JOIN "User" u ON u.id = m.user_id
WHERE r.returned_at IS NULL
AND r.due_at < NOW();

vw_available_editions_for_rent

CREATE VIEW vw_available_editions_for_rent AS
SELECT
e.isbn,
e.name,
COUNT(rc.id) AS total_copies,
COUNT(ar.id) AS currently_rented_copies,
(COUNT(rc.id) - COUNT(ar.id)) AS available_copies
FROM Edition e
LEFT JOIN ResourceCopy rc
ON rc.edition_isbn = e.isbn
LEFT JOIN Rental r
ON r.resource_copy_id = rc.id
AND r.returned_at IS NULL
LEFT JOIN vw_active_rentals ar
ON ar.resource_copy_id = rc.id
GROUP BY e.isbn, e.name
HAVING (COUNT(rc.id) - COUNT(ar.id)) > 0;

vw_future_events_for_lib

CREATE VIEW vw_future_events_for_lib AS
SELECT e.name AS event_name,
e.description AS event_description,
et.name AS type_of_event,
l.name AS library_name,
e.capacity AS event_capacity,
e.start_time AS start_date_time,
r.name AS room_name
FROM event AS e
JOIN library AS l ON e.library_id = l.id
JOIN room AS r ON r.id = e.event_room_id
JOIN eventtype AS et ON et.id = e.event_type_id
WHERE e.start_time >= now()
ORDER BY start_time;