Задание 26
Создать базу данных «РАБОТА ДЕКАНАТА в ВУЗе». Возможные таблицы: факультеты, группы, студенты, кафедры, преподаватели, предметы.
-
Структура базы данных
Логическая структура разрабатываемой базы данных должна содержит 6 сущностей:
-
факультет;
-
кафедра;
-
преподаватель;
-
дисциплина;
-
группа;
-
студент.
В системе Erwin были проведены следующие действия:
-
Была создана новая модель данных (тип модели – «Логическая / Физическая», целевая база данных – «MS Access»).
-
В логической модели были созданы 6 сущностей.
-
В логической модели были созданы необходимые связи «один ко многим» между сущностями.
-
Для созданных связей были установлены правила для ссылок как RESTRICT так и CASCADE.
Полученная, в итоге, модель Erwin, приведенная на рисунке 1, является
графическим представлением логической структуры разрабатываемой базы данных.
Рисунок 1-Логическая структура базы данных
Используя средство системы Erwin «Forward Engineer» для экспортирования структуры в СУБД были созданы необходимые таблицы с заданными параметрами.
Рисунок 2 – Физическая структура разрабатываемой базы данных
После выполнения прямого проектирования проверим результат:
Рисунок 3 – Схема данных
-
Создание 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