Университет

МИНИСТЕРСТВО ОБРАЗОВАНИЯ  РЕСПУБЛИКИ БЕЛАРУСЬ

БЕЛОРУСКИЙ ГОСУДАРСТВЕННЫЙ  УНИВЕРСИТЕТ ИНФОРМАТИКИ И РАДИОЭЛЕКТРОНИКИ

 

 

 

 

 

 

 

Кафедра экономической  информатики

 

 

 

 

Отчёт

 по  индивидуальной работе 

дисциплины «Базы данных»

на тему:

«Университет»

 

 

 

 

 

 

 

 Выполнил:                                                    студент группы 872302

 

 

 Проверил: Бутов А.А.

   

 

 

 

Минск

2010

 

Постановка  задачи

 

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

 

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

 

3. Создать SQL-сценарий, который позволяет сгенерировать базу данных в формате MS SQL Server 2005, а также наполнить созданную базу данных конкретными данными.

 

4. Дополнить сценарий текстами перечисленных ниже хранимых процедур:

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

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

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

 

5. Разработать SQL-код для проверки работы созданных хранимых процедур.

 

Таблицы

ВУЗ

- UNIV_ID

- НАЗВАНИЕ_В

- СТРАНА

- ГОРОД

 

 

СПЕЦИАЛЬНОСТЬ

- НОМЕР_СП

 - НАЗВАНИЕ_СП

 

 

ФАКУЛЬТЕТ

 - НОМЕР_Ф

- НАЗВАНИЕ_Ф

- UNIV_ID

 

 

ГРУППА

- НОМЕР_ГР

- НОМЕР_Ф

- НОМЕР_СП

 

СТУДЕНТ

- STUDENT_ID

- ИМЯ

- ФАМИЛИЯ

- СТИПЕНДИЯ

- КУРС

- ГОРОД

 - ДЕНЬ_РОЖДЕНИЯ

 - НОМЕР_ГР

 

 

ПРЕДМЕТ

- SUBJ_ID

- НАЗВАНИЕ_ПР 

- ЧАСЫ 

- СЕМЕСТР

- STUDENT_ID

- MARK_EX_ID

- MARK_T_ID

 

 

ОЦЕНКА

- MARK_ID

- ОЦЕНКА

 - STUDENT_ID

- SUBJ_ID

 

 

 

ЗВАНИЕ

- НАЗВАНИЕ_ЗВ

 

 

УЧЕННАЯ_СТЕПЕНЬ

- НАЗВАНИЕ_СТ

 

 

ПРЕПОДАВАТЕЛЬ

- TEACH_ID

- ИМЯ

- ФАМИЛИЯ

- SUBJ_ID

- НАЗВАНИЕ_ЗВ

- НАЗВАНИЕ_СТ

 

На рисунке показано проектирование данных таблиц в Erwin 4.0

 

 

Как видно из рисунка, здесь использованы неидентифицирующие связи. На некоторые  поля таблиц накладывались ограничения  на физическом уровне в соответствии с MS Access 2003.

Путем прямого проектирования модели в базу MS Access получил соответствующую базу данных.

 

Следующим этапом в выполнении задания  является создание SQL-скрипта, который сгенерирует аналогичную базу данных для MS SQL Server 2005. А также целью является написание нескольких процедур и соответственно проверить их работу.

Исходный код приведен ниже:

CREATE DATABASE UNIVERSITY

ON PRIMARY    

    ( NAME = STUDENTINFO_Data,  

      FILENAME = 'C:\university\университет\БД\Raschiotnaya\STUDENTINFO_Data.mdf',

      SIZE = 3MB,

      MAXSIZE = 50MB,

      FILEGROWTH = 2MB ),

FILEGROUP Secondary

    ( NAME = STUDENTINFO2_Data,

      FILENAME = 'C:\university\университет\БД\Raschiotnaya\STUDENTINFO2_Data.ndf',

      SIZE = 2MB,

      MAXSIZE = 70MB,

      FILEGROWTH = 20% ),

    ( NAME = STUDENTINFO3_Data,

      FILENAME = 'C:\university\университет\БД\Raschiotnaya\STUDENTINFO3_Data.ndf',

      SIZE = 2MB,

  FILEGROWTH = 5MB )

LOG ON

    ( NAME = 'STUDENTINFO_Log',

      FILENAME = 'C:\university\университет\БД\Raschiotnaya\STUDENTINFO_Log.ldf',

      SIZE = 1MB,

      MAXSIZE = 10MB,

      FILEGROWTH = 15% ),

    ( NAME = 'STUDENTINFO2_Log',

      FILENAME = 'C:\university\университет\БД\Raschiotnaya\STUDENTINFO2_Log.ldf',

      SIZE = 512KB,

      MAXSIZE = 5MB,

      FILEGROWTH = 10% )

GO

 

use UNIVERSITY

GO

 

CREATE RULE Logical_Rule AS @value IN ('Нет', 'Да')

GO

EXEC sp_addtype Logical, 'char(3)', 'NOT NULL'

GO

 EXEC sp_bindrule 'Logical_Rule', 'Logical'

GO

 

 

 

CREATE TABLE ВУЗ(

UNIV_ID INT IDENTITY(1,1) PRIMARY KEY,

НАЗВАНИЕ_В VARCHAR(20) NOT NULL,

СТРАНА VARCHAR(20) NOT NULL,

ГОРОД VARCHAR(20) DEFAULT 'МИНСК' NOT NULL,

UNIQUE( НАЗВАНИЕ_В, ГОРОД)

)

 

CREATE TABLE СПЕЦИАЛЬНОСТЬ(

НОМЕР_СП INT PRIMARY KEY,

НАЗВАНИЕ_СП VARCHAR(20)

)

 

CREATE TABLE ФАКУЛЬТЕТ(

НОМЕР_Ф INT PRIMARY KEY ,

НАЗВАНИЕ_Ф VARCHAR(20),

UNIV_ID INT NOT NULL,

CONSTRAINT FK_ФАКУЛЬТЕТ_ВУЗ FOREIGN KEY ( UNIV_ID ) REFERENCES ВУЗ,

)

 

CREATE TABLE ГРУППА(

НОМЕР_ГР INT PRIMARY KEY,

НОМЕР_Ф INT NOT NULL,

НОМЕР_СП INT NOT NULL,

CONSTRAINT FK_ГРУППА_СПЕЦИАЛЬНОСТЬ FOREIGN KEY ( НОМЕР_СП ) REFERENCES СПЕЦИАЛЬНОСТЬ,

CONSTRAINT FK_ГРУППА_ФАКУЛЬТЕТ FOREIGN KEY ( НОМЕР_Ф ) REFERENCES ФАКУЛЬТЕТ

)

 

CREATE TABLE СТУДЕНТ(

STUDENT_ID INT IDENTITY(1,1) PRIMARY KEY,

ИМЯ VARCHAR(20) NOT NULL,

ФАМИЛИЯ VARCHAR(15) NULL,

СТИПЕНДИЯ MONEY NULL CHECK( СТИПЕНДИЯ >= 0 ),

КУРС INT NOT NULL CHECK ( КУРС BETWEEN 1 AND 6 ),

ГОРОД VARCHAR(20) DEFAULT 'МИНСК' NULL,

ДЕНЬ_РОЖДЕНИЯ DATETIME CHECK ( ДЕНЬ_РОЖДЕНИЯ > '01.01.1900' AND ДЕНЬ_РОЖДЕНИЯ < GETDATE() ),

НОМЕР_ГР INT NOT NULL,

CONSTRAINT FK_СТУДЕНТ_ГРУППА FOREIGN KEY ( НОМЕР_ГР ) REFERENCES ГРУППА ON UPDATE CASCADE

)

 

CREATE TABLE ОЦЕНКА_ПО_ЭКЗАМЕНУ(

MARK_EX_ID INT IDENTITY(1,1) PRIMARY KEY,

ОЦЕНКА INT,

ДАТА DATETIME DEFAULT GETDATE() NOT NULL

)

 

CREATE TABLE ОЦЕНКА_ПО_ЗАЧЕТУ(

MARK_T_ID INT IDENTITY(1,1) PRIMARY KEY,

ЗАЧТЕНО Logical NULL,

ОЦЕНКА INT NULL,

ДАТА DATETIME DEFAULT GETDATE() NOT NULL

)

 

CREATE TABLE ПРЕДМЕТ(

SUBJ_ID INT IDENTITY(1,1) PRIMARY KEY,

НАЗВАНИЕ_ПР VARCHAR(20),

ЧАСЫ INT,

СЕМЕСТР INT,

STUDENT_ID INT NOT NULL,

MARK_EX_ID INT NULL,

MARK_T_ID INT NULL,

 

)

 

CREATE TABLE ОЦЕНКА(

MARK_ID INT IDENTITY(1,1) PRIMARY KEY,

ОЦЕНКА INT,

STUDENT_ID INT NOT NULL,

SUBJ_ID INT NOT NULL,

CONSTRAINT FK_ОЦЕНКА_ПРЕДМЕТ FOREIGN KEY ( SUBJ_ID ) REFERENCES ПРЕДМЕТ,

CONSTRAINT FK_ОЦЕНКА_СТУДЕНТ FOREIGN KEY ( STUDENT_ID ) REFERENCES СТУДЕНТ,

)

 

 

 

CREATE TABLE ЗВАНИЕ(

НАЗВАНИЕ_ЗВ VARCHAR(30) PRIMARY KEY

)

 

CREATE TABLE УЧЕННАЯ_СТЕПЕНЬ(

НАЗВАНИЕ_СТ VARCHAR(30) PRIMARY KEY

)

 

CREATE TABLE ПРЕПОДАВАТЕЛЬ(

TEACH_ID INT IDENTITY(1,1),

ИМЯ VARCHAR(20) NOT NULL,

ФАМИЛИЯ VARCHAR(15) NULL,

SUBJ_ID INT NOT NULL,

НАЗВАНИЕ_ЗВ VARCHAR(30) NOT NULL,

НАЗВАНИЕ_СТ VARCHAR(30) NOT NULL,

CONSTRAINT FK_ПРЕПОДВАТЕЛЬ_ЗВАНИЕ FOREIGN KEY (НАЗВАНИЕ_ЗВ) REFERENCES ЗВАНИЕ,

CONSTRAINT FK_ПРЕПОДВАТЕЛЬ_УЧЕННАЯ_СТЕПЕНЬ FOREIGN KEY (НАЗВАНИЕ_СТ) REFERENCES УЧЕННАЯ_СТЕПЕНЬ,

CONSTRAINT FK_ПРЕПОДВАТЕЛЬ_ПРЕДМЕТ FOREIGN KEY (SUBJ_ID) REFERENCES ПРЕДМЕТ

)

 

GO

 

INSERT INTO ВУЗ

VALUES ('БГУИР', 'Беларусь', 'Минск')

 

INSERT INTO ВУЗ

VALUES ('ГрГУ', 'Беларусь', 'Гродно')

 

INSERT INTO ВУЗ

VALUES ('БГУ', 'Беларусь', 'Минск')

 

INSERT INTO ВУЗ

VALUES ('БНТУ', 'Беларусь', 'Минск')

 

INSERT INTO ВУЗ

VALUES ('ГМУ', 'Беларусь', 'Гродно')

 

INSERT INTO СПЕЦИАЛЬНОСТЬ

VALUES ( 23, 'ИСиТ' )

 

INSERT INTO СПЕЦИАЛЬНОСТЬ

VALUES ( 55, 'ПОИТ')

 

INSERT INTO ФАКУЛЬТЕТ

VALUES ( 7, 'ИЭФ', 1 )

 

INSERT INTO ФАКУЛЬТЕТ

VALUES ( 5,'КСиС', 1 )

 

GO

 

 

GO

 

INSERT INTO ГРУППА

VALUES ( 872302, 7, 23)

 

INSERT INTO ГРУППА

VALUES ( 872301, 7, 23)

 

INSERT INTO ГРУППА

VALUES ( 872303, 7, 23)

 

GO

 

INSERT INTO ЗВАНИЕ

VALUES( 'ПРОФЕССОР' )

 

INSERT INTO ЗВАНИЕ

VALUES( 'ДОЦЕНТ' )

 

INSERT INTO ЗВАНИЕ

VALUES( 'СТАРШИЙ ПРЕПОДОВАТЕЛЬ' )

 

GO

 

INSERT INTO УЧЕННАЯ_СТЕПЕНЬ

VALUES ( 'ДОКТОР' )

 

INSERT INTO УЧЕННАЯ_СТЕПЕНЬ

VALUES ( 'КАНДИДАТ' )

 

GO

 

INSERT INTO СТУДЕНТ

VALUES( 'Андрей','Морозов', 0, 2, 'Минск','29.10.1990' ,872302)

 

INSERT INTO СТУДЕНТ

VALUES( 'Мария','Сакович', 0, 2, 'Минск','09.11.1990' ,872302)

 

INSERT INTO СТУДЕНТ

VALUES( 'Павел','Потапович', 0, 2, 'Гродно','29.10.1990' ,872302)

 

INSERT INTO СТУДЕНТ

VALUES( 'Ольга','Кайте', 0, 2, 'Вильнюс','29.10.1990' ,872302)

 

INSERT INTO СТУДЕНТ

VALUES( 'Павел','Морозов', 0, 2, 'Минск','29.10.1990' ,872301)

 

INSERT INTO СТУДЕНТ

VALUES( 'Олег','Андреев', 0, 2, 'Минск','29.10.1990' ,872303)

 

GO

 

 

 

INSERT INTO ПРЕДМЕТ

VALUES ( 'Логика', 36, 4, 1, 1, 1 )

 

INSERT INTO ПРЕДМЕТ

VALUES ( 'ВМ', 36, 4, 1, 1, 1 )

 

INSERT INTO ПРЕДМЕТ

VALUES ( 'ООП', 36, 4, 1, 1, 1 )

 

INSERT INTO ПРЕДМЕТ

VALUES ( 'БД', 36, 4, 1, 1, 1 )

 

GO

 

INSERT INTO ОЦЕНКА

VALUES ( 4, 1, 1 )

 

INSERT INTO ОЦЕНКА

VALUES ( 6, 2, 1 )

 

INSERT INTO ОЦЕНКА

VALUES ( 8, 3, 1 )

 

INSERT INTO ОЦЕНКА

VALUES ( 7, 4, 1 )

 

INSERT INTO ПРЕПОДАВАТЕЛЬ

VALUES ( 'Федор','Колько', 1, 'ПРОФЕССОР','ДОКТОР' )

 

INSERT INTO ПРЕПОДАВАТЕЛЬ

VALUES ( 'Николай','Серегеев', 1, 'ДОЦЕНТ','ДОКТОР' )

 

INSERT INTO ПРЕПОДАВАТЕЛЬ

VALUES ( 'Ольга','Иванова', 2, 'ПРОФЕССОР','КАНДИДАТ' )

 

INSERT INTO ПРЕПОДАВАТЕЛЬ

VALUES ( 'Мария','Иванова', 3, 'ПРОФЕССОР','КАНДИДАТ' )

 

INSERT INTO ПРЕПОДАВАТЕЛЬ

VALUES ( 'Николай','Морозов', 4, 'ДОЦЕНТ','КАНДИДАТ' )

 

GO

 

/*• Создайте хранимую процедуру, которая для указанной таблицы

подсчитывает число строк, содержащихся в этой таблице. Эта процедура

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

и один выходной параметр*/

 

CREATE PROCEDURE pr_ПодсчетСтрок

@ИмяТаблицы VARCHAR(20),

@ЧислоСтрок INT OUTPUT

AS

IF ( @ИмяТаблицы = 'ВУЗ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM ВУЗ

 

IF ( @ИмяТаблицы = 'ГРУППА' )

SELECT @ЧислоСтрок = COUNT(*)

FROM ГРУППА

 

IF ( @ИмяТаблицы = 'ЗВАНИЕ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM ЗВАНИЕ

 

IF ( @ИмяТаблицы = 'ОЦЕНКА' )

SELECT @ЧислоСтрок = COUNT(*)

FROM ОЦЕНКА

 

IF ( @ИмяТаблицы = 'ОЦЕНКА_ПО_ЗАЧЕТУ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM ОЦЕНКА_ПО_ЗАЧЕТУ

 

IF ( @ИмяТаблицы = 'ОЦЕНКА_ПО_ЭКЗАМЕНУ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM ОЦЕНКА_ПО_ЭКЗАМЕНУ

 

IF ( @ИмяТаблицы = 'ПРЕДМЕТ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM ПРЕДМЕТ

 

IF ( @ИмяТаблицы = 'ПРЕПОДАВАТЕЛЬ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM ПРЕПОДАВАТЕЛЬ

 

IF ( @ИмяТаблицы = 'СПЕЦИАЛЬНОСТЬ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM СПЕЦИАЛЬНОСТЬ

 

IF ( @ИмяТаблицы = 'СТУДЕНТ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM СТУДЕНТ

 

IF ( @ИмяТаблицы = 'УЧЕННАЯ_СТЕПЕНЬ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM УЧЕННАЯ_СТЕПЕНЬ

 

IF ( @ИмяТаблицы = 'ФАКУЛЬТЕТ' )

SELECT @ЧислоСтрок = COUNT(*)

FROM ФАКУЛЬТЕТ

 

GO

 

DECLARE @NAME_TABLE VARCHAR(20), @COUNT_STR INT

SET @NAME_TABLE = 'СТУДЕНТ'

 

EXEC pr_ПодсчетСтрок @NAME_TABLE, @COUNT_STR OUTPUT

 

SELECT @NAME_TABLE [НАЗВАНИЕ_ТАБЛИЦЫ],@COUNT_STR [ЧИСЛО_СТРОК]

 

GO

 

/*Создайте хранимую  процедуру, которая изменяет все  даты, имеющиеся 

в таблицах базы данных, добавляя к ним или отнимая от них некоторое

число дней. Эта  процедура должна иметь два входных  параметра. Первый

параметр определяет, нужно ли увеличивать или уменьшать  все даты.

Второй параметр указывает количество дней, которые  должны добавляться 

или отниматься.*/

 

CREATE PROCEDURE pr_ИзменениеДаты

@ФЛАГ VARCHAR(20),

@КОЛ_ДНЕЙ INT

AS

 

IF( @ФЛАГ = 'ДОБАВИТЬ' )

UPDATE ОЦЕНКА_ПО_ЗАЧЕТУ

SET ДАТА = ДАТА + @КОЛ_ДНЕЙ

UPDATE ОЦЕНКА_ПО_ЭКЗАМЕНУ

SET ДАТА = ДАТА + @КОЛ_ДНЕЙ

IF( @ФЛАГ = 'УБАВИТЬ' )

UPDATE ОЦЕНКА_ПО_ЗАЧЕТУ

SET ДАТА = ДАТА - @КОЛ_ДНЕЙ

UPDATE ОЦЕНКА_ПО_ЭКЗАМЕНУ

SET ДАТА = ДАТА - @КОЛ_ДНЕЙ

 

GO

 

DECLARE @FLAG VARCHAR(20), @KOL_DNEJ INT

SET  @FLAG = 'ДОБАВИТЬ'

SET @KOL_DNEJ = 2

 

EXEC pr_ИзменениеДаты @FLAG, @KOL_DNEJ

GO

 

/*Создайте хранимую  процедуру, которая для конкретной  таблицы 

подсчитывает  минимальное, среднее, максимальное и суммарное

значения в  столбце числового типа. Эта процедура  должна иметь один

входной параметр (с помощью которого указывается  имя или номер 

столбца) и четыре выходных параметра.*/

 

CREATE PROCEDURE pr_MinAvrMaxSum_ОЦЕНКА

@ИМЯ_СТОЛБЦА VARCHAR(20),

@МИНИМУМ INT OUTPUT,

@СРЕДНЕЕ INT OUTPUT,

@МАКСИМУМ INT OUTPUT,

@СУММАРНОЕ INT OUTPUT

AS

IF ( @ИМЯ_СТОЛБЦА = 'ОЦЕНКА' )

SELECT @МИНИМУМ = MIN(ОЦЕНКА), @СРЕДНЕЕ = AVG(ОЦЕНКА), @МАКСИМУМ = MAX(ОЦЕНКА), @СУММАРНОЕ = SUM(ОЦЕНКА)

FROM ОЦЕНКА

IF ( NOT @ИМЯ_СТОЛБЦА LIKE 'ОЦЕНКА' )

SELECT @МИНИМУМ = 0, @СРЕДНЕЕ = 0, @МАКСИМУМ = 0, @СУММАРНОЕ = 0

 

GO

 

DECLARE @MIN INT, @MAX INT, @AVG INT, @SUM INT, @IMIA_ST VARCHAR(20)

SET @IMIA_ST = 'ОЦЕНКА'

EXEC pr_MinAvrMaxSum_ОЦЕНКА @IMIA_ST, @MIN OUTPUT, @AVG OUTPUT, @MAX OUTPUT, @SUM OUTPUT

SELECT @MIN [МИН], @AVG [СРЕДНЯЯ], @MAX [МАКС], @SUM [СУММ]

 

GO

 

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

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

иметь два входных параметра, задающих значения для отбора данных по каждому из

этих столбцов. При этом значение NULL должно трактоваться как отсутствие какого-либо

критерия отбора строк по данному столбцу (например, если оба параметра будут иметь 

значение NULL, то должны быть выбраны все строки таблицы).*/

 

CREATE PROCEDURE pr_Оценка