SQL Hotel Database

hoteldatabase.sql

REM hoteldatabase.sql
REM Script to create the database for the hotel
REM Jake Black, jjblack@mtu.edu

DROP TABLE customer;
DROP TABLE reservation;
DROP TABLE room;
DROP TABLE incidental;
DROP TABLE payment;

CREATE TABLE customer (
  cust_name         VARCHAR2(50)
    CONSTRAINT pk_customer PRIMARY KEY,
  cust_card         NUMBER(12)
    CONSTRAINT nn_cust_card NOT NULL
);

CREATE TABLE room (
  room_number       NUMBER(3)
    CONSTRAINT pk_room PRIMARY KEY,
  room_beds         VARCHAR2(10),
  room_cost         NUMBER(3)
);

CREATE TABLE reservation (
  resv_id           NUMBER(4)
    CONSTRAINT pk_reservation PRIMARY KEY,
  resv_date         DATE
    CONSTRAINT nn_resv_date NOT NULL,
  resv_room         NUMBER(3)
    CONSTRAINT nn_resv_room NOT NULL,
  CONSTRAINT fk_resv_room FOREIGN KEY (resv_room) REFERENCES room ON DELETE CASCADE,
);

CREATE TABLE incidental (
  incd_id           NUMBER(4)
    CONSTRAINT pk_incidental PRIMARY KEY,
  incd_item         VARCHAR2(50),
  incd_cost         NUMBER(4),
  incd_room         NUMBER(3),
  CONSTRAINT fk_incd_room FOREIGN KEY (incd_room) REFERENCES room ON DELETE CASCADE,
);

CREATE TABLE payment (
  pymt_id           NUMBER(4)
    CONSTRAINT pk_payment PRIMARY KEY,
  pymt_resv_id      NUMBER(4)
  pymt_incd_id      NUMBER(4),
  pymt_cust         VARCHAR2(50),
  CONSTRAINT fk_pymt_resv_id FOREIGN KEY (pymt_resv_id) REFERENCES reservation ON DELETE CASCADE,
  CONSTRAINT fk_pymt_incd_id FOREIGN KEY (pymt_incd_id) REFERENCES incidental ON DELETE CASCADE,
  CONSTRAINT fk_pymt_cust FOREIGN KEY (pymt_cust) REFERENCES customer ON DELETE CASCADE
);

CREATE SEQUENCE resv_id_sequence INCREMENT BY 1 START WITH 1 MAXVALUE 9999 CACHE 10;
CREATE SEQUENCE incd_id_sequence INCREMENT BY 1 START WITH 1 MAXVALUE 9999 CACHE 10;
CREATE SEQUENCE pymt_id_sequence INCREMENT BY 1 START WITH 1 MAXVALUE 9999 CACHE 10;

INSERT INTO customer VALUES ( 'Beulah Reyes', 5280902725806647 );
INSERT INTO customer VALUES ( 'Charles Martinez', 4916012562814467 );
INSERT INTO customer VALUES ( 'Eva Cassady', 4532934273339613 );
INSERT INTO customer VALUES ( 'Linda Koehler', 4716644881160909 );
INSERT INTO customer VALUES ( 'Matthew Gregory', 5564823663961334 );
INSERT INTO customer VALUES ( 'Daisy Barrett', 4929725061056000 );
INSERT INTO customer VALUES ( 'Lowell Williams', 5124008635813481 );
INSERT INTO customer VALUES ( 'Sherman Jordan', 4485186993006009 );
INSERT INTO customer VALUES ( 'Peter Bowman', 4916162162986833 );
INSERT INTO customer VALUES ( 'Ella Serna', 4532984907586246 );
INSERT INTO customer VALUES ( 'Nancy Scarberry', 4929070057568928 );
INSERT INTO customer VALUES ( 'Sergio Matthews', 4556752992998670 );
INSERT INTO customer VALUES ( 'Joy Marson', 5432907334722686 );
INSERT INTO customer VALUES ( 'Doris Morrison', 5517562884202684 );
INSERT INTO customer VALUES ( 'Stacey Romero', 4532213912709241 );
INSERT INTO customer VALUES ( 'Dominick Crary', 4716378648675608 );
INSERT INTO customer VALUES ( 'Doris Morrison', 5517562884202684 );
INSERT INTO customer VALUES ( 'James Quensinberry', 5250996315490370 );
INSERT INTO customer VALUES ( 'Angeline Howard', 5520264453321467 );
INSERT INTO customer VALUES ( 'John Whitley', 4929410265263369 );
INSERT INTO customer VALUES ( 'Holly Centers', 5523041340193086 );
INSERT INTO customer VALUES ( 'William Cooper', 4485463053817553 );
INSERT INTO customer VALUES ( 'Kenneth Ladner', 5477554546355743 );

INSERT INTO room VALUES ( 101, '2 queen', 120 );
INSERT INTO room VALUES ( 102, '2 queen', 120 );
INSERT INTO room VALUES ( 103, '2 queen', 120 );
INSERT INTO room VALUES ( 104, '2 queen', 120 );
INSERT INTO room VALUES ( 105, '2 queen', 120 );
INSERT INTO room VALUES ( 106, '2 queen', 120 );
INSERT INTO room VALUES ( 107, '2 queen', 120 );
INSERT INTO room VALUES ( 108, '2 queen', 120 );
INSERT INTO room VALUES ( 109, '2 queen', 120 );
INSERT INTO room VALUES ( 110, '2 twin', 80 );
INSERT INTO room VALUES ( 111, '2 twin', 80 );
INSERT INTO room VALUES ( 112, '2 twin', 80 );
INSERT INTO room VALUES ( 113, '2 twin', 80 );
INSERT INTO room VALUES ( 114, '2 twin', 80 );
INSERT INTO room VALUES ( 115, '2 twin', 80 );
INSERT INTO room VALUES ( 116, '2 twin', 80 );
INSERT INTO room VALUES ( 117, '1 king', 100 );
INSERT INTO room VALUES ( 118, '1 king', 100 );
INSERT INTO room VALUES ( 119, '1 king', 100 );
INSERT INTO room VALUES ( 120, '1 king', 100 );
INSERT INTO room VALUES ( 201, '2 twin', 80 );
INSERT INTO room VALUES ( 202, '2 twin', 80 );
INSERT INTO room VALUES ( 203, '2 twin', 80 );
INSERT INTO room VALUES ( 204, '2 twin', 80 );
INSERT INTO room VALUES ( 205, '2 twin', 80 );
INSERT INTO room VALUES ( 206, '2 twin', 80 );
INSERT INTO room VALUES ( 207, '2 twin', 80 );
INSERT INTO room VALUES ( 208, '1 king', 100 );
INSERT INTO room VALUES ( 209, '1 king', 100 );
INSERT INTO room VALUES ( 210, '2 queen', 120 );
INSERT INTO room VALUES ( 211, '2 queen', 120 );
INSERT INTO room VALUES ( 212, '2 queen', 120 );
INSERT INTO room VALUES ( 213, '2 queen', 120 );
INSERT INTO room VALUES ( 214, '2 queen', 120 );
INSERT INTO room VALUES ( 215, '2 queen', 120 );
INSERT INTO room VALUES ( 216, '2 queen', 120 );
INSERT INTO room VALUES ( 217, '2 queen', 120 );
INSERT INTO room VALUES ( 218, '2 queen', 120 );
INSERT INTO room VALUES ( 219, '2 king', 150 );
INSERT INTO room VALUES ( 220, '2 king', 150 );



INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 7-01-2019, DD-MM-YYYY), 101 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 7-01-2019, DD-MM-YYYY), 103 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 7-01-2019, DD-MM-YYYY), 107 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 7-01-2019, DD-MM-YYYY), 203 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 8-01-2019, DD-MM-YYYY), 107 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 8-01-2019, DD-MM-YYYY), 208 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 8-01-2019, DD-MM-YYYY), 213 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 9-01-2019, DD-MM-YYYY), 107 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 10-01-2019, DD-MM-YYYY), 101 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 10-01-2019, DD-MM-YYYY), 107 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 10-01-2019, DD-MM-YYYY), 218 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 10-01-2019, DD-MM-YYYY), 220 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 10-01-2019, DD-MM-YYYY), 119 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 10-01-2019, DD-MM-YYYY), 105 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 11-01-2019, DD-MM-YYYY), 218 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 11-01-2019, DD-MM-YYYY), 219 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 11-01-2019, DD-MM-YYYY), 220 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 11-01-2019, DD-MM-YYYY), 116 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 11-01-2019, DD-MM-YYYY), 101 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 11-01-2019, DD-MM-YYYY), 206 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 11-01-2019, DD-MM-YYYY), 107 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 11-01-2019, DD-MM-YYYY), 213 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 107 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 101 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 118 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 117 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 109 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 220 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 205 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 201 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 203 );
INSERT INTO reservation VALUES ( resv_id_sequence.nextval, TO_DATE( 12-01-2019, DD-MM-YYYY), 106 );

INSERT INTO incidental VALUES ( incd_id_sequence.nextval, 'door', 50, 203 );
INSERT INTO incidental VALUES ( incd_id_sequence.nextval, 'bed', 100, 208 );
INSERT INTO incidental VALUES ( incd_id_sequence.nextval, 'door', 50, 119 );
INSERT INTO incidental VALUES ( incd_id_sequence.nextval, 'window', 200, 219 );
INSERT INTO incidental VALUES ( incd_id_sequence.nextval, 'carpet stain', 20, 206 );
INSERT INTO incidental VALUES ( incd_id_sequence.nextval, 'sink', 120, 101 );
INSERT INTO incidental VALUES ( incd_id_sequence.nextval, 'bed', 250, 220 );

INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 1, , 'Beulah Reyes' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 2, ,'Beulah Reyes' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 3, , 'Charles Martinez' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 4, 1, 'Eva Cassady' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 5, , 'Charles Martinez' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 6, 2, 'Linda Koehler' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 7, , 'Matthew Gregory' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 8, , 'Charles Martinez' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 9, , 'Daisy Barrett' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 10, , 'Charles Martinez' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 11, , 'Lowell Williams' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 12, , 'Sherman Jordan' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 13, 3, 'Peter Bowman' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 14, , 'Ella Serna' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 15, , 'Lowell Williams' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 16, 4, 'Nancy Scarberry' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 17, , 'Sergio Matthews' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 18, , 'Joy Marson' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 19, 6, 'Doris Morrison' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 20, 5, 'Stacey Romero' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 21, , 'Charles Martinez' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 22, , 'Dominick Crary' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 23, , 'Charles Martinez' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 24, , 'Doris Morrison' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 25, , 'James Quensinberry' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 26, , 'James Quensinberry' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 27, , 'Angeline Howard' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 28, 7, 'John Whitley' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 29, , 'Holly Centers' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 30, , 'William Cooper' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 31, , 'Kenneth Ladner' );
INSERT INTO payment VALUES ( pymt_id_sequence.nextval, 32, , 'Eva Cassady' );