Автор: Пользователь скрыл имя, 15 Сентября 2013 в 10:21, курсовая работа
Целью курсового проектирования является применение на практике знаний, полученных в процессе изучения курса "Базы данных" и получение практических навыков проектирования и реализации баз данных, для последующего создания информационных систем.
В курсовой работе будет разработана базы данных по теме «Автотехцентр», которая занимается ремонтом автомобилей и реализована в виде программного продукта, также будут получены навыки по моделированию структуры баз и созданию средствами языка SQL.
Введение…………………………………………………………………………4
1 Проектирование базы данных……………………………………………..5
1.1 Описание предметной области………………………………………...5
1.2 Инфологическая модель базы данных………………………………...6
1.3 Даталогическая модель базы данных………………………………….7
1.4 Стратегия резервного копирования и восстановления……………….9
2 Реализация базы данных…………………………………………………12
2.1 Характеристика используемой СУБД………………………………...12
2.2 Создание структуры базы данных……………………………………15
2.2.1 База данных и таблицы…………………………………………..15
2.2.2 Создание индексов……………………………………………….19
2.3 Создание представлений……………………………………………....21
2.3.1 Однотабличный запрос на выборку по условиям……………...21
2.3.2 Многотабличный запрос………………………………………....22
2.3.3 Запрос с использованием подзапроса.............................................22
2.3.4 Итоговый запрос……………………………………………….....23
2.4 Примеры запросов на модификацию данных………………………..24
2.4.1 Простые запросы…………………………………………………24
2.4.2 Запросы с использованием подзапросов………………………..26
2.5 Описание триггеров…………………………………………………....28
2.6 Создание пользователей и назначение привилегий………………….32
Заключение………………………………………………………………….37
Список использованной литературы……………………………………...38
Приложение 1. Пример инфологической модели………………………...39
•Некластерные индексы. В отличие от кластерных, они не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки.
Первичные ключи относятся к кластерным индексам и являются уникальными.
Теперь создадим два некластеризованных индекса для таблиц Клиенты и Специалисты по столбцу Фамилия. Элементы этих индексов будут располагаться по возрастанию. Эти индексы создаются вручную при помощи запроса.
Создание индекса index_clients для таблицы Клиенты:
CREATE UNIQUE NONCLUSTERED INDEX index_clients
ON Клиенты(Фамилия asc)
Содание индекса index_spec для таблицы Специалисты:
CREATE UNIQUE NONCLUSTERED INDEX index_spec
ON Специалисты(Фамилия asc)
Данные индексы являются уникальными.
Представления, или просмотры (VIEW), представляет собой временные, производные (виртуальные) таблицы и являются объектами базы данных, информация в которых не хранится постоянно, как в базовых таблицах, а формируется динамически при обращении к ним.
Представление – это предопределенный запрос, хранящийся в базе данных, который выглядит подобно обычной таблице и не требует для своего хранения дисковой памяти.
В данном разделе приводятся текст запроса, SQL-сценарии для создания представлений и результаты их работы в форме таблицы (или рисунка).
Данный запрос должен выполнять выборку информации из одной таблицы, ограничивая набор записей определенными условиями.
Для базы данных «Клиенты» был создан однотабличный запрос, который осуществляет выборку всех клиентов из таблицы «Клиенты», которые имеют марку машины «Audi» и проживающих по улице «Лениградская». Данный запрос был записан в представление view1.
Текст запроса выглядит следующим образом:
CREATE VIEW view1 AS
SELECT * FROM Клиенты
WHERE Марка_машины like '%Audi%' and Адрес like '%Ленинградская%'
Результат запроса показан на рисунке 2.3.1.1.
Рисунок 2.3.1.1 – Представление view1
Данный запрос должен выполнять выборку информации из нескольких таблиц.
Для базы данных «Договор» был создан многотабличный запрос, который осуществляет выборку всех клиентов из таблицы «Клиенты», чье транспортное средство нуждается в покраске. Выборка данных осуществляется также из таблицы «Договор», так как она является связующей между таблицами «Клиенты» и «Ремонт». Данный запрос был записан в представление view2.
Текст запроса выглядит следующим образом:
CREATE VIEW view2 AS
SELECT Клиенты.Фамилия, Клиенты.Имя, Клиенты.Отчество,
Клиенты.Марка_машины, Ремонт.Название AS [Вид технического обслуживания]
FROM Клиенты, Договор, Ремонт
WHERE Ремонт.Название like '%Покраска%' and
Договор.Код_клиента=Клиенты.
Договор.Код_ремонта=Ремонт.
Результат запроса показан на рисунке 2.3.2.1.
Рисунок 2.3.2.1 – Представление view2.
Данный запрос должен выполнять выборку информации из одной или нескольких таблиц, используя вложенный запрос.
Для базы данных «Договор» был создан запрос с использованием подзапроса, который показывает данные о клиентах, у которых сумма за ремонт больше 2000 . Данный запрос был записан в представление view3. Текст запроса выглядит следующим образом:
CREATE VIEW view3 AS
SELECT Ремонт.Название, Специалисты.Фамилия AS Специалисты, Клиенты.Фамилия AS Клиенты,
Договор.Дата_ремонта, Ремонт.Стоимость_ремонта
FROM Ремонт, Специалисты, Клиенты, Договор
WHERE Клиенты.Код_клиента=Договор.
Специалисты.Код_специалиста=
Ремонт.Код_ремонта=Договор.
And Ремонт.Названиеin ((SELECT Ремонт.Название FROM
Ремонт,Договор
WHERE Ремонт.Код_ремонта=Договор.
And Ремонт.Стоимость_ремонта>'
Результат запроса показан на рисунке 2.3.3.1.
Рисунок 2.3.3.1 – Представление view3.
Данный запрос должен выполнять выборку информации из одной или нескольких таблиц, выполняя группировку записей по одному из полей и вычисляя итоговые значения с использованием агрегатных функций по другим полям.
Для базы данных «Договор» был создан итоговый запрос, который показывает количество клиентов у каждого специалиста. В данном запросе было создано вычисляемое поле Количество_клиентов при помощи выражения COUNT. Данный запрос был записан в представление view4. Текст запроса выглядит следующим образом:
CREATE VIEW view4 AS
SELECT Специалисты.Фамилия,
Специалисты.Отчество,
COUNT (Договор.Код_клиента) AS Количество_клиентов
FROM Договор, Специалисты
WHERE Специалисты.Код_специалиста=
GROUP BY Специалисты.Фамилия,
Результат запроса показан на рисунке 2.3.4.1.
Рисунок 2.3.4.1 – Представление view4.
В данном разделе приводятся тексты простых однотабличных запросов, используемых для выполнения операций вставки, обновления и удаления данных: INSERT,DELETE и UPDATE.
Операция вставки INSERT вставляет новые строки в таблицу. При этом значения столбцов могут представлять собой константы либо являться результатом выполнения подзапроса. Был создан запрос, осуществляющий вставку записи в таблицу «Специалисты». Текст запроса выглядит следующим образом:
INSERTINTO Специалисты (Фамилия, Имя, Отчество, Должность)
VALUES ('Гончаров', 'Павел', 'Алексеевич', 'Механик')
Из запроса видно, что была вставлена запись со следующими полями: Фамилия – Гончаров, Имя – Павел, Отчество – Алексеевич, Должность –Механик.
При вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отвергнута.
Операция удаления DELETE. Был создан запрос, который удаляет клиента из таблицы Клиенты, имеющих фамилию «Акулов». Текст запроса выглядит следующим образом:
DELETE FROM Клиенты
WHERE Фамилия='Акулов'
Если предложение WHERE присутствует, удаляются записи из таблицы, удовлетворяющие условию отбора. Если опустить предложение WHERE, из таблицы будут удалены все записи, однако сама таблица сохранится.
Операция обновления UPDATE. Данный оператор применяется для изменения значений в группе записей или в одной записи указанной таблицы. Был создан запрос, который изменяет дату начала ремонта «12/12/19» на «12/12/21». Текст запроса выглядит следующим образом:
UPDATE Договор
SET Дата_ремонта='12/12/21'
WHERE Дата_ремонта='12/12/19'
Предложение WHERE является необязательным. Если оно опущено, значения указанных столбцов будут изменены во всех строках таблицы. Если предложение WHERE присутствует, то обновлены будут только те строки, которые удовлетворяют условию отбора.
В данном разделе приводятся тексты запросов на модификацию данных, в теле которых присутствуют подзапросы для излечения данных.
Был создан запрос с использованием подзапроса и оператора обновления данных UPDATE, который осуществляет замену названий документов, которые не были подписаны на название «Неподписанный документ». Текст запроса выглядит следующим образом:
UPDATE Ремонт
SET Название='Корректировка развал-схождения'
WHERE Название in
(SELECT Ремонт.Название
FROM Ремонт,Договор
WHERE Ремонт.Код_ремонта=Договор.
Ремонт.Название='Регулировка развал-схождения')
Также был создан запрос с использованием подзапроса и оператора удаления DELETE. Данный запрос выполняет удаление записей, которые были созданы ранее 1 декабря 2012 года.
Текст запроса выглядит следующим образом:
DELETE FROM Договор
WHERE Дата_ремонтаin
(SELECT Дата_ремонта
FROM Договор
WHERE Дата_ремонта< '12/12/01')
2.5 Описание триггеров
Триггер – это
откомпилированная SQL-
С помощью ограничений и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность.
На данном этапе для базы данных «Автотехцентр» были реализованы определенные ограничения посредством триггеров.
Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с ограничениями, стандартными значениями.
С помощью триггеров достигаются следующие цели:
•проверка корректности
введенных данных и выполнение сложных
ограничений целостности
•выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновления таблицы, реализованном определенным образом;
•накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;
Типология триггеров:
Для базы данных было разработано три триггера: два на удаление (DELETE), на вставку (INSERT).
Для базы данных «Автотехцентр» было создано два триггера на удаление.
При попытке
удалить определенного
Текст создания двух триггеров на удаление будет выглядеть следующим образом:
1) CREATE TRIGGER Триггер_del1
ON Специалисты INSTEAD OF DELETE
AS
IF @@ROWCOUNT=1
BEGIN
DECLARE @y INT
SELECT @y=Код_специалиста
FROM deleted
DELETE Договор
WHERE Договор.Код_специалиста=@y
DELETE Специалисты
WHERE Специалисты.Код_специалиста=@y
END
2) CREATE TRIGGER Триггер_del2
ON Клиенты INSTEAD OF DELETE
AS
IF @@ROWCOUNT=1
BEGIN
DECLARE @y INT
SELECT @y=Код_клиента
FROM deleted
DELETE Договор
WHERE Договор.Код_клиента=@y
DELETE Клиенты
WHERE Клиенты.Код_клиента=@y
END
Эти триггеры работают следующим образом. Когда мы удаляем запись из таблиц «Специалситы» или «Клиенты», то удаляются записи из таблицы из таблицы «Договор» с соответствующими значениями полей Код_специалиста или Код_клиента.