Структура базы данных

CREATE TABLE PASSPORT_DATE

(

SER_NUMBER CHAR(11) NOT NULL,

ISSUED VARCHAR(255) NOT NULL,

DATE_ISSUED DATE NOT NULL,

CODE_DIVISION CHAR(7) NOT NULL

);

ALTER TABLE PASSPORT_DATE

ADD PRIMARY KEY (SER_NUMBER);

CREATE TABLE SUBSCRIBER

(

FIO Varchar(255) NOT NULL,

ADRESS Varchar(255) NOT NULL,

TEL Varchar(20) NOT NULL,

SER_NUM_FK char(11) NOT NULL,

PRIMARY KEY (SER_NUM_FK)

);

ALTER TABLE SUBSCRIBER ADD

FOREIGN KEY (SER_NUM_FK) REFERENCES PASSPORT_DATA(SER_NUMBER);

CREATE TABLE OPERATOR

(

ID_OP INTEGER NOT NULL,

FIO VARCHAR(255) NOT NULL,

PRIMARY KEY (ID_OP)

);

CREATE TABLE RATE

(

ID_RATE INTEGER NOT NULL,

TITLE VARCHAR(255) NOT NULL,

COAST_CONNECT DECIMAL(10,4) NOT NULL,

RENTAL_FEE DECIMAL(10,4) NOT NULL,

DESCRIPTION VARCHAR(255) NOT NULL,

PRIMARY KEY (ID_RATE)

);

CREATE TABLE REPAIR

(

ID_WORK INTEGER NOT NULL,

TITLE VARCHAR(255) NOT NULL,

DESCRIPTION VARCHAR(255) NOT NULL,

COAST DECIMAL(10,4) NOT NULL,

PRIMARY KEY (ID_WORK)

);

CREATE TABLE SERVICE (

ID_SERV INTEGER NOT NULL,

TITLE VARCHAR(255) NOT NULL,

COAST DECIMAL(10,4) NOT NULL,

TERM_OF VARCHAR(20) NOT NULL,

PRIMARY KEY (ID_SERV)

);

CREATE TABLE PACT(

S_NUM_FK CHAR(11) NOT NULL,

N_PACT Integer NOT NULL,

N_PA Integer NOT NULL,

TITLE_PACT Varchar(255) NOT NULL,

STATE PACT_STATE DEFAULT 'Действует' NOT NULL,

DATE_CONC Date NOT NULL,

DATE_DISCONC Date,

ID_OP_FK Integer NOT NULL,

PRIMARY KEY (N_PACT,N_PA)

);

ALTER TABLE PACT

ADD FOREIGN KEY (S_NUM_FK) REFERENCES SUBSCRIBER (SER_NUM_FK),

ADD FOREIGN KEY (ID_OP_FK) REFERENCES OPERATOR (ID_OP);

CREATE TABLE CON_SERVICE

(

ID_CON_SERVICE Integer NOT NULL,

N_PACT_FK Integer NOT NULL,

N_PA_FK Integer NOT NULL,

ID_SERVICE_FK INTEGER,

STATUS_S SERVICE_STATE,

DATE_CON_S DATE NOT NULL,

DATE_DISCON_S DATE,

PRIMARY KEY (ID_CON_SERVICE)

);

ALTER TABLE CON_SERVICE

ADD FOREIGN KEY (N_PACT_FK,N_PA_FK) REFERENCES PACT (N_PACT,N_PA),

ADD FOREIGN KEY (ID_SERVICE_FK) REFERENCES SERVICE (ID_SERV);

CREATE TABLE CARRIED_WORK

(

ID_CARRIED_WORK Integer NOT NULL,

NPACT_FK Integer NOT NULL,

NPA_FK Integer NOT NULL,

ID_WORK_FK INTEGER,

DATE_W DATE NOT NULL,

PRIMARY KEY (ID_CARRIED_WORK)

);

ALTER TABLE CARRIED_WORK

ADD FOREIGN KEY (NPACT_FK,NPA_FK) REFERENCES PACT (N_PACT,N_PA),

ADD FOREIGN KEY (ID_WORK_FK) REFERENCES REPAIR (ID_WORK);

CREATE TABLE CON_RATE

(

ID_CON_RATE INTEGER NOT NULL,

NPACTFK Integer NOT NULL,

NPAFK Integer NOT NULL,

ID_RATE_FK INTEGER NOT NULL,

STATUS_R RATE_STATE,

DATE_CON_R DATE NOT NULL,

DATE_DISCON_R DATE,

PRIMARY KEY (ID_CON_RATE)

);

ALTER TABLE CON_RATE

ADD FOREIGN KEY (NPACTFK,NPAFK) REFERENCES PACT (N_PACT,N_PA),

ADD FOREIGN KEY (ID_RATE_FK) REFERENCES RATE (ID_RATE);

Дата добавления: 2015-07-10