01_create_tables.sql

/* 
===========================================================================================
Script Name: Script2_1_Create
Authors: Dimitrios Chasanidis, Konstantina Tzeikou, Panteleimon Skenderoglou, Thomas Kanoutas 
Date: 22-01-2025
Description: 
    This script creates the database schema for apartment rental system, including 
    tables, sequences, and initial session settings. The schema consists of five main 
    entities (OWNERS, APARTMENTS, TENANTS, RENTAL`, and REVIEWS) and the  relationships 
    between them. Additionally, it configures the session to handle date formatting 
    and language settings.

===========================================================================================
Script Purpose:
    1) Configure the database session with appropriate date format and language.
    2) Define the following database objects:
       - Tables to store data about owners, apartments, tenants, rentals, and reviews.
       - Sequences to auto-generate unique IDs for primary key columns.

===========================================================================================
Database Objects Created:
    1) Tables:
        - OWNERS: Stores owner details (e.g., ID, name, address, and TIN).
        - APARTMENTS: Stores apartment details (e.g., address, owner, price, and floor).
        - TENANTS: Stores tenant details (e.g., name, gender, and country).
        - RENTALS: Stores rental information (e.g., start and end dates).
        - REVIEWS: Stores reviews for apartments (e.g., star rating and comments).
    2) Sequences:
        - `seq_owners`: Generates IDs for the `OWNERS` table.
        - `seq_apartments`: Generates IDs for the `APARTMENTS` table.
        - `seq_tenants`: Generates IDs for the `TENANTS` table.
        - `seq_rentals`: Generates IDs for the `RENTALS` table.
        - `seq_reviews`: Generates IDs for the `REVIEWS` table.

===========================================================================================
*/

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY' NLS_DATE_LANGUAGE='english'; 
select sysdate from dual; 

--Create OWNERS table
CREATE TABLE owners (
O_ID        NUMBER(2), -- Owner's ID 
O_NAME      VARCHAR2(30) NOT NULL, -- Owner's first name
O_SURNAME   VARCHAR2(30) NOT NULL, --Owner's last name
EMAIL       VARCHAR2(50) NOT NULL, -- Owner's email
CITY        VARCHAR2(30) NOT NULL, -- city where the owner lives
D_OF_BIRTH  DATE NOT NULL, -- Owner's date of birth
TIN         NUMBER(9) NOT NULL, -- Owner's Tax Identification Number
O_ADR       VARCHAR2(60) NOT NULL -- Owner's address
);

-- Create sequence for OWNERS table
CREATE SEQUENCE seq_owners
INCREMENT BY 1
START WITH 1 
NOCYCLE 
NOCACHE;
---------------------------------

-- Create APARTMENTS table
CREATE TABLE apartments (
APT_ID      NUMBER(2), -- Apartment's ID
O_ID        NUMBER(2), -- Owner's ID
STREET      VARCHAR2(30) NOT NULL, -- Apartment's street
STR_NUMBER  NUMBER(2) NOT NULL, -- Apartment's street number
POSTAL_CODE NUMBER(5) NOT NULL, -- Apartment's Postal Code
CITY        VARCHAR2(30) DEFAULT 'ATHENS' NOT NULL, -- The city of the Apartment
FLOOR       NUMBER NOT NULL, -- Apartment's floor (0 for ground, 1 for first floor etc.)
PRICE       NUMBER(4,2) NOT NULL -- Apartment's price per day (in euros)
);

-- Create sequence for APARTMENTS table
CREATE SEQUENCE seq_apartments
INCREMENT BY 1
START WITH 1 
NOCYCLE 
NOCACHE;
---------------------------------

-- Create TENANTS table
CREATE TABLE tenants (
T_ID        NUMBER(2), -- Tenant's ID
T_NAME      VARCHAR2(30) NOT NULL, -- Tenant's first name
T_SURNAME   VARCHAR2(30) NOT NULL, -- Tenant's last name
T_GENDER    CHAR(1) NOT NULL, -- Tenant's gender (female, male, other)
EMAIL       VARCHAR2(50) NOT NULL, -- Tenant's email
D_OF_BIRTH  DATE NOT NULL, -- Tenant's date of birth
COUNTRY     VARCHAR2(30) DEFAULT 'GREECE', -- Tenant's country of origin
N_OF_TENANTS NUMBER(2) DEFAULT 1 -- Number of tenants (how many people are staying in the apartment)
);

-- Create sequence for TENANTS table
CREATE SEQUENCE seq_tenants
INCREMENT BY 1
START WITH 1 
NOCYCLE 
NOCACHE;
---------------------------------

-- Create RENTALS table
CREATE TABLE rentals (
RENTAL_ID   NUMBER(2), -- Rental's ID
APT_ID      NUMBER(2), -- Apartment's ID
T_ID        NUMBER(2), -- Tenant's ID
START_DATE  DATE NOT NULL, -- Start date of the Rental
END_DATE    DATE NOT NULL -- End date of the Rental 
);

-- Create sequence for RENTALS table
CREATE SEQUENCE seq_rentals
INCREMENT BY 1
START WITH 1 
NOCYCLE 
NOCACHE;
---------------------------------

-- Create REVIEWS table
CREATE TABLE reviews (
REVIEW_ID   NUMBER(2), -- Review's ID
APT_ID      NUMBER(2), -- Apartment's ID
T_ID        NUMBER(2), -- Tenant's ID
REVIEW_DATE DATE NOT NULL, -- Date of the Review
STAR_RATING NUMBER(1) NOT NULL, -- Star rating
REVIEW_TXT  VARCHAR(4000) NOT NULL -- Review's text
);

-- Create sequence for reviews table 
CREATE SEQUENCE seq_reviews
INCREMENT BY 1
START WITH 1 
NOCYCLE 
NOCACHE;
---------------------------------