ЗАДАНИЕ 1.3. Приемы моделирования Справочников / create_db

/****************Скрипты создания таблиц*********************/
/*********************Параметры-перечисления*************************/
CREATE TABLE ENUM(
ID_ENUM SERIAL PRIMARY KEY,
NAME VARCHAR(50) UNIQUE,
SHORT_NAME VARCHAR(10)
);

CREATE TABLE ENUM_VALUE(
ID_VALUE SERIAL PRIMARY KEY,
ID_ENUM INTEGER,
VALUE INTEGER NOT NULL,
NAME VARCHAR(50) NOT NULL,
SHORT_NAME VARCHAR(10),
FOREIGN KEY (ID_ENUM) REFERENCES ENUM(ID_ENUM) ON DELETE CASCADE
);


/*********************Изделия и классы*************************/
CREATE TABLE EI
(
SHORT_NAME varchar(10),
NAME varchar(50) NOT NULL,
CODE varchar(3) primary key
);

create table PRODUCT_CLASS
(
SHORT_NAME VARCHAR(10),
ID_CLASS SERIAL PRIMARY KEY,
NAME VARCHAR(50) unique,
MAIN_CLASS INTEGER,
BASE_EI VARCHAR(3),
FOREIGN KEY (BASE_EI) REFERENCES EI (CODE),
FOREIGN KEY (MAIN_CLASS) REFERENCES PRODUCT_CLASS (ID_CLASS)
);

CREATE TABLE PRODUCT
(
ID_PROD SERIAL PRIMARY KEY ,
SHORT_NAME VARCHAR(50),
NAME VARCHAR(250) NOT NULL ,
ID_CLASS INTEGER,
FOREIGN KEY (ID_CLASS) REFERENCES PRODUCT_CLASS (ID_CLASS)
);


/*********************Параметры******************************/
CREATE TABLE PARAMETER
(
ID_PAR SERIAL PRIMARY KEY ,
NAME VARCHAR(100) UNIQUE ,
SHORT_NAME VARCHAR(10),
ENUM_TYPE BOOL NOT NULL,
EI_CODE VARCHAR(3) NULL,
ENUM_ID INTEGER NULL,
FOREIGN KEY (EI_CODE) REFERENCES EI (CODE),
FOREIGN KEY (ENUM_ID) REFERENCES ENUM (ID_ENUM)
);

CREATE TABLE CLASS_PARAMETERS
(
ID_CLASS INTEGER,
ID_PARAMETER INTEGER,
MIN_VALUE REAL NULL,
MAX_VALUE REAL NULL,
PRIMARY KEY (ID_CLASS, ID_PARAMETER),
FOREIGN KEY (ID_CLASS) REFERENCES PRODUCT_CLASS (ID_CLASS),
FOREIGN KEY (ID_PARAMETER) REFERENCES PARAMETER (ID_PAR)
);

CREATE TABLE PRODUCT_PARAM
(
PRODUCT_ID INTEGER,
PARAM_ID INTEGER,
VALUE REAL,
ENUM_VALUE VARCHAR(30),
PRIMARY KEY (PRODUCT_ID, PARAM_ID),
FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (ID_PROD),
FOREIGN KEY (PARAM_ID) REFERENCES PARAMETER (ID_PAR)

);