-- create_store.sql
-- Appendix H, Oracle Database XE PHP Web Programming
-- by Michael McLaughlin
--
-- This script demonstrates how to use SQL statements to build a DVD,
-- Game Cartridge and VHS tape store. It also highlights constraints
-- and compound check constraints and seeds the tables with data.


SPOOL create_store.log

-- SET ECHO ON
SET FEEDBACK ON
SET NULL '<Null>'
SET PAGESIZE 999
SET SERVEROUTPUT ON

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'SYSTEM_USER') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE system_user CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'SYSTEM_USER_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE system_user_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create SYSTEM_USER table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE system_user
( system_user_id              NUMBER
, system_user_name            VARCHAR2(20)
, system_user_group_id        NUMBER
, system_user_type            NUMBER
, last_name                   VARCHAR2(20)
, first_name                  VARCHAR2(20)
, middle_initial              VARCHAR2(1)
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_system_user_1 PRIMARY KEY(system_user_id)
, CONSTRAINT nn_system_user_1 CHECK(system_user_name IS NOT NULL)
, CONSTRAINT nn_system_user_2 CHECK(system_user_group_id IS NOT NULL)
, CONSTRAINT nn_system_user_3 CHECK(system_user_type IS NOT NULL)
, CONSTRAINT nn_system_user_4 CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_system_user_5 CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_system_user_6 CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_system_user_7 CHECK(last_update_date IS NOT NULL));

CREATE SEQUENCE system_user_s1 START WITH 1001;

INSERT INTO system_user
( system_user_id
, system_user_name
, system_user_group_id
, system_user_type
, created_by
, creation_date
, last_updated_by
, last_update_date)
VALUES ( 1,'SYSADMIN', 1, 1, 1, SYSDATE, 1, SYSDATE);

ALTER TABLE system_user ADD CONSTRAINT fk_system_user_1 FOREIGN KEY(created_by)
  REFERENCES system_user(system_user_id);

ALTER TABLE system_user ADD CONSTRAINT fk_system_user_2 FOREIGN KEY(last_updated_by)
  REFERENCES system_user(system_user_id);

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'COMMON_LOOKUP') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE common_lookup CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'COMMON_LOOKUP_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE common_lookup_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create COMMON_LOOKUP table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE common_lookup
( common_lookup_id            NUMBER
, common_lookup_context       VARCHAR2(30)
, common_lookup_type          VARCHAR2(30)
, common_lookup_meaning       VARCHAR2(30)
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_c_lookup_1    PRIMARY KEY(common_lookup_id)
, CONSTRAINT nn_c_lookup_1    CHECK(common_lookup_context IS NOT NULL)
, CONSTRAINT nn_c_lookup_2    CHECK(common_lookup_type IS NOT NULL)
, CONSTRAINT nn_c_lookup_3    CHECK(common_lookup_meaning IS NOT NULL)
, CONSTRAINT nn_c_lookup_4    CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_c_lookup_5    CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_c_lookup_6    CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_c_lookup_7    CHECK(last_update_date IS NOT NULL)
, CONSTRAINT fk_c_lookup_1    FOREIGN KEY(created_by)
  REFERENCES system_user      (system_user_id)
, CONSTRAINT fk_c_lookup_2    FOREIGN KEY(last_updated_by)
  REFERENCES system_user      (system_user_id));

CREATE INDEX common_lookup_n1
  ON common_lookup(common_lookup_context);

CREATE UNIQUE INDEX common_lookup_u2
  ON common_lookup(common_lookup_context,common_lookup_type);

CREATE SEQUENCE common_lookup_s1 START WITH 1001;

INSERT INTO common_lookup VALUES
( 1,'SYSTEM_USER','SYSTEM_ADMIN','System Administrator'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'SYSTEM_USER','DBA','Database Administrator'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'CONTACT','EMPLOYEE','Employee'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'CONTACT','CUSTOMER','Customer'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'MEMBER','INDIVIDUAL','Individual Membership'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'MEMBER','GROUP','Group Membership'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'MEMBER','DISCOVER_CARD','Discover Card'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'MEMBER','MASTER_CARD','Master Card'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'MEMBER','VISA_CARD','VISA Card'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'MULTIPLE','HOME','Home'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'MULTIPLE','WORK','Work'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'ITEM','DVD_FULL_SCREEN','DVD: Full Screen'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'ITEM','DVD_WIDE_SCREEN','DVD: Wide Screen'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'ITEM','NINTENDO_GAMECUBE','Nintendo GameCube'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'ITEM','PLAYSTATION2','PlayStation2'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'ITEM','XBOX','XBOX'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'ITEM','VHS_SINGLE_TAPE','VHS: Single Tape'
, 1, SYSDATE, 1, SYSDATE);

INSERT INTO common_lookup VALUES
( common_lookup_s1.nextval,'ITEM','VHS_DOUBLE_TAPE','VHS: Double Tape'
, 1, SYSDATE, 1, SYSDATE);

ALTER TABLE system_user ADD CONSTRAINT fk_system_user_3 FOREIGN KEY(system_user_type)
  REFERENCES common_lookup(common_lookup_id);

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'MEMBER') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE member CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'MEMBER_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE member_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create MEMBER table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE member
( member_id                   NUMBER
, account_number              VARCHAR2(10)
, credit_card_number          VARCHAR2(19)
, credit_card_type            NUMBER
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_member_1      PRIMARY KEY(member_id)
, CONSTRAINT nn_member_1      CHECK(account_number IS NOT NULL)
, CONSTRAINT nn_member_2      CHECK(credit_card_number IS NOT NULL)
, CONSTRAINT nn_member_3      CHECK(credit_card_type IS NOT NULL)
, CONSTRAINT nn_member_4      CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_member_5      CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_member_6      CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_member_7      CHECK(last_update_date IS NOT NULL)
, CONSTRAINT fk_member_1      FOREIGN KEY(credit_card_type)
  REFERENCES common_lookup    (common_lookup_id)
, CONSTRAINT fk_member_2      FOREIGN KEY(created_by)
  REFERENCES system_user      (system_user_id)
, CONSTRAINT fk_member_3      FOREIGN KEY(last_updated_by)
  REFERENCES system_user      (system_user_id));

CREATE INDEX member_n1 ON member(credit_card_type);

CREATE SEQUENCE member_s1 START WITH 1001;

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'CONTACT') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE contact CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'CONTACT_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE contact_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create CONTACT table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE contact
( contact_id                  NUMBER
, member_id                   NUMBER
, contact_type                NUMBER
, last_name                   VARCHAR2(20)
, first_name                  VARCHAR2(20)
, middle_initial              VARCHAR2(1)
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_contact_1     PRIMARY KEY(contact_id)
, CONSTRAINT nn_contact_1     CHECK(contact_type IS NOT NULL)
, CONSTRAINT nn_contact_2     CHECK(last_name IS NOT NULL)
, CONSTRAINT nn_contact_3     CHECK(first_name IS NOT NULL)
, CONSTRAINT nn_contact_4     CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_contact_5     CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_contact_6     CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_contact_7     CHECK(last_update_date IS NOT NULL)
, CONSTRAINT fk_contact_1     FOREIGN KEY(member_id)
  REFERENCES member           (member_id)
, CONSTRAINT fk_contact_2     FOREIGN KEY(contact_type)
  REFERENCES common_lookup    (common_lookup_id)
, CONSTRAINT fk_contact_3     FOREIGN KEY(created_by)
  REFERENCES system_user      (system_user_id)
, CONSTRAINT fk_contact_4     FOREIGN KEY(last_updated_by)
  REFERENCES system_user      (system_user_id));

CREATE INDEX contact_n1 ON contact(member_id);

CREATE INDEX contact_n2 ON contact(contact_type);

CREATE SEQUENCE contact_s1 START WITH 1001;

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'ADDRESS') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE address CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'ADDRESS_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE address_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create ADDRESS table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE address
( address_id                  NUMBER
, contact_id                  NUMBER
, address_type                NUMBER
, city                        VARCHAR2(30)
, state_province              VARCHAR2(30)
, postal_code                 VARCHAR2(20)
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_address_1     PRIMARY KEY(address_id)
, CONSTRAINT nn_address_1     CHECK(contact_id IS NOT NULL)
, CONSTRAINT nn_address_2     CHECK(address_type IS NOT NULL)
, CONSTRAINT nn_address_3     CHECK(city IS NOT NULL)
, CONSTRAINT nn_address_4     CHECK(state_province IS NOT NULL)
, CONSTRAINT nn_address_5     CHECK(postal_code IS NOT NULL)
, CONSTRAINT nn_address_6     CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_address_7     CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_address_8     CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_address_9     CHECK(last_update_date IS NOT NULL)
, CONSTRAINT fk_address_1     FOREIGN KEY(contact_id)
  REFERENCES contact          (contact_id)
, CONSTRAINT fk_address_2     FOREIGN KEY(address_type)
  REFERENCES common_lookup    (common_lookup_id)
, CONSTRAINT fk_address_3     FOREIGN KEY(created_by)
  REFERENCES system_user      (system_user_id)
, CONSTRAINT fk_address_4     FOREIGN KEY(last_updated_by)
  REFERENCES system_user      (system_user_id));

CREATE INDEX address_n1 ON address(contact_id);

CREATE INDEX address_n2 ON address(address_type);

CREATE SEQUENCE address_s1 START WITH 1001;

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'STREET_ADDRESS') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE street_address CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'STREET_ADDRESS_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE street_address_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create STREET_ADDRESS table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE street_address
( street_address_id           NUMBER
, address_id                  NUMBER
, street_address              VARCHAR2(30)
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_s_address_1   PRIMARY KEY(street_address_id)
, CONSTRAINT nn_s_address_1   CHECK(address_id IS NOT NULL)
, CONSTRAINT nn_s_address_2   CHECK(street_address IS NOT NULL)
, CONSTRAINT nn_s_address_3   CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_s_address_4   CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_s_address_5   CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_s_address_6   CHECK(last_update_date IS NOT NULL)
, CONSTRAINT fk_s_address_1   FOREIGN KEY(address_id)
  REFERENCES address          (address_id)
, CONSTRAINT fk_s_address_3   FOREIGN KEY(created_by)
  REFERENCES system_user      (system_user_id)
, CONSTRAINT fk_s_address_4   FOREIGN KEY(last_updated_by)
  REFERENCES system_user      (system_user_id));

CREATE SEQUENCE street_address_s1 START WITH 1001;

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'TELEPHONE') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE telephone CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'TELEPHONE_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE telephone_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create TELEPHONE table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE telephone
( telephone_id                NUMBER
, contact_id                  NUMBER
, address_id                  NUMBER
, telephone_type              NUMBER
, country_code                VARCHAR2(3)
, area_code                   VARCHAR2(6)
, telephone_number            VARCHAR2(10)
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_telephone_1   PRIMARY KEY(telephone_id)
, CONSTRAINT nn_telephone_1   CHECK(contact_id IS NOT NULL)
, CONSTRAINT nn_telephone_2   CHECK(address_id IS NOT NULL)
, CONSTRAINT nn_telephone_3   CHECK(telephone_type IS NOT NULL)
, CONSTRAINT nn_telephone_4   CHECK(country_code IS NOT NULL)
, CONSTRAINT nn_telephone_5   CHECK(area_code IS NOT NULL)
, CONSTRAINT nn_telephone_6   CHECK(telephone_number IS NOT NULL)
, CONSTRAINT nn_telephone_7   CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_telephone_8   CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_telephone_9   CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_telephone_10  CHECK(last_update_date IS NOT NULL)
, CONSTRAINT fk_telephone_1   FOREIGN KEY(contact_id)
  REFERENCES contact          (contact_id)
, CONSTRAINT fk_telephone_2   FOREIGN KEY(telephone_type)
  REFERENCES common_lookup    (common_lookup_id)
, CONSTRAINT fk_telephone_3   FOREIGN KEY(created_by)
  REFERENCES system_user      (system_user_id)
, CONSTRAINT fk_telephone_4   FOREIGN KEY(last_updated_by)
  REFERENCES system_user      (system_user_id));

CREATE INDEX telephone_n1 ON telephone(contact_id,address_id);

CREATE INDEX telephone_n2 ON telephone(address_id);

CREATE INDEX telephone_n3 ON telephone(telephone_type);

CREATE SEQUENCE telephone_s1 START WITH 1001;

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'RENTAL') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE rental CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'RENTAL_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE rental_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create RENTAL table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE rental
( rental_id                   NUMBER
, customer_id                 NUMBER
, check_out_date              DATE
, return_date                 DATE
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_rental_1      PRIMARY KEY(rental_id)
, CONSTRAINT nn_rental_1      CHECK(customer_id IS NOT NULL)
, CONSTRAINT nn_rental_2      CHECK(check_out_date IS NOT NULL)
, CONSTRAINT nn_rental_3      CHECK(return_date IS NOT NULL)
, CONSTRAINT nn_rental_4      CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_rental_5      CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_rental_6      CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_rental_7      CHECK(last_update_date IS NOT NULL)
, CONSTRAINT fk_rental_1      FOREIGN KEY(customer_id)
  REFERENCES contact          (contact_id)
, CONSTRAINT fk_rental_2      FOREIGN KEY(created_by)
  REFERENCES system_user      (system_user_id)
, CONSTRAINT fk_rental_3      FOREIGN KEY(last_updated_by)
  REFERENCES system_user      (system_user_id));

CREATE SEQUENCE rental_s1 START WITH 1001;

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'ITEM') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE item CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'ITEM_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE item_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create ITEM table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE item
( item_id                     NUMBER
, item_barcode                VARCHAR2(14)
, item_type                   NUMBER
, item_title                  VARCHAR2(60)
, item_subtitle               VARCHAR2(60)
, item_rating                 VARCHAR2(8)
, item_release_date           DATE
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_item_1        PRIMARY KEY(item_id)
, CONSTRAINT nn_item_1        CHECK(item_barcode IS NOT NULL)
, CONSTRAINT nn_item_2        CHECK(item_type IS NOT NULL)
, CONSTRAINT nn_item_3        CHECK(item_title IS NOT NULL)
, CONSTRAINT nn_item_4        CHECK(item_rating IS NOT NULL)
, CONSTRAINT nn_item_5        CHECK(item_release_date IS NOT NULL)
, CONSTRAINT nn_item_6        CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_item_7        CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_item_8        CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_item_9        CHECK(last_update_date IS NOT NULL)
, CONSTRAINT fk_item_1        FOREIGN KEY(item_type)
  REFERENCES common_lookup    (common_lookup_id)
, CONSTRAINT fk_item_2        FOREIGN KEY(created_by)
  REFERENCES system_user      (system_user_id)
, CONSTRAINT fk_item_3        FOREIGN KEY(last_updated_by)
  REFERENCES system_user      (system_user_id));

CREATE SEQUENCE item_s1 START WITH 1001;

-- Conditionally drop objects.
BEGIN
  FOR i IN (SELECT null
            FROM   user_tables
            WHERE  table_name = 'RENTAL_ITEM') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE rental_item CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT null
            FROM   user_sequences
            WHERE  sequence_name = 'RENTAL_ITEM_S1') LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE rental_item_s1';
  END LOOP;
END;
/

-- ------------------------------------------------------------------
-- Create RENTAL_ITEM table and sequence and seed data.
-- ------------------------------------------------------------------

CREATE TABLE rental_item
( rental_item_id              NUMBER
, rental_id                   NUMBER
, item_id                     NUMBER
, created_by                  NUMBER
, creation_date               DATE
, last_updated_by             NUMBER
, last_update_date            DATE
, CONSTRAINT pk_rental_item_1 PRIMARY KEY(rental_item_id)
, CONSTRAINT nn_rental_item_1 CHECK(rental_id IS NOT NULL)
, CONSTRAINT nn_rental_item_2 CHECK(item_id IS NOT NULL)
, CONSTRAINT nn_rental_item_3 CHECK(created_by IS NOT NULL)
, CONSTRAINT nn_rental_item_4 CHECK(creation_date IS NOT NULL)
, CONSTRAINT nn_rental_item_5 CHECK(last_updated_by IS NOT NULL)
, CONSTRAINT nn_rental_item_6 CHECK(last_update_date IS NOT NULL)
, CONSTRAINT fk_rental_item_1 FOREIGN KEY(rental_id)
  REFERENCES rental           (rental_id)
, CONSTRAINT fk_rental_item_2 FOREIGN KEY(item_id)
  REFERENCES item             (item_id)
, CONSTRAINT fk_rental_item_3 FOREIGN KEY(created_by)
  REFERENCES system_user      (system_user_id)
, CONSTRAINT fk_rental_item_4 FOREIGN KEY(last_updated_by)
  REFERENCES system_user      (system_user_id));

CREATE SEQUENCE rental_item_s1 START WITH 1001;

COMMIT;

SPOOL OFF