База данных "Фирма по продаже запчастей"

Размещено на http://www.allbest.ru/

14

Курсовая работа

База данных «Фирма по продаже запчастей»

Оглавление

Введение

Основная часть

1.Анализ предметной области

2.Создание таблиц

3.Формализованное описание предметной области

4.Создание базы данных в MS SQL Server 2008

5.Заполнение таблиц

6.Простой выбор данных

7.Объединение таблиц

8.Выбор с помощью группирующих запросов с условием

9.Выбор данных с помощью подзапроса

10.Операторы для работы с курсором

11.Создание хранимых процедур и функций

12.Изменение структуры

Заключение

Список литературы

предметная область запрос таблица данные

Введение

Целью курсового проекта по дисциплине «Базы данных» является систематизация, закрепление и проявление теоретических знаний, развитие и проявление навыков самостоятельного решения прикладных задач с применением информационных технологий, практической реализации автоматизированной обработки экономической информации.

В рамках достижения поставленной цели и выбранного варианта задания необходимо спроектировать и разработать базу данных фирмы по продаже запчастей.

В ходе выполнения курсовой работы мы должна приобрести практические навыки по обследованию конкретной предметной области и разработке реляционных моделей баз данных экономического направления; научиться применять прикладные программы общего назначения для решения экономико-управленческих задач.

Основная часть

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»:

USE BD_DETALI;

CREATE TABLE detali (

code_detali INT IDENTITY (1,1) PRIMARY KEY,

nazvanie_detali VARCHAR (30),

artikul INT,

cena FLOAT,

primechanie VARCHAR (100)

);

CREATE TABLE postavchiki(

code_postavchika INT IDENTITY (1,1) PRIMARY KEY,

name_postavchika VARCHAR (30),

adress VARCHAR (100),

telefon INT

);

CREATE TABLE postavki(

code_postavki INT IDENTITY (1,1) PRIMARY KEY,

data SMALLDATETIME,

kolichestvo INT,

code_postavchika INT FOREIGN KEY REFERENCES postavchiki (code_postavchika),

code_detali INT FOREIGN KEY REFERENCES detali (code_detali )

);

После добавления таблиц в базу данных создадим диаграмму базы данных (Рисунок 3):

Рисунок 3

5. Заполнение таблиц

Заполним таблицу detali (Рисунок 4):

INSERT INTO detali (nazvanie_detali,artikul,cena,primechanie) VALUES ('Gidrokompensator','2251234','400','3.7/4.7L')

INSERT INTO detali VALUES('Klapan','047956','700','')

INSERT INTO detali VALUES('Natyagitel remnya','326578','300','2 rolica')

INSERT INTO detali VALUES ('Vcladishi','4366936','800','3.7L 0.25MM')

INSERT INTO detali VALUES('Knopka rulua','365987','150','levaya')



Рисунок 4

Таблица postavchiki (Рисунок 5):

INSERT INTO postavchiki VALUES('Mopar','Kalinina 86','345193')

INSERT INTO postavchiki VALUES('Victor Reinz','Gafuri 4','375690')

INSERT INTO postavchiki VALUES('Crown','Zaki Validi 9','342000')

INSERT INTO postavchiki VALUES('CLEVITE','Mira 100','320320')

INSERT INTO postavchiki VALUES('Delta','Komsonolakaya','348956')

Рисунок 5

Таблица postavki (Рисунок 6):

INSERT INTO postavki VALUES('15/03/2015 14:00:00','20','1','2')

INSERT INTO postavki VALUES('16/03/2015 18:30:00','15','4','3')

INSERT INTO postavki VALUES('16/03/2015 16:30:00','20','1','4')

INSERT INTO postavki VALUES('17/03/2015 12:00:00','10','4','4')

INSERT INTO postavki VALUES('17/03/2015 14:00:00','20','1','5')

INSERT INTO postavki VALUES('17/03/2015 16:20:00','10','2','2')

INSERT INTO postavki VALUES('17/03/2015 12:00:00','15','5','2')

INSERT INTO postavki VALUES('18/03/2015 10:30:00','30','2','3')

INSERT INTO postavki VALUES('18/03/2015 12:40:00','20','5','3')

INSERT INTO postavki VALUES('18/03/2015 17:00:00','15','2','5')

INSERT INTO postavki VALUES('20/03/2015 11:50:00','20','3','4')

INSERT INTO postavki VALUES('20/03/2015 14:00:00','10','3','3')

INSERT INTO postavki VALUES('23/03/2015 18:20:00','10','3','5')

INSERT INTO postavki VALUES('24/03/2015 14:30:00','30','4','2')

INSERT INTO postavki VALUES('24/03/2015 17:00:00','20','5','3')

INSERT INTO postavki VALUES('27/03/2015 16:00:00','10','4','3')

INSERT INTO postavki VALUES('27/03/2015 16:00:00','10','2','3')

Рисунок 6

Создадим несколько запросов по изменению данных с использованием условий по разным типам данных полей таблиц.

Изменим в таблице detali содержимое поля primechanie на «Нет сведений» если значение поля является пустым (Рисунок 7):

UPDATE 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):

SELECT name_postavchika, telefon, data FROM postavchiki a, postavki b WHERE a.code_postavchika=b.code_postavchika

Рисунок 20

Выберем названия деталей из таблицы detali, цена которых равна 300 или 700 (Рисунок 21):

SELECT 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):

SELECT 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');

INSERT INTO postavchiki VALUES('Hummer',NULL,'348956')

SELECT 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

Выведем названия поставщиков из таблицы postavchiki которые содержат в названии первую букву C или D, 2ой и 3ий символы являются любыми, четвертый символ V, а остальные символы произвольны (Рисунок 31):

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 detali

UNION

SELECT name_postavchika FROM postavchiki



Рисунок 37

Запрос на внутренне объеденение.

Выведем названия деталей, который поставлял нам поставщик 'Victor Reinz' (Рисунок 38):

SELECT A.nazvanie_detali FROM detali A

INNER JOIN postavki B ON B.code_detali=A.code_detali

INNER JOIN postavchiki C ON C.code_postavchika=B.code_postavchika

AND C.name_postavchika='Victor Reinz'

Рисунок 38

Запрос на внешнее левое объединение таблиц.

Выведем список деталей и их количества, а также детали, котгорые не поставлялись, если такие имеются (Рисунок 39):

SELECT A.nazvanie_detali, B.kolichestvo

FROM detali A

LEFT JOIN postavki B ON A.code_detali=B.code_detali



Рисунок 39

Внешнее правое объединение таблиц.

Выведем название поставщиков и количество деталей ими поставляемых. А также количество деталей, чьи поставщики не известны, если такие имеются (Рисунок 40):

SELECT A.name_postavchika, B.kolichestvo

FROM postavchiki A

RIGHT OUTER JOIN postavki B ON A.code_postavchika=B.code_postavchika

Рисунок 40

Запрос на полное внешнее объединение таблиц.

Выведем список поставщиков и количество деталей ими поставленные, включая детали у которых поставщик не известен, а также поставщиков, которые не поставляли деталей (Рисунок 41):

SELECT A.name_postavchika, B.kolichestvo

FROM postavchiki A

FULL OUTER 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):

SELECT D.name_postavchika, AVG(P.kolichestvo) AS 'Srednee kolichestvo'

FROM postavchiki D

INNER JOIN postavki P ON D.code_postavchika=P.code_postavchika

GROUP BY D.name_postavchika

HAVING AVG(P.kolichestvo)>15

Рисунок 43

Вывести поставщиков и количество поставляемых ими деталей, у которых цена больше 500 рублей (Рисунок 44):

SELECT P.name_postavchika, COUNT(B.nazvanie_detali) AS 'Kolichestvo'

FROM postavchiki P

INNER JOIN postavki D ON P.code_postavchika=D.code_postavchika

INNER JOIN detali B ON D.code_detali=B.code_detali

WHERE B.cena>500

GROUP BY P.name_postavchika

Рисунок 44

9. Выбор данных с помощью подзапроса

Выведем информацию о поставщике, с макимальным количеством поставленных деталей (Рисунок 45):

SELECT C.name_postavchika, C.adress, C.telefon

FROM postavchiki C

INNER JOIN postavki B ON C.code_postavchika=B.code_postavchika

WHERE B.kolichestvo=(SELECT MAX(kolichestvo) FROM postavki)

Рисунок 45

Определим поставщиков, у которых среднее количество поставляемых ими деталей больше среднего количества среди всех поставок (Рисунок 46):

SELECT D.name_postavchika FROM postavchiki D

INNER JOIN postavki P ON D.code_postavchika=P.code_postavchika

GROUP BY D.name_postavchika

HAVING AVG(P.kolichestvo)>(SELECT AVG(kolichestvo) FROM postavki)

Рисунок 46

Выберем поставщиков, количество поставляемых деталей которых больше 15 (Рисунок 47):

SELECT C.name_postavchika

FROM postavchiki C

WHERE code_postavchika IN

(SELECT code_postavchika FROM postavki WHERE kolichestvo>15)



Рисунок 47

Выберем поставщиков, количество поставляемых деталей которых не больше 15 (Рисунок 48):

SELECT C.name_postavchika

FROM postavchiki C

WHERE code_postavchika NOT IN

(SELECT code_postavchika FROM postavki WHERE kolichestvo >15)

Рисунок 48

Выведем детали, которые поставлялись (Рисунок 49):

SELECT nazvanie_detali FROM detali A

WHERE Exists (SELECT 1 FROM postavki B WHERE B.code_detali=A.code_detali)

Рисунок 49

Определим поставщиков, у которых среднее количество поставленных им деталей больше среднего количества среди всех поставок (Рисунок 50):

SELECT A.name_postavchika, B.kolichestvo

FROM postavki B

INNER JOIN postavchiki A ON B.code_postavchika=A.code_postavchika

WHERE B.kolichestvo>(SELECT AVG(B2.kolichestvo)FROM postavki B2

WHERE B2.code_postavchika=B.code_postavchika)

Рисунок 50

Определим количество поставок деталей, сгруппировав их по количеству поставляемых деталей, и исключим те детали, поставок которых было меньше 4 (Рисунок 51):

SELECT A.kolichestvo, COUNT(A.kolichestvo) AS[kol-vo postavok]

FROM postavki A

GROUP BY A.kolichestvo

HAVING 4>=

(SELECT COUNT (A2.code_postavki) FROM postavki A2

WHERE A2.kolichestvo=A.kolichestvo)

Рисунок 51

Создадим таблицу detal_kolichestvo c полями названия детали и количества. И заполним записями из таблиц detai, postavki (Рисунок 52):

CREATE TABLE detal_kolichestvo(nazvanie_detali VARCHAR(30), kolichestvo INT);

INSERT INTO detal_kolichestvo

SELECT A.nazvanie_detali, B.kolichestvo FROM detali A

INNER JOIN postavki B ON A.code_detali=B.code_detali

select*from detal_kolichestvo

Рисунок 52

Увеличим количество поставок на 2, которые имеют минимальное количество деталей (Рисунок 54):

UPDATE postavki

SET kolichestvo=kolichestvo+5

WHERE kolichestvo=(SELECT MIN(B.kolichestvo) FROM postavki B))

Рисунок 53. Исходная таблица

Рисунок 54

Удалим поставки с минимальным количеством деталей (Рисунок 55):

DELETE FROM postavki

WHERE code_postavki IN(

SELECT B1.code_postavki FROM postavki B1

WHERE B1.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)

DECLARE @cena FLOAT, @kolvo INT, @code INT

OPEN MyCursor6

FETCH FIRST FROM MyCursor6 INTO @cena, @kolvo, @code

WHILE @@FETCH_STATUS=0

BEGIN

FETCH NEXT FROM MyCursor6 INTO @cena, @kolvo, @code

PRINT @cena

PRINT @kolvo

END

DECLARE @sum_table FLOAT, @sum FLOAT, @str CHAR(30)

SET @sum_table=0

IF @code<=MAX(@code)

BEGIN

SET @sum=@cena*@kolvo

set @sum_table=@sum_table+@sum

END

ELSE

BEGIN

SET @str='Summa proizvedeni'+STR(@sum_table)

SELECT @str

END

PRINT @sum_table

CLOSE MyCursor6

DEALLOCATEMyCursor6

Рисунок 56

11. Создание хранимых процедур и функций

Создадим процедуру с входными параметрами.

Выведем дату поступления, название деталей и поставщиков по определенному коду поставщика (Рисунок 57):

ALTER PROCEDURE select_infa

@k CHAR(30)

AS

SELECT nazvanie_detali C

FROM detali C

INNER JOIN postavki B ON C.code_detali=B.code_detali

WHERE code_postavchika=@k

SELECT name_postavchika FROM postavchiki

WHERE code_postavchika=@k

SELECT data FROM postavki

WHERE code_postavchika=@k

GO

EXEC select_infa @k='3'

Рисунок 57

Создадим процедуру с использованием агрегатных функций.

Выведем количество деталей, чья цена больше определенного числа и примечание начинается на цифру 3 (Рисунок 58):

CREATE PROC count_detali3

@cc AS INT,

@a AS VARCHAR(10)

AS

SELECT COUNT(code_detali)

FROM detali

WHERE cena>=@cc

AND primechanie LIKE @a

GO

EXEC count_detali3 400, '3%'

Рисунок 58

Создать процедуру с входным и выходным параметрами.

Выведем количество деталей, чья цена больше определенного числа (Рисунок 59):

ALTER PROC count_detal_itogo

@c INT,

@itogo INT OUTPUT

AS

SELECT @itogo=COUNT(code_detali)

FROM detali

WHERE cena>=@c

GO

DECLARE @q AS INT EXEC count_detal_itogo 400,@q output

SELECT @q



Рисунок 59

Создадим процедуру с входным параметром по удалению значений одной из таблиц.

Удалим из таблицы postavchiki определенного поставщика (Рисунок 61):

CREATE PROC udalenie

@a CHAR(10)

AS

DELETE FROM postavchiki

WHERE name_postavchika=@a

GO

EXEC udalenie 'Chukamuka'

Рисунок 60. Исходная таблица

Рисунок 61

12. Изменение структуры

Согласно пункту «Развитие постановки задачи», в которой выяснилось, что цена детали может меняться от поставки к поставке, было решено дополнить текущую базу данных новой сущностью «Izmenenie_cen_po_date», которая позволит хранить не только текущее значение цены, но и всю историю изменения цен. Ключевым атрибутом будет являться code_detali, по которому данная сущность связывается с сущностью detail идентифицирующей связью. Атрибутами будут cena и data. Так как теперь цена не постоянная, удалим атрибут cena в сущности detali.

Таким образом, диаграмма сущность-связь будет выглядеть следующим образом (Рисунок 62):

Рисунок 62

Заключение

В ходе выполнения курсовой работы мы систематизировали, закрепили и проявили теоретические знания, развили и проявили навыков самостоятельного решения прикладных задач с применением информационных технологий, практической реализации автоматизированной обработки экономической информации. Также мы приобрели практические навыки по обследованию конкретной предметной области и разработке реляционных моделей баз данных экономического направления; научились применять прикладные программы общего назначения для решения экономико-управленческих задач.

Результатами выполнения курсового проекта явились:

1. Логическая и физическая модели базы данных информационно-аналитического центра коммерческого банка.

2. Реляционная модель базы данных банка, для отслеживания динамики кредитного отдела.

3. Разработанная с применением MS SQL Server 2008 база данных.

Список литературы

1.Базы данных. Методические указания к лабораторной работе №1: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 31 с.

2.Базы данных. Методические указания к лабораторной работе №2: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 33 с.

3.Базы данных. Методические указания к лабораторной работе №3: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 21 с.

4.Базы данных. Методические указания к лабораторной работе №4: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 26 с.

5.Базы данных. Методические указания к лабораторной работе №5: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 24 с.

6.Базы данных. Методические указания к лабораторной работе №6: / УГАТУ. Сост.: Е.Н. Прошин - Уфа, 2014. - 24 с.

Размещено на Allbest.ru