Разработка базы данных 'Работа деканата в вузе'

Содержание

Задание 1. Разработка схемы данных в ErWIn

Задание 2. Создание SQL-сценария

Задание 3. Создание процедур

Задание 4. Создание таблицы «Протокол»

Задание 5. Создание триггеров к таблице «Студент»

Создание триггера

Литература

Задание

Создать базу данных «Работа деканата в вузе». Возможные таблицы: факультеты, группы, студенты, кафедры, преподаватели, предметы.

1. Структура базы данных

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

В системе Erwin были проведены следующие действия:

)   Была создана новая модель данных (тип модели - «Логическая / Физическая», целевая база данных - «MS Access»).

2)      В логической модели были созданы 6 сущностей.

)        В логической модели были созданы необходимые связи «один ко многим» между сущностями.

)        Для созданных связей были установлены правила для ссылок как RESTRICT так и CASCADE.

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

Рисунок 1 - Логическая структура базы данных

Используя средство системы Erwin «Forward Engineer» для экспортирования структуры в СУБД были созданы необходимые таблицы с заданными параметрами.

Рисунок 2 - Физическая структура разрабатываемой базы данных

После выполнения прямого проектирования проверим результат:

Рисунок 3 - Схема данных

2. Создание SQL-сценария

CREATE DATABASE decanatPRIMARY

( NAME=decanat,='D:\БГУИР\Деканат\decanat.mdf',= 3 MB,= 50 MB,= 2 MB),Secondary

(NAME = decanat2_Data,= 'D:\БГУИР\Деканат\decanat_Data2.ndf',= 2 MB,= 70 MB,= 20%),

( NAME = decanat3_Data,= 'D:\БГУИР\Деканат\decanat_Data3.ndf',= 2 MB,= 5 MB )ON

(NAME = decanat_Log,= 'D:\БГУИР\Деканат\decanat_Log.ldf',= 1 MB,= 15%),

(NAME = student2_Log,= 'D:\БГУИР\Деканат\decanat_Log2.ldf',=512 KB,= 5 MB,= 10%)[decanat]TABLE [dbo].[факультет] (

[КодФакультета] [INT] IDENTITY(1,1) NOT NULL,

[Наименование] [NVARCHAR](50) NOT NULL,[PK_факультет] PRIMARY KEY CLUSTERED

([КодФакультета] ASC)(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]TABLE [dbo].[кафедра]

([КодКафедры] [INT] IDENTITY (1,1)NOT NULL,

[КодФакультета] [INT] NOT NULL,

[Наименование] [nvarchar](50)NOT NULL,[PK_кафедра] PRIMARY KEY CLUSTERED

([КодКафедры] ASC,

[КодФакультета] ASC)(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])[PRIMARY]TABLE [dbo].[кафедра] WITH CHECK ADD CONSTRAINT

[FK_кафедра_факультет] FOREIGN KEY ([КодКафедры])

REFERENCES [dbo].[факультет] ([КодФакультета])

TABLE [dbo].[группа] (

[КодГруппы] [INT] IDENTITY (1,1) NOT NULL,

[КодФакультета] [INT] NOT NULL,

[НазваниеГруппы] [nvarchar](10) NOT NULL,[PK_группа] PRIMARY KEY CLUSTERED

([КодГруппы] ASC,

[КодФакультета] ASC),[FK_факультет]FOREIGN KEY ([КодФакультета])[dbo].[факультет] ([КодФакультета])

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[PK_преподаватель] PRIMARY KEY CLUSTERED

([КодПреподавателя] ASC,

[КодКафедры] ASC,

[КодФакультета] ASC)

TABLE [dbo].[преподаватель] WITH CHECK ADD

CONSTRAINT [FK_преподаватель_кафедра] FOREIGN KEY

([КодКафедры],[КодФакультета])[dbo].[кафедра] ([КодКафедры], [КодФакультета])

TABLE [dbo].[дисциплина] (

[КодДисциплины] [INT] NOT NULL,

[КодПреподавателя] [INT] NOT NULL,

[КодФакультета] [INT] NOT NULL,

[Название] [nvarchar](30) NOT NULL,

[ОбъемЧасов] [INT] NOT NULL[PK_КодДисциплины] PRIMARY KEY CLUSTERED

([КодДисциплины] ASC,

[КодПреподавателя] ASC,

[КодКафедры] ASC,

[КодФакультета] ASC)

TABLE [dbo].[дисциплина] WITH CHECK ADD CONSTRAINT

[FK_дисциплина_преподаватель]FOREIGN KEY

([КодПреподавателя],[КодКафедры],[КодФакультета])[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[PK_студент] PRIMARY KEY CLUSTERED

([КодСтудента] ASC,

[КодГруппы]ASC,

[КодФакультета] ASC),

CONSTRAINT [FK_группа] FOREIGN KEY

([КодГруппы],[КодФакультета])

REFERENCES [dbo].[группа] ([КодГруппы],[КодФакультета])

TABLE [dbo].[студент] WITH CHECK ADD CONSTRAINT

[CK_студент_пол] CHECK (([Пол]='М' OR [Пол]='Ж'))

GO

UNIQUE INDEX UIX_факультет ON факультет

(Наименование) ON SecondaryUNIQUE INDEX UIX_кафедра ON кафедра (Наименование)

ON SecondaryUNIQUE INDEX UIX_группа ON группа (НазваниеГруппы)

ON SecondaryUNIQUE INDEX UIX_дисциплина ON дисциплина (название)

ON SecondaryINDEX IX_студент ON студент (ФИО, ДатаРождения, Пол,

Адрес, СреднийБалл) ON Secondary

CREATE INDEX IX_Услуга ON преподаватель (ФИО, УченаяСтепень,

УченоеЗвание, Должность, Стаж) ON Secondary

GO

INTO [dbo].[факультет]

([Наименование])('энергетический')

INTO [dbo].[факультет]

([Наименование])('машиностроительный')INTO [dbo].[факультет]

([Наименование])('механико-технологический')

INTO [dbo].[факультет]

([Наименование])('гуманитарно-экономический')

INTO [dbo].[факультет]

([Наименование])('автоматизированных и информационных систем')

INTO [dbo].[кафедра]

([КодФакультета], [Наименование])('1','Промышленная теплоэнергетика и экология')

INTO [dbo].[кафедра]

([КодФакультета], [Наименование])('1','Теоретические основы электротехники')INTO [dbo].[кафедра]

([КодФакультета], [Наименование])

VALUES ('4','Экономика')

INSERT INTO [dbo].[кафедра]

([КодФакультета], [Наименование])('1','Физика')

INTO [dbo].[кафедра]

([КодФакультета], [Наименование])('2','Гидропневмоавтоматика')INTO [dbo].[группа]

([КодФакультета], [НазваниеГруппы])('4','ОП-11')

INTO [dbo].[группа]

([КодФакультета], [НазваниеГруппы])

VALUES ('4','УП-11')

INSERT INTO [dbo].[группа]

([КодФакультета], [НазваниеГруппы])('1','ТЭ-51')

INTO [dbo].[группа]

([КодФакультета], [НазваниеГруппы])

VALUES ('1','ТЭ-52')

INSERT INTO [dbo].[студент]

([КодГруппы],[КодФакультета], [ФИО], [ДатаРождения], [Адрес],

[Пол], [СреднийБалл])('4','1','Дацюк Андрей Александрович','18.01.1985','г.

([КодГруппы],[КодФакультета], [ФИО], [ДатаРождения], [Адрес],

[Пол], [СреднийБалл])('4','1','Капралова Ольга Алексеевна','24.06.1989','г. Гомель,

ул. 2-я линейная, д.51/2','Ж','7.9')INTO [dbo].[студент]

([КодГруппы],[КодФакультета], [ФИО], [ДатаРождения], [Адрес],

[Пол], [СреднийБалл])('4','1','Ковалев Дмитрий Александрович',

'25.01.1989','г. Гомель, ул. Студ. проезд, д.19, кв.65','М','6.9')INTO [dbo].[студент]

([КодГруппы],[КодФакультета], [ФИО], [ДатаРождения], [Адрес],

[Пол], [СреднийБалл])('4','1','Никулина Татьяна Николаевна','27.02.1989','г. Гомель,

ул. Студ. проезд, д.21, кв.119','Ж','8.1')

INTO [dbo].[преподаватель]

([КодКафедры],[КодФакультета], [ФИО], [УченаяСтепень],

[УченоеЗвание], [Должность], [Стаж])('1','1','Овсянник Анатолий Васильевич',

'к.т.н.','доцент','заведующий кафедрой','19')

INTO [dbo].[преподаватель]

([КодКафедры],[КодФакультета], [ФИО], [УченаяСтепень],

[УченоеЗвание], [Должность], [Стаж])('1','1','Шаповалов Александр

Валерьевич','к.т.н.','доцент','старший преподаватель','8')INTO [dbo].[преподаватель]

([КодКафедры],[КодФакультета], [ФИО], [УченаяСтепень],

[УченоеЗвание], [Должность], [Стаж])('3','4','Дрозд Станислав Степанович',

'к.э.н.','доцент','доцент','35')

INTO [dbo].[дисциплина]

([КодДисциплины],[КодПреподавателя],[КодКафедры],

[КодФакультета], [Название], [ОбъемЧасов])('1','1','1','1','Техническая термодинамика','98')

INTO [dbo].[дисциплина]

([КодДисциплины],[КодПреподавателя],[КодКафедры],

[КодФакультета], [Название], [ОбъемЧасов])

VALUES ('2','1','1','1','Экология','32')

INSERT INTO [dbo].[дисциплина]

([КодДисциплины],[КодПреподавателя],[КодКафедры],

[КодФакультета], [Название], [ОбъемЧасов])('3','3','3','4','Экономика организации','144')INTO [dbo].[дисциплина]

([КодДисциплины],[КодПреподавателя],[КодКафедры],

[КодФакультета], [Название], [ОбъемЧасов])

VALUES ('4','3','3','4','ЭПМ','102')

GO

3. Создание процедур

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

Создание хранимой процедуры подсчёта числа строк в указанной таблице:

CREATE PROCEDURE pr_count

@table VARCHAR(20),

@count INT OUTPUT@sql NVARCHAR(255)@sql = 'SELECT @count = COUNT(*) FROM ' + @tablesp_executesql @sql, N'@count int out', @count=@count out

GO

база данный деканат вуз

Проверка хранимой процедуры подсчёта числа строк в указанной таблице

DECLARE @Count INT, @Table VARCHAR(20)@Table = 'студент'pr_count @Table, @Count OUTPUT

SELECT @Table AS [Название таблицы],

@Count AS [Количество записей в таблице]

Рисунок 4 - Результат работы

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

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

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

CREATE PROCEDURE pr_date

@pom SMALLINT, --plus or minus

@date SMALLINT

ASстудент SET ДатаРождения = ДатаРождения + @pom*@date

Проверка хранимой процедуры изменения всех дат

SELECT * FROM студент@pom SMALLINT,

@date SMALLINT@pom=1@date=30pr_date @pom, @date* FROM студент

GO

Рисунок 5 - Результат работы

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

Создание хранимой процедуры подсчёта минимального, среднего, максимального и суммарного значения

CREATE PROCEDURE pr_min_max_sr_sum

@columnVARCHAR(20),

@minDECIMAL(10,2) OUTPUT,

@maxDECIMAL(10,2) OUTPUT,

@sumDECIMAL(10,2) OUTPUT,

@avgDECIMAL(10,2) OUTPUT@sql NVARCHAR(255)@sql = 'SELECT @min = MIN(' + @column + ') FROM

преподаватель'sp_executesql @sql, N'@min DECIMAL(10,2) out', @min=@min out@sql = 'SELECT @max = MAX(' + @column + ') FROM

out@sql = 'SELECT @sum = SUM(' + @column + ') FROM

преподаватель'sp_executesql @sql, N'@sum DECIMAL(10,2) out', @sum=@sum out@sql = 'SELECT @avg = AVG(' + @column + ') FROM

преподаватель'sp_executesql @sql, N'@avg DECIMAL(10,2) out', @avg=@avg out

GO

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

DECLARE @Column VARCHAR(20)@Column = 'Стаж'@min DECIMAL(10,2)@max DECIMAL(10,2)@sum DECIMAL(10,2)@avg DECIMAL(10,2)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 [Среднее значение]

Рисунок 6 - Результат работы

4. Создайте хранимую процедуру, которая из конкретной таблицы отбирает строки по условию, налагаемому на значения конкретных двух столбцов. Эта процедура должна иметь два входных параметра, задающих значения для отбора данных по каждому из этих столбцов. При этом значение NULL должно трактоваться как отсутствие какого-либо критерия отбора строк по данному столбцу (например, если оба параметра будут иметь значение NULL, то должны быть выбраны все строки таблицы).

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

CREATE PROCEDURE pr_yslov

@date DATETIME,

@sr_bINT@date IS NOT NULL@sr_b IS NOT NULL* FROM студент WHERE ДатаРождения > @date AND

СреднийБалл > @sr_b* FROM студент WHERE ДатаРождения > @date@sr_b IS NOT NULL* FROM студент WHERE СреднийБалл > @sr_b

ELSE* FROM студент

/*Проверка хранимой процедуры по отбору строк по условию*/

SELECT * FROM студент@Date DATETIME, @Sr_b INT@Date='1.1.1988' /*getdate()*/@Sr_b=7.4pr_yslov @Date, @Sr_b

Рисунок 7 - Результат работы

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

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

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

Создание пользовательской функции по формированию текстовой строки

CREATE FUNCTION fio (@str VARCHAR(50))VARCHAR(30)@str_gen VARCHAR(35)@position TINYINT@str = LTRIM(@str)@str = RTRIM(@str)@position = CHARINDEX(' ', @str, 0)@str_gen = LEFT(@str, @position)@str = STUFF(@str, 1, @position, '')@str = LTRIM(@str)@str_gen = @str_gen + LEFT(@str, 1) + '.'@position = CHARINDEX(' ', @str, 0)@str = STUFF(@str, 1, @position, '')@str = LTRIM(@str)@str_gen = @str_gen + LEFT(@str, 1) + '.'

END

Проверка пользовательской функции по формированию текстовой строки

@str VARCHAR(50)@str = ' Никулина Татьяна Николаевна '@str AS 'Первоначальные данные',.fio(@str) AS 'Конечные данные'

Рисунок 8 - Результат работы

4. Создание таблицы «Протокол»

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

Здесь столбец Номер является автоинкрементным первичным ключом. В столбце Действие будет указываться одна из трех возможных операций с данными: «Вставка», «Обновление», «Удаление». Столбец ЧислоСтрок будет содержать данные о числе вставленных, либо обновленных, либо удаленных строк.

Создание таблицы Протокол

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студентINSERT@count INT@count = COUNT(*) FROM INSERTED INTO Протокол (Пользователь, Действие, ЧислоСтрок)

VALUES(SUSER_SNAME(), 'Вставка', @count)

GO

Создание триггера для автоматической фиксации в таблице Протокол (Удаление)

CREATE TRIGGER tu_deleteстудентDELETE@count INT@count = COUNT(*) FROM DELETED INTO Протокол (Пользователь, Действие, ЧислоСтрок)

VALUES(SUSER_SNAME(), 'Удаление', @count)

GO

Создание триггера для автоматической фиксации в таблице Протокол (Обновление)

CREATE TRIGGER tu_updateстудентUPDATE@count INT@count = COUNT(*) FROM INSERTED INTO Протокол (Пользователь, Действие, ЧислоСтрок)

GO

Проверка работоспособности триггера

DELETE FROM студент WHERE СреднийБалл=6.9

INSERT INTO студент('4', '1', 'Петров Андрей Сергеевич', '15.04.1986','г. Бобруйск,

ул. Лесная, д.3, кв.12', 'М', '8.0')студент SET ДатаРождения = getdate() WHERE

СреднийБалл>8

* FROM Протокол

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

1. BPwin и ERwin. CASE-средства разработки информационных систем. / Маклаков С.В. - М., 1999.

2.      Справочник по Transact-SQL.

.        MS SQL Server 2000: управление и программирование. / Пирогов В.Ю. - СПб., 2005.

.        Визуальные и программные средства проектирования реляционных баз данных / А.А.Бутов, И.Г. Орешко, Е.А. Шестаков - Мн., 2009.

.        Язык SQL. Учебный курс. / Шкарина Л. - СПб., 2003.