DataBase_for_PC_Service_Center

Сервисные центры по ремонту компьютеров и игровых консолей

Рус | Eng

Описание проекта:

Проект представляет собой базу данных для сети сервисных центров, специализирующихся на ремонте компьютеров и игровых консолей. Основная цель системы - автоматизация бизнес-процессов, включая управление заказами, бронированием, клиентами, сотрудниками и запчастями

Структура базы данных:

1. ServiceCenters_and_StorageCenters - информация о сервисных центрах и складах
2. Employees - данные о сотрудниках (техники, менеджеры, администраторы)
3. Clients - информация о клиентах и их статусе (Ordinary, Regular, Premium)
4. Services - перечень предоставляемых услуг
5. Parts - каталог запчастей с указанием количества и мест хранения
6. Orders - заказы на ремонт
7. Receipts - чеки по завершенным заказам
8. Bookings - записи на прием
9. OrderContent - состав заказов (услуги и запчасти)
10. PartsPurchases - закупки запчастей

Тестовые данные для проверки БД

В проекте также представле набор тестовых данных для всех таблиц.

Роли и привилегии

• Администраторы (administrator)

  • Полный доступ ко всем таблицам, функциям и последовательностям
  • Доступ ограничен по сервисным центрам с помощью RLS (Row Level Security)

• Аналитики (analyst)

  • Только чтение всех данных
  • Доступ к аналитическим функциям
  • Нет прав на модификацию данных

• Техники (technician)

  • Чтение и обновление данных по своим сервисным центрам
  • Доступ к заказам, запчастям и клиентам своего центра
  • Могут создавать закупки запчастей

• Менеджеры (manager)

  • Полный доступ к клиентам, заказам, бронированиям своего центра
  • Могут создавать и обновлять заказы, бронирования, чеки
  • Ограниченный доступ к данным сотрудников

функции и триггеры

1. Функция update_client_status()
   Обновляет статус клиента (Premium, Regular, Ordinary) на основе суммы его чеков.

2. Триггер trg_update_client_status
   Автоматически обновляет статус клиента при добавлении или изменении чека.

3. Функция burn_client_bonuses()
   Обнуляет бонусные баллы клиентов, если с момента последней покупки прошел год.

4. Функция transfer_client_to_another_technician()
   Переводит клиента к другому мастеру, обновляя его бронирования.

5. Функция calculate_order_cost()
   Рассчитывает общую стоимость заказа, включая услуги и запчасти.

6. Функция create_receipt_for_order()
   Создает чек для завершенного заказа, учитывая скидки и бонусные баллы.

7. Функция check_part_availability()
   Проверяет наличие запчасти на складе перед добавлением в заказ.

8. Триггер trg_check_part_availability
   Блокирует добавление запчасти в заказ, если её нет в наличии.

9. Триггер trg_decrease_part_count
   Уменьшает количество запчастей на складе после их добавления в заказ.

10. Функция check_booking_availability()
    Проверяет доступность времени бронирования у мастера.

11. Функция create_booking_with_check()
    Создает бронирование, предварительно проверяя доступность времени.

12. Функция suggest_booking_alternatives()
    Предлагает альтернативные варианты бронирования, если выбранное время занято.

13. Функция get_bookings_statistics_by_date()
    Возвращает статистику бронирований по датам, включая количество клиентов и популярные услуги.

14. Функция get_popular_services_stats()
    Показывает статистику по востребованным услугам, включая количество бронирований и завершенных заказов.

15. Функция get_completed_services_stats()
    Анализирует выполненные услуги, включая выручку и среднее время выполнения.

Индексы

В базе данных созданы индексы для оптимизации.

Интеграция с Grafana

Проект включает подключение к Grafana для визуализации ключевых метрик:

• Отфражения выручки по каждому серсному центру;

• Выручка по дням;

• Популярные услуги и методы оплаты;

• Распределение клиентов по статусам.

  

Модель ИИ для предсказаний

Реализована модель RandomForest для:

• Предсказания востребованных услуг

• Персонализированных предложений клиентам

• Анализа трендов спроса

Особенности системы

1. Гибкая система бронирования с проверкой доступности и предложением альтернатив

2. Автоматическое обновление статусов клиентов на основе их активности

3. Комплексные аналитические функции для принятия управленческих решений

4. Система прав доступа с Row Level Security

5. Оптимизированная структура с индексами для быстрого поиска

Как установить:

1. Установка базы данных:

   • Запустите скрипт Service_Centers_Schema.sql из папки DB_source/Schema для создания базы данных и таблиц.
   • Заполните таблицы тестовыми данными, используя скрипт Test_data.sql из папки DB_source/Importing_Test_data.

2. Настройка функций и триггеров:

   • Примените скрипты из папки DB_source/Functions_and_Triggers.

3. Настройка прав доступа и индексов:

   • Используйте скрипты из папки DB_source/Politics_and_Privileges для создания ролей и настройки политик.
   • Выполните скрипт из папки DB_source/Indexes для установки индексов на БД.

4. Аналитика:

   • Для выполнения аналитических запросов используйте файлы из папки Analytical_queries.
   • Для визуализации данных подключите Grafana к вашей базе данных (см. пример в Resources/Grafana.png).

Аналитические запросы созданные для БД:

1. Запрос 1 – Выявление запчастей с низким спросом и высокой стоимостью хранения для оптимизации складских запасов.
2. Запрос 2 – Анализ эффективности сотрудников (техников) по количеству выполненных заказов, времени выполнения и доходу.
3. Запрос 3 – Определение сотрудников, заслуживающих премии, на основе их продуктивности за последние 3 месяца.
4. Запрос 4 – Тестирование функции создания бронирования с проверкой накладок во времени.
5. Запрос 5 – Расчет ключевых метрик сервисных центров (заказы, бронирования, выручка, клиенты).
6. Запрос 6 – Анализ сезонных тенденций по месяцам и типам устройств для выявления спроса на услуги и запчасти.
7. Запрос 7 – Пример транзакции для безопасного создания чека с блокировкой строки заказа.
8. Запрос 8 – Анализ сезонности по месяцам и неделям, а также популярности услуг и запчастей.
9. Запрос 9 – Подготовка данных для кластеризации клиентов по их активности и предпочтениям.
10. Запрос 10 – Анализ спроса на запчасти за последний месяц для управления запасами.
11. Запрос 11 – Анализ загруженности временных слотов и пиковых часов для оптимизации расписания.
12. Запрос 12 – Выявление запчастей для обмена между сервисными центрами на основе их востребованности.
13. Запрос 13 – Анализ востребованности и прибыльности услуг и запчастей для стратегического планирования.
14. Запрос 14 – Расчет процента занятости временных слотов для записи за последний год.
15. Запрос 16 – Создание датасета для анализа клиентского поведения и истории заказов.
16. Запрос 18 – Генерация персонализированных рекомендаций и скидок для клиентов на основе их истории заказов.