Оглавление
Введение…………………………………………………………………………2
Физическая модель базы данных………………………………………………4
Разработка запросов, процедур, триггеров……………………………….10-17
Заключение…………………………………………………………………......18
Список использованной литературы……………………………………........19
Введение
За последние 30 лет в области теории систем баз данных была проведен ряд исключительно продуктивных исследований. Полученные результаты вполне можно считать наиболее важным достижением информатики за этот период. Базы данных стали основой информационных систем и в корне изменили методы работы многих организаций. В частности, в последние годы развитие технологии баз данных привело к созданию весьма мощных и удобных в эксплуатации систем. Благодаря этому системы баз данных стали доступными широкому кругу пользователей. Но, к сожалению, кажущаяся простота таких систем способствовала тому, что пользователи стали самостоятельно создавать базы данных и приложения, не имея достаточных знаний о методах проектирования эффективно работающих систем, что часто приводило к непроизводительным затратам ресурсов и некачественным результатам. Вызванная этим неудовлетворенность пользователей стала причиной возникновения известного "кризиса программного обеспечения", или так называемой "депрессии программного обеспечения", последствия которой не устранены и поныне.
Реляционная база данных – это совокупность отношений, содержащих всю информацию, которая должна храниться в БД. Пользователи воспринимают такую базу данных как совокупность таблиц, связанных между собой.
Основными задачами системы SQL Server являются: организация одновременного доступа к данным большого количества пользователей; манипуляция информацией, хранимой в БД и др.
SQL Server поддерживает реляционную модель данных и выполняет функции создания объектов БД (таблиц, индексов, представлений и т.д.), осуществляет проверку целостности БД и отвечает за безопасность данных в системе.
Доступ пользователя к данным обычно осуществляется с компьютера рабочей станции. При этом создаются соответствующие приложения (например, в средах Visual Basic, Delphi и др.), которые позволяют выполнять операции над данными.
Задачи, связанные с администрированием БД системы SQL Server, обращение к системе удобно выполнять непосредственно с компьютера-сервера. В SQL Server для манипулирования данными используется язык Transact SQL, переработанной компанией Microsoft версией языка SQL.
Система SQL Server сохраняет создаваемые объекты в соответствующих файлах на дисках компьютера сервера. При этом для БД создаются специальные таблицы, в которых хранится информация о различных элементах базы данных: индексах, таблицах, пользователях и т.д.
Файлы БД сохраняются с расширением MDF, а системные файлы с расширением LDF.
Физическая модель базы данных
Создадим базу данных «Магазин продуктов»:
CREATE DATABASE Magazin_Productov
Затем создадим таблицы:
«Товар»
CREATE TABLE Tovap (codtovara int not null primary key, ekzemplar char(30) not null)
Таблица №1
| Атрибуты | Тип | Длина | Ключ | Описание |
| codtovara | int | primary | Код товара | |
| ekzemplar | varchar | Наименование товара |
«Поставщики»
CREATE TABLE Postavsiki (codpostavsika int not null primary key, firma varchar(20) not null)
Таблица №2
| Атрибуты | Тип | Длина | Ключ | Описание |
| codpostavsika | int | primary | Код поставщика | |
| firma | varchar | Наименование фирмы |
«Менеджеры поставок»
CREATE TABLE Manageri_postavok (cod_meng_postav int not null primary key, familia char(20) not null, imya char(10) not null, otchestvo char(20) not null, telefon int not null)
Таблица №3
| Атрибуты | Тип | Длина | Ключ | Описание |
| cod_meng_postav | int | primary | Код менеджера поставок | |
| familia | varchar | Фамилия менеджера поставок | ||
| imya | varchar | Имя менеджера поставок | ||
| otchestvo | varchar | Отчество менеджера поставок | ||
| telefon | int | Номер телефона менеджера поставок |
«Поставка товара»
CREATE TABLE Postavka_tovara (codpostavki int not null primary key, codtovara int not null references Tovap, codpostavsika int not null references Postavsiki, colich_post_tov int not null, cena_post_tov money not null, symmapostavki money not null, cod_meng_postav int not null references Manageri_postavok)
Таблица №4
| Атрибуты | Тип | Длина | Ключ | Описание |
| codpostavki | int | primary | Код поставки | |
| codtovara | int | Код товара | ||
| codpostavsika | int | Код поставщика | ||
| colich_post_tov | int | Количество поставляемого товара | ||
| cena_post_tov | money | Цена поставляемого товара | ||
| symmapostavki | money | Сумма поставки | ||
| cod_meng_postav | int | Код менеджера поставки |
«Продавцы»
CREATE TABLE Prodavci (codprodavca int not null primary key, familia varchar(20) not null, imya varchar(10) not null, otchestvo varchar(20) not null, telefon int not null)
Таблица №5
| Атрибуты | Тип | Длина | Ключ | Описание |
| codprodavca | int | primary | Код продавца | |
| familia | varchar | Фамилия продавца | ||
| imya | varchar | Имя продавца | ||
| otchestvo | varchar | Отчество продавца | ||
| telefon | int | Номер телефона продавца |
«Продажа товара»
CREATE TABLE Prodaza_tovara (cod_vibitogo_cheka int not null primary key, codtovara int not null references Tovap, colich_prod_tov int not null, cena_prod_tov money not null, dohod money not null, codprodavca int not null references Prodavci)
Таблица №6
| Атрибуты | Тип | Длина | Ключ | Описание |
| cod_vibitogo_cheka | int | primary | Код выбитого чека | |
| codtovara | int | Код товара | ||
| CodFirmaClient | int | Код фирмы-клиента | ||
| colich_prod_tov | int | Количество проданного товара | ||
| cena_prod_tov | money | Цена проданного товара | ||
| dohod | money | Доход | ||
| codprodavca | int | Код продавца |
Заполним таблицы следующими SQL запросами:
Таблица «Товар»
INSERT INTO Tovap values (1, 'Сыр копченый')
INSERT INTO Tovap values (2, 'Молоко')
INSERT INTO Tovap values (3, 'Творог')
INSERT INTO Tovap values (4, 'Сметана')
INSERT INTO Tovap values (5, 'Йогурт')
INSERT INTO Tovap values (6, 'Сыр плавленый')
INSERT INTO Tovap values (7, 'Кефир')
INSERT INTO Tovap values (8, 'Ряженка')
INSERT INTO Tovap values (9, 'Сыр российский')
INSERT INTO Tovap values (10, 'Снежок')
Рис. 1. Таблица «Товар».
«Поставщики»
INSERT INTO Postavsiki values (11, 'ОАО "Молоко"')
INSERT INTO Postavsiki values (12, 'ОАО "МолПрод"')
INSERT INTO Postavsiki values (13, 'ОАО "Снежинка"')
INSERT INTO Postavsiki values (14, 'ОАО "Меркурий+"')
INSERT INTO Postavsiki values (15, 'ОАО "Молочный дом"')
Рис. 2. Таблица «Поставщики».
«Менеджеры поставок»
INSERT INTO Manageri_postavok values (21, 'Банин', 'Александр', 'Абрамович', 22431)
INSERT INTO Manageri_postavok values (22, 'Сидорова', 'Раиса', 'Николаевна', 22543)
INSERT INTO Manageri_postavok values (23, 'Викенов', 'Дмитрий', 'Александрович', 22564)
INSERT INTO Manageri_postavok values (24, 'Воробьев', 'Николай', 'Иванович', 22468)
INSERT INTO Manageri_postavok values (25, 'Николаева', 'Юлия', 'Михайловна', 22541)
Рис. 3. Таблица «Менеджеры поставок».
«Поставка товара»
INSERT INTO Postavka_tovara values (31, 1, 15, 210, 190, 0, 22)
INSERT INTO Postavka_tovara values (32, 2, 12, 280, 25, 0, 25)
INSERT INTO Postavka_tovara values (33, 3, 14, 190, 80, 0, 23)
INSERT INTO Postavka_tovara values (34, 4, 13, 200, 40, 0, 21)
INSERT INTO Postavka_tovara values (35, 5, 11, 270, 30, 0, 21)
INSERT INTO Postavka_tovara values (36, 6, 13, 220, 15, 0, 23)
INSERT INTO Postavka_tovara values (37, 7, 11, 170, 17, 0, 24)
INSERT INTO Postavka_tovara values (38, 8, 14, 200, 16, 0, 25)
INSERT INTO Postavka_tovara values (39, 9, 12, 160, 165, 0, 24)
INSERT INTO Postavka_tovara values (40, 10, 13, 240, 20, 0, 22)
Рис. 4. Таблица «Поставка товара».
«Продавцы»
INSERT INTO Prodavci values (41, 'Краснов', 'Алексей', 'Петрович', 22235)
INSERT INTO Prodavci values (42, 'Вдовин', 'Андрей', 'Константинович', 22371)
INSERT INTO Prodavci values (43, 'Михайлова', 'Ольга', 'Ивановна', 22364)
INSERT INTO Prodavci values (44, 'Демидова', 'Анна', 'Викторовна', 22216)
INSERT INTO Prodavci values (45, 'Семенов', 'Игорь', 'Алексеевич', 22353)
Рис. 5. Таблица «Продавцы».
«Продажа товара»
INSERT INTO Prodaza_tovara values (51, 1, 177, 210, 0, 44)
INSERT INTO Prodaza_tovara values (52, 2, 265, 30, 0, 45)
INSERT INTO Prodaza_tovara values (53, 3, 146, 100, 0, 43)
INSERT INTO Prodaza_tovara values (54, 4, 180, 60, 0, 45)
INSERT INTO Prodaza_tovara values (55, 5, 119, 40, 0, 41)
INSERT INTO Prodaza_tovara values (56, 6, 110, 25, 0, 43)
INSERT INTO Prodaza_tovara values (57, 7, 170, 30, 0, 42)
INSERT INTO Prodaza_tovara values (58, 8, 190, 27, 0, 43)
INSERT INTO Prodaza_tovara values (59, 9, 150, 180, 0, 44)
INSERT INTO Prodaza_tovara values (60, 10, 120, 30, 0, 41)
Рис. 6. Таблица «Продажа товара».
Создадим представления:
«Поставка»
CREATE VIEW Postavka AS SELECT Postavka_tovara.codpostavki, Tovap.ekzemplar, Postavsiki.firma,Postavka_tovara.colich_post_tov, Postavka_tovara.cena_post_tov, Postavka_tovara.symmapostavki, Manageri_postavok.familia, Manageri_postavok.imya FROM Postavka_tovara INNER JOIN Postavsiki ON Postavka_tovara.codpostavsika = Postavsiki.codpostavsika INNER JOIN Tovap ON Postavka_tovara.codtovara = Tovap.codtovara INNER JOIN Manageri_postavok ON Manageri_postavok.cod_meng_postav = Postavka_tovara.cod_meng_postav
Рис. 7. Представление «Поставка».
«Продажа»
CREATE VIEW Prodaza AS SELECT Prodaza_tovara.cod_vibitogo_cheka, Tovap.ekzemplar, Prodaza_tovara.colich_prod_tov, Prodavci.familia, Prodavci.imya, Prodavci.telefon FROM Prodaza_tovara INNER JOIN Tovap ON Prodaza_tovara.codtovara = Tovap.codtovara INNER JOIN Prodavci ON Prodaza_tovara.codprodavca = Prodavci.codprodavca
Рис. 8. Представление «Продажа».
Разработка запросов, процедур, триггеров
Процедура Symmapostavki (без параметра):
CREATE PROC Symmapostavki AS UPDATE Postavka SET Symmapostavki = colich_post_tov * cena_post_tov
EXEC Symmapostavki SELECT * FROM Postavka
Рис. 9. Процедура «Сумма поставки».
Процедура Izmenenie (с параметром):
CREATE PROC Izmenenie (@kod int) AS UPDATE Postavka_tovara SET symmapostavki = symmapostavki * 10, colich_post_tov = colich_post_tov * 10 WHERE codpostavki = @kod
EXEC Izmenenie @kod = 35 SELECT * FROM Postavka_tovara
Рис. 10. Процедура «Изменение».
Процедура Dohod:
CREATE PROC Dohod AS UPDATE Prodaza_tovara SET dohod = colich_prod_tov * cena_prod_tov
EXEC Dohod SELECT * FROM Prodaza_tovara
Рис. 11. Процедура «Доход».
Создадим несколько запросов:
1. Товар, выбранный по коду в диапазоне от 4 до 8
SELECT * FROM Tovap WHERE codtovara BETWEEN 4 AND 8
Рис. 12. Запрос 1.
2. Товар, выбранный по коду из указанного множества
SELECT * FROM Tovap WHERE codtovara IN (2, 5, 6, 11, 12, 4)
Рис. 13. Запрос 2.
3. Выбор записей из таблицы, являющейся соединением двух таблиц
SELECT Tovap.ekzemplar, Prodaza_tovara.colich_prod_tov, Prodaza_tovara.cena_prod_tov, Prodaza_tovara.Dohod FROM Tovap, Prodaza_tovara where Tovap.codtovara = Prodaza_tovara.codtovara
Рис. 14. Запрос 3.
4. Подсчет количества выбитых чеков
SELECT COUNT (*) AS 'Количество выбитых чеков' FROM Prodaza_tovara
Рис. 15. Запрос 4.
5. Минимальное количество поставленного товара
SELECT MIN (colich_post_tov) AS 'Минимальное количество поставленного товара' FROM Postavka_tovara
Рис. 16. Запрос 5.
6. Наименования товара по алфавиту
SELECT * FROM Tovap ORDER BY ekzemplar ASC
Рис. 17. Запрос 6.
7. Количество проданного товара ниже среднего количества продаж
SELECT * FROM Prodaza WHERE colich_prod_tov < (SELECT AVG (colich_prod_tov) FROM Prodaza)
Рис. 18. Запрос 7.
8. Выбор менеджеров поставок по коду в диапазоне между 21 и 24
SELECT * FROM Manageri_postavok WHERE cod_meng_postav > 21 AND cod_meng_postav < 24
Рис. 19. Запрос 8.
Создадим триггер, который удаляет запись в таблице «Поставка товара», соответствующую одиннадцатому коду товара.
CREATE TRIGGER Udalen ON Tovap FOR DELETE AS IF @@rowcount = 1 BEGIN DECLARE @x int SELECT @x = Tovap.codtovara FROM Tovap DELETE Postavka_tovara.codtovara WHERE Tovap.codtovara = Postavka_tovara.codtovara IF EXISTS (SELECT * FROM Postavka_tovara WHERE codtovara = @x) DELETE FROM Postavka_tovara WHERE codtovara = 11 END
Рис. 20.
Рис. 21. Диаграмма
Заключение
В данном курсовом проекте с помощью СУБД Microsoft SQL Server было рассмотрено создание шести сущностей с определенными атрибутами, представлений, основанных на соединении столбцов нескольких таблиц; создание связей между таблицами, а также создание процедур, запросов и диаграммы.
Список использованной литературы
1. Методические указания к курсовому проектированию.
2. Лекции по базам данных.