Changes between Version 3 and Version 4 of ddl

Timestamp:

06/11/26 13:40:14 (5 days ago)

Author:

236036

Comment:

--

ddl

@@ -4 +4 @@
 
 
-
 {{{#!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)
+    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)
+    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)
-);
-
-...
+    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)
+
+);
 
 }}}