Логички и физички дизајн - Креирање база податоци (со SQL DDL): DDL-Script-Book-Tracker.sql

-- Drop tables if they exist
DROP TABLE IF EXISTS Users CASCADE;
DROP TABLE IF EXISTS Member CASCADE;
DROP TABLE IF EXISTS Book CASCADE;
DROP TABLE IF EXISTS Book_Details CASCADE;
DROP TABLE IF EXISTS Book_Copies CASCADE;
DROP TABLE IF EXISTS Book_Author CASCADE;
DROP TABLE IF EXISTS Author CASCADE;
DROP TABLE IF EXISTS Loan CASCADE;
DROP TABLE IF EXISTS Fine CASCADE;
DROP TABLE IF EXISTS FinePayment CASCADE;
DROP TABLE IF EXISTS Cart CASCADE;

CREATE TABLE Users (
    UserID SERIAL PRIMARY KEY,
    Username TEXT NOT NULL,
    FirstName TEXT,
    LastName TEXT,
    Address TEXT,
    Phone NUMERIC,
    Password TEXT NOT NULL,
    Email TEXT NOT NULL,
    Role TEXT NOT NULL
);

CREATE TABLE Member (
    MemberID SERIAL PRIMARY KEY,
    Expired_Date DATE NOT NULL,
    Membership_Status TEXT NOT NULL CHECK (Membership_Status IN ('Active', 'Inactive', 'Suspended')),
    UserID INT REFERENCES Users(UserID) ON DELETE CASCADE
);

CREATE TABLE Book (
    BookID SERIAL PRIMARY KEY,
    ISBN TEXT NOT NULL,
    Title TEXT NOT NULL,
    Genre TEXT NOT NULL,
    PublishedYear NUMERIC NOT NULL,
    Description TEXT, 
    CoverImage TEXT,
    TotalCopies NUMERIC DEFAULT 0
);

CREATE TABLE Book_Details (
    DetailsID SERIAL PRIMARY KEY,
    BookID INT REFERENCES Book(BookID) ON DELETE CASCADE,
    Format TEXT NOT NULL CHECK (Format IN ('Hardcover', 'Paperback')),
    Language TEXT NOT NULL,
    Publisher TEXT NOT NULL,
    Pages TEXT NOT NULL
);

CREATE TABLE Book_Copies (
    CopyID SERIAL PRIMARY KEY,
    BookID INT REFERENCES Book(BookID) ON DELETE CASCADE,
    Condition TEXT NOT NULL CHECK (Condition IN ('New', 'Good', 'Damaged'))
);

CREATE TABLE Author (
    AuthorID SERIAL PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Nationality TEXT NOT NULL,
    DateOfBirth DATE NOT NULL,
    Author_description text,
    Author_image text
);

CREATE TABLE Book_Author (
    BookID INT REFERENCES Book(BookID) ON DELETE CASCADE,
    AuthorID INT REFERENCES Author(AuthorID) ON DELETE CASCADE,
    PRIMARY KEY (BookID, AuthorID)
);

CREATE TABLE Loan (
    LoanID SERIAL PRIMARY KEY,
    LoanDate DATE NOT NULL,
    ReturnDate DATE,
    Status TEXT NOT NULL,
    BookCopyID INT REFERENCES Book_Copies(CopyID) ON DELETE CASCADE,
    MemberID INT REFERENCES Member(MemberID) ON DELETE CASCADE
);

CREATE TABLE Fine (
    FineID SERIAL PRIMARY KEY,
    FineAmount NUMERIC NOT NULL,
    FineDate DATE NOT NULL,
    Status TEXT NOT NULL,
    LoanID INT REFERENCES Loan(LoanID) ON DELETE CASCADE
);

CREATE TABLE FinePayment (
    FinePaymentID SERIAL PRIMARY KEY,
    PaymentDate DATE NOT NULL,
    PaymentAmount NUMERIC NOT NULL,
    FineID INT REFERENCES Fine(FineID) ON DELETE CASCADE
);

CREATE TABLE Cart (
    CartID SERIAL PRIMARY KEY,       
    BookID INT REFERENCES Book(BookID) ON DELETE CASCADE,
    MemberID INT REFERENCES Member(MemberID) ON DELETE CASCADE
);


CREATE OR REPLACE FUNCTION update_total_copies() 
RETURNS TRIGGER AS $$
BEGIN
    -- If a new copy is inserted, increment the total copies count
    IF TG_OP = 'INSERT' THEN
        UPDATE Book 
        SET TotalCopies = TotalCopies + 1
        WHERE BookID = NEW.BookID;
    -- If a copy is deleted, decrement the total copies count
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE Book 
        SET TotalCopies = TotalCopies - 1
        WHERE BookID = OLD.BookID;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER trigger_update_total_copies
AFTER INSERT OR DELETE ON Book_Copies
FOR EACH ROW EXECUTE FUNCTION update_total_copies();