Выполнение лабораторной работы. Изменение определения таблицы

Изменение определения таблицы

Удаление данных

Для удаления данных в таблице используется директива delete [ from ] <имя таблицы> [ where <условие> ]. Здесь <условие> играет ту же роль, что и в операторе update.

Оператор:

delete from Оценка

where

Ном_зач = 87 and Дисциплина = 'БД'

go

удалит информацию о сдаче экзамена указанным студентом по заданной дисциплине.

Если в таблице, в которую осуществляется вставка из другой таблицы, присутствует столбец со свойством identity, для сохранения существующих ключей можно отключить генерацию значений identity с помощью директивы set identity_insert <имя таблицы> { on | off }. В MS SQL в один момент времени только для одной таблицы в сеансе может быть установлено on, при необходимости установить on для другой таблицы, необходимо предварительно выполнить сброс — off для текущей.

Таблица, созданная в БД (например, с помощью create table) существует, пока не будет явно удалена (например, с помощью drop table). Так как при удалении таблицы происходит удаление всех содержащихся в ней записей, в языке SQL предусмотрен оператор alter table, позволяющий осуществить модификацию таблицы сохранив, если это возможно, содержащиеся в ней сведения.

Основные варианты синтаксиса и действий, выполняемых с помощью alter table:

(i) alter table <имя таблицы> add <определение столбца> — добавление столбца к таблице, например:

alter table Студент add Ср_балл real

go

(ii) alter table <имя таблицы> drop column [8] <имя столбца> — удаление столбца таблицы, например:

alter table Студент drop column Ср_балл

go

(iii) alter table <имя таблицы> alter column [9] <определение столбца> — модификация столбца таблицы[10], например:

alter table Студент alter column ФИО varchar(70) not null

go

(iv) alter table <имя таблицы> rename <имя столбца> to <новое имя> — переименование столбца таблицы, в MS SQL не поддерживается, используется системная хранимая процедура (СХП):

sp_rename 'Студент.Группа', 'Номер_группы'

go

(v) alter table <имя таблицы> rename <новое имя> — переименование таблицы, в MS SQL не поддерживается, используется СХП:

sp_rename 'Оценка', 'Успеваемость'

go

(vi) alter table <имя таблицы> add constraint <определение ограничения> [11] — добавление ограничения к таблице, например:

alter table Группа add constraint Уник_номер unique (Номер)

go

(vii) alter table <имя таблицы> drop constraint <имя ограничения> [12] — удаление ограничения таблицы, например:

alter table Группа drop constraint Уник_номер

go

В качестве ограничений могут так же выступать помимо unique директивы primary key, foreign key, check, default. В приведенных примерах Уник_номер — имя ограничения. Если имя ограничения не указывается в create table или alter table, SQL-сервер присваивает ему автоматически сформированное имя. SQL-сервер может отклонить создание ограничения, если ему не соответствуют данные, ранее занесенные в таблицы.

Воспользуемся приведенными конструкциями для изменения текущей БД, зададим уникальность для номера группы:

alter table Группа add constraint Уник_номер unique (Номер)

go

Повторно заполним таблицу Группа:

delete from Группа

go

insert into Группа (Номер, Факультет)

select distinct Номер_группы, Факультет from Студент

go

Добавим в Студент столбец Группа:

alter table Студент add Группа smallint

go

и заполним его:

update Студент

set Группа =

(select Id from Группа

where Группа.Номер = Студент.Номер_группы)

go

удалим столбец Номер_группы:

alter table Студент drop column Номер_группы

go

Удалим столбец Факультет:

alter table Студент drop column Факультет

go

Определим столбец Группа как внешний ключ:

alter table Студент add constraint Вн_кл_группа

foreign key (Группа) references Группа (Id)

on delete set null

on update cascade

go

Зададим уникальность для номера зачетной книжки:

alter table Студент add constraint Уник_ном_зач unique (Ном_Зач)

go

Создадим справочник дисциплин:

create table Дисциплина (

Id smallint identity primary key,

Наименование varchar(50) not null constraint Уник_наимен unique)

go

Заполним справочник дисциплин:

insert into Дисциплина (Наименование)

select distinct Дисциплина from Успеваемость

go

Добавим в Успеваемость столбец Id_Дисциплины:

alter table Успеваемость add Id_Дисциплины smallint

go

и заполним его:

update Успеваемость

set Id_Дисциплины =

(select Id from Дисциплина

where Дисциплина.Наименование =

Успеваемость.Дисциплина)

go

Удалим столбец Дисциплина:

alter table Успеваемость drop column Дисциплина

go

Определим столбец Id_Дисциплины как внешний ключ:

alter table Успеваемость add constraint Вн_кл_дисциплина

foreign key (Id_Дисциплины) references Дисциплина (Id)

on delete no action

on update cascade

go

Определим столбец Id_Дисциплины как обязательный:

alter table Успеваемость alter column Id_Дисциплины smallint not null

go

Зададим ограничение уникальности (предполагается, что дисциплины изучаются один семестр и не может быть 2-х оценок по одной дисциплине):

alter table Успеваемость add constraint Уник_ном_зач_id_дисц

unique (Ном_Зач, Id_Дисциплины)

go

Зададим ограничение на значение оценки (фиксируются только положительные оценки, истории двоек и н/а не ведется):

alter table Успеваемость add constraint Полож_оценка

check (Оценка in (3, 4, 5))

go

Для просмотра результирующей схемы БД необходимо выполнить команду Создать диаграмму базы данных из контекстного меню узла Диаграммы баз данных в обозревателе объектов.

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

Для получения полного текста SQL для создания БД необходимо выполнить команды Задачи\Сформировать сценарии из контекстного узла, соответствующего базе данных в обозревателе объектов.

Выполнить вставку тестовых данных в таблицы, созданные в ходе выполнения лабораторной работы 1. В строках, вставляемых в таблицы, должны быть данные как удовлетворяющие, так и не удовлетворяющие условиям запросов, приведенных в варианте задания. В случае внесения в таблицы ошибочных данных произвести их корректировку операторами update и delete. При обнаружении недочетов в структуре БД произвести ее корректировку с помощью alter table.

Дата добавления: 2015-05-09