Constraint progresssubjectforeign Foreign KEY (idsubject) references Subject,

Введение

Настоящее методическое пособие предназначено для выполнения лабораторных работ по дисциплинам Управление данными, Базы и банки знаний, Системы управления базами данных (СУБД) включает в себя описание 8 лабораторных работ, контрольные вопросы, на которые требуется дать ответы при защите работы, теоретический материал необходимый для выполнения работы.

Следует отметить, что данное пособие является лишь частью комплекса учебных пособий по дисциплинам Базы и банки знаний, Управление данными разработанных кафедрой Информационные системы и технологии Ухтинского государственного технического университета. Весь же комплект пособий включает в себя тексты лекций, учебные пособия для выполнения практических работ, контрольных работ, курсовых проектов и курсовых работ, а также пособия для самостоятельной работы студентов.

Несомненно, данное пособие должно помочь студенту закрепить основные положения из курса базы и банки знаний по направлению 654700 (специальность 230201). Однако наиболее полно освоить данный курс студент может только, используя все пособия по данной дисциплине, входящие в комплект, а также литературу, список которой приведен автором в заключительной части пособия.

Лабораторные работы могут выполняться с использованием СУБД Microsoft SQL, а также любой СУБД, поддерживающей стандарт ANSI/ISO SQL2.

Язык SQL - Structure Query Language (Язык структурных запросов) является одним из доминирующих языков, используемых в реляционных базах данных. В качестве стандартного языка баз данных он принят такими организациями как American National Standards Insnitute –ANSI (Американский национальный институт стандартов) и International Standards Organization – ISO (Международная организация стандартов).

SQL является непроцедурным языком, что позволяет ему обрабатывать наборы записей и автоматизировать процесс управления данными. SQL не требует от пользователя спецификации метода доступа к данным, пользователь вообще не должен описывать, как должна выполняться та или иная операция над данными, он просто описывает, что должно быть сделано с данными, предоставляя право системе решать, как это сделать.

SQL включает в себя три основные составляющие:

1. Язык управления данными Data Direction Language (DDL)

2. Язык манипулирования данными Data Manipulation Language (DML)

3. Язык запросов к базе данных

Целью лабораторных работ является изучение основных составляющих этого языка.

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

ЛАБОРАТОРНОЕ ЗАНЯТИЕ № 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

Внешние ключи отношения Успеваемость (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		Not null
Имя студента	StName	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

Таблица Вид занятий (Work)
Название атрибута	Имя поля	Тип поля	Размер	Ограничения
Код вида занятий	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 – задает ограничения на значения атрибутов.

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

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

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

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

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

Создание таблиц

Синтаксис команды CREATE TABLE для создания таблицы без ограничений:

CREATE TABLE [<имя базы данных>.[<имя владельца>| <имя владельца>]

<имя таблицы> ({<имя столбца> <тип данных> (<размер>)});

Пример 1

Задача.

Создать таблицу Subject [1] cо столбцами IDSubject, NameSubject.

Решение.

CREATE TABLE Subject

(IDSubject INT,

NameSubject VARCHAR (35));

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

Пример 2

Задача.

Создать таблицу Subject cо столбцами IDSubject (значение которого генерируется автоматически) и NameSubject.

Решение.

CREATE TABLE Subject

(IDSubject INT IDENTITY,

NameSubject VARCHAR (35));

 

Автоматическая генерация значения столбца IDSubject достигается за счет использования свойства IDENTITY, по умолчанию начальное значение, генерируемое с помощью IDENTITY равно 1, так же как и его приращение. Таким образом, следующее значение будет равно 2. Значения в IDENTITY-столбцах обязательно последовательные, то есть если приращение положительное, то следующее значение всегда больше предыдущего, если приращение отрицательное, то – всегда меньше. Приращение и начальное значение могут быть заданы, однако этот механизм чрезвычайно редко используется в реальных проектах.

Пример 3

Задача.

Создать таблицу Subject cо столбцами IDSubject (значение которого генерируется автоматически, начиная со значения 2 с шагом 2) и NameSubject.

Решение.

CREATE TABLE Subject

(IDSubject INT IDENTITY (2,2),

NameSubject VARCHAR (35));

 

Не нуждается в доказательстве, что таблица без ограничений не имеет смысла. Например, в таблице Subject – IDSubject является первичным ключом, другой атрибут NameSubject – потенциальным. В первом случае необходимо наложить ограничение PRIMARY KEY, во втором – UNIQUE, кроме того, целесообразно наложить ограничение NOT NULL на оба атрибута.

 

Синтаксис команды CREATE TABLE для создания таблицы c ограничениями:

CREATE TABLE [<имя базы данных>.[<имя владельца>] <имя таблицы>

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

[,…n])

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

Ограничения на уровне таблицы целесообразно вводить, если они имеют отношения к нескольким столбцам. Ограничения для одного столбца рекомендуется устанавливать на уровне столбца. Все ограничения за исключением ограничения NULL/NOT NULL создаются как на уровне столбца, так и на уровне таблицы. Каждому созданному ограничению за исключением ограничений NULL/NOT NULL присваивается уникальное имя. Если вы хотите сами присвоить имя тому или иному ограничению, то необходимо при создании ограничения воспользоваться предложением CONSTRAINT. Синтаксис предложения CONSTRAINT, задающего в команде CREATE TABLE ограничения, как на уровне таблицы, так и на уровне столбца приведен ниже.

Синтаксис предложения CONSTRAINT:

[CONSTRAINT <имя ограничения>]

[PRIMARY KEY (<имя столбца>[, …n]) ]

[UNIQUE (<имя столбца>[, …n]) ]

[NOT NULL (<имя столбца>[, …n]) ]

[CHECK (<условие>)]

[FOREIGN KEY (<имя столбца, ссылающейся таблицы>[, …n])

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

таблицы [, …n])]]

!

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

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

Имена ограничений

Договоримся, назначая ограничение PRIMARY KEY использовать шаблон: <имя таблицы><тип ограничения>, поскольку ограничение PRIMARY KEY может быть в таблице только одно. Для ограничений FOREIGN KEY будем использовать шаблон - <имя ссылающейся таблицы><имя ссылочной таблицы><тип ограничения>. Для ограничения CHECK <имя таблицы><имя столбца><тип ограничения>, а для ограничения UNIQUE <имя таблицы><имя одного из столбцов><тип ограничения>.

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

Ограничения NULL и NOT NULL

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

Пример 4

Задача.

Создать таблицу Subject cо столбцами IDSubject (значение которого генерируется автоматически) и NameSubject, исключив возможность неопределенных значений для обоих атрибутов.

Решение.

CREATE TABLE Subject

(IDSubject INT IDENTITY NOT NULL,

NameSubject VARCHAR (35) NOT NULL);

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

Ограничение PRIMARY KEY задает значение первичного ключа на уровне столбца или таблицы.

Особенности при создании ограничения PRIMARY KEY:

· В таблице может быть только один первичный ключ.

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

· Одна и та же комбинация столбцов не может быть объявлена одновременно в качестве уникального и первичного ключа.

Cинтаксис предложения CONSTRAINT, задающего ограничение PRIMARY KEY на уровне таблицы.

[,СONSTRAINT <имя ограничения>] PRIMARY KEY (<список столбцов>)

Пример 5

Задача.

Создать таблицу Subject, установив ограничение первичного ключа на уровне столбца IDSubject.

Решение.

CREATE TABLE Subject

(IDSubject INT IDENTITY

CONSTRAINT SubjectPrimary PRIMARY KEY,

NameSubject VARCHAR (35) NOT NULL)

В данном случае на атрибут IDSubject можно дополнительно не накладывать ограничение NOT NULL, это произойдет автоматически при наложении ограничения PRIMARY KEY.

Пример 6

Задача.

Создать таблицу Progress, установив ограничение первичного ключа на уровне таблицы.

Решение.

CREATE TABLE Progress

(NRecordBook VARCHAR(6),

PIN INT,

IDSubject INT,

IDReport INT,

NTerm INT,

Mark SMALLINT

CONSTRAINT ProgressPrimary PRIMARY KEY (NRecordBook,

IDSubject, IDReport, NTerm));

Cинтаксис предложения CONSTRAINT, задающего ограничение PRIMARY KEY на уровне столбца

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

Ограничение UNIQUE

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

Cинтаксис предложения CONSTRAINT, задающего ограничение UNIQUE на уровне столбца

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

Пример 7

Задача.

Создать ограничение UNIQUE в таблице Subject для столбцаNameSubject.

Решение.

Ограничение затрагивает только один столбец, и оно может быть задано на уровне столбца.

CREATE TABLE Subject

(IDSubject INT IDENTITY

CONSTRAINT SubjectPrimary PRIMARY KEY,

NameSubject VARCHAR(35) NOT NULL

CONSTRAINT SubjectNameSubjectUnique UNIQUE);

В этом случае целесообразно установить ограничение NOT NULL на атрибут NameSubject так как автоматически оно не устанавливается.

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

[, СONSTRAINT <имя ограничения>] UNIQUE (<список столбцов>)

Пример 8

Задача.

Установить ограничения UNIQUE в таблице Student.

Решение.

При установлении ограничений следует обратить внимание на следующее: в отношении студент существует несколько множеств атрибутов, которые могут однозначно идентифицировать любую строку в таблице. Во-первых, это ИНН, во-вторых, Номер зачетки, в-третьих, так называемые паспортные данные, которые включают в себя Номер паспорта, Серию паспорта, Наименование организации, выдавшей паспорт и Дату выдачи. В качестве первичного ключа был выбран Номер зачетки, поскольку именно значение этого атрибута не меняется в процессе жизни базы данных, по крайней мере, его значение определяется внутренними требованиями ВУЗа и может быть ими же отрегулировано. Вероятность изменения ИНН, как это не грустно, существует, а что касается третьего множества, то первой причиной того, чтобы отвергнуть его использование в качестве первичного ключа является то, что оно составное, во-вторых, вероятность того, что его значения будут меняться в процессе жизни базы данных, очень велика. Для этого студентке нужно просто выйти замуж и сменить фамилию, или кому-то из студентов потерять паспорт. Таким образом, создавая таблицу Student, целесообразно кроме ограничения PRIMARY KEY, создать два ограничения UNIQUE.

CREATE TABLE Student

(NRecordBook VARCHAR (6)

CONSTRAINT StudentPrimary PRIMARY KEY,

INN VARCHAR(10)

CONSTRAINT StudentINNUnique UNIQUE,

StName VARCHAR(35),

IDGroup INT,

SPasport VARCHAR(4),

NPasport VARCHAR(6),

DataPasport Datetime,

NameDeptPasport VARCHAR(35),

CONSTRAINT StudentSPasportUnique UNIQUE (SPasport, NPasport, DataPasport, NameDeptPasport));

Задание 1

Создать таблицы Report, Teacher, SGroup, установив все необходимые ограничения.

Ограничение Foreign key

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

Пример 9

Задача.

Назначить в таблице Успеваемость (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,

Ограничение CHECK

С помощью ограничения CHECK задаются ограничения на значения атрибутов.

Cинтаксис ограничения CHECK на уровне столбца

[СONSTRAINT <имя ограничения>] CHECK (<условие>)

Пример 11

Задача.

Создать таблицу 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),

CONSTRAINT ProgressPrimary PRIMARY KEY

(NrecordBook,IDSubject,IDReport,Nterm),

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)

Команда CHECK также не может ссылаться на значения столбцов в других строках.

Задание 2

Удалить все созданные таблицы. Повторить создание учебного примера[2] согласно описаниям приведенным выше.

Если таблицы созданы и связаны правильно, то на диаграмме созданной в Enterprise Manager это будет выглядеть так, как показано на следующем рисунке (см. Рисунок 1).

 

 

 

Рисунок 1

Вопросы для самоконтроля к лабораторной работе № 1

1. К какой группе команд SQL следует отнести команду CREATE?

2. Даны два отношения R={ AB } и R={ A C}. В какой последовательности следует создавать таблицы, чтобы задать ограничения целостности, используя только команду Create?

3. Какие ограничения могут быть установлены в отношении?

4. С какой целью используется предложение ON DELETE Cascade?

5. Какова область действия ограничений в таблице?

6. Какие требования предъявляются к именам объектов базы данных?

7. Когда целесообразно устанавливать ограничения на уровне таблицы?

8. Когда целесообразно устанавливать ограничения на уровне столбца?

9. С какой целью устанавливаются ограничения?

10. В чем отличие ограничений Primary key и Unique?

11. Каких правил следует придерживаться при назначении имен ограничений?

12. Для каких типов данных ширина столбца не является обязательным параметром?

13. Какой тип данных может быть присвоен только единственному столбцу в таблице?

14. При каком определении ограничения FOREIGN KEY строка в ссылочной таблице не может быть удалена?

15. На какие столбцы запрещена ссылка в выражении ограничения CHECK?

16. Для каких столбцов обязательно должно быть установлено ограничение NOT NULL?

 

Лабораторная работа № 2

Цель занятия: Изучение синтаксиса команд языка манипулирования данными (Data Manipulation Language - DML).

Три хорошо известные операции над кортежами:

§ Добавление

§ Правка

§ Удаление

реализуются в SQL с помощью команд:

§ INSERT

§ UPDATE

§ DELETE

Команда вставки - INSERT

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

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

INSERT INTO {<имя таблицы>[(<имя столбца> [псевдоним] [, …n]] |[<подзапрос>]}

VALUES (<значение>[,…n]);

При реализации команды INSERT необходимо отслеживать, чтобы

· Последовательность данных в предложение VALUES, соответствовала порядку столбцов в таблице.

· Заполнялись все столбцы с признаком NOT NULL.

Пример 12

Задача.

Ввести в таблицу SGroup значения названия групп ИСТ-01 и АИС-01.

Решение.

INSERT INTO SGroup (NameGroup)

VALUES('АИС-01');

INSERT INTO SGroup (NameGroup)

VALUES('ИСT-01');

При успешном выполнении каждой команды вы получите сообщение: 1 row(s) affected

Обратим внимание на то, что поле IDGroup заполняется автоматически, поскольку имеет свойство IDENTITY. Попытка выполнить, например, такую команду

INSERT INTO SGroup (IDGroup, NameGroup)

VALUES(3,'ИСT-02');

Приведет к сообщению об ошибке.

Server: Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table 'SGroup' when IDENTITY_INSERT is set to OFF.

Однако если вы все-таки хотите ввести код группы вручную вам необходимо отключить действие IDENTITY с помощью команды SET IDENTITY_INSERT.

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

SET IDENTITY_INSERT <имя таблицы> { ON | OFF }

Опция - ON отключает процесс автоматического присвоения identity-значений, OFF - включает.

Пример 13

Задача.

Ввести в таблицу SGroup значения названия групп ИСТ-02, присвоив столбцу IDGroup значение 3.

Решение.

SET IDENTITY_INSERT SGroup ON [3]

INSERT INTO SGroup (IDGroup, NameGroup)

VALUES(3,'ИСT-02');

Несмотря на то, что в команде INSERT список столбцов является необязательным, его рекомендуется указывать явно. Последнее позволяет избежать недиагностируемых ошибок при заполнении столбцов. Если вы все-таки хотите отказаться от перечисления столбцов, то вам придется отслеживать, чтобы порядок столбцов в таблице соответствовал порядку столбцов в команде INSERT.

Пример 14

Задача.

Ввести данные, приведенные ниже (см. Приложение 2. Пример заполнения таблиц.) в таблицу Student.

Решение.

Попытка ввода первой строки с помощью приведенной ниже команды

INSERT INTO Student

VALUES('050001','Иванов И.И.',3,'8701','192355','01.06.2002','ГОВД г.Ухты','1111111111')

даст сообщение об ошибке:

Syntax error converting datetime from character string

Ошибка вызвана несоответствием типов данных, причиной же ее послужило нарушение порядка столбцов в списке VALUES. (Надо отметить, что появление сообщения об ошибке, является наилучшим исходом. Дела бы обстояли хуже, если бы не возникло конфликта, вызванного несоответствием типа данных или размера данных. В этом случае результатом стала бы некорректно заполненная таблица.) Исправить ошибку можно или, изменив порядок следования данных в предложении:

INSERT INTO Student

VALUES('050001','1111111111','Иванов И.И.',3,'8701','192355','01.06.2002', 'УВД г.Ухты');

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

INSERT INTO Student (NRecordBook,Stname,IDGroup,SPasport,NPasport,DataPasport, NameDeptPasport,INN)

VALUES('050002', 'Петров П.П',3,'8702','191256','11.20.2002', 'УВД г.Сосногорск', '1111111112')

Естественно, что если столбец не имеет признак NOT NULL, то его значения могут не вводиться, например, в следующем примере не вводится значение ИНН.

INSERT INTO Student (NRecordBook,Stname,IDGroup,SPasport,NPasport,DataPasport, NameDeptPasport)

VALUES('050003','Сидоров С.С.', 2,'8703','192457','11.26.2002', 'УВД г.Ухты')

INSERT INTO Student (NRecordBook)

VALUES('050004')

Данные в таблицу можно внести и из других таблиц, используя запрос SELECT. Реакцией системы, как и в предыдущем случае при успешном выполнении команды будет фраза N row affected (создано N строк), где в качестве N указывается количество созданных строк.

Задание 3

Создать таблицу Student1, аналогичную таблице Student и заполнить ее данными из таблицы приложения (см. Приложение 2. Пример заполнения таблиц.)

Пример 15

Задача.

Ввести в таблицу Student записи из таблицы Student1.

Решение.

INSERT INTO Student

SELECT *

FROM Student1;

Команда обновления - UPDATE

Команда UPDATE предназначена для редактирования данных в таблице.

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

UPDATE {<имя таблицы> [SET (<имя столбца>)] = <выражение> [,…n]|<подзапрос>] WHERE <условие>};

В случае успешного выполнения команды выдается сообщение (N row(s) affected) (N записей задействовано), где N - количество редактируемых записей.

Пример 16

Задача.

В отношении Student в строку с номером зачетки ' 050004' ' ввести имя студента.

Решение.

UPDATE Student

SET StName='Митькин М.М.'

WHERE NRecordBook='050004';

Выполнение следующего запроса позволит вывести на экран содержание всей таблицы и проверить результат предыдущей операции.

SELECT * FROM Student;

Результат выполнения команды UPDATE:

!

Если предложение WHERE не задано, то исправления вносятся во все строки таблицы. Будьте внимательны при выполнении команд DELETE, UPDATE.

Пример 17

Задача.

Изменить регистр (с верхнего на нижний) при написании имени студента в отношении Student.

Решение.

UPDATE Student

SET StName=LOWER (StName);

Результат операции посмотрим, используя следующий запрос

SELECT NRecordBook, StName

FROM Student

Результат выполнениязапроса:

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

Команда удаления - DELETE

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

DELETE FROM{<имя таблицы> WHERE <условие>};

Команда DELETE удаляет записи в таблице. В случае успешного выполнения команды выдается сообщение: N row(s) affected (N записей задействовано), где N - количество удаленных записей. Еще раз следует напомнить, что, как и в случае с командой UPDATE не следует забывать про предложение WHERE, в противном случае вы удалите все содержимое таблицы.

Пример 18

Задача.

Удалить записи, в которой значение атрибута имя студента (StName) Митькин М.М.

Решение.

DELETE FROM Student

WHERE StName='Митькин М.М.';

!

Пример 19

ВНИМАНИЕ! Если вы готовы удалить все содержимое вашей таблицы, то можете опробовать следующую команду.

Задача.

Удалить все записи из таблицы Student.

Решение.

DELETE FROM Student;

Следует указать, что существует еще одна команда, позволяющая удалить записи из таблицы, это команда TRUNCATE. Отличие команды DELETE от команды TRUNCATE состоит в том, что после ее выполнения действие этой команды можно отменить посредством команды ROLLBACK[4].

Задание 4

Удалить данные из ранее созданных таблиц и заполнить таблицы в соответствии с приложением (см.Приложение 2. Пример заполнения таблиц).

Вопросы для самоконтроля к лабораторной работе № 2

1. Как называется язык, к которому относятся команды INSERT, UPDATE, DELETE?

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

3. Какие предложения являются обязательными в команде INSERT?

4. Чему будет равна мощность отношения Plan после выполнения команды DELETE FROM Plan?

5. Какие предложения являются обязательными в команде DELETE?

6. Какие предложения являются обязательными в команде UPDATE?

7. Как удалить из таблицы повторяющиеся строки?

8. При каком синтаксисе команды UPDATE корректируются значения во всех записях отношения?

9. В чем отличие команд DELETE и TRUNCATE?

Лабораторная работа №3

Цель занятия: Изучить команды DDL: ALTER TABLE, DROP TABLE.

Команда ALTER TABLE

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

Команда ALTER TABLE позволяет

· Добавлять и удалять столбцы.