bsuir.info
БГУИР: Дистанционное и заочное обучение
(файловый архив)
Вход (быстрый)
Регистрация
Категории каталога
Другое [195]
Бухучет [16]
ВМиМОвЭ [4]
ОДМиТА [13]
ОЛОБД [17]
ООПиП [67]
ОС [19]
ПСОД [46]
Форма входа
Логин:
Пароль:
Поиск
Статистика

Онлайн всего: 2
Гостей: 2
Пользователей: 0
Файловый архив
Файлы » ИСиТвЭ » ОЛОБД

Базы данных (вариант 26)
Подробности о скачивании 13.02.2012, 20:30
Задание 26
Создать базу данных «РАБОТА ДЕКАНАТА в ВУЗе». Возможные таблицы: факультеты, группы, студенты, кафедры, преподаватели, предметы.

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', @[email protected] 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', @[email protected] out
SET @sql = 'SELECT @max = MAX(' + @column + ') FROM преподаватель'
exec sp_executesql @sql, N'@max DECIMAL(10,2) out', @[email protected] out
SET @sql = 'SELECT @sum = SUM(' + @column + ') FROM преподаватель'
exec sp_executesql @sql, N'@sum DECIMAL(10,2) out', @[email protected] out
SET @sql = 'SELECT @avg = AVG(' + @column + ') FROM преподаватель'
exec sp_executesql @sql, N'@avg DECIMAL(10,2) out', @[email protected] 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.
Категория: ОЛОБД | Добавил: Tanik_nik
Просмотров: 1863 | Загрузок: 99
Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]