SQLMODE INTERNAL
//
CREATE USER EDEMO PASSWORD initial  DBA NOT EXCLUSIVE 
//
CREATE USER SQLTRAVEL00 PASSWORD initial  DBA NOT EXCLUSIVE 
//
CREATE USER SQLTRAVEL10 PASSWORD initial  DBA NOT EXCLUSIVE 
//
CREATE USER SQLTRAVEL20 PASSWORD initial  DBA NOT EXCLUSIVE 
//
USE USER SQLTRAVEL00 initial 
//
CREATE USER SQLTRAVEL01 PASSWORD initial  RESOURCE EXCLUSIVE 
//
CREATE USER SQLTRAVEL02 PASSWORD initial  RESOURCE EXCLUSIVE 
//
CREATE USER SQLTRAVEL03 PASSWORD initial  RESOURCE EXCLUSIVE 
//
USE USER SQLTRAVEL10 initial 
//
CREATE USER SQLTRAVEL11 PASSWORD initial  RESOURCE EXCLUSIVE 
//
CREATE USER SQLTRAVEL12 PASSWORD initial  RESOURCE EXCLUSIVE 
//
CREATE USER SQLTRAVEL13 PASSWORD initial  RESOURCE EXCLUSIVE 
//
USE USER SQLTRAVEL20 initial 
//
CREATE USER SQLTRAVEL21 PASSWORD initial  RESOURCE EXCLUSIVE 
//
CREATE USER SQLTRAVEL22 PASSWORD initial  RESOURCE EXCLUSIVE 
//
CREATE USER SQLTRAVEL23 PASSWORD initial  RESOURCE EXCLUSIVE 
//
USE USER EDEMO initial 
//
USE USER SQLTRAVEL00 initial 
//
USE USER SQLTRAVEL10 initial 
//
USE USER SQLTRAVEL20 initial 
//
USE USER EDEMO initial 
//
CREATE TABLE "NL_TEST"
(
	"NUMBER"               Integer,
	"DESCRIPTION"               Char (500) ASCII,
	"PRENUMBER"               Integer,
	"CHANGED"               Timestamp
)
//
CREATE TABLE "PHOTOS"
(
	"NAME"               Char (18) ASCII    NOT NULL,
	"PHOTO"               Long BYTE
)
//
CREATE TABLE "REQUESTS"
(
	"REQUESTNAME"               Varchar (40) ASCII    NOT NULL,
	"REQUEST"               Long ASCII    NOT NULL,
	PRIMARY KEY ("REQUESTNAME")
)
//
USE USER SQLTRAVEL00 initial 
//
CREATE TABLE "ACCOUNT"
(
	"BOOKING_DAY"               Date    NOT NULL,
	"ACTIVITIES"               Fixed (6,2),
	PRIMARY KEY ("BOOKING_DAY")
)
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL10
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL20
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL01
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL02
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL03
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL11
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL12
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL13
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL21
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL22
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.ACCOUNT TO SQLTRAVEL23
//
CREATE TABLE "CITY"
(
	"ZIP"               Char (5) ASCII    NOT NULL,
	"NAME"               Char (20) ASCII    NOT NULL,
	"STATE"               Char (2) ASCII    NOT NULL,
	PRIMARY KEY ("ZIP"),
 CONSTRAINT "ZIP_DOM" CHECK  SUBSTR(ZIP,1,1) BETWEEN '1' AND '9' AND SUBSTR(ZIP,2,1) BETWEEN '0' AND '9' AND SUBSTR(ZIP,3,1) BETWEEN '0' AND '9' AND SUBSTR(ZIP,4,1) BETWEEN '0' AND '9' AND SUBSTR(ZIP,5,1) BETWEEN '0' AND '9'
)
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL10
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL10 WITH GRANT OPTION
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL20
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL20 WITH GRANT OPTION
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL01
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL02
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL03
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL11
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL12
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL13
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL21
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL22
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CITY TO SQLTRAVEL23
//
USE USER EDEMO initial 
//
CREATE TABLE "CATEGORIES"
(
	"CATEGORYID"               Integer    NOT NULL,
	"CATEGORYNAME"               Varchar (16) ASCII    NOT NULL,
	"DESCRIPTION"               Long ASCII,
	"PICTURE"               Long BYTE,
	PRIMARY KEY ("CATEGORYID")
)
//
COMMENT ON COLUMN "CATEGORIES"."CATEGORYID" IS 'Number automatically assigned to new category.'
//
COMMENT ON COLUMN "CATEGORIES"."CATEGORYNAME" IS 'Name of food category.'
//
COMMENT ON COLUMN "CATEGORIES"."PICTURE" IS 'A picture representing the food category.'
//
CREATE UNIQUE INDEX "CATEGORYNAME" ON "EDEMO"."CATEGORIES" ("CATEGORYNAME"  ASC )
//
CREATE TABLE "SUPPLIERS"
(
	"SUPPLIERID"               Integer    NOT NULL,
	"COMPANYNAME"               Varchar (40) ASCII    NOT NULL,
	"CONTACTNAME"               Varchar (30) ASCII,
	"CONTACTTITLE"               Varchar (32) ASCII,
	"ADDRESS"               Varchar (60) ASCII,
	"CITY"               Varchar (15) ASCII,
	"REGION"               Varchar (15) ASCII,
	"POSTALCODE"               Varchar (10) ASCII,
	"COUNTRY"               Varchar (15) ASCII,
	"PHONE"               Varchar (24) ASCII,
	"FAX"               Varchar (24) ASCII,
	PRIMARY KEY ("SUPPLIERID")
)
//
COMMENT ON COLUMN "SUPPLIERS"."SUPPLIERID" IS 'Number automatically assigned to new supplier.'
//
COMMENT ON COLUMN "SUPPLIERS"."ADDRESS" IS 'Street or post-office box.'
//
COMMENT ON COLUMN "SUPPLIERS"."REGION" IS 'State or province.'
//
COMMENT ON COLUMN "SUPPLIERS"."PHONE" IS 'Phone number includes country code or area code.'
//
COMMENT ON COLUMN "SUPPLIERS"."FAX" IS 'Phone number includes country code or area code.'
//
CREATE  INDEX "COMPANYNAME" ON "EDEMO"."SUPPLIERS" ("COMPANYNAME"  ASC )
//
CREATE TABLE "CUSTOMERS"
(
	"CUSTOMERID"               Varchar (5) ASCII    NOT NULL,
	"COMPANYNAME"               Varchar (40) ASCII    NOT NULL,
	"CONTACTNAME"               Varchar (30) ASCII,
	"CONTACTTITLE"               Varchar (30) ASCII,
	"ADDRESS"               Varchar (60) ASCII,
	"CITY"               Varchar (15) ASCII,
	"REGION"               Varchar (15) ASCII,
	"POSTALCODE"               Varchar (10) ASCII,
	"COUNTRY"               Varchar (15) ASCII,
	"PHONE"               Varchar (24) ASCII,
	"FAX"               Varchar (24) ASCII,
	PRIMARY KEY ("CUSTOMERID")
)
//
COMMENT ON COLUMN "CUSTOMERS"."CUSTOMERID" IS 'Unique five-character code based on customer name.'
//
COMMENT ON COLUMN "CUSTOMERS"."ADDRESS" IS 'Street or post-office box.'
//
COMMENT ON COLUMN "CUSTOMERS"."REGION" IS 'State or province.'
//
COMMENT ON COLUMN "CUSTOMERS"."PHONE" IS 'Phone number includes country code or area code.'
//
COMMENT ON COLUMN "CUSTOMERS"."FAX" IS 'Phone number includes country code or area code.'
//
CREATE  INDEX "COMPANYNAME" ON "EDEMO"."CUSTOMERS" ("COMPANYNAME"  ASC )
//
CREATE  INDEX "CITY" ON "EDEMO"."CUSTOMERS" ("CITY"  ASC )
//
CREATE  INDEX "REGION" ON "EDEMO"."CUSTOMERS" ("REGION"  ASC )
//
CREATE TABLE "EMPLOYEES"
(
	"EMPLOYEEID"               Integer    NOT NULL,
	"LASTNAME"               Varchar (20) ASCII    NOT NULL,
	"FIRSTNAME"               Varchar (10) ASCII    NOT NULL,
	"TITLE"               Varchar (30) ASCII,
	"BIRTHDATE"               Date,
	"HIREDATE"               Date,
	"ADDRESS"               Varchar (60) ASCII,
	"CITY"               Varchar (15) ASCII,
	"REGION"               Varchar (15) ASCII,
	"POSTALCODE"               Varchar (10) ASCII,
	"COUNTRY"               Varchar (15) ASCII,
	"HOMEPHONE"               Varchar (24) ASCII,
	"EXTENSION"               Varchar (4) ASCII,
	"PHOTO"               Long BYTE,
	"NOTES"               Long ASCII,
	"REPORTSTO"               Integer,
	PRIMARY KEY ("EMPLOYEEID")
)
//
COMMENT ON TABLE "EMPLOYEES" IS 'This table holds the data of all employees.'
//
COMMENT ON COLUMN "EMPLOYEES"."EMPLOYEEID" IS 'Number automatically assigned to new employees.'
//
COMMENT ON COLUMN "EMPLOYEES"."LASTNAME" IS 'This is a ''test'' comment for SAPDBLOADER.'
//
COMMENT ON COLUMN "EMPLOYEES"."TITLE" IS 'Employee''s title.'
//
COMMENT ON COLUMN "EMPLOYEES"."ADDRESS" IS 'Street or post-office box.'
//
COMMENT ON COLUMN "EMPLOYEES"."REGION" IS 'State or province.'
//
COMMENT ON COLUMN "EMPLOYEES"."HOMEPHONE" IS 'Phone number includes country code or area code.'
//
COMMENT ON COLUMN "EMPLOYEES"."EXTENSION" IS 'Phone number includes country code or area code.'
//
COMMENT ON COLUMN "EMPLOYEES"."PHOTO" IS 'Picture of employee.'
//
COMMENT ON COLUMN "EMPLOYEES"."NOTES" IS 'General information about employee''s background.'
//
COMMENT ON COLUMN "EMPLOYEES"."REPORTSTO" IS 'Employee''s supervisor.'
//
CREATE  INDEX "LASTNAME" ON "EDEMO"."EMPLOYEES" ("LASTNAME"  ASC )
//
CREATE TABLE "SHIPPERS"
(
	"SHIPPERID"               Integer    NOT NULL,
	"COMPANYNAME"               Varchar (40) ASCII    NOT NULL,
	PRIMARY KEY ("SHIPPERID")
)
//
COMMENT ON COLUMN "SHIPPERS"."SHIPPERID" IS 'Number automatically assigned to new shipper.'
//
COMMENT ON COLUMN "SHIPPERS"."COMPANYNAME" IS 'Name of shipping company.'
//
USE USER SQLTRAVEL00 initial 
//
CREATE TABLE "CUSTOMER"
(
	"CNO"               Fixed (4,0)    NOT NULL,
	"TITLE"               Char (7) ASCII,
	"FIRSTNAME"               Char (10) ASCII,
	"NAME"               Char (10) ASCII    NOT NULL,
	"ZIP"               Char (5) ASCII,
	"ADDRESS"               Char (25) ASCII    NOT NULL,
	PRIMARY KEY ("CNO"),
 CONSTRAINT "CNO_DOM" CHECK  CNO BETWEEN 1 AND 9999,
 CONSTRAINT "TITLE" CHECK TITLE IN ('Mr', 'Mrs', 'Company'),
 CONSTRAINT "ZIP_DOM" CHECK  SUBSTR(ZIP,1,1) BETWEEN '1' AND '9' AND SUBSTR(ZIP,2,1) BETWEEN '0' AND '9' AND SUBSTR(ZIP,3,1) BETWEEN '0' AND '9' AND SUBSTR(ZIP,4,1) BETWEEN '0' AND '9' AND SUBSTR(ZIP,5,1) BETWEEN '0' AND '9',
	FOREIGN KEY "CITY_CUSTOMER"	("ZIP") REFERENCES "SQLTRAVEL00"."CITY" ("ZIP") ON DELETE  RESTRICT
)
//
CREATE  INDEX "CUSTOMER_INDEX" ON "SQLTRAVEL00"."CUSTOMER" ("NAME"  ASC )
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL10
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL10 WITH GRANT OPTION
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL20
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL20 WITH GRANT OPTION
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL01
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL02
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL03
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL11
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL12
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL13
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL21
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL22
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL00.CUSTOMER TO SQLTRAVEL23
//
USE USER SQLTRAVEL10 initial 
//
CREATE TABLE "HOTEL"
(
	"HNO"               Fixed (4,0)    NOT NULL,
	"NAME"               Char (15) ASCII    NOT NULL,
	"ZIP"               Char (5) ASCII    NOT NULL,
	"ADDRESS"               Char (25) ASCII    NOT NULL,
	"INFO"               Long ASCII,
	PRIMARY KEY ("HNO"),
 CONSTRAINT "HNO_DOM" CHECK  HNO BETWEEN 1 AND 9999,
 CONSTRAINT "ZIP_DOM" CHECK  SUBSTR(ZIP,1,1) BETWEEN '1' AND '9' AND SUBSTR(ZIP,2,1) BETWEEN '0' AND '9' AND SUBSTR(ZIP,3,1) BETWEEN '0' AND '9' AND SUBSTR(ZIP,4,1) BETWEEN '0' AND '9' AND SUBSTR(ZIP,5,1) BETWEEN '0' AND '9',
	FOREIGN KEY "CITY_HOTEL"	("ZIP") REFERENCES "SQLTRAVEL00"."CITY" ("ZIP") ON DELETE  RESTRICT
)
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL00
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL00 WITH GRANT OPTION
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL20
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL20 WITH GRANT OPTION
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL01
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL02
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL03
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL11
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL12
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL13
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL21
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL22
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.HOTEL TO SQLTRAVEL23
//
USE USER EDEMO initial 
//
CREATE TABLE "PRODUCTS"
(
	"PRODUCTID"               Integer    NOT NULL,
	"SUPPLIERID"               Integer,
	"CATEGORYID"               Integer,
	"PRODUCTNAME"               Varchar (40) ASCII,
	"QUANTITYPERUNIT"               Varchar (23) ASCII,
	"UNITPRICE"               Float (8),
	"UNITSINSTOCK"               Smallint,
	"UNITSONORDER"               Smallint,
	"REORDERLEVEL"               Smallint,
	"DISCONTINUED"               Boolean    DEFAULT FALSE,
	"BARCODEID"               Char (10) BYTE    DEFAULT x'0086B22A318528A4D700',
	PRIMARY KEY ("PRODUCTID"),
	FOREIGN KEY "CATEGORYPRODUCT"	("CATEGORYID") REFERENCES "EDEMO"."CATEGORIES" ("CATEGORYID") ON DELETE  RESTRICT,
	FOREIGN KEY "SUPPLIERPRODUCT"	("SUPPLIERID") REFERENCES "EDEMO"."SUPPLIERS" ("SUPPLIERID") ON DELETE  RESTRICT
)
//
COMMENT ON COLUMN "PRODUCTS"."PRODUCTID" IS 'Number automatically assigned to new product.'
//
COMMENT ON COLUMN "PRODUCTS"."SUPPLIERID" IS 'Same as SupplierID in Suppliers table.'
//
COMMENT ON COLUMN "PRODUCTS"."CATEGORYID" IS 'Same as CategoryID in Categoriers table.'
//
COMMENT ON COLUMN "PRODUCTS"."QUANTITYPERUNIT" IS '(e.g., 24-count case, 1-liter bottle).'
//
COMMENT ON COLUMN "PRODUCTS"."REORDERLEVEL" IS 'Minimum units to maintain in stock.'
//
COMMENT ON COLUMN "PRODUCTS"."DISCONTINUED" IS 'Yes means item no longer available.'
//
CREATE  INDEX "PRODUCTNAME" ON "EDEMO"."PRODUCTS" ("PRODUCTNAME"  ASC )
//
CREATE  INDEX "CATEGORYID" ON "EDEMO"."PRODUCTS" ("CATEGORYID"  ASC )
//
CREATE  INDEX "SUPPLIERID" ON "EDEMO"."PRODUCTS" ("SUPPLIERID"  ASC )
//
CREATE TABLE "ORDERS"
(
	"ORDERID"               Integer    NOT NULL,
	"CUSTOMERID"               Varchar (5) ASCII    NOT NULL,
	"EMPLOYEEID"               Integer,
	"SHIPNAME"               Varchar (40) ASCII,
	"SHIPADDRESS"               Varchar (60) ASCII,
	"SHIPCITY"               Varchar (15) ASCII,
	"SHIPREGION"               Varchar (15) ASCII,
	"SHIPPOSTALCODE"               Varchar (10) ASCII,
	"SHIPCOUNTRY"               Varchar (15) ASCII,
	"SHIPVIA"               Integer,
	"ORDERDATE"               Date,
	"REQUIREDDATE"               Date,
	"SHIPPEDDATE"               Date,
	"FREIGHT"               Float (8),
	PRIMARY KEY ("ORDERID"),
	FOREIGN KEY "CUSTOMERORDER"	("CUSTOMERID") REFERENCES "EDEMO"."CUSTOMERS" ("CUSTOMERID") ON DELETE  RESTRICT,
	FOREIGN KEY "EMPLOYEEORDER"	("EMPLOYEEID") REFERENCES "EDEMO"."EMPLOYEES" ("EMPLOYEEID") ON DELETE  RESTRICT,
	FOREIGN KEY "SHIPPERORDER"	("SHIPVIA") REFERENCES "EDEMO"."SHIPPERS" ("SHIPPERID") ON DELETE  RESTRICT
)
//
COMMENT ON COLUMN "ORDERS"."ORDERID" IS 'Unique order number from invoice.'
//
COMMENT ON COLUMN "ORDERS"."CUSTOMERID" IS 'Same as CustomerID in Customers table.'
//
COMMENT ON COLUMN "ORDERS"."EMPLOYEEID" IS 'Same as EmployeeID in Employees table.'
//
COMMENT ON COLUMN "ORDERS"."SHIPNAME" IS 'Name of person or company to receive the shipment.'
//
COMMENT ON COLUMN "ORDERS"."SHIPADDRESS" IS 'Street address only -- no post-office box allowed.'
//
COMMENT ON COLUMN "ORDERS"."SHIPREGION" IS 'State or Province'
//
COMMENT ON COLUMN "ORDERS"."SHIPVIA" IS 'Same as ShipperID in Shippers table.'
//
CREATE  INDEX "ORDERDATE" ON "EDEMO"."ORDERS" ("ORDERDATE"  ASC )
//
CREATE  INDEX "CUSTOMERID" ON "EDEMO"."ORDERS" ("CUSTOMERID"  ASC )
//
CREATE  INDEX "EMPLOYEEID" ON "EDEMO"."ORDERS" ("EMPLOYEEID"  ASC )
//
USE USER SQLTRAVEL10 initial 
//
CREATE TABLE "ROOM"
(
	"HNO"               Fixed (4,0)    NOT NULL,
	"TYPE"               Char (6) ASCII    NOT NULL,
	"FREE"               Fixed (3,0),
	"PRICE"               Fixed (6,2),
	PRIMARY KEY ("HNO", "TYPE"),
 CONSTRAINT "HNO_DOM" CHECK  HNO BETWEEN 1 AND 9999,
 CONSTRAINT "FREE" CHECK FREE >= 0,
 CONSTRAINT "PRICE" CHECK PRICE BETWEEN 0.00 AND 5000.00,
 CONSTRAINT "ROOM_DOM" CHECK  TYPE IN ('single', 'double','suite'),
	FOREIGN KEY "HOTEL_ROOM"	("HNO") REFERENCES "SQLTRAVEL10"."HOTEL" ("HNO") ON DELETE  CASCADE
)
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL00
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL00 WITH GRANT OPTION
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL20
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL20 WITH GRANT OPTION
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL01
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL02
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL03
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL11
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL12
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL13
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL21
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL22
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL10.ROOM TO SQLTRAVEL23
//
USE USER EDEMO initial 
//
CREATE TABLE "ORDERDETAILS"
(
	"ORDERID"               Integer    NOT NULL,
	"PRODUCTID"               Integer    NOT NULL,
	"UNITPRICE"               Float (8)    NOT NULL,
	"QUANTITY"               Smallint    DEFAULT      1,
	"DISCOUNT"               Float (4)    DEFAULT  0.000E+00,
	PRIMARY KEY ("ORDERID", "PRODUCTID"),
 CONSTRAINT "DISCOUNT" CHECK DISCOUNT BETWEEN 0 AND 1,
 CONSTRAINT "QUANTITY" CHECK QUANTITY>0,
	FOREIGN KEY "PRODUCTORDERDETAILS"	("PRODUCTID") REFERENCES "EDEMO"."PRODUCTS" ("PRODUCTID") ON DELETE  RESTRICT,
	FOREIGN KEY "ORDERORDERDETAIL"	("ORDERID") REFERENCES "EDEMO"."ORDERS" ("ORDERID") ON DELETE  CASCADE
)
//
COMMENT ON COLUMN "ORDERDETAILS"."ORDERID" IS 'Same as OrderID in Orders Table.'
//
COMMENT ON COLUMN "ORDERDETAILS"."PRODUCTID" IS 'Same as ProductID in Products table.'
//
CREATE  INDEX "PRODUCTID" ON "EDEMO"."ORDERDETAILS" ("PRODUCTID"  ASC )
//
USE USER SQLTRAVEL20 initial 
//
CREATE TABLE "RESERVATION"
(
	"RNO"               Fixed (4,0)    NOT NULL,
	"CNO"               Fixed (4,0),
	"HNO"               Fixed (4,0),
	"TYPE"               Char (6) ASCII,
	"ARRIVAL"               Date    NOT NULL,
	"DEPARTURE"               Date    NOT NULL,
	PRIMARY KEY ("RNO"),
 CONSTRAINT "CNO_DOM" CHECK  CNO BETWEEN 1 AND 9999,
 CONSTRAINT "DEPARTURE" CHECK DEPARTURE > ARRIVAL,
 CONSTRAINT "HNO_DOM" CHECK  HNO BETWEEN 1 AND 9999,
 CONSTRAINT "RNO_DOM" CHECK  RNO BETWEEN 1 AND 9999,
 CONSTRAINT "ROOM_DOM" CHECK  TYPE IN ('single', 'double','suite'),
	FOREIGN KEY "CUSTOMER_RESERVATION"	("CNO") REFERENCES "SQLTRAVEL00"."CUSTOMER" ("CNO") ON DELETE  CASCADE,
	FOREIGN KEY "ROOM_RESERVATION"	("HNO","TYPE") REFERENCES "SQLTRAVEL10"."ROOM" ("HNO","TYPE") ON DELETE  CASCADE
)
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL00
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL10
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL01
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL02
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL03
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL11
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL12
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL13
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL21
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL22
//
GRANT ALTER,INDEX,SELECT,INSERT,UPDATE,DELETE,REFERENCES ON SQLTRAVEL20.RESERVATION TO SQLTRAVEL23
//
USE USER SQLTRAVEL00 initial 
//
CREATE VIEW CUSTOMER_ADDR
(C_NO, C_TITLE, C_NAME, C_FIRSTNAME,
C_ZIP, C_CITY, C_STATE, C_ADDRESS)
AS SELECT CNO, TITLE, CUSTOMER.NAME, FIRSTNAME,
CUSTOMER.ZIP, CITY.NAME, STATE,
CUSTOMER.ADDRESS
FROM CUSTOMER, CITY
WHERE CUSTOMER.ZIP = CITY.ZIP
WITH CHECK OPTION   
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL10
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL10 WITH GRANT OPTION
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL20
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL20 WITH GRANT OPTION
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL01
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL02
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL03
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL11
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL12
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL13
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL21
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL22
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL00.CUSTOMER_ADDR TO SQLTRAVEL23
//
USE USER SQLTRAVEL10 initial 
//
CREATE VIEW HOTEL_ADDR (H_NO, H_NAME, H_ZIP, H_CITY, H_STATE, H_ADDRESS)
AS SELECT HNO, HOTEL.NAME, HOTEL.ZIP, CITY.NAME,
STATE, HOTEL.ADDRESS
FROM HOTEL, SQLTRAVEL00.CITY
WHERE HOTEL.ZIP = CITY.ZIP
WITH CHECK OPTION   
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL00
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL00 WITH GRANT OPTION
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL20
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL20 WITH GRANT OPTION
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL01
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL02
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL03
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL11
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL12
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL13
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL21
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL22
//
GRANT SELECT,INSERT,UPDATE,DELETE ON SQLTRAVEL10.HOTEL_ADDR TO SQLTRAVEL23
//
USE USER SQLTRAVEL00 initial 
//
CREATE  SYNONYM  "HOTEL" FOR "SQLTRAVEL10"."HOTEL"
//
CREATE  SYNONYM  "ROOM" FOR "SQLTRAVEL10"."ROOM"
//
CREATE  SYNONYM  "RESERVATION" FOR "SQLTRAVEL20"."RESERVATION"
//
USE USER SQLTRAVEL10 initial 
//
CREATE  SYNONYM  "CITY" FOR "SQLTRAVEL00"."CITY"
//
CREATE  SYNONYM  "CUSTOMER" FOR "SQLTRAVEL00"."CUSTOMER"
//
CREATE  SYNONYM  "ACCOUNT" FOR "SQLTRAVEL00"."ACCOUNT"
//
CREATE  SYNONYM  "RESERVATION" FOR "SQLTRAVEL20"."RESERVATION"
//
USE USER SQLTRAVEL20 initial 
//
CREATE  SYNONYM  "CITY" FOR "SQLTRAVEL00"."CITY"
//
CREATE  SYNONYM  "CUSTOMER" FOR "SQLTRAVEL00"."CUSTOMER"
//
CREATE  SYNONYM  "ACCOUNT" FOR "SQLTRAVEL00"."ACCOUNT"
//
CREATE  SYNONYM  "HOTEL" FOR "SQLTRAVEL10"."HOTEL"
//
CREATE  SYNONYM  "ROOM" FOR "SQLTRAVEL10"."ROOM"
//
USE USER EDEMO initial 
//
CREATE VIEW EDEMO.ORDERSUBTOTALS AS
SELECT ORDERID, SUM(UNITPRICE * QUANTITY * (1 - DISCOUNT) * 100 / 100) AS SUBTOTAL FROM ORDERDETAILS
GROUP BY ORDERID      
//
USE USER SQLTRAVEL20 initial 
//
CREATE VIEW CUSTOM_HOTEL
(CUSTOMNAME, CUSTOMCITY, HOTELNAME, HOTELCITY)
AS SELECT C_NAME, C_CITY, H_NAME, H_CITY
FROM SQLTRAVEL00.CUSTOMER_ADDR,
SQLTRAVEL10.HOTEL_ADDR,
RESERVATION
WHERE C_NO = RESERVATION.CNO
AND H_NO = RESERVATION.HNO 
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL00
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL10
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL01
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL02
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL03
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL11
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL12
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL13
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL21
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL22
//
GRANT SELECT ON SQLTRAVEL20.CUSTOM_HOTEL TO SQLTRAVEL23
//
USE USER SQLTRAVEL00 initial 
//
CREATE  SYNONYM  "HOTEL_ADDR" FOR "SQLTRAVEL10"."HOTEL_ADDR"
//
USE USER SQLTRAVEL10 initial 
//
CREATE  SYNONYM  "CUSTOMER_ADDR" FOR "SQLTRAVEL00"."CUSTOMER_ADDR"
//
USE USER SQLTRAVEL20 initial 
//
CREATE  SYNONYM  "CUSTOMER_ADDR" FOR "SQLTRAVEL00"."CUSTOMER_ADDR"
//
CREATE  SYNONYM  "HOTEL_ADDR" FOR "SQLTRAVEL10"."HOTEL_ADDR"
//
USE USER SQLTRAVEL00 initial 
//
CREATE  SYNONYM  "CUSTOM_HOTEL" FOR "SQLTRAVEL20"."CUSTOM_HOTEL"
//
USE USER SQLTRAVEL10 initial 
//
CREATE  SYNONYM  "CUSTOM_HOTEL" FOR "SQLTRAVEL20"."CUSTOM_HOTEL"
//
USE USER EDEMO initial 
//
USE USER SQLTRAVEL00 initial 
//
USE USER SQLTRAVEL10 initial 
//
USE USER SQLTRAVEL20 initial 
//
