Уровни представления данных. Функции СУБД. (Лекция 2)

1. Лекция 2

I . Уровни
представления данных.
Функции СУБД
II.
Изменение и удаление таблиц
Индексы
Типы специальных таблиц
Data Manipulation Language (DML

2. Уровни представления данных

В процессе научных исследований предлагались различные способы реализации
СУБД. Самым жизнеспособным из них оказалась предложенная американским
комитетом по стандартизации ANSI (American National Standards Institute)
трехуровневая система:
Внешний уровень связан с индивидуальными представлениями
пользователей.
Например, пользователь из отдела кадров некоторого предприятия может
рассматривать базу данных как набор записей с информацией об отделах плюс
набор записей с информацией о служащих и ничего не знать о данных с ин формацией о деталях и поставщиках, с которыми работают пользователи в
отделе материально-технического снабжения.
Концептуальный уровень связан с обобщением представлений
пользователей.
Иначе говоря, может быть несколько внешних представлений, каждое из
которых состоит из более или менее абстрактного представления определенной
части базы данных. И может быть только одно концептуальное представление
базы данных целиком. Говорят, что концептуальное представление – это
представление данных такими, какие «есть на самом деле», а не такими, какими
их видит тот или иной пользователь.
Внутренний уровень – это уровень, на котором определяются способы и
структура хранения данных на физических носителях.

3.

Каждому из перечисленных уровней ставится в соответствие модель описания
данных:
внешняя модель – модель данных на внешнем уровне, отражающая
представления пользователя о базе данных (подсхема БД и ее описание).
логическая модель– отражение концептуальной модели в терминах
конкретной СУБД. Содержит полный набор объектов и связей между ними.
внутренняя модель – отражает представления данных на физических
устройствах и методы доступа к ним.

4. Функции СУБД

Непосредственное управление данными во внешней памяти
Эта функция включает обеспечение необходимых структур внешней памяти как
для хранения данных, непосредственно входящих в БД, так и для служебных
целей, н-р, для убыстрения доступа к данным в некоторых случаях (обычно для
этого используются индексы).
Управление буферами оперативной памяти. СУБД обычно работают с БД
значительного размера, по крайней мере, этот размер существенно больше
доступного объема оперативной памяти. Естественно, что если при обращении к
любому элементу данных будет производиться обмен с внешней памятью, то вся
система будет работать со скоростью устройства внешней памяти. Практически
единственным способом реального увеличения этой скорости является
буферизация данных в оперативной памяти (т.е. часть данных следует хранить в
буферах оперативной памяти). Поэтому в развитых СУ БД поддерживается
собственный набор буферов оперативной памяти с собственной дисциплиной
защиты буферов
Управление транзакциями. Транзакция – это последовательность операций
над БД, рассматриваемых СУБД как единое целое. Либо транзакция успешно
выполняется и СУБД фиксирует изменения БД, произведенные этой
транзакцией, либо ни одно из этих изменений не отражается на состоянии БД.
Понятие транзакции необходимо для поддержания логической целостности БД.
Журнализация изменений и восстановление базы данных после сбоев.

5. Функции СУБД

Поддержка языков БД. (язык определения данных, язык манипулирования
данными). Стандартным языком наиболее распространенных в настоящее
время СУБД является язык SQL (Structured Query Language).
Защита баз данных от несанкционированного доступа – обычно
применяют два традиционных способа защиты:
установка пароля на открытие базы данных целиком;
защита на уровне пользователей, когда в зависимости от пароля
пользователя определяется возможность его доступа к различным
объектам базы данных.
Синхронизация работы нескольких пользователей.
Достаточно часто может иметь место ситуация, когда несколько пользователей
одновременно выполняют операцию обновления одних и тех же данных. Такие
коллизии могут привести к нарушению логической целостности данных,
поэтому система должна предусматривать меры, не допускающие обновление
данных другим пользователям, пока работающий с этими данными пользователь
полностью не закончит с ними работать.
Службы поддержки целостности данных. Триггеры, ограничения, которые
не позволяют менять данные, как попало.

6. Лекция часть II (SQL)

7. Оператор ALTER TABLE Statement. Syntax

В концепции реляционных баз заложена «гибкость» структуры, т.е. простота
изменения структуры.
Изменение структуры таблиц осуществляется с помощью оператора Alter Table
Систаксис:
ALTER TABLE table_name
{[
ALTER COLUMN column_name
{DROP DEFAULT | SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
| ADD
{ < column_definition > | < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name | COLUMN column }
]}

8. Пример оператора ALTER TABLE

Пример 1: Допустим в таблице employee был создан столбец employeeName
varchar(50) без указания ограничения not null . В этом случае его можно добавить
с помощью специального оператора:
USE “db_name”
ALTER TABLE Employees
ALTER COLUMN EmployeeName varchar(50) NOT NULL
Пример 2: Необходимо ввести новый столбец ChildrenNumber, определяющий
количество детей.
Add new to Employees table
ALTER TABLE Employees
ADD ChildrenNumber tinyint DEFAULT 0
Пример 3: Удалить столбец ChildrenNumber из таблицы Employees
ALTER TABLE Employees
DROP COLUMN ChildrenNumber

9. Пример оператора ALTER TABLE

Пример 4: Необходимо добавить новое табличное ограничение в таблицу
Employees
ALTER TABLE Employees
ADD CONSTRAINT CK_Birthdate CHECK (YEAR(GETDATE()) YEAR(Birthdate) >= 18 )
Пример 5: Необходимо удалить табличное ограничение таблицы
Employees
ALTER TABLE Employees
DROP CONSTRAINT CK_Birthdate

10. Оператор DROP TABLE

Удаление таблицы из базы данных осуществляется с помощью оператора
Drop Table (оператор Drop может быть применен также и к индексам,
представлениям и схемам)
Синтаксис:
DROP TABLE < table_name >
Пример : Удалить таблицу Countries
DROP TABLE Countries

11. Индексы

Индекс(англ. index) –объект базы данных, связанный с таблицей или
представлением, создаваемый с целью повышения производительности поиска
данных.
При отсутствии индексов сканирование записей таблицы осуществляется
последовательно в порядке их физического размещения. Наличие индексов
предполагает, что анализ записей производится в соответствие с возрастанием
/убыванием значений полей, из которых сформирован индекс таблицы.
Пользователи могут создать индексы, состоящие из любого числа полей таблицы
в различных их сочетаниях.
Типы индексов в SQL Server:
Существует два типа индексов: кластерные и некластерные. При наличии
кластерного индекса строки таблицы упорядочены по значению ключа этого
индекса. Если в таблице нет кластерного индекса, таблица называется кучей.
Некластерный индекс, созданный для такой таблицы, содержит только указатели
на записи таблицы. Кластерный индекс может быть только одним для каждой
таблицы, но каждая таблица может иметь несколько различных некластерных
индексов, каждый из которых определяет свой собственный порядок следования
записей.

12. Структура Индекса

В большинстве СУБД поиск записей с нужным значением индекса реализован с
помощью B-tree алгоритмов, которые минимизируют количество обращений к диску
при поиске записей. B-tree алгоритмы основаны на том факте, что каждый узел (node)
дерева имеет только заданное количество ветвей (branch).
Самая простая версия B-tree алгоритма
- это бинарное дерево (иначе такой
алгоритм называют алгоритмом
дихотомии), т.е. для поиска записи по
заданному условию обращаются к
записи находящейся в середине
списка. Если значение условия в
найденной записи равно нужному, то
выбираются все рядом находящиеся
записи с тем же условием . Если
значение условия в найденной записи
меньше (больше) нужного, то вторая
(новая половина) записей делится
пополам и т.д.
Индексы могут быть созданы как для одного атрибута (простые индексы), так и для
совокупности атрибутов(составные индексы).
К индексам может быть предъявлено требование уникальности, что означает
невозможность существования нескольких записей с одинаковыми значениями индекса.

13. Кластерные индексы ( Clustered Indexes)

Кластерные индексы – принципиальным отличие кластерного индекса
является то, что при его определении физическое расположение записей
таблицы изменяется в соответствии со структурой индекса, т.е. записи в таблице
упорядочиваются в соответствии со значением индекса. При некластерном
индексе каждая новая запись добавляется в конец таблицы, а при кластерной
записи перемещаются таким образом, что новая помещается в то место, которое
определяется по значению соответствующего ей индекса. Кластерные индексы
дают существенное увеличение производительности, особенно при работе с
совокупностью последовательных записей (идущих одна за другой).
Необходимо избегать создания кластерного индекса для часто изменяемых
столбцов (атрибутов), поскольку сервер должен будет выполнять физическое
перемещение записей таблицы, чтобы они находились в упорядоченном
состоянии, соответствующем кластерному индексу.
При создании Primary Key сервер автоматически создает для него кластерный
индекс.

14. Некластерные индексы (Nonclustered Indexes)

Некластерные индексы – наиболее типичный вид индексов. Они не
перестраивают физическую структуру таблицы, а лишь организуют ссылки,
определяющие по значению индекса адреса соответствующих записей.
Если в таблице в дополнение к
кластерному индексу создан еще и
некластерный индекс, то указатель
в некластерном индексе ссылается
не на физическое положение
строки, а на соответствующий
элемент кластерного индекса,
описывающего эту строку, что
позволяет не перестраивать
структуру некластерных индексов
каждый раз, когда кластерный
индекс меняет физический порядок
строк в таблице.
SQL Server 2005 поддерживает до
249 некластеризованных индексов
и SQL Server 2008 поддерживает до
999.

15. Типы индексов

В свою очередь кластерные и некластерные индексы могут быть следующих
типов:
Композитный индекс: индекс, который содержит более одной колонки. В
обоих SQL Server 2005 и 2008, можно включать до 16 столбцов в индексе, пока
индекс не превышает 900-байтового предела. Оба кластерные и некластерные
индексы могут быть составными индексами.
Уникальный индекс: индекс, который гарантирует уникальность каждого
значения в индексированном столбце. Если индекс является составным,
уникальность обеспечивается через все столбцы в целом, а не на отдельные
столбцы. Уникальный индекс создается автоматически при определении
первичного ключа или ограничения уникальности.
Индекс покрытия : Это некластерный индекс, который содержит все
столбцы, необходимые для выполнения запроса. Он устраняет необходимость
сканирования всей таблицы с помощью операторов сканирования таблицы или
кластерного индекса. По сути индекс представляет собой уменьшенную копию
таблицы, где содержится подмножество ее столбцов. В индекс включаются
только те столбцы, которые необходимы для ответа на запрос или запросы, то
есть только то, что "покрывает" запрос.

16. Создание и удаление индекса

Индекс создается командой CREATE INDEX
Синтаксис:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
Примечание: Имя индекса должно быть уникальным в пределах таблицы,
а сам индекс создается только для одной таблицы или представления.
Удаление индекса выполняется командой DROP INDEX.
Синтаксис:
DROP INDEX <table_name>.<index_name>
Примечание: Одной командой можно удалить несколько индексов(через
запятую).

17. Общие рекомендации для создания и использования индексов

Обычно не имеет смысла создавать индексы для таблиц, содержащих
мало записей. Скажем, если таблица содержит не более 50 записей,
возможно быстрее будет сканировать ее, чем применить B-tree алгоритм.
Для таблиц с большим количеством записей индексы следует создавать
тогда, когда запросы, построенные на включенных в индекс столбцах
возвращают небольшой процент (обычно =15%) всех записей.
Индексы замедляют операции DML. Например, если обновляются
значения столбцов, включенных в состав индекса, соответствующая
индексу структура также должна быть обновлена.

18. Типы специальных таблиц

Помимо стандартных пользовательских таблиц, SQL Server 2005/2008 предоставляет
следующие типы таблиц, которые служат для специальных целей в базе данных:
Временные таблицы Существует два вида временных таблиц: локальные и
глобальные.
Локальные временные таблицы (есть # в качестве первого символа их имена) видны
только их создателям до завершения сеанса соединения с экземпляром SQL Server, как
только они впервые созданы или когда на них появляется ссылка. Локальные
временные таблицы удаляются после отключения пользователя от экземпляра SQL
Server.
Глобальные временные таблицы (есть # #, как первые буквы их имен) видны всем
пользователям в течение любых сеансов соединения после создания этих таблиц и
удаляются, когда все пользователи, ссылающиеся на эти таблицы, отключаются от
экземпляра SQL Server.
Системные таблицы: SQL Server хранит данные, определяющие конфигурацию сервера
и всех его таблиц в специальном наборе таблиц, называющихся системными.
Пользователи не могут напрямую обращаться к системным таблицам или обновлять их,
кроме случаев использования выделенного административного соединения (DAC).
Обычно системные таблицы меняются с каждой новой версией SQL Server.
Секционированные таблицы — это таблицы, данные которые горизонтально разделены
на блоки, которые могут быть распределены между несколькими файловыми группами в
базе данных. Секционирование делает большие таблицы и индексы более управляемыми,
позволяет быстро и эффективно получать доступ к наборам данных и управлять ими, при
этом сохраняя целостность всей коллекции.

19. Операторы DML

INSERT: заполнение таблиц с данными;
UPDATE: изменение табличных данных;
DELETE: удаление данных из таблицы;
TRUNCATE: удаление всех строк из таблицы

20. Оператор INSERT INTO

Ввод новых записей в таблицу осуществляется оператором INSERT INTO
Синтаксис:
INSERT INTO table_name
[ (column_name1, column_name2, …) ]
VALUES (value_1, value_2, …)
Пример:
INSERT INTO Countries (CountryID, CountryName) VALUES (3, ‘USA’)
INSERT INTO Countries VALUES (4, ‘Kyrgyzstan’)

21. Оператор UPDATE

Изменение существующих данных – осуществляется с помощью
инструкции UpDate, имеющей следующий синтаксис
Синтаксис:
UPDATE table_name
SET column_name1 = value_1
[, column_name2 = value_2]
[, column_name_n = value_n]
[ WHERE some_condition]
Пример:
UPDATE Cities SET Population = Population * 1.25 WHERE CountryId = 3

22. Оператор DELETE

Удаление данных из таблицы осуществляется командой Delete, которая
удаляет записи, удовлетворяющие заданному условию
Синтаксис:
DELETE FROM table_name [ WHERE some_condition ]
Пример:
DELETE FROM Cities WHERE CountryId = 4 AND Population < 100
DELETE FROM Employees

23. Оператор TRUNCATE TABLE

Инструкция TRUNCATE TABLE по функциональности равнозначна
инструкции DELETE без предложения WHERE, однако TRUNCATE
TABLE выполняется быстрее и требует меньших ресурсов системы и
журналов транзакций.
Синтаксис:
TRUNCATE TABLE table_name
Пример :
TRUNCATE TABLE Employees