Размещено на http://www.allbest.ru/

Содержание

• Задание 1. Разработка схемы данных в ErWIn
• Задание 2. Создание SQL-сценария
• Задание 3. Создание процедур
• Задание 4. Создание таблицы «Протокол»
• Задание 5. Создание триггеров к таблице «Студент»
• Создание триггера
• Литература


Задание

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



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

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

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

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

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

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

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

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

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

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

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

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

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



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

CREATE DATABASE decanat

ON PRIMARY

( NAME=decanat,

FILENAME='D:\БГУИР\Деканат\decanat.mdf',

SIZE = 3 MB,

MAXSIZE = 50 MB,

FILEGROWTH = 2 MB),

FILEGROUP Secondary

(NAME = decanat2_Data,

FILENAME = 'D:\БГУИР\Деканат\decanat_Data2.ndf',

SIZE = 2 MB,

MAXSIZE = 70 MB,

FILEGROWTH = 20%),

( NAME = decanat3_Data,

FILENAME = 'D:\БГУИР\Деканат\decanat_Data3.ndf',

SIZE = 2 MB,

FILEGROWTH = 5 MB )

LOG ON

(NAME = decanat_Log,

FILENAME = 'D:\БГУИР\Деканат\decanat_Log.ldf',

SIZE = 1 MB,

FILEGROWTH = 15%),

(NAME = student2_Log,

FILENAME = 'D:\БГУИР\Деканат\decanat_Log2.ldf',

SIZE =512 KB,

MAXSIZE = 5 MB,

FILEGROWTH = 10%)

GO

USE [decanat]

GO

CREATE TABLE [dbo].[факультет] (

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

[Наименование] [NVARCHAR](50) NOT NULL,

CONSTRAINT [PK_факультет] PRIMARY KEY CLUSTERED

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

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

CREATE TABLE [dbo].[кафедра]

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

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

[Наименование] [nvarchar](50)NOT NULL,

CONSTRAINT [PK_кафедра] PRIMARY KEY CLUSTERED

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

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

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])

ON [PRIMARY]

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,

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

CONSTRAINT [PK_группа] PRIMARY KEY CLUSTERED

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

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

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 ('3','4','Дрозд Станислав Степанович',

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

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

@columnVARCHAR(20),

@minDECIMAL(10,2) OUTPUT,

@maxDECIMAL(10,2) OUTPUT,

@sumDECIMAL(10,2) OUTPUT,

@avgDECIMAL(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_bINT

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



Рисунок 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) + '.'

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

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

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

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

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

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

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

Размещено на Allbest.ru