Changes between Version 2 and Version 3 of ddl


Ignore:
Timestamp:
06/11/26 13:38:00 (5 days ago)
Author:
236036
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • ddl

    v2 v3  
    55
    66
    7 ```sql
     7{{{#!sql
     8
    89CREATE TABLE public.author (
    9     author_id BIGINT NOT NULL,
    10     first_name VARCHAR(100),
    11     last_name VARCHAR(100),
    12     birth_date DATE,
    13     nationality VARCHAR(100),
    14     CONSTRAINT author_pkey PRIMARY KEY (author_id)
     10author_id BIGINT NOT NULL,
     11first_name VARCHAR(100),
     12last_name VARCHAR(100),
     13birth_date DATE,
     14nationality VARCHAR(100),
     15CONSTRAINT author_pkey PRIMARY KEY (author_id)
    1516);
    1617
    1718CREATE TABLE public.author_book (
    18     author_id BIGINT NOT NULL,
    19     book_id BIGINT NOT NULL,
    20     CONSTRAINT author_book_pkey PRIMARY KEY (author_id, book_id),
    21     CONSTRAINT author_book_author_id_fkey
    22         FOREIGN KEY (author_id)
    23         REFERENCES public.author(author_id)
     19author_id BIGINT NOT NULL,
     20book_id BIGINT NOT NULL,
     21CONSTRAINT author_book_pkey PRIMARY KEY (author_id, book_id),
     22CONSTRAINT author_book_author_id_fkey
     23FOREIGN KEY (author_id)
     24REFERENCES public.author(author_id)
    2425);
    2526
    2627CREATE TABLE public.book (
    27     book_id BIGINT NOT NULL,
    28     title VARCHAR(255) NOT NULL,
    29     isbn VARCHAR(20) NOT NULL,
    30     publication_year INT NOT NULL,
    31     total_copies INT NOT NULL,
    32     edition BIGINT,
    33     publisher_id BIGINT,
    34     author_id BIGINT,
    35     location_id BIGINT,
    36     CONSTRAINT book_pkey PRIMARY KEY (book_id),
    37     CONSTRAINT fk_author
    38         FOREIGN KEY (author_id)
    39         REFERENCES public.author(author_id),
    40     CONSTRAINT fk_location
    41         FOREIGN KEY (location_id)
    42         REFERENCES public.location(location_id),
    43     CONSTRAINT fk_publisher
    44         FOREIGN KEY (publisher_id)
    45         REFERENCES public.publisher(publisher_id)
     28book_id BIGINT NOT NULL,
     29title VARCHAR(255) NOT NULL,
     30isbn VARCHAR(20) NOT NULL,
     31publication_year INT NOT NULL,
     32total_copies INT NOT NULL,
     33edition BIGINT,
     34publisher_id BIGINT,
     35author_id BIGINT,
     36location_id BIGINT,
     37CONSTRAINT book_pkey PRIMARY KEY (book_id)
    4638);
    4739
    48 CREATE TABLE public.bookcopy (
    49     copy_id BIGINT NOT NULL,
    50     book_id BIGINT,
    51     location_id BIGINT,
    52     available INT,
    53     status_id BIGINT,
    54     library_id BIGINT,
    55     reservation_id INT,
    56     CONSTRAINT bookcopy_pkey PRIMARY KEY (copy_id),
    57     CONSTRAINT bookcopy_library_id_fkey
    58         FOREIGN KEY (library_id)
    59         REFERENCES public.library(library_id),
    60     CONSTRAINT bookcopy_location_id_fkey
    61         FOREIGN KEY (location_id)
    62         REFERENCES public.location(location_id),
    63     CONSTRAINT bookcopy_status_id_fkey
    64         FOREIGN KEY (status_id)
    65         REFERENCES public.bookstatus(status_id),
    66     CONSTRAINT fk_bookcopy_reservation
    67         FOREIGN KEY (reservation_id)
    68         REFERENCES public.reservation(reservation_id)
    69 );
     40...
    7041
    71 CREATE TABLE public.bookgenre (
    72     book_genre_id BIGINT NOT NULL,
    73     book_id BIGINT,
    74     genre_id BIGINT,
    75     CONSTRAINT bookgenre_pkey PRIMARY KEY (book_genre_id),
    76     CONSTRAINT bookgenre_genre_id_fkey
    77         FOREIGN KEY (genre_id)
    78         REFERENCES public.genre(genre_id)
    79 );
    80 
    81 CREATE TABLE public.booklanguages (
    82     book_language_id BIGINT NOT NULL,
    83     book_id BIGINT,
    84     language_id BIGINT,
    85     CONSTRAINT booklanguages_pkey PRIMARY KEY (book_language_id),
    86     CONSTRAINT booklanguages_language_id_fkey
    87         FOREIGN KEY (language_id)
    88         REFERENCES public.language(language_id)
    89 );
    90 
    91 CREATE TABLE public.bookstatus (
    92     status_id BIGINT NOT NULL,
    93     name VARCHAR(50),
    94     description TEXT,
    95     CONSTRAINT bookstatus_pkey PRIMARY KEY (status_id)
    96 );
    97 
    98 CREATE TABLE public.borrowing (
    99     borrowing_id BIGINT NOT NULL DEFAULT nextval('borrowing_seq'::regclass),
    100     book_id BIGINT,
    101     borrow_date DATE,
    102     return_date DATE,
    103     due_date DATE,
    104     return_status INT,
    105     copy_id BIGINT,
    106     membership_id BIGINT,
    107     borrowed_by BIGINT,
    108     returned_by BIGINT,
    109     reservation_id BIGINT,
    110     CONSTRAINT borrowing_pkey PRIMARY KEY (borrowing_id),
    111     CONSTRAINT borrowing_borrowed_by_fkey
    112         FOREIGN KEY (borrowed_by)
    113         REFERENCES public.employee(employee_id),
    114     CONSTRAINT borrowing_copy_id_fkey
    115         FOREIGN KEY (copy_id)
    116         REFERENCES public.bookcopy(copy_id),
    117     CONSTRAINT borrowing_membership_id_fkey
    118         FOREIGN KEY (membership_id)
    119         REFERENCES public.membership(membership_id),
    120     CONSTRAINT borrowing_reservation_id_fkey
    121         FOREIGN KEY (reservation_id)
    122         REFERENCES public.reservation(reservation_id),
    123     CONSTRAINT borrowing_returned_by_fkey
    124         FOREIGN KEY (returned_by)
    125         REFERENCES public.employee(employee_id)
    126 );
    127 
    128 CREATE TABLE public.employee (
    129     employee_id BIGINT NOT NULL,
    130     first_name VARCHAR(100),
    131     last_name VARCHAR(100),
    132     email VARCHAR(150),
    133     phone VARCHAR(50),
    134     employee_type_id BIGINT,
    135     hire_date DATE,
    136     salary INT,
    137     CONSTRAINT employee_pkey PRIMARY KEY (employee_id),
    138     CONSTRAINT employee_employee_type_id_fkey
    139         FOREIGN KEY (employee_type_id)
    140         REFERENCES public.employeetype(employee_type_id)
    141 );
    142 
    143 CREATE TABLE public.employeetype (
    144     employee_type_id BIGINT NOT NULL,
    145     type_name VARCHAR(100),
    146     description TEXT,
    147     CONSTRAINT employeetype_pkey PRIMARY KEY (employee_type_id)
    148 );
    149 
    150 CREATE TABLE public.fee (
    151     fee_id BIGINT NOT NULL,
    152     amount INT,
    153     description TEXT,
    154     fee_type_id BIGINT,
    155     membership_id BIGINT,
    156     CONSTRAINT fee_pkey PRIMARY KEY (fee_id),
    157     CONSTRAINT fee_fee_type_id_fkey
    158         FOREIGN KEY (fee_type_id)
    159         REFERENCES public.feetype(fee_type_id),
    160     CONSTRAINT fee_membership_id_fkey
    161         FOREIGN KEY (membership_id)
    162         REFERENCES public.membership(membership_id)
    163 );
    164 
    165 CREATE TABLE public.feetype (
    166     fee_type_id BIGINT NOT NULL,
    167     type_name VARCHAR(255),
    168     description TEXT,
    169     CONSTRAINT feetype_pkey PRIMARY KEY (fee_type_id)
    170 );
    171 
    172 CREATE TABLE public.fine (
    173     fine_id BIGINT NOT NULL,
    174     membership_id BIGINT,
    175     borrowing_id BIGINT,
    176     amount INT,
    177     created_date DATE,
    178     payment_id BIGINT,
    179     fee_type_id BIGINT,
    180     CONSTRAINT fine_pkey PRIMARY KEY (fine_id),
    181     CONSTRAINT fine_borrowing_id_fkey
    182         FOREIGN KEY (borrowing_id)
    183         REFERENCES public.borrowing(borrowing_id),
    184     CONSTRAINT fine_fee_type_id_fkey
    185         FOREIGN KEY (fee_type_id)
    186         REFERENCES public.feetype(fee_type_id),
    187     CONSTRAINT fine_membership_id_fkey
    188         FOREIGN KEY (membership_id)
    189         REFERENCES public.membership(membership_id),
    190     CONSTRAINT fine_payment_id_fkey
    191         FOREIGN KEY (payment_id)
    192         REFERENCES public.payment(payment_id)
    193 );
    194 
    195 CREATE TABLE public.genre (
    196     genre_id BIGSERIAL NOT NULL,
    197     name VARCHAR(100),
    198     description TEXT,
    199     CONSTRAINT genre_pkey PRIMARY KEY (genre_id)
    200 );
    201 
    202 CREATE TABLE public.language (
    203     language_id BIGINT NOT NULL,
    204     name VARCHAR(100),
    205     code VARCHAR(10),
    206     native_name VARCHAR(100),
    207     CONSTRAINT language_pkey PRIMARY KEY (language_id)
    208 );
    209 
    210 CREATE TABLE public.library (
    211     library_id BIGINT NOT NULL,
    212     name VARCHAR(255),
    213     address TEXT,
    214     contact_email VARCHAR(150),
    215     CONSTRAINT library_pkey PRIMARY KEY (library_id)
    216 );
    217 
    218 CREATE TABLE public.location (
    219     location_id BIGINT NOT NULL,
    220     name VARCHAR(100),
    221     address TEXT,
    222     floor INT,
    223     section VARCHAR(50),
    224     library_id BIGINT,
    225     CONSTRAINT location_pkey PRIMARY KEY (location_id),
    226     CONSTRAINT location_library_id_fkey
    227         FOREIGN KEY (library_id)
    228         REFERENCES public.library(library_id)
    229 );
    230 
    231 CREATE TABLE public.member (
    232     member_id BIGINT NOT NULL DEFAULT nextval('member_id_seq'::regclass),
    233     first_name VARCHAR(100),
    234     last_name VARCHAR(100),
    235     email VARCHAR(150),
    236     phone VARCHAR(50),
    237     member_type_id BIGINT,
    238     membership_start_date DATE,
    239     membership_end_date DATE,
    240     address TEXT,
    241     membership_status VARCHAR(20),
    242     CONSTRAINT member_pkey PRIMARY KEY (member_id),
    243     CONSTRAINT member_member_type_id_fkey
    244         FOREIGN KEY (member_type_id)
    245         REFERENCES public.membershiptype(member_type_id)
    246 );
    247 
    248 CREATE TABLE public.membership (
    249     membership_id BIGINT NOT NULL,
    250     member_id BIGINT,
    251     start_date DATE,
    252     end_date DATE,
    253     status INT,
    254     member_type_id BIGINT,
    255     CONSTRAINT membership_pkey PRIMARY KEY (membership_id),
    256     CONSTRAINT membership_member_id_fkey
    257         FOREIGN KEY (member_id)
    258         REFERENCES public.member(member_id),
    259     CONSTRAINT membership_member_type_id_fkey
    260         FOREIGN KEY (member_type_id)
    261         REFERENCES public.membershiptype(member_type_id)
    262 );
    263 
    264 CREATE TABLE public.membershiptype (
    265     member_type_id BIGINT NOT NULL,
    266     type_name VARCHAR(100),
    267     max_borrow_limit INT,
    268     max_days INT,
    269     fee_per_day INT,
    270     CONSTRAINT membershiptype_pkey PRIMARY KEY (member_type_id)
    271 );
    272 
    273 CREATE TABLE public.payment (
    274     payment_id BIGINT NOT NULL,
    275     member_id BIGINT,
    276     fee_id BIGINT,
    277     payment_date DATE,
    278     amount INT,
    279     CONSTRAINT payment_pkey PRIMARY KEY (payment_id),
    280     CONSTRAINT payment_fee_id_fkey
    281         FOREIGN KEY (fee_id)
    282         REFERENCES public.fee(fee_id),
    283     CONSTRAINT payment_member_id_fkey
    284         FOREIGN KEY (member_id)
    285         REFERENCES public.member(member_id)
    286 );
    287 
    288 CREATE TABLE public.publisher (
    289     publisher_id BIGINT NOT NULL,
    290     name VARCHAR(255),
    291     website VARCHAR(255),
    292     registration_number VARCHAR(100),
    293     CONSTRAINT publisher_pkey PRIMARY KEY (publisher_id)
    294 );
    295 
    296 CREATE TABLE public.reservation (
    297     reservation_id BIGINT NOT NULL DEFAULT nextval('reservation_seq'::regclass),
    298     member_id BIGINT,
    299     book_id BIGINT,
    300     requested_date DATE,
    301     expected_date DATE,
    302     status CHAR(20),
    303     copy_id BIGINT,
    304     CONSTRAINT reservation_pkey PRIMARY KEY (reservation_id),
    305     CONSTRAINT reservation_copy_id_fkey
    306         FOREIGN KEY (copy_id)
    307         REFERENCES public.bookcopy(copy_id),
    308     CONSTRAINT reservation_member_id_fkey
    309         FOREIGN KEY (member_id)
    310         REFERENCES public.member(member_id)
    311 );
    312 ```
     42}}}