eddoc
Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
-- Domains
CREATE DOMAIN DMN_BLOBTXT AS
BLOB SUB_TYPE 1 SEGMENT SIZE 80 CHARACTER SET UTF8;
CREATE DOMAIN DMN_BOOL AS
SMALLINT
DEFAULT 0
NOT NULL
CHECK (VALUE IN (0,1));
CREATE DOMAIN DMN_DATE AS
DATE
DEFAULT CURRENT_DATE
NOT NULL;
-- Tables
CREATE TABLE TBL_CLINIC (-- табля с клиниками
ID_CLINIC INTEGER NOT NULL,
NAME_CLINIC VARCHAR(100) CHARACTER SET UTF8 NOT NULL
);
CREATE TABLE TBL_DOCTOR (-- табля с докторами
ID_DOCTOR INTEGER NOT NULL,
FIO_DOCTOR VARCHAR(100) CHARACTER SET UTF8 NOT NULL
);
CREATE TABLE TBL_PATIENTS_GENERAL (-- пациенты с оформленной амб.картой
ID_PATIENTS_GENERAL INTEGER NOT NULL,
FIO_PATIENTS_GENERAL VARCHAR(100) CHARACTER SET UTF8 NOT NULL
);
CREATE TABLE TBL_PATIENTS_TEMP (-- пациенты без оформленной амб.карты
ID_PATIENTS_TEMP INTEGER NOT NULL,
FIO_PATIENTS_TEMP VARCHAR(100) CHARACTER SET UTF8 NOT NULL
);
CREATE TABLE TBL_PLANNER (-- табля расписания
ID_PLANNER INTEGER NOT NULL,
FK_DOCTOR INTEGER NOT NULL,
FK_CLINIC INTEGER NOT NULL,
FK_ROOM INTEGER NOT NULL,
FK_PATIENTS_GENERAL INTEGER NOT NULL,
FK_PATIENTS_TEMP INTEGER NOT NULL,
DATE_PLANNER DMN_DATE,
NOTES_PLANNER DMN_BLOBTXT
);
CREATE TABLE TBL_ROOM (--табля кабинетов
ID_ROOM INTEGER NOT NULL,
NUMB_ROOM INTEGER,
NAME_ROOM VARCHAR(20) CHARACTER SET UTF8 DEFAULT 'Unnamed Room' NOT NULL
);
CREATE TABLE TBL_TIME_SLICE (--табля интервалов
ID_TIME_SLICE INTEGER NOT NULL,
FK_PLANNER INTEGER NOT NULL,
TIME_SLICE_300 DMN_BOOL,
TIME_SLICE_310 DMN_BOOL,
TIME_SLICE_320 DMN_BOOL,
TIME_SLICE_340 DMN_BOOL,
TIME_SLICE_350 DMN_BOOL,
TIME_SLICE_360 DMN_BOOL,
TIME_SLICE_370 DMN_BOOL,
TIME_SLICE_380 DMN_BOOL
);
-- Primary Keys
ALTER TABLE TBL_CLINIC ADD CONSTRAINT PK_TBL_CLINIC PRIMARY KEY (ID_CLINIC);
ALTER TABLE TBL_DOCTOR ADD CONSTRAINT PK_TBL_DOCTOR PRIMARY KEY (ID_DOCTOR);
ALTER TABLE TBL_PATIENTS_GENERAL ADD CONSTRAINT PK_TBL_PATIENTS_GENERAL PRIMARY KEY (ID_PATIENTS_GENERAL);
ALTER TABLE TBL_PATIENTS_TEMP ADD CONSTRAINT PK_TBL_PATIENTS_TEMP PRIMARY KEY (ID_PATIENTS_TEMP);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT PK_TBL_PLANNER PRIMARY KEY (ID_PLANNER);
ALTER TABLE TBL_ROOM ADD CONSTRAINT PK_TBL_ROOM PRIMARY KEY (ID_ROOM);
ALTER TABLE TBL_TIME_SLICE ADD CONSTRAINT PK_TBL_TIME_SLICE PRIMARY KEY (ID_TIME_SLICE);
-- Foreign Keys
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_1 FOREIGN KEY (FK_DOCTOR) REFERENCES TBL_DOCTOR (ID_DOCTOR);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_2 FOREIGN KEY (FK_CLINIC) REFERENCES TBL_CLINIC (ID_CLINIC);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_3 FOREIGN KEY (FK_ROOM) REFERENCES TBL_ROOM (ID_ROOM);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_4 FOREIGN KEY (FK_PATIENTS_GENERAL) REFERENCES TBL_PATIENTS_GENERAL (ID_PATIENTS_GENERAL);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_5 FOREIGN KEY (FK_PATIENTS_TEMP) REFERENCES TBL_PATIENTS_TEMP (ID_PATIENTS_TEMP);
ALTER TABLE TBL_TIME_SLICE ADD CONSTRAINT FK_TBL_TIME_SLICE_1 FOREIGN KEY (FK_PLANNER) REFERENCES TBL_PLANNER (ID_PLANNER);
COMMIT WORK;
-- Primary Keys
ALTER TABLE TBL_CLINIC ADD CONSTRAINT PK_TBL_CLINIC PRIMARY KEY (ID_CLINIC);
ALTER TABLE TBL_DOCTOR ADD CONSTRAINT PK_TBL_DOCTOR PRIMARY KEY (ID_DOCTOR);
ALTER TABLE TBL_PATIENTS_GENERAL ADD CONSTRAINT PK_TBL_PATIENTS_GENERAL PRIMARY KEY (ID_PATIENTS_GENERAL);
ALTER TABLE TBL_PATIENTS_TEMP ADD CONSTRAINT PK_TBL_PATIENTS_TEMP PRIMARY KEY (ID_PATIENTS_TEMP);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT PK_TBL_PLANNER PRIMARY KEY (ID_PLANNER);
ALTER TABLE TBL_ROOM ADD CONSTRAINT PK_TBL_ROOM PRIMARY KEY (ID_ROOM);
ALTER TABLE TBL_TIME_SLICE ADD CONSTRAINT PK_TBL_TIME_SLICE PRIMARY KEY (ID_TIME_SLICE);
-- Foreign Keys
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_1 FOREIGN KEY (FK_DOCTOR) REFERENCES TBL_DOCTOR (ID_DOCTOR);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_2 FOREIGN KEY (FK_CLINIC) REFERENCES TBL_CLINIC (ID_CLINIC);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_3 FOREIGN KEY (FK_ROOM) REFERENCES TBL_ROOM (ID_ROOM);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_4 FOREIGN KEY (FK_PATIENTS_GENERAL) REFERENCES TBL_PATIENTS_GENERAL (ID_PATIENTS_GENERAL);
ALTER TABLE TBL_PLANNER ADD CONSTRAINT FK_TBL_PLANNER_5 FOREIGN KEY (FK_PATIENTS_TEMP) REFERENCES TBL_PATIENTS_TEMP (ID_PATIENTS_TEMP);
ALTER TABLE TBL_TIME_SLICE ADD CONSTRAINT FK_TBL_TIME_SLICE_1 FOREIGN KEY (FK_PLANNER) REFERENCES TBL_PLANNER (ID_PLANNER);
COMMIT WORK;
|
Всего записей: 328 | Зарегистр. 25-11-2007 | Отправлено: 17:39 17-12-2012 | Исправлено: eddoc, 17:46 17-12-2012 |
|
