bonus (DBA)
call_track (DBA)
contact (DBA)
customer (DBA)
department (DBA)
employee (DBA)
exam_xref_info (DBA)
exam_xref_list (DBA)
examples (DBA)
examples_categories_list (DBA)
examples_groups (DBA)
examples_previews (DBA)
fin_code (DBA)
fin_data (DBA)
ole (DBA)
printer (DBA)
product (DBA)
province (DBA)
sales_order (DBA)
sales_order_items (DBA)
states (DBA)
terminated_employee (DBA)
CREATE TABLE DBA.bonus (
emp_id /* PK */ INTEGER NOT NULL,
bonus_date /* PK */ DATE NOT NULL,
bonus_amount NUMERIC ( 9, 2 ) NULL );
// ***** Primary Key for DBA.bonus *****
ALTER TABLE DBA.bonus ADD PRIMARY KEY (
emp_id,
bonus_date );
CREATE TABLE DBA.call_track (
id /* PK */ INTEGER DEFAULT autoincrement NOT NULL,
call_date DATE NOT NULL,
caller_name VARCHAR ( 30 ) NOT NULL,
call_notes VARCHAR ( 32765 ) NOT NULL );
// ***** Primary Key for DBA.call_track *****
ALTER TABLE DBA.call_track ADD PRIMARY KEY (
id );
CREATE TABLE DBA.contact (
id /* PK */ INTEGER NOT NULL,
last_name CHAR ( 15 ) NOT NULL,
first_name CHAR ( 15 ) NOT NULL,
title CHAR ( 2 ) NOT NULL,
street CHAR ( 30 ) NOT NULL,
city CHAR ( 20 ) NOT NULL,
state CHAR ( 2 ) NOT NULL,
zip CHAR ( 5 ) NOT NULL,
phone CHAR ( 10 ) NULL,
fax CHAR ( 10 ) NULL );
// ***** Primary Key for DBA.contact *****
ALTER TABLE DBA.contact ADD PRIMARY KEY (
id );
CREATE TABLE DBA.customer (
id /* PK */ INTEGER NOT NULL,
fname /* X */ CHAR ( 15 ) NOT NULL,
lname /* X */ CHAR ( 20 ) NOT NULL,
address CHAR ( 35 ) NOT NULL,
city CHAR ( 20 ) NOT NULL,
state CHAR ( 2 ) NOT NULL,
zip CHAR ( 10 ) NOT NULL,
phone CHAR ( 12 ) NOT NULL,
company_name CHAR ( 35 ) NULL );
// ***** Children of DBA.customer *****
// DBA.sales_order
// ***** Primary Key for DBA.customer *****
ALTER TABLE DBA.customer ADD PRIMARY KEY (
id );
// ***** Non-Unique Index for DBA.customer *****
CREATE INDEX ix_cust_name ON DBA.customer (
lname ASC,
fname ASC );
CREATE TABLE DBA.department (
dept_id /* PK */ INTEGER NOT NULL,
dept_name CHAR ( 40 ) NOT NULL,
dept_head_id /* FK */ INTEGER NULL );
// ***** Parents of DBA.department *****
// DBA.employee
// ***** Children of DBA.department *****
// DBA.employee
// ***** Primary Key for DBA.department *****
ALTER TABLE DBA.department ADD PRIMARY KEY (
dept_id );
// ***** Foreign Key from DBA.department to Parent DBA.employee *****
ALTER TABLE DBA.department ADD FOREIGN KEY ky_dept_head (
dept_head_id )
REFERENCES DBA.employee (
emp_id ) ON UPDATE RESTRICT ON DELETE SET NULL
CREATE TABLE DBA.employee (
emp_id /* PK */ INTEGER NOT NULL,
manager_id INTEGER NULL,
emp_fname CHAR ( 20 ) NOT NULL,
emp_lname CHAR ( 20 ) NOT NULL,
dept_id /* FK */ INTEGER NOT NULL,
street CHAR ( 40 ) NOT NULL,
city CHAR ( 20 ) NOT NULL,
state CHAR ( 4 ) NOT NULL,
zip_code CHAR ( 9 ) NOT NULL,
phone CHAR ( 10 ) NULL,
status CHAR ( 1 ) NULL,
ss_number CHAR ( 11 ) NOT NULL,
salary NUMERIC ( 20, 3 ) NOT NULL,
start_date DATE NOT NULL,
termination_date DATE NULL,
birth_date DATE NULL,
bene_health_ins CHAR ( 1 ) NULL,
bene_life_ins CHAR ( 1 ) NULL,
bene_day_care CHAR ( 1 ) NULL,
sex CHAR ( 1 ) NULL );
// ***** Parents of DBA.employee *****
// DBA.department
// ***** Children of DBA.employee *****
// DBA.department
// DBA.sales_order
// ***** Primary Key for DBA.employee *****
ALTER TABLE DBA.employee ADD PRIMARY KEY (
emp_id );
// ***** Foreign Key from DBA.employee to Parent DBA.department *****
ALTER TABLE DBA.employee ADD FOREIGN KEY ky_dept_id (
dept_id )
REFERENCES DBA.department (
dept_id ) ON UPDATE RESTRICT ON DELETE RESTRICT
CREATE TABLE DBA.exam_xref_info (
object_ref /* PK X */ VARCHAR ( 40 ) NOT NULL,
object_ref_type /* X */ VARCHAR ( 100 ) NOT NULL,
event /* PK X */ VARCHAR ( 100 ) NOT NULL,
referenced_in /* PK X */ VARCHAR ( 40 ) NOT NULL,
ref_in_type VARCHAR ( 40 ) NOT NULL,
pbl VARCHAR ( 60 ) NOT NULL,
application /* PK */ CHAR ( 10 ) NOT NULL,
scope CHAR ( 1 ) NULL,
short_event VARCHAR ( 40 ) NULL );
// ***** Primary Key for DBA.exam_xref_info *****
ALTER TABLE DBA.exam_xref_info ADD PRIMARY KEY (
object_ref,
event,
referenced_in,
application );
// ***** Non-Unique Index for DBA.exam_xref_info *****
CREATE INDEX xref_info_idx1 ON DBA.exam_xref_info (
object_ref ASC,
referenced_in ASC );
// ***** Non-Unique Index for DBA.exam_xref_info *****
CREATE INDEX xref_info_idx2 ON DBA.exam_xref_info (
object_ref_type ASC,
referenced_in ASC );
// ***** Non-Unique Index for DBA.exam_xref_info *****
CREATE INDEX xref_info_idx3 ON DBA.exam_xref_info (
referenced_in ASC,
event ASC );
// ***** Non-Unique Index for DBA.exam_xref_info *****
CREATE INDEX xref_info_idx4 ON DBA.exam_xref_info (
referenced_in ASC );
CREATE TABLE DBA.exam_xref_list (
object /* PK */ VARCHAR ( 40 ) NOT NULL,
refer /* PK X */ VARCHAR ( 40 ) NOT NULL,
application /* PK */ CHAR ( 10 ) NOT NULL );
// ***** Primary Key for DBA.exam_xref_list *****
ALTER TABLE DBA.exam_xref_list ADD PRIMARY KEY (
object,
refer,
application );
// ***** Non-Unique Index for DBA.exam_xref_list *****
CREATE INDEX xref_list_idx1 ON DBA.exam_xref_list (
refer ASC );
CREATE TABLE DBA.examples (
title CHAR ( 60 ) NOT NULL,
window /* PK */ CHAR ( 40 ) NOT NULL,
version CHAR ( 5 ) NULL,
description CHAR ( 32765 ) NULL,
technique CHAR ( 32765 ) NULL,
new SMALLINT NULL,
enhanced SMALLINT NULL,
ostype VARCHAR ( 40 ) NULL,
pbtype VARCHAR ( 17 ) NULL,
exhelp SMALLINT NULL );
// ***** Children of DBA.examples *****
// DBA.examples_groups
// DBA.examples_previews
// ***** Primary Key for DBA.examples *****
ALTER TABLE DBA.examples ADD PRIMARY KEY (
window );
CREATE TABLE DBA.examples_categories_list (
group_id /* PK */ CHAR ( 40 ) NOT NULL );
// ***** Children of DBA.examples_categories_list *****
// DBA.examples_groups
// ***** Primary Key for DBA.examples_categories_list *****
ALTER TABLE DBA.examples_categories_list ADD PRIMARY KEY (
group_id );
CREATE TABLE DBA.examples_groups (
group_id /* PK FK */ CHAR ( 40 ) NOT NULL,
window /* PK FK */ CHAR ( 40 ) NOT NULL );
// ***** Parents of DBA.examples_groups *****
// DBA.examples
// DBA.examples_categories_list
// ***** Primary Key for DBA.examples_groups *****
ALTER TABLE DBA.examples_groups ADD PRIMARY KEY (
group_id,
window );
// ***** Foreign Key from DBA.examples_groups to Parent DBA.examples_categories_list *****
ALTER TABLE DBA.examples_groups ADD FOREIGN KEY examples_group_fk2 (
group_id )
REFERENCES DBA.examples_categories_list (
group_id ) ON UPDATE RESTRICT ON DELETE RESTRICT
// ***** Foreign Key from DBA.examples_groups to Parent DBA.examples *****
ALTER TABLE DBA.examples_groups ADD FOREIGN KEY examples_groups_fk (
window )
REFERENCES DBA.examples (
window ) ON UPDATE RESTRICT ON DELETE RESTRICT
CREATE TABLE DBA.examples_previews (
window /* PK FK U */ CHAR ( 40 ) NOT NULL,
screen IMAGE NULL );
// ***** Parents of DBA.examples_previews *****
// DBA.examples
// ***** Primary Key for DBA.examples_previews *****
ALTER TABLE DBA.examples_previews ADD PRIMARY KEY (
window );
// ***** Foreign Key from DBA.examples_previews to Parent DBA.examples *****
ALTER TABLE DBA.examples_previews ADD FOREIGN KEY examples_previews_fk (
window )
REFERENCES DBA.examples (
window ) ON UPDATE RESTRICT ON DELETE CASCADE
// ***** Unique Constraint for DBA.examples_previews *****
ALTER TABLE DBA.examples_previews ADD UNIQUE (
window );
// ***** Unique Index for DBA.examples_previews *****
CREATE UNIQUE INDEX previews_index ON DBA.examples_previews (
window ASC );
CREATE TABLE DBA.fin_code (
code /* PK */ CHAR ( 2 ) NOT NULL,
type CHAR ( 10 ) NOT NULL,
description CHAR ( 50 ) NULL );
// ***** Children of DBA.fin_code *****
// DBA.fin_data
// DBA.sales_order
// ***** Primary Key for DBA.fin_code *****
ALTER TABLE DBA.fin_code ADD PRIMARY KEY (
code );
CREATE TABLE DBA.fin_data (
year /* PK */ CHAR ( 4 ) NOT NULL,
quarter /* PK */ CHAR ( 2 ) NOT NULL,
code /* PK FK X */ CHAR ( 2 ) NOT NULL,
amount NUMERIC ( 9 ) NULL );
// ***** Parents of DBA.fin_data *****
// DBA.fin_code
// ***** Primary Key for DBA.fin_data *****
ALTER TABLE DBA.fin_data ADD PRIMARY KEY (
year,
quarter,
code );
// ***** Foreign Key from DBA.fin_data to Parent DBA.fin_code *****
ALTER TABLE DBA.fin_data ADD FOREIGN KEY ky_code_data (
code )
REFERENCES DBA.fin_code (
code ) ON UPDATE RESTRICT ON DELETE CASCADE
// ***** Non-Unique Index for DBA.fin_data *****
CREATE INDEX fin_data_idx ON DBA.fin_data (
code ASC );
// DBA . ole Table
CREATE TABLE DBA.ole (
id /* PK */ CHAR ( 20 ) NOT NULL,
object IMAGE NULL,
description VARCHAR ( 32765 ) NOT NULL );
// ***** Primary Key for DBA.ole *****
ALTER TABLE DBA.ole ADD PRIMARY KEY (
id );
CREATE TABLE DBA.printer (
rep /* PK */ CHAR ( 10 ) NOT NULL,
quarter /* PK */ CHAR ( 2 ) NOT NULL,
product /* PK */ CHAR ( 12 ) NOT NULL,
units INTEGER NOT NULL );
// ***** Primary Key for DBA.printer *****
ALTER TABLE DBA.printer ADD PRIMARY KEY (
rep,
quarter,
product );
CREATE TABLE DBA.product (
id /* PK */ INTEGER NOT NULL,
name /* X */ CHAR ( 15 ) NOT NULL,
description /* X */ CHAR ( 30 ) NOT NULL,
prod_size /* X */ CHAR ( 18 ) NOT NULL,
color /* X */ CHAR ( 6 ) NOT NULL,
quantity INTEGER NOT NULL,
unit_price NUMERIC ( 15, 2 ) NOT NULL,
picture_name CHAR ( 12 ) NULL,
catalog_picture IMAGE NULL );
// ***** Children of DBA.product *****
// DBA.sales_order_items
// ***** Primary Key for DBA.product *****
ALTER TABLE DBA.product ADD PRIMARY KEY (
id );
// ***** Non-Unique Index for DBA.product *****
CREATE INDEX ix_prod_color ON DBA.product (
color ASC );
// ***** Non-Unique Index for DBA.product *****
CREATE INDEX ix_prod_desc ON DBA.product (
description ASC );
// ***** Non-Unique Index for DBA.product *****
CREATE INDEX ix_prod_name ON DBA.product (
name ASC );
// ***** Non-Unique Index for DBA.product *****
CREATE INDEX ix_prod_size ON DBA.product (
prod_size ASC );
CREATE TABLE DBA.province (
province_code /* PK */ VARCHAR ( 2 ) NOT NULL,
province_name VARCHAR ( 50 ) NOT NULL );
// ***** Primary Key for DBA.province *****
ALTER TABLE DBA.province ADD PRIMARY KEY (
province_code );
CREATE TABLE DBA.sales_order (
id /* PK */ INTEGER NOT NULL,
cust_id /* FK X */ INTEGER NOT NULL,
order_date DATE NOT NULL,
fin_code_id /* FK */ CHAR ( 2 ) NULL,
region CHAR ( 7 ) NULL,
sales_rep /* FK */ INTEGER NOT NULL );
// ***** Parents of DBA.sales_order *****
// DBA.customer
// DBA.employee
// DBA.fin_code
// ***** Children of DBA.sales_order *****
// DBA.sales_order_items
// ***** Primary Key for DBA.sales_order *****
ALTER TABLE DBA.sales_order ADD PRIMARY KEY (
id );
// ***** Foreign Key from DBA.sales_order to Parent DBA.customer *****
ALTER TABLE DBA.sales_order ADD FOREIGN KEY ky_so_customer (
cust_id )
REFERENCES DBA.customer (
id ) ON UPDATE RESTRICT ON DELETE RESTRICT
// ***** Foreign Key from DBA.sales_order to Parent DBA.employee *****
ALTER TABLE DBA.sales_order ADD FOREIGN KEY ky_so_employee_id (
sales_rep )
REFERENCES DBA.employee (
emp_id ) ON UPDATE RESTRICT ON DELETE RESTRICT
// ***** Foreign Key from DBA.sales_order to Parent DBA.fin_code *****
ALTER TABLE DBA.sales_order ADD FOREIGN KEY ky_so_fincode (
fin_code_id )
REFERENCES DBA.fin_code (
code ) ON UPDATE RESTRICT ON DELETE SET NULL
// ***** Non-Unique Index for DBA.sales_order *****
CREATE INDEX ix_sales_cust ON DBA.sales_order (
cust_id ASC );
CREATE TABLE DBA.sales_order_items (
id /* PK FK */ INTEGER NOT NULL,
line_id /* PK */ SMALLINT NOT NULL,
prod_id /* FK X */ INTEGER NOT NULL,
quantity INTEGER NOT NULL,
ship_date DATE NOT NULL );
// ***** Parents of DBA.sales_order_items *****
// DBA.product
// DBA.sales_order
// ***** Primary Key for DBA.sales_order_items *****
ALTER TABLE DBA.sales_order_items ADD PRIMARY KEY (
id,
line_id );
// ***** Foreign Key from DBA.sales_order_items to Parent DBA.product *****
ALTER TABLE DBA.sales_order_items ADD FOREIGN KEY ky_prod_id (
prod_id )
REFERENCES DBA.product (
id ) ON UPDATE RESTRICT ON DELETE RESTRICT
// ***** Foreign Key from DBA.sales_order_items to Parent DBA.sales_order *****
ALTER TABLE DBA.sales_order_items ADD FOREIGN KEY id_fk (
id )
REFERENCES DBA.sales_order (
id ) ON UPDATE RESTRICT ON DELETE CASCADE
// ***** Non-Unique Index for DBA.sales_order_items *****
CREATE INDEX ix_item_prod ON DBA.sales_order_items (
prod_id ASC );
CREATE TABLE DBA.states (
state_id /* PK */ CHAR ( 3 ) NOT NULL,
state_name CHAR ( 24 ) NOT NULL,
state_capital /* PK */ CHAR ( 24 ) NOT NULL,
country CHAR ( 3 ) NOT NULL );
// ***** Primary Key for DBA.states *****
ALTER TABLE DBA.states ADD PRIMARY KEY (
state_id,
state_capital );
CREATE TABLE DBA.terminated_employee (
emp_id /* PK */ INTEGER NOT NULL,
emp_fname VARCHAR ( 20 ) NOT NULL,
emp_lname VARCHAR ( 20 ) NOT NULL,
start_date DATE NOT NULL,
termination_date DATE NULL,
dept_id INTEGER NOT NULL,
street VARCHAR ( 40 ) NOT NULL,
city VARCHAR ( 20 ) NOT NULL,
state VARCHAR ( 4 ) NOT NULL,
zip_code VARCHAR ( 9 ) NOT NULL,
phone VARCHAR ( 10 ) NULL,
ss_number VARCHAR ( 11 ) NOT NULL,
salary NUMERIC ( 20, 3 ) NOT NULL,
sex VARCHAR ( 1 ) NULL );
// ***** Primary Key for DBA.terminated_employee *****
ALTER TABLE DBA.terminated_employee ADD PRIMARY KEY (
emp_id );