База данных "Автотехцентр"

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

1. Проектирование базы данных

1.1 Описание предметной области

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

В данной базе данных будут храниться следующие таблицы:

1) Клиенты (Код_клиента, Фамилия, Имя, Отчество, Марка_машины, Телефон, Адрес)

2) Специалисты (Код_специалиста, Фамилия, Имя, Отчество, Должность)

3) Ремонт (Код_ремонта, код_договора, Стоимость_ремонта)

4) Договор (Код_договора, Код_специалиста, Код_клиента, Код_ремонта, Дата_ремонта, Время_ремонта, Стоимость_ремонта)

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

База данных будет выполнять следующие функции:

1) Хранение информации о клиентах;

2) Хранение информации о специалистах;

3) Учет, выполняемых работ.

1.2 Инфологическая модель базы данных

Инфологическая модель базы данных представляет собой описание объектов (сущностей), с набором атрибутов и связей между ними, которые выявляются в процессе исследования как входных, так и выходных данных. Она предназначается для структурного образования предметной области, с ориентированием на информационное внимание пользователей, разрабатываемой системы. Самая распространенная модель в инфологическом моделировании это модель «сущность-связь», к главным компонентам её относятся - сущности и связи. Модель сущность-связь (ER-модель) (англ. entity-relationshipmodel, ERM) - модель данных, позволяющая описывать концептуальные схемы предметной области. ER-модель используется при высокоуровневом (концептуальном) проектировании баз данных. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями.

Выделим основные сущности:

· сущность «Клиенты»;

· сущность «Специалисты»;

· сущность «Договор»;

· сущность «Ремонт»;

Сущность «Клиенты» содержит информацию обо всех клиентах. Отдельный экземпляр этой сущности содержит информацию о каждом конкретном клиенте. Договора заключаются клиентом, поэтому вводится сущность «Договор». Каждый экземпляр сущности «Договор» содержит информацию о конкретном договоре. Каждым клиентом может быть оформлено несколько договоров. Сущность «Специалисты» содержит информацию о людях, которые осуществляют ремонт попредоставлении договора. Каждому специалисту может быть предоставлено несколько договоров, но у каждого договора только один специалист. Специалист является ответственным за ремонт машины. У каждого ремонта один ответственный. В каждом договоре прописывается один ремонт, и каждый ремонт выполняется по одному договору.

Связи с этим имеются следующие связи:

· Договор - клиент: «один-ко-многим»;

· Клиент - специалист: «один-ко-многим»;

· Договор - ремонт: «один-к-одному»

Определяются ключи - уникальные идентификаторы экземпляров каждой сущности: для сущности «Клиенты» - это Код клиента, для сущности «Специалиста» - Код специалиста, для сущности «Договор» - Код договора, для сущности «Ремонт» - Код ремонта.

1.3 Даталогическая модель базы данных

Даталогическое проектирование является проектированием логической структуры базы данных, на него оказывают влияние возможности физической организации данных, предоставляемые конкретной СУБД.

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

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

1) Клиенты (Код_клиента, Фамилия, Имя, Отчество, Марка_машины, Телефон, Адрес) (рисунок 1.3.1);



Рисунок 1.3.1 - Таблица «Клиенты»

2) Специалисты (Код_специалиста, Фамилия, Имя, Отчество, Должность) (рисунок 1.3.2);

Рисунок 1.3.2 - Таблица «Специалисты»

3) Ремонт (Код_ремонта, код_договора, Стоимость_ремонта) (рисунок 1.3.3);

Рисунок 1.3.3 - Таблица «Ремонт»

4) Договор (Код_договора, Код_специалиста, Код_клиента, Код_ремонта, Дата_ремонта, Время_ремонта, Стоимость_ремонта) (рисунок 1.3.4)



Рисунок 1.3.4 - Таблица «Договор»

1.4 Стратегия резервного копирование и восстановления

Резервное копирование - важнейшая задача системного администратора баз данных. Резервное копирование и восстановление - две неразрывно связанные задачи. Резервное копирование базы данных является одной из наиболее важных задач администратора баз данных (DBA - Databaseadministrator). Имея файлы резервной копии и тщательно планируя восстановление после аварии, DBA может восстанавливать систему в случае отказа. DBA несет ответственность за поддержку системы в работоспособном состоянии, насколько это возможно, и за максимально быстрое восстановление ее работы в случае отказа системы. Простой системы может доставлять неудобства и приносить большие убытки. Поэтому важно как можно быстрее восстановить базу данных и вернуть ее к работе. Здесь может компонент резервного копирования и восстановления SQL Server.

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

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

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

Для проектируемой базы данных будем использовать простую модель восстановления. Данная модель будет включать только полные резервные копии. Копию будем создавать на дисковом устройстве. Создание полной резервной копии проектируемой базы данных через запрос будет выглядеть так:

USE master;

GO

BACKUP DATABASE Автотехцентр

TO DISK='D:\backup\Автотехцентр.bak';

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

Далее зададим модель восстановления, используя запрос и конструкцию ALTER DATABASE. Получаем:

USE master;

GO

ALTER DATABASE Автотехцентр

SET RECOVERY SIMPLE;

GO

Чтобы просмотреть заданную для базы данных модель восстановления, можно использовать функцию DATABASEPROPERTYEX, которая извлекает параметры текущей базы даты или свойства указанной базы данных.

SELECT DATABASEPROPERTYEX ('Автотехцентр', 'Recovery').

2. Реализация базы данных

2.1 Характеристика используемой СУБД

Основные идеи современной информационной технологии базируются на концепции, согласно которой данные должны быть организованы в базы данных с целью адекватного отображения изменяющегося реального мира и удовлетворения информационных потребностей пользователей. Эти базы данных создаются и функционируют под управлением специальных программных комплексов, называемых системами управления базами данных (СУБД).

Для разработки проектируемой базы данных будем использовать Microsoft SQL Server 2008 r2. Основным используемым языком является язык запросов - Transact-SQL. SQL (StructuredQueryLanguage - «язык структурированных запросов») - универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных.

Функции, которые обеспечивает язык запросов SQL:

- определение, переопределение и удаление таблиц базы данных и других ее объектов (доменов, представлений, индексов, триггеров, сохраненных процедур, функций и т.д.);

- указание физической организации данных;

- поддержка ограничений целостности и непротиворечивости базы данных;

- защита данных от несанкционированного доступа с помощью определения пользователей (с именами и паролями) и ролей, прав доступа к данным и правам на смену стана базы данных;

- манипулирование данными в таблицах базы, включая вставку, изменение и удаление значений;

- поиск данных в нескольких таблицах и упорядочение полученных результатов;

- организация резервного копирования и восстановления базы данных;

- поддержка целостности транзакций;

- поддержка пользовательских процедур и функций, которые расширяют функциональные возможности SQL.

SQL является непроцедурным языком и не содержит операторов управления, организации подпрограмм, ввода-вывода и т.п. В связи с этим SQL автономно не используется, обычно он погружен в среду встроенного языка программирования СУБД.

В современных СУБД с интерактивным интерфейсом можно создавать запросы. Применение SQL зачастую позволяет повысить эффективность обработки данных в базе. Подготовку нового запроса путем редактирования уже имеющегося в ряде случае проще выполнить путем изменения оператора SQL. В различных СУБД состав операторов SQL может несколько отличаться.

Язык SQL не обладает функциями полноценного языка разработки, а ориентирован на доступ к данным, поэтому его включают в состав средств разработки программ. В этом случае его называют встроенным SQL. Стандарт языка SQL поддерживают современные реализации следующих языков программирования: PL/1, Ada, С, COBOL, Fortran, MUMPS и Pascal.

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

Microsoft SQL Server 2008 r2 - это мощная и надежная система управления данными, обеспечивающая множество функций, защиту данных и высокую производительность для внедренных приложений-клиентов, «легких» веб-приложений и локальных хранилищ данных. SQL Server 2008 r2 предназначен для упрощенного развертывания и быстрого создания прототипов; его можно получить бесплатно и свободно распространять вместе с приложениями. Он разработан таким образом, чтобы полностью интегрироваться с другими продуктами, входящими в серверную инфраструктуру.

Имеются две формы SQL: Интерактивная и Вложенная. Большей частью, обе формы работают одинаково, но используются различно. Интерактивный SQL используется для функционирования непосредственно в базе данных, чтобы производить вывод для использования его заказчиком. В этой форме SQL, когда вы введете команду, она сейчас же выполнится, и вы сможете увидеть вывод (если он вообще получится) - немедленно. Вложенный SQL состоит из команд SQL помещенных внутри программ, которые обычно написаны на некотором другом языке (типа КОБОЛА или Паскаля). Это делает эти программы более мощными и эффективным. Однако, допуская эти языки, приходится иметь дело со структурой SQL и стилем управления данных, который требует некоторых расширений к интерактивному SQL. Передача SQL команд во вложенный SQL является выдаваемой («passedoff») для переменных или параметров используемых программой, в которую они были вложены.

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

Основные характеристики:

· многопользовательская поддержка;

· многоплатформность;

· поддержка 64-разрядной архитектуры;

· масштабируемость (многопроцессорная обработка и поддержка терабайтных БД

· стандарт SQL92 (язык Transact SQL);

· параллельные архивирование и восстановление БД;

· репликация данных;

· распределенные запросы;

· распределенные транзакции;

· динамические блокировки;

· интеграция с IIS и InterDev.

В состав Microsoft SQL Server входят программы, позволяющие следить за текущим состоянием базы данных и измерять производительность, если это состояние меняется. Для наблюдения за Microsoft SQL Server можно использовать целый ряд инструментов и методик. Поняв способы мониторинга SQL Server, можно:

· Определять возможности увеличения производительности. Например, выполняя мониторинг времени ответа для часто используемых запросов, можно определить, требуется ли изменить текст запроса или индексы таблицы.

· Оценивать активность пользователей. Например, выполняя мониторинг пользователей, которые подключаются к экземпляру SQL Server, можно определить, правильно ли настроены параметры безопасности, и проверить работу приложений и систем разработки. Контролируя выполнение SQL-запросов, можно определить, правильно ли они написаны, и проверить результаты, которые они возвращают.

· Устранять любые проблемы или отлаживать компоненты приложений, например хранимые процедуры.

2.2 Создание структуры базы данных

2.2.1 База данных и таблицы

В этом разделе описывается создание базы данных в SQL Server 2008 r2 с помощью среды SQL ServerManagementStudio или Transact-SQL.

База данных создается в два этапа:

1) организация самой базы данных (*.mdf);

2) организация принадлежащего ей журнала транзакций (*.ldf).

Оператор, отвечающий за создание базы данных в системе SQL-сервер называется CREATE DATABASE:

CREATE DATABASE Автотехцентр

ON PRIMARY (NAME=Avt1, FILENAME= «C:\Автотехцентр\avt1.mdf»,

SIZE=100MB, MAXSIZE=200MB, FILEGROWTH=20),

(NAME = Avt2, FILENAME = «C:\Автотехцентр\avt2.mdf»,

SIZE=100MB, MAXSIZE=200MB, FILEGROWTH =20)

LOG ON

(NAME = Avtlog1, FILENAME = «C:\Автотехцентр\avtlog1.ldf»,

SIZE=100MB, MAXSIZE=200MB, FILEGROWTH =20),

(NAME= Avtlog2, FILENAME = «C:\Автотехцентр\avtlog2.ldf»,

SIZE=100MB, MAXSIZE=200MB, FILEGROWTH =20)

Подробно рассмотрев запрос, увидим, что имя проектируемой базы данных будет Автотехцентр. ON - определяет список файлов на диске для размещения информации, хранящейся в базе данных. PRIMARY - определяет первичный файл. LOGON - определяет список файлов на диске для размещения журнала транзакций. В параметрах NAME указывается логическое имя файла, в FILENAME - физическое имя файла, в SIZE - размер файла, в MAXSIZE - максимальный размер файла, в FILEGROWTH - величина прироста.

Далее создаем таблицы базы данных, в данной БД 4 таблицы.

1) Таблица «Клиенты»:

CREATE TABLE Клиенты

(Код_клиента int NOT NULL,

Фамилия varchar(50) NOT NULL,

Имя varchar(50) NOT NULL,

Отчество varchar(50) NOT NULL,

Марка_машины varchar(50) NOT NULL,

Телефон varchar(50) NOT NULL,

Адрес varchar(50) NOT NULL)

Далее добавляем записи в таблицу: Оператор INSERT вставляет новые строки в существующую таблицу. Форма данной команды INSERT… VALUES вставляет строки в соответствии с точно указанными в команде значениями.

Получим:

Insertinto Клиенты (Фамилия, Имя, Отчество, Марка_машины, Телефон, Адрес)

Values ('Потапов', 'Алексей', 'Олегович', 'BMW X6', '23-16-71', 'Петровская 5-14')

….

Insertinto Клиенты (Фамилия, Имя, Отчество, Марка_машины, Телефон, Адрес)

Values ('Акулов', 'Максим', 'Сергеевич', 'VolkswagenPoloSedan', '45-23-67', 'Магистральная 4-3')

После добавления записей получили таблицу, представленную на рисунке 2.2.1.1.



Рисунок 2.2.1.1 - Таблица «Клиенты»

2) Таблица «Специалисты»:

CREATE TABLE Специалисты

(Код_специалистаint NOT NULL,

Фамилияvarchar(50) NOT NULL,

Имяvarchar(50) NOT NULL,

Отчествоvarchar(50) NOT NULL,

Должностьvarchar(50) NOT NULL)

После добавления записей получили таблицу, представленную на рисунке 2.2.1.2.

Рисунок 2.2.1.2 - Таблица «Специалисты»

3) Таблица «Ремонт»:

CREATE TABLE Ремонт

(Код_ремонтаint NOT NULL,

Название varchar(50) NOT NULL,

Стоимость_ремонтаvarchar(10) NOT NULL)

После добавления записей получили таблицу, представленную на рисунке 2.2.1.3.

Рисунок 2.2.1.3 - таблица «Ремонт»

4) Таблица «Договор»:

CREATE TABLE Договор

(Код_договораint NOT NULL,

Код_специалистаint NOT NULL,

Код_клиентаint NOT NULL,

Код_ремонтаint NOT NULL,

Дата_ремонтаdatetime NOT NULL,

Время_ремонтаvarchar(20) NOT NULL)

После добавления записей получили таблицу, представленную на рисунке 2.2.1.4.



Рисунок 2.2.1.4 - Таблица «Договор»

2.2.2 Создание индексов

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

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

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

Некоторые индексы создаются автоматически, когда задается первичный ключ. Это следующие индексы:

1) PK_Клиенты - для таблицы Клиенты;

2) PK_Специалисты - для таблицы Специалисты;

3) PK_Ремонт - для таблицы Ремонт;

4) PK_Договор - для таблицы Договор.

Типология индексов:

*Кластерные индексы. При определении такого индекса в таблице физическое расположение данных перестраиваются в соответствии со структурой индекса;

*Некластерные индексы. В отличие от кластерных, они не перестраивают физическую структуру таблицы, а лишь организуют ссылки на соответствующие строки.

Первичные ключи относятся к кластерным индексам и являются уникальными.

Теперь создадим два некластеризованных индекса для таблиц Клиенты и Специалисты по столбцу Фамилия. Элементы этих индексов будут располагаться по возрастанию. Эти индексы создаются вручную при помощи запроса.

Создание индекса index_clients для таблицы Клиенты:

CREATE UNIQUE NONCLUSTERED INDEX index_clients

ON Клиенты (Фамилия asc)

Содание индекса index_spec для таблицы Специалисты:

CREATE UNIQUE NONCLUSTERED INDEX index_spec

ON Специалисты (Фамилия asc)

Данные индексы являются уникальными.

2.3 Создание представлений

Представления, или просмотры (VIEW), представляет собой временные, производные (виртуальные) таблицы и являются объектами базы данных, информация в которых не хранится постоянно, как в базовых таблицах, а формируется динамически при обращении к ним.

Представление - это предопределенный запрос, хранящийся в базе данных, который выглядит подобно обычной таблице и не требует для своего хранения дисковой памяти.

В данном разделе приводятся текст запроса, SQL-сценарии для создания представлений и результаты их работы в форме таблицы (или рисунка).

2.3.1 Однотабличный запрос на выборку по условиям

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

Для базы данных «Клиенты» был создан однотабличный запрос, который осуществляет выборку всех клиентов из таблицы «Клиенты», которые имеют марку машины «Audi» и проживающих по улице «Лениградская». Данный запрос был записан в представление view1.

Текст запроса выглядит следующим образом:

CREATE VIEW view1 AS

SELECT * FROM Клиенты

WHERE Марка_машины like ' % Audi % ' and Адрес like ' % Ленинградская % '

Рисунок 2.3.1.1 - Представление view1

2.3.2 Многотабличный запрос

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

Для базы данных «Договор» был создан многотабличный запрос, который осуществляет выборку всех клиентов из таблицы «Клиенты», чье транспортное средство нуждается в покраске. Выборка данных осуществляется также из таблицы «Договор», так как она является связующей между таблицами «Клиенты» и «Ремонт». Данный запрос был записан в представление view2.

Текст запроса выглядит следующим образом:

CREATE VIEW view2 AS

SELECT Клиенты. Фамилия, Клиенты. Имя, Клиенты. Отчество,

Клиенты. Марка_машины, Ремонт. Название AS [Вид технического обслуживания]

FROM Клиенты, Договор, Ремонт

WHERE Ремонт. Название like ' % Покраска % ' and

Договор. Код_клиента=Клиенты. Код_клиентаand

Договор. Код_ремонта=Ремонт. Код_ремонта

Результат запроса показан на рисунке 2.3.2.1.

Рисунок 2.3.2.1 - Представление view2

2.3.3 Запрос с использованием подзапроса

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

Для базы данных «Договор» был создан запрос с использованием подзапроса, который показывает данные о клиентах, у которых сумма за ремонт больше 2000. Данный запрос был записан в представление view3. Текст запроса выглядит следующим образом:

CREATE VIEW view3 AS

SELECT Ремонт. Название, Специалисты. Фамилия AS Специалисты, Клиенты. Фамилия AS Клиенты,

Договор. Дата_ремонта, Ремонт. Стоимость_ремонта

FROM Ремонт, Специалисты, Клиенты, Договор

WHERE Клиенты. Код_клиента=Договор. Код_клиентаand

Специалисты. Код_специалиста=Договор. Код_специалистаand

Ремонт. Код_ремонта=Договор. Код_ремонта

And Ремонт. Названиеin ((SELECT Ремонт. Название FROM

Ремонт, Договор WHERE Ремонт. Код_ремонта=Договор. Код_ремонта

And Ремонт. Стоимость_ремонта>'2000'))

Результат запроса показан на рисунке 2.3.3.1.

Рисунок 2.3.3.1 - Представление view3

2.3.4 Итоговый запрос

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

Для базы данных «Договор» был создан итоговый запрос, который показывает количество клиентов у каждого специалиста. В данном запросе было создано вычисляемое поле Количество_клиентов при помощи выражения COUNT. Данный запрос был записан в представление view4. Текст запроса выглядит следующим образом:

CREATE VIEW view4 AS

SELECT Специалисты. Фамилия, Специалисты. Имя,

Специалисты. Отчество,

COUNT (Договор. Код_клиента) AS Количество_клиентов

FROM Договор, Специалисты

WHERE Специалисты. Код_специалиста=Договор. Код_специалиста

GROUP BY Специалисты. Фамилия, Специалисты. Имя, Специалисты. Отчество

Результат запроса показан на рисунке 2.3.4.1.



Рисунок 2.3.4.1 - Представление view4

2.4 Примеры запросов на модификацию данных

2.4.1 Простые запросы

В данном разделе приводятся тексты простых однотабличных запросов, используемых для выполнения операций вставки, обновления и удаления данных: INSERT, DELETE и UPDATE.

1) INSERT

Операция вставки INSERT вставляет новые строки в таблицу. При этом значения столбцов могут представлять собой константы либо являться результатом выполнения подзапроса. Был создан запрос, осуществляющий вставку записи в таблицу «Специалисты». Текст запроса выглядит следующим образом:

INSERTINTO Специалисты (Фамилия, Имя, Отчество, Должность)

VALUES ('Гончаров', 'Павел', 'Алексеевич', 'Механик')

Из запроса видно, что была вставлена запись со следующими полями: Фамилия - Гончаров, Имя - Павел, Отчество - Алексеевич, Должность - Механик.

При вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отвергнута.

2) DELETE

Операция удаления DELETE. Был создан запрос, который удаляет клиента из таблицы Клиенты, имеющих фамилию «Акулов». Текст запроса выглядит следующим образом:

DELETE FROM Клиенты

WHERE Фамилия='Акулов'

Если предложение WHERE присутствует, удаляются записи из таблицы, удовлетворяющие условию отбора. Если опустить предложение WHERE, из таблицы будут удалены все записи, однако сама таблица сохранится.

3) UPDATE

Операция обновления UPDATE. Данный оператор применяется для изменения значений в группе записей или в одной записи указанной таблицы. Был создан запрос, который изменяет дату начала ремонта «12/12/19» на «12/12/21». Текст запроса выглядит следующим образом:

UPDATE Договор

SET Дата_ремонта='12/12/21'

WHERE Дата_ремонта='12/12/19'

Предложение WHERE является необязательным. Если оно опущено, значения указанных столбцов будут изменены во всех строках таблицы. Если предложение WHERE присутствует, то обновлены будут только те строки, которые удовлетворяют условию отбора.

2.4.2 Запросы с использованием подзапросов

В данном разделе приводятся тексты запросов на модификацию данных, в теле которых присутствуют подзапросы для излечения данных.

Был создан запрос с использованием подзапроса и оператора обновления данных UPDATE, который осуществляет замену названий документов, которые не были подписаны на название «Неподписанный документ». Текст запроса выглядит следующим образом:

UPDATE Ремонт

SET Название='Корректировка развал-схождения'

WHERE Название in

(SELECT Ремонт. Название

FROM Ремонт, Договор

WHERE Ремонт. Код_ремонта=Договор. Код_ремонтаand

Ремонт. Название='Регулировка развал-схождения')

Также был создан запрос с использованием подзапроса и оператора удаления DELETE. Данный запрос выполняет удаление записей, которые были созданы ранее 1 декабря 2012 года.

Текст запроса выглядит следующим образом:

DELETE FROM Договор

WHERE Дата_ремонтаin

(SELECT Дата_ремонта

FROM Договор

WHERE Дата_ремонта< '12/12/01')

2.5 Описание триггеров

Триггер - это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных.

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

На данном этапе для базы данных «Автотехцентр» были реализованы определенные ограничения посредством триггеров.

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

С помощью триггеров достигаются следующие цели:

*проверка корректности введенных данных и выполнение сложных ограничений целостности данных, которые трудно поддерживать с помощью ограничений целостности, установленных для таблицы;

*выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновления таблицы, реализованном определенным образом;

*накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;

Типология триггеров:

1) INSERT TRIGGER - запускаются при попытке вставки данных с помощью команды INSERT.

2) UPDATE TRIGGER - запускаются при попытке изменения данных с помощью команды UPDATE.

3) DELETE TRIGGER - запускаются при попытке удаления данных с помощью команды DELETE.

Для базы данных было разработано три триггера: два на удаление (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

Эти триггеры работают следующим образом. Когда мы удаляем запись из таблиц «Специалситы» или «Клиенты», то удаляются записи из таблицы из таблицы «Договор» с соответствующими значениями полей Код_специалиста или Код_клиента.

В таблице «Ремонт» существует поле Стоимость_ремонта. Введем ограничение стоимости ремонта 6000. Иными словами, пользователь не сможет ввести стоимость ремонта больше 6000.

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

CREATE TRIGGER Триггер_ins

ON Ремонт FOR INSERT

AS

IF @@ROWCOUNT=1

BEGIN

IF NOT EXISTS (SELECT *

FROM inserted

WHERE inserted. Стоимость_ремонта <'6000')

BEGIN

ROLLBACK TRAN

PRINT

'Предупреждение: Стоимость ремонта не должна превышать 6000'

END

END

Исходя из текста, видно, что в случае возникновения ошибки появится сообщение, в котором будет сообщено пользователю, что стоимость ремонта не должна превышать 6000'. Данное окно можно посмотреть на рисунке 1.1.

Попытке добивать новые данные в таблицу ремонт:

Результат запроса показан на рисунке 1.1.

Рисунок 1.1 - Запрос добавления новых данных в таблицу ремонт

Появляется сообщение с предупреждением (рисунок 1.2):

Рисунок 1.2 - Сообщение об ошибке при вставке записи

2.6 Создание пользователей и привилегий

В базе данных «Автотехцентр» были созданы группы пользователей и соответствующие привилегия. Создание пользователя базы данных, сопоставленного с именем входа, с помощью среды SQL Server Management Studio или Transact-SQL.

Пользователь базы данных - это идентификатор имени входа при подключении к базе данных. Имя пользователя базы данных может совпадать с именем входа, но это не является обязательным требованием. Пользователь является субъектом безопасности уровня базы данных. Для соединения с базой данных имя входа должно быть сопоставлено с пользователем базы данных. Имя входа может быть сопоставлено с различными базами данных в качестве разных пользователей, но в каждой базе данных ему может быть сопоставлен только один пользователь. В частично автономной базе данных можно создать пользователя, не имеющего имени входа. Если в базе данных включена учетная запись гостя, то под именем входа, не сопоставленным с пользователем базы данных, можно войти от пользователя guest.

Администраторы баз данных, сами создают пользователей и дают им привилегии. С другой стороны пользователи, которые создают таблицы, сами имеют права на управление этими таблицами. Каждый пользователь в SQL базе данных имеет набор привилегий. Это то что пользователю разрешается делать. Эти привилегии могут изменяться со временем - новые добавляться, старые удаляться. Привилегии даются, отменяются двумя командами SQL: - GRANT (ДОПУСК) и REVOKE (ОТМЕНА).

Рассматривается система безопасности, принятая в языке SQL. Излагаются общие правила разграничения доступа. Описываются режимы аутентификации и компоненты структуры безопасности (пользователи, роли баз данных), администрирование системы безопасности (создание учетных записей и управление ими, управление пользователями и ролями). Дается определение прав пользователя на доступ к объектам базы данных. Рассматриваются неявные права, вопросы запрета доступа и неявного отклонения доступа, а также конфликты доступа.

Стабильная система управления пользователями - обязательное условие безопасности данных, хранящихся в любой реляционной СУБД. В языке SQL не существует единственной стандартной команды, предназначенной для создания пользователей базы данных - каждая реализация делает это по-своему. В одних реализациях эти специальные команды имеют определенное сходство, в то время как в других их синтаксис имеет существенные отличия. Однако независимо от конкретной реализации все основные принципы одинаковы.

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

В системе SQL-сервер организована двухуровневая настройка ограничения доступа к данным. На первом уровне необходимо создать так называемую учетную запись пользователя (login), что позволяет ему подключиться к самому серверу, но не дает автоматического доступа к базам данных. На втором уровне для каждой базы данных SQL-сервера на основании учетной записи необходимо создать запись пользователя. На основе прав, выданных пользователю как пользователю базы данных (user), его регистрационное имя (login) получает доступ к соответствующей базе данных. В разных базах данных login одного и того же пользователя может иметь одинаковые или разные имена user с разными правами доступа. Иначе говоря, с помощью учетной записи пользователя осуществляется подключение к SQL-серверу, после чего определяются его уровни доступа для каждой базы данных в отдельности.

В системе SQL-сервер существуют дополнительные объекты - роли, которые определяют уровень доступа к объектам SQL-сервера. Они разделены на две группы: назначаемые для учетных записей пользователя сервера и используемые для ограничения доступа к объектам базы данных.

Итак, на уровне сервера система безопасности оперирует следующими понятиями:

*аутентификация;

*учетная запись;

*встроенные роли сервера.

На уровне базы данных применяются следующие понятия:

*пользователь базы данных;

*фиксированная роль базы данных;

*пользовательская роль базы данных.

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

Таблица 1

Таблица	Группа пользователей
	Admin	Specialist	Director
Клиенты	ALL	SELECT, UPDATE, DELETE, INSERT	SELECT, INSERT, UPDATE, DELETE
Специалисты	ALL	SELECT, UPDATE, DELETE, INSERT	SELECT, INSERT, UPDATE, DELETE
Ремонт	ALL	SELECT, UPDATE, DELETE, INSERT	SELECT, INSERT, UPDATE, DELETE

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

Создание в базе данных учетной записи пользователя, указав для него пароль.

Было создано три учетных записи - это Admin, Specialist и Director. Текст запросов выглядит следующим образом:

1) CREATE LOGIN Admin

WITH PASSWORD = 'Adm';

GO

CREATE USER Admin FOR LOGIN Admin;

GO

2) CREATE LOGIN Specialist

WITH PASSWORD = 'Spec';

GO

CREATE USER Specialist FOR LOGIN Specialist;

GO

3) CREATE LOGIN Director

WITH PASSWORD = 'Dir';

GO

CREATE USER Director FOR LOGIN Director;

GO

А затем были назначены привилегии для каждой группы пользователей:

1) GRANT ALL ON Клиенты TO Admin

GRANT ALL ON Специалисты TO Admin

GRANT ALL ON Ремонт TO Admin

GRANT ALL ON Договор TO Admin

2) GRANT SELECT, UPDATE, DELETE, INSERT ON Клиенты TO Specialist

GRANT SELECT, UPDATE, DELETE, INSERT ON Специалисты TO Specialist

GRANT SELECT, UPDATE, DELETE, INSERT ON Ремонт TO Specialist

GRANT SELECT, UPDATE, DELETE, INSERT ON Договор TO Specialist

3) GRANT SELECT, UPDATE, DELETE, INSERT ON Клиенты TO Director

GRANT SELECT, UPDATE, DELETE, INSERT ON Специалисты TO Director

GRANT SELECT, UPDATE, DELETE, INSERT ON Ремонт TO Director

GRANT SELECT, UPDATE, DELETE, INSERT ON Договор TO Director

Заключение

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

Результатом явилось создание реляционной базы данных «Автотехцентр». При создании этой базы данных было освоено:

1) Создание базы данных посредством запроса, создание таблиц и наполнение их содержанием, а также построение диаграммы базы данных;

2) Создание представлений, реализующих различные запросы;

3) Работы с запросами на модификацию данных (INSERT, DELETE, UPDATE);

4) Наложение ограничений посредством создания триггеров;

5) Создание пользователей и назначение им привилегий.

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

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

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

даталогический восстановление автотехцентр индексирование

1) http://support.microsoft.com

2) http://pyramidin.narod.ru/rusql/index.htm

3) http://msdn.microsoft.com

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

...