Создание базы данных для маркетплейса с помощью SQL
SQL (Structured Query Language) - это язык программирования, предназначенный для управления данными в реляционных базах данных. Он позволяет извлекать, вставлять, обновлять и удалять данные из баз данных, а также создавать и изменять структуры баз данных, такие как таблицы, индексы и представления.
Одна из простых причин изучения SQL заключается в том, что это широко используемый язык для работы с данными. Независимо от того, являетесь ли вы аналитиком данных, разработчиком программного обеспечения, специалистом по бизнес-аналитике или любым другим специалистом, работающим с данными, навыки SQL помогут вам эффективно управлять большими объёмами данных и анализировать их.
SQL важен, поскольку он является основой многих бизнес-приложений и процессов принятия решений, основанных на данных. Обладая знаниями SQL, вы можете извлекать ценную информацию из данных и принимать обоснованные решения на основе этих данных. SQL также необходим для управления и поддержки баз данных, критически важных компонентов многих программных систем и приложений. В целом, SQL - важный навык для любого, кто хочет работать с данными или создавать приложения, управляемые данными.
Чтобы узнать больше о SQL и реляционных базах данных, мы можем создать проект для практики. В этом проекте перед вами стоит задача создать реляционную базу данных для веб-сайта, предлагающего продажу подержанных автомобилей. Общее описание этого проекта заключается в том, что любой желающий может предлагать свои товары (подержанные автомобили) в виде рекламы, а потенциальные покупатели могут осуществлять поиск по нескольким категориям. клиент определяет несколько параметров, которым мы должны соответствовать при создании базы данных, которая включает:
- пользователь может продать более одного автомобиля, но сначала должен предоставить личные данные (имя пользователя, контактные данные, место жительства).
- Пользователи могут размещать объявления о продаже автомобилей при условии, что в объявлении должна содержаться информация об автомобиле (марка, модель, тип, коробка передач, год выпуска и т.д.).
- Каждый пользователь может выполнить поиск предлагаемого автомобиля на основе местоположения пользователя продавца, марки автомобиля и типа кузова.
- Существует функция ставок для пользователей, которые разрешают “делать ставки” на автомобили, которые они продают.
- транзакции совершаются вне Сети, поэтому они не включены в объём работ
На основе параметров, предоставленных пользователем, мы создадим базу данных, которая сможет вместить необходимые таблицы, столбцы и данные. Для базы данных необходимо несколько таблиц, которые включают таблицы для размещения пользователей, таблицы для размещения транзакций, таблицы для хранения информации о проданных автомобилях, таблицы, связанные с информацией о местоположении, и таблицы для функций ставок.
Ниже приведен ERD, который мы ранее представляли себе на основе параметров, предоставленных клиентом. Он содержит 5 реляционных таблиц. Первая строка - это название таблицы. Остальные строки представляют столбцы в таблице.
Как мы можем видеть, в ERD есть 5 таблиц.
пользователи: в этой таблице содержится информация, относящаяся к пользователям, зарегистрированным в базе данных, такая как user_id, местоположение, контакт и т.д. Поскольку user_id уникален, user_id будет использоваться в качестве первичного ключа.
коммерция: содержит информацию об автомобилях, проданных пользователем, такую как описания, предложения, цены и т.д. Поскольку для представления каждой продажи требуется уникальный ключ, в качестве первичного ключа используется commerce_id.
ставки: содержит информацию о функции ставок.
car_details : Эта таблица содержит информацию и данные об автомобилях, проданных пользователем, поскольку каждый автомобиль уникален, car_id используется в качестве первичного ключа.
Город: содержит информацию, относящуюся к местоположению.
Чтобы создать таблицу из базы данных, нам нужно выполнить запрос. Мы можем использовать этот запрос для создания базы данных, подобной приведённой выше ERD:
Мы создали таблицы, но оставили их пустыми. Теперь мы заполним эти таблицы соответствующими данными, а затем запустим запросы для извлечения данных из них. Чтобы заполнить базу данных, нам нужно вставить наши данные или датасет в таблицы.
Существует несколько способов ввода данных в базу данных, включая импорт файлов (таких как файлы CSV и XLSX) или использование команды INSERT в запросе.
Для этой базы данных мы будем использовать данные, сгенерированные на Python.
Мы используем коды для команды Python для создания CSV-файлов с желаемыми данными. Возможно, сделать это с помощью Excel было бы намного, намного проще. После того, как мы выполним код, автоматически будет создан CSV-файл с именем "город", содержащий необходимые нам данные внутри.
Для этой базы данных мы будем использовать оба метода. Первый метод - это метод импорта. во-первых, мы должны убедиться, что тип данных (string, integer, boolean и т.д.) соответствует параметрам, которые мы создали в запросе на создание таблицы.
Во-вторых, щёлкните правой кнопкой мыши по таблице, из которой мы хотим ввести данные, и выберите импорт.
В-третьих, выберите файл для загрузки и нажмите кнопку ОК.
После успешной загрузки должно появиться уведомление, подобное этому.
Помимо использования функции импорта файла, мы также можем использовать запрос INSERT для вставки данных в базу данных. Для таблицы commerce мы введём данные, используя метод INSERT. Ниже представлен синтаксис:
Поскольку нам нужно настроить имена таблиц и столбцов, синтаксис для таблицы commerce будет следующим:
После того, как мы наполним базу данных созданным нами фиктивным набором данных, мы можем добавить связи между таблицами, чтобы она выглядела как ERD выше, где есть линии, соединяющие таблицы. Чтобы создать эти отношения, мы должны вставить внешний ключ между таблицами. Чтобы сделать это, мы добавим ограничения внешнего ключа к этим таблицам и свяжем их с исходной таблицей следующим образом:
Наконец-то мы создали рабочую базу данных! Но мы ещё не закончили. Нам нужно выполнить несколько запросов, чтобы убедиться, что созданная нами база данных и введённый нами фиктивный файл достаточны для нашего клиента. Давайте реализуем 10 запросов.
1. Поиск автомобилей 2015 года выпуска и старше:
Результат:
2. Добавление одной новой записи о заявке на товар:
До:
После:
3. Просмотр всех проданных автомобилей в 1 аккаунте, начиная с самого последнего (используя в качестве примера идентификатор пользователя ‘kolektormobil’)
Результат:
4. Поиск самых дешевых подержанных автомобилей по ключевым словам (ключевое слово: Yaris)
Результат:
5. Поиск ближайшего подержанного автомобиля на основе идентификатора города, кратчайшее расстояние рассчитывается на основе широты и долготы (city_id 3173)
Результат:
6. Аналитический запрос: Рейтинг популярности модели автомобиля на основе суммы ставки:
7. Аналитический запрос: Сравнение цен на автомобили на основе средней цены по городу:
Результат:
8. Аналитический запрос: требуется сравнить дату, когда пользователь сделал ставку, со следующей ставкой, а также цену предложения, указанную в предложении для модели автомобиля (пример модели "Camry").
Результат:
9. Аналитический запрос: Сравнение процентной разницы в средней цене автомобиля по моделям и средней цене предложения, предложенной клиентами за последние 6 месяцев:
Результат:
10. Аналитический запрос: Создайте функцию отображения средней цены предложения марки и модели автомобиля за последние 6 месяцев:
Результат:
Наконец, мы смогли создавать базы данных, заполнять базы данных, выстраивать связи с базами данных и выполнять базовые запросы к более сложным запросам.
Спасибо вам за чтение!
Доступ ко всем кодам и наборам данных можно получить по этой ссылке: https://github.com/frhnkl/Database-for-Car-Marketplace
Статья была взята из этого источника: