Tables Essay Research Paper DROP TABLE contractDROP

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

INSE

Внимание, отключите Adblock

Вы посетили наш сайт со включенным блокировщиком рекламы!
Ссылка для скачивания станет доступной сразу после отключения Adblock!

Скачать