Changes between Version 4 and Version 5 of ddl

Timestamp:

06/11/26 15:29:36 (5 days ago)

Author:

236036

Comment:

--

ddl

@@ -14 +14 @@
     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)
 );
 
@@ -35 +38 @@
     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
@@ -113 +118 @@
         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)
@@ -122 +132 @@
         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)