Технологии бд теоретические основы организации бд. Реляционная модель данны



страница10/20
Дата23.04.2016
Размер2.56 Mb.
1   ...   6   7   8   9   10   11   12   13   ...   20

3.2.Группы операторов SQL


Язык SQL определяет:

  • операторы языка, называемые иногда командами языка SQL;

  • типы данных;

  • набор встроенных функций.

По своему логическому назначению операторы языка SQL часто разбиваются на следующие группы:

  • язык определения данных DDL (Data Definition Language);

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

Язык определения данных включает операторы, управляющие объектами базы данных. К последним относятся таблицы, индексы, представления. Для каждой конкретной базы данных существует свой набор объектов базы данных, который может значительно расширять набор объектов, предусмотренный стандартом. В некоторых СУБД, таких как Oracle, все объекты базы данных, принадлежащие одному пользователю, образуют схему базы данных. С другой стороны, в стандарте SQL92 термином "схема" стали называть группу взаимосвязанных таблиц.

Язык манипулирования данными включает операторы, управляющие содержанием таблиц базы данных и извлекающими информацию из этих таблиц.

Язык DML определяет следующие операторы:



  • SELECT - извлечение данных из одной или нескольких таблиц;

  • INSERT - добавление строк в таблицу;

  • DELETE - удаление строк из таблицы;

  • UPDATE - изменение значений полей в таблице.

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

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

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

3.3.Средства определения схемы БД


SDL (Schema Definition Language)

  • CREATE SCHEMA - создать схему базы данных

  • DROP SHEMA - удалить схему базы данных

  • CREATE TABLE - создать таблицу

  • ALTER TABLE - изменить таблицу

  • DROP TABLE - удалить таблицу

  • CREATE DOMAIN - создать домен

  • ALTER DOMAIN - изменить домен

  • DROP DOMAIN - удалить домен

  • CREATE VIEW - создать представление

  • DROP VIEW - удалить представление

3.3.1.Описание примера и используемого для учебных целей сервера БД


На Рис. 71 приведена модель БД, которая далее будет использоваться в примерах для данного раздела.

Рис. 71. IDEF1X-диаграмма базы данных примера

В дальнейшем, все примеры SQL-операторов будут приводиться для сервера БД Borland InterBase.

InterBase представляет собой очень мощную СУБД. С самого начала InterBase создавался на основе современной и надежной архитектуры. Первоначальный автор, Джим Старки (Jim Starkey), спроектировал архитектуру для обработки параллельности и транзакций, не накладывая физические блокировки на части таблиц, что все еще недоступно многим известным серверам баз данных и сегодня. Архитектура InterBase носит название Multi-Generational Architecture (MGA, архитектура нескольких поколений), она обрабатывает параллельный доступ нескольких пользователей к одним и тем же данным, позволяя им изменять записи, не затрагивая ту часть данных, которая видна в базе данных пользователям, имеющим параллельный доступ.

Этот подход основан на уровне изоляции транзакций Repeatable Read (повторяющееся чтение), который позволяет пользователю в рамках транзакции видеть одни и те же данные, независимо от изменений, сделанных и подтвержденных остальными пользователями. Технически сервер реализует это, поддерживая различные версии каждой записи, к которой было обращение, для всех открытых транзакций.

Хотя такой подход (также носящий название «создание версий», versioning) может потребовать больших затрат памяти, при этом практически не накладываются физические блокировки на таблицы и система в целом становится значительно более надежной в случае сбоя.

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

InterBase был выбран несколькими весьма значительными организациями, например, фирмой Ericsson, министерством обороны США, он также нашел применение на фондовых биржах и в банковских системах удаленного доступа.

Помимо архитектуры нескольких поколений, сердца InterBase, сервер обладает и другими техническими преимуществами:


  • нетребовательность к ресурсам (в минимальной установке для InterBase требуется менее 10 Мбайт дискового пространства, требования к памяти также невелики)

  • хорошая производительность для большого количества данных

  • реализация на многих платформах (среди которых 32 разрядные версии Windows, Solaris и Linux) с поддержкой полной совместимости, что позволяет без особых изменений масштабировать сервер от небольших до очень объемных систем

  • прекрасный послужной список – InterBase находится в эксплуатации более 15 лет, практически не вызывая проблем

  • внутренний язык сервера очень близок к стандарту SQL

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

  • простая установка и управление с небольшими затратами на администрирование

3.3.2.Создание БД


CREATE { DATABASE | SHEMA } “<имя_файла>”

{ USER “имя_пользователя” [PASSWORD “пароль”] ]

[ PAGE_SIZE [=] целое ]

[ LENGTH [=] целое [PAGE [S] ] ]

[ DEFAULT CHARASTER SET набор_символов ]

[ <вторичный_файл> ];

<вторичный файл> = FILE “<имя_файла>” [<файлов_информ>] [<вторичный файл>]

<файлов_информ> = LENGTH [ = ] целое [ PAGE [S] ] | STARTING

[AT [PAGE]] целое <файлов_информ>

----------------------------------------------------------

<имя_файла>” – спецификация файла, в котором будет храниться создаваемая БД.

USER “имя_пользователя” – имя пользователя

PASSWORD “пароль” – пароль пользователя

PAGE_SIZE [=] целое – размер страницы БД в байтах

DEFAULT CHARASTER SET набор_символов – определяет используемый в БД набор символов

FILE “<имя_файла>” – имя одного или нескольких файлов, в которых располагается БД

STARTING [AT [PAGE]] – если БД занимает несколько файлов, предложение определяет с какой страницы располагается БД в указанном файле

LENGTH [ = ] целое [ PAGE [S] ] – длина файла в страницах. По умолчанию 50. Минимум 50.
Пример:

CREATE DATABASE “D:\BD\SKLAD.GDB”

FILE “D:\BD\SKLAD.GD1” STARTING AT PAGE 1001

LENGTH 500

FILE “D:\BD\SKLAD.GD\2”

Здесь определяется БД D:\BD\SKLAD.GDB, состоящая из 3 файлов: первичного длиной 1000 страниц, второго длиной 500 страниц и третьего неопределенной длины.


3.3.3.Типы данных и домены


Типы данных:

  • Символьные (CHAR, VARCHAR)

  • Целочисленные (INTEGER, SMALLINT)

  • Вещественные (FLOAT, DOUBLE PRECISION)

  • Фиксированно-десятичные значения (DECIMAL, NUMERIC)

  • Значения типа даты (DATE)

  • Двоичные (BLOB)


Понятие домена:

CREATE DOMAIN домен [AS] <тип данных>

[DEFAULT {литерал} | NULL | USER]

[NOT NULL] [CHECK (<Ограничение домена>) ]
Пример:

CREATE DOMAIN ID_TYPE AS INTEGER

CHECK (VALUE >= 100);
< Ограничение домена >= {

VALUE <оператор> <значение>

| VALUE [NOT] BETWEEN <значение1> AND <значение2>

| VALUE [NOT] LIKE <значение1> [ESCAPE <значение2>]

| VALUE [NOT] IN <значение1> [, <значение2> …])

| VALUE IS [NOT] NULL

| VALUE [NOT] CONTAINING <значение>

| < Ограничение домена >

| NOT < Ограничение домена >

| < Ограничение домена > OR | < Ограничение домена >

| < Ограничение домена > AND < Ограничение домена >

};

где


<оператор> = { = | < | > | <= | >= | != | !< | !> | <> }

3.3.4.Общий формат оператора создания таблиц


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

( <опр_столбца>

[, <опр_столбца> | <ограничение> …]);

<опр_столбца> - определение столбца таблицы.

<опр_столбца> =столбец { тип_данных | COMPUTED [ BY ]

(<выражение>) | домен }

[ DEFAULT {литерал | NULL | USER } ]

[NOT NULL] [<огранич_столбца>]
Пример оператора создания таблиц:

CREATE TABLE SAL_HIST

QUORTER INTEGER NOT NULL,

LAST_YEAR INTEGER,

THIS_YEAR INTEGER,

DELTA COMPUTED_BY (THIS_YEAR - LAST_YEAR),

PRIMARY KEY (QUORTER) );


QUORTER

LAST_YEAR

THIS_YEAR

DELTA

1

100

129

29

2

200

280

-20

3

300

350

0

4

400

403

3

3.3.5.Ограничения целостности


Ограничения целостности бывают двух видов:

  • Накладываемые на отдельный столбец;

  • Накладываемые на всю таблицу.


При наложении на отдельный столбец :

TOVAR VARCHAR(20) NOT NULL PRIMARY KEY, …
При наложении ограничений на таблицу :

CREATE TABLE … (

TOVAR VARCHAR(20) NOT NULL



PRIMARY KEY (TOVAR)

);

3.3.6.Первичные и уникальные (альтернативные) ключи


На уровне столбцов:

CREATE TABLE VLADLIM (

KODVLAD INTEGER NOT NULL PRIMARY KEY,

NAZVVLAD VARCHAR(50) NOT NULL UNIQUE

);

На уровне таблицы:

CREATE TABLE VLADLIM (

KODVLAD INTEGER NOT NULL,

NAZVVLAD VARCHAR(50) NOT NULL,

PRIMARY KEY ( KODVLAD),

UNIQUE ()NAZVVLAD)

);

3.3.7.Внешний ключ и определение ссылочной целостности


Внешний ключ строится в дочерней таблице.

Описание формата:

[CONSTRAINT <имя ссылочной целостности>]

FOREIGN KEY ( <список столбцов внешнего ключа>)

REFERENCES <имя родительской таблицы>

[ <список столбцов родительской таблицы > ]

[ON DELETE { NO ACTION | CACADE | SET DEFAULT | SET NULL}]

[ON UPDATE { NO ACTION | CACADE | SET DEFAULT | SET NULL}]
Пример:

CREATE TABLE P (

PK_FIELD INTEGER NOT NULL,

OTHER FIELD INTEGER,

PRIMARY KEY (PK_FIELD)

);

CREATE TABLE F (

PK_FIELD1 INTEGER NOT NULL,

PK_FIELD2 INTEGER NOT NULL,

SOME_FIELD INTEGER,

PRIMARY KEY (PK_FIELD1,PK_FIELD2),

FOREIGN KEY (PK_FIELD1) REFERENCES P

ON UPDATE CASCADE

);

3.3.8.Требования к значениям столбцов


Примеры фрагментов ограничений:

CREATE TABLE PERSON _PARAMS (

ID_INTEGER NOT NULL PRIMARY KEY,

HEIGHT INTEGER NOT NULL,

WIEGHT INTEGER NOT NULL CHECK (HEIGHT > WIEGHT)

);

---------------------------------------------------------------------------------

CREATE TABLE RASHOD (



CONSTRAINT PO_DATE_RASH



CHECK (EXISTS (SELECT TOVAR FROM PRIHOD

WHERE PROHOD.TOVAR=RASHOD.TOVAR))

);
Столбец должен содержать сочетание символов USD

… CHECK (STOLBEZ CONTAINING “USD”)

Столбец должен начинаться с сочетания символов USD

… CHECK (STOLBEZ STARTING WITH “USD”)

3.3.9.Изменение объявлений таблицы


Оператор ALTER TABLE позволяет:

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

ALTER TABLE <имя таблицы> ADD <определение столбца>;

Удалить столбец из таблицы

ALTER TABLE <имя таблицы> DROP <имя столбца1> [,<имя столбца2>…

Удалить атрибуты целостности таблицы или отдельного столбца

ALTER TABLE <имя таблицы> DROP <имя ограничения целостности>

Добавить новые ограничения целостности

ALTER TABLE <имя таблицы> ADD [CONSTRAINT <имя ограничения>] <определение целостности>;

3.3.10.Удаление таблицы


Удаление таблицы целиком:

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

3.3.11.Работа с индексами

Логическое разделение на ключи индексы:

Логический уровень

  • Первичный ключ выполняет функцию однозначной идентификации записи в таблицы. Первичный и внешний ключи строятся для обеспечения ссылочной целостности реляцинно-связанных таблиц.

  • Индексы служат для сортировок и оптимизации доступа к данным

Физический уровень

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



Необходимость создания индексов:

Индексы необходимо создавать в случае, когда по столбцу или группе столбцов:

  • Часто производится поиск в БД;

  • Часто строятся объединения таблиц;

  • Часто производится сортировка;

  • Часто производится сортировка;

Не рекомендуется строить индексы по столбцам или группам столбцов, которые:

  • Редко используются для поиска, объединения , сортировки результатов запроса

  • Часто меняют значение, что приводит к необходимости часто обновлять индекс и способно существенно замедлить скорость работы с БД;

  • Содержит небольшое число вариантов значения


Пример:

CREATE TABLE SOTR (

ID_SOTR INTEGER NOT NULL,

OTDEL VARCHAR (10),

DOLGNOST CHAR (10),

FIO VARCHAR(25),

PRIMARY KEY (ID_SOTR));

CREATE INDEX DLJ ON SOTR (DOLGNOST);
Именоваться индексы будут так :

DLJ INDEX ON SOTR (DOLGNOST)

RDB$PRIMARY18 UNIQUE INDEX ON SOTR (ID_SOTR)
После многократного внесения изменений в таблицу БД индексы этой таблицы могут быть разбалансированы. Разбалансировка приводит к тому, что глубина индекса возрастает сверх критического значения.

Для улучшения показателя индекса необходимо выполнить его перестроение:



ALTER INDEX <имя индекса> DEACTIVATE;

ALTER INDEX <имя индекса> ACTIVATE;

Замечания:

  • Нельзя перестроить индекс, если он используется в данный момент в запросах

  • Нельзя перестроить индекс, созданный в результате создания первичного ключа, внешнего и уникального ключей.



1   ...   6   7   8   9   10   11   12   13   ...   20


База данных защищена авторским правом ©bezogr.ru 2016
обратиться к администрации

    Главная страница