Практическая работа считается выполненной, если

Практическая работа считается выполненной, если

- предоставлен отчет о результатах выполнения задания;

- проведена защита проделанной работы.

Защита проводится в два этапа:

1) демонстрируются результаты выполнения задания;

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

Вариант задания выдается преподавателем, проводящим лабораторные занятия.

1.1.Критерии оценки:

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

- Оценка «хорошо» выставляется студенту за полное выполнение всех заданий с незначительными ошибками без помощи преподавателя.

- Оценка «удовлетворительно» выставляется студенту за полное выполнение всех заданий с незначительными ошибками с помощью преподавателя.

- Оценка «неудовлетворительно» выставляется студенту за выполнение заданий с большим количеством ошибок или за невыполнение заданий.

Практическая работа № 1

Тема: «Разработка серверной части базы данных в СУБД. Создание таблиц».

Цель работы: Научиться создавать таблицы базы данных в СУБД MySQL.

Время выполнения: подготовка: 3 мин; выполнение: 77 мин; проверка: 10 мин; всего: 90 мин.

Указания по выполнению практической работы

Возможности монитора MySQL

Создание базы данных средствами SQL выполняется с помощью команды

CREATE DATABASE <ИмяБазыДанных>.

Существующие в системе базы данных:

show databases;

Большинство команд, которые набираются на мониторе, должно заканчиваться точкой с запятой, иначе MySQL не выполнит их. Это позволяет разделить сложные команды на несколько строк для улучшения их восприятия.

Команда

use имя_базы_данных;

сообщает MySQL о том, с какой конкретной базой данных вы хотите работать.

Выбрав базу данных, можно увидеть, какие таблицы она содержит, если набрать:

show tables;

Получить информацию о структуре конкретной таблице можно с помощью команды

describe имя_таблицы;

Выйти из программы монитор можно c помощью команды \q.

Задание 1

1. Изучить структуру и возможности MySQL

2. Загрузить MySQL

3. Просмотреть какие базы данных существуют

4. Выбрать одну из существующих баз данных

5. Просмотреть какие таблицы содержит выбранная база данных

6. Получить информацию о каждой таблице из выбранной базы данных

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

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

· Студент (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, который в свою очередь является потенциальным ключом.

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

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

Таблица Вид занятий (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. Таблица Студент (Student)

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

№ зачетной книжки

NRecordBook

Varchar

Primary key

Имя студента

StName

Varchar

Код группы

IDGroup

INT

Foreign key

Серия паспорта

SPasport

Varchar

Not null Unique

Номер паспорта

NPasport

Varchar

Дата выдачи

DataPasport

DateTime

Наименование организации

NameDeptPasport

Varchar

ИНН

INN

Varchar

Unique

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

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Идентификационный номер преподавателя

IDP

INT

Primary key

Имя преподавателя

TeachName

Varchar

Название кафедры

DeptName

Varchar

Должность

TeachPost

Varchar

Дата приема на работу

DateHire

DateTime

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

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Идентификатор успеваемости

IDu

INT

Primary key

Код вида отчетности

IDReport

INT

Not null, Foreign key

Код предмета

IDSubject

INT

Not null, Foreign key

№ зачетки

NRecordBook

Varchar

Not null, Foreign key

№ семестра

NTerm

Numeric

Not null

Идентификационный номер преподавателя

IDP

Numeric

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

Primary key

Название предмета

NameSubject

Varchar

Unique

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

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Код отчетности

IDReport

INT

Primary key

Название вида отчетности

NameReport

Varchar

Unique

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

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Код вида занятий

IDWork

INT

Primary key

Название вида занятия

NameWork

Varchar

Unique

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

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Код группы

IDGroup

INT

Primary key

Название группы

NGroup

Varchar

Unique

Описание правил написания синтаксиса языка SQL

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

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

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

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

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

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

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

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

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

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

Для создания таблиц используется оператор CREATE TABLE. Сокращенный синтаксис оператора:

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]

create_definition:

col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY]

type:

TINYINT[(length)

или SMALLINT[(length)]

или MEDIUMINT[(length)]

или INT[(length)]

или INTEGER[(length)]

или BIGINT[(length)]

или REAL[(length,decimals)]

или DOUBLE[(length,decimals)]

или FLOAT[(length,decimals)]

или DECIMAL(length,decimals)

или NUMERIC(length,decimals)

или CHAR(length) [BINARY]

или VARCHAR(length) [BINARY]

или DATE

или TIME

или TIMESTAMP

или DATETIME

или TINYBLOB

или BLOB

или MEDIUMBLOB

или LONGBLOB

или TINYTEXT

или TEXT

или MEDIUMTEXT

или LONGTEXT

или ENUM(value1,value2,value3,...)

или SET(value1,value2,value3,...)

Описание:

IF NOT EXISTS – попытка создания таблицы делается только в том случае, если она отсутствует в БД.

tbl_name – желаемое имя таблицы.

(create_definition,...) – описание полей создаваемой таблицы.

col_name – желаемое имя создаваемого поля.

type – тип поля (TINYINT, SMALLINT и т.д.). После типа поля в некоторых случаях в круглых скобкахследует длина поля.

NOT NULL (NULL ) – может ли принимать значение NULL.

DEFAULT default_value – значение по умолчанию.

AUTO_INCREMENT – автонумерация значений поля при вводе данных в таблицу.

PRIMARY KEY – первичный индекс.

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

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

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

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

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

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

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

Удаление таблиц

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

Синтаксис команды:

DROP TABLE<имя таблицы> [,<имя таблицы>]

Таким образом, одной командой мы можем удалить не одну, а несколько таблиц.

Задание 1

1. Создать базу данных

CREATE DATABASE Student;

2. Создать таблицы базы данных.

CREATE TABLE Subject

(IDSubject INT NOT NULL AUTOINCREMENT PRIMARY KEY,

NameSubject VARCHAR (35) NOT NULL) INGINE=InnoDB;

Здесь и всегда сообщение об успешном выполнении команды CREATE имеет вид: The command(s) completed successfully.

CREATE TABLE Progress

( IDuINT NOT NULL,

NRecordBook VARCHAR(6) NOT NULL ,

PIN INT,

IDSubject INT,

IDReport INT,

NTerm INT,

Mark SMALLINT ) INGINE=InnoDB;

CREATE TABLE Student

(NRecordBook VARCHAR (6) NOT NULL PRIMARY KEY,

INN VARCHAR(10),

StName VARCHAR(35),

IDGroup INT,

SPasport VARCHAR(4),

NPasport VARCHAR(6) NOT NULL,

DataPasport Datetime,

NameDeptPasport VARCHAR(35)) INGINE=InnoDB;

3. Создайте таблицы Teacher, UPlan, Work, SGroup.

4. Создайте таблицы базы данных в соответствии со своим вариантом.

Контрольные вопросы

1. Как создать базу данных?

2. Что можно задать на уровне столбца при создании таблицы оператором CREATE TABLE? Можно ли на уровне столбца описать составной первичный ключ?

3. Как посмотреть информацию о конкретной таблице?

4. Можно ли добавить ограничение NOT NULL для столбца? Как это сделать?

5. Можно ли добавить значение по умолчанию для столбца? Как это сделать?

Практическая работа №2

Тема: «Разработка серверной части базы данных в СУБД MySQL. Модификация таблиц».

Цель:научиться изменять структуру таблиц базы данных.

Время выполнения: подготовка: 3 мин; выполнение: 32 мин; проверка: 10 мин; всего: 45 мин.

Команда ALTER TABLE

Если при создании таблицы были допущены ошибки в ее описании, исправить их можно несколькими способами. Во-первых, если таблица еще не содержит информации, ее можно просто удалить и создать снова. В противном случае, целесообразно использовать команду ALTER TABLE.

Команда ALTER TABLE позволяет изменить структуру таблицы и имеет следующий синтаксис:

ALTER TABLE ИмяТаблицы

КакИзменить [, КакИзменить ] ...

КакИзменить:

| ADD [COLUMN] (ИмяСтолбца ОпределениеСтолбца,… )

[FIRST|AFTER ИмяСтолбца]

| ADD {INDEX|KEY} ИмяИндекса (ИмяСтолбцаВИндексе,...)

| ADD [CONSTRAINT [Имя1]] PRIMARY KEY

(ИмяСтолбцаВИндексе,...)

| ADD [CONSTRAINT [Имя2]]

UNIQUE [INDEX|KEY]

ИмяИндекса(ИмяСтолбцаВИндексе,...)

| ADD [FULLTEXT|SPATIAL] [INDEX|KEY]

ИмяИндекса(ИмяСтолбцаВИндексе,...)

| ADD [CONSTRAINT [Имя3]]

FOREIGN KEY [Имя4] (ИмяСтолбца,...)

ОпределениеСсылки

| ALTER [COLUMN] ИмяСтолбца

ОпределениеСтолбца [FIRST|AFTER ИмяСтолбца]

| MODIFY [COLUMN] ИмяСтолбца ОпределениеСтолбца

[FIRST | AFTER ИмяСтолбца]

| DROP [COLUMN] ИмяСтолбца

| DROP PRIMARY KEY

| DROP {INDEX|KEY} ИмяИндекса

| DROP FOREIGN KEY Имя5

| RENAME [TO] НовоеИмяТаблицы

ИмяСтолбцаВИндексе:

ИмяСтолбца [(length)] [ASC | DESC]

Таким образом, команда позволяет:

ADD [COLUMN] – добавить столбец;

ADD {INDEX|KEY} – добавить индекс;

ADD [CONSTRAINT [Имя1]] PRIMARY KEY – добавить ограничение первичного ключа, при этом Имя1 – это имя ограничения. По умолчанию система сама создает системное имя

ограничения;

ADD [CONSTRAINT [Имя2]] FOREIGN KEY [Имя3]

(ИмяСтолбца,...) ОпределениеСсылки – Добавить ограничение внешнего ключа, где Имя2 и Имя3 – имена ограничений;

ALTER [COLUMN] ИмяСтолбца {SET DEFAULT Литерал

| DROP DEFAULT} – Задать новое значение по умолчанию для столбца или удалить значение по умолчанию для столбца;

CHANGE [COLUMN] СтароеИмяСтолбца НовоеИмяСтолбца

ОпределениеСтолбца [FIRST|AFTER ИмяСтолбца] – изменить имя и определение столбца, а также указать имя столбца, перед или после которого будет следовать данный столбец;

MODIFY [COLUMN] ИмяСтолбца ОпределениеСтолбца – изменить определение столбца;

DROP [COLUMN] ИмяСтолбца – Удалить столбец;

DROP PRIMARY KEY – Удалить первичный ключ;

DROP {INDEX|KEY} ИмяИндекса – Удалить индекс по имени;

DROP FOREIGN KEY Имя4 – Удалить ограничение внешнего

ключа по имени ограничения;

RENAME [TO] НовоеИмяТаблицы – переименовать таблицу;

Задание 1

1. Добавить столбец YearBegin (год начала учебы в институте) в таблицу Student, задав тип данных Datetime.

ALTER TABLE Student ADD YearBegin Datetime;

2. Добавить столбец IDFacultet (Код факультета) в таблицу SGroup, задав тип данных INT и определив значение по умолчанию 1.

ALTER TABLE SGroup ADD IDFacultet INT Default 1;

3. Добавить в таблицу SGroup, содержащую данные, столбец IDFacultet, установив для него ограничение NOT NULL и значение по умолчанию 0..

ALTER TABLE SGroup ADD IDFacultet INT NOT NULL DEFAULT 0;

4. Добавить в таблицу SGroup столбец IDFacultet, установив для него ограничение NOT NULL.

ALTER TABLE SGroup ADD IDFacultet INT NOT NULL;

5. Переименовать таблицу Teachers в Teacher.

ALTER TABLE Teachers CHANGE TO Teacher;

6. Переименовать поле IDP в PIN в таблице UPlan.

ALTER TABLE UPlan CHANGE IDP PIN NOT NULL;

7. Переименовать поле NGroup таблицы SGroup в NameGroup c типом INT.

ALTER TABLE SGroup CHANGE NGroup NameGroup INT;

Задание 2

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

Контрольные вопросы

1. Какие операции изменения структуры таблицы позволяет делать оператор ALTER TABLE?

2. Как удалить определение первичного ключа?

3. Как удалить определение внешнего ключа?

4. Можно ли добавить ограничение NOT NULL для столбца? Как это сделать?

5. Можно ли добавить значение по умолчанию для столбца? Как это сделать?

6. В каких случаях оператор удаления таблицы DROP TABLE не работает и таблицу не удается удалить?

7. Как вставить новый столбец за определенным полем или сделать его первым в таблице?

8. Как переименовать таблицу?

9. Как переименовать столбец?

10. Как изменить тип данных в столбце?

Практическая работа № 3

Тема: «Разработка серверной части базы данных в СУБД MySQL. Создание индексов (простых, уникальных, ключевых)»

Цель: научиться создавать простые, уникальные и ключевые индексы.

Время выполнения: подготовка: 3 мин; выполнение: 32 мин; проверка: 10 мин; всего: 45 мин.

Задание 1

1. Удалите все таблицы, созданные в практической работе№1.

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

Задание 2

1. Разработайте SQL-запросы для создания простых, уникальных индексов, ключевых полей для таблиц базы данных выбранного варианта.

Контрольные вопросы

1. Что такое индекс? Для чего используются индексы?

2. Перечислить и кратко охарактеризовать типы индексов в MySQL.

3. Каким образом можно создать индекс? Для каких полей

следует создавать индексы?

4. Какие команды языка SQL работают с индексами?

5. С помощью какой команды можно просмотреть созданные для таблицы индексы?

Практическая работа № 4

Тема: Разработка серверной части базы данных в СУБД MySQL. Ограничения

Цель: научиться применять в таблицах ограничения к значениям полей таблицы.

Время выполнения: подготовка: 3 мин; выполнение: 32 мин; проверка: 10 мин; всего: 45 мин.

Задание 1

1. Удалите таблицы, созданные в практической работе №3

2. Создать таблицу Progress, назначить ограничения PRIMARY KEY и запретить ввод в таблицу оценок, отличных от 2,3,4,5, т.е. создать ограничение для значений столбца Mark в таблице Progress.

Решение.

CREATE TABLE Progress

(NRecordBook Varchar(6)

CONSTRAINT ProgressStudentForeign FOREIGN KEY

REFERENCES Student,

PIN INT,

IDSubject INT,

IDReport INT,

NTerm Varchar(2),

Mark SMALLINT

CONSTRAINT ProgressMarkCheck CHECK(Mark BETWEEN 2 AND 5 ))INGAIN = InnoDB;

Задание 2

1. Задайте ограничения для других таблиц рассматриваемой базы данных.

2. Задайте ограничения для полей таблиц базы данных вашего варианта.

Контрольные вопросы

1. На каких уровнях создаются ограничения?

2. Какие ключевые слова используются при создании ограничений?

3. Что можно написать в скобках после ключевого слова CHECK?

4. Можно ли в условии ограничения создавать сложные выражения?

Практическая работа № 5

Тема: «Разработка серверной части базы данных в СУБД MySQL. Установление связей между таблицами».

Цель: научиться устанавливать связи между таблицами, используя язык SQL.

Время выполнения: подготовка: 3 мин; выполнение: 32 мин; проверка: 10 мин; всего: 45 мин.

Ограничение FOREIGN KEY

Синтаксис на уровне столбца

[СONSTRAINT <имя ограничения>] FOREIGN KEY

REFERENCES <имя ссылочной таблицы> [(<имя столбца> )]

[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}] [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}]

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

Необязательные конструкции ON DELETE и ON UPDATE позволяют задать поведение СУБД при удалении и обновлении строк из таблицы-предка. Параметры, следующие за этими ключевыми словами, имеют следующие значения:

· CASCADE – при удалении или обновлении записи в таблице-предке, содержащей первичный ключ, записи со ссылками на это значение в таблице-потомке удаляются или обновляются автоматически;

· SET NULL – при удалении или обновлении записи в таблице-предке, содержащей первичный ключ, в таблице-потомке значения внешнего ключа, ссылающегося на таблицу-предка, устанавливаются в null;

· NO ACTION – при удалении или обновлении записей, содержащих первичный ключ, с таблицей-потомком никаких действий не производится;

· RESTRICT – если в таблице-потомке имеются записи, ссылающиеся на первичный ключ таблицы-предка, при удалении или обновлении записей с таким первичным ключом возвращается ошибка;

· SET DEFAULT – согласно стандарту SQL, при удалении или обновлении первичного ключа в таблице-потомке для ссылающихся на него записей в поле внешнего ключа должно устанавливаться значение по умолчанию (в MySQL это ключевое слово зарезервировано, но не обрабатывается).

Задача.

Назначить в таблице Успеваемость (Progress) внешний ключ и организовать ссылку по внешнему ключу на все ссылочные таблицы.

Решение.

Ссылочными по отношению к отношению Progress являются таблицы Student, Teacher, Subject, Report, следовательно, в таблице будет четыре внешних ключа.

CREATE TABLE Progress

(NRecordBook VARCHAR(6),

PIN INT,

IDSubject INT,

IDReport INT,

NTerm VARCHAR(2),

Mark SMALLINT,

CONSTRAINT ProgressPrimary PRIMARY KEY

(NrecordBook,IDSubject,IDReport,NTerm),

CONSTRAINT ProgressStudentForeign FOREIGN KEY (NRecordBook)

REFERENCES Student,

CONSTRAINT ProgressSubjectForeign FOREIGN KEY (IDSubject)

REFERENCES Subject,

CONSTRAINT ProgressReportForeign FOREIGN KEY (IDReport) REFERENCES Report,

CONSTRAINT ProgressTeacherForeign FOREIGN KEY

(PIN) REFERENCES Teacher ) INGAIN = InnoDB;

Внешние ключи являются по своей сути двунаправленными. По умолчанию SQL Server 2000 запретит удаление строки в ссылочной таблице, если ей соответствует хотя бы одна строка в ссылающейся таблице. То есть нельзя удалить информацию о студенте, если в таблице Progress есть хотя бы одна запись, которая соответствует этому студенту. Опция ON DELETE CASCADE указывает на то, что при удалении записи в ссылочной таблице, автоматически удаляются записи в ссылающейся таблице. Опция же ON UPDATE CASCADE позволяет при модификации строки в родительской таблице модифицировать

соответствующие строки в дочерних таблицах. По умолчанию устанавливается опция NO ACTION, запрещающая в одном случае удаление, в другом – редактирование.

Задание 1

1. Удалите таблицы, созданные в практической работе №4.

2. Обеспечить каскадное удаление в таблице Progress при удалении соответствующих записей в ссылочных таблицах.

Решение

CREATE TABLE Progress

(NRecordBook VARCHAR(6),

PIN INT,

IDSubject INT,

IDReport INT,

NTerm VARCHAR(2),

Mark SMALLINT,

CONSTRAINT ProgressPrimary PRIMARY KEY

(NrecordBook,IDSubject,IDReport,NTerm),

CONSTRAINT ProgressStudentForeign FOREIGN KEY (NRecord Book) REFERENCES Student ON DELETE CASCADE,

CONSTRAINT ProgressSubjectForeign FOREIGN KEY (IDSubject)

REFERENCES Subject ON DELETE CASCADE,

CONSTRAINT ProgressReportForeign FOREIGN KEY (IDReport)

REFERENCES Report ON DELETE CASCADE,

CONSTRAINT ProgressTeacherForeign FOREIGN KEY

(PIN) REFERENCES Teacher ON DELETE CASCADE ) INGAIN = InnoDB;

Обратим внимание на тот факт, что опция ON DELETE CASCADE указывается для каждого ограничения FOREIGN KEY, если мы хотим, чтобы каскадное удаление выполнялось при выполнении операции удаления данных в каждой ссылочной таблице.

3. Удалить таблицу Progress.

Задание 2

Создайте базу данных book

База данных book состоит из четырех таблиц:

· catalogs – список торговых каталогов;

· books – список предлагаемых книг;

· users – список зарегистрированных пользователей магазина;

· orders – список заказов (осуществленных сделок).

Таблица catalogs состоит из двух полей:

· cat_ID – уникальный код каталога;

· cat_name – имя каталога.

Оба поля должны быть снабжены атрибутом not null, поскольку неопределенное значение для них недо­пустимо.

Таблица books состоит из семи полей:

· book_ID – уникальный код книги;

· b_name – название книги;

· b_author – автор книги;

· b_year – год издания;

· b_price – цена книги;

· b_count – количество книг на складе;

· b_cat_ID – код каталога из таблицы catalogs.

Цена книги b_price и количество экземпляров на складе b_count могут иметь атрибут null. На момент доставки час­то неизвестны количество товара и его цена, но отразить факт наличия товара в прайс-листе необходимо.

Поле b_cat_ID устанавливает связь между таблицами catalogs и books. Это поле должно быть объявлено как внешний ключ (FK)с правилом каскадного удаления и обновления. Обновление таблицы catalogs вызовет автоматическое обновление таблицы books. Удаление каталога в таблице catalogs приведет к автоматическому удалению всех записей в таблице books, соответствующих каталогу.

Таблица users состоит из семи полей:

· user_ID – уникальный код покупателя;

· u_name – имя покупателя;

· u_patronymic – отчество покупателя;

· u_surname – фамилия покупателя;

· u_phone – телефон покупателя (если имеется);

· u_email – e-mail покупателя (если имеется);

· u_status – статус покупателя.

Статус покупателя представлен полем типа enum, которое может принимать одно из четырех значений:

· active – авторизованный покупатель, который может осуществлять покупки через Интернет;

· passive – неавторизованный покупатель (значение по умолчанию), который осуществил процедуру регистрации, но не подтвердил ее и пока не может осуществлять покупки через Интернет, однако ему доступны каталоги для просмотра;

· lock – заблокированный покупатель, не может осуществлять покупки и просматривать каталоги магазина;

· gold – активный покупатель с хорошей кредитной историей, которому предоставляется скидка при следующих покупках в магазине.

Поля u_phone и u_email могут быть снабжены атрибутом null. Остальные поля должны получить атрибут not null.

Таблица orders вклю­чает пять полей:

· order_ID – уникальный номер сделки;

· o_user_ID – номер пользователя из таблицы users;

· o_book_ID – номер товарной позиции из таблицы books;

· o_time – время совершения сделки;

· o_number – число приобретенных товаров.

Поля таблицы orders должны быть снабжены атрибутом not null, т. к. при совершении покупки вся информация должна быть занесена в таблицу.

В таблице orders устанавливается связь с таблицами users (за счет поля o_user_id) и books (за счет поля o_book_id). Эти поля объявлены как внешние ключи (FK)с правилом каскадного удаления и обновления. Обновление таблиц users и books приведет к автоматическому обновлению таблицы orders. Удаление любого пользователя в таблице users приведет к автоматическому удалению всех записей в таблице orders, соответствующих этому пользователю.

DROP DATABASE IF EXISTS book;

CREATE DATABASE book;

USE book;

CREATE TABLE catalogs (

cat_ID int(6) NOT NULL AUTO_INCREMENT,

cat_name varchar(20) NOT NULL,

PRIMARY KEY (cat_ID)

) INGINE =InnoDB;

CREATE TABLE books (

book_ID int(6) NOT NULL AUTO_INCREMENT,

b_name varchar(100) NOT NULL,

b_author varchar(100) NOT NULL,

b_year year NOT NULL,

b_price decimal(7,2) NULL default '0.00',

b_count int(6) NULL default '0',

b_cat_ID int(6) NOT NULL default '0',

PRIMARY KEY (book_ID),

FOREIGN KEY (b_cat_ID) REFERENCES catalogs(cat_ID) ON DELETE
CASCADE ON UPDATE CASCADE

) INGINE =InnoDB;

CREATE TABLE users (

user_ID int(6) NOT NULL AUTO_INCREMENT,

u_name varchar(20) NOT NULL,

u_patronymic varchar(20) NOT NULL,

u_surname varchar(20) NOT NULL,

u_phone varchar(12) NULL,

u_email varchar(20) NULL,

u_status ENUM ('active','passive','lock','gold') default 'passive',

PRIMARY KEY (user_ID)

) INGINE=InnoDB;

CREATE TABLE orders (

order_ID int(6) NOT NULL AUTO_INCREMENT,

o_user_ID int NOT NULL,

o_book_ID int NOT NULL,

o_time datetime NOT NULL default '0000-00-00 00:00:00',

o_number int(6) NOT NULL default '0',

PRIMARY KEY (order_ID),

FOREIGN KEY (o_book_ID) REFERENCES books (book_ID) ON DELETE
CASCADE ON UPDATE CASCADE,

FOREIGN KEY (o_user_ID) REFERENCES users (user_ID) ON DELETE CASCADE ON UPDATE CASCADE

) INGAIN=InnoDB;

Задание 3

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

2. Просмотрите структуры созданных таблиц.

Контрольные вопросы

1. Как осуществляется ссылочная целостность?

2. Как обеспечить каскадное удаление и обновление данных?

3. Какие типы таблиц могут участвовать в установлении связей?

Практическая работа № 6

Тема: «Создание структуры базы данных с помощью case-средства».

Цель работы: Научиться разрабатывать серверную часть базы данных case-средства.

Время выполнения: подготовка: 5 мин; выполнение: 165 мин; проверка: 10 мин; всего: 180 мин.

Создание EER-диаграммы

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

Для построения моделей предназначена секция Data Modeling:

Выберем пункт Create new EER Model.

EER model расшифровывается как Extended Entity-Relationship Modelи переводится как Расширенная модель сущностей-связей.

По умолчанию имя созданной модели myDB. Щелкните правой кнопкой мыши по имени модели и выберите в появившемся меню пункт Edit schema.В появившемся окне можно изменить имя модели. Назовем ее, например, kontora. В именах таблиц и столбцов нельзя использовать русские буквы.

В этом окне также нужно настроить так называемую «кодовую страницу» для корректного отображения русских букв внутри таблиц. Для этого выберите из списка пункт «cp1251- cp1251_general_ci». Окно свойств можно закрыть.

Диаграмму будем строить с помощью визуальных средств. Щелкнем по пункту Add diagram, загрузится пустое окно диаграммы:

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

Будем создавать таблицу Отделы со следующими столбцами: номер_отдела, полное_название_отдела, короткое_название_отдела. Переименуем table1 в k_deptи начнем создавать столбцы.

Каждый столбец имеет:

- имя (не используйте русские буквы в имени!),

- тип данных. Самые распространенные типы данных:

a) INT – целое число;

b) VARCHAR(размер) – символьные данные переменной длины, в скобках указывается максимальный размер;

c) DECIMAL(размер, десятичные_знаки) – десятичное число;

d) DATE – дата:

e) DATETIME – дата и время.

Далее ра