Пример физического проектирования БД презентация

План лекции
Соглашение об именовании объектов БД
Необходимость денормализации.
Выбор структур хранения для таблиц.
Выбор индексов.

Даталогическая модель «Продукты питания»
Окончательный вариант реляционной модели (Схемы БД)

Стандарт именования объектов БД
При именовании объектов БД необходимо использовать какой-либо стандарт.
Имена объектов БД должны быть информативными.
Возможный вариант именования объектов БД: Префикс_ТипОбъекта_ИмяОбъектаБД
Пример: Продажа продуктов питания – Food Product Sale (FPS): FPS_TS_CITY

Сокращения для объектов БД

Таблица «Города»
Индекс-таблица
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;

Таблица «Поставщики»
Обычная таблица (куча)
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)
);

Таблица «Поставщики» - Ограничения
Первичный ключ
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);

Таблица «Поставщики» - Индексы
Индекс для внешнего ключа
create index FPS_IN_PROVIDER_FK1
on FPS_TS_PROVIDER (CITY_ID);
Индекс для поиска по имени поставщика
create index FPS_IN_PROVIDER_1
on FPS_TS_PROVIDER (PROVIDER_NAME);

Таблица «Продукты»
Обычная таблица (куча)
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)
);

Таблица «Продукты» - Ограничения
Первичный ключ
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);

Таблица «Продукты» - Индексы
Уникальный индекс
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);

Таблица «Продажи»
Обычная таблица (куча)
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
);

Таблица «Продажи» - Ограничения
Первичный ключ (составной)
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);

Таблица «Заказы»
Обычная таблица (куча)
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
);

Таблица «Заказы» - Ограничения
Первичный ключ (составной)
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);

Таблица «Заказы» - Индексы
Уникальный индекс будет автоматически создан для первичного ключа.
Индекс для внешнего ключа 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);

Таблица «Поставки»
Обычная таблица (куча)
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
);

Таблица «Поставки» - Ограничения
Первичный ключ (составной)
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);

Таблица «Поставки» - Индексы
Уникальный индекс будет автоматически создан для первичного ключа.
Индекс для внешнего ключа 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);

Последовательности
Последовательность для таблицы «Города»
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;

Денормализация таблицы «Продукты»
Проблема: Для определения возможности продажи любого продукта надо проводить дополнительные вычисления:
Вычислять общее количество поставленных и проданных продуктов
Вычислять ОСТАТОК = ПОСТАВЛЕНО - ПРОДАНО

Денормализация таблицы «Продукты»
Для того, чтобы упростить логику работы с этой БД надо в таблицу «Продукты» добавить поле «Количество»:
alter table FPS_TS_PRODUCT add
QUANTITY number(6) default 0 not null;
Это пример восходящей денормализации.
Для поддержания согласованности данных надо использовать серверную логику:
- увеличивать значение при поставке продукта;
- уменьшать значение при продаже продукта;