Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer

CREATE Statements

The following statements were used to create the tables in the example database. Only the CREATE statements are listed here.

CREATE DATABANK 

 CREATE DATABANK HOTELDB
        OF 60 PAGES
        IN 'HOTELDB'
        WITH TRANS OPTION;

CREATE DOMAIN 

 CREATE DOMAIN HOTELCODE
        AS CHARACTER(4);
 
 CREATE DOMAIN STATUS
        AS CHARACTER(10)
        DEFAULT 'UNKNOWN';
 
 CREATE DOMAIN ROOMTYPE
        AS CHARACTER(6)
        DEFAULT '-ND-';
 
 CREATE DOMAIN ROOMNO
        AS CHARACTER(7);
 
 CREATE DOMAIN PERSONNAME
        AS CHARACTER(25);
 
 CREATE DOMAIN NUMBER 
        AS INTEGER(3)
        DEFAULT 0;
 
 CREATE DOMAIN BOOK_RATE
        AS DECIMAL(3,2)
        DEFAULT 1.10;

CREATE TABLE 

 CREATE TABLE HOTEL (HOTELCODE   HOTELCODE  NOT NULL,
                     NAME        CHAR(15)   NOT NULL,
                     CITY        CHAR(15)   NOT NULL,
        PRIMARY KEY (HOTELCODE))
        IN HOTELDB;  
 
 CREATE TABLE ROOMSTATUS (STATUS STATUS NOT NULL,
        PRIMARY KEY (STATUS)) IN HOTELDB;
 
 CREATE TABLE ROOMTYPES (ROOMTYPE     ROOMTYPE     NOT NULL,
                         DESCRIPTION  VARCHAR(40)  NOT NULL,
        PRIMARY KEY (ROOMTYPE))
        IN HOTELDB;
                                
 CREATE TABLE ROOMS (ROOMNO     ROOMNO     NOT NULL,
                     HOTELCODE  HOTELCODE  NOT NULL,
                     ROOMTYPE   ROOMTYPE   NOT NULL,
                     STATUS     STATUS     NOT NULL,
        PRIMARY KEY (ROOMNO),
        FOREIGN KEY (HOTELCODE) REFERENCES HOTEL,
        FOREIGN KEY (ROOMTYPE)  REFERENCES ROOMTYPES,
        FOREIGN KEY (STATUS)    REFERENCES ROOMSTATUS)
        IN HOTELDB;                 
                                         
 CREATE TABLE ROOM_PRICES (HOTELCODE  HOTELCODE  NOT NULL,
                           ROOMTYPE   ROOMTYPE   NOT NULL,
                           FROM_DATE  DATE       NOT NULL,
                           TO_DATE    DATE       NOT NULL,
                           PRICE      INTEGER(4),
        PRIMARY KEY (HOTELCODE,ROOMTYPE,FROM_DATE),
        FOREIGN KEY (HOTELCODE) REFERENCES HOTEL,
        FOREIGN KEY (ROOMTYPE)  REFERENCES ROOMTYPES)
        IN HOTELDB;
 
 CREATE TABLE CHARGES (CHARGE_CODE   CHAR(3)   NOT NULL,
                       DESCRIPTION   CHAR(25)  NOT NULL,
                       CHARGE_PRICE  INTEGER(4),
        PRIMARY KEY (CHARGE_CODE))
        IN HOTELDB;
                          
 CREATE TABLE BOOK_GUEST (RESERVATION     INTEGER(5)    NOT NULL,
                          BOOKING_DATE    DATE
                            DEFAULT  CURRENT_DATE       NOT NULL,
                          HOTELCODE       HOTELCODE     NOT NULL,
                          ROOMTYPE        ROOMTYPE      NOT NULL,
                          COMPANY         VARCHAR(100)  NOT NULL,
                          TELEPHONE       CHAR(15),
                          RESERVED_FNAME  PERSONNAME,
                          RESERVED_LNAME  PERSONNAME,
                          ARRIVE          DATE          NOT NULL,
                          DEPART          DATE          NOT NULL,
                          GUEST_FNAME     PERSONNAME,
                          GUEST_LNAME     PERSONNAME,
                          ADDRESS         VARCHAR(50),
                          CHECKIN         DATE,
                          CHECKOUT        DATE,
                          ROOMNO          ROOMNO,
                          PAYMENT         CHAR(10),
        PRIMARY KEY (RESERVATION),
        FOREIGN KEY (HOTELCODE) REFERENCES HOTEL,
        FOREIGN KEY (ROOMTYPE)  REFERENCES ROOMTYPES,
        FOREIGN KEY (ROOMNO)    REFERENCES ROOMS,
        CHECK (ARRIVE < DEPART AND CHECKIN <= CHECKOUT))
        IN HOTELDB;               
         
 CREATE TABLE BILL (RESERVATION  INTEGER(5)   NOT NULL,
                    ON_DATE      TIMESTAMP(0) NOT NULL,
                    CHARGE_CODE  CHAR(3)      NOT NULL,
                    COST         INTEGER(4)
                      DEFAULT  NULL,
        FOREIGN KEY (RESERVATION) REFERENCES BOOK_GUEST,
        FOREIGN KEY (CHARGE_CODE) REFERENCES CHARGES)
        IN HOTELDB;     
 
 CREATE TABLE WAKE_UP(ROOMNO     ROOMNO  NOT NULL,
                      WAKE_DATE  DATE    NOT NULL,
                      WAKE_TIME  TIME    NOT NULL,
        PRIMARY KEY (ROOMNO,WAKE_DATE),
        FOREIGN KEY (ROOMNO) REFERENCES ROOMS)
        IN HOTELDB;
 
 CREATE TABLE EXCHANGE_RATE (CURRENCY  CHAR(3)       NOT NULL,
                             RATE      DECIMAL(6,3),
        PRIMARY KEY (CURRENCY))
        IN HOTELDB;

CREATE Procedures 

 --
 -- PROCEDURE TO ENTER THE CHARGE FOR LODGING ON A GUEST'S BILL
 --
 @
 CREATE PROCEDURE ADD_LODGING (IN IN_RESERVATION INTEGER)
 MODIFIES SQL DATA
 BEGIN
    DECLARE P_PRICE, P_DAYS INTEGER;
    DECLARE P_CHECKIN DATE;
 --
 -- FIND PRICE OF ROOM
 --
    SELECT PRICE INTO P_PRICE
      FROM ROOM_PRICES, BOOK_GUEST
     WHERE BOOK_GUEST.RESERVATION = IN_RESERVATION
       AND ROOM_PRICES.ROOMTYPE = BOOK_GUEST.ROOMTYPE
       AND ROOM_PRICES.HOTELCODE = BOOK_GUEST.HOTELCODE
       AND FROM_DATE <= CURRENT_DATE
       AND TO_DATE >= CURRENT_DATE;
 --
 -- FIND LENGTH OF STAY
 --
    SELECT CAST((CHECKOUT-CHECKIN) DAY AS INTEGER), CHECKIN
      INTO P_DAYS, P_CHECKIN
      FROM BOOK_GUEST WHERE RESERVATION=IN_RESERVATION;
 
    BEGIN
       DECLARE P_COUNTER INTEGER DEFAULT 0;
       WHILE P_COUNTER < P_DAYS DO
          INSERT INTO BILL VALUES
                (IN_RESERVATION,
                 CAST(P_CHECKIN+CAST(P_COUNTER AS INTERVAL DAY)
                   AS TIMESTAMP),
                 '100',
                 P_PRICE);
          SET P_COUNTER = P_COUNTER+1;
       END WHILE;
    END;
 END
 @
 
 --
 -- PROCEDURE TO LIST ALL ROOMS THAT HAVE REQUIRED A WAKE-UP
 -- CALL WITHIN THE GIVEN INTERVAL
 --
 @
 create procedure wake_up(in wake_up interval minute(4)) values(char(7))
 reads sql data
 begin
     declare wake cursor for select roomno
     from wake_up
     where cast(substring(cast(wake_date as char(20))from 6 for 10) ||
           ' ' ||
             substring(cast(wake_time as char(20))from 6 for 8)as timestamp)
                    between current_timestamp
                        and current_timestamp + wake_up;
     declare room char(7);
     open wake;
     begin
       declare exit handler for not found begin end;
       loop
          fetch wake into room;
          return room;
       end loop;
     end;
     close wake;
 end@
 
 --
 -- PROCEDURE TO ALLOCATE A ROOM FOR A GUEST
 --
 @
 CREATE PROCEDURE ALLOCATE_ROOM (IN IN_RESERVATION INTEGER,INOUT OUT_ROOMNO CHAR(7))
 MODIFIES SQL DATA
 BEGIN
    SELECT MAX(ROOMS.ROOMNO)
      INTO OUT_ROOMNO
      FROM ROOMS,BOOK_GUEST
     WHERE BOOK_GUEST.RESERVATION = IN_RESERVATION
       AND ROOMS.HOTELCODE = BOOK_GUEST.HOTELCODE
       AND ROOMS.ROOMTYPE  = BOOK_GUEST.ROOMTYPE
       AND ROOMS.STATUS = 'FREE';
 
    UPDATE ROOMS
       SET STATUS = 'UNKNOWN'
     WHERE ROOMNO = OUT_ROOMNO;
 
    UPDATE BOOK_GUEST
       SET ROOMNO = OUT_ROOMNO
     WHERE RESERVATION = IN_RESERVATION;
 END
 @
 
 --
 -- PROCEDURE TO BE CALLED WHENEVER A GUEST CONSUMES ANYTHING
 -- AND CHARGES IT TO HIS/HER ROOM
 --
 @
 CREATE PROCEDURE CHARGE_ROOM(IN IN_ROOMNO CHAR(7),
                              IN IN_CHARGE_CODE CHAR(3))
 MODIFIES SQL DATA
 BEGIN
    DECLARE P_RESERVATION, P_PRICE, P_RC  INTEGER;
 
    SELECT RESERVATION
      INTO P_RESERVATION
      FROM BOOK_GUEST
     WHERE ROOMNO = IN_ROOMNO;
 
    GET DIAGNOSTICS P_RC = ROW_COUNT;
    IF P_RC = 0 THEN
       SIGNAL SQLSTATE '05001';
    END IF;
 
    SELECT CHARGE_PRICE
      INTO P_PRICE
      FROM CHARGES
     WHERE CHARGE_CODE = IN_CHARGE_CODE;
 
     GET DIAGNOSTICS P_RC = ROW_COUNT;
     IF P_RC = 0 THEN
        SIGNAL SQLSTATE '05002';
     END IF;
 
     BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
           SIGNAL SQLSTATE '05003';
        END;
        INSERT INTO BILL VALUES
                 (P_RESERVATION,
                  LOCAL_TIMESTAMP,
                  IN_CHARGE_CODE,
                  P_PRICE);
     END;
 END
 @
 --
 -- PROCEDURE TO FREE UP A ROOM
 --
 @
 CREATE PROCEDURE DEALLOC_ROOM (IN IN_RESERVATION INTEGER)
 MODIFIES SQL DATA
 BEGIN
    DECLARE P_ROOMNO CHAR(7);
 
    SELECT ROOMNO
      INTO P_ROOMNO
      FROM BOOK_GUEST
     WHERE RESERVATION = IN_RESERVATION;
 
    UPDATE ROOMS
       SET STATUS = 'FREE'
     WHERE ROOMNO = P_ROOMNO;
 
    UPDATE BOOK_GUEST
       SET ROOMNO = NULL
     WHERE RESERVATION = IN_RESERVATION;
 END
 @
 
 --
 -- PROCEDURE TO FIND FREE ROOMS FOR A RESERVATION REQUEST
 --
 @
 CREATE PROCEDURE FREEQ (IN IN_HOTELCODE CHAR(4),
                         IN IN_ROOMTYPE CHAR(6),
                         IN IN_ARRIVE DATE,
                         IN IN_DEPART DATE,
                         OUT OUT_ROOMS INTEGER)
 READS SQL DATA
 BEGIN
    DECLARE P_RESERVED,P_AVAIL INTEGER;
 
    SELECT COUNT(RESERVATION)
      INTO P_RESERVED
      FROM BOOK_GUEST
     WHERE ARRIVE <= IN_ARRIVE
       AND DEPART >= IN_DEPART
       AND ROOMTYPE = IN_ROOMTYPE
       AND HOTELCODE = IN_HOTELCODE;
 
    SELECT COUNT(ROOMNO)
      INTO P_AVAIL
      FROM ROOMS
     WHERE ROOMTYPE = IN_ROOMTYPE
       AND HOTELCODE = IN_HOTELCODE;
 
    SET OUT_ROOMS = P_AVAIL - P_RESERVED;
 END
 @
 
 --
 -- PROCEDURE TO PROCESS A GUEST CHECKING OUT
 --
 @
 CREATE PROCEDURE GUEST_LEAVES(IN IN_RESERVATION INTEGER)
 MODIFIES SQL DATA
 BEGIN
    CALL ADD_LODGING(IN_RESERVATION);
    CALL DEALLOC_ROOM(IN_RESERVATION);
 END
 @

CREATE View

This is an example of how you could create a view. 

 --
 -- AT THE DESK OF THE HOTEL THE STAFF USE A VIEW "FREE_ROOMS" TO FIND
 -- FREE ROOMS, AS IT IS A JOINVIEW IT IS NOT UPDATABLE. 
 --
 
 CREATE VIEW FREE_ROOMS AS SELECT R.ROOMNO,R.HOTELCODE,T.DESCRIPTION FROM 
    ROOMS R,ROOMTYPES T
    WHERE R.ROOMTYPE=T.ROOMTYPE
       AND R.STATUS='FREE';

CREATE Trigger

The following are examples of how you could create triggers. 

 @
 CREATE TRIGGER FREEUPDATE INSTEAD OF UPDATE ON FREE_ROOMS
 REFERENCING NEW TABLE AS N
 BEGIN ATOMIC
   .UPDATE ROOMS
       SET STATUS = 'USED'
    WHERE ROOMS.ROOMNO =(SELECT ROOMNO FROM N);
 END
 @
 
 --
 -- THE STATUS OF A ROOM IS KEPT IN THE ROOMS TABLE, NOW THE HOTEL
 -- POLICY IS THAT YOU MAY NEVER DO ANY MAINTAINANCE ON A ROOM WHEN THE
 -- KEY IS OUT
 --
 -- THIS TRIGGER PREVENTS SETTING THE STATUS 'MAINT' WHEN IT IS CURRENTLY
 -- 'KEY OUT'
 --
 
 @
 CREATE TRIGGER SETMAINT  AFTER  UPDATE ON ROOMS
 REFERENCING NEW TABLE AS N 
    OLD TABLE AS O
 BEGIN ATOMIC
    IF  EXISTS (SELECT STATUS FROM O WHERE STATUS='KEY OUT')
    AND EXISTS (SELECT STATUS FROM N WHERE STATUS='MAINT')  THEN
       SIGNAL SQLSTATE  VALUE '07020';  
    END IF ;
 END
 @
 
 --
 -- THIS TRIGGER ONLY WORKS IF YOU UPDATE ONLY ONE ROOM AT A TIME, TO GET
 -- IT WORKING FOR MULTI-ROW-UPDATES YOU WOULD HAVE TO DECLARE 2 CURSORS
 -- AND STEP IN PARALLEL OVER THE O AND N TABLE COMPARING VALUES.
 --
 -- IN THE BILL TABLE THERE MAY NEVER BE MORE THAN ONE
 -- CHARGE FOR EACH DAY FOR THE CHARGES
 -- THIS TRIGGER PREVENTS SUCH INSERTS
 --
 @
 CREATE TRIGGER BILLINSERT AFTER INSERT ON BILL
 REFERENCING NEW TABLE AS N 
 BEGIN ATOMIC
    IF EXISTS (SELECT * 
               FROM BILL,N 
               WHERE BILL.RESERVATION = N.RESERVATION
                 AND BILL.ON_DATE = N.ON_DATE
                 AND BILL.CHARGE_CODE = N.CHARGE_CODE
                 AND N.CHARGE_CODE IN ('100','170','720') )  THEN
       SIGNAL SQLSTATE  VALUE '07020';
    END IF;
 END
 @
 
 --
 -- WHEN A CUSTOMER PAYS THE BILL RECORDS IN THE BILL TABLE ARE
 -- DELETED.  IF THE CUSTOMER PAYS FOR LODGING (CODE=100) MAKE SURE THE
 -- ROOM GETS THE STATUS 'FREE'
 --
 
 @
 CREATE TRIGGER BILLDELETE AFTER DELETE ON BILL
 REFERENCING OLD TABLE AS OLDROWS
 BEGIN ATOMIC
     UPDATE ROOMS 
        SET STATUS='FREE' 
           WHERE ROOMS.ROOMNO = (SELECT BOOK_GUEST.ROOMNO 
                                 FROM BOOK_GUEST, OLDROWS
                                 WHERE OLDROWS.RESERVATION
                                     = BOOK_GUEST.RESERVATION
                                    AND OLDROWS.CHARGE_CODE='100');
 END
 @
 
 --
 -- HOTEL MANAGEMENT DECIDES THAT A COLUMN "RATING" SHOULD BE ADDED TO THE
 -- HOTEL INFORMATION. A NEW TABLE HOTELN IS DEFINED THAT CONTAINS THIS
 -- NEW COLUMN.ALL NEW APPLICATIONS SHOULD USE THIS TABLE.
 --
 
 CREATE TABLE HOTELN(
    HOTELCODE HOTELCODE NOT NULL,
    NAME CHAR(15) NOT NULL,
    CITY CHAR(15) NOT NULL,
    RATING CHAR(5),
    PRIMARY KEY(HOTELCODE) )
   IN  HOTELDB;
 
 --
 -- IN ORDER TO GET ALL OLD APPLICATIONS WORKING A VIEW HOTEL IS DEFINED 
 --
 CREATE VIEW HOTEL AS SELECT  HOTELCODE, NAME, CITY FROM HOTELN;
 
 -- 
 -- BY DEFINING A INSTEAD OF INSERT TRIGGER ON THE VIEW,
 -- WE CAN GET THE EFFECT THAT WHENEVER AN OLD APPLICATION
 -- INSERTS THINGS IN THE HOTEL VIEW (THE OLD APPLICATIONS SEES IT AS A 
 -- TABLE) THE VALUE '-' IS INSERTED IN THE NEW HOTELN TABLE!
 --
 
 @
 CREATE TRIGGER HOTINSERT INSTEAD OF INSERT ON HOTEL
 REFERENCING NEW TABLE AS NEWROWS
 BEGIN ATOMIC
    INSERT INTO HOTELN  SELECT HOTELCODE, NAME, CITY, '-' FROM NEWROWS ;
 END
 @

Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX