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

DDL

`sql 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)

);

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 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 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 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)

); `