Физическая модель базы данных (базовые таблицы)

Задача
Создание таблиц и объектов в базе данных, в которых будет храниться информация о сущностях предметной области.
Вход: логическая модель базы данных
Выход: скрипт для создания таблиц и индексов на языке определения данных DDL (Data Definition Language).

Типы таблиц
Постоянные базовые таблицы (Base Table) - таблицы, содержимое которых хранится в базе данных и которые остаются в базе данных постоянно, если не удаляются явным образом.
Глобальные временные таблицы - таблицы, которые применяются в качестве рабочей области хранения данных и которые уничтожаются в конце сеанса работы с базой данных.
Локальные временные таблицы - таблицы, которые аналогичны глобальным временным таблицам, но доступны только тому программному модулю, в котором созданы.

ОПРЕДЕЛЕНИЕ БАЗОВЫХ ТАБЛИЦ

Создание базовой таблицы
CREATE TABLE имя таблицы (
Имя_столбца тип_данных [,
Имя_столбца тип_данных] … )

Создание таблицы заказов
CREATE TABLE order (
id_order INT(5),
o_date DATETIME,
o_cost FLOAT,
o_status VARCHAR(30)
)

Создать таблицу products
id_product INT(5),
p_name VARCHAR(20),
id_category INT(5),
p_price FLOAT

Определение таблиц на основе существующей
CREATE TABLE product_category1
SELECT id_product, p_name, id_category, p_price
FROM products
WHERE id_category=1

Определение таблиц на основе существующей
CREATE TABLE ptoduct_category2(id int(5))
SELECT id_product as ‘id’
FROM products
WHERE id_category=2

Клонирование таблиц
CREATE TABLE new_products
LIKE products

МОДИФИКАЦИЯ ТАБЛИЦ

Переименование таблицы
Изменим таблицу заказов: переименуем ее в orders
ALTER TABLE catalog.order RENAME catalog.orders
RENAME TABLE catalog.order TO catalog.orders

Добавление столбца
ALTER TABLE имя_таблицы
ADD определение столбца [,
ADD определение столбца]…)

Добавьте поле id_buyer в таблицу заказов
ALTER TABLE orders
ADD id_buyer INT(11)

Добавьте поле id_seller в таблицу заказов
ALTER TABLE orders
ADD id_seller INT(11)

Удаление столбца
ALTER TABLE имя_таблицы DROP имя_столбца

Удалить столбец o_date
ALTER TABLE orders
DROP o_date

Изменение определения столбца
ALTER TABLE имя_таблицы
MODIFY имя_столбца тип_данных

У таблицы new_products установите
для p_name тип данных для varchar(30),
для p_price – тип int(11)
ALTER TABLE new_products
MODIFY p_name varchar(30),
MODIFY p_price int(11)

УДАЛЕНИЕ ТАБЛИЦ

Удаление таблицы заказов
DROP TABLE orders

ОГРАНИЧЕНИЯ ЦЕЛОСТНОСТИ

Типы ограничений целостности
PRIMARY KEY
[NOT] NULL
DEFAULT
UNIQUE- уникальное поле
FOREIGN KEY
CHECK ограничение значений

Свойства первичного ключа
отношение (таблица) может иметь только один первичный ключ;
первичный ключ должен быть уникальным;
первичный ключ должен быть минимальным, т.е. включать минимальное число атрибутов, необходимых для однозначной идентификации кортежа;
первичный ключ не может содержать нулевых значений;
значение первичного ключа не должно меняться при смене состояний базы данных.

Ограничения первичных ключей
имя_столбца тип_данных PRIMARY KEY
имя_столбца тип_данных AUTO_INCREMENT
PRIMARY KEY
ALTER TABLE имя_таблицы
ADD PRIMARY KEY (список_столбцов)

Ограничения первичных ключей
CREATE TABLE orders (
id_order INT(5) AUTO_INCREMENT PRIMARY KEY, o_date DATETIME,
o_cost FLOAT,
o_status VARCHAR(30)
)

Ограничение на отсутствие значений
имя_столбца тип_данных [NOT] NULL
CREATE TABLE order (
id_order INT(5) PRIMARY KEY AUTO_INCREMENT,
o_date DATETIME NOT NULL,
o_cost FLOAT NULL,
o_status VARCHAR(30) NULL
)

Определение значений по умолчанию
имя_столбца тип_данных
DEFAULT значение_по_умолчанию
CREATE TABLE order (
id_order INT(5),
o_date DATATIME NOT NULL,
o_cost FLOAT DEFAULT 0,
o_status VARCHAR(30) DEFAULT ‘в обработке’
)

Определение значений по умолчанию
ALTER TABLE имя_таблицы
ALTER имя_столбца SET DEFAULT значение
ALTER TABLE имя_таблицы
ALTER имя_столбца DROP DEFAULT

Добавить (удалить) у столбца p_price (таблица products) значение по умолчанию
ALTER TABLE products
ALTER p_price SET DEFAULT 0
ALTER TABLE products
ALTER p_price DROP DEFAULT

Ограничение уникальности
имя_столбца тип_данных UNIQUE
ALTER TABLE имя_таблицы
ADD UNIQUE(список_столбцов)

Свойства внешних ключей
Внешний ключ должен содержать такое же число колонок, такого же типа и в том же порядке следования, что и соответствующий первичный ключ.
Имена колонок внешнего ключа и их значения по умолчанию могут отличаться от используемых в соответствующем первичном ключе (в том числе иметь NULL-значения).
Таблица может иметь любое число внешних ключей.
Упорядочение значений колонок внешнего ключа в его индексе может отличаться от соответствующего первичного ключа.
Внешний ключ не может ссылаться на виртуальную таблицу.

Ограничение ссылочной целостности
FOREIGN KEY (имя_столбца(ов)_дочерней_таблицы) REFERENCES имя_родительской_таблицы (имя_столбца(ов))
ON DELETE RESTRICT
ON UPDATE CASCADE

Поддержка целостности
RESTRICT – запрещает удалять строки, если на эту строку имеются ссылки из дочерней таблицы
CASCADE – удаление строки родительской таблицы приводит к удалению всех связанных с ней строк в дочерней таблицы
SET NULL – удаление строки родительской таблицы приводит к установке в значение NULL всех внешних ключей дочерней таблицы, которые на нее ссылаются
SET DEFAULT удаление строки родительской таблицы приводит к установке в значение по умолчанию всех внешних ключей дочерней таблицы, которые на нее ссылаются

Ограничение на значение
ALTER TABLE users
ADD u_phone CHAR(16)
CHECK(u_phone LIKE '_(___)___-__-__')

Добавление ограничений
ALTER TABLE имя_таблицы
ADD ограничение_целостности