|
id
|
name
|
description
|
image
|
discount
|
|
1
|
bronze
|
nominal 100000
|
imag1
|
5
|
|
2
|
silver
|
nominal 1500000
|
imag2
|
7
|
|
3
|
gold
|
nominal 5000000
|
imag3
|
10
|
|
4
|
platina
|
nominal 15000000
|
imag4
|
20
|
3.2 Вывод
данных для отчетности
1. Информация о всех продуктах:
SELECT * FROM product;
Рисунок 3.2.1 Результат запроса №1
1. Информация о всех покупателях:
SELECT * FROM client;
Рисунок 3.2.2 Результат запроса №2
2. Запрос продуктов которых производителем является
"Armani” (id=2):
SELECT * FROM product WHERE manufacturer_id=2;
Рисунок 3.2.3 Результат запроса №3
3. Сумма покупки клиента у которого id равен 1:
SELECT itogo FROM chek WHERE client_id=1;
Рисунок 3.2.4 Результат запроса №4
4.
Сумма покупок в период 2014-01-01 и 2016-01-01:
SELECT SUM (itogo) FROM chek WHERE date BETWEEN
‘2014-01-01’ AND ‘2016-01-01’;
Рисунок 3.2.5 Результат запроса №5
5. Сумма продаж продавца которого id равен 1:SUM (itogo)
FROM chek WHERE seller_id=1;
Рисунок 3.2.6 Результат запроса №6
6. Информация о дисконтных карт с упорядочиванием по
типу карты:
SELECT * FROM discount_card ORDER BY type_card;
Рисунок 3.2.7 Результат запроса №7
7. Какой товар продался, название, сумма и количество:
SELECT product. name, sum, purchases. quantity
FROM purchases INNER JOIN product ON purchases. product_id=product. id;
Рисунок 3.2.8 Результат запроса №8
Заключение
При работе над курсовым проектом была проделана работа над
исследованием предметной области, выполнено инфологическое и даталогическое
проектирование, выявлены сущности и бизнес-процессы.
Были выполнены следующие действия:
· Создана реляционная база данных и модель
"Сущность-связь", с последующим указанием связей между сущностями;
· Созданы несколько таблиц в программе MySQL
и были заполнены конкретной информацией, с помощью языка SQL;
· Выполнены несколько запросов, касающихся
вывода нужной нам информации.
На данный момент база данных отвечает всем требованиям,
предоставляемым к учебным базам данных, и практически в полной мере использует
возможности СУБД MySQL.
Список
использованной литературы
1. Бен
Форта "SQL". 2005.
2. Семенова
И.И. "Сборник упражнений по стандарту SQL". 2005.
. Abraham
Silberschatz, Henry F. Korth, S. Sudarshan - Database System Concepts, 6th
Edition. 2011.
. Кевин
Янк - PHP и MySQL. От новичка к профессионалу. 2013.
. Tim
Converse, Joyce Park, Clark Morgan - PHP5 and MySQL Bible. 2004.
. Л.
Аткинсон - MySQL. Библиотека профессионала. 2002.
Приложение
CREATE SCHEMA IF NOT EXISTS `shop` DEFAULT CHARACTER SET
utf8;TABLE IF NOT EXISTS `shop`. `categories` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`parent_id` INT (11),
`category_name` VARCHAR (255) NOT NULL,
`description` TEXT (255) NULL DEFAULT NULL,
`category_image` VARCHAR (45) NULL DEFAULT NULL,KEY
(`id`),INDEX `id_UNIQUE` (`id` ASC),`fk_categories_categories_idx` (`parent_id`
ASC),`fk_categories_categories`KEY (`parent_id`)`shop`. `categories`
(`id`)DELETE NO ACTIONUPDATE NO ACTION)= InnoDBCHARACTER SET = utf8;TABLE IF
NOT EXISTS `shop`. `manufacturers` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR (255) NULL DEFAULT NULL,
`info` VARCHAR (255) NULL DEFAULT NULL,
`logo` VARCHAR (45) NULL DEFAULT NULL,
`site` VARCHAR (45) NULL DEFAULT NULL,
`phone` VARCHAR (45) NULL DEFAULT NULL,
`country` VARCHAR (45) NULL DEFAULT NULL,KEY (`id`),INDEX
`id_UNIQUE` (`id` ASC))= InnoDBCHARACTER SET = utf8;TABLE IF NOT EXISTS `shop`.
`product` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`manufacturer_id` INT (11) NOT NULL,
`name` VARCHAR (45) NOT NULL,
`description` TEXT (255) NULL DEFAULT NULL,
`price` DECIMAL (20,2) NOT NULL,
`image` VARCHAR (45) NULL DEFAULT NULL,
`quantity` INT (11) NOT NULL,KEY (`id`),INDEX `id_UNIQUE`
(`id` ASC),`fk_product_manufacturers1_idx` (`manufacturer_id`
ASC),`fk_product_manufacturers1`KEY (`manufacturer_id`)`shop`. `manufacturers`
(`id`)DELETE NO ACTIONUPDATE NO ACTION)= InnoDBCHARACTER SET = utf8;TABLE IF
NOT EXISTS `shop`. `product_properties` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`product_id` INT (11) NOT NULL,
`property_name` VARCHAR (255) NOT NULL,
`property_value` VARCHAR (255) NOT NULL,
`property_price` DECIMAL (10,2) NULL DEFAULT NULL,KEY
(`id`),INDEX `id_UNIQUE` (`id` ASC),`fk_product_properties_product1_idx`
(`product_id` ASC),`fk_product_properties_product1`KEY (`product_id`)`shop`.
`product` (`id`)DELETE NO ACTIONUPDATE NO ACTION)= InnoDBCHARACTER SET =
utf8;TABLE IF NOT EXISTS `shop`. `product_category` (
`product_id` INT (11) NOT NULL,
`categories_id` INT (11) NOT
NULL,`fk_product_category_categories1_idx` (`categories_id`
ASC),`fk_product_category_product1_idx` (`product_id`
ASC),`fk_product_category_categories1`KEY (`categories_id`)`shop`. `categories`
(`id`)DELETE NO ACTIONUPDATE NO ACTION,`fk_product_category_product1`KEY
(`product_id`)`shop`. `product` (`id`)DELETE NO ACTIONUPDATE NO ACTION)=
InnoDBCHARACTER SET = utf8;TABLE IF NOT EXISTS `shop`. `discount_card` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`serial_number` INT (30) NOT NULL,
`type_dcard` INT (11) NOT NULL,
`client_id` INT (11) NOT NULL,
`accumulations` DECIMAL (20,2) NOT NULL,KEY (`id`),INDEX
`id_UNIQUE` (`id` ASC),`fk_discount_card_type_dcard1_idx` (`type_dcard`
ASC),`fk_discount_card_client1_idx` (`client_id`
ASC),`fk_discount_card_type_dcard1`KEY (`type_dcard`)`shop`. `type_dcard`
(`id`)DELETE NO ACTIONUPDATE NO ACTION,`fk_discount_card_client1`KEY
(`client_id`)`shop`. `client` (`id`)DELETE NO ACTIONUPDATE NO ACTION)=
InnoDBCHARACTER SET = utf8;TABLE IF NOT EXISTS `shop`. `client` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`surname` VARCHAR (45) NULL DEFAULT NULL,
`name` VARCHAR (45) NOT NULL,
`lname` VARCHAR (45) NULL DEFAULT NULL,
`sex` VARCHAR (45) NOT NULL,
`birthday` DATE NULL DEFAULT NULL,
`phone` VARCHAR (45) NOT NULL,
`email` VARCHAR (45) NULL DEFAULT NULL,KEY (`id`),INDEX
`id_UNIQUE` (`id` ASC))= InnoDBCHARACTER SET = utf8;TABLE IF NOT EXISTS `shop`.
`seller` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`surname` VARCHAR (45) NOT NULL,
`name` VARCHAR (45) NOT NULL,
`lname` VARCHAR (45) NOT NULL,
`birthday` DATE NOT NULL,
`phone` VARCHAR (45) NULL DEFAULT NULL,
`address` VARCHAR (255) NOT NULL,KEY (`id`),INDEX `id_UNIQUE`
(`id` ASC))= InnoDBCHARACTER SET = utf8;TABLE IF NOT EXISTS `shop`.
`product_images` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`product_id` INT (11) NOT NULL,
`image` VARCHAR (255) NOT NULL,
`title` VARCHAR (255) NOT NULL,KEY (`id`),INDEX `id_UNIQUE`
(`id` ASC),`fk_product_images_product1_idx` (`product_id`
ASC),`fk_product_images_product1`KEY (`product_id`)`shop`. `product`
(`id`)DELETE NO ACTIONUPDATE NO ACTION)= InnoDBCHARACTER SET = utf8;TABLE IF
NOT EXISTS `shop`. `purchases` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`chek_id` INT (11) NOT NULL,
`product_id` INT (11) NOT NULL,
`quantity` INT (11) NOT NULL,
`sum` DECIMAL (20,2) NOT NULL,KEY (`id`),INDEX `id_UNIQUE`
(`id` ASC),`fk_purchases_product1_idx` (`product_id`
ASC),`fk_purchases_chek1_idx` (`chek_id` ASC),`fk_purchases_product1`KEY (`product_id`)`shop`.
`product` (`id`)DELETE NO ACTIONUPDATE NO ACTION,`fk_purchases_chek1`KEY
(`chek_id`)`shop`. `chek` (`id`)DELETE NO ACTIONUPDATE NO ACTION)=
InnoDBCHARACTER SET = utf8;TABLE IF NOT EXISTS `shop`. `type_dcard` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR (45) NOT NULL,
`description` TEXT (255) NULL DEFAULT NULL,
`image` VARCHAR (45) NOT NULL,
`discount` INT (2) NOT NULL,KEY (`id`),INDEX `id_UNIQUE`
(`id` ASC))= InnoDBCHARACTER SET = utf8;TABLE IF NOT EXISTS `shop`. `chek` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`date` DATETIME NOT NULL,
`client_id` INT (11) NOT NULL,
`discount` INT (2) NULL DEFAULT NULL,
`itogo` DECIMAL (20,2) NOT NULL,
`seller_id` INT (11) NOT NULL,KEY (`id`),INDEX `id_UNIQUE`
(`id` ASC),`fk_chek_seller1_idx` (`seller_id` ASC),`fk_chek_client1_idx`
(`client_id` ASC),`fk_chek_seller1`KEY (`seller_id`)`shop`. `seller`
(`id`)DELETE NO ACTIONUPDATE NO ACTION,`fk_chek_client1`KEY
(`client_id`)`shop`. `client` (`id`)DELETE NO ACTIONUPDATE NO ACTION)=
InnoDBCHARACTER SET =
utf8;SQL_MODE=@OLD_SQL_MODE;FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;INTO
`shop`. `categories` (`category_name`, `description`, `category_image`) VALUES
('men\'s', 'мужская одежда', '1');INTO `shop`. `categories` (`category_name`,
`description`, `category_image`) VALUES ('woman\'s', 'женская одежда',
'2');INTO `shop`. `categories` (`category_name`, `description`,
`category_image`) VALUES ('kid\'s', 'детская одежда', '3');INTO `shop`.
`categories` (`parent_id`, `category_name`, `description`, `category_image`)
VALUES ('1', 'jackets', 'мужской жакет', '4');INTO `shop`. `categories`
(`parent_id`, `category_name`, `description`, `category_image`) VALUES ('1',
'shirts', 'мужские рубашки', '5');INTO `shop`. `categories` (`parent_id`,
`category_name`, `description`, `category_image`) VALUES ('1', 'jeans',
'мужские брюки джинси', '6');INTO `shop`. `categories` (`parent_id`,
`category_name`, `description`, `category_image`) VALUES ('2', 'dresses',
'женские платя', '7');INTO `shop`. `categories` (`parent_id`, `category_name`,
`description`, `category_image`) VALUES ('2', 'sweaters', 'женские свитеры',
'8');INTO `shop`. `categories` (`parent_id`, `category_name`, `description`,
`category_image`) VALUES ('2', 'woman_jeans', 'женские брюки', '9');INTO
`shop`. `categories` (`parent_id`, `category_name`, `description`,
`category_image`) VALUES ('3', 'bodysuits', 'боди для малышей', '10');INTO
`shop`. `categories` (`parent_id`, `category_name`, `description`,
`category_image`) VALUES ('3', 'jackets', 'детские куртки', '11');INTO `shop`.
`categories` (`parent_id`, `category_name`, `description`, `category_image`)
VALUES ('3', 'sleepwear', 'детские ночные вещи', '12');INTO `shop`.
`manufacturers` (`name`, `info`, `logo`, `site`, `phone`, `country`) VALUES
('gucci', 'фирма gucci', 'logo1', 'www.gucci.com', '99999999',
'Uzbekistan');INTO `shop`. `manufacturers` (`name`, `info`, `logo`, `site`,
`phone`, `country`) VALUES ('armani', 'фирма армани', 'logo2',
'www.armani.com', '22222222', 'Uzbekistan');INTO `shop`. `manufacturers`
(`name`, `info`, `logo`, `site`, `phone`, `country`) VALUES ('versaci', 'фирма
', 'logo3', 'www.versace.com', '111111111', 'Uzbekistan');INTO `shop`.
`product` (`manufacturer_id`, `name`, `description`, `price`, `image`,
`quantity`) VALUES ('1', 'shirt t1', 'материал, дизайн и тд. ', '50000.00',
'photo1', '10');INTO `shop`. `product` (`manufacturer_id`, `name`,
`description`, `price`, `image`, `quantity`) VALUES ('2', 'jeans t1',
'материал, дизайн и тд. ', '80000.00', 'photo2', '10');INTO `shop`. `product`
(`manufacturer_id`, `name`, `description`, `price`, `image`, `quantity`) VALUES
('3', 'jacket', 'материал, дизайн и тд. ', '100000.00', 'photo3', '10');INTO
`shop`. `product` (`manufacturer_id`, `name`, `description`, `price`, `image`,
`quantity`) VALUES ('2', 'plate', 'материал, дизайн и тд. ', '50000.00',
'photo4', '10');INTO `shop`. `product` (`manufacturer_id`, `name`,
`description`, `price`, `image`, `quantity`) VALUES ('2', 'sviter', 'материал,
дизайн и тд. ', '80000.00', 'photo5', '10');INTO `shop`. `product`
(`manufacturer_id`, `name`, `description`, `price`, `image`, `quantity`) VALUES
('2', 'jeans w', 'материал, дизайн и тд. ', '100000.00', 'photo6', '10');INTO
`shop`. `product` (`manufacturer_id`, `name`, `description`, `price`, `image`,
`quantity`) VALUES ('1', 'bodi kids', 'материал, дизайн и тд. ', '50000.00',
'photo7', '10');INTO `shop`. `product` (`manufacturer_id`, `name`,
`description`, `price`, `image`, `quantity`) VALUES ('1', 'jacket', 'материал,
дизайн и тд. ', '80000.00', 'photo8', '10');INTO `shop`. `product`
(`manufacturer_id`, `name`, `description`, `price`, `image`, `quantity`) VALUES
('1', 'clothes', 'материал, дизайн и тд. ', '100000.00', 'photo9', '10');INTO
`shop`. `product_category` (`product_id`, `categories_id`) VALUES ('1',
'4');INTO `shop`. `product_category` (`product_id`, `categories_id`) VALUES
('2', '5');INTO `shop`. `product_category` (`product_id`, `categories_id`)
VALUES ('3', '6');INTO `shop`. `product_category` (`product_id`, `categories_id`)
VALUES ('4', '7');INTO `shop`. `product_category` (`product_id`,
`categories_id`) VALUES ('5', '8');INTO `shop`. `product_category`
(`product_id`, `categories_id`) VALUES ('6', '9');INTO `shop`.
`product_category` (`product_id`, `categories_id`) VALUES ('7', '10');INTO
`shop`. `product_category` (`product_id`, `categories_id`) VALUES ('8',
'11');INTO `shop`. `product_category` (`product_id`, `categories_id`) VALUES
('9', '12');INTO `shop`. `product_properties` (`product_id`, `property_name`, `property_value`)
VALUES ('1', 'size', 'XL');INTO `shop`. `product_properties` (`product_id`,
`property_name`, `property_value`) VALUES ('1', 'color', 'red');INTO `shop`.
`product_properties` (`product_id`, `property_name`, `property_value`) VALUES
('2', 'size', 'XL');INTO `shop`. `product_properties` (`product_id`,
`property_name`, `property_value`) VALUES ('2', 'color', 'red');INTO `shop`.
`product_properties` (`product_id`, `property_name`, `property_value`) VALUES
('3', 'size', 'XL');INTO `shop`. `product_properties` (`product_id`,
`property_name`, `property_value`) VALUES ('3', 'color', 'red');INTO `shop`.
`product_properties` (`product_id`, `property_name`, `property_value`) VALUES
('4', 'size', 'XL');INTO `shop`. `product_properties` (`product_id`, `property_name`,
`property_value`) VALUES ('4', 'color', 'red');INTO `shop`.
`product_properties` (`product_id`, `property_name`, `property_value`) VALUES
('5', 'size', 'XL');INTO `shop`. `product_properties` (`product_id`,
`property_name`, `property_value`) VALUES ('5', 'color', 'red');INTO `shop`.
`product_properties` (`product_id`, `property_name`, `property_value`) VALUES
('6', 'size', 'XL');INTO `shop`. `product_properties` (`product_id`,
`property_name`, `property_value`) VALUES ('6', 'color', 'red');INTO `shop`.
`product_properties` (`product_id`, `property_name`, `property_value`) VALUES
('7', 'size', 'XL');INTO `shop`. `product_properties` (`product_id`,
`property_name`, `property_value`) VALUES ('7', 'color', 'red');INTO `shop`.
`product_properties` (`product_id`, `property_name`, `property_value`) VALUES
('8', 'size', 'XL');INTO `shop`. `product_properties` (`product_id`,
`property_name`, `property_value`) VALUES ('8', 'color', 'red');INTO `shop`.
`product_properties` (`product_id`, `property_name`, `property_value`) VALUES
('9', 'size', 'XL');INTO `shop`. `product_properties` (`product_id`,
`property_name`, `property_value`) VALUES ('9', 'color', 'red');INTO `shop`.
`product_images` (`product_id`, `image`, `title`) VALUES ('1', 'foto1',
'foto');INTO `shop`. `product_images` (`product_id`, `image`, `title`) VALUES
('2', 'foto2', 'foto');INTO `shop`. `product_images` (`product_id`, `image`,
`title`) VALUES ('3', 'foto3', 'foto');INTO `shop`. `product_images`
(`product_id`, `image`, `title`) VALUES ('1', 'foto1.1', 'foto');INTO `shop`.
`product_images` (`product_id`, `image`, `title`) VALUES ('1', 'foto1.2',
'foto');INTO `shop`. `product_images` (`product_id`, `image`, `title`) VALUES
('4', 'foto4', 'foto');INTO `shop`. `product_images` (`product_id`, `image`,
`title`) VALUES ('5', 'foto5', 'foto');INTO `shop`. `product_images`
(`product_id`, `image`, `title`) VALUES ('5', 'foto5', 'foto');INTO `shop`.
`product_images` (`product_id`, `image`, `title`) VALUES ('5', 'foto5',
'foto');INTO `shop`. `type_dcard` (`name`, `description`, `image`, `discount`)
VALUES ('bronze', 'nominal 100000', 'imag1', '5');INTO `shop`. `type_dcard`
(`name`, `description`, `image`, `discount`) VALUES ('silver', 'nominal
1500000', 'imag2', '7');INTO `shop`. `type_dcard` (`name`, `description`,
`image`, `discount`) VALUES ('gold', 'nominal 5000000', 'imag3', '10');INTO
`shop`. `type_dcard` (`name`, `description`, `image`, `discount`) VALUES
('platina', 'nominal 15000000', 'imag4', '20');INTO `shop`. `client`
(`surname`, `name`, `sex`, `birthday`, `phone`, `email`) VALUES ('scofield',
'michael', 'man', '1988-01-11', '+998946628220', 'qwert@gmail.com');INTO
`shop`. `client` (`surname`, `name`, `sex`, `birthday`, `phone`, `email`)
VALUES ('cyrus', 'miley', 'woman', '1989-02-12', '+998946628220',
'qwert@gmail.com');INTO `shop`. `client` (`surname`, `name`, `sex`, `birthday`,
`phone`, `email`) VALUES ('finch', 'brian', 'man', '1986-01-01',
'+998946628220', 'qwert@gmail.com');INTO `shop`. `client` (`surname`, `name`,
`sex`, `birthday`, `phone`, `email`) VALUES ('morro', 'edvard', 'man',
'1980-01-01', '+998946628220', 'qwert@gmail.com');INTO `shop`. `discount_card`
(`serial_number`, `type_dcard`, `client_id`, `accumulations`) VALUES
('12345678', '1', '1', '100000');INTO `shop`. `discount_card` (`serial_number`,
`type_dcard`, `client_id`, `accumulations`) VALUES ('00000001', '2', '2',
'1500000');INTO `shop`. `discount_card` (`serial_number`, `type_dcard`,
`client_id`, `accumulations`) VALUES ('00000002', '3', '3', '5000000');INTO
`shop`. `discount_card` (`serial_number`, `type_dcard`, `client_id`,
`accumulations`) VALUES ('00000003', '4', '4', '15000000');INTO `shop`.
`seller` (`surname`, `name`, `lname`, `birthday`, `phone`, `address`) VALUES
('Obidov', 'Javlon', 'lname', '1993-09-04', '+998998048220', 'tashkent city,
region yunusabad');INTO `shop`. `seller` (`surname`, `name`, `lname`,
`birthday`, `phone`, `address`) VALUES ('Erkinov', 'Sardor', 'lname',
'1994-05-22', '+998998048220', 'tashkent city, region yunusabad');INTO `shop`.
`seller` (`surname`, `name`, `lname`, `birthday`, `phone`, `address`) VALUES
('Erkinov', 'Anvar', 'lname', '1993-10-10', '+998998048220', 'tashkent city,
region yunusabad');INTO `shop`. `seller` (`surname`, `name`, `lname`,
`birthday`, `phone`, `address`) VALUES ('Gomez', 'Selena', 'lname',
'1992-10-10', '+998998048220', 'tashkent city, region yunusabad');INTO `shop`.
`chek` (`date`, `client_id`, `discount`, `itogo`, `seller_id`) VALUES
('2015-01-01 00: 00: 00', '1', '5', '100000.00', '1');INTO `shop`. `chek`
(`date`, `client_id`, `discount`, `itogo`, `seller_id`) VALUES ('2015-01-01 00:
00: 00', '2', '7', '300000', '3');INTO `shop`. `chek` (`date`, `client_id`,
`discount`, `itogo`, `seller_id`) VALUES ('2015-01-01 00: 00: 00', '3', '10',
'210000', '1');INTO `shop`. `chek` (`date`, `client_id`, `discount`, `itogo`,
`seller_id`) VALUES ('2015-01-01 00: 00: 00', '4', '20', '100000', '3');INTO
`shop`. `purchases` (`chek_id`, `product_id`, `quantity`, `sum`) VALUES ('1',
'1', '3', '150000.00');INTO `shop`. `purchases` (`chek_id`, `product_id`,
`quantity`, `sum`) VALUES ('2', '6', '2', '200000.00');INTO `shop`. `purchases`
(`chek_id`, `product_id`, `quantity`, `sum`) VALUES ('2', '7', '2',
'100000.00');INTO `shop`. `purchases` (`chek_id`, `product_id`, `quantity`,
`sum`) VALUES ('3', '8', '2', '160000.00');INTO `shop`. `purchases` (`chek_id`,
`product_id`, `quantity`, `sum`) VALUES ('3', '1', '1', '50000.00');INTO
`shop`. `purchases` (`chek_id`, `product_id`, `quantity`, `sum`) VALUES ('4',
'9', '1', '100000.00');