ELowell: /* DRIVERS view */

2015-04-01T21:19:23Z

DRIVERS view

← Older revision		Revision as of 21:19, 1 April 2015
Line 210:		Line 210:
	GRANT ALL PRIVILEGES ON DRIVERS TO PUBLIC;		GRANT ALL PRIVILEGES ON DRIVERS TO PUBLIC;
	</p>		</p>

			==See also==
			{{Template:SQL topic list}}

	[[Category: Model 204 SQL processing]]		[[Category: Model 204 SQL processing]]

ELowell: Created page with "==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 table..."

2015-03-24T20:54:03Z

Created page with "==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 table..."

New page

==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:
<ul>
<li>This DDL maps to the Version 6.1 demonstration database files. Ensure that you have the correct set of files attached to your Online.</li>
<li>You must supply a value for the placeholder xxxx in the DDL below (for SCHEMA NAME). </li>
</ul>
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;


[[Category: Model 204 SQL processing]]

SQL DDL mapping of the demonstration database - Revision history

ELowell: /* DRIVERS view */

ELowell: Created page with "==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 table..."