База данных автосалона
Разработка программного продукта - базы данных для хранения и обработки информации о товарах в автосалоне. Составление процедур - запросов для обработки реестра покупок, классификации товаров по их коду, расчета минимальной цены и определения скидок.
| Рубрика | Программирование, компьютеры и кибернетика |
| Вид | курсовая работа |
| Язык | русский |
| Дата добавления | 13.01.2013 |
| Размер файла | 538,5 K |
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
2
Размещено на http://www.allbest.ru/
Размещено на http://www.allbest.ru/
ПОСТАНОВКА ЗАДАЧИ
Задание 1
Создать базу данных, состоящую из следующих таблиц.
Справочник товаров.
Поля:
Идентификатор товара (целочисленное (4 байта), автоматическая генерация значения: начальное значение - 10, приращение - 2), первичный ключ
Код товара (тип - текстовый, постоянное кол-во символов, 15 символов), обязательное
Наименование товара (тип - текстовый, переменное кол-во символов, 25 символов), обязательное
Описание (тип текстовый Unicode, переменное кол-во символов, 1000 символов), не обязательное
Начало продаж (тип - дата и время (4 байта)), обязательное
Реестр покупок
Поля:
Идентификатор покупки (целочисленное (8 байт), автоматическая генерация значения: начальное значение - 1, приращение - 1)
Дата и время покупки (тип - дата и время (8 байт)), обязательное
Идентификатор купленного товара (тип -целочисленный (4 байта)) - ссылка на справочник товаров, обязательное
Кол-во проданного товара (тип -целочисленный (2 байта)), обязательное
Цена единицы товара (тип - денежный(4 байта)), обязательное
Скидка (тип - дробное с фиксированной точкой (2 разряда на целую часть, 2 разряда - на десятичную)) - скидка в процентах от суммы покупки, необязательное
Покупатель (тип - текстовый, переменное кол-во символов, 100 символов) - фамилия, имя и отчество покупателя, необязательное
Задание 2.
Заполните базу данных:
Введите 10 товаров
Введите 20-30 покупок. Покупки должны быть за 2007 и 2008 года. В поле покупатель должны присутствовать значения в форматах:
Фамилия
Фамилия Имя Отчество
Фамилия Имя
Фамилия И.О.
Фамилия И.
Задание 3
Напишите запрос , который выберет из реестра покупок все покупки, совершенные ранее 01.01.2008. Запрос должен вернуть все поля из реестра покупок.
Напишите запрос, который вернет покупки для определенного кода товара. Запрос должен вернуть следующие поля: Наименование товара, Покупатель, Кол-во проданного товара.
Напишите запрос, который вернёт все покупки для товаров, наименования которых содержат определённую подстроку. Запрос должен вернут следующие поля: Идентификатор покупки, Покупатель, Наименование товара, Сумма покупки (Кол-во товара * Цена ед. товара * (100% - Скидка)). Запрос оформить в виде хранимой процедуры.
Задание 4
Напишите запрос, который выводит среднюю скидку, максимальную и минимальную цену для определённого id товара.
Напишите запрос, выводящий суммы покупок и среднюю цену по кодам товара. Товары, которые ни разу не продавались, НЕ ДОЛЖНЫ присутствовать в результате запроса. Запрос оформить в виде хранимой процедуры.
Напишите запрос, выводящий товары, с количеством продаж более определенного значения (параметр хранимой процедуры). Одна продажа - один факт продажи, вне зависимости от кол-ва проданного товара. Результат запроса должен содержать следующие поля: код товара, наименование товара, кол-во продаж, максимальную и минимальную цену товара. Запрос оформить в виде хранимой процедуры.
Напишите запрос, выводящий ВСЕ товары и суммарное кол-во купленных единиц для каждого товара. Результат запроса должен содержать поля: код товара, имя товара и кол-во товара. Запрос оформить в виде хранимой процедуры.
Напишите запрос, выводящий Фамилию самого ценного покупателя (покупателя, заплатившего больше всего за покупки) за определённый период (начало и конец периода передается в виде параметров). Обратите внимание, что в поле покупатель могут быть покупатели в разных форматах. Запрос оформить в виде хранимой процедуры.
Задание 5
Напишите хранимую процедуру, которая возвращает (в виде выходного параметра) максимальную сумму покупки для определённого кода товара (код товара - входной параметр хранимой процедуры) на определённый месяц (входные параметры: год и месяц), а также историю продаж товара по дням - два столбца: день месяца (должны быть все дни указанного месяца), сумма покупок. Проверьте результат выполнения процедуры вручную.
Напишите хранимую процедуру, которая возвращает для определённого товара на указанный месяц (входные параметры: код товара, год и месяц) кол-во проданного товара нарастающим итогом (т.е. каждая строчка должна содержать кол-во проданного товара с начала месяца до текущего дня включительно). Проверьте результат выполнения процедуры вручную.
Выполнение работы
база данные программный покупка
Задание 1.
create database mydb
use mydb
create table dbo.products(
product_id int not null identity(10,2),
product_code char(15) not null,
product_name varchar(25) not null,
product_desc nvarchar(1000) null,
product_SoS smalldatetime not null
)
alter table dbo.products
add constraint pk_products primary key (product_id)
create table dbo.purchases(
purchase_id bigint not null identity(1,1),
purchase_time datetime not null,
product_id int not null,
purchase_count smallint not null,
purchase_cost smallmoney not null,
purchase_discount decimal(4,2) null,
purchase_buyer varchar(100) null
)
alter table dbo.purchases
add constraint fk_purchases foreign key (product_id) references dbo.products(product_id)
Задание 2.
insert dbo.products(product_code, product_name,product_SoS)
values('PIZZA', 'Пица с грибами', '2005-01-01 00:00:00'),
('TEA', 'Индийский чай', '2005-01-01 00:00:00'),
('BREAD', 'Нарезной батон','2005-01-01 00:00:00'),
('WATER', 'Вода в бутылке','2005-01-01 00:00:00'),
('POTATO', 'Картошка', '2005-01-01 00:00:00'),
('CHOCOLATE', 'Шоколадный батончик','2005-01-01 00:00:00'),
('DOUGHNUTS', 'Пончики', '2005-01-01 00:00:00'),
('SHAMPOO', 'Шампунь', '2005-01-01 00:00:00'),
('JUICE', 'Апельсиновый сок', '2005-01-01 00:00:00'),
('DVD', 'DVD фильм', '2005-01-01 00:00:00')
select * from dbo.products
insert dbo.purchases(purchase_time, product_id, purchase_count, purchase_cost, purchase_discount,purchase_buyer )
values('2007-12-12 12:30:00', 12, 1, 50.00, 3.50 ,'Дунаев А.'),
('2007-13-12 8:30:00', 16, 1, 30.50, NULL,'Поташев Д. А.'),
('2007-14-12 21:20:00', 10, 2, 49.75, NULL,'Анисимов'),
('2007-15-12 12:15:00', 18, 1, 60.40, 2, 'Анисимов Алексей'),
('2007-16-12 18:45:00', 14, 1, 21.50, 1,'Поташев Дмитрий Александрович'),
('2007-17-12 8:45:00', 16, 1, 30.50, NULL,'Поташев Дмитрий'),
('2007-18-12 19:11:00', 18, 2, 60.40, 3,'Разина А.'),
('2007-19-12 20:00:00', 26, 2, 14.88, NULL,NULL),
('2007-20-12 20:00:00', 26, 8, 14.88, NULL,NULL),
('2007-21-12 8:00:00', 16, 1, 30.50, 2,'Поташев Д.'),
('2007-22-12 14:15:00', 12, 1, 50.00, NULL,'Дунаев А. Н.'),
('2007-23-12 16:45:00', 12, 2, 50.00, 3.75,'Дунаев Александр'),
('2007-24-12 20:00:00', 10, 2, 49.75, NULL,'Разина Анастасия'),
('2007-25-12 14:59:00', 10, 2, 49.75, NULL,'Поташев Д.'),
('2007-26-12 12:31:00', 16, 1, 30.20, 7.25,'Дунаев А. Н.'),
('2007-27-12 12:31:00', 18, 3, 60.32, 3.50,'Путинцев А.'),
('2007-30-12 16:00:00', 10, 20, 49.75, 5,'Поташев Д. А.'),
('2007-30-12 16:00:00', 14, 2, 21.50, 5,'Поташев Д. А.'),
('2007-30-12 16:00:00', 12, 4, 50.00, 5,'Поташев Д. А.'),
('2007-30-12 16:00:00', 18, 4, 60.40, 5,'Поташев Д. А.'),
('2007-30-12 16:00:00', 16, 2, 30.50, 5,'Поташев Д. А.'),
('2008-02-01 7:22:00', 22, 1, 115.50, NULL,'Поташев Д. А.'),
('2008-03-01 20:00:00', 26, 8, 14.88, NULL,'Поташев Д. А.'),
('2008-04-01 12:30:00', 24, 1, 32.17, NULL,'Поташев Д. А.'),
('2008-05-01 12:30:00', 28, 1, 99.99, NULL,'Дунаев А. Н.')
Результат:
Задание 3
1) select * from dbo.purchases
where purchase_time < '2008-01-01'
Результат:
2) select pr.product_name, p.purchase_buyer, p.purchase_count
from dbo.products as pr
join dbo.purchases as p
on pr.product_id = p.product_id
where pr.product_code = 'PIZZA'
Результат:
3) if exists (select * from dbo.sysobjects
where id = object_id(N'find_purchases_masked') and objectproperty(id, N'IsProcedure') = 1
)
drop procedure find_purchases_masked
go
create procedure find_purchases_masked @mask varchar(25)
as
select pr.product_id, p.purchase_buyer, pr.product_name,
case when p.purchase_discount is null then p.purchase_count * p.purchase_cost
else p.purchase_count * p.purchase_cost * ( (100-p.purchase_discount) / 100)
end as [Total cost]
from dbo.products as pr
join dbo.purchases as p
on pr.product_id = p.product_id
where pr.product_name like '%'+@mask+'%'
go
Результат:
execute find_purchases_masked @mask = 'ин'
Задание 4.
1) select SUM(p.purchase_discount) / COUNT(*) as Average_discount,
MIN(p.purchase_cost)
as min_cost,
max(p.purchase_cost)
as max_cost
from dbo.purchases as p
where product_id = 18
Результат:
2) if exists (select * from dbo.sysobjects
where id = object_id(N'find_purchases') and objectproperty(id, N'IsProcedure') = 1
)
drop procedure find_purchases
go
create procedure find_purchases
as
select pr.product_code as code,
sum(case when p.purchase_discount is null then p.purchase_count * p.purchase_cost
else p.purchase_count * p.purchase_cost * ( (100-p.purchase_discount) / 100)
end) as [Total Cost],
avg(p.purchase_cost) as Avg_cost
from dbo.purchases as p join
dbo.products as pr on pr.product_id = p.product_id group by pr.product_code;
go
Результат:
execute find_purchases
3) if exists (select * from dbo.sysobjects
where id = object_id(N'selled_products') and objectproperty(id, N'IsProcedure') = 1
)
drop procedure selled_products
go
create procedure selled_products
@count int
as
select pr.product_code as code,
pr.product_name as NAME,
COUNT(*) as [count],
max(p.purchase_cost)
as max_cost,
min(p.purchase_cost)
as min_cost
from dbo.products as pr
join dbo.purchases as p
on pr.product_id = p.product_id
group by pr.product_code, pr.product_name
having COUNT(*) > @count
go
Результат:
execute selled_products @count = 2
4) if exists (select * from dbo.sysobjects
where id = object_id(N'number_of_sold') and objectproperty(id, N'IsProcedure') = 1
)
drop procedure number_of_sold
go
create procedure number_of_sold
as
select pr.product_code as code,
pr.product_name as NAME,
sum(p.purchase_count) as number
from dbo.products as pr join
dbo.purchases as p
on pr.product_id = p.product_id
group by pr.product_code,pr.product_id, pr.product_name
go
Результат:
execute number_of_sold
5) if exists (select * from dbo.sysobjects
where id = object_id(N'best_buyer') and objectproperty(id, N'IsProcedure') = 1
)
drop procedure best_buyer
go
create procedure best_buyer @b datetime, @e datetime
as
select top 1
case
when charindex(' ', p.purchase_buyer)=0 then p.purchase_buyer
else substring(p.purchase_buyer,0, charindex(' ', p.purchase_buyer))
end as [Покупатель],
SUM(case when p.purchase_discount is null then p.purchase_count * p.purchase_cost
else p.purchase_count * p.purchase_cost * ( (100-p.purchase_discount) / 100)
end) as [Total Spent]
from dbo.purchases as p
where p.purchase_time >= @b and p.purchase_time <= @e
group by p.purchase_buyer, (case
when charindex(' ', p.purchase_buyer)=0 then p.purchase_buyer
else substring(p.purchase_buyer,0, charindex(' ', p.purchase_buyer))
end)
order by [Total Spent] desc
go
Результат:
execute best_buyer @b = '2007-20-12', @e = '2008-20-12'
Задание 5.
Для выполнения этого задания дополнительно были реализованы функции определяющий число дней в месяце, а так же является ли год високосным.
DROP FUNCTION IsYearLeap
GO
CREATE FUNCTION IsYearLeap
(
@year int
)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN ((@year % 4 = 0) AND (@year % 100 != 0)) OR (@year % 400 = 0) THEN 1 ELSE 0 END
END
GO
DROP FUNCTION GetMaxDaysInMonth
GO
CREATE FUNCTION GetMaxDaysInMonth
(
@month int,
@year int
)
RETURNS INT
AS
BEGIN
RETURN CASE WHEN @month IS NULL THEN 31
WHEN @month IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN @month IN (4, 6, 9, 11) THEN 30
WHEN @month = 2 THEN CASE WHEN (@year IS NULL) OR (dbo.IsYearLeap(@year) <> 0)
THEN 29 ELSE 28
END
ELSE 0
END
END
GO
1) create procedure max_purchase @code char(15), @year int, @month int, @maxcost smallmoney output
as
CREATE TABLE #tempTable
(
p_day INT ,
p_sum SMALLMONEY
);
insert into #tempTable
select DAY(p.purchase_time) as p_day,
(sum(case when p.purchase_discount is null then p.purchase_count * p.purchase_cost
else p.purchase_count * p.purchase_cost * ( (100-p.purchase_discount) / 100)
end)) as p_sum
from dbo.purchases as p join
dbo.products pr on p.product_id = pr.product_id
where pr.product_code = @code and year(p.purchase_time) = @year and month(p.purchase_time) = @month
group by DAY(p.purchase_time)
declare @to int = dbo.GetMaxDaysInMonth(@month, @year)
declare @counter int = 1
while @counter <= @to
begin
if not exists(select * from #tempTable where p_day = @counter)
begin
insert into #tempTable (p_day, p_sum) values (@counter, 0)
end
set @counter = @counter + 1
end
select * from #tempTable order by p_day
select @maxcost = max(case when p.purchase_discount is null then p.purchase_count * p.purchase_cost
else p.purchase_count * p.purchase_cost * ( (100-p.purchase_discount) / 100)
end) from dbo.purchases as p
join
dbo.products pr on p.product_id = pr.product_id
where pr.product_code = @code and year(p.purchase_time) = @year and month(p.purchase_time) = @month
group by DAY(p.purchase_time)
go
Результат:
declare @var smallmoney
execute max_purchase @code = 'PIZZA', @year = 2007, @month = 12 ,@maxcost = @var output
select @var
2) drop procedure purchases_incr
go
create procedure purchases_incr @code char(15), @year int, @month int
as
CREATE TABLE #tempTable
(
p_day INT ,
p_count int
);
insert into #tempTable
select DAY(p.purchase_time) as p_day,
sum(p.purchase_count) as p_count
from dbo.purchases as p join
dbo.products pr on p.product_id = pr.product_id
where pr.product_code = @code and year(p.purchase_time) = @year and month(p.purchase_time) = @month
group by DAY(p.purchase_time)
declare @to int = dbo.GetMaxDaysInMonth(@month, @year)
declare @counter int = 1
while @counter <= @to
begin
if not exists(select * from #tempTable where p_day = @counter)
begin
insert into #tempTable (p_day, p_count) values (@counter, 0)
end
set @counter = @counter + 1
end
select p_day, (select SUM(tt.p_count) from #tempTable tt where tt.p_day <= t.p_day) from #tempTable t order by p_day
return 0
go
Результат
Размещено на Allbest.ru
...Подобные документы
Процесс разработки базы данных для хранения и обработки информации. Ключи, индексы, триггеры, хранимые процедуры. Разработка пользовательского интерфейса и базы данных. Основные инструментальные средства для разработки клиентской и серверной частей.
дипломная работа [225,0 K], добавлен 18.05.2013Разработка базы данных с информацией о сотрудниках, товарах, со справочником типов товаров средствами системы управления базами данных MySQL с помощью SQL-запросов. Разработка инфологической модели предметной области. Структура таблиц, полей базы данных.
контрольная работа [648,7 K], добавлен 13.04.2012Программные продукты, используемые при проектировании базы данных. Разработка базы данных "Библиотека" с использование программного проекта Microsoft SQL Server. Создание таблиц, триггеров, пользователей, репликации, запросов, функций, процедур.
курсовая работа [897,6 K], добавлен 21.11.2011Разработка базы данных для автоматизации учета и хранения сведений о заявках от работодателей. Проектирование приложения в СУБД Access. Описание запросов, отчетов и представлений данных. Интерфейс, условия выполнения и тестирование программного продукта.
курсовая работа [3,7 M], добавлен 05.04.2012Создание базы данных для хранения и обработки информации городской телефонной станции. Состав программного продукта. Практические аспекты лицензирования Access. Инфологическое моделирование системы. Определение логической структуры реляционной базы.
курсовая работа [295,1 K], добавлен 16.12.2013Разработка базы данных "Поставка и реализация продуктов питания". Применение базы данных. Цель инфологического проектирования. Выборка информации при помощи запросов. Подпрограммы, работающие на сервере и управляющие процессами обработки информации.
курсовая работа [326,0 K], добавлен 28.06.2011Особенности проектирования программы на языке С++ для обработки данных из таблиц базы данных. Основные функции программы, создание концептуальной модели базы данных и диаграммы классов, разработка интерфейса пользователя и запросов к базе данных.
курсовая работа [2,1 M], добавлен 08.06.2012Проведение исследования стандартов и основ проектирования базы данных. Особенность создания запросов на языке SQL. Функциональные требования, предъявляемые к программе Microsoft SQL Server. Анализ заполнения таблиц. Создание процедур и запросов.
курсовая работа [2,7 M], добавлен 19.03.2021Построение информационно-логической модели базы данных. Корректировка данных средствами запросов. Проектирование алгоритмов обработки данных. Реализация пользовательского интерфейса средствами форм. Разработка запросов для корректировки и выборки данных.
курсовая работа [680,9 K], добавлен 19.10.2010Проектирование базы данных, предназначенной для ввода, хранения и обработки информации по продажам сотовых телефонов. Список таблиц и свойств полей таблиц. Описание созданных запросов, отчётов и форм. Руководство пользователя, условия применения данных.
курсовая работа [1,9 M], добавлен 14.05.2014Определение базы данных и банков данных. Компоненты банка данных. Основные требования к технологии интегрированного хранения и обработки данных. Система управления и модели организации доступа к базам данных. Разработка приложений и администрирование.
презентация [17,1 K], добавлен 19.08.2013Исследование значения информации и информационных услуг в современном мире. Изучение истории хранения и обработки информации. Проектирование инфологической модели базы данных. Реляционная модель баз данных. Домены и отношения. Реляционное исчисление.
курсовая работа [47,9 K], добавлен 13.07.2015База данных для ЗАО "ФК "Зенит", предназначенная для хранения и обработки данных о работниках клуба, его бюджете и результатах участия в соревнованиях. Разработка предварительных отношений и пользовательского интерфейса. Структура таблиц базы данных.
курсовая работа [4,4 M], добавлен 10.12.2011Использование баз данных менеджерами автосалонов для повышения качества и скорости обслуживания клиентов. Создание запросов на добавление, удаление, обновление данных. Запросы перекрестный, на выборку. Кнопочная форма базы данных с практичным интерфейсом.
курсовая работа [1,6 M], добавлен 10.02.2014Проведение системного анализа предметной области и разработка проекта по созданию базы данных для хранения информации о перевозках пассажиров и грузов. Обоснование выбора системы управления базой данных и разработка прикладного программного обеспечения.
курсовая работа [1,1 M], добавлен 18.07.2014Разработка проекта базы данных для сбора данных в компании, владеющей сетью ресторанов, с целью ведения контроля над заведениями, накопления и хранения информации о деятельности каждого ресторана в сети. Реализация запросов для получения информации.
курсовая работа [501,7 K], добавлен 02.12.2014Системы управления базами данных и их использование для решения задач автоматизации предприятия. Разработка информационного и программного обеспечения для автоматизации хранения и обработки информации при организации работы агропромышленного предприятия.
курсовая работа [607,1 K], добавлен 07.05.2011Разработка и программная реализация сайта и базы данных, наполнение базы данных тестовой информацией о товарах. Инструментальные средства создания сайта. Организация тестирования сайта, модуль визуализации интерфейса. Создание запросов в базе данных SQL.
курсовая работа [1,4 M], добавлен 24.12.2012Разработка программного продукта "ИС Автотранспорт". Автоматизация функционирования автопарка и временного склада товаров, учета заявок клиентов и заполнения путевых листов. Реляционная модель базы данных. Описание функционирования программного продукта.
дипломная работа [1,8 M], добавлен 14.03.2017Основные понятия базы данных. Разработка сложной формы для обработки данных. Модели организации данных. Архитектура Microsoft Access. Реляционные связи между таблицами баз данных. Проектирование базы данных. Модификация данных с помощью запросов действий.
лабораторная работа [345,5 K], добавлен 20.12.2011
