SQL DDL mapping of the demonstration database

Overview

This topic displays DDL that maps the Model 204 demonstration database files to Model 204 SQL tables. This sample DDL also includes two views of one of the tables.

If you use this mapping at your site for test purposes, please note the following:

This DDL maps to the Version 6.1 demonstration database files. Ensure that you have the correct set of files attached to your Online.
You must supply a value for the placeholder xxxx in the DDL below (for SCHEMA NAME).

After this cataloging of the demonstration database files, the following query:

SELECT RECTYPE, FULLNAME, SEX, STATE FROM CLIENTS WHERE POLICY_NO = 100648

results in the following:

DRIVER BALDWIN, LEE D M CALIFORNIA DRIVER BALDWIN, MARY C F CALIFORNIA POLICYHOLDER BALDWIN, MARY C CALIFORNIA

DDL stream

CLIENTS table

CREATE SCHEMA xxxx; CREATE TABLE CLIENTS ( ADDRESS CHAR(40), AGENT CHAR(20), ANNIV_DATE SYSNAME 'ANNIV DATE' INTEGER, CITY CHAR(20), DATE_OF_BIRTH SYSNAME 'DATE OF BIRTH' INTEGER, DRIVER_ID SYSNAME 'DRIVER ID' INTEGER, FULLNAME CHAR(40) NOT NULL, MARITAL_STATUS SYSNAME 'MARITAL STATUS' CHAR(15), POLICY_NO SYSNAME 'POLICY NO' CHAR(6) NOT NULL, POLICYHOLDER CHAR(40), RECTYPE CHAR(15) NOT NULL, RESTRICTIONS CHAR(255), SEX CHAR(1), STATE CHAR(25), TOTAL_PREMIUM SYSNAME 'TOTAL PREMIUM' INTEGER, ZIP CHAR(9), PID INTEGER NOT NULL PRIMARY KEY SYSTEM ); GRANT ALL PRIVILEGES ON CLIENTS TO PUBLIC;

ACCIDENTS table, nested under CLIENTS

CREATE TABLE ACCIDENTS NESTED USING PID ( INCIDENT CHAR(2) NOT NULL, INCIDENT_DATE SYSNAME 'INCIDENT DATE' INTEGER NOT NULL, PID INTEGER NOT NULL REFERENCES CLIENTS ); GRANT ALL PRIVILEGES ON ACCIDENTS TO PUBLIC;

INSURED-VINS table, nested under CLIENTS

CREATE TABLE INSURED_VINS NESTED USING PID ( VIN CHAR(12) NOT NULL, PID INTEGER NOT NULL REFERENCES CLIENTS ); GRANT ALL PRIVILEGES ON INSURED_VINS TO PUBLIC;

VEHICLES table

CREATE TABLE VEHICLES (BODY CHAR(4), COLLISION_PREMIUM SYSNAME 'COLLISION PREMIUM' INTEGER, COLOR CHAR(15), DEDUCTIBLE DECIMAL(3,0), GARAGING_LOCATION SYSNAME 'GARAGING LOCATION' CHAR(4), LIABILITY_LIMIT SYSNAME 'LIABILITY LIMIT' DECIMAL(4,0), LIABILITY_PREMIUM SYSNAME 'LIABILITY PREMIUM' INTEGER, MAKE CHAR(20), MODEL CHAR(20), OWNER_POLICY SYSNAME 'OWNER POLICY' CHAR(6), PRINCIPAL_DRIVER SYSNAME 'PRINCIPLE DRIVER' INTEGER, SURCHARGE SYSNAME 'SURCHARGE%' CHAR(2), TRANS CHAR(2), USAGE CHAR(60), VEHICLE_PREMIUM SYSNAME 'VEHICLE PREMIUM' INTEGER, VEHICLE_RATING SYSNAME 'VEHICLE RATING' CHAR(1), VEHICLE_USE_CLASS SYSNAME 'VEHICLE USE CLASS' CHAR(2), VIN CHAR(10) NOT NULL PRIMARY KEY, YEAR DECIMAL(4,0)); GRANT ALL PRIVILEGES ON VEHICLES TO PUBLIC;

OTHER_DRIVER table, nested under VEHICLES

CREATE TABLE OTHER_DRIVER NESTED USING VIN (OTHER_DRIVER SYSNAME 'OTHER DRIVER' CHAR(6) NOT NULL, VIN CHAR(10) NOT NULL UNIQUE REFERENCES VEHICLES); GRANT ALL PRIVILEGES ON OTHER_DRIVER TO PUBLIC;

CLAIMS03 table

CREATE TABLE CLAIMS03 (CLAIM_NO SYSNAME 'CLAIM NO' INTEGER NOT NULL UNIQUE, CLAIM_STATUS SYSNAME 'CLAIM STATUS' CHAR(15), CLAIM_TYPE SYSNAME 'CLAIM TYPE' CHAR(1), CLAIMEE CHAR(255), DRIVER_INVOLVED SYSNAME 'DRIVER INVOLVED' INTEGER, LOCATION CHAR(4), MISC_CLAIM_DESC SYSNAME 'MISC CLAIM DESC' CHAR(100), POLICY_NO SYSNAME 'POLICY NO' CHAR(6), SETTLEMENT_AMOUNT SYSNAME 'SETTLEMENT AMOUNT' INTEGER, SETTLEMENT_DATE SYSNAME 'SETTLEMENT DATE' INTEGER, TIME CHAR(4), VIN_INVOLVED SYSNAME 'VIN INVOLVED' CHAR(10), WEATHER CHAR(20)); GRANT ALL PRIVILEGES ON CLAIMS03 TO PUBLIC;

VIEWS against the CLIENTS table

The following views of the CLIENTS table represent only one of many ways of constructing a set of views for the table.

POLICIES view

CREATE VIEW POLICIES (ADDRESS, AGENT, ANNIV_DATE, CITY, DATE_OF_BIRTH, FULLNAME, POLICY_NO, POLICYHOLDER, STATE, TOTAL_PREMIUM, ZIP) AS SELECT ADDRESS, AGENT, ANNIV_DATE, CITY, DATE_OF_BIRTH, FULLNAME, POLICY_NO, POLICYHOLDER, STATE, TOTAL_PREMIUM, ZIP FROM CLIENTS WHERE RECTYPE = 'POLICYHOLDER'; GRANT ALL PRIVILEGES ON POLICIES TO PUBLIC;

DRIVERS view

CREATE VIEW DRIVERS (DATE_OF_BIRTH, DRIVER_ID, FULLNAME, MARITAL_STATUS, POLICY_NO, SEX, STATE) AS SELECT DATE_OF_BIRTH, DRIVER_ID, FULLNAME, MARITAL_STATUS, POLICY_NO, SEX, STATE FROM CLIENTS WHERE RECTYPE = 'DRIVER'; GRANT ALL PRIVILEGES ON DRIVERS TO PUBLIC;