Электроснабжение цеха металлорежущих станков

Тип:
Добавлен:

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

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

Оглавление

Введение

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

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

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

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

.Создание базы данных в 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. Исходная таблица

Изменим адрес у поставщика с кодом 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

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

Рисунок 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)

Рисунок 48

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 база данных.

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

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

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

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

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

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

Copyright © 2018 WorldReferat.ru All rights reserved.