Карпова Т.С. Базы данных: модели, разработка, реализация

Подождите немного. Документ загружается.

Средства

изменения

описания

таблиц

средства удаления таблиц

151

[MODIFY

FILE

«спецификация

файлов>

IMODIFY

RLEGROUP

имя_группы_файлов

имя_свойства_группы

файлов}

Здесь свойства группы файлов определяет одно

из

допустимых ключевых слов:

READONLY

—

только

для

чтения;

READWRITE

— для

чтения

записи;

DEFAULT

—

назначает

данную группу

файлов

качестве

группы

по

умолчанию,

которой размещаются данные, если

не

задано дополнительных условий

размещения

информации.

Как

видно,

при

изменении схемы базы данных

в нее

могут быть добавлены

(ADD)

дополнительные файлы

файловые группы

или

удалены

(REMOVE

)

ранее опре-

деленные

файлы

или

файловые

группы.

Назначение

этих

файлов

нам

будет

бо-

лее

понятно после

того,

как мы

познакомимся

физическими моделями

фай-

ловыми

структурами, используемыми

для

хранения

данных

базах

данных.

Сейчас

мы

познакомимся

последней командой, которая

предназначена

для

уда-

ления

базы данных.

В MS SQL

Server

7.0 это

команда имеет следующий синтаксис;

DROP

DATABASE

databusejiame

После

выполнения

этой

команды

уничтожается

вся

база

данных

вместе

содер-

жащимися

в ней

данными,

Средства изменения описания

таблиц

средства удаления таблиц

язык

SQL

добавлены

средства

изменения

схемы

таблиц.

Что

можно

и что

нельзя

изменять

описании

таблицы?

стандарте SQL2

добавлены

достаточно

широкие

возможности

по

модификации

уже

существующих

схем

таблиц.

Для

модификации

таблиц

используется

оператор

ALTER TABLE,

который

позволяет

вы-

полнить

следующие

операции

изменения

для

схемы

таблицы:

СЗ

добавить

новый

ртолбец

в уже

существующую

заполненную

таблицу;

изменить

значение

по

умолчанию

для

какого-либо

столбца;

удалить

столбец

из

существующей

таблицы;

добавить

или

удалить

первичный

ключ

таблицы;

добавить

или

удалить

новый

внешний

ключ

таблицы;

добавить

или

удалить условие

уникальности;

добавить

или

удалить

условие

проверки

для

любого

столбца

пли

для

табли-

цы

целом.

Синтаксис

оператора

ALTER TABLE:

«Изменить

описание

таблицы>::°

ALTER TABLE

<имя

таблицы>

{

ADD

определение

столбца>

152

Глава

ВЛ1ринципы

поддержки целостности

реляционной

модели

данных

ALTER

<иия

столбца>

{SET

DEFAULT

<значение>

DROP

DEFAULT

} j

DROP

<иня

столбца>

{CASCADE

RESTRICT}

ADD

{

определение

первичного

ключа>|

определение

внешнего

ключа>

[

<условие

уникальности

данных>

<условие

проверки>

} {

DROP

CONSTRAINT

имя

условия

{

CASCADE

RESTRICT}

}

Одним оператором

ALTER

TABLE

можно провести только

одно

из

перечисленных

изменений,

например,

за

один

раз

мджно

добавить

один столбец. Если

вам

тре-

буется добавить

два

столбца,

то

необходимо

применить

два

оператора.

Давайте

рассмотрим

несколько примеров. Чаще всего

применяется

операция

до-

бавления столбца.

Предложение

определения нового столбца

операторе

ALTER

TABLE

имеет точно такой

же

синтаксис,

как и в

операторе создания

таблицы.

До-

бавим

столбец

EDUCATION

(образов!

ше),

содержащий символьный

тип

данных,

с за-

данным

перечнем

значении

(«начальное»,

«среднее»,

«неоконченное

высшее»,

«вьющее»).

ALTER TABLE READERS

ADD

EDUCATION

varchar

(30)

DEFAULT

NULL-

CHECK

(EDUCATION

NULL

EDUCATION=

"начальное"

EDUCATION=

"среднее

EDUCATION=

"неоконченное

высшее"

EDUCATION

"высшее"

)

таблицу

READERS

будет

добавлен столбец

EDUCATION,

который

по

умолчанию

будут добавлены

все

кортежи

неопределенного

значения.

дальнейшем

эти

зна-

чения

могут быть

заменены

на

одно

из

допустимых

символьных

значений.

Добавим

ограничение

на

соответствие между датами взятия

возврата

книги

таблице

EXEMPLAR.

Действительно,

если даты

введены,

то

требуется,

чтобы дата

возврата книги была

бы

больше

на

срок выдачи

книги.

Считаем,

что

стандарт-

ным

сроком являются

недели. Теперь сформулируем оператор

изменения

таб-

лицы

EXEMPLARE:

ALTER TABLE EXEMPLARE

ADD

CONSTRAINT

CK_

EXEMPLARE

CHECK

((DATAJN

NULL

AND

DATA_OUT

NULL)

(DATAJUT

>°

DATAJN

+14)

)

Здесь

мы

применили операцию сложения

календарной дате, которая

предпо,-

лагает,

что

добавляют

заданное

число дней.

Операция удаления столбца связана

проверкой ссылочной

целостности,

и по-

этому

не

разрешается удалять столбцы,

связанные

поддержкой

ссылочной

це-

Средства

изменения

описания таблиц

средства

удаления

таблиц

153

лостностн

таблицы,

то

есть

нельзя удалить столбцы родительской

таблицы,

вхо-

дящие

первичный ключ таблицы, если

на них

есть ссылки

подчиненных

таблицах.

При

изменении

первичного

ключа таблицы следует

быть

внимательными.

Во-

первых,

исходной таблицы могут быть

подчиненные,

при

этом первичный

ключ

исходной таблицы является внешним ключом

для

подчиненных таблиц,

просто

его

удалить

невозможно,

СУБД

контролирует

ссылочную

целостность

не

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

на

него имеются ссылки. Следовательно,

этом случае порядок

изменения

пер-

вичного

ключа должен

быть

таким,

как на

рис. 8.1:

154

Глава

Принципы

поддержки

целостности

реляционной

модели данных

Чаще

всего

операция

ALTER

TABLE

применяется

CASE-системах

при

автомати-

ческой

генерации скриптов создания таблиц

базе

данных.

этих системах

универсальный алгоритм предполагает сначала создание

всех

таблиц, которые

заданы

даталогической

модели,

только

после

этого добавляются соответ-

ствующие

связи.

И это

понятно

— в

отличие

от

человеческого разума

искусственный

интеллект

CASE-системы

будет испытывать

затруднения

опре-

делении

иерархических

взаимосвязей

таблиц базы данных, поэтому

он

предпо-

читает использовать универсальный алгоритм,

котором сначала

все

объекты

определяются,

затем

добавляются

соответствующие

свойства

для

атрибутов,

которые

являются внешними ключами

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

этом

случае

все

операции

назначения

внешних ключей

будут

считаться корректны-

ми,

потому

что все

объекты были описаны

заранее,

и для

такого алгоритма

по-

рядок создания таблиц безразличен.

приведен скрипт, который

был

полу-

чен

при

разработке схемы базы данных

«Библиотека»

Po\verDesigner6.1.

По

умолчанию

для

каждой

таблицы

создается

индекс

по

первичному

ключу»

так

что

кроме знакомых операций создания

изменения таблиц

мы

увидим

еще и

операцию создания индексов

(CREATE

INDEX),

после изучения физических

моде-

лей

базах данных

мы еще

вернемся

этой операции,

пока примем

ее на

веру.

При

создании

даталогичекой

модели

качестве

СУБД

был

выбран

сервер

SQL

Server

6.X,

и для

этого сервера скрипт

был

сгенерирован

на

устроенном

языке

этой

СУБД,

называмом

TransactSQL,

В нем

операция

USE

<икя

базы

дан-

ных>

соответствует операции открытия базы

данных,

команда

до

означает

пере-

ход к

выполнению

следующей

команды.

fTf

==Иогг=вивкг===Е=гаа1а£==г==;=я=ноЕз==!==:=я==5!£эяя=1=с!г;й:==ся=1£=зЯ1==ь:==г:

Database name:

LIBRARY

DBMS

name:

Microsoft

SQL

Server

6.x */

Created

on:

06.10.00

18:56

"ft

==cjos=:=e

ее=и====исзи=и==Е=и=аня=с===г:=1======1е'=з==г==:===1=:;=а1=«я

У*

==г=ап====и=е=1=а==а=»в=Е=========аея=»===е=5===езс!Е;веэе=з=!===:=ег

е1

Database

name;

LIBRARY

во-йигзоHs=J"=iaa=s==3=ааи==в=======e==:3=j=3==:

a==ess=я=я

e=KJae====J=t==3

use

LIBRARY

cts=i===:=ac3S==e=!

e==s=£s===s=a

саз

cj=s=========ii====i=Etsci=:c!=ci==«=;=:=

Table: BOOKS

jit

е==еп

=JOH

иняиэи

именвввеоаsевенеяавявивяяавввявиеезtaяРчиssesesasssi

create

table BOOKS

ISBN

varchar(14)

not

null.

TITLE varchar(255>

not

null.

AUTOR

varchar(30)

null,

COAUTOR

varchar(30)

null,

PUBLICHER

varchar(30>

null,

Средства

изменения

описания

таблиц

средства

удаления

таблиц

155

WHER£_PUBLICH

varchar(30)

null.

YEARJZD

smallint

not

null

constraint

CKCJEARJZDJQOKS

check

(

YEAR_PUBL

1969

AND

YEAR_PUBL

YEAR(GetDateC)}),

PAGES

smallint

not

null

constraint

CKC_PAGESJOOKS

check

(

PAGES between

5 and

1000).

constraint

PKJOOKS

primary

key

(ISBN),

constraint

CKT_BOOKS

check

(

(AUTOR

IS NOT

NULL

(AUTOR

NULL

AND

COAUTOR

NULL)))

)

CSSE=EE:=—

r=s=31=Е==я

ас

пя=с

пз

п=п=с;а

с=с=с

ЕЗП

сг=1==сз

п==

ее

гг=зш

ся^пссз=в

Table:

READERS

create

table

READERS

(

NUM_READER

intnot

null.

NAME

varcharOO)

not

null,

BIRTH__DAY

datetime

not

null

constraint

CKC_BIRTHJ)AY_READERS

check

(DateDiffCyear.

GetDateO.BIRTHjJAY)

>=i?

)),

SEX

chard)

not

null

constraint

CKC_SEX_READERS

check

(

SEX

CM'.'r.V.W)),

HOME_PHON

char(9)

null.

WORK_PHON

char(9)

null.

constraint

PKJEADERS

primary

key

(NUM_READER).

constraint

CKTJEAOERS

check

(

CHOMEJHON

IS NOT

NULL

WORK.PHON

IS NOT

NULL))

)

"if

ciC3Oi===i=c!ci=!ni==:nc:eEst3=s=!i=e«raEseaiKEB:i==Besi=o(3a!=ncis:aaHCsaca=CH==сзожхяа

Table:

CATALOG

/*t

ЕЭ

еы

c^-зс^:

&сз=«

^ia

си

ия^сги

асз==й»==с=в

Щи

е-а

попз

cseo

=ЗЕ:

st±=a===з

arts

create table CATALOG

(

KOD

smallint

not

null.

156

Глава

Принципы

поддержки

целостности

реляционной модели

данных

NAMEJCW

varchar(255)

null,

constraint

PK_CATALOG

primary

key

(KWJCOD)

)

t^f

__^

——.-..*»-^тд

•.

'St£lC~-

,т.г

t=f

[

ju—

—

,-,-

l-v

„

„._.*___„.„.

,_____^_,

Table:

EXEMPLAR

create

table

EXEMPLAR

(

INVJUMER

intnot

null.

ISBN

varchar(14)

not

null,

NUMJEADER

int

null,

PRESENT

bitnot

null,

DATEJN

datetime

null,

DATEJUT

datetime

nul

constraint

PKJXEMPLAR

primary

key

(INVJUMER)

)

=======«i==;s=i===ici=K===rs=======st=c:=!3====(===:=:=======ci»e=:=

Л/

Index:

RELATIOM3JK

====а

г==гав!=!

==:=.-!===—

=====i-ai=~==!==a-===eoc==

create

index

RELATION_43JK

EXEMPLAR

(ISBN)

«==t=====:===

=£

;±3

!=E

=B===:==

3c=

:c=:=====e=i=i==:i=c:c:c5i=E:=:=s:===Dcai3

Ж/

Index:

RELATIONJ4_FK

е=1=

===

сз=:==г:с=====я=13==Е==з==и========г:==а

£=««==="============

create

index

RELATIONJ4JK

EXEMPLAR

(NUHJEAOER)

=======a=s=

=:=::===

3=ae3i====:=====E:====oa===ci==a====c===:==B3=i==!3

Table:

RELATIONJ7

яа=

ос==иае===1====зc=c:i====sc===!i==ieaae=i==;at3=i=:=i====c===a===E:seci

"К/

create

table

RELATION_67

(

ISBN

varchar(14)

not

null,

KWJOD

small

int not

null.

constraint

PKJELATIOMJ7

primary

key

(ISBN,

KW_KOD)

)

Средства

изменения

описания

таблиц

средства

удаления таблиц

157

===ои=====е====в=!п

[

===5==1

е==о====авв==£=

н=

=о=в====1=

я=

,з

Index:

IOIINEONYJ_IAEANOE_CIAIEE_FK

вши===

=====

iaс=и========ииисвсг=а====ивн=======:=яоаыи=========а

create

index

IOIINEONY_EJAEANOE_CIAIEE_FK

RELATIQNJ7

(ISBN)

==a=c=====:==5====сет======ггвеиа

апис:=======ен

Е==в

н==========Q

в=

А/

Index:

IJWNOAAEAIAJUIEAAOJK

—=========J=

с===========:=o===tsc=====s3E:=:j=eei=is

=======я=

a it I

create

Index

I_AANOAAEAIA_A_EIEAAO_FK

RELATIONJ7

(KWKOO)

alter

table

EXEMPLAR

add

constraint

FKJXEMPLAR_RELATION_BOOKS

foreign

key

(ISBN)

references

BOOKS

(ISBN)

alter

table

EXEMPLAR

add

constraint

FKJXEMPLAR_RELATION_READERS

foreign

key

(NUMJEADER)

references

READERS

(NUM_REAOER)

alter

table

RELATIONJ7

add

constraint

FK_RELATION_IOIINEONY_BOOKS

foreign

key

(ISBN)

references

BOOKS

(ISBN)

alter

table

RELATIQN_67

add

constraint

FK_RELATION_I_AANOAAE_CATALOG

foreign

key

(KWJCOD)

references

CATALOG

(KWJCOD)

языке

SQL

присутствует-и

операция

удаления

таблиц.

Синтаксис

этой

опера-

ции

предельно прост:

<УдалиП

таблицу>:;=

DROP

TABLE

<иня

таблицы>

[CASCADE

RESTRICT]

Параметр

CASCADE

означает,

что при

удалении таблицы

одновременно

удаляются

все

объекты,

связанные

ней.

таблицей,

кроме

рассмотренных

ранее огра-

ничений,

могут

быть

связаны

также объекты

тина

триггеров

представления»

Понятие

представления

будет

рассмотрено

следующем

подразделе,

тригге-

ров

мы

коснемся

разделах,

связанных

архитектурой

клиент-сервер.

Однако

операция

удаления

объектов определяется

еще

правами

пользователей,

что

свя-

зано

концепцией

безопасности

базах данных.

Это

значит,

что

если

вы не яв-

ляетесь владельцем объекта,

то вы

можете

не

иметь

прав

на его

удаление.

И в

этом

случае

синтаксически

правильный оператор

DROP

TABLE

не

может быть

вы-

полнен

системой

силу отсутствия прав

на

удаление

связанных

удаляемой

15?

Глава

Принципы

поддержки

целостности

реляционной модели

данных

таблицей

объектов

Кроме

того,

операция

удаления таблицы

не

должна

нару-

шать

целостность базы данных, поэтому удалять

таблицу,

на

которую

имеются

ссылки

других таблиц, невозможно.

Например,

нашей

схеме, связанной

библиотекой,

мы

не

можем

удалить

ни

таблицу

BOOKS,

ни

таблицу

READERS,

ни

таблицу

CATALOG.

этих

таблиц есть связь

подчиненными таблицами

EXEMPLAR

RELATION

67.

Поэтому если

вы

хотите

уда-

лить

некоторый набор

таблиц,

то

сначала необходимо грамотно построить

по-

следовательность

их

удаления, которая

не

нарушит

базовых:

принципов

поддержки

целостности

вашей

схемы

БД. В

нашем

примере

последовательность

операторов

удаления таблиц может быть

следующей:

DROP

TABLE

EXEMPLAR

DROP

TABLE

RELATIOH_67

DROP

TABLE

CATALOG

DROP

TABLE

READERS

DROP

TABLE

BOOKS

Понятие

представления операции

создания

представлений

Для

описания

внешних

моделей

реляционной модели могут использоваться

представления.

Представление

(View)

- это

SQL-запрос

на

выборку,

который

пользователь воспринимает

как

некоторое виртуальное отношение. Задание

пред-

ставлении входит

описание схемы

БД в

реляционных

СУБД.

Представления

позволяют

скрыть ненужные несущественные

детали

для

разных пользователей,

модифицировать реальные структуры

данных

удобном

для

приложений виде

и,

наконец, разграничить права доступа

данным

тем

самым повысить

защиту

данных

от

несанкционированного

доступа.

отличие

от

реальной таблицы представление

в том

виде,

как оно

сконструиро-

вано,

не

существует

базе

данных,

это

действительно

только виртуальное

отно-

шение,

хотя

все

данные,

которые представлены

нем, действительно существуют

базе

данных,

но в

разных

отношениях.

Они

скомпонованы

для

пользователя

удобном виде

из

реальных таблиц

помощью

некоторого запроса.

Однако

поль-

зователь может этого

не

знать,

он

может обращаться

этим представлением

как

со

стандартной

таблицей.

Представление

при

создании

получает

некоторое

уни-

кально^

имя,

его

описание

хранится

описании схемы базы

данных,

СУБД

любой момент времени

при

обращении

этому представлению выполняет

запрос,

соответствующий

его

описанию, поэтому пользователь, работая

пред-

ставлением,

каждый момент времени видит

действительно

реальные,

актуаль-

ные

на

настоящий момент данные.

Оно

формируется

как бы на

лету,

момент

обращения.

Оператор

определения

представления имеет следующий

вид:

<создание

представлениям

CREATE

VIEW

<иня

представлена

(<шсок

столбцов>)]

<SQL-3anpoc>

Понятие

представления

операции

создания

представлений

159

При

необходимости

представлении

можно задать

новое

имя для

каждого столб-

ца

виртуальной таблицы.

При

этом

надо

помнить,

что

если указывается список

столбцов,

то

он

должен содержать ровно столько столбцов, сколько

содержит

Их

SQL-sanpoc.

Если

список

имей

столбцов

представлении

не

задай,

то

каждый столбец пред-

ставления

получает

имя

соответствующего столбца запроса.

Рассмотрим типичные виды представлений

и их

назначение.

Горизонтальное

представление

Этот

вид

представления широко

применяется

для

уменьшения

объема

реальных

таблиц

обработке

ограничения

доступа

пользователей

закрытой

для

них

информации.

Так,

например,

правилом хорошего тона

считается,

что

руководи-

тель подразделения

некоторой

фирме

может видеть оклады

результаты

ра-

боты только своих сотрудников,

этом

случае

для

него создается горизонталь-

ное

представление,

которое загружены строки общей таблицы

сотрудников,

работающих

в его

подразделении.

Например,

у нас

есть

таблица

«Сотрудник»

(EMPLOYEE)

полями

«Табельный

но-

мер»

(Т

NUH),

«ФИО»

(NAME),

«долЖНость»(Р051ТШ),

«оклад»(SALARY),

«надбав-

ка»

(PREM~i(JM),

«отдел»

(DEPARTMENT).

Для

приложения,

которым работает

начальник

отдела продаж, будет

создано

представление

CREATE

VIEW

SALJ3EPT

SELECT

FROM

EMPLOYEE

WHERE

DEPARTMENT-

"Отдел

продаж"

Вертикальное

представление

Этот

вид

представления практически соответствует

выполнению

операции

про-

ектирования

некоторого

отношения

на

ряд

столбцов.

Он

используется

основ-

ном

для

скрытия

информации,

которая

не

должна быть доступна

конкретной

внешней

модели.

Например,

для

работника

табельной

службы,

который учитывает

присутствие

сотрудников

па

работе,

информация

об

окладе

надбавке

должна

быть закрыта.

Для

него

можно создать следующее

вертикальное

представление:

CREATE

VIEW

TABEL

SELECT

TJUM.NAME,

POSITION.

DEPARTMENT

FROM

EMPLOYEE

160

Глава

Принципы

поддержки

целостности

реляционной

модели данных

Сгруппированные

представления

Эти

представления содержат запросы, которые имеют

группировку.

Сгруппиро-

ванные

представления всегда должны содержать список столбцов.

Они

могут

использовать агрегированные

функции

качестве

результирующих

столбцов,

а в

дальнейшем

это

представление

может использоваться

как

виртуальная

таб-

лица,

например,

других

запросах.

Создадим представление, которое

определяет

суммарный

фон

заработной платы

надбавок

по

каждому подразделению

указанием количества

сотрудников»

минимальной, максимальной

средней

зарплаты

надбавки

по

подразделению.

Такой запрос позволяет сравнить заработную плату

надбавки

прямо

по

всем

подразделениям,

и он

может быть очень эффективно использован администра-

цией

при

проведении

сравнительного

анализа подразделений фирмы.

CREATE

VIEW

RATE

DEPARTMENT,

COUNTC*).

SUM(SALARY),

SUM(PREHIUM),

MAXCSALARY).

MIN(SALARY),

AVERAGE

(SALARY),

MAX(PREMIUM).

MIN(PREMIUM).

AVERAGE

(PREMIUM)

SELECT

DEPARTMENT,

COUNT(*>.

SUM(SALARY),

SUM(PREMIUM).

MAX(SALARY),

MIN(SALARY).

AVERAGE

(SALARY).

MAX(PREMIUM),

MIN(PREMIUM).

AVERAGE

(PREMIUM)

FROM

EMPLOYEE

GROUP

DEPARTMENT

Объединенные

представления

Часто

представления

базируются

на

многотабличных

запросах.

Такое

использо-

вание

позволяет упростить разработку пользовательского

интерфейса»

сохраняв

при

этом корректность схемы базы данных.

Для

примера снова обратимся

базе

данных

«Библиотека»

создадим

представление,

которое

содержит

список

чи-

тателей-должников

указанием

книг, которые

у них па

руках,

указанных

базе сроков сдачи этих

книг.

Такое

представление

может понадобиться

для ад-

министративного

приложения,

которое

разрабатывается

для

директора

библио-

теки

или его

заместителя,

они

должны

принимать административные меры

для

наказания

нарушителей

возврата

книг

библиотеку.

CREATE

VIEW

DEBTORS

ISBN.TITLE,

NUMJEADER.NAME.ADRES.HOMEJHON,

WORKJHON.DATA_OUT

SELECT

ISBN

.TITLE,

NUMJEADER,

NAME,

ADRES.

HOMEJHON.

WORKJHON.

DATAJUT

FROM

BOOKS.EXEMPIAR.READERS

WHERE

BOOKS.ISBN

EXEMPLAR.ISBN

AND

EXEMPLAR,NUMJEADER

READERS.NUMJEADER

AND

EXEMPLAR.PRESENT

FALSE

AND

EXEMPLAR.DATA

OUT <

GetOateO