Описание учебного примера

В пособии порядок выполнения заданий лабораторных работ продемонстрирован на примере базы данных «Успеваемость студентов», которая включает в себя следующие таблицы:

· Студент (Student)

· Преподаватель (Teacher)

· Успеваемость (Progress)

· Учебный план (UPlan)

· Предмет (Subject)

· Отчетность (Report)

· Вид занятий (SWork)

· Группа (SGroup)

Таблица Студент (Student).

Таблица содержит данные о студенте. Каждый студент имеет уникальный номер зачетки, который является первичным ключом отношения. Схема отношения имеет вид: R(Student)={ NRecordBook, SName, IDGroup, SPasport, NPasport, DataPasport, NameDeptPasport, INN}, что аналогично R(Студент)={ N зачетки, Имя студента, Код группы, Серия паспорта, Номер паспорта, Наименование организации, ИНН}. Внешним ключом является IDGroup (Код группы). Комбинация атрибутов SPasport, NPasport, DataPasport, NameDeptPasport должна быть уникальна, также должен быть уникальным атрибут INN, который в свою очередь является потенциальным ключом.

Таблица Преподаватель (Teacher).

Таблица содержит данные о преподавателе. Каждый преподаватель имеет уникальный идентификационный номер, который является первичным ключом отношения. Схема отношения имеет вид: R(Teacher)={ PIN, TeachName, DeptNname, TeachPost, DateHire}, что аналогично R(Преподаватель) = { Идентификационный номер преподавателя, Имя преподавателя, Название кафедры, Должность, Дата приема на работу}. Таблица не содержит внешних ключей.

Таблица Успеваемость (Progress).

В таблице хранятся оценки, полученные студентом за весь период его обучения в институте, как при сдаче курсовых работ, экзаменов и т.п., так и при сдаче зачетов. Известно, что зачеты могут быть дифференцированными или не дифференцированными. В первом случае результат сдачи оценивается по той же системе, что и экзамен, во втором - в ведомости фиксируется только факт сдачи зачета («зачет» или «незачет»). Договоримся в поле Mark (Оценка) заносить значение 1, если «зачет» и 0 – если «незачет». Первичным ключом в отношении являются атрибуты: № зачетки, Код предмета, Код вида отчетности, № cеместра. Схема отношения имеет вид: R(Progress)={ NRecordBook, PIN, IDSubject, IDReport, NTerm, Mark}, что соответствует схеме R(Успеваемость)={ № зачетки, Идентификационный номер преподавателя, Код предмета, Код вида отчетности, № семестра, Оценка}. Внешние ключи приведены ниже (см. Таблица 1).

Таблица 1

Внешние ключи отношения Успеваемость (Progress)
Внешний ключ	Ссылочное отношение
№ зачетки (NRecordBook)	Студент (Student)
Идентификационный номер преподавателя (PIN)	Преподаватель (Teacher)
Код предмета(IDSubject)	Предмет (Subject)
Код вида отчетности (IDReport)	Отчетность (Report)

Таблица Учебный план (UPlan).

В таблице содержится информация о предметах, которые изучают студенты той или иной группы, о количестве часов, отводящихся для того или иного вида занятий (лекций, практических, лабораторных и т.п.). Ключ отношения выделен на схеме. R(UPlan)={ IDSubject, IDWork, IDGroup, NTerm, Clock, PIN) или - R(План)={ Код предмета, Код вида занятия, Код группы, Семестр, Кол-во часов, Идентификационный номер преподавателя }. Внешние ключи отношения План (UPlan) приведены ниже (см. Таблица 2).

Таблица 2

Внешние ключи отношения План (UPlan)

Внешний ключ	Ссылочное отношение
Код вида занятия (IDWork)	Вид занятий (Work)
Идентификационный номер преподавателя (PIN)	Преподаватель (Teacher)
Код предмета (IDSubject)	Предмет (Subject)
Код группы (IDGroup)	Группа (SGroup)

Таблица Предмет (Subject).

Таблица представляет собой справочник предметов, изучаемых студентом. Первичным ключом отношения является Код предмета (IDSubject). Схема отношения имеет вид: R(Subject)={ IDSubject, NameSubject}, аналог этой схемы R(Предмет)={ Код предмета, Название предмета}. Атрибут NameSubject (Название предмета) должен быть уникальным. Таблица не содержит внешних ключей.

Таблица Отчетность (Report).

Таблица представляет собой справочник видов отчетности. Домен атрибута Название вида отчетности будет включать в себя экзамен, зачет, курсовой проект и т.п. R(Report)={ IDReport, NameReport}, или R(Отчетность)={ Код вида отчетности, Название вида отчетности}. Атрибут NameReport (Название вида отчетности) должен быть уникальным. Таблица не содержит внешних ключей.

Таблица Вид занятий (SWork).

Таблица представляет собой справочник видов занятий. Домен атрибута Название вида занятия будет включать в себя следующие значения: лекция, практическое занятие, лабораторное занятие и т.п. R(SWork)={ IDWork, NameWork}, или R(Виды занятий)={ Код вида занятия, Название вида занятия}. Атрибут NameWork (Название вида занятия) должен быть уникальным. Таблица не содержит внешних ключей.

Таблица Группа (SGroup).

Таблица представляет информацию о группах обучающихся в ВУЗе. Первичным ключом отношения является IDGroup (Код группы). Домен атрибута Название группы будет включать в себя все названия групп ВУЗа. R(SGroup)={ IDGroup, NameGroup}, или R(Группа)={ Код группы, Название группы}. Атрибут NameGroup (Название группы) должен быть уникальным. Таблица не содержит внешних ключей.

Перед созданием таблицы следует предварительно определить типы полей и их размер. С этой целью анализируются возможные значения тех или иных атрибутов, а также методы обработки, которым они будут подвергаться. Основные типы данных, имеющие место в СУБД SQL Server 2000, даны в приложении (см. Приложение 1).

Структура таблиц учебного примера приведена ниже (см.
Таблица 3 - Таблица 9), где первичные ключи выделены цветом и подчеркнуты.

Таблица 3

Таблица Студент (Student)
Название атрибута	Имя поля	Тип поля	Размер	Ограничения
№ зачетной книжки	NRecordBook	Varchar		Not null
Имя студента	SName	Varchar
Код группы	IDGroup	INT		Foreign key
Серия паспорта	SPasport	Varchar		Not null Unique
Номер паспорта	NPasport	Varchar
Дата выдачи	DataPasport	DateTime
Наименование организации	NameDeptPasport	Varchar
ИНН	INN	Varchar		Unique

Таблица 4

Таблица Преподаватель (Teacher)
Название атрибута	Имя поля	Тип поля	Размер	Ограничения
Идентификационный номер преподавателя	PIN	INT		Not Null
Имя преподавателя	TeachName	Varchar
Название кафедры	DeptName	Varchar
Должность	TeachPost	Varchar
Дата приема на работу	DateHire	DateTime

Таблица 5

Таблица Успеваемость (Progress)
Название атрибута	Имя поля	Тип поля	Размер	Ограничения
Код вида отчетности	IDReport	INT		Not null, Foreign key
Код предмета	IDSubject	INT		Not null, Foreign key
№ зачетки	NRecordBook	Varchar		Not null, Foreign key
№ семестра	NTerm	Numeric		Not null
Идентификационный номер преподавателя (PIN)	PIN	INT		Foreign key
Оценка	Mark	SmallInt

Таблица 6

Таблица Учебный план (UPlan)
Название атрибута	Имя поля	Тип поля	Размер	Ограничения
Код предмета	IDSubject	INT		Not null, Foreign key
№ семестра	NTerm	Varchar		Not null
Код вида занятия	IDWork	INT		Not null, Foreign key
Код группы	IDGroup	INT		Not null, Foreign key
Идентификационный номер преподавателя	PIN	INT		Not null, Foreign key
Количество часов	Clock	Numeric

Таблица 7

Таблица Предмет (Subject)
Название атрибута	Имя поля	Тип поля	Размер	Ограничения
Код предмета	IDSubject	INT		Not null
Название предмета	NameSubject	Varchar		Unique

Таблица 8

Таблица Отчетность (Report)
Название атрибута	Имя поля	Тип поля	Размер	Ограничения
Код отчетности	IDReport	INT		Not null
Название вида отчетности	NameReport	Varchar		Unique

Таблица 9

Таблица Вид занятий (SWork)
Название атрибута	Имя поля	Тип поля	Размер	Ограничения
Код вида занятий	IDWork	INT		Not null
Название вида занятия	NameWork	Varchar		Unique

Таблица 10

Таблица Группа (SGroup)
Название атрибута	Имя поля	Тип поля	Размер	Ограничения
Код группы	IDGroup	INT		Not null
Название группы	NameGroup	Varchar		Unique

Для определения SQL операторов здесь и далее применяется расширенная форма BNF-нотации (Backus Naur Form).

Прописные буквы используются для записи зарезервированных слов.

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

Вертикальная строка (|) указывает на необходимость выбора одного из нескольких приведенных значений.

Фигурные скобки определяют обязательный элемент – например, {SELECT}.

Угловые скобки (< >) означают, что вместо параметра обозначенного в них, должно проставляться его конкретное значение, при этом угловые скобки аннулируются.

Квадратные скобки определяют необязательный элемент [DISTINCT].

Многоточие (…) используется для указания необязательной возможности повторения конструкции. Наименьшее допустимое количество итераций равно нулю. Например, CREATE TABLE <имя таблицы>

(<имя столбца> <тип данных> (<размер>) [<ограничение для столбца>]

[, <имя столбца> <тип данных> (<размер>) [<ограничение для столбца>]… ])

[,<ограничение для таблицы>];

Рекомендации по написанию операторов SQL:

· Каждая фраза в операторе должна начинаться с новой строки.

· Начало каждой фразы должно начинаться с той же позиции, что и начало предыдущей фразы.

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

С помощью команды CREATE создается база данных и все ее объекты.

CREATE DATABASE Student ON PRIMARY

(Name=' Student ',

Filename='C:\Program Files\Microsoft SQL Server\MSSQL\Data\ Student.mdf')

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

· NULL/NOT NULL – разрешает или не разрешает неопределенность значений атрибутов;

· UNIQUE – разрешает только уникальные значения атрибутов;

· PRIMARY KEY – определяет первичный ключ отношения (в каждом отношении может иметь место только один первичный ключ);

· FOREIGN KEY – определяет внешний ключ отношения (в одном отношении может быть несколько внешних ключей);

· CHECK – задает ограничения на значения атрибутов.