Changes between Initial Version and Version 1 of DatabaseCreation

Timestamp:

06/07/26 15:37:00 (10 days ago)

Author:

231062

Comment:

--

DatabaseCreation

@@ +1 @@
+**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;
+}}}