Имя столбца
|
Содержательное
описание
|
Тип данных
|
Размерность
|
Область
допустимых значений
|
Возможность
значения Null
|
Роль
|
Пример
|
Примечание
|
Id_Representation
|
Код спектакля
|
Целый
|
5
|
00001-99999
|
нет
|
PK
|
3690
|
|
Title_Representation
|
Название
|
Символьный
|
50
|
‘А-я’ ‘,’ ‘-’
|
нет
|
|
Красная шапочка
|
|
Year
|
Год постановки
|
Целый
|
4
|
1930-2030
|
нет
|
|
2001
|
|
Id_Genre
|
Код жанра
|
Целый
|
1
|
1-9
|
нет
|
FK
|
2
|
|
Budget
|
Бюджет
|
Целый
|
10
|
20000-9999999999
|
нет
|
|
1650000
|
|
1.5 Программа
реализации проекта базы данных
Программная реализация проекта базы данных выполнена с
помощью операторов языка SQL: USE, CRETE, DROP, UPDATE, ALTER, INSERT
Текст программы создания базы данных приведён в приложении А.
Для спроектированной базы данных средствами СУБД Microsoft SQL Server 2008 построена
диаграмма, которая приведена в приложении Б.
Текст программы ввода тестовых данных приведён в приложении
В.
1.6
Разработка хранимых процедур для поддержки сложных ограничений целостности в
базе данных
Для облегчения работы с БД и реализации сложных ограничений
были разработаны следующие процедуры:
. Процедура без параметров.
Процедура выводит информацию об актерской династии Исаковых.
create Procedure AllActorsSelect Actors. Surname
As "Фамилия",
Actors. First_Name As "Имя", Actors. Last_Name As "Отчество",.
Expirience As "Стаж", Ranks. Title_Rank As "Звание" From
Actors Inner Join Ranks ON Actors. ID_Rank = Ranks. ID_RankActors. Surname = 'Исаков';
Обращение к
процедуре: AllActors;
Рисунок 2
. Процедура с параметром.
Процедура для получения данных об актеров по званию.
create procedure AllActorsSelect
@actor_rank char (16)Actors. Surname As
"Фамилия", Actors. First_Name As
"Имя", Actors. Last_Name As
"Отчество",. Expirience As "Стаж", Ranks. Title_Rank As "Звание" From Actors, RanksActors. ID_Rank =
Ranks. ID_Rank And Ranks. Title_Rank In
(Select Title_Rank From Ranks
Обращение к процедуре: AllActorsSelect'Артист';
Рисунок 3
. Процедура с параметрами.
Процедура для получения информации об актере по фамилии и стажу.
create procedure AllActorsData_Actor
@actor_expirience date, @actor_surname
char (40)Select Actors. Surname As "Фамилия",
Actors. First_Name As "Имя", Actors. Last_Name As
"Отчество",. Expirience As "Стаж", Ranks. Title_Rank As "Звание" From Actors Inner Join Ranks ON
Actors. ID_Rank = Ranks. ID_RankActors. Expirience = @actor_expirience and
Actors. Surname = @actor_surname;
Обращение к процедуре:
execute AllActorsData_Actor '19900112', 'Сидоров';
Рисунок 4
. Процедура с параметром и значением по умолчанию.
Процедура поиска актеров по маске.
create procedure AllActorsDefaultData
@mask varchar (40) = '%', @ranktitle
varchar (40) = '%'Select Actors. Surname As "Фамилия", Actors. First_Name As "Имя", Actors. Last_Name As "Отчество",. Expirience As "Стаж",. Title_Rank As "Звание" From Actors, Ranks(Surname like
@mask) and (Actors. ID_Rank = Ranks. ID_Rank) and (Title_Rank like @ranktitle);
Обращение к процедуре:AllActorsDefaultData;
Рисунок
5
AllActorsDefaultData 'Ше%';
Рисунок
6
AllActorsDefaultData '%г%', 'Народный%';
Рисунок 4
. Процедура с входными и выходными значениями.
Процедура для вывода количества актеров определенного звания.
create procedure AmountActors
@number Int Output,
@rank varchar (40)Select @number = Count
(*)Actors Inner Join Ranks On (Ranks. ID_Rank = Actors. ID_Rank)(Ranks.
Title_Rank = @rank);
Обращение к процедуре:
Declare @out_num int
execute AmountActors @out_num OUTPUT, 'Народный артист''Колво актеров: ' Print CAST (@out_num as
Int);
Рисунок 8
. Использование вложенных процедур.
Процедура для вывода жанра спектакля и ролей в спектакле по
названию спектакля.
Процедура определения жанра спектакля:
create procedure GenreRepresentation
@representation varchar (40),
@genre varchar (40) OUTPUT@genre = Genres.
Title_GenreGenres, Representations(Representations. ID_Genre = Genres.
ID_Genre)(Representations. Title_Representation = @representation);
Процедура определения жанра спектакля и его ролей:
create procedure GenreRepresentation_Roles
@representation varchar (40),
@genre varchar (40) OUTPUT,
@role_ varchar (40) OUTPUT@R_genre varchar
(40) Select @R_genre = Genres. Title_Genre,
@role_ = Actors_Employments. Role_ From
Genres, Actors_Employments, Representations(Representations.
Title_Representation = @representation) And (Representations. ID_Genre =
Genres. ID_Genre)(Actors_Employments. ID_Representation = Representations.
ID_Representation)GenreRepresentation @representation, @genre OUTPUT;
Обращение к процедуре:
DECLARE @genre varchar (40), @role_ varchar (40)
Execute GenreRepresentation_Roles 'Недоросль', @genre OUTPUT, @role_ OUTPUT
Print ('Жанр: ' +@genre)('Роли: ' +@role_);
Рисунок 9
1.7
Разработка триггеров для поддержки сложных ограничений целостности в базе
данных
Для поддержания логической целостности базы данных и
реализации сложных ограничений были разработаны следующие триггеры:
. При добавлении записи о спектакле в таблицу Representations, проверяется, чтобы год
начала проката не был больше 2015 года.
create trigger ForRepresentations1Representations
for INSERTDECLARE @id int@@ROWCOUNT = 1@id = ID_RepresentationINSERTED2015 >
(Select COUNT (Year_)INSERTEDID_Representation = @id)'Запись
добавлена'transaction'Неправильно введен год'
END
END
Запрос для проверки работы триггера.
Insert Into Representations
(Title_Representation, ID_Genre, Year_, Budget)
Values
('Ночь на Волге','2','2020','900000');
Рисунок 5
. При добавлении записи о новом актере, проверяется, чтобы его
стаж по году был меньше 2016.
create trigger ForActors1Actors for
InsertDeclare @expirience dateIF
@@ROWCOUNT = 1@expirience =
ExpirienceINSERTED2016 > (Select YEAR (Expirience)INSERTEDExpirience =
@expirience)('Запись добавлена')transaction('Неверная дата')
end
Запрос для проверки работы триггера.
Insert Into Actors
(Surname,First_Name,Last_Name,
ID_Rank,Expirience) Values
('Грибоедов','Сергей','Михайлович','2','20.01.2017');
Рисунок 11
. Триггер для команды UPDATE,
при смене значения бюджета спектакля, старое и новое значение бюджета отдельно
сохраняются в другую таблицу.
Создадим таблицу:table LogsRepresentations
(_ID integer identity (1,1) Not Null
Primary Key,_representation varchar (50) Not Null Check ( (Title_representation
BETWEEN 'А' AND 'я') OR (Title_representation=' ') OR
(Title_representation='-') OR (Title_representation=',')),_Budget int Not Null
Check (Old_Budget between 200000 and 2000000000),_Budget int Not Null Check
(New_Budget between 200000 and 2000000000),_date date Not Null default GETDATE
()
);
Создадим триггер:trigger
ForLogsRepresentationsBudgetRepresentations after UPDATE@@ROWCOUNT =
1@old_budget int@new_budget int@title_repres varchar (40)@new_budget = Budget
from Inserted@old_budget = Budget from deleted@title_repres =
Title_Representation from insertedINTO LogsRepresentations
(Title_representation, Old_Budget, New_Budget)(@title_repres, @old_budget,
@new_budget)
END
Запрос для проверки работы триггера:
Update Representations set Budget =
3000000ID_Representation = 7;
Результаты:
Основная таблица Representations
до транзакции
Рисунок 12
Рисунок 6
Дополнительная таблица после транзакции
Рисунок 7
. Триггер с процедурой, при удалении информации об актере будет
выводиться специальное сообщение о произведенной транзакции и будут выводиться
Имя, Фамилия и Отчество удаленного актера
Создадим процедуру для триггера:procedure DeletingActor
@name Varchar (100)Print 'Удалена запись об актере'+ @name;
Создадим Триггер с использованием нашей хранимой процедуры:
create trigger DeletingActor_1Actors For
DELETE
AS@@ROWCOUNT = 1'Из БД была удалена информация'
Select ID_Actor As 'ID_aktera',As
'Familiya',_Name As 'Imya',_Name As 'Otchestvo',_Rank As 'ID_zvaniya',As
'Akterskiy stazh'Deleted@f varchar (50)@f = Surname + First_Name +
Last_NameDELETEDDeletingActor @f;
Вид таблицы до транзакций:
Рисунок 8
Напишем запрос на удаление в таблицу Actors
DELETE From ActorsID_Actor = 41;
Рисунок 9
Рисунок 10
Таблица после транзакции:
Рисунок 11
1.8 Запросы
Все запросы на получение практически любого количества данных
из одной или нескольких таблиц выполняются с помощью предложения SELECT. В общем случае
результатом реализации предложения SELECT является другая таблица.
В курсовой работе разработаны следующие запросы:
a) Запрос выбирает ФИО актеров из таблицы Actors, которые заняты в
спектакле "Любовь и Голуби".
b) Запросы выбирает названия спектаклей из таблицы Representations, в которых занят Актер с
фамилией Тарасеев.
c) Запросы выбирает ФИО актера, который играет роль Стародума.
d) Запрос выбирает название спектакля, в котором играет актер
с фамилией Шехтман.
e) Запрос выбирает роли из таблицы Actors_Employments, которых играют в
спектакле "Руслан и Людмила".
f) Запрос выбирает всех актеров без особых званий.
g) Запрос выбирает всех актеров со званием народный артист.
h) Запрос выбирает спектакли жанра оперы.
i) Запрос выбирает роли из таблицы Actors_Employments, которые участвуют в
спектакле "Недоросль", и звания актеров Народный артист.
j) Запрос выбирает актеров из таблицы Actors_Employments, которые играют в
спектакле жанра оперы и с названием "Руслан и Людмила".
k) Запрос выбирает актеров со званием Народный артист со
стажем более 40 лет.
1.9
Представления
. Создание необновляемого представления, скрываем наличие
других полей в базовой таблице Actors, так же изменены имена столбцов.
Create View Actors_1 AsSurname As "Фамилия",
First_Name As "Имя", Last_Name As "Отчество", Expirience As
"Дата начала выступлений в театре"
From Actors;
Рисунок 12
Покажем, что представление №1 является не обновляемым. Напишем к
нему запрос на обновление фамилии у актера, отчество которого Леонидович. Оно
не обновляемо, потому что в него не включены все столбцы таблицы Actors со свойством NOT NULL.
use Theater;Into Actors_1
("Фамилия", "Имя", "Отчество",
"Дата начала выступлений в театре")
Values
('Свистунов','Олег','Афанасьевич', '1990.10.10');
Результат запроса:
Рисунок 13
Фамилия не обновилась.
. Представление выводит названия спектаклей и количество
участвующих в них актерах, представление не обновляемо.
Create View ActorsEployments_2
AsRepresentations. Title_Representation As "Спектакль",COUNT (Actors_Employments. ID_Representation) As "Кол-во актеров"Representations,
Actors_EmploymentsRepresentations. ID_Representation = Actors_Employments.
ID_Representation AND Actors_Employments. ID_Representation =
(Select Representations. ID_Representation
From Representations Where Representations. ID_Representation =
Actors_Employments. ID_Representation)By Representations. Title_Representation;
Рисунок 14
Представление не будет являться обновляемым, В нем используется
несколько таблиц и агрегирующая функция GROUP BY.
use Theater;ActorsEployments_2"Кол-во актеров" = '4'
Where "Кол-во актеров" = '3';
Результат запроса:
Рисунок 15
Обновление не было выполнено.
. Представление показывающее спектакли и их жанры.
Create View Representations_1
AsRepresentations. Title_Representation, Genres. Title_GenreRepresentations
Inner Join Genres ON. ID_Genre = Genres. ID_Genre;
Рисунок 16
Представление не обновляемое, основано на двух таблицах.
use Theater;Into Representations_1
("Title_Representation",
"Title_Genre")
('Снегурочка','Сказка');
Результат запроса:
Рисунок 17
4. Представление, показывающее ФИО актеров и их Роли, которые они
играют в разных спектаклях.
create View Actors_RolesSelect Actors.
Surname As "Фамилия",. First_Name As "Имя",. Last_Name As "Отчество",_Employments. Role_ As "Роль",. Title_Representation As "Спектакль"Actors, Actors_Employments,
RepresentationsActors. ID_Actor = Actors_Employments. ID_Actor AND_Employments.
ID_Representation = Representations. ID_Representation;
Рисунок 18
Представление не будет обновляемо, так как основано на трех
таблицах.Into Actors_Roles
(Фамилия, Имя, Отчество, Роль, Спектакль)
('Драгункина','Елена','Кирилловна','Снегурочка','Морозко');
Рисунок 19
Заключение
В ходе курсового проектирования разработана и реализована
база данных и серверная часть информационной системы Склада.
Разработана структура, состоящая из 6 таблиц.
Разработаны ограничения целостности для сохранения логической
непротиворечивости данных в системе.
Реализованы наиболее часто употребляемые в данной предметной
области запросы.
Разработаны и отлажены хранимые процедуры, упрощающие работу
с БД.
Разработаны и отлажены триггеры, осуществляющие проверку
сложных логических условий и синхронизацию таблиц между собой при их изменении.
Разработаны и реализованы представления, повышающие комфорт и
безопасность работы с системой.
В итоге, были успешно реализованы все особенности предметной
области и требования, выработанные на этапе проектирования.
Список
использованных источников
1. Ицик Бен-Ган - Microsoft SQL Server
2008. Основы T-SQL -
2009
2. Оутей
М., Конте П. Эффективная работа: SQL Server 2000. СПб,
2002.
. Грофф
Дж., Вайнберг П. SQL: Полное
руководство. / 2-е изд. - К., 2001.
. Мамаев
Е., Шкарина Л. Microsoft SQL Server 2000 для профессионалов. - СПб., 2001.
. Мартин
Грабер. Понимание SQL. - Москва,
2005.
Приложения
Приложение А
Программа создания базы данных
Create database Theater
(Name = database_theater_dat,= 'D: \sql\Teatr\database_theater.
mdf',= 10,MAXSIZE = 50,FILEGROWTH = 5)ON
(Name = database_theater_log,= 'D:
\sql\Teatr\database_theater. mdf',= 5MB,= 25MB,= 5MB);Table Ranks
(_Rank Integer Not Null Primary Key Check
(ID_Rank between 1 and 3),_Rank VarChar (50) Not Null Check (Title_Rank between
'А' AND 'я')
);Table Actors
(_Actor Integer Not NUll Primary key Check
(ID_Actor Between 0001 and 9999),VarChar (30) Not NUll Check ( (Surname BETWEEN
'А' AND 'я') OR
(Surname='-')),_Name VarChar (30) Not NUll Check (First_Name BETWEEN 'А' AND 'я'),_Name VarChar (30) Not
NUll Check (Last_Name BETWEEN 'А' AND 'я'),_Rank Integer Not Null Foreign Key (ID_Rank) References Ranks
Check (ID_Rank between 1 and 3),Datetime Not Null
);Table Genres
(_Genre Integer Not Null Primary Key Check
(ID_Genre between 1 and 9),_Genre VarChar (25) Not Null Check (Title_Genre
BETWEEN 'А' AND 'я')
);Table Representations
(_Representation Integer Not Null Primary Key
Check (ID_Representation between 00001 and 99999),_Representation VarChar (50)
Not Null Check ( (Title_Representation BETWEEN 'А' AND 'я') OR (Title_Representation=' ') OR
(Title_Representation='-') OR (Title_Representation=',')),_ INTEGER Not NULL
Check (Year_ between 1960 and 2030),_Genre Integer Not Null FOREIGN key
(ID_Genre) REFERENCES Genres Check (ID_Genre between 1 and 9),Integer Not Null
Check (Budget between 200000 and 2000000000)
);Table Actors_Employments
(_Actors_Employments Integer Not NUll Primary key
Check (ID_Actors_Employments Between 0001 and 9999),_Actor Integer Not Null
Foreign Key (ID_Actor) References Actors Check (ID_Actor Between 0001 and
9999),_Representation Integer Not Null Foreign Key (ID_Representation)
References Representations Check (ID_Representation between 0001 AND 9999),VarChar
(50) Not Null Check (Role between 'А' AND 'я')
);
Приложение Б
Диаграмма базы данных
Рисунок 27 - Структура БД
Приложение В
Программа ввода тестовых данных
use TheaterINTO Ranks
(Title_Rank)
VALUES
('Народный артист'),
('Заслуженный артист'),
('Артист');INTO Actors
(Surname,First_Name,Last_Name,
ID_Rank,Expirience)
VALUES
('Иванов', 'Николай', 'Николаевич', '1', '22.01.1990'),
('Петров', 'Алексей', 'Федорович', '2', '22.01.1990'),
('Сидоров', 'Александр', 'Александрович', '3', '12.01.1990'),
('Алексеев', 'Дмитрий', 'Иванович', '3', '22.01.1995'),
('Федоров', 'Дмитрий', 'Леонидович', '3', '22.01.1997'),
('Шехтман', 'Вячеслав', 'Максимович', '1', '22.01.1980'),
('Малахов', 'Алексей', 'Сергеевич', '1', '11.01.1980'),
('Иванов', 'Сергей', 'Федорович', '2', '22.06.1991'),
('Иванова', 'Ольга', 'Андреевна', '1', '30.11.1976'),
('Уварова', 'Екатерина', 'Васильевна', '3', '29.11.2006'),
('Урванова', 'Дарья', 'Андреевна', '3', '28.11.2007');
('Куприянов', 'Геннадий', 'Иванович', '1', '22.03.1778'),
('Ганяев', 'Максим', 'Анатольевич', '1', '01.01.1770'),
('Подопригора', 'Кирилл', 'Аркадьевич', '3', '20.01.2005'),
('Маслов', 'Максим', 'Александрович', '3', '22.06.2009'),
('Тарасеев', 'Александр', 'Игоревич', '1', '30.05.1950'),
('Галкин', 'Евгений', 'Андреевич', '2', '11.09.1980'),
('Афанасьев', 'Алексей', 'Иванович', '2', '11.09.1980'),
('Глебов', 'Александр', 'Дмитриевич', '1', '20.08.1950'),
('Просвирин', 'Александр', 'Сергеевич', '2', '20.09.1990'),
('Исаков', 'Геннадий', 'Сергеевич', '1', '20.06.1967'),
('Исаков', 'Сергей', 'Геннадьевич', '2', '31.05.1990'),
('Исаков', 'Александр', 'Сергеевич', '3', '20.05.2011'),
('Меркулова', 'Ольга', 'Андреевна', '2', '14.11.1980'),
('Козлова', 'Екатерина', 'Васильевна', '3', '20.12.1998');
INSERT INTO Genres
(Title_Genre)
('Комедия'),
('Драма'),
('Мелодрама'),
('Опера'),
('Мюзикл'),
('Трагедия');INTO Representations
(Title_Representation,Year_, ID_Genre,Budget)
VALUES
('Недоросль', '1980', '1', '7250000'),
('Ревизор', '1980', '2', '8500000'),
('Любовь и Голуби', '1970', '3', '600000'),
('Руслан и Людмила', '1960', '4', '4450000'),
('Три Мушкетера', '2002', '5', '700000'),
('Ромео и Джульетта', '1975', '6', '13890000');
('Гроза', '1990', '2', '2450000'),
('Пиковая Дама', '1995', '4', '39000000'),
('Борис Годунов', '1989', '6', '11600000'),
('Граф Орлов', '2000', '5', '7900000');
INSERT INTO Actors_Employments
(ID_Actor, ID_Representation,Role_)
VALUES
('1', '1', 'Простаков'),
('2', '1', 'Стародум'),
('3', '2', 'Абдулин'),
('4', '2', 'Свистунов'),
('5', '3', 'Василий Кузякин'),
('6', '3', 'Дядя Митя'),
('7', '4', 'Руслан'),
('9', '4', 'Финн'),
('8', '5', 'Атос'),
('8', '6', 'Ромео'),
('10', '6', 'Джульетта'),
('11', '4', 'Людмила');
('13', '1', 'Цыфиркин'),
('14', '1', 'Митрофан'),
('15', '1', 'Вральман'),
('16', '7', 'Шапкин'),
('17', '7', 'Кулигин'),
('18', '10', 'Граф Орлов'),
('19', '10', 'Доманский'),
('20', '8', 'Николай Фигнер'),
('21', '8', 'Иван Мельников'),
('22', '9', 'Пимен'),
('23', '9', 'Гришка Отрепьев'),
('24', '2', 'Мария Антоновна'),
('25', '10', 'Екатерина 2'),
('26', '8', 'Графиня Мария Славина'),
('27', '7', 'Глаша'),
('28', '9', 'Ксения Годунова'),
('29', '4', 'Наина'),
('30', '6', 'Кормилица');
Приложение Г
Реализация запросов на SQL
. ФИО актеров из таблицы Actors, которые участвуют в
спектакле "Любовь и Голуби".
Select Surname,First_Name,Last_Name From Actors
Where ID_Actor In
(Select ID_Actor From Actors_Employments Where
ID_Representation In
(Select ID_Representation From Representations
Where Title_Representation = 'Любовь и Голуби'));
Рисунок 208
. Названия спектаклей из таблицы Representations, в которых занят Актер с фамилией
Тарасеев.
Select Title_Representation From
Representations Where ID_Representation In
(Select ID_Representation From
Actors_Employments Where ID_Actor IN
(Select ID_Actor From Actors Where Surname
= 'Тарасеев'));
Рисунок 29
. ФИО актера из таблицы Actors,
который играет роль Стародума.
Select Surname,First_Name,Last_Name From
Actors Where ID_Actor In
(Select ID_Actor From Actors_Employments
Where Role_ = 'Стародум');
Рисунок 21
. Название спектакля из таблицы Representations, в котором играет актер Шехтман.
Select Title_Representation From
Representations Where ID_Representation In
(Select ID_Representation From
Actors_Employments Where ID_Actor In
(Select ID_Actor From Actors Where Surname
= 'Шехтман'));
Рисунок 31
. Роли из таблицы Actors_Employments, участвующих в спектакле "Руслан и
Людмила".
Select Role_ from Actors_Employments where
ID_Representation =
(Select ID_Representation from
Representations where Title_Representation = 'Руслан и Людмила');
Рисунок 32
. Актеры без особых званий.
Select Surname, First_Name, Last_Name from
Actors where ID_Rank In
(Select ID_Rank from Ranks where
Title_Rank = 'Артист');
Рисунок 223
. Актеры со званием Народный артист.
Select Surname, First_Name, Last_Name from
Actors where ID_Rank =
(Select ID_Rank from Ranks where
Title_Rank = 'Народный артист');
Рисунок 234
. Спектакли жанра Опера.Title_Representation from
Representations where ID_Genre =
(Select ID_Genre from Genres where
Title_Genre = 'Опера');
Рисунок 245
. Роли из таблицы Actors_Employments, которые участвуют в спектакле
"Недоросль" и с актерскими званиями Народный артист.
Select Role_ from Actors_Employments where
ID_Representation In
(Select ID_Representation From
Representations where Title_Representation = 'Недоросль' and ID_Actor In
(Select ID_Actor From Actors Where ID_Rank
=
(Select ID_Rank From Ranks Where
Title_Rank = 'Народный артист')));
Рисунок 256
. Актеры из таблицы Actors_Employments, которые играют в спектакле жанра Опера с
названием "Руслан и Людмила".
Select Surname, First_Name, Last_Name from
Actors where ID_Actor In
(Select ID_Actor from Actors_Employments
where ID_Representation In
(Select ID_Representation From
Representations Where Title_Representation = 'Руслан и Людмила' and ID_Genre In
(Select ID_Genre from Genres where
Title_Genre = 'Опера')));
Рисунок 267
. Актеры со стажем более 40 лет и с актерским званием Народный
Артист.
Select Surname, First_Name, Last_Name from
Actors where (DATEDIFF (year, Expirience, '2015.01.01') > 40) and (ID_Rank =
(Select ID_Rank from Ranks where
Title_Rank = 'Народный артист'));
Рисунок 278