Креирање и пополнување на база на податоци (DDL, DML)

DDL

CREATE TABLE public.author (
    author_id BIGINT NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    birth_date DATE,
    nationality VARCHAR(100),
    CONSTRAINT author_pkey PRIMARY KEY (author_id)
    CONSTRAINT uq_author_name UNIQUE (first_name, last_name), 
    CONSTRAINT chk_first_name_length CHECK (LENGTH(TRIM(first_name)) BETWEEN 2 AND 100), 
    CONSTRAINT chk_last_name_length CHECK (LENGTH(TRIM(last_name)) BETWEEN 2 AND 100)
);

CREATE TABLE public.author_book (
    author_id BIGINT NOT NULL,
    book_id BIGINT NOT NULL,
    CONSTRAINT author_book_pkey PRIMARY KEY (author_id, book_id),
    CONSTRAINT author_book_author_id_fkey
        FOREIGN KEY (author_id)
        REFERENCES public.author(author_id)
);

CREATE TABLE public.book (
    book_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    isbn VARCHAR(20) NOT NULL,
    publication_year INT NOT NULL,
    total_copies INT NOT NULL,
    edition BIGINT,
    publisher_id BIGINT,
    author_id BIGINT,
    location_id BIGINT,
    CONSTRAINT uq_book_title UNIQUE (title),
    CONSTRAINT uq_book_isbn UNIQUE (isbn),
    CONSTRAINT book_pkey PRIMARY KEY (book_id),
    CONSTRAINT fk_author
        FOREIGN KEY (author_id)
        REFERENCES public.author(author_id),
    CONSTRAINT fk_location
        FOREIGN KEY (location_id)
        REFERENCES public.location(location_id),
    CONSTRAINT fk_publisher
        FOREIGN KEY (publisher_id)
        REFERENCES public.publisher(publisher_id)
);

CREATE TABLE public.bookcopy (
    copy_id BIGINT NOT NULL,
    book_id BIGINT,
    location_id BIGINT,
    available INT,
    status_id BIGINT,
    library_id BIGINT,
    reservation_id INT,
    CONSTRAINT bookcopy_pkey PRIMARY KEY (copy_id),
    CONSTRAINT bookcopy_library_id_fkey
        FOREIGN KEY (library_id)
        REFERENCES public.library(library_id),
    CONSTRAINT bookcopy_location_id_fkey
        FOREIGN KEY (location_id)
        REFERENCES public.location(location_id),
    CONSTRAINT bookcopy_status_id_fkey
        FOREIGN KEY (status_id)
        REFERENCES public.bookstatus(status_id),
    CONSTRAINT fk_bookcopy_reservation
        FOREIGN KEY (reservation_id)
        REFERENCES public.reservation(reservation_id)
);

CREATE TABLE public.bookgenre (
    book_genre_id BIGINT NOT NULL,
    book_id BIGINT,
    genre_id BIGINT,
    CONSTRAINT bookgenre_pkey PRIMARY KEY (book_genre_id),
    CONSTRAINT bookgenre_genre_id_fkey
        FOREIGN KEY (genre_id)
        REFERENCES public.genre(genre_id)
);

CREATE TABLE public.booklanguages (
    book_language_id BIGINT NOT NULL,
    book_id BIGINT,
    language_id BIGINT,
    CONSTRAINT booklanguages_pkey PRIMARY KEY (book_language_id),
    CONSTRAINT booklanguages_language_id_fkey
        FOREIGN KEY (language_id)
        REFERENCES public.language(language_id)
);

CREATE TABLE public.bookstatus (
    status_id BIGINT NOT NULL,
    name VARCHAR(50),
    description TEXT,
    CONSTRAINT bookstatus_pkey PRIMARY KEY (status_id)
);

CREATE TABLE public.borrowing (
    borrowing_id BIGINT NOT NULL DEFAULT nextval('borrowing_seq'::regclass),
    book_id BIGINT,
    borrow_date DATE,
    return_date DATE,
    due_date DATE,
    return_status INT,
    copy_id BIGINT,
    membership_id BIGINT,
    borrowed_by BIGINT,
    returned_by BIGINT,
    reservation_id BIGINT,
    CONSTRAINT borrowing_pkey PRIMARY KEY (borrowing_id),
    CONSTRAINT borrowing_borrowed_by_fkey
        FOREIGN KEY (borrowed_by)
        REFERENCES public.employee(employee_id),
    CONSTRAINT chk_borrowing_dates
          CHECK (
                 due_date >= borrow_date
                 AND (return_date IS NULL OR return_date >= borrow_date)
                ),
    CONSTRAINT borrowing_copy_id_fkey
        FOREIGN KEY (copy_id)
        REFERENCES public.bookcopy(copy_id),
    CONSTRAINT borrowing_membership_id_fkey
        FOREIGN KEY (membership_id)
        REFERENCES public.membership(membership_id),
    CONSTRAINT borrowing_reservation_id_fkey
        FOREIGN KEY (reservation_id)
        REFERENCES public.reservation(reservation_id),
    CONSTRAINT uq_borrowing_reservation
        UNIQUE (reservation_id),
    CONSTRAINT borrowing_returned_by_fkey
        FOREIGN KEY (returned_by)
        REFERENCES public.employee(employee_id)
);

CREATE TABLE public.employee (
    employee_id BIGINT NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(150),
    phone VARCHAR(50),
    employee_type_id BIGINT,
    hire_date DATE,
    salary INT,
    CONSTRAINT employee_pkey PRIMARY KEY (employee_id),
    CONSTRAINT employee_employee_type_id_fkey
        FOREIGN KEY (employee_type_id)
        REFERENCES public.employeetype(employee_type_id)
);

CREATE TABLE public.employeetype (
    employee_type_id BIGINT NOT NULL,
    type_name VARCHAR(100),
    description TEXT,
    CONSTRAINT employeetype_pkey PRIMARY KEY (employee_type_id)
);

CREATE TABLE public.fee (
    fee_id BIGINT NOT NULL,
    amount INT,
    description TEXT,
    fee_type_id BIGINT,
    membership_id BIGINT,
    CONSTRAINT fee_pkey PRIMARY KEY (fee_id),
    CONSTRAINT fee_fee_type_id_fkey
        FOREIGN KEY (fee_type_id)
        REFERENCES public.feetype(fee_type_id),
    CONSTRAINT fee_membership_id_fkey
        FOREIGN KEY (membership_id)
        REFERENCES public.membership(membership_id)
);

CREATE TABLE public.feetype (
    fee_type_id BIGINT NOT NULL,
    type_name VARCHAR(255),
    description TEXT,
    CONSTRAINT feetype_pkey PRIMARY KEY (fee_type_id)
);

CREATE TABLE public.fine (
    fine_id BIGINT NOT NULL,
    membership_id BIGINT,
    borrowing_id BIGINT,
    amount INT,
    created_date DATE,
    payment_id BIGINT,
    fee_type_id BIGINT,
    CONSTRAINT fine_pkey PRIMARY KEY (fine_id),
    CONSTRAINT fine_borrowing_id_fkey
        FOREIGN KEY (borrowing_id)
        REFERENCES public.borrowing(borrowing_id),
    CONSTRAINT fine_fee_type_id_fkey
        FOREIGN KEY (fee_type_id)
        REFERENCES public.feetype(fee_type_id),
    CONSTRAINT fine_membership_id_fkey
        FOREIGN KEY (membership_id)
        REFERENCES public.membership(membership_id),
    CONSTRAINT fine_payment_id_fkey
        FOREIGN KEY (payment_id)
        REFERENCES public.payment(payment_id)
);

CREATE TABLE public.genre (
    genre_id BIGSERIAL NOT NULL,
    name VARCHAR(100),
    description TEXT,
    CONSTRAINT genre_pkey PRIMARY KEY (genre_id)
);

CREATE TABLE public.language (
    language_id BIGINT NOT NULL,
    name VARCHAR(100),
    code VARCHAR(10),
    native_name VARCHAR(100),
    CONSTRAINT language_pkey PRIMARY KEY (language_id)
);

CREATE TABLE public.library (
    library_id BIGINT NOT NULL,
    name VARCHAR(255),
    address TEXT,
    contact_email VARCHAR(150),
    CONSTRAINT library_pkey PRIMARY KEY (library_id)
);

CREATE TABLE public.location (
    location_id BIGINT NOT NULL,
    name VARCHAR(100),
    address TEXT,
    floor INT,
    section VARCHAR(50),
    library_id BIGINT,
    CONSTRAINT location_pkey PRIMARY KEY (location_id),
    CONSTRAINT location_library_id_fkey
        FOREIGN KEY (library_id)
        REFERENCES public.library(library_id)
);

CREATE TABLE public.member (
    member_id BIGINT NOT NULL DEFAULT nextval('member_id_seq'::regclass),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(150),
    phone VARCHAR(50),
    member_type_id BIGINT,
    membership_start_date DATE,
    membership_end_date DATE,
    address TEXT,
    membership_status VARCHAR(20),
    CONSTRAINT member_pkey PRIMARY KEY (member_id),
    CONSTRAINT member_member_type_id_fkey
        FOREIGN KEY (member_type_id)
        REFERENCES public.membershiptype(member_type_id)
);

CREATE TABLE public.membership (
    membership_id BIGINT NOT NULL,
    member_id BIGINT,
    start_date DATE,
    end_date DATE,
    status INT,
    member_type_id BIGINT,
    CONSTRAINT membership_pkey PRIMARY KEY (membership_id),
    CONSTRAINT membership_member_id_fkey
        FOREIGN KEY (member_id)
        REFERENCES public.member(member_id),
    CONSTRAINT membership_member_type_id_fkey
        FOREIGN KEY (member_type_id)
        REFERENCES public.membershiptype(member_type_id)
);

CREATE TABLE public.membershiptype (
    member_type_id BIGINT NOT NULL,
    type_name VARCHAR(100),
    max_borrow_limit INT,
    max_days INT,
    fee_per_day INT,
    CONSTRAINT membershiptype_pkey PRIMARY KEY (member_type_id)
);

CREATE TABLE public.payment (
    payment_id BIGINT NOT NULL,
    member_id BIGINT,
    fee_id BIGINT,
    payment_date DATE,
    amount INT,
    CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
    CONSTRAINT payment_fee_id_fkey
        FOREIGN KEY (fee_id)
        REFERENCES public.fee(fee_id),
    CONSTRAINT payment_member_id_fkey
        FOREIGN KEY (member_id)
        REFERENCES public.member(member_id)
);

CREATE TABLE public.publisher (
    publisher_id BIGINT NOT NULL,
    name VARCHAR(255),
    website VARCHAR(255),
    registration_number VARCHAR(100),
    CONSTRAINT publisher_pkey PRIMARY KEY (publisher_id)
);

CREATE TABLE public.reservation (
    reservation_id BIGINT NOT NULL DEFAULT nextval('reservation_seq'::regclass),
    member_id BIGINT,
    book_id BIGINT,
    requested_date DATE,
    expected_date DATE,
    status CHAR(20),
    copy_id BIGINT,
    CONSTRAINT reservation_pkey PRIMARY KEY (reservation_id),
    CONSTRAINT reservation_copy_id_fkey
        FOREIGN KEY (copy_id)
        REFERENCES public.bookcopy(copy_id),
    CONSTRAINT reservation_member_id_fkey
        FOREIGN KEY (member_id)
        REFERENCES public.member(member_id)

);