ЛАБОРАТОРНОЕ ЗАНЯТИЕ № 1

Цель занятия: Приобретение навыков настройки и работы в среде Query Analyzer. Изучение синтаксиса команды SQL: CREATE TABLE.

Результат занятия: Создание базы данных «Успеваемость», которая в дальнейшем используется как учебный пример.

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

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

· Студент (Student)

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

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

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

· Предмет (Subject)

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

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

· Группа (SGroup)

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

Таблица содержит данные о студенте. Каждый студент имеет уникальный номер зачетки, который является первичным ключом отношения. Схема отношения имеет вид: R(Student)={NRecordBook, StName, 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

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

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

Таблица 2

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

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

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

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

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

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

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

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

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

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

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

Таблица 3

Таблица 4

Таблица 5

Таблица 6

Таблица 7

Таблица 8

Таблица 9

Таблица 10

Для определения 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 – задает ограничения на значения атрибутов.