48668 (Разработка проекта управления базами данных для процесса "Учет ремонта и ТО автотранспорта"), страница 3

CREATE TABLE ConditionUnit

(IDConditionUnit INT IDENTITY CONSTRAINT ConditionUnitPrimary PRIMARY KEY,

IDCondition INT,

IDTransport INT,

Working VARCHAR(250),

Beginning DateTime,

Theend DateTime,

CONSTRAINT ConditionUnitConditionForeign FOREIGN KEY (IDCondition)REFERENCES Condition,

CONSTRAINT ConditionUnitTransportForeign FOREIGN KEY (IDTransport) REFERENCES Transport);

Команды языка манипулирования данными

Команды языка манипулирования данными (DML)

Три хорошо известные операции над кортежами:

1. Добавление, реализуется в SQL с помощью команды, INSERT

2. Правка, реализуется в SQL с помощью команды, UPDATE

3. Удаление, реализуется в SQL с помощью команды, DELETE Команда вставки INSERT используется для ввода новых строк в таблицу. Команда UPDATE - предназначена для редактирования данных в таблице. Команда DELETE - удаляет записи в таблице.

Команды языка определения данных (DDL)

Команда ALTER TABLE используется для внесения изменений в описание таблицы, в том числе для: добавления и изменения столбцов, добавления, разрешения, запрета и удаления ограничений.

Команда DROP TABLE используется для удаления таблицы.

Команда вставки INSERT. Команда языка DML INSERT используется для ввода новых строк в таблицу. Синтаксис команды:

INSERT INTO {[( [псевдоним] [, …n]] |[]}

VALUES ([,…n]);

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

Заполнение таблицы "Автобаза"

INSERT INTO Autobase(NameAutobase,Address) VALUES('Автобаза №1','г.Воркута, ул.Транспортная,10');

INSERT INTO Autobase(NameAutobase,Address) VALUES('Автобаза №2','г.Воркута, ул.Комсомольская,11');

Заполнение таблицы "Справочник состояний"

INSERT INTO Condition (NameCondition) VALUES ('ТО1');

INSERT INTO Condition (NameCondition) VALUES ('ТО2');

INSERT INTO Condition (NameCondition) VALUES ('СО');

INSERT INTO Condition (NameCondition) VALUES ('ТР');

Заполнение таблицы "Справочник технического обслуживания"

INSERT INTO Technical (NameTechnical,TO1,TO2) VALUES ('легковые', 3500, 14000);

INSERT INTO Technical (NameTechnical, TO1, TO2) VALUES ('автобусы',2600,13000);

INSERT INTO Technical (NameTechnical,TO1,TO2) VALUES ('грузовые, автобусы на базе грузовых автомобилей',2200,11000);

Заполнение таблицы "Тип марок автотранспорта"

INSERT INTO TypeMarka (NameTypeMarka, IDTechnical) VALUES ('легковая',1);

INSERT INTO TypeMarka (NameTypeMarka, IDTechnical) VALUES ('мазутовоз',3);

INSERT INTO TypeMarka (NameTypeMarka, IDTechnical) VALUES ('молокоцистерна',3);

INSERT INTO TypeMarka (NameTypeMarka,IDTechnical) VALUES ('поливомоечная',3);

INSERT INTO TypeMarka (NameTypeMarka, IDTechnical) VALUES ('самосвал',3);

INSERT INTO TypeMarka (NameTypeMarka, IDTechnical) VALUES ('спец.фургон',3);

Заполнение таблицы "Марка автотранспорта"

INSERT INTO Marka (IDTypeMarka, NameMarka, Capacity) VALUES (1,'ГАЗ-31029',0.00);

INSERT INTO Marka (IDTypeMarka, NameMarka, Capacity) VALUES(1, УАЗ-2206',0.00);

INSERT INTO Marka (IDTypeMarka, NameMarka, Capacity) VALUES (22,'КАВЗуд ЛАЗ',0.00);

Заполнение таблицы "Автотранспорт"

INSERT INTO Transport (IDAutobase, IDMarka, Garage Number, Gos Number) VALUES (1,294, 502,'11ко3813');

INSERT INTO Transport (IDAutobase, IDMarka, GarageNumber, GosNumber) VALUES(1,294, 837,'11ко3866');

INSERT INTO Transport (IDAutobase, IDMarka, Garage Number, GosNumber) VALUES(1,483, 125,'14-51КМО');

INSERT INTO Transport (IDAutobase, IDMarka, GarageNumber, GosNumber) VALUES (1,483, 152,'14-52КМО');

Заполнение таблицы "Состояние единицы"

INSERT INTO Condition Unit (IDCondition, IDTransport, Working, Beginning, Theend) VALUES (2,1,'покраска переднего левого крыла; покраска крыши а/м','2008.09.15','2008.09.16');

INSERT INTO ConditionUnit (IDCondition, IDTransport, Working, Beginning, Theend) VALUES (2,2,'покраска передней левой двери; подготовка к покраске и покраска передней левой стойки','2008.09.15','2008.09.16');

INSERT INTO ConditionUnit (IDCondition, IDTransport, Working, Beginning, Theend) VALUES (2,3,'чистка хромированных деталей','2008.09.15','2008.09.16');

Запросы к базе данных

Команда SELECT позволяет реализовывать все операторы реляционной алгебры над отношениями (таблицами) базы данных. Обязательными предложениями команды SELECT являются предложения SELECT и FROM. В самой простой форме, команда SELECT используется для того, чтобы извлечь информацию из таблицы. Не обязательно использовать все предложения команды, но обязательно соблюдать их порядок следования, то есть предложение ORDER BY всегда завершает команду SELECT, а предложение HAVING всегда стоит после GROUP BY, которое, в свою очередь, не может предшествовать предложению WHERE и т.п.

Синтаксис команды SELECT:

SELECT [DISTINCT] *| [] [,] [,…n]

FROM [, …n]| ()

[WHERE ]

[GROUP BY<выражение группировки]

[HAVING ]

[ORDER BY [,…n]]

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

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

Выбор всех автобаз

Select * from AutoBase

Выбор Гаражного номера, Госномера из таблицы "Автотранспорт", Имя марки из таблицы "Марка автотранспорта", Тип марки из таблицы "Тип марки автотранспорта"

Select T.IDTransport, T.GarageNumber[Гар.№], TM.NameTypeMarka+' '+M.NameMarka+' '+T.GosNumber[Тип марки, Имя марки, Гос.номер] from Transport T INNER JOIN Marka M ON T.IDmarka=M.IDmarka INNER JOIN TypeMarka TM ON M.IDTypeMarka=TM.IDTypeMarka where (T.IDAutobase=:IDAutoBase) ORDER BY T.GarageNumber;

Выбор Даты постановки в ремонт, Дата снятия с ремонта, Вид работ

Select LTrim(Str(Day(Beginning)))+'.'+LTrim(Str(Month(Beginning)))+'.' +LTrim(Str(Year(Beginning)))[Дата пост.],LTrim(Str(Day(Theend)))+'.' +LTrim(Str(Month(Theend)))+'.'+LTrim(Str(Year(Theend)))[Дата вых.], Working[Вид работ] from ConditionUnit where (IDTransport=:IDTransport);

Согласно приведенным выше запросам имеем некоторые выходные данные:

Хранимые процедуры

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

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

При создании процедур необходимо придерживаться следующих правил: во время выполнения хранимой процедуры все объекты, на которые она ссылается, должны присутствовать в базе данных. В хранимых процедурах нельзя применять операторы создания объектов: CREATE PROCEDURE, CREATE TRIGGER, CREATE VIEW.

Синтаксис команды:

CREATE PROC[EDURE ] имя_процедуры [; число ]

[ { @параметр тип_данных }

[ VARYING ] [ = значение_по_умолчанию ] [ OUTPUT ] ] [,...n ]

[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ]

AS оператор_SQL [ ...n ]

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

VARYING – определяет, что выходным параметром будет результирующее множество (используется совместно с параметром OUTPUT).

RECOMPILE – предписывает выполнять перекомпиляцию кода процедуры при ее запуске.

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

AS – индикатор начала собственно кода процедуры.

Вызов процедуры (исключение составляют триггеры) осуществляется по команде EXEC.

Синтаксис команды EXEC

EXEC []

- Создадим хранимую процедуру для таблицы "Марка автотранспорта"

CREATE PROC PMarkaINSERTFirst

@IDTypeMarka INT,

@NameMarka VARCHAR (15),

@Capacity FLOAT

AS

INSERT INTO Marka(IDTypeMarka,NameMarka,Capacity)VALUES( @IDTypeMarka, @NameMarka,@Capacity);

Теперь заполнение таблицы "Марка автотранспорта" через хранимые процедуры будет иметь следующий вид:

EXEC PMarkaINSERTFirst 1,'ГАЗ-31029',0.00

EXEC PMarkaINSERTFirst 22,'КАВЗ,ПАЗ',0.00

EXEC PMarkaINSERTFirst 22,'КАВЗуд ЛАЗ',0.00

EXEC PMarkaINSERTFirst 22,'КАВЗуд ЛАЗ',0.00

EXEC PMarkaINSERTFirst 22,'КАВЗ,ПАЗ',0.00

Создание хранимые процедуры для запросов Select

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

- Выбор всех автобаз

CREATE PROC PAutoBaseSelect

AS

Select * from AutoBase

Теперь выборка всех автобаз будет иметь следующий вид

Exec PAutoBaseSelect;

Выбор Гаражного номера, Госномера из таблицы "Автотранспорт", Имя марки из таблицы "Марка автотранспорта", Тип марки из таблицы "Тип марки автотранспорта"

CREATE PROC PTransportSelect1

@IDAutoBase INT

AS

Select T.GarageNumber[Гар.№], T.GosNumber[Гос.номер], M.NameMarka[Имя марки], TM.NameTypeMarka[Тип марки] from Transport T INNER JOIN Marka M ON T.IDmarka=M.IDmarka

INNER JOIN TypeMarka TM ON M.IDTypeMarka=TM.IDTypeMarka where (T.IDAutobase=@IDAutoBase);

Теперь выбор Гаражного номера, Госномера из таблицы "Автотранспорт", Имя марки из таблицы "Марка автотранспорта", Тип марки из таблицы "Тип марки автотранспорта" будет иметь следующий вид

Exec PTransportSelect1 1;

Выбор Даты постановки в ремонт, Дата снятия с ремонта, Вид работ

CREATE PROC PConditionUnit1

@IDTransport INT

AS

Select LTrim(Str(Day(Beginning)))+'.'+LTrim(Str(Month(Beginning)))+'.'+LTrim(Str(Year(Beginning)))[Дата пост.],

LTrim(Str(Day(Theend)))+'.'+LTrim(Str(Month(Theend)))+'.'+LTrim(Str(Year(Theend)))[Дата вых.], Working[Вид работ]

from ConditionUnit where (IDTransport=@IDTransport);

Теперь Выбор Даты постановки в ремонт, Дата снятия с ремонта, Вид работ примет следующий вид

Exec PConditionUnit1 1;

Триггеры

Триггер – это специальный тип хранимой процедуры, которая автоматически выполняется при возникновении некоторого события (попытке выполнить операции удаления, добавления, редактирования). Как и любой объект базы данных, триггер создается с помощью команды CREATE

Cинтаксис команды создания триггера

CREATE TRIGGER

ON

[WITH ENCRYPTION]

{{{FOR|AFTER}}|INSTEAD OF}

[WITH APPEND]

[NOT FOR REPLICATION]

AS

….

….

….>,

где

ON имя объекта, для которого триггер используется.

WITH ENCRYPTION – кодирует текст представления.

WITH APPEND – используется для совместимости с версией 6.5

NOT FOR REPLICATION – меняет правила запуска триггера. Такой триггер не будет стартовать при выполнении над таблицей операций, связанных с репликацией данных.

При срабатывании триггера создаются таблицы INSERTED, DELETED. INSERTED – для хранения добавленных записей, DELETED – для хранения удаленных записей. Таблицы видимы только для триггера и существуют только при выполнении триггера.

Создание триггера для таблицы "Состояние единицы".Проверка на ввод пустой даты окончания ТО или ремонта и замена ее на 9999.01.01

CREATE TRIGGER ConditionUnitTerm

ON ConditionUnit

FOR INSERT,UPDATE

AS

IF EXISTS

( SELECT 'TRUE' FROM ConditionUnit WHERE Theend='1900.01.01')

BEGIN

UPDATE ConditionUnit SET Theend='9999.01.01' WHERE Theend='1900.01.01'

END

Заключение

В результате курсового проекта была реализована база данных для процесса «Учет ремонта и ТО автотранспорта». Были созданы таблицы, в которых были определены ограничения на уровне столбцов и на уровне самой таблицы, и занесены необходимые данные. Изучение предметной области позволило выделить и реализовать основные запросы и представления к базе данных.

Для реализации целостности базы данных был созданы триггер, изменяющий введенную дату окончания ремонта или ТО.

Реализованная база данных позволит в дальнейшем хранить необходимую информацию, добавлять данные и редактировать их. Эта база данных может быть использована в дальнейшем для разработки информационной системы Учет ремонта и ТО автотранспорта», с целью эффективной организации работы отдела ПТО УАТХ.

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

1. Томас Конноли, Каролин Бегг. Базы данных. Проектирование, реализация и сопровождение. Теория и практика: Пер. с англ. М.:Вильямс> 2001.-1440с.

2. К. Дж. Дейт. Введение в системы баз данных.: Пер. с англ. М.:Вильямс_? 2001.-1072с.

3. Г.Н. Калянов. Консалтинг в автоматизации бизнес процессов. М.: Горячая линия - Телеком, 2002. - с.320.:ил.

Приложение

Приложение