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
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