Введение
Это вторая часть серии «Проект инженерии данных — розничный магазин». После получения данных о виски для розничного магазина в части 1. Следующим шагом является продолжение процесса ETL и загрузка данных в центральную базу данных организации.
Шаги проекта
- Генерация случайных данных.
- В этой части я буду использовать Python для генерации случайных данных о различных частях организации.
2. Разработайте центральную СУБД и примените нормализацию.
3. Загрузите данные в центральную СУБД.
Первая часть серии посвящена извлечению данных о продукте. На данный момент у меня есть датафрейм со всеми данными о виски, которые я смог собрать с https://www.thewhiskyexchange.com/.
Первую часть серии можно посмотреть здесь: https://medium.com/@bdadon50/data-engineering-project-retail-store-part-1-web-scraping-a99ac5d6d44c.
Напоминаю, здесь я остановился в прошлый раз.
Шаг № 1 — Генерация случайных данных
Сначала я буду генерировать случайные данные о сотрудниках, клиентах, платежах и продуктах.
Импорт и функции
1. Генерация данных о продукте
Во-первых, я загружу CSV-файл, содержащий продукты, которые я извлек в части 1, в фрейм данных.
Далее я создам столбец уникальных идентификаторов продуктов. Он будет действовать как первичный ключ для таблицы продуктов.
Давайте посмотрим образец кадра данных.
Я быстро сгенерирую полностью случайные данные о сотрудниках, клиентах и платежах с помощью Python на следующих шагах. Если вас не интересует код, не стесняйтесь пропустить эту часть и сразу перейти к шагу № 2 — проектирование центральной СУБД и нормализация данных
2. Генерация данных о сотрудниках
3. Генерация данных о клиентах
3. Генерация платежных данных
Шаг № 2 — Проектирование центральной СУБД и нормализация данных
После создания случайных данных я могу приступить к проектированию центральной базы данных. База данных будет выступать в качестве основного источника данных организации, и поэтому мы должны стремиться к максимальной согласованности и целостности данных в этой базе данных.
Для этого я нормализую каждую таблицу, чтобы свести к минимуму избыточность в базе данных и максимизировать целостность базы данных.
Я нормализую каждую таблицу, чтобы она соответствовала требованиям первой, второй, а затем и третьей нормальной формы (3НФ).
Кроме того, я нормализую таблицы сначала на Python в Jupyter Notebook, и только когда все будет сделано, я загружу всю схему в базу данных.
Текущая база данных выглядит так:
Нормализация таблиц
Теперь, когда я сгенерировал все денормализованные таблицы, я могу их нормализовать, чтобы они соответствовали требованиям третьей нормализованной формы (3NF).
Давайте быстро пробежимся по требованиям для каждой нормализованной формы:
Первая нормализованная форма (1NF)
- Каждая строка должна быть уникальной.
- Каждая ячейка может содержать только одно значение.
Вторая нормализованная форма (2NF)
- Таблица уже должна соответствовать требованиям 1NF.
- Таблица должна быть отделена от повторяющихся значений, которые применяются к нескольким строкам.
Третья нормализованная форма (3NF)
- Таблица уже должна соответствовать требованиям для 2NF.
- Каждая сущность должна зависеть только от первичного ключа
1. Нормализация таблицы клиентов
Начну с нормализации таблицы клиентов. Начнем с проверки, соответствует ли эта таблица требованиям 1NF.
1NF
Сейчас каждая строка уникальна, и каждая ячейка содержит ровно одно значение, так что эта таблица уже соответствует требованиям первой нормальной формы (1НФ).
2NF
Требование для второй нормальной формы состоит в том, чтобы разделить группы значений, которые применяются к нескольким строкам.
Например, страна, код_страны и кредит_провайдеры не имеют большого количества элементов и применяются к нескольким строкам. Это пример избыточности в таблице.
Я создам три отдельные таблицы из этой таблицы, чтобы соответствовать требованиям 2NF.
Первая таблица — страны
После создания новой таблицы мне нужно связать ее с таблицей клиентов, для этого давайте создадим внешний ключ с именем country_ids.
Теперь я могу удалить столбцы «страна» и «код_страны» из клиентов без потери данных (поскольку теперь я храню данные в отдельной таблице).
Сделанный. Давайте продолжим разделение таблицы клиентов, на этот раз создадим таблицу с именем customer_cc, в которой будет храниться информация о поставщиках кредитных карт клиентов.
Вторая таблица — Customer_cc
Как и раньше, мне нужно подключить его к клиентам. Давайте создадим внешний ключ с именем credit_provider_id.
Теперь я могу удалить столбец credit_provider из списка клиентов.
И теперь таблица клиентов находится во 2NF. Посмотрим, как теперь выглядит база данных:
3NF
Теперь давайте проверим, соответствует ли таблица клиента третьей нормальной форме.
Требование для 3NF состоит в том, что каждый столбец зависит только от первичного ключа. В этом случае первичный ключ — это customer_id. Теперь, чтобы понять, зависит ли столбец только от первичного ключа, мы должны задать себе два вопроса:
Q1. «Учитывая первичный ключ, могу ли я определить значение этого столбца?»
Если ответ положительный, то переходим ко второму вопросу.
Q2. «Учитывая любой другой столбец, могу ли я определить значение этого столбца?»
Если ответ отрицательный, мы можем с уверенностью сказать, что этот столбец зависит только от первичного ключа.
Например, давайте проверим, зависит ли столбец first_name только от первичного ключа. Давай спросим:
Q1. Учитывая идентификатор клиента, могу ли я узнать имя этого клиента?
Ответ: Да. Знание идентификатора клиента позволит точно определить имя клиента.
Вопрос 2. Учитывая любой другой столбец, могу ли я узнать имена клиентов?
Ответ: Нет. Глядя на фамилию, страну или любой другой столбец, я не могу точно определить имя клиента. Несколько человек могут иметь одну и ту же фамилию и одну и ту же страну.
Итак, в заключение, столбец first_name действительно принадлежит этой таблице, при условии, что я намерен нормализовать таблицу клиентов до 3NF.
2. Нормализация таблицы сотрудников
Давайте быстро проделаем тот же процесс для таблицы сотрудника. напомню, таблица выглядит так:
1NF
Как и прежде, каждая строка уникальна, и каждая ячейка содержит ровно одно значение, поэтому эта таблица уже находится в первой нормальной форме (1НФ).
2NF
Чтобы проверить требования 2NF, давайте спросим, есть ли в этой таблице значения, применимые к нескольким строкам?
Да. Отдел является повторяющимся значением.
В организации всего четыре отдела, этот столбец вызывает избыточность данных. Разделим эту таблицу на две таблицы. таблица сотрудников и таблица отдела.
таблица отделов
Новая таблица выглядит так:
После создания новой таблицы мне нужно подключить ее к сотрудникам. Как и раньше, я создам внешний ключ, чтобы присоединиться к ним.
Теперь я могу удалить колонку отдел из сотрудников
Это для 2NF. Давайте проверим требования для 3NF:
3NF
Теперь таблица сотрудников выглядит так:
Как видите, каждый столбец зависит только от первичного ключа, которым является employee_id, поэтому эта таблица соответствует 3NF.
Я нормализовал остальную часть базы данных, используя те же принципы. Вот как выглядит новая нормализованная база данных:
Напоминание: предыдущие шаги выполнялись исключительно на Python. Теперь, когда таблицы нормализованы, я подключаюсь к базе данных и загружаю данные в соответствии с моим дизайном. Инструмент, который я использовал в качестве своей базы данных, — это MySQL.
Шаг № 3. Загрузка данных в центральную СУБД.
1. Подключение Python к MySQL
2. Создание новой схемы
3. Создание пустых таблиц
Во-первых, я начну с таблиц без внешнего ключа: страны, клиент_cc, продукты и отделы.
Страны
Customer_cc
Продукты
Отделы
Теперь я могу создать остальные таблицы, клиентов, сотрудников и платежи.
Клиенты
Сотрудники
Платежи
3. Заполнение таблиц
Чтобы вставить кадры данных, которые я создал в Python, в MySQL, мне нужно их преобразовать. Я преобразую каждую таблицу в кортеж и буду вставлять каждую строку одну за другой, используя цикл for. Я зафиксирую всю транзакцию, когда цикл закончится.
Страны
Прежде чем заполнить остальные таблицы, я хотел бы показать, как это выглядит в MySQL.
Давайте выполним простой запрос на выборку, чтобы показать, что эти данные теперь перемещены из моей записной книжки Python Jupyter в MySQL.
После заполнения остальных таблиц я буду использовать только MySQL при работе с данными. В этом весь смысл этой части проекта — хранить данные в инструменте, предназначенном для запроса данных, таком как MySQL.
Продолжим заполнение таблиц. Я сделаю то же самое для остальных таблиц.
Customer_cc
Продукты
Отделы
Клиенты
Сотрудники
Платежи
Вот и все. Теперь все данные хранятся в MySQL в нормализованной схеме, которую можно использовать для запроса и извлечения данных. Давайте создадим диаграмму ERD, используя MySQL, чтобы увидеть готовую схему:
Вот и все, что касается 2 части серии.
В части 3 я разработаю хранилище данных, которое организация будет использовать в качестве единого источника данных, используемого для аналитических целей и принятия решений и отчетности, связанных с BI.
