04_drop_tables.sql

/* 
===========================================================================================
Script Name: Script2_4_Drop
Author: Dimitrios Chasanidis, Konstantina Tzeikou, Panteleimon Skenderoglou, Thomas Kanoutas 
Date: 22-01-2025
Description: 
    This script removes various constraints from the tables apartment rental system, 
    ensuring that the data integrity rules related to uppercase values, unique emails, 
    and logical relationships are no longer enforced. After dropping the constraints, 
    it proceeds to drop the tables themselves.

===========================================================================================
Script Purpose:
    1) Drop constraints from the following tables:
        - `REVIEWS`: Removes constraints on review text case and star rating.
        - `RENTALS`: Removes constraint on rental start and end dates.
        - `TENANTS`: Removes constraints on tenant's name, surname, email, country, and gender.
        - `APARTMENTS`: Removes constraints on apartment street and city names.
        - `OWNERS`: Removes constraints on owner's name, surname, email, and TIN.
    2) Drop the tables entirely along with any associated constraints.
    
===========================================================================================
Tables Affected:
    1) APARTMENTS
    2) OWNERS
    3) RENTALS
    4) REVIEWS
    5) TENANTS
===========================================================================================
*/

-- Drop Constraint from REVIEWS table related to checking if the text of 
-- a review is written uppercase
ALTER TABLE reviews DROP CONSTRAINT reviews_review_txt_ck;
-- Drop Constraint from REVIEWS table related to checking if the star 
-- review is between numbers 1 to 5
ALTER TABLE reviews DROP CONSTRAINT reviews_star_rating_ck;

-- Drop Constraint from RENTALS table related to checking if rental end date 
-- is after the rental starting date
ALTER TABLE rentals DROP CONSTRAINT rentals_date_ck;

-- Drop Constraint FROM TENANTS table related to checking if the name of the tenant
-- is in uppercase
ALTER TABLE tenants DROP CONSTRAINT tenants_t_name_ck;
-- Drop Constraint FROM TENANTS table related to checking if the surname of the tenant
-- is in uppercase
ALTER TABLE tenants DROP CONSTRAINT tenants_t_surname_ck;
-- Drop Constraint FROM TENANTS table related to checking if the country of the tenant
-- is in uppercase
ALTER TABLE tenants DROP CONSTRAINT tenants_country_ck;
-- Drop Constraint FROM TENANTS table related to checking if the email of the tenant
-- is unique
ALTER TABLE tenants DROP CONSTRAINT tenants_email_uq;
-- Drop Constraint FROM TENANTS table related to checking if the gender of the tenant
-- fall into (M, F, O)
ALTER TABLE tenants DROP CONSTRAINT gender_ck;

-- Drop Constraint FROM APARTMENTS table related to checking if the street of the apartment
-- is in uppercase
ALTER TABLE apartments DROP CONSTRAINT apartments_street_ck;
-- Drop Constraint FROM APARTMENTS table related to checking if the city of the apartment
-- is in uppercase
ALTER TABLE apartments DROP CONSTRAINT apartments_city_ck;

-- Drop Constraint FROM OWNERS table related to checking if the name of the owner
-- is in uppercase
ALTER TABLE owners DROP CONSTRAINT owners_o_name_ck;
-- Drop Constraint FROM OWNERS table related to checking if the surname of the owner
-- is in uppercase
ALTER TABLE owners DROP CONSTRAINT owners_o_surname_ck;
-- Drop Constraint FROM OWNERS table related to checking if the city of the owner
-- is in uppercase
ALTER TABLE owners DROP CONSTRAINT owners_city_ck;
-- Drop Constraint FROM OWNERS table related to checking if the email of the owner
-- is unique
ALTER TABLE owners DROP CONSTRAINT owners_email_uq;
-- Drop Constraint FROM OWNERS table related to checking if the TIN of the owner
-- is unique
ALTER TABLE owners DROP CONSTRAINT owners_tin_uq

-- Drop DB Tables
DROP TABLE reviews CASCADE CONSTRAINTS;
DROP TABLE rentals CASCADE CONSTRAINTS;
DROP TABLE tenants CASCADE CONSTRAINTS;
DROP TABLE apartments CASCADE CONSTRAINTS;
DROP TABLE owners CASCADE CONSTRAINTS;

-- Drop DB Sequences
DROP SEQUENCE seq_owners;
DROP SEQUENCE seq_apartments;
DROP SEQUENCE seq_tenants;
DROP SEQUENCE seq_rentals;
DROP SEQUENCE seq_reviews;