Слайд 2
![План лекции Соглашение об именовании объектов БД Необходимость денормализации. Выбор структур хранения для таблиц. Выбор индексов.](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-1.jpg)
План лекции
Соглашение об именовании объектов БД
Необходимость денормализации.
Выбор структур хранения для таблиц.
Выбор
индексов.
Слайд 3
![Даталогическая модель «Продукты питания» Окончательный вариант реляционной модели (Схемы БД)](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-2.jpg)
Даталогическая модель «Продукты питания»
Окончательный вариант реляционной модели (Схемы БД)
Слайд 4
![Стандарт именования объектов БД При именовании объектов БД необходимо использовать](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-3.jpg)
Стандарт именования объектов БД
При именовании объектов БД необходимо использовать какой-либо стандарт.
Имена
объектов БД должны быть информативными.
Возможный вариант именования объектов БД: Префикс_ТипОбъекта_ИмяОбъектаБД
Пример: Продажа продуктов питания – Food Product Sale (FPS): FPS_TS_CITY
Слайд 5
![Сокращения для объектов БД](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-4.jpg)
Сокращения для объектов БД
Слайд 6
![Таблица «Города» Индекс-таблица create table FPS_TS_CITY ( CITY_ID NUMBER(6) not](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-5.jpg)
Таблица «Города»
Индекс-таблица
create table FPS_TS_CITY
(
CITY_ID NUMBER(6) not null,
CITY_NAME VARCHAR2(30) not
null,
constraint FPS_IU_CITY_PK
primary key (CITY_ID)
)
organization index;
Слайд 7
![Таблица «Поставщики» Обычная таблица (куча) create table FPS_TS_PROVIDER ( PROVIDER_ID](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-6.jpg)
Таблица «Поставщики»
Обычная таблица (куча)
create table FPS_TS_PROVIDER
(
PROVIDER_ID NUMBER(6) not null,
PROVIDER_NAME
VARCHAR2(30) not null,
CITY_ID NUMBER(6) not null,
ADDRESS VARCHAR2(100) not null,
BOSS_NAME VARCHAR2(50),
PHONE VARCHAR2(10) not null,
FAX VARCHAR2(10)
);
Слайд 8
![Таблица «Поставщики» - Ограничения Первичный ключ alter table FPS_TS_PROVIDER add](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-7.jpg)
Таблица «Поставщики» - Ограничения
Первичный ключ
alter table FPS_TS_PROVIDER
add constraint FPS_IU_PROVIDER_PK
primary key (PROVIDER_ID);
Внешний ключ
alter table FPS_TS_PROVIDER
add constraint FPS_IN_PROVIDER_FK1
foreign key (CITY_ID)
references FPS_TS_CITY (CITY_ID);
Слайд 9
![Таблица «Поставщики» - Индексы Индекс для внешнего ключа create index](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-8.jpg)
Таблица «Поставщики» - Индексы
Индекс для внешнего ключа
create index FPS_IN_PROVIDER_FK1
on FPS_TS_PROVIDER (CITY_ID);
Индекс для поиска по имени поставщика
create index FPS_IN_PROVIDER_1
on FPS_TS_PROVIDER (PROVIDER_NAME);
Слайд 10
![Таблица «Продукты» Обычная таблица (куча) create table FPS_TS_PRODUCT ( PRODUCT_ID](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-9.jpg)
Таблица «Продукты»
Обычная таблица (куча)
create table FPS_TS_PRODUCT
(
PRODUCT_ID NUMBER(6) not null,
PRODUCT_NAME
VARCHAR2(50) not null,
UNIT VARCHAR2(10) not null,
STORAGE_TIME NUMBER(3) not null,
CONDITION VARCHAR2(100)
);
Слайд 11
![Таблица «Продукты» - Ограничения Первичный ключ alter table FPS_TS_PRODUCT add](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-10.jpg)
Таблица «Продукты» - Ограничения
Первичный ключ
alter table FPS_TS_PRODUCT
add constraint FPS_IU_PRODUCT_P
primary key (PRODUCT_ID);
Ограничение CHECK (бизнес-правило)
alter table FPS_TS_PRODUCT
add constraint fps_ch_product_1
check (storage_time < 250);
Слайд 12
![Таблица «Продукты» - Индексы Уникальный индекс create unique index FPS_IU_PRODUCT_1](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-11.jpg)
Таблица «Продукты» - Индексы
Уникальный индекс
create unique index FPS_IU_PRODUCT_1
on FPS_TS_PRODUCT
(PRODUCT_NAME);
Составной индекс
create index FPS_IN_PRODUCT_1 on
FPS_TS_PRODUCT (UNIT, STORAGE_TIME);
Слайд 13
![Таблица «Продажи» Обычная таблица (куча) create table FPS_TT_SALE ( SALE_DATE](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-12.jpg)
Таблица «Продажи»
Обычная таблица (куча)
create table FPS_TT_SALE
(
SALE_DATE DATE not null,
PRODUCT_ID
NUMBER(6) not null,
QUANTITY NUMBER(3) not null,
PRICE NUMBER(9,2) not null
);
Слайд 14
![Таблица «Продажи» - Ограничения Первичный ключ (составной) alter table FPS_TT_SALE](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-13.jpg)
Таблица «Продажи» - Ограничения
Первичный ключ (составной)
alter table FPS_TT_SALE
add constraint
FPS_IU_SALE_PK
primary key (PRODUCT_ID, SALE_DATE);
Внешний ключ
alter table FPS_TT_SALE
add constraint FPS_IN_SALE_FK1
foreign key (PRODUCT_ID);
Слайд 15
![Таблица «Заказы» Обычная таблица (куча) create table FPS_TT_ORDER ( ORDER_DATE](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-14.jpg)
Таблица «Заказы»
Обычная таблица (куча)
create table FPS_TT_ORDER
(
ORDER_DATE DATE not null,
PROVIDER_ID
NUMBER(6) not null,
PRODUCT_ID NUMBER(6) not null,
QUANTITY NUMBER(6) not null
);
Слайд 16
![Таблица «Заказы» - Ограничения Первичный ключ (составной) alter table FPS_TT_ORDER](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-15.jpg)
Таблица «Заказы» - Ограничения
Первичный ключ (составной)
alter table FPS_TT_ORDER
add constraint
FPS_IU_ORDER_3
primary key (ORDER_DATE, PRODUCT_ID, PROVIDER_ID);
Внешний ключ 1
alter table FPS_TT_ORDER
add constraint FPS_IN_ORDER_1
foreign key (PROVIDER_ID);
Внешний ключ 2
alter table FPS_TT_ORDER
add constraint FPS_IN_ORDER_2
foreign key (PRODUCT_ID);
Слайд 17
![Таблица «Заказы» - Индексы Уникальный индекс будет автоматически создан для](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-16.jpg)
Таблица «Заказы» - Индексы
Уникальный индекс будет автоматически создан для первичного ключа.
Индекс
для внешнего ключа 1
create index FPS_IN_ORDER_FK1
on FPS_TT_ORDER (PROVIDER_ID);
Индекс для внешнего ключа 2
create index FPS_IN_ORDER_FK2
on FPS_TT_ORDER (PRODUCT_ID);
Слайд 18
![Таблица «Поставки» Обычная таблица (куча) create table FPS_TT_SUPPLY ( SUPPLY_DATE](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-17.jpg)
Таблица «Поставки»
Обычная таблица (куча)
create table FPS_TT_SUPPLY
(
SUPPLY_DATE DATE not null,
PROVIDER_ID
NUMBER(6) not null,
PRODUCT_ID NUMBER(6) not null,
QUANTITY NUMBER(6) not null,
PRICE NUMBER(9,2) not null,
CREATE_DATE DATE not null
);
Слайд 19
![Таблица «Поставки» - Ограничения Первичный ключ (составной) alter table FPS_TT_SUPPLY](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-18.jpg)
Таблица «Поставки» - Ограничения
Первичный ключ (составной)
alter table FPS_TT_SUPPLY
add constraint
FPS_IU_SUPPLY_PK
primary key (SUPPLY_DATE, PRODUCT_ID, PROVIDER_ID);
Внешний ключ 1
alter table FPS_TT_SUPPLY
add constraint FPS_IN_SUPPLY_1 foreign key (PRODUCT_ID);
Внешний ключ 2
alter table FPS_TT_SUPPLY
add constraint FPS_IN_SUPPLY_2
foreign key (PROVIDER_ID);
Слайд 20
![Таблица «Поставки» - Индексы Уникальный индекс будет автоматически создан для](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-19.jpg)
Таблица «Поставки» - Индексы
Уникальный индекс будет автоматически создан для первичного ключа.
Индекс
для внешнего ключа 1
create index FPS_IN_SUPPLY_FK1 on FPS_TT_SUPPLY (PRODUCT_ID);
Индекс для внешнего ключа 2
create index FPS_IN_SUPPLY_FK2 on FPS_TT_SUPPLY (PROVIDER_ID);
Слайд 21
![Последовательности Последовательность для таблицы «Города» create sequence FPS_SQ_CITY start with](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-20.jpg)
Последовательности
Последовательность для таблицы «Города»
create sequence FPS_SQ_CITY
start with 1 increment
by 1 nocache;
Последовательность для таблицы «Поставщики»
create sequence FPS_SQ_PROVIDER
start with 1 increment by 1 nocache;
Последовательность для таблицы «Продукты»
create sequence FPS_SQ_PRODUCT
start with 1 increment by 1 nocache;
Слайд 22
![Денормализация таблицы «Продукты» Проблема: Для определения возможности продажи любого продукта](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-21.jpg)
Денормализация таблицы «Продукты»
Проблема: Для определения возможности продажи любого продукта надо проводить
дополнительные вычисления:
Вычислять общее количество поставленных и проданных продуктов
Вычислять ОСТАТОК = ПОСТАВЛЕНО - ПРОДАНО
Слайд 23
![Денормализация таблицы «Продукты» Для того, чтобы упростить логику работы с](/_ipx/f_webp&q_80&fit_contain&s_1440x1080/imagesDir/jpg/46653/slide-22.jpg)
Денормализация таблицы «Продукты»
Для того, чтобы упростить логику работы с этой БД
надо в таблицу «Продукты» добавить поле «Количество»:
alter table FPS_TS_PRODUCT add
QUANTITY number(6) default 0 not null;
Это пример восходящей денормализации.
Для поддержания согласованности данных надо использовать серверную логику:
- увеличивать значение при поставке продукта;
- уменьшать значение при продаже продукта;