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