База данных 'Фирма по продаже запчастей'
Курсовая
работа
База данных
«Фирма по продаже запчастей»
Оглавление
Введение
Основная часть
.Анализ предметной области
.Создание таблиц
.Формализованное описание предметной
области
.Создание базы данных в MS SQL
Server 2008
.Заполнение таблиц
.Простой выбор данных
.Объединение таблиц
.Выбор с помощью группирующих
запросов с условием
.Выбор данных с помощью подзапроса
.Операторы для работы с курсором
.Создание хранимых процедур и
функций
.Изменение структуры
Заключение
Список литературы
предметная
область запрос таблица данные
Введение
Целью курсового проекта по дисциплине «Базы
данных» является систематизация, закрепление и проявление теоретических знаний,
развитие и проявление навыков самостоятельного решения прикладных задач с
применением информационных технологий, практической реализации
автоматизированной обработки экономической информации.
В рамках достижения поставленной цели и
выбранного варианта задания необходимо спроектировать и разработать базу данных
фирмы по продаже запчастей.
В ходе выполнения курсовой работы мы должна
приобрести практические навыки по обследованию конкретной предметной области и
разработке реляционных моделей баз данных экономического направления; научиться
применять прикладные программы общего назначения для решения
экономико-управленческих задач.
Основная
часть
1.
Анализ предметной области
Нашей задачей является отслеживание финансовой
стороны работы компании.
Основная часть деятельности, находящейся в нашем
ведении, связана с работой с поставщиками. Фирма имеет определенный набор
поставщиков, по каждому из которых известны название, адрес и телефон. У этих
поставщиков мы приобретаем детали.
Каждая деталь наряду с названием характеризуется
артикулом и ценой (считаем цену постоянной). Некоторые из поставщиков могут
поставлять одинаковые детали (один и тот же артикул).
Каждый факт покупки запчастей у поставщика
фиксируется, причем обязательными для запоминания являются дата покупки и
количество приобретенных деталей.
2.
Создание таблиц
Определим первичный ключ code_detali
в таблице detail. Также
определим атрибуты nazvanie_detali,
articul, cena,
primechanie.
В таблице
postavchiki первичный ключ
code_postavchika. Артибутами
будут
являться
nazvanie, address, telefon.
В таблице postavki первичный ключ
code_postavki, атрибуты: kolichestvo, data. Притом поля являются обязательными.
Тут атрибуты code_
postavchika и code_detali являются ссылками на ключевые атрибуты отношений
postavchiki и detali и, следовательно, являются внешними ключами.
В сущностях рассматриваемой
предметной области используется связь- один ко многим , это означает, что
одному экземпляру сущности соответсвует 1 или более экземпляров другой
сущности, но каждый экземляр сущности связан не более чем с 1 экземпляром
другой сущности. То есть в нашем случае один поставщик может выполнять
несколько поставок и Одна Деталь может поставляться несколькими поставками.
Родительскими сущностями являются
сущности Детали и Поставщики, а Поставки является дочерней.
3.
Формализованное описание предметной области
Создадим диаграмму сущность-связь с помощью
программного средства ER-Win
DataModeler. Создадим три
сущности, которые рассматриваются в предметной области - postavchiki, detali,
postavki. Для каждой сущности введем набор атрибутов и определим первичный
ключ. Для каждого из атрибутов выберем тип данных. в которой установим
неидентифицирующие связи между сущностями. В результате получим логическую
модель (Рисунок 1):
Рисунок 1
Далее перейдем на физический уровень и установим
больее точный тип данных и размер (Рисунок 2):
Рисунок 2
4.
Создание базы данных в MS SQL Server 2008
Создадим базу данных «Фирма по
продаже запчастей» с помощью команды CREATE DATABASE BD_DETALI. Создадим
таблицы «detali», «postavchiki», «postavki»:BD_DETALI;TABLE detali (_detali INT
IDENTITY (1,1) PRIMARY KEY,_detali VARCHAR (30),INT,FLOAT,VARCHAR (100)
);TABLE postavchiki(_postavchika INT
IDENTITY (1,1) PRIMARY KEY,_postavchika VARCHAR (30),VARCHAR (100),INT
);TABLE postavki(_postavki INT
IDENTITY (1,1) PRIMARY KEY,SMALLDATETIME,INT,_postavchika INT FOREIGN KEY
REFERENCES postavchiki (code_postavchika),_detali INT FOREIGN KEY REFERENCES
detali (code_detali )
);
После добавления таблиц в базу
данных создадим диаграмму базы данных (Рисунок 3):
Рисунок 3
5.
Заполнение таблиц
Заполним таблицу detali (Рисунок 4):INTO detali
(nazvanie_detali,artikul,cena,primechanie) VALUES
('Gidrokompensator','2251234','400','3.7/4.7L')INTO detali
VALUES('Klapan','047956','700','')INTO detali VALUES('Natyagitel
remnya','326578','300','2 rolica')INTO detali VALUES
('Vcladishi','4366936','800','3.7L 0.25MM')INTO detali VALUES('Knopka
rulua','365987','150','levaya')
Рисунок 4
Таблица postavchiki
(Рисунок
5):
INSERT INTO postavchiki
VALUES('Mopar','Kalinina 86','345193')INTO postavchiki VALUES('Victor
Reinz','Gafuri 4','375690')INTO postavchiki VALUES('Crown','Zaki Validi
9','342000')INTO postavchiki VALUES('CLEVITE','Mira 100','320320')INTO
postavchiki VALUES('Delta','Komsonolakaya','348956')
Рисунок 5
Таблица postavki (Рисунок 6):INTO postavki
VALUES('15/03/2015 14:00:00','20','1','2')INTO postavki VALUES('16/03/2015
18:30:00','15','4','3')INTO postavki VALUES('16/03/2015
16:30:00','20','1','4')INTO postavki VALUES('17/03/2015
12:00:00','10','4','4')INTO postavki VALUES('17/03/2015
14:00:00','20','1','5')INTO postavki VALUES('17/03/2015 16:20:00','10','2','2')INTO
postavki VALUES('17/03/2015 12:00:00','15','5','2')INTO postavki
VALUES('18/03/2015 10:30:00','30','2','3')INTO postavki VALUES('18/03/2015
12:40:00','20','5','3')INTO postavki VALUES('18/03/2015
17:00:00','15','2','5')INTO postavki VALUES('20/03/2015
11:50:00','20','3','4')INTO postavki VALUES('20/03/2015
14:00:00','10','3','3')INTO postavki VALUES('23/03/2015
18:20:00','10','3','5')INTO postavki VALUES('24/03/2015
14:30:00','30','4','2')INTO postavki VALUES('24/03/2015 17:00:00','20','5','3')INTO
postavki VALUES('27/03/2015 16:00:00','10','4','3')INTO postavki
VALUES('27/03/2015 16:00:00','10','2','3')
Рисунок 6
Создадим несколько запросов по изменению данных
с использованием условий по разным типам данных полей таблиц.
Изменим в таблице detali содержимое поля
primechanie на «Нет сведений» если значение поля является пустым (Рисунок
7):detali SET primechanie='net svedeny' WHERE primechanie is NULL or
primechanie=''
Рисунок 7
Увеличим в таблице postavki
количество на 2 детали, если поставки были поставлены поставщиков Сrown
(Рисунок 9):
UPDATE postavki SET kolichestvo=kolichestvo+2
WHERE code_postavchika=3
Рисунок 8. Исходная таблица
Рисунок 9
Изменим адрес у поставщика с кодом 5 на «komsomolckaya
73» (Рисунок 11):
UPDATE postavchiki SET adress='komsomolckaya 73'
WHERE code_postavchika=5
Рисунок 10. Исходная таблица
Рисунок 11
Создать несколько запросов по удалению данных с
использованием условий по разным типам данных полей таблиц.
Удалим из таблицы postavki
все записи, у которых kolichestvo=0
(Рисунок 13):
DELETE FROM postavki WHERE
kolichestvo=0
Рисунок 12. Исходная таблица
Рисунок 13
Удалим из таблицы postavchiki
все записи, у которых не указан адрес (Рисунок 15):
DELETE FROM postavchiki WHERE adress is NULL or
adress=''
Рисунок 14. Исходная таблица
Рисунок 15
Удалим из таблицы postavki
все записи, поставки которых оформлялись после 24 марта (Рисунок 17):
DELETE FROM postavki WHERE data
>’24/03/2015’
Рисунок 16. Исходная таблица
Рисунок 17
6.
Простой выбор данных
Выберем из таблицы detali
названия деталей, их цену и примечание (Рисунок 18):
SELECT nazvanie_detali, cena, primechanie FROM
detali
Рисунок 18
Выберем все полня из таблицы detali
и отсортируем результат по артиклу( поле artikul по возрастанию) и по цене(
поле cena по убыванию) (Рисунок 19):
SELECT*FROM detali ORDER BY artikul ASC, cena
DESC
Рисунок19
Выберем из таблицы postavchiki их название и
номер телефона, а из таблицы postavki дату (Рисунок 20):name_postavchika,
telefon, data FROM postavchiki a, postavki b WHERE
a.code_postavchika=b.code_postavchika
Рисунок 20
Выберем названия деталей из таблицы detali, цена
которых равна 300 или 700 (Рисунок 21):nazvanie_detali FROM detali
WHERE(detali.cena='300' OR detali.cena='700')
Рисунок 21
Выберем поставщиков, чьи поставки
оформлялись в период между 17 и 20 марта (Рисунок 23):
SELECT name_postavchika FROM postavchiki a,
postavki b WHERE a.code_postavchika=b.code_postavchika AND data BETWEEN
'17/03/2015' AND '20/03/2015'
Рисунок 22. Исходная таблица
Рисунок 23
Выведем список поставщиков, которые не находятся
по адресу Комсомольская 73 (Рисунок 25):name_postavchika FROM postavchiki WHERE
NOT (adress='komsomolckaya 73')
Рисунок 24. Исходная таблица
Рисунок 25
Выведем названия поставщиков из таблицы postavchiki,
чьи поставки поступили в периоде между 17 и 20 марта (Рисунок 26):
SELECT name_postavchika FROM postavchiki a,
postavki b WHERE a.code_postavchika=b.code_postavchika AND data BETWEEN
'17/03/2015' AND '20/03/2015'
Рисунок 26
Выведем названия поставщиков у которых не
известен адрес. Для этого сначала пополним таблицу следующими данными (Рисунок
28):
INSERT INTO postavchiki
VALUES('Chukamuka',NULL,'320320');INTO postavchiki
VALUES('Hummer',NULL,'348956')name_postavchika FROM postavchiki WHERE adress IS
NULL
Рисунок 27. Исходная таблица
Рисунок 28
Теперь выведем названия поставщиков у которых
известен адрес (Рисунок 29):
SELECT name_postavchika FROM postavchiki WHERE
adress IS NOT NULL
Рисунок 29
Выведем названия поставщиков из таблицы postavchiki,
которые содержат в названии первую букву C
или D, а остальные
произвольны (Рисунок 30):
SELECT name_postavchika FROM postavchiki WHERE
name_postavchika LIKE '[CD]%'
Рисунок 30
SELECT name_postavchika FROM postavchiki WHERE
name_postavchika LIKE '[CD]__V%'
Рисунок 31
Выведем названия поставщиков из таблицы postavchiki,
у которые первая буква в названии не M
или D, следующие символы
произвольны, но последняя буква «а» (Рисунок 32):
SELECT name_postavchika FROM postavchiki WHERE
name_postavchika LIKE '[^DM]%[a]'
Рисунок 32
Выведем список названия деталей из таблицы detail,
которые стоят 700,800 и 300 рублей (Рисунок 33):
SELECT nazvanie_detali FROM detali WHERE cena IN
('700','800',’300’)
Рисунок 33
Выведем суммарную стоимость партии одноименных
деталей и их названия (Рисунок 34):
SELECT nazvanie_detali, cena*kolichestvo AS
'Summa' FROM detali, postavki WHERE postavki.code_detali=detali.code_detali
Рисунок 34
Выведем неповторяющийся список адресов
поставщиков (Рисунок 36):
SELECT DISTINCT adress FROM postavchiki
Рисунок 35. Исходная таблица
Рисунок 36
7.
Объединение таблиц
Выведем список названий деталей, поставщиков в
одном столбце (Рисунок 37):
SELECT nazvanie_detali FROM
detaliname_postavchika FROM postavchiki
Рисунок 37
Запрос на внутренне объеденение.
Выведем названия деталей, который поставлял нам
поставщик 'Victor Reinz' (Рисунок 38):A.nazvanie_detali FROM detali AJOIN
postavki B ON B.code_detali=A.code_detaliJOIN postavchiki C ON
C.code_postavchika=B.code_postavchikaC.name_postavchika='Victor Reinz'
Рисунок 38
Запрос на внешнее левое объединение таблиц.
Выведем список деталей и их количества, а также
детали, котгорые не поставлялись, если такие имеются (Рисунок 39):
SELECT A.nazvanie_detali, B.kolichestvodetali
AJOIN postavki B ON A.code_detali=B.code_detali
Рисунок 39
Внешнее правое объединение таблиц.
Выведем название поставщиков и количество
деталей ими поставляемых. А также количество деталей, чьи поставщики не
известны, если такие имеются (Рисунок 40):
SELECT A.name_postavchika,
B.kolichestvopostavchiki AOUTER JOIN postavki B ON
A.code_postavchika=B.code_postavchika
Рисунок 40
Запрос на полное внешнее объединение таблиц.
Выведем список поставщиков и количество деталей
ими поставленные, включая детали у которых поставщик не известен, а также
поставщиков, которые не поставляли деталей (Рисунок 41):
SELECT A.name_postavchika, B.kolichestvopostavchiki
AOUTER JOIN postavki B ON A.code_postavchika=B.code_postavchika
Рисунок 41
8.
Выбор с помощью группирующих запросов с условием
Выведем общую среднюю цену деталей количество
всех поставленных деталей (Рисунок 42):
SELECT avg(A.cena) AS 'Srednya cena',
SUM(B.kolichestvo) AS 'srednee kolichestvo' FROM detali A, postavki B WHERE
A.code_detali=B.code_detali
Рисунок 42
Выведем поставщиков, у которых среднее
количество поставляемых им деталей больше 15 (Рисунок 43):D.name_postavchika,
AVG(P.kolichestvo) AS 'Srednee kolichestvo'postavchiki DJOIN postavki P ON
D.code_postavchika=P.code_postavchikaBY
D.name_postavchikaAVG(P.kolichestvo)>15
Рисунок 43
Вывести поставщиков и количество поставляемых
ими деталей, у которых цена больше 500 рублей (Рисунок 44):P.name_postavchika,
COUNT(B.nazvanie_detali) AS 'Kolichestvo'postavchiki PJOIN postavki D ON
P.code_postavchika=D.code_postavchikaJOIN detali B ON
D.code_detali=B.code_detaliB.cena>500BY P.name_postavchika
Рисунок 44
9.
Выбор данных с помощью подзапроса
Выведем информацию о поставщике, с макимальным
количеством поставленных деталей (Рисунок 45):C.name_postavchika, C.adress,
C.telefonpostavchiki CJOIN postavki B ON
C.code_postavchika=B.code_postavchikaB.kolichestvo=(SELECT MAX(kolichestvo)
FROM postavki)
Рисунок 45
Определим поставщиков, у которых среднее количество
поставляемых ими деталей больше среднего количества среди всех поставок
(Рисунок 46):D.name_postavchika FROM postavchiki DJOIN postavki P ON
D.code_postavchika=P.code_postavchikaBY
D.name_postavchikaAVG(P.kolichestvo)>(SELECT AVG(kolichestvo) FROM postavki)
Рисунок 46
Выберем поставщиков, количество поставляемых
деталей которых больше 15 (Рисунок 47):C.name_postavchikapostavchiki
Ccode_postavchika IN
(SELECT code_postavchika FROM postavki WHERE
kolichestvo>15)
Рисунок 47
Выберем поставщиков, количество поставляемых
деталей которых не больше 15 (Рисунок 48):C.name_postavchikapostavchiki
Ccode_postavchika NOT IN
(SELECT code_postavchika FROM postavki WHERE
kolichestvo >15)
Выведем детали, которые поставлялись (Рисунок
49):
SELECT nazvanie_detali FROM detali AExists
(SELECT 1 FROM postavki B WHERE B.code_detali=A.code_detali)
Рисунок
49
Определим поставщиков, у которых среднее
количество поставленных им деталей больше среднего количества среди всех
поставок (Рисунок 50):
SELECT A.name_postavchika, B.kolichestvopostavki
BJOIN postavchiki A ON B.code_postavchika=A.code_postavchika B.kolichestvo>(SELECT
AVG(B2.kolichestvo)FROM postavki B2B2.code_postavchika=B.code_postavchika)
Рисунок 50
Определим количество поставок деталей,
сгруппировав их по количеству поставляемых деталей, и исключим те детали,
поставок которых было меньше 4 (Рисунок 51):
SELECT A.kolichestvo, COUNT(A.kolichestvo)
AS[kol-vo postavok]postavki ABY A.kolichestvo4>=
(SELECT COUNT (A2.code_postavki) FROM postavki
A2A2.kolichestvo=A.kolichestvo)
Рисунок 51
Создадим таблицу detal_kolichestvo
c полями названия
детали и количества. И заполним записями из таблиц detai,
postavki (Рисунок 52):
CREATE TABLE detal_kolichestvo(nazvanie_detali
VARCHAR(30), kolichestvo INT);INTO detal_kolichestvoA.nazvanie_detali,
B.kolichestvo FROM detali AJOIN postavki B ON A.code_detali=B.code_detali*from
detal_kolichestvo
Рисунок 52
Увеличим количество поставок на 2, которые имеют
минимальное количество деталей (Рисунок 54):
UPDATE
postavkikolichestvo=kolichestvo+5kolichestvo=(SELECT MIN(B.kolichestvo) FROM
postavki B))
Рисунок 53. Исходная таблица
Рисунок 54
Удалим поставки с минимальным
количеством деталей (Рисунок 55):
DELETE FROM postavkicode_postavki
IN(B1.code_postavki FROM postavki B1B1.kolichestvo=(SELECT MIN(B2.kolichestvo)
FROM postavki B2))
Рисунок 55
10.
Операторы для работы с курсором
Поместив в курсор данные таблицы postavki.
Переберем все записи таблицы и выведем их на экран. Просуммируем значения поля kolichestvo
в переменной sum_table,
которую выведем на экран. Закроем и удалим из памяти курсор (Рисунок 56):
DECLARE MyCursor6 SCROLL CURSOR FOR(SELECT
cena,kolichestvo, code_postavki FROM detali, postavki)@cena FLOAT, @kolvo INT,
@code INTMyCursor6FIRST FROM MyCursor6 INTO @cena, @kolvo, @code@@FETCH_STATUS=0NEXT
FROM MyCursor6 INTO @cena, @kolvo, @code@cena@kolvo@sum_table FLOAT, @sum
FLOAT, @str
CHAR(30)@sum_table=0@code<=MAX(@code)@sum=@cena*@kolvo@sum_table=@sum_table+@sum@str='Summa
proizvedeni'+STR(@sum_table)@str@sum_tableMyCursor6
Рисунок 56
11.
Создание хранимых процедур и функций
Создадим процедуру с входными параметрами.
Выведем дату поступления, название деталей и
поставщиков по определенному коду поставщика (Рисунок 57):PROCEDURE select_infa
@k CHAR(30)nazvanie_detali Cdetali CJOIN
postavki B ON C.code_detali=B.code_detalicode_postavchika=@kname_postavchika
FROM postavchikicode_postavchika=@kdata FROM
postavkicode_postavchika=@kselect_infa @k='3'
Рисунок 57
Создадим процедуру с использованием агрегатных
функций.
Выведем количество деталей, чья цена больше
определенного числа и примечание начинается на цифру 3 (Рисунок 58):
CREATE PROC count_detali3
@cc AS INT,
@a AS
VARCHAR(10)COUNT(code_detali)detalicena>=@ccprimechanie LIKE @acount_detali3
400, '3%'
Рисунок 58
Создать процедуру с входным и выходным
параметрами.
Выведем количество деталей, чья цена больше
определенного числа (Рисунок 59):
ALTER PROC count_detal_itogo
@c INT,
@itogo INT OUTPUT
@itogo=COUNT(code_detali)detalicena>=@c@q AS INT EXEC count_detal_itogo
400,@q output@q
Рисунок 59
Создадим процедуру с входным параметром по
удалению значений одной из таблиц.
Удалим из таблицы postavchiki определенного
поставщика (Рисунок 61):PROC udalenie
@a CHAR(10)FROM
postavchikiname_postavchika=@audalenie 'Chukamuka'
Рисунок 60. Исходная таблица
Рисунок 61
12.
Изменение структуры
Согласно пункту «Развитие постановки задачи», в
которой выяснилось, что цена детали может меняться от поставки к поставке, было
решено дополнить текущую базу данных новой сущностью «Izmenenie_cen_po_date»,
которая позволит хранить не только текущее значение цены, но и всю историю
изменения цен. Ключевым атрибутом будет являться code_detali,
по которому данная сущность связывается с сущностью detail
идентифицирующей связью. Атрибутами будут cena
и data. Так как теперь
цена не постоянная, удалим атрибут cena
в сущности detali.
Таким образом, диаграмма сущность-связь будет
выглядеть следующим образом (Рисунок 62):
Рисунок 62
Заключение
В ходе выполнения курсовой работы мы
систематизировали, закрепили и проявили теоретические знания, развили и
проявили навыков самостоятельного решения прикладных задач с применением
информационных технологий, практической реализации автоматизированной обработки
экономической информации. Также мы приобрели практические навыки по
обследованию конкретной предметной области и разработке реляционных моделей баз
данных экономического направления; научились применять прикладные программы
общего назначения для решения экономико-управленческих задач.
Результатами выполнения курсового проекта
явились:
1. Логическая и физическая модели базы данных
информационно-аналитического центра коммерческого банка.
2. Реляционная модель базы данных банка,
для отслеживания динамики кредитного отдела.
. Разработанная с применением MS
SQL Server
2008 база данных.
1.Базы данных. Методические указания
к лабораторной работе №1: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 31 с.
.Базы данных. Методические указания
к лабораторной работе №2: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 33 с.
.Базы данных. Методические указания
к лабораторной работе №3: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 21 с.
.Базы данных. Методические указания
к лабораторной работе №4: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 26 с.
.Базы данных. Методические указания
к лабораторной работе №5: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 24 с.
.Базы данных. Методические указания
к лабораторной работе №6: / УГАТУ. Сост.: Е.Н.
Прошин - Уфа, 2014. - 24 с.