Tables Essay, Research Paper

DROP TABLE contract;

DROP TABLE client;

DROP TABLE organization;

DROP TABLE expertise;

DROP TABLE consultant;

DROP TABLE classification;

DROP TABLE region;

CREATE TABLE region

(reg_num NUMBER(2) CONSTRAINT region_regnum_pk PRIMARY KEY,

reg_name VARCHAR2(30) CONSTRAINT region_reg_name_nn NOT NULL);

CREATE TABLE classification

(class_num NUMBER(4) CONSTRAINT classification_classnum_pk PRIMARY KEY,

class_type VARCHAR2(30) CONSTRAINT classification_classtype_nn NOT NULL);

CREATE TABLE consultant

(cons_num NUMBER(8) CONSTRAINT consultant_consnum_pk PRIMARY KEY,

cons_lname VARCHAR2(30),

cons_fname VARCHAR2(30),

cons_mi VARCHAR2(1),

cons_add VARCHAR2(30),

cons_city VARCHAR2(30),

cons_state VARCHAR2(2),

cons_zip VARCHAR2(5),

cons_phone VARCHAR2(10),

cons_pay NUMBER(8),

reg_num NUMBER(2) CONSTRAINT consultant_reg_num_fk

REFERENCES region(reg_num));

CREATE TABLE expertise

(cons_num NUMBER(8) CONSTRAINT expertise_cons_num_fk

REFERENCES consultant(cons_num),

class_num NUMBER(4) CONSTRAINT expertise_class_num_fk

REFERENCES classification(class_num),

PRIMARY KEY (cons_num, class_num));

CREATE TABLE organization

(org_num NUMBER(8) CONSTRAINT organization_org_num_pk PRIMARY KEY,

org_name VARCHAR2(30),

reg_num NUMBER(2) CONSTRAINT organization_org_num_fk

REFERENCES region(reg_num));

CREATE TABLE client

(client_num NUMBER(8) CONSTRAINT client_client_num_pk PRIMARY KEY,

client_lname VARCHAR2(30),

client_fname VARCHAR2(30),

client_mi VARCHAR2(1),

client_phone VARCHAR2(10),

client_add VARCHAR2(30),

client_city VARCHAR2(30),

client_state VARCHAR2(2),

client_zip VARCHAR2(5),

org_num NUMBER(8) CONSTRAINT client_org_num_fk

REFERENCES organization(org_num),

reg_num NUMBER(2) CONSTRAINT client_reg_num_fk

REFERENCES region(reg_num));

CREATE TABLE contract

(contr_num NUMBER(10) CONSTRAINT contract_contr_num_pk PRIMARY KEY,

contr_date DATE CONSTRAINT contract_contr_date_nn NOT NULL,

contr_desc VARCHAR2(30),

contr_status VARCHAR2(30),

contr_hours NUMBER(8),

client_num NUMBER(8) CONSTRAINT contract_client_num_fk

REFERENCES client(client_num),

class_num NUMBER(4) CONSTRAINT contract_class_num_fk

REFERENCES classification(class_num),

cons_num NUMBER(8) CONSTRAINT contract_cons_num_fk

REFERENCES consultant(cons_num));

— inserting records into REGION

INSERT INTO region VALUES

(1, ‘North East’);

INSERT INTO region VALUES

(2, ‘South East’);

INSERT INTO region VALUES

(3, ‘Mid West’);

INSERT INTO region VALUES

(4, ‘North West’);

INSERT INTO region VALUES

(5, ‘South West’);

— inserting records into CLASSIFICATION

INSERT INTO classification VALUES

(01, ‘Networking’);

INSERT INTO classification VALUES

(02, ‘Database’);

— inserting records into CONSULTANT

INSERT INTO consultant VALUES

(201, ‘Ryan’, ‘Jessica’, ‘B’, ‘3 Telegraph St. – Apt. 3N’, ‘Binghamton’,

‘NY’, 13903, ‘6077727361′, 200, 1);

INSERT INTO consultant VALUES

(202, ‘Pruskowski’, ‘Heather’, ‘C’, ‘273 Ryan St’, ‘Oxford’,

‘NY’, 13830, ‘6078438435′, 200, 1);

INSERT INTO consultant VALUES

(203, ‘Hill’, ‘Grant’, ‘R’, ‘5 Redmont Lane’, ‘Detroit’,

‘MI’, 18956, ‘7035679078′, 150, 3);

INSERT INTO consultant VALUES

(204, ‘Hadwin’, ‘Jeffrey’, ‘T’, ‘4 University Ave.’, ‘Ann Arbor’,

‘MI’, 16895, ‘7035689087′, 150, 3);

INSERT INTO consultant VALUES

(205, ‘Stewart’, ‘Rodney’, ‘G’, ‘4 Tulip Blvd.’, ‘Phoenix’,

‘AZ’, 15648, ‘6053346758′, 175, 5);

INSERT INTO consultant VALUES

(206, ‘Laettner’, ‘Christian’, ‘M’, ‘678 Lovers Lane’, ‘Las Vegas’,

‘NV’, 15689, ‘5674567896′, 175, 5);

INSERT INTO consultant VALUES

(207, ‘Carter’, ‘Vince’, ‘L’, ‘465 Laurel Ave’, ‘Durham’,

‘NC’, 16897, ‘4568972345′, 100, 2);

INSERT INTO consultant VALUES

(208, ‘Lang’, ‘Antonio’, ‘M’, ‘234 Kentral Blvd.’, ‘Orlando’,

‘FL’, 19786, ‘3546782341′, 100, 2);

INSERT INTO consultant VALUES

(209, ‘Trelmont’, ‘Michelle’, ‘E’, ‘3241 Park Place’, ‘Portland’,

‘OR’, 14875, ‘5684587894′, 175, 4);

INSERT INTO consultant VALUES

(210, ‘Brand’, ‘Elton’, ‘G’, ‘23 Tremont Rd.’, ‘Seattle’,

‘WA’, 15649, ‘7845268459′, 175, 4);

— inserting records into EXPERTISE

INSERT INTO expertise VALUES

(201, 01);

INSERT INTO expertise VALUES

(202, 02);

INSERT INTO expertise VALUES

(203, 01);

INSERT INTO expertise VALUES

(204, 02);

INSERT INTO expertise VALUES

(205, 01);

INSERT INTO expertise VALUES

(206, 02);

INSERT INTO expertise VALUES

(207, 01);

INSERT INTO expertise VALUES

(208, 02);

INSERT INTO expertise VALUES

(209, 01);

INSERT INTO expertise VALUES

(210, 02);

— inserting records into ORGANIZATION

INSERT INTO organization VALUES

(10, ‘Simulation and Control Tech.’, 1);

INSERT INTO organization VALUES

(9, ‘Morgan Stanley Dean Witter’, 4);

INSERT INTO organization VALUES

(8, ‘First Trade’, 2);

INSERT INTO organization VALUES

(7, ‘First Investors’, 5);

INSERT INTO organization VALUES

(6, ‘E-Toys’, 4);

— inserting records into CLIENT

INSERT INTO client VALUES

(100, ‘Smith’, ‘James’, ‘D’, ‘3157896578′, ‘145 Park Slope Dr.’, ‘Syracuse’,

‘NY’, 13029, 10, 1);

INSERT INTO client VALUES

(101, ‘LeClerc’, ‘Patrick’, ‘A’, ‘9174673256′, ‘123 Miami Beach Dr.’, ‘Miami’,

‘FL’, 19876, NULL, 2);

INSERT INTO client VALUES

(102, ‘Hyde’, ‘Kirk’, ‘G’, ‘5128972341′, ‘897 Lombard St.’, ‘San Francisco’,

‘CA’, 45876, 6, 5);

INSERT INTO client VALUES

(103, ‘Kale’, ‘William’, ‘L’, ‘8173253476′, ‘112 Farview Way’, ‘Seattle’,

‘WA’, 78691, 9, 4);

INSERT INTO client VALUES

(104, ‘Lannis’, ‘Frank’, ‘P’, ‘4135872351′, ‘98 Worth Ave.’, ‘Omaha’,

‘NE’, 23657, NULL, 3);

INSERT INTO client VALUES

(105, ‘Lasda’, ‘Jeffrey’, ‘S’, ‘3644428127′, ‘75 Router Blvd.’, ‘Los Angeles’,

‘CA’, 47123, 7, 5);

INSERT INTO client VALUES

(106, ‘Riordan’, ‘Hilda’, ‘F’, ‘3127652341′, ‘14 Gateway Pk.’, ‘Hobokin’,

‘NJ’, 18675, NULL, 1);

INSERT INTO client VALUES

(107, ‘Lee’, ‘Elizabeth’, ‘A’, ‘5466672341′, ‘77 Energy Terrace’, ‘Atlanta’,

‘GA’, 22567, 8, 2);

INSERT INTO client VALUES

(108, ‘Powers’, ‘Allen’, ‘M’, ‘4123350978′, ‘45 Canal St.’, ‘Lincoln’,

‘NE’, 26778, NULL, 3);

INSERT INTO client VALUES

(109, ‘Racee’, ‘Bertha’, ‘Q’, ‘1158763452′, ‘12 Potto Way’, ‘Boise’,

‘ID’, 45617, 6, 4);

— inserting records into CONTRACT

INSERT INTO contract VALUES

(1000, TO_DATE(’12/25/99′, ‘MM/DD/YY’), ‘Santa Maria Foundation’, ‘completed’, 35,

100, 01, 201);

INSERT INTO contract VALUES

(1001, TO_DATE(’03/08/00′, ‘MM/DD/YY’), ‘Jerry’’s Kids’, ‘IP’, NULL,

108, 02, 204);

INSERT INTO contract VALUES

(1002, TO_DATE(’04/12/00′, ‘MM/DD/YY’), ‘Borders’, ‘IP’, NULL,

108, 01, 203);

INSERT INTO contract VALUES

(1003, TO_DATE(’02/18/00′, ‘MM/DD/YY’), ‘Industrial Waste’, ‘completed’, 50,

106, 02, 202);

INSERT INTO contract VALUES

(1004, TO_DATE(’11/30/99′, ‘MM/DD/YY’), ‘Asian NPO’, ‘completed’, 20,

107, 01, 207);

INSERT INTO contract VALUES

(1005, TO_DATE(’01/14/00′, ‘MM/DD/YY’), ‘Internal Affairs’, ‘completed’, 48,

105, 01, 206);

INSERT INTO contract VALUES

(1006, TO_DATE(’12/02/99′, ‘MM/DD/YY’), ‘Routers’, ‘IP’, NULL,

104, 02, 203);

INSERT INTO contract VALUES

(1007, TO_DATE(’04/01/00′, ‘MM/DD/YY’), ‘Government Planning’, ‘IP’, NULL,

102, 01, 205);

INSERT INTO contract VALUES

(1008, TO_DATE(’10/30/99′, ‘MM/DD/YY’), ‘International Peace Foundation’, ‘completed’, 80, 103, 02, 209);

INSERT INTO contract VALUES

(1009, TO_DATE(’03/24/00′, ‘MM/DD/YY’), ‘Viacom’, ‘IP’, NULL,

101, 02, 208);

INSERT INTO contract VALUES

(1010, TO_DATE(’02/01/00′, ‘MM/DD/YY’), ‘Nova’, ‘IP’, NULL,

109, 02, 210);

COMMIT;

Tables foe computer science