-- Generated by Oracle SQL Developer Data Modeler 18.4.0.339.1536
--   at:        2019-05-19 13:52:04 CEST
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



CREATE TABLE aircrafts (
    registration     VARCHAR2(8) NOT NULL,
    engine_hours     FLOAT,
    airframe_hours   FLOAT
)
LOGGING;

ALTER TABLE aircrafts ADD CONSTRAINT aircrafts_pk PRIMARY KEY ( registration );

CREATE TABLE airports (
    airportid     INTEGER NOT NULL,
    airporticao   VARCHAR2(4)
)
LOGGING;

ALTER TABLE airports ADD CONSTRAINT airports_pk PRIMARY KEY ( airportid );

CREATE TABLE locations (
    airports_airportid       INTEGER NOT NULL,
    aircrafts_registration   VARCHAR2(8) NOT NULL
)
LOGGING;

ALTER TABLE locations ADD CONSTRAINT relation_5_pk PRIMARY KEY ( airports_airportid,
                                                                 aircrafts_registration );

CREATE TABLE rentals (
    rentalid                 INTEGER NOT NULL,
    cost                     FLOAT,
    aircraftid               INTEGER NOT NULL,
    aircrafts_registration   VARCHAR2(8) NOT NULL,
    users_userid             INTEGER NOT NULL
)
LOGGING;

CREATE UNIQUE INDEX rentals__idx ON
    rentals (
        aircrafts_registration
    ASC )
        LOGGING;

CREATE UNIQUE INDEX rentals__idxv1 ON
    rentals (
        users_userid
    ASC )
        LOGGING;

ALTER TABLE rentals ADD CONSTRAINT rentals_pk PRIMARY KEY ( rentalid );

CREATE TABLE users (
    userid      INTEGER NOT NULL,
    firstname   VARCHAR2(50),
    lastname    VARCHAR2(50),
    license     INTEGER
)
LOGGING;

ALTER TABLE users ADD CONSTRAINT users_pk PRIMARY KEY ( userid );

ALTER TABLE locations
    ADD CONSTRAINT relation_5_aircrafts_fk FOREIGN KEY ( aircrafts_registration )
        REFERENCES aircrafts ( registration )
    NOT DEFERRABLE;

ALTER TABLE locations
    ADD CONSTRAINT relation_5_airports_fk FOREIGN KEY ( airports_airportid )
        REFERENCES airports ( airportid )
    NOT DEFERRABLE;

ALTER TABLE rentals
    ADD CONSTRAINT rentals_aircrafts_fk FOREIGN KEY ( aircrafts_registration )
        REFERENCES aircrafts ( registration )
    NOT DEFERRABLE;

ALTER TABLE rentals
    ADD CONSTRAINT rentals_users_fk FOREIGN KEY ( users_userid )
        REFERENCES users ( userid )
    NOT DEFERRABLE;



-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE                             5
-- CREATE INDEX                             2
-- ALTER TABLE                              9
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- 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                          0
-- 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
--
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
--
-- ERRORS                                   0
-- WARNINGS                                 0