Итоговый проект: база данных интернет-магазина
Спроектировать, построить, защитить и получить отчёты — от начала до конца
Главная идея: реальная база данных приложения — это не один хитрый запрос, а система. В этом проекте вы смоделируете небольшой интернет-магазин как четыре связанные таблицы, зададите нормализованную схему, защищённую ограничениями, добавите индексы для нужных запросов, будете оформлять заказы безопасно внутри транзакции и отвечать на бизнес-вопросы с помощью CTE и CASE. Здесь сходятся все навыки из тем 1-13.
К концу вы сможете:
- превращать требования магазина в сущности и связи и читать ER-диаграмму,
- писать нормализованную (3НФ) схему, чьи операторы
CREATE TABLEобеспечивают целостность через ключи,NOT NULL,UNIQUEиCHECK, - выбирать индексы, соответствующие реальным запросам магазина,
- оформлять заказ атомарно, чтобы остатки и заказы никогда не расходились,
- строить отчёты (лучшие клиенты, выручка по товарам, продажи по месяцам) с помощью
WITHиCASE.
1. Предметная область: сущности и связи
Начните с языка бизнеса. У магазина есть клиенты (customers), которые оформляют заказы (orders); каждый заказ содержит один или несколько товаров (products), и для каждого товара в заказе мы записываем, сколько купили и по какой цене на тот момент. Эти существительные становятся таблицами:
| Сущность | Значение | Ключевая связь |
|---|---|---|
| customers | люди, которые могут покупать | один клиент -> много заказов |
| orders | отдельное событие покупки | один заказ -> много order_items |
| products | товары в продаже | один товар -> много order_items |
| order_items | одна товарная строка одного заказа | связывает заказ и товар |
Заказ может содержать много товаров, а товар может встречаться во многих заказах — это связь многие-ко-многим. Мы никогда не соединяем две таблицы напрямую через такую связь; вместо этого вводим соединительную (промежуточную) таблицу order_items, которая хранит одну строку на каждую пару «товар в заказе» плюс дополнительные факты (количество, цену), относящиеся к этой паре.
order_items.