Курсовая работа (т): Разработка базы данных для CMS интернет-магазина
2) name - поля для имени покупателя. Тип данных varchar(30)
3) email - поля для email адреса покупателя. Тип данных varchar(25)
4) email - поля для номера телефона покупателя. Тип данных varchar(10)
5) address - поля для адреса доставки покупателя. Тип данных varchar(100)
6) login - поля для логина покупателя. Тип данных varchar(20)
7) password - поля для пароля покупателя. Тип данных varchar(32)
Первичный ключ: Поле customers_id
Теперь мы напишем код и создадим таблицу customers в нашей базе данных
Код:TABLE IF NOT EXISTS `customers` (
`customer_id` mediumint(10), unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(25) NOT NULL,
`address` varchar(100) NOT NULL,
`login` varchar(20) NOT NULL,
`password` varchar(32) NOT NULL,KEY (`customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Результат:
Мы создали таблицу покупателей в нашей базе данных.
Таблица покупателей
2.3 Таблица способов доставки
По идеи у покупателей при оформление заказа будет возможность выбрать способ доставки, так как способов доставки у нас будет несколько, то рациональней нам создать для способов доставки отдельную таблицу. Назовем мы её «dostavka» и у неё будет следующая структура:
1. dostavka_id - тип поля tinyint(2) unsigned
2. name - это имя варианта доставки тип поля varchar(32)
Теперь мы добавим эту таблицу в нашу базу данных написав следующий SQL код:
CREATE TABLE IF NOT EXISTS `dostavka` (
`dostavka_id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,KEY (`dostavka_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Результат:
И так мы добавили таблицу «способов доставки» в нашу базу данных и теперь для наглядности мы добавим в ней несколько способов доставки, написал следующий SQL код:
INSERT INTO `dostavka` (`name`) VALUES
('Пересылка по почте'),
('Курьером'),
('Самовывоз'),
('Другое, по согласованию с менеджером');
Так как у нас на в таблице «dostavka» на поле «dostavka_id» стоит атрибут «AUTO_INCREMENT» в коде INSERT INTO нам не обязательно указывать ключ к каждой записи, достаточно только указать поле «name» для каждой записи, а ключ заполняется автоматически.
В результате мы получили, наполненную таблицу способов доставки.
Таблица «dostavka»
2.4 Таблица товаров
Таблицу категорий и таблицу покупателей мы уже создали теперь пора перейти к одной из главных таблиц, таблица товаров. Таблицу мы назовем её goods. В таблице goods мы создадим следующие поля:
1) goods_id - Счетчик товаров, тип данных int(10), добавим ключевое слово unsigned, чтобы исключить отрицательные числа. Добавим атрибут AUTO_INCREMENT для автоматического инкремента счетчика.
2) name - Поле для имени товара, тип varchar(30)
3) keywords - Поле для ключевых слов используемых в мета-тегах товара. Тип поля varchar(255)
4) description - Поле для описание товара в мета-тегах, тип поля аналогичен полю keywords
5) img - Это поле будет хранить в себе имя главной картинки товара (обложки).
6) goods_catid - Это поле будет хранить в себе ключ категории к которой принадлежит данный товар
7) anons - Данное поле будет хранить в себе сокращенное описание товара. Тип поля text.
8) content - Поле будет хранить в себе полное описание товара, Тип поля text
9) visible - Это поле будет отвечать за состояние товара, (видимы-невидимый) 0 - невидимый, 1 - видимый. По умолчанию - 1. Тип поля enum('0','1')
10) hits - Если 1 - значит товар относится к «хитам продаж» иначе - 0 По умолчанию - 0. Тип поля enum('0','1')
11) new - Если 1 - значит товар относится к «новинкам» иначе - 0 По умолчанию - 0. Тип поля enum('0','1')
12) sale - Если 1 - значит товар относится к «товарам со скидкой» иначе - 0 По умолчанию - 0. Тип поля enum('0','1')
13) data - дата добавление товара, Тип поля date
14) images - varchar(255)
15) price - Цена, тип поля float
Первичный ключ: Поле goods_id
После того как мы закончили проектировать таблицу, теперь мы можем написать SQL код для добавления таблицы в нашу базу данных. Создаем таблицу goods в базе данных
SQL код:
CREATE TABLE IF NOT EXISTS `goods` (
`goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`keywords` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`img` varchar(30) NOT NULL,
`goods_catid ` tinyint(3) unsigned NOT NULL,
`anons` text NOT NULL,
`content` text NOT NULL,
`visible` enum('0','1') NOT NULL DEFAULT '1',
`hits` enum('0','1') NOT NULL DEFAULT '0',
`new` enum('0','1') NOT NULL DEFAULT '0',
`sale` enum('0','1') NOT NULL DEFAULT '0',
`price` float NOT NULL DEFAULT '0',
`date` date NOT NULL,
`img_slide` varchar(100) NOT NULL,KEY (`goods_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Таблица товаров
2.5 Таблица заказов
По идеи в таблицу заказов мы должны собирать следующие данные: ключ заказа, ключ покупателя, ключ способов доставки, дату заказа, ключ статуса заказа, примечание к заказу. Мы назовем таблицу заказов - orders. Она будет иметь следующую структуру:
1) orders_id - это поле будет счетчиком и первичным ключем таблицы orders. Тип поля mediumint(50) unsigned
2) customer_id - ключ покупателя. Тип поля mediumint(10) unsigned
) dostavka_id - ключ способа доставки, тип поля tinyint(3) unsigned
) status - ключ статуса заказа, тип поля tinyint(2) unsigned
Первичный ключ таблицы orders - order_id.
Теперь мы приступим непосредственно к написанию SQL кода для добавления нашей таблицы в базу данных. Выполним следующий запрос к базе данных:TABLE IF NOT EXISTS `orders` (
`order_id` mediumint(50) unsigned NOT NULL AUTO_INCREMENT,
`customer_id` mediumint(10) unsigned NOT NULL,
`date` datetime NOT NULL,
`dostavka_id` tinyint(3) unsigned NOT NULL,
`status` tinyint(2) NOT NULL DEFAULT '0',KEY (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
В результате мы получили следующую структуру.
Таблица заказов
2.6 Таблица заказанных товаров
Мы создали таблицу orders, где мы сохраняем ключ заказа, ключ покупателя, ключ способов доставки, дату заказа, ключ статуса заказа. Но в таблицу orders мы не сохраняем товары, которые заказал покупатель, а это для функциональности интернет-магазина очень немаловажный момент. Мы не будем сохранять данные о заказанных товарах в таблице orders, так как это будет не очень рационально по отношению к структуре таблице и это весьма осложнить выборку данных. Для этого дела мы создадим отдельную таблицу, где мы будет собирать товары, которые заказал покупатель. Назовем эту таблицу «zakaz_tovar».
Создадим структуру таблицы:
1) zakaz_tovar_id, - счетчик, тип данных int(10) unsigned AUTO_INCREMENT
2) orders_id - ключ заказа, тип данных mediumint(50) unsigned
) goods_id - ключ товара, тип данных int(10) unsigned AUTO_INCREMENT
) quantity - количество заказанного товара, тип данных tinyint(3) unsigned
Поскольку в нашем интернет-магазине можно при заказе товара указать его количество мы в таблице zakaz_tovar добавили поле quantity. Теперь мы приступим к написанию SQL кода, для добавление нашей таблицы базу данных.
SQL код:TABLE IF NOT EXISTS `zakaz_tovar` (
`zakaz_tovar_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`orders_id` mediumint(50) unsigned NOT NULL,
`goods_id` int(10) unsigned NOT NULL,
`quantity` tinyint(3) unsigned NOT NULL,KEY (`zakaz_tovar_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
В результате мы получили:
Таблица заказанных товаров
2.7 Итоги создания базы данных
В итоге мы получили нормализованную базу данных по всем стандартам
нормализации, то есть нормальных форм. В итоге у нас получилось 7 таблиц это не
много и не мало для базового функционала стандартного интернет-магазина. Теперь
мы можем наполнить нашу базу данных временными данными и разрабатывать SQL выборки.
Схема базы данных
3. Разработка SQL выборок данных из БД
3.1 Выборка таблицы категорий
После создание базы данных у нас стоит немаловажная задача, написать SQL запрос, который будет доставать нужные данные из нашей БД. Мы будем составлять запросы по такому же порядку, как создавали нашу БД. Первый делом нам нужно написать запрос, который вытащит из нашей БД все категории товаров. Проговорим подробно задачу: нам нужно выбрать поля cat_id, cat_name, parent_id из таблицы cat и сортировать эти данные сначала по полю parent_id, потом по полю cat_name. Все очень просто, грубо говоря мы уже составили запрос осталось только написать его в синтаксисе SQL:
SELECT cat_id, cat_name, parent_id FROM cat ORDER BY
parent_id, cat_name;
Выборка таблицы категорий
Таким образом, мы получили из нашей базы данных список всех категорий
отсортированных по полям parent_id, cat_name.
3.2 Выборка всех товаров определенной категории
Выборка товаров определенной категории имеет не самый тривиальный запрос. По задумке нашего интернет-магазина мы не можем добавлять товар в категорию, которая имеет дочерние категории и тем не менее при выборке всех товаров с предикатом родительской категории мы должны получить все товары дочерних категорий, которые относятся к данной категории.
Запрос будет состоять из 2 подзапросов. Первый подзапрос будет выбирать из таблицы goods все товары где goods_brandid = КЛЮЧ_КАТЕГОРИИ и товар не скрыт на сайте. Второй подзапрос будет немного сложнее, он будет иметь вложенный запрос. Это нужно для того чтобы выбрать все товары дочерних категорий. Потом мы возьмем эти два подзапроса и объединим оператором UNION. В конечном итоге мы получим универсальный запрос который будет выбирать все товары конкретной категории, и если категория родительская, то он будет выбирать все товары всех дочерних подкатегорий.
SQL:
(SELECT goods_id, name, img, anons, price, hits, new, sale, date FROM goods WHERE goods_brandid = $category AND visible='1')
(SELECT goods_id, name, img, anons, price, hits, new, sale, date FROM goods WHERE goods_brandid IN (SELECT brand_id FROM brands WHERE parent_id = $category) AND visible='1')
$category - это ключ выбираемой категории.
На самом деле тут все очень просто, если $category родительская,
то первый подзапрос вернет нам пустой результат, а второй подзапрос вернет нам
все товары из подкатегории где, parent_id = $category (родитель
подкатегории = категории).
Заключение
Мы закрепили изучаемый материал по дисциплине «Информационные ресурсы и
системы. Базы данных». Повторили весь материал и сделали самостоятельную
практическую часть. Мы разработали нормализированную базу данных для
стандартного интернет магазина. При разработке БД, мы придерживались, правилам
так называемых «нормальных форм», тем самым разработали правильную структуру
БД. Также мы продемонстрировали широкое использование реляционной модели базы
данных и работали с сервером MySQL
и СУБД phpMyAdmin. В итоге проектирование и разработке
мы получили базу данных из 7 таблиц. После проектирования и разработки нашей
базы данных интернет-магазина, мы закрепили наши знания по языку
программирования запросов к базе данных SQL и разобрали несколько нестандартных запросов и
показали наиболее оптимальное решение одной нетривиальной задачи выборки данных
из БД.