Разработка базы данных и серверной части информационной системы учета технического обслуживания станков средствами СУБД Microsoft SQL Server

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования

" Пензенский государственный университет"

(ФГБОУ ВПО «Пензенский государственный университет»)

Кафедра «Математическое обеспечение и применение ЭВМ»

Разработка базы данных и серверной части информационной системы учета технического обслуживания станков средствами СУБД Microsoft SQL Server

Пояснительная записка к курсовому проекту по дисциплине

" Системы управления базами данных"

ПГУ 230 105−5КР101.16 ПЗ Автор работы Борунов А.А.

Группа 10ВП2

Специальность 230 105

Руководитель работы Казакова И.А.

Пенза 2012 г.

Реферат

Пояснительная записка содержит 28 листов, 23 рисунка, 5 таблиц, 2 использованных источника и 4 приложения.

MS SQL SERVER 2008, СИСТЕМЫ УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ (СУБД), ТАБЛИЦЫ, ЗАПРОСЫ, ИНФОРМАЦИОННАЯ СИСТЕМА, ПРЕДМЕТНАЯ ОБЛАСТЬ.

Объектом разработки является информационная система, созданная с использованием средств, предоставляемых современными СУБД реляционного типа.

Предмет разработки - программные средства для реализации базы данных и серверной части информационной системы «учета технического обслуживания станков» средствами СУБД Microsoft SQL Server 2008

Цель работы — разработка серверной части информационной системы «учета технического обслуживания станков»

Результаты работы

В процессе разработки проводилось изучение основных средств, предоставляемых средой MS SQL SERVER 2008, и на основе их использования разработана и реализована программа.

• Реферат
• Введение
• 1. Разработка базы данных и серверной части информационной системы «учета технического обслуживания станков»
• 1.1 Анализ предметной области
• 1.2 Анализ информационных задач и круга пользователей системы
• 1.3 Выработка требований и ограничений
• 1.4 Разработка проекта базы данных
• 1.5 Программная реализация проекта базы данных
• 1.6 Разработка триггеров для поддержки сложных ограничений целостности в базе данных
• 1.7 Запросы
• 1.8 Представления
• Заключение
• Список использованных источников
• Приложения

Основной целью курсового проекта является разработка серверной части информационной системы «система учета технического обслуживания станков». Для её выполнения нужно воспользоваться СУБД (в данном случае Microsoft SQL Server 2008).

Задачи, которые преследует курсовой проект:

1. Изучение возможностей СУБД Microsoft SQL Server 2008 для построения информационных систем,

2. Изучение языка TRANSACT SQL

3. Разработать структуру БД и описать её отношения

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

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

6. Создание триггеров и хранимых процедур для поддержания целостности

база серверный информационная система

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

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

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

В соответствии с предметной областью система строится с учётом следующих особенностей:

1) Каждому станку соответствует свой номер;

2) Каждому работнику соответствует свой номер;

3) Каждая должность имеет свой номер;

Выделим базовые сущности этой ПО:

1) Ремонт. Атрибуты ремонта — код ремонта, код станка, код работника, дата начала, дата окончания, код вида ремонта, примечания.

2) Станки. Атрибуты станков — код станка, месторасположение, год выпуска, марка.

3) Работник. Атрибуты работника — код работника, ФИО работника, код должности.

4) Должность. Атрибуты должности — код должности, название должности, оклад.

5) Виды ремонта. Атрибуты видов ремонта — код вида ремонта, продолжительность, стоимость, примечания.

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

Система создаётся для обслуживания следующих групп пользователей:

1) Начальство;

2) работники;

Функциональные возможности:

1) ведение базы данных (запись, чтение, модификация, удаление);

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

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

1.3 Выработка требований и ограничений

Основные ограничения целостности:

1. Значения всех числовых атрибутов — больше 0.

2. Значения всех атрибутов должны быть not null, т. е. нуждаются в обязательном заполнении.

1.4 Разработка проекта базы данных

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

Рисунок 1. Разработанная структура данных

Для таблицы «Станки»

Для таблицы «Виды ремонта»

Для таблицы «Ремонт»

Для таблицы «Работник»

Для таблицы «Должность»

1.5 Программная реализация проекта базы данных

Программная реализация проекта базы данных выполнена с помощью операторов языка SQL CREATE DATABASES, CREATE TABLE.

Текст программы создания базы данных приведен в приложении А.

Для спроектированной базы данных средствами СУБД Microsoft SQL Server 2008 построена диаграмма, которая приведена в приложении Б.

Текст программы ввода тестовых данных приведен в приложении В.

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

1. Триггер на команду вставки

Данный триггер добавляет сведения о пассажире, при этом проверяет правильность ввода данных.

create trigger name_val

on employee

for insert, update

as

if @@rowcount=1

begin

declare @p char (30), @i int, @s char

select @p=FIOra from employee

set @i=1

while @i<=len (@p)

begin

set @s=substring (@p, @i, 1)

if not ((@s between 'А' and 'Я') or (@s between 'а' and 'я') or (@s=' ') or (@s='-')

or (@s between 'A' and 'Z') or (@s between 'a' and 'z'))

begin

print 'Отмена: неправильно указана фамилия сотрудника'

rollback tran

return

end

set @i=@i+1

end

end

print 'Добавление/изменение выполнено'

Вызов триггера осуществляется запросами вида:

Результат работы триггера для данного примера приведен на рисунках 2, 3

Рисунок 2.

Вызов триггера осуществляется запросами вида:

insert into employee values (10, 'Ульянов Петр Петрович', 3);

Рисунок 3.

2. Триггер на команду удаления

Данный триггер удаляет все данные о пассажире.

Такой триггер необходим для очистки ненужных данных.

CREATE TRIGGER del

ON employee FOR INSERT

as

begin

declare @FIO char (40), @Cod int

select @Fio = employee. FIORa from deleted employee

select @Cod = employee. FIORa from deleted employee

where employee. FIORa = @FIO

if @Cod is not null

begin

exec @Cod

delete from employee where employee. CodeRa = @Cod

end;

end;

Вызов триггера осуществляется запросами вида:

delete from employee where CodeRa = 3;

Результат работы триггера для данного примера приведен на рисунках 3,4.

Рисунок 4.

Рисунок 5. Работа триггера на команду удаления

3. Триггер на команду обновления

Данный тригер меняет стоимость работы.

CREATE TRIGGER repDob

ON repairs FOR INSERT

AS

IF @@ROWCOUNT = 1

BEGIN

DECLARE @bc INTEGER, @cm INTEGER

SELECT @bc = i. Stoim, @cm = i. CodeVR

FROM inserted i

IF @bc > 55 500

BEGIN

PRINT 'Ошибка! Указанная стоимость превышает максимум. '

ROLLBACK TRAN

END

ELSE

BEGIN

UPDATE repairs

SET repairs. Stoim = @bc

WHERE repairs. CodeVR = @cm

END

END

Вызов триггера осуществляется запросами вида:

update repairs

set repairs. Stoim = 500

where repairs. CodeVR = 3;

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

Рисунок 6.

Рисунок 7. Работа триггера на команду обновления.

1.7 Запросы

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

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

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

• Найти все коды вида ремонта

• По коду вида ремонта определить стоимость.

• Показать все станки марки «Янтарь'.

Сложные запросы

•. По коду станка определить стоимость его вида ремонта.

• Узнать марку станка по коду вида ремонта.

• Найти все ФИО работников, имеющих оклад 20 000 рублей.

•. Определить все марки станков, находящихся в 1 цеху.

• Показать фамилию работника, который выполнял самый продолжительный ремонт.

• Найти должность работников, выполняющих самый дорогостоящий вид ремонта.

Найти все марки станков, которые обслуживал Иванов Иван Иванович.

Программная реализация запросов приведена в приложении Д.

1.8 Представления

1. Необновляемое представление, маскирующее строки и столбцы. Задать новые имена для столбцов.

CREATE VIEW data AS

SELECT

DataN AS [Дата начала],

DataO As [Дата окончания]

FROM repair WHERE DataN <> '2011;08−10' AND DataO <> '2011;08−11';

SELECT * FROM data WHERE [Дата начала] = '2011;08−14';

Рисунок 8.

2. Агрегирующее представление.

CREATE VIEW CAShs AS

SELECT

sum (DISTINCT Stoim) AS [Общая стоимость]

FROM repairs;

SELECT * FROM CAShs;

Рисунок 9.

3. Представление, основанное на нескольких таблицах.

· create VIEW Dates AS

SELECT

NameDol AS [Название должности],

FIORa AS [ФИО]

FROM post, employee

where CodeDol = a_CodeDol;

SELECT * FROM Dates;

Рисунок 10.

· create VIEW Mon AS

SELECT

Oklad AS [Оклад],

FIORa AS [ФИО]

FROM post, employee

where CodeDol = a_CodeDol;

SELECT * FROM Mon;

Рисунок 11.

· create VIEW smotr AS

SELECT

marca AS [Название Станка],

FIORa AS [ФИО],

Stoim AS [Стоимость работы]

FROM machines, employee, repair, repairs

where CodeSt = a_CodeSt

and CodeRa = a_CodeRa

and CodeVR = a_CodeVR;

SELECT * FROM smotr;

Рисунок 12.

Заключение

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

Разработана структура базы данных, состоящей из 6 таблиц. Разработано 3 триггера, 3 представления.

Изучены основы языка программирования TRANSACT SQL.

Список использованных источников

1. Грофф Дж., Вайнберг П. SQL: Полное руководство. / 2-е изд. — К., 2001.

2. Казакова И. А. Основы языка Transact SQL, учебное пособие. — Издательство ПГУ, г. Пенза, 2010 г.

Приложения

Программа создания базы данных

Приложение А

1. Запрос создания базы данных «Система учета технического обслуживания станков» :

CREATE DATABASE repair_of_machines;

2. Запрос создания отношения Станки:

create table machines

(

CodeSt INT NOT NULL CHECK (CodeSt BETWEEN 0 AND 9999) PRIMARY KEY,

MestoVARCHAR (40) NOT NULL,

Godvip DATETIME NOT NULL,

Marca VARCHAR (11) NOT NULL,

);

3. Запрос создания отношения Виды ремонта:

create table repairs

(

CodeVR INT NOT NULL CHECK (CodeVR BETWEEN 0 AND 500) PRIMARY KEY,

ProdVARCHAR (20) NOT NULL,

StoimVARCHAR (5) NOT NULL,

PrimechVARCHAR (1000),

);

4. Запрос создания отношения Должность:

create table post

(

CodeDol INT NOT NULL CHECK (CodeDol BETWEEN 0 AND 500) PRIMARY KEY,

NameDol VARCHAR (30) NOT NULL,

OkladVARCHAR (7) NOT NULL,

);

5. Запрос создания отношения Работник:

create table employee

(

CodeRa INT NOT NULL CHECK (CodeRa BETWEEN 0 AND 9999) PRIMARY KEY,

FIORa VARCHAR (30) NOT NULL,

aCodeDol INT NOT NULL

CONSTRAINT a_CodeDolFOREIGN KEY REFERENCES post (CodeDol),

);

6. Запрос создания отношения Ремонт:

create table repair

(

CodeReINTNOT NULL

CHECK (CodeRe BETWEEN 1 AND 500) PRIMARY KEY,

CodeSt INT NOT NULL

CONSTRAINT CodeStFOREIGN KEY REFERENCES machines (CodeSt),

CodeRa INT NOT NULL

CONSTRAINT CodeRaFOREIGN KEY REFERENCES employee (CodeRa),

DataN DATETIME NOT NULL,

DataO DATETIME NOT NULL,

CodeVR INT NOT NULL

CONSTRAINT CodeVRFOREIGN KEY REFERENCES repairs (CodeVR),

Primech VARCHAR (1000),

);

Приложение Б

Диаграмма базы данных

Рисунок 13. Диаграмма базы данных

Приложение В

Текст программы ввода текстовых данных

Отношение Работник:

insert into employee values (1, 'Петров Василий Николаевич', 1);

insert into employee values (2, 'Сидорова Елена Петровна',

2);

insert into employee values (3, 'Мазурков Роман Олегович',

3);

insert into employee values (4, 'Онегина Василиса Сергеевна',

4);

insert into employee values (5, 'Прокин Александр Иванович',

5)

insert into employee values (6, 'Копьева Ольга Сергеевна',

6);

insert into employee values (7, 'Грланова Кристина Сергеевна',

7);

insert into employee values (8, 'Каширов Александр Олегович',

8);

insert into employee values (9, 'Красов Игорь Сергеевич',

9);

Отношение Станки:

insert into machines values (1, '1-й цех', '2011;08−10', 'Янтарь');

insert into machines values (2, '2-й цех', '2011;08−11', 'Ячмень');

insert into machines values (3, '3-й цех', '2011;08−12', 'Клинцы');

insert into machines values (4, '4-й цех', '2011;08−13', 'Магр');

insert into machines values (5, '5-й цех', '2011;08−14', 'Авангард');

insert into machines values (6, '6-й цех', '2011;08−15', 'Кедр');

insert into machines values (7, '7-й цех', '2011;08−16', 'Атлант');

insert into machines values (8, '8-й цех', '2011;08−17', 'Полюс');

insert into machines values (9, '9-й цех', '2011;08−18', 'Скиф');

Отношение Должность:

insert into post values (1, 'Главный ремонтник', '10 000');

insert into post values (2, 'Охранник', '15 000');

insert into post values (3, 'Инженер', '12 000');

insert into post values (4, 'Главный инженер', '20 000');

insert into post values (5, 'Ремонтник', '7000');

insert into post values (6, 'Бригадир', '11 000');

insert into post values (7, 'Бухгалтер', '5000');

insert into post values (8, 'Рабочий', '6000');

insert into post values (9, 'Финансовый директор', '30 000');

Отношение Ремонт:

insert into repair values (1, 1, 1, '2011;08−10', '2011;08−11', 1, ' ');

insert into repair values (2, 2, 2, '2011;08−11', '2011;08−12', 2, ' ');

insert into repair values (3, 3, 3, '2011;08−12', '2011;08−13', 3, ' ');

insert into repair values (4, 4, 4, '2011;08−13', '2011;08−14', 4, ' ');

insert into repair values (5, 5, 5, '2011;08−14', '2011;08−15', 5, ' ');

insert into repair values (6, 6, 6, '2011;08−15', '2011;08−16', 6, ' ');

insert into repair values (7, 7, 7, '2011;08−16', '2011;08−17', 7, ' ');

insert into repair values (8, 8, 8, '2011;08−17', '2011;08−18', 8, ' ');

insert into repair values (9, 9, 9, '2011;08−18', '2011;08−19', 9, ' ');

Отношение Виды ремонта:

insert into repairs values (1, '10 дней', '100', ' ');

insert into repairs values (2, '12 дней', '120', ' ');

insert into repairs values (3, '20 дней', '200', ' ');

insert into repairs values (4, '13 дней', '130', ' ');

insert into repairs values (5, '14 дней', '140', ' ');

insert into repairs values (6, '15 дней', '150', ' ');

insert into repairs values (7, '16 дней', '160', ' ');

insert into repairs values (8, '18 дней', '180', ' ');

insert into repairs values (9, '17 дней', '170', ' ');

Приложение Г

Реализация запросов на языке SQL

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

1. Найти все названия видов ремонта

SELECT CodeVR AS Код_вида_ремонта

FROM repairs

Рисунок 14.

2. По продолжительности работы, определить стоимость

SELECT Stoim AS Стоимость, Prod AS Продолжительность

FROM repairs

WHERE Prod = '12 дней';

Рисунок 15.

3. Показать все станки марки «Янтарь'

SELECT Marca AS Марка, CodeSt AS Код_станка

FROM machines

WHERE Marca = 'Янтарь';

Рисунок 16.

Сложные запросы:

1. По названию станка определить стоимость его вида ремонта

SELECT Marca AS Название_станка, Stoim AS Стоимость, CodeVR AS Код_вида_ремонта

FROM repairs, machines

WHERE Marca = 'Клинцы';

Рисунок 17.

2. Узнать марку станка по названию вида ремонта

SELECT CodeVR AS Код_вида_ремонта, Marca AS Марка_станка

FROM repairs, machines

WHERE CodeVR = 9;

Рисунок 18.

3. Найти всех работников, имеющих оклад 20 000 рублей

SELECT Oklad AS Оклад, FIORa AS ФИО

FROM post, employee

WHERE Oklad = '20 000';

Рисунок 19.

4. Определить все ФИО работников, находящихся в 1 цеху

SELECT Mesto AS Место, FIORa AS ФИО

FROM machines, employee

WHERE Mesto = '1-й цех';

Рисунок 20.

5. Показать ФИО работника, который выполнял ремонт сроком 20 дней

SELECT FIORa AS ФИО, Prod AS Время_ремонта

FROM employee, repairs

WHERE Prod = '20 дней';

Рисунок 21.

6. Найти должность работников, выполняющих ремонт, стоимостью 150

SELECT NameDol AS Название_должности, Stoim AS Стоимость_ремонта

FROM post, repairs

WHERE Stoim = '150'

and NameDol <> 'Бухгалтер'

and NameDol <> 'Финансовый директор'

and NameDol <> 'Охранник';

Рисунок 22.

7. Найти все марки станков, которые обслуживал Прокин Александр Иванович

SELECT Marca AS Марка_станка, FIORa AS ФИО

FROM employee, machines

WHERE FIORa = 'Прокин Александр Иванович';

Рисунок 23.