Changes between Version 3 and Version 4 of LogicalAndPhysicalDesign


Ignore:
Timestamp:
12/10/25 00:21:01 (3 weeks ago)
Author:
231035
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • LogicalAndPhysicalDesign

    v3 v4  
    66
    77CREATE TABLE User (
    8     user_id      BIGSERIAL PRIMARY KEY,
     8    user_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    99    username     VARCHAR(50)  NOT NULL UNIQUE,
    1010    email        VARCHAR(255) NOT NULL UNIQUE,
    … …  
    1818
    1919CREATE TABLE VetClinic (
    20     clinic_id BIGSERIAL PRIMARY KEY,
     20    clinic_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    2121    name      VARCHAR(100) NOT NULL,
    2222    address   VARCHAR(200),
    … …  
    2828
    2929CREATE TABLE Animal (
    30     animal_id     BIGSERIAL PRIMARY KEY,
     30    animal_id     BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    3131    owner_id      BIGINT     NOT NULL,
    3232    name          VARCHAR(80) NOT NULL,
    … …  
    4040    CONSTRAINT fk_animal_owner
    4141        FOREIGN KEY (owner_id)
    42         REFERENCES "User"(user_id)
     42        REFERENCES User(user_id)
    4343        ON DELETE CASCADE
    4444);
    … …  
    4646
    4747CREATE TABLE Listing (
    48     listing_id  BIGSERIAL PRIMARY KEY,
     48    listing_id  BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    4949    user_id     BIGINT     NOT NULL, 
    5050    animal_id   BIGINT     NOT NULL, 
    … …  
    5555    CONSTRAINT fk_listing_user
    5656        FOREIGN KEY (user_id)
    57         REFERENCES "User"(user_id)
     57        REFERENCES User(user_id)
    5858        ON DELETE CASCADE,
    5959    CONSTRAINT fk_listing_animal
    6060        FOREIGN KEY (animal_id)
    61         REFERENCES "Animal"(animal_id)
     61        REFERENCES Animal(animal_id)
    6262        ON DELETE CASCADE
    6363);
    6464
    6565CREATE TABLE Appointment (
    66     appointment_id BIGSERIAL PRIMARY KEY,
     66    appointment_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    6767    animal_id      BIGINT     NOT NULL,
    6868    clinic_id      BIGINT     NOT NULL,
    … …  
    7272    CONSTRAINT fk_appt_animal
    7373        FOREIGN KEY (animal_id)
    74         REFERENCES "Animal"(animal_id)
     74        REFERENCES Animal(animal_id)
    7575        ON DELETE CASCADE,
    7676    CONSTRAINT fk_appt_clinic
    7777        FOREIGN KEY (clinic_id)
    78         REFERENCES "VetClinic"(clinic_id)
     78        REFERENCES VetClinic(clinic_id)
    7979        ON DELETE CASCADE
    8080);
    … …  
    8282
    8383CREATE TABLE HealthRecord (
    84     healthrecord_id BIGSERIAL PRIMARY KEY,
     84    healthrecord_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    8585    animal_id       BIGINT     NOT NULL,
    8686    appointment_id  BIGINT,
    … …  
    9191    CONSTRAINT fk_hr_animal
    9292        FOREIGN KEY (animal_id)
    93         REFERENCES "Animal"(animal_id)
     93        REFERENCES Animal(animal_id)
    9494        ON DELETE CASCADE,
    9595    CONSTRAINT fk_hr_appt
    9696        FOREIGN KEY (appointment_id)
    97         REFERENCES "Appointment"(appointment_id)
     97        REFERENCES Appointment(appointment_id)
    9898        ON DELETE SET NULL,
    9999    CONSTRAINT fk_hr_clinic
    100100        FOREIGN KEY (clinic_id)
    101         REFERENCES "VetClinic"(clinic_id)
     101        REFERENCES VetClinic(clinic_id)
    102102        ON DELETE SET NULL
    103103);
    104104
    105105CREATE TABLE Notification (
    106     notification_id BIGSERIAL PRIMARY KEY,
     106    notification_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    107107    user_id         BIGINT     NOT NULL, 
    108108    message         TEXT       NOT NULL,
    … …  
    112112    CONSTRAINT fk_notification_user
    113113        FOREIGN KEY (user_id)
    114         REFERENCES "User"(user_id)
     114        REFERENCES User(user_id)
    115115        ON DELETE CASCADE
    116116);
    … …  
    118118
    119119CREATE TABLE Review (
    120     review_id        BIGSERIAL PRIMARY KEY,
     120    review_id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    121121    reviewer_id      BIGINT NOT NULL,       
    122122    target_user_id   BIGINT,                 
    … …  
    127127    CONSTRAINT fk_review_reviewer
    128128        FOREIGN KEY (reviewer_id)
    129         REFERENCES "User"(user_id)
     129        REFERENCES User(user_id)
    130130        ON DELETE CASCADE,
    131131    CONSTRAINT fk_review_target_user
    132132        FOREIGN KEY (target_user_id)
    133         REFERENCES "User"(user_id)
     133        REFERENCES User(user_id)
    134134        ON DELETE CASCADE,
    135135    CONSTRAINT fk_review_target_clinic
    136136        FOREIGN KEY (target_clinic_id)
    137         REFERENCES "VetClinic"(clinic_id)
     137        REFERENCES VetClinic(clinic_id)
    138138        ON DELETE CASCADE,
    139139    CONSTRAINT review_must_have_target