ddl.txt

-- Generated by Oracle SQL Developer Data Modeler 23.1.0.087.0806
--   at:        2024-06-10 17:49:38 EEST
--   site:      Oracle Database 12c
--   type:      Oracle Database 12c



-- predefined type, no DDL - MDSYS.SDO_GEOMETRY

-- predefined type, no DDL - XMLTYPE

CREATE TABLE branch (
    branchid     VARCHAR2(20) NOT NULL,
    city         VARCHAR2(50) NOT NULL,
    address      VARCHAR2(255) NOT NULL,
    phone_number VARCHAR2(20) NOT NULL
);

ALTER TABLE branch ADD CONSTRAINT branch_pk PRIMARY KEY ( branchid );

CREATE TABLE car (
    type                 VARCHAR2(50) NOT NULL,
    number_of_seats      INTEGER NOT NULL,
    number_of_doors      INTEGER NOT NULL,
    conventionality      NUMBER NOT NULL,
    gasoline_powered     NUMBER NOT NULL,
    diesel_powered       NUMBER NOT NULL,
    electric             NUMBER NOT NULL,
    hybrid               NUMBER NOT NULL,
    rental_price_per_day NUMBER(10, 2) NOT NULL,
    carid                VARCHAR2(17) NOT NULL,
    branch_branchid      VARCHAR2(20) NOT NULL
);

ALTER TABLE car ADD CONSTRAINT car_pk PRIMARY KEY ( carid );

CREATE TABLE customer (
    first_name     VARCHAR2(50) NOT NULL,
    last_name      VARCHAR2(50) NOT NULL,
    email          VARCHAR2(100) NOT NULL,
    license_number VARCHAR2(50) NOT NULL,
    phone_number   VARCHAR2(20) NOT NULL,
    mobile_number  VARCHAR2(20) NOT NULL,
    customerid     VARCHAR2(20) NOT NULL
);

ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( customerid );

CREATE TABLE reservation (
    pickup_location         VARCHAR2(15) NOT NULL,
    pickup_point_location   VARCHAR2(15) NOT NULL,
    pickup_date             DATE NOT NULL,
    pickup_time             DATE NOT NULL,
    return_location         VARCHAR2(15),
    return_date             DATE NOT NULL,
    return_time             DATE NOT NULL,
    car_type                VARCHAR2(50),
    costumer_email          VARCHAR2(4000) NOT NULL,
    costumer_license_number VARCHAR2(4000) NOT NULL,
    costumer_mobile_number  INTEGER NOT NULL,
    costumer_phone_number   INTEGER NOT NULL,
    renting_car_id          NUMBER NOT NULL,
    reservationid           VARCHAR2(20) NOT NULL,
    total_price             NUMBER(10, 2) NOT NULL,
    customer_customerid     VARCHAR2(20) NOT NULL,
    car_carid2              VARCHAR2(17)
);

CREATE UNIQUE INDEX renting_car__idx ON
    reservation (
        costumer_email
    ASC,
        costumer_license_number
    ASC,
        costumer_mobile_number
    ASC,
        costumer_phone_number
    ASC );

ALTER TABLE reservation ADD CONSTRAINT reservation_pk PRIMARY KEY ( reservationid );

ALTER TABLE reservation ADD CONSTRAINT renting_car_pk UNIQUE ( renting_car_id );

ALTER TABLE car
    ADD CONSTRAINT car_branch_fk FOREIGN KEY ( branch_branchid )
        REFERENCES branch ( branchid );

ALTER TABLE reservation
    ADD CONSTRAINT reservation_car_fk FOREIGN KEY ( car_carid2 )
        REFERENCES car ( carid );

ALTER TABLE reservation
    ADD CONSTRAINT reservation_customer_fk FOREIGN KEY ( customer_customerid )
        REFERENCES customer ( customerid );

CREATE SEQUENCE reservation_renting_car_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER reservation_renting_car_id_trg BEFORE
    INSERT ON reservation
    FOR EACH ROW
    WHEN ( new.renting_car_id IS NULL )
BEGIN
    :new.renting_car_id := reservation_renting_car_id_seq.nextval;
END;
/



-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                             4
-- CREATE INDEX                             1
-- ALTER TABLE                              8
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           1
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          1
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- TSDP POLICY                              0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0