Задание 26 Создать базу данных «РАБОТА ДЕКАНАТА в ВУЗе». Возможные таблицы: факультеты, группы, студенты, кафедры, преподаватели, предметы.
1. Структура базы данных
Логическая структура разрабатываемой базы данных должна содержит 6 сущностей: факультет; кафедра; преподаватель; дисциплина; группа; студент. В системе Erwin были проведены следующие действия: 1) Была создана новая модель данных (тип модели – «Логическая / Физическая», целевая база данных – «MS Access»). 2) В логической модели были созданы 6 сущностей. 3) В логической модели были созданы необходимые связи «один ко многим» между сущностями. 4) Для созданных связей были установлены правила для ссылок как RESTRICT так и CASCADE. Полученная, в итоге, модель Erwin, приведенная на рисунке 1, является графическим представлением логической структуры разрабатываемой базы данных.
Рисунок 1-Логическая структура базы данных
Используя средство системы Erwin «Forward Engineer» для экспортирования структуры в СУБД были созданы необходимые таблицы с заданными параметрами.
Рисунок 2 – Физическая структура разрабатываемой базы данных
После выполнения прямого проектирования проверим результат:
GO ALTER TABLE [dbo].[кафедра] WITH CHECK ADD CONSTRAINT [FK_кафедра_факультет] FOREIGN KEY ([КодКафедры]) REFERENCES [dbo].[факультет] ([КодФакультета])
CREATE TABLE [dbo].[преподаватель] ( [КодПреподавателя] [INT] IDENTITY (1,1) NOT NULL, [КодКафедры] [INT] NOT NULL, [КодФакультета] [INT] NOT NULL, [ФИО] [nvarchar](50) NOT NULL, [УченаяСтепень] [nvarchar](30) NOT NULL, [УченоеЗвание] [nvarchar] (10) NOT NULL, [Должность] [nvarchar](30) NOT NULL, [Стаж] [int] NOT NULL CONSTRAINT [PK_преподаватель] PRIMARY KEY CLUSTERED ( [КодПреподавателя] ASC, [КодКафедры] ASC, [КодФакультета] ASC) )
GO ALTER TABLE [dbo].[преподаватель] WITH CHECK ADD CONSTRAINT [FK_преподаватель_кафедра] FOREIGN KEY ([КодКафедры],[КодФакультета]) REFERENCES [dbo].[кафедра] ([КодКафедры],[КодФакультета])
CREATE TABLE [dbo].[дисциплина] ( [КодДисциплины] [INT] NOT NULL, [КодПреподавателя] [INT] NOT NULL, [КодКафедры] [INT] NOT NULL, [КодФакультета] [INT] NOT NULL, [Название] [nvarchar](30) NOT NULL, [ОбъемЧасов] [INT] NOT NULL CONSTRAINT [PK_КодДисциплины] PRIMARY KEY CLUSTERED ([КодДисциплины] ASC, [КодПреподавателя] ASC, [КодКафедры] ASC, [КодФакультета] ASC ) )
GO ALTER TABLE [dbo].[дисциплина] WITH CHECK ADD CONSTRAINT [FK_дисциплина_преподаватель]FOREIGN KEY ([КодПреподавателя],[КодКафедры],[КодФакультета]) REFERENCES [dbo].[преподаватель] ([КодПреподавателя],[КодКафедры],[КодФакультета])
CREATE TABLE [dbo].[студент] ( [КодСтудента] [INT] IDENTITY (1,1) NOT NULL, [КодГруппы] [INT] NOT NULL, [КодФакультета] [INT] NOT NULL, [ФИО] [nvarchar] (50) NOT NULL, [ДатаРождения] [datetime] NOT NULL, [Адрес] [nvarchar] (40) NOT NULL, [Пол] [nvarchar] (4)NOT NULL CONSTRAINT [DF_студент_пол] DEFAULT ('М'), [СреднийБалл] [float] NOT NULL CONSTRAINT [PK_студент] PRIMARY KEY CLUSTERED ([КодСтудента] ASC, [КодГруппы]ASC, [КодФакультета] ASC), CONSTRAINT [FK_группа] FOREIGN KEY ([КодГруппы],[КодФакультета]) REFERENCES [dbo].[группа] ([КодГруппы],[КодФакультета]) )
GO ALTER TABLE [dbo].[студент] WITH CHECK ADD CONSTRAINT [CK_студент_пол] CHECK (([Пол]='М' OR [Пол]='Ж')) GO
CREATE UNIQUE INDEX UIX_факультет ON факультет (Наименование) ON Secondary CREATE UNIQUE INDEX UIX_кафедра ON кафедра (Наименование) ON Secondary CREATE UNIQUE INDEX UIX_группа ON группа (НазваниеГруппы) ON Secondary CREATE UNIQUE INDEX UIX_дисциплина ON дисциплина (название) ON Secondary CREATE INDEX IX_студент ON студент (ФИО, ДатаРождения, Пол, Адрес, СреднийБалл) ON Secondary CREATE INDEX IX_Услуга ON преподаватель (ФИО, УченаяСтепень, УченоеЗвание, Должность, Стаж) ON Secondary GO
INSERT INTO [dbo].[факультет] ([Наименование]) VALUES ('энергетический')
INSERT INTO [dbo].[факультет] ([Наименование]) VALUES ('машиностроительный')
INSERT INTO [dbo].[факультет] ([Наименование]) VALUES ('механико-технологический')
INSERT INTO [dbo].[факультет] ([Наименование]) VALUES ('гуманитарно-экономический')
INSERT INTO [dbo].[факультет] ([Наименование]) VALUES ('автоматизированных и информационных систем')
INSERT INTO [dbo].[кафедра] ([КодФакультета], [Наименование]) VALUES ('1','Промышленная теплоэнергетика и экология')
INSERT INTO [dbo].[кафедра] ([КодФакультета], [Наименование]) VALUES ('1','Теоретические основы электротехники') INSERT INTO [dbo].[кафедра] ([КодФакультета], [Наименование]) VALUES ('4','Экономика')
INSERT INTO [dbo].[кафедра] ([КодФакультета], [Наименование]) VALUES ('1','Физика')
INSERT INTO [dbo].[кафедра] ([КодФакультета], [Наименование]) VALUES ('2','Гидропневмоавтоматика')
INSERT INTO [dbo].[группа] ([КодФакультета], [НазваниеГруппы]) VALUES ('4','ОП-11')
INSERT INTO [dbo].[группа] ([КодФакультета], [НазваниеГруппы]) VALUES ('4','УП-11')
INSERT INTO [dbo].[группа] ([КодФакультета], [НазваниеГруппы]) VALUES ('1','ТЭ-51')
INSERT INTO [dbo].[группа] ([КодФакультета], [НазваниеГруппы]) VALUES ('1','ТЭ-52')
INSERT INTO [dbo].[студент] ([КодГруппы],[КодФакультета], [ФИО], [ДатаРождения], [Адрес], [Пол], [СреднийБалл]) VALUES ('4','1','Дацюк Андрей Александрович','18.01.1985','г.Бобруйск, ул.Ульяновская, д.7, кв.15','М','7.5') INSERT INTO [dbo].[студент] ([КодГруппы],[КодФакультета], [ФИО], [ДатаРождения], [Адрес], [Пол], [СреднийБалл]) VALUES ('4','1','Капралова Ольга Алексеевна','24.06.1989','г.Гомель, ул.2-я линейная, д.51/2','Ж','7.9') INSERT INTO [dbo].[студент] ([КодГруппы],[КодФакультета], [ФИО], [ДатаРождения], [Адрес], [Пол], [СреднийБалл]) VALUES ('4','1','Ковалев Дмитрий Александрович','25.01.1989','г.Гомель, ул.Студ.проезд, д.19, кв.65','М','6.9') INSERT INTO [dbo].[студент] ([КодГруппы],[КодФакультета], [ФИО], [ДатаРождения], [Адрес], [Пол], [СреднийБалл]) VALUES ('4','1','Никулина Татьяна Николаевна','27.02.1989','г.Гомель, ул.Студ.проезд, д.21, кв.119','Ж','8.1')
INSERT INTO [dbo].[преподаватель] ([КодКафедры],[КодФакультета], [ФИО], [УченаяСтепень], [УченоеЗвание], [Должность], [Стаж]) VALUES ('1','1','Овсянник Анатолий Васильевич','к.т.н.','доцент','заведующий кафедрой','19')
INSERT INTO [dbo].[преподаватель] ([КодКафедры],[КодФакультета], [ФИО], [УченаяСтепень], [УченоеЗвание], [Должность], [Стаж]) VALUES ('1','1','Шаповалов Александр Валерьевич','к.т.н.','доцент','старший преподаватель','8')
INSERT INTO [dbo].[дисциплина] ([КодДисциплины],[КодПреподавателя],[КодКафедры],[КодФакультета], [Название], [ОбъемЧасов]) VALUES ('1','1','1','1','Техническая термодинамика','98')
INSERT INTO [dbo].[дисциплина] ([КодДисциплины],[КодПреподавателя],[КодКафедры],[КодФакультета], [Название], [ОбъемЧасов]) VALUES ('2','1','1','1','Экология','32')
INSERT INTO [dbo].[дисциплина] ([КодДисциплины],[КодПреподавателя],[КодКафедры],[КодФакультета], [Название], [ОбъемЧасов]) VALUES ('3','3','3','4','Экономика организации','144') INSERT INTO [dbo].[дисциплина] ([КодДисциплины],[КодПреподавателя],[КодКафедры],[КодФакультета], [Название], [ОбъемЧасов]) VALUES ('4','3','3','4','ЭПМ','102')
GO
3. Создание процедур
1. Создайте хранимую процедуру, которая для указанной таблицы подсчитывает число строк, содержащихся в этой таблице. Эта процедура должна иметь один входной параметр (с помощью которого задается имя таблицы) и один выходной параметр.
/*Создание хранимой процедуры подсчёта числа строк в указанной таблице*/ CREATE PROCEDURE pr_count @table VARCHAR(20), @count INT OUTPUT AS DECLARE @sql NVARCHAR(255) SET @sql = 'SELECT @count = COUNT(*) FROM ' + @table exec sp_executesql @sql, N'@count int out', @count=@count out GO
/*Проверка хранимой процедуры подсчёта числа строк в указанной таблице*/ DECLARE @Count INT, @Table VARCHAR(20) SET @Table = 'студент' EXECUTE pr_count @Table, @Count OUTPUT SELECT @Table AS [Название таблицы], @Count AS [Количество записей в таблице] GO
Рисунок 4 – Результат работы
2. Создайте хранимую процедуру, которая изменяет все даты, имеющиеся в таблицах базы данных, добавляя к ним или отнимая от них некоторое число дней. Эта процедура должна иметь два входных параметра. Первый параметр определяет, нужно ли увеличивать или уменьшать все даты. Второй параметр указывает количество дней, которые должны добавляться или отниматься. Примечание. Если в таблицах отсутствуют даты, то аналогичную операцию необходимо проделать применительно ко всем имеющимся ценам и стоимостям. При этом второй параметр будет задавать процент, на который должны измениться все цены и стоимости.
/*Создание хранимой процедуры изменения всех дат*/ CREATE PROCEDURE pr_date @pom SMALLINT, --plus or minus @date SMALLINT AS UPDATE студент SET ДатаРождения = ДатаРождения + @pom*@date GO
/*Проверка хранимой процедуры изменения всех дат*/ SELECT * FROM студент DECLARE @pom SMALLINT, @date SMALLINT SET @pom=1 SET @date=30 EXECUTE pr_date @pom, @date SELECT * FROM студент GO
Рисунок 5 – Результат работы
3. Создайте хранимую процедуру, которая для конкретной таблицы подсчитывает минимальное, среднее, максимальное и суммарное значения в столбце числового типа. Эта процедура должна иметь один входной параметр (с помощью которого указывается имя или номер столбца) и четыре выходных параметра.
/*Создание хранимой процедуры подсчёта минимального, среднего, максимального и суммарного значения */ CREATE PROCEDURE pr_min_max_sr_sum @column VARCHAR(20), @min DECIMAL(10,2) OUTPUT, @max DECIMAL(10,2) OUTPUT, @sum DECIMAL(10,2) OUTPUT, @avg DECIMAL(10,2) OUTPUT AS DECLARE @sql NVARCHAR(255) SET @sql = 'SELECT @min = MIN(' + @column + ') FROM преподаватель' exec sp_executesql @sql, N'@min DECIMAL(10,2) out', @min=@min out SET @sql = 'SELECT @max = MAX(' + @column + ') FROM преподаватель' exec sp_executesql @sql, N'@max DECIMAL(10,2) out', @max=@max out SET @sql = 'SELECT @sum = SUM(' + @column + ') FROM преподаватель' exec sp_executesql @sql, N'@sum DECIMAL(10,2) out', @sum=@sum out SET @sql = 'SELECT @avg = AVG(' + @column + ') FROM преподаватель' exec sp_executesql @sql, N'@avg DECIMAL(10,2) out', @avg=@avg out GO
/*Проверка хранимой процедуры подсчёта минимального, среднего, максимальногое и суммарного значения */ DECLARE @Column VARCHAR(20) SET @Column = 'Стаж' DECLARE @min DECIMAL(10,2) DECLARE @max DECIMAL(10,2) DECLARE @sum DECIMAL(10,2) DECLARE @avg DECIMAL(10,2) EXEC pr_min_max_sr_sum @Column, @min OUTPUT, @max OUTPUT, @sum OUTPUT, @avg OUTPUT SELECT @Column AS [Название столбца], @min AS [Минимальное значение], @max AS [Максимальное значение], @sum AS [Сумма значений], @avg AS [Среднее значение] GO
Рисунок 6 – Результат работы
4. Создайте хранимую процедуру, которая из конкретной таблицы отбирает строки по условию, налагаемому на значения конкретных двух столбцов. Эта процедура должна иметь два входных параметра, задающих значения для отбора данных по каждому из этих столбцов. При этом значение NULL должно трактоваться как отсутствие какого-либо критерия отбора строк по данному столбцу (например, если оба параметра будут иметь значение NULL, то должны быть выбраны все строки таблицы).
/*Создание хранимой процедуры по отбору строк по условию*/ CREATE PROCEDURE pr_yslov @date DATETIME, @sr_b INT AS IF @date IS NOT NULL IF @sr_b IS NOT NULL SELECT * FROM студент WHERE ДатаРождения > @date AND СреднийБалл > @sr_b ELSE SELECT * FROM студент WHERE ДатаРождения > @date ELSE IF @sr_b IS NOT NULL SELECT * FROM студент WHERE СреднийБалл > @sr_b ELSE SELECT * FROM студент GO
/*Проверка хранимой процедуры по отбору строк по условию*/ SELECT * FROM студент DECLARE @Date DATETIME, @Sr_b INT SET @Date='1.1.1988' /*getdate()*/ SET @Sr_b=7.4 EXECUTE pr_yslov @Date, @Sr_b GO
Рисунок 7 – Результат работы
5. Создайте хранимую процедуру, которая на основе текстовой строки, содержащей фамилию, имя и отчество, формирует текстовую строку, содержащую фамилию и инициалы, и при этом все строчные буквы должны быть заменены прописными. Эта процедура должна иметь один входной параметр (с помощью которого задается исходная текстовая строка) и один выходной параметр. При этом необходимо расширить возможности процедуры таким образом, чтобы была допустима исходная строка (задаваемая входным параметром), содержащая не один, а несколько пробелов между фамилией и именем или между именем и отчеством, а также допускающая наличие лидирующих пробелов перед фамилией. Примечание. Некоторые их указанный выше хранимых процедур можно, по желанию, оформить в виде соответствующих пользовательских функций. /*Создание пользовательской функции по формированию текстовой строки*/ CREATE FUNCTION fio (@str VARCHAR(50)) RETURNS VARCHAR(30) BEGIN DECLARE @str_gen VARCHAR(35) DECLARE @position TINYINT SET @str = LTRIM(@str) SET @str = RTRIM(@str) SET @position = CHARINDEX(' ', @str, 0) SET @str_gen = LEFT(@str, @position) SET @str = STUFF(@str, 1, @position, '') SET @str = LTRIM(@str) SET @str_gen = @str_gen + LEFT(@str, 1) + '.' SET @position = CHARINDEX(' ', @str, 0) SET @str = STUFF(@str, 1, @position, '') SET @str = LTRIM(@str) SET @str_gen = @str_gen + LEFT(@str, 1) + '.' RETURN @str_gen END GO
/*Проверка пользовательской функции по формированию текстовой строки*/ DECLARE @str VARCHAR(50) SET @str = ' Никулина Татьяна Николаевна ' SELECT @str AS 'Первоначальные данные', dbo.fio(@str) AS 'Конечные данные' GO
Рисунок 8 – Результат работы
4. Создание таблицы «Протокол»
Создайте в базе данных таблицу Протокол со структурой, приведенной ниже.
Номер ДатаВремя Пользователь Действие ЧислоСтрок . . . . . . . . . . . . . . .
Здесь столбец Номер является автоинкрементным первичным ключом. В столбце Действие будет указываться одна из трех возможных операций с данными: «Вставка», «Обновление», «Удаление». Столбец ЧислоСтрок будет содержать данные о числе вставленных, либо обновленных, либо удаленных строк.
/*Создание таблицы Протокол*/ CREATE TABLE Протокол (Номер INT IDENTITY(1,1) PRIMARY KEY, ДатаВремя DATETIME DEFAULT getdate() NULL, Пользователь VARCHAR(20) NOT NULL, Действие VARCHAR(10) NOT NULL CHECK (Действие='Обновление' OR Действие='Вставка' OR Действие='Удаление'), ЧислоСтрок INT NOT NULL CHECK (ЧислоСтрок >= 0)) GO
Рисунок 9 - Таблица «Протокол»
5. Создание триггеров к таблице «Студент»
Создайте триггер, связанный с конкретной таблицей базы данных, который предназначен для автоматической фиксации в таблице Протокол всех действий, вызванных вставкой, обновлением или удалением данных в таблице, снабженной триггером. Каждая SQL-команда, изменяющая содержимое этой таблицы, должна быть отражена отдельной строкой в таблице Протокол.
/*Создание триггера для автоматической фиксации в таблице Протокол (Вставка)*/ CREATE TRIGGER tu_insert ON студент FOR INSERT AS DECLARE @count INT SELECT @count = COUNT(*) FROM INSERTED INSERT INTO Протокол (Пользователь, Действие, ЧислоСтрок) VALUES(SUSER_SNAME(), 'Вставка', @count) GO
/*Создание триггера для автоматической фиксации в таблице Протокол (Удаление)*/ CREATE TRIGGER tu_delete ON студент FOR DELETE AS DECLARE @count INT SELECT @count = COUNT(*) FROM DELETED INSERT INTO Протокол (Пользователь, Действие, ЧислоСтрок) VALUES(SUSER_SNAME(), 'Удаление', @count) GO
/*Создание триггера для автоматической фиксации в таблице Протокол (Обновление)*/ CREATE TRIGGER tu_update ON студент FOR UPDATE AS DECLARE @count INT SELECT @count = COUNT(*) FROM INSERTED INSERT INTO Протокол (Пользователь, Действие, ЧислоСтрок) VALUES(SUSER_SNAME(), 'Обновление', @count) GO
/*Проверка работоспособности триггера*/ DELETE FROM студент WHERE СреднийБалл=6.9 INSERT INTO студент VALUES ('4', '1', 'Петров Андрей Сергеевич', '15.04.1986','г.Бобруйск, ул. Лесная, д.3, кв.12', 'М', '8.0') UPDATE студент SET ДатаРождения = getdate() WHERE СреднийБалл>8
SELECT * FROM Протокол GO
Рисунок 10 – Результат работы триггера Список использованных источников
1. BPwin и ERwin. CASE-средства разработки информационных систем. / Маклаков С.В. – М., 1999. 2. http://msdn.microsoft.com/ru-ru/library/bb510741.aspx – cправочник по Transact-SQL. 3. MS SQL Server 2000: управление и программирование. / Пирогов В.Ю. – СПб., 2005. 4. Визуальные и программные средства проектирования реляционных баз данных / А.А.Бутов, И.Г. Орешко, Е.А. Шестаков – Мн., 2009. 5. Язык SQL. Учебный курс. / Шкарина Л. – СПб., 2003.
