Сетевая библиотекаСетевая библиотека

Лекции БД

Дата публикации: 28.05.2018
Тип: Текстовые документы DOC
Размер: 1.57 Мбайт
Идентификатор документа: -164919962_466399869
Файлы этого типа можно открыть с помощью программы:
Microsoft Word из пакета Microsoft Office
Для скачивания файла Вам необходимо подтвердить, что Вы не робот


Не то что нужно?


Вернуться к поиску
Оглавление HYPER13 HYPERLINK \l "_Toc369441314" HYPER141.ВведениеHYPER13 PAGEREF _Toc369441314 \h HYPER142HYPER15 1.1.Для чего нужны базы данныхHYPER13 PAGEREF _Toc369441315 \h HYPER142HYPER15 1.2.Основные определенияHYPER13 PAGEREF _Toc369441316 \h HYPER142HYPER15 2.Классификация БДHYPER13 PAGEREF _Toc369441317 \h HYPER143HYPER15 3.Реляционные БДHYPER13 PAGEREF _Toc369441318 \h HYPER146HYPER15 3.1.Структура таблиц и типы данныхHYPER13 PAGEREF _Toc369441319 \h HYPER146HYPER15 3.2.КлючиHYPER13 PAGEREF _Toc369441320 \h HYPER146HYPER15 3.3.СвязиHYPER13 PAGEREF _Toc369441321 \h HYPER147HYPER15 3.4.Целостность данныхHYPER13 PAGEREF _Toc369441322 \h HYPER147HYPER15 3.5.Поддержание ссылочной целостностиHYPER13 PAGEREF _Toc369441323 \h HYPER148HYPER15 3.6.Правила КоддаHYPER13 PAGEREF _Toc369441324 \h HYPER148HYPER15 4.SQL – язык структурированных запросовHYPER13 PAGEREF _Toc369441325 \h HYPER1411HYPER15 4.1.Создание таблицHYPER13 PAGEREF _Toc369441326 \h HYPER1412HYPER15 4.2.Выборка данныхHYPER13 PAGEREF _Toc369441327 \h HYPER1415HYPER15 4.3.Объединение таблицHYPER13 PAGEREF _Toc369441328 \h HYPER1419HYPER15 4.4.Добавление данныхHYPER13 PAGEREF _Toc369441329 \h HYPER1421HYPER15 4.5.Удаление данныхHYPER13 PAGEREF _Toc369441330 \h HYPER1422HYPER15 4.6.Изменение данныхHYPER13 PAGEREF _Toc369441331 \h HYPER1423HYPER15 5.Проектирование баз данныхHYPER13 PAGEREF _Toc369441332 \h HYPER1424HYPER15 5.1.Концептуальное проектирование и построение ER-моделиHYPER13 PAGEREF _Toc369441333 \h HYPER1424HYPER15 5.2.Логическое проектированиеHYPER13 PAGEREF _Toc369441334 \h HYPER1425HYPER15 5.3.Физическое проектированиеHYPER13 PAGEREF _Toc369441335 \h HYPER1425HYPER15 5.4.Нормализация базы данныхHYPER13 PAGEREF _Toc369441336 \h HYPER1426HYPER15 6.ИндексыHYPER13 PAGEREF _Toc369441337 \h HYPER1429HYPER15 6.1.Общие сведенияHYPER13 PAGEREF _Toc369441338 \h HYPER1429HYPER15 6.2.Кластерные индексыHYPER13 PAGEREF _Toc369441339 \h HYPER1429HYPER15 6.3.Некластерные индексыHYPER13 PAGEREF _Toc369441340 \h HYPER1431HYPER15 6.4.Создание индексаHYPER13 PAGEREF _Toc369441341 \h HYPER1432HYPER15 7.Многопользовательский доступ к даннымHYPER13 PAGEREF _Toc369441342 \h HYPER1433HYPER15 7.1.Технология клиент-серверHYPER13 PAGEREF _Toc369441343 \h HYPER1433HYPER15 7.2.Настройка IISHYPER13 PAGEREF _Toc369441344 \h HYPER1434HYPER15 8.ТранзакцииHYPER13 PAGEREF _Toc369441345 \h HYPER1435HYPER15 8.1.Проблемы параллельного доступа.HYPER13 PAGEREF _Toc369441346 \h HYPER1436HYPER15 8.2.Блокировки и уровни изоляцииHYPER13 PAGEREF _Toc369441347 \h HYPER1438HYPER15 8.3.Грануляция блокировок (уровни блокирования)HYPER13 PAGEREF _Toc369441348 \h HYPER1438HYPER15 9.ПриложенияHYPER13 PAGEREF _Toc369441349 \h HYPER1439HYPER15 HYPER15 Введение Для чего нужны базы данных БД не просто набор данных, а программа, которая должна обеспечивать ваши нужды. Вы – большая компания. База данных должна: хранить данные; обеспечивать доступ к данным (возможно по сети); выполнять поиск данных; делать выборки данных; выводить отчеты; создавать резервные копии и восстанавливать данные; разграничивать права пользователей; обеспечивать многопользовательский доступ; защищать данные; обладать пользовательским интерфейсом и интерфейсом администратора. Основные определения БД (база данных) – Совокупность специальным образом организованных данных, хранимых в памяти вычислительной системы и отображающих состояние объектов и их взаимосвязей в некоторой предметной области. Пример использования БД: для работы филиалов банка в разных городах необходимо иметь единую базу всех клиентов банка. Еще пример: для управления производственным процессом отдельного участка необходимы сведения о наличии исходных комплектующих и мощностях участка. СУБД (система управления базами данных) – специальный пакет программ, посредством которого реализуется централизованное управление базой данных и обеспечивается доступ к данным. СУБД обеспечивает выполнение запросов пользователей – просмотр, редактирование, добавление и удаление данных: разграничение прав доступа к информации, резервное копирование и восстановление БД и другие возможности. СУБД не зависит от рабочих программ и операционных систем, с которыми она взаимодействует. Примеры СУБД – MS Access, MS SQL Server, Oracle. БнД (банк данных) – автоматизированная информационно-справочная система централизованного хранения и коллективного использования данных. В состав банка данных входят одна или несколько баз данных, СУБД, а также библиотеки запросов и прикладных программ. Классификация БД Иерархические. Структура БД представляет собой многоуровневое иерархическое дерево, данные связываются по принципу главный – подчиненный (предок – потомок). Каждый объект-предок может иметь любое количество потомков или не иметь их вообще, однако у каждого потомка может быть только один предок. Пример иерархической структуры – файловая система. Иерархические БД появились раньше всех других, сейчас используются крайне редко, имеют скорее историческое значение. Пример ИБД - Information Management System (IBM, 1968 г.). Иерархические базы данных наиболее пригодны для моделирования структур, по своей природе являющихся иерархическими. Для всех других БД неоправданно усложняется, становится медленной и громоздкой. Например, если у потомка существует несколько предков, приходится строить несколько деревьев. Сетевые. Являются расширением иерархических БД, каждый потомок может иметь несколько предков. Структуру можно изобразить в виде графа. Объекты представляются как вершины, или узлы графа, а связи — как дуги, или рёбра. Пример – СООБЗ Cerebrum, СУБД IDMS (Integrated Database Management System), разработанная компанией Cullinet Software, Inc. Сетевой базой данных фактически являетсяВсемирная паутинаглобальной компьютерной сети Интернет. Гиперссылки связывают между собой сотни миллионов документов в единую распределенную сетевую базу данных. Реляционные. В основе лежит понятие отношения. Визуально отношение можно представить в виде таблицы. ФИО Год рождения Год поступления Курс Группа … Реляционная БД – это совокупность связанных друг с другом двумерных таблиц. Постреляционные. Созданы на основе реляционной модели, позволяют хранить в столбце таблицы множественные данные, группы значений (нарушается правило атомарности). Такие БД называют еще многомерными. Используются трехмерные структуры, можно хранить в полях таблицы другие таблицы. Предыдущий пример можно записать в виде постреляционной БД, поле группа будет включать в себя еще одну таблицу, информацию о группе. Пример – СУБД Cache Объектно-реляционные. Реляционные БД, поддерживающие некоторые технологии, реализующиеобъектно-ориентированный подход. Для каждого объекта описываются его свойства (состояние) и методы (поведение). Множество объектов с одним и тем же набором атрибутов и методов образует класс объектов. Поддерживаются понятия инкапсуляции, наследования и полиморфизма. В ООП: Инкапсуляция означает объединение в единое целое данных и алгоритмов (функций и методов) их обработки, а также скрытие данных внутри объектов, что повышает надежность разрабатываемого программного обеспечения. Доступ к объекту может осуществляться только через его интерфейс (свойства и методы). Закрытые коды или данные доступны только для других частей этого объекта. Если коды и данные являются открытыми, то, несмотря на то, что они заданы внутри объекта, они доступны и для других частей программы. Вся информация об объекте заключена в определении его класса. Поведение объекта полностью определяется принадлежностью к конкретному классу. Если в объект типа Каталог добавить свойство, задающее телефон автора книги и имеющее название телефон, то мы получим одноименные свойства у объектов Абонент и Каталог . Смысл такого свойства будет определяться тем объектом, в который оно инкапсулировано. Наследование – позволяет описать новый класс на основе уже существующего (родительского), при этом свойства и методы родительского класса заимствуются новым классом. Однако в дочерних классах могут определяться новые свойства и методы. Полиморфизм допускает в объектах разных типов иметь методы (процедуры и функции) с одинаковыми именами, что означает способность одного и того же программного кода работать с разнотипными данными. Например для языка Си нахождение модуля числа требует трёх различных функций: abs(), labs() и fabs(). (целые, длинных целые, числа с плавающей точкой). В С++ каждая из этих функций может быть названа abs(). Тип данных, который используется при вызове функции, определяет, какая конкретная версия функции действительно выполняется. Все СУБД можно разделить на персональные и многопользовательские. Персональные СУБД используются для создания локальных БД, работающих на одном компьютере, как правило, они предназначены для одного пользователя. К таким СУБД относятся Paradox, dBase, Access и др. Многопользовательские СУБД позволяют создавать информационные системы, с которыми могут работать одновременно несколько пользователей. К многопользовательским СУБД относятся Oracle, InterBase, SyBase, Microsoft SQL Server и др. Реляционные БД Структура таблиц и типы данных Реляционная БД представляет собой набор таблиц. Каждая таблица, как правило, описывает отдельный объект (сущность). Таблица состоит из строк (записей) и столбцов. Строки таблицы имеют одинаковую структуру и состоят из полей. Поля описывают отдельные данные экземпляра (например, ФИО, год рождения, номер телефона и т.п.). В каждом столбце таблицы содержатся данные определенного типа. Основные типы данных – это числа (целые или дробные), строковые данные, дата и время. Типы данных, использующиеся в СУБД MS SQL Server, указаны в Приложении 1. Домен – множество данных одного типа, описывающих некоторое свойство. Домен имеет уникальное имя, определяется на некотором простом типе данных, может иметь логическое условие, позволяющее описать подмножество данных домена. Например, домен ФИО определен на типе данных строка символов, но должен содержать только строки – конкретные имена. В литературе используются термины тип поля (домен) и экземпляр поля (значение типа поля для одной конкретной записи). На практике квалификаторы тип и экземпляр часто опускают, полагая, что точный смысл ясен из контекста. Ключи Существуют следующие основные типы ключей. Первичный – набор полей, однозначно определяющий запись. Такой ключ в таблице может быть только один, его значения не повторяются. Альтернативный – возможный ключ, не являющийся первичным. На практике в качестве первичного обычно выбирается тот ключ, который имеет меньший размер (на диске) или состоит из меньшего числа полей. Внешний – набор полей, ссылающихся на первичный или альтернативный ключ другой таблицы. Ключи также делятся на простые (состоят из одного атрибута) и составные (состоят из нескольких атрибутов). Ключ может быть уникальным (если его значения не повторяются) и неуникальным (если значения ключа повторяются). Неуникальным часто бывает внешний ключ. Перечисленные виды ключей могут быть объединены: ключ может быть первичным, составным и уникальным. Связи При создании схемы данных можно определить связи между таблицами. Связь 1:1 – одной записи одной таблицы соответствует одна запись другой таблицы (и только одна). Так обычно связываются две таблицы по уникальному ключу. Например, сотрудники – паспорта. Связь 1:М – одной записи одной таблицы соответствует несколько записей другой. Например, связь первичный ключ – внешний ключ. В одном городе может жить несколько сотрудников. Связь М:М – нескольким записям одной таблицы соответствует несколько записей другой. Для реализации такой связи используют дополнительные таблицы. Например, студенты-предметы-отметки. В связующей таблице внешние ключи обычно идентифицируют запись, и также есть одно или несколько дополнительных полей, где хранятся данные. Целостность данных Целостность данных – это требование соответствия значений данных некоторому набору правил. В реляционных БД рассматриваются несколько уровней ограничения целостности: Целостность таблицы – каждая запись в таблице должна иметь уникальный идентификатор (не NULL). Нельзя хранить в одной и той же таблице несколько записей с одинаковыми идентификаторами – это может привести к противоречивости данных. Целостность домена – каждый атрибут принимает только допустимые значения. При выполнении условий целостности обеспечивается отсутствие значений, выходящих за рамки возможного. Для реализации целостности достаточно правильно выбрать для столбца тип и длину данных. Пример, длина графы семейное положение не может быть больше 10 символов. целостность группы связанных логически таблиц (для Access вся БД). Если таблицы в БД связаны друг с другом, то значения, по которым они связаны, должны не противоречить друг другу. Ссылочная целостность – внешний ключ не может быть указателем на несуществующий родительский. Например, если в городе переименовали улицу, то адреса у всех проживающих на этой улице также должны измениться. А если дом на улице подлежит сносу, то предварительно необходимо отселить всех проживающих в этом доме. Если не соблюдается ограничение целостности на уровне домена, то в БД будут появляться записи без постоянного места жительства. Поддержание ссылочной целостности Рассмотрим связь между таблицами Groups и Students. Все значения столбца Students.IDGroup должны присутствовать в столбце Groups.IDGroup, т.е. первый столбец ссылается на второй. Тогда Students.IDGroup называют внешним ключом, а Groups.IDGroup – родительским. Каждое значение (каждая строка) внешнего ключа должно ссылаться на одно и только одно значение (строку) родительского ключа. Если это условие выполняется, система находится в состоянии ссылочной целостности. Внешний ключ может состоять из нескольких столбцов. Совместимость предполагает, что: Количество столбцов внешнего и родительского ключей должно быть одинаковым. Первый, второй и т.д. столбцы внешнего ключа должны иметь те же типы данных и размеры, что и первый, второй и т.д. столбцы родительского ключа. При этом имена столбцов не обязаны совпадать. Целостность может быть нарушена при изменении и удалении строк в родительской таблице. В SQL Server существует четыре варианта поведения связанных таблиц. CASCADE. Внешний ключ будет приведен в соответствие родительскому ключу. При изменении р.к. внешний ключ также изменится, при удалении – будет удалены все содержащие ключ записи. SET NULL. Значения внешнего ключа будут установлены в NULL. SET DEFAULT. Для внешнего ключа будет установлено значение по умолчанию, а при отсутствии такового – NULL. NO ACTION. Применяется по умолчанию. Внешний ключ не меняется, но если в результате применения оператора ссылка может стать недействительной, оператор игнорируется. Правила Кодда Эдгар Кодд (1923-2003) – английский математик, участвовал во второй мировой войне, после переехал в Нью-Йорк и стал сотрудником компании IBM, в 1969-1970 гг. создал реляционную модель данных. Со временем реляционные СУБД набирали популярность, появлялось очень много некачественных продуктов, потому что производители очень многие устаревшие СУБД выдавали за реляционные. Тогда Кодд описал 12 правил, которые сейчас широко известны. Кстати, язык SQL Кодд считал неподходящим под его теорию. Однако компания IBM выпускала очень большое число продуктов, основанных на SQL. Поэтому работать в IBM ученый не смог и вскоре основал собственную консалтинговую компанию. Явное представление данных (правило информации). Информация должна быть записана в виде данных, хранящихся в ячейках таблицы, составные значения недопустимы. Порядок строк не должен влиять на смысл данных. По сути это – определение реляционной БД. (Искл. постреляционные СУБД) Гарантированный доступ к данным. К каждому элементу данных должен быть обеспечен доступ при помощи следующей комбинации: имя таблицы, значение первичного ключа и имя столбца. В реальных СУБД добавлены ещё такие параметры, как имя пользователя или имя БД. Полная обработка неопределённых значений. Для любых операций с любыми типами данных должны поддерживаться неопределённые значения (NULL). Пустая строка, 0, false <> NULL. Доступ к описанию БД в терминах реляционной БД. Словарь данных должен сохраняться в виде таблицы и СУБД должна обеспечивать к нему доступ стандартными средствами, теми же, которые используются для работы с таблицами, содержащими пользовательские данные. Словарь данных – это описание структуры БД. Получается, что реляционная база данных должна сама себя описывать. БД должна содержать набор системных таблиц, описывающих структуру самой базы данных. Полнота подмножества языка. Хотя бы один из поддерживаемых языков должен иметь четко определенный синтаксис и быть всеобъемлющим. Он должен поддерживать описание структуры данных и манипулирование ими, правила целостности, авторизацию и транзакции. Возможность обновления представлений. Все представления, которые теоретически можно обновить, должны быть обновляемы. Представление – виртуальная таблица, в которой содержится динамический набор данных из основных таблиц (например, объединение двух таблиц, результат выполнении операции над таблицей, подмножество столбцов или записей). Чаще всего строится на основе SQL-запросов. Сложность может возникнуть, например, если представление создано на основе нескольких таблиц. Наличие высокоуровневых операций управления данными. Должна быть возможность применения операций добавления, удаления и обновления данных не только к одной строке, но и ко множеству строк, т.е. ко всей таблице. Например, изменить в таблице зарплату всех сотрудников. Физическая независимость данных. Прикладные программы не должны зависеть от способа хранения данных и методов обращения к ним. Независимо от носителей, аппаратного обеспечения компьютеров – прикладные программы будут работать по-прежнему. Логическая независимость данных. Прикладные программы не должны зависеть от структуры реляционных таблиц. (При разбиении таблицы на две создается представление её заменяющее, чтобы изменение не сказывалось на работе приложений). Например, добавление в таблицу нового столбца не может сказаться на функционировании прикладных программ, так как доступ к данным осуществляется по именам столбцов. Правила 8 и 9 означают отделение пользователя и прикладных программ от низкоуровневой реализации БД. Независимость контроля целостности. Всё необходимое для контроля целостности должно храниться в словаре данных. Язык БД должен быть способен определять правила целостности и не должно существовать способа их обойти. Для каждой СУБД можно определить свои специфические правила целостности. Дистрибутивная независимость (правило независимости распространения). Реляционная БД должна быть переносимой и способной к распространению. (Как с системы на систему, так и по сети, объединяющей несколько систем). Дистрибутивность – согласованность двух операций, определенных на одном и том же множестве. База данных может быть распределенной или переноситься на другие компьютеры и это не должно сказаться на функционировании прикладного программного обеспечения. Согласование языковых уровней. Если в реляционной базе данных есть низкоуровневый язык, то должна отсутствовать возможность использования его для обхода правил и условий целостности данных, сформулированных на языке высокого уровня. Низкоуровневым называется язык, обрабатывающий одну запись за один раз, а высокоуровневым - обрабатывающий несколько записей за один раз. Правило 0.Реляционная СУБД должна быть способна полностью управлять базой данных через ее реляционные возможности. Получить доступ к словарю данных, обеспечить целостность, которую нельзя обойти даже на низкоуровневом языке, обрабатывать массивы записей. Всё управление реализуется с помощью связей между данными. Правило 0 требует выполнения всех остальных 12 правил. В SQL – представления, доступ к данным через имя БД. SQL – язык структурированных запросов SQL – непроцедурный язык, ориентированный на работу с реляционными базами данных. Теоретически, все его функции можно было бы выполнять с помощью любого объектно-ориентированного ЯП. Определить объект таблица, который содержал бы любое количество строк и написать процедуры для помещения в это объект данных, извлечение, поиск и т.д. (что-то похожее на работу со стеком). Однако занятие получается очень трудоемкое и непростое. Для того чтобы всего этого избежать, создан SQL. Пользователь работает с простым набором команд, вся обработка выполняется внутри СУБД. Существует 2 формы SQL. Интерактивный SQL. Применяется для непосредственной работы с БД с целью получения результатов для последующего использования. Результат (если он существует) сразу выводится на экран. (Запросы в Access). Встроенный SQL. Является частью приложения или программного модуля, SQL-код включается в текст программы, написанный на другом языке. (Например, на C). Результат работы операторов SQL перенаправляется в переменные, которыми оперирует базовая программа. SQL-код может быть статическим или динамическим (генерироваться во время выполнения программы). Встроенный SQL – это расширение интерактивного SQL. Любая форма SQL делится на несколько подразделов. Наиболее часто упоминаются следующие: Язык определения данных (Data Definition Language, DDL). Состоит из команд, которые создают объекты БД (таблицы, представления и т.д.). Язык манипулирования данными (Data Manipulation Language, DML). Команды для работы с данными в таблицах. Язык управления данными (Data Control Language, DCL). Включает в себя средства подтверждения прав пользователей на выполнение определенных действий. В ISO рассматривается как часть DDL. Мы будем рассматривать интерактивный SQL, команды языка манипулирования данными. Команды SQL создаются с помощью ключевых слов и аргументов команд. Ключевые слова принято писать заглавными буквами. Каждая команда SQL завершается символом ; . Строковые данные заключаются в апострофы. Создание таблиц Для создания таблиц используется инструкция CREATE TABLE. CREATE TABLE имя_таблицы (имя_столбцатип данных [NULL | NOT NULL], …) CREATE TABLE Plants (id_plant int, p_name char(20), p_definition varchar(200)); Имена таблиц и полей не могут содержать пробелов (или должны заключаться в []). Порядок столбцов в таблице будет таким же, как и в запросе. CREATE TABLE Zombies (id_zombie int, z_name char(20), z_definition varchar(200)); Для каждого столбца можно указать параметр NULL | NOT NULL. Значение NOT NULL означает, что столбец не может быть пустым (должен содержать данные). По умолчанию столбцы создаются с параметром NULL. CREATE TABLE Plants (id_plant int NOT NULL, p_name char(20), p_definition varchar(200)); Для создания счётчика используется команда IDENTITY. Для счётчика свойство NOT NULL устанавливается по умолчанию. CREATE TABLE Plants (id_plant int IDENTITY, p_name char(20), p_definition varchar(200)); Для объявления первичного ключа используется команда PRIMARY KEY. Ключ можно объявить на уровне столбца или таблицы. Объявление на уровне столбца (простой ключ). Параметр PRIMARY KEY указывается сразу после типа данных столбца. CREATE TABLE Plants (id_plant int PRIMARY KEY IDENTITY, p_name char(20), p_definition varchar(200)); Объявление на уровне таблицы (для ключа из нескольких столбцов). Параметр PRIMARY KEY указывается после описания всех полей таблицы. В скобках через запятую перечисляются поля, входящие в состав ключа. CREATE TABLE Plants (id_plant int IDENTITY, p_name char(20), p_definition varchar(200) PRIMARY KEY (id_plant)); Объявление первичного ключа устанавливает для ключевого поля значение NOT NULL. Ограничение CHECK устанавливает допустимые значения, которые могут быть вставлены в таблицу. В разделе CHECK описываются логические выражения, результат выполнения которых – истина (TRUE) или ложь (FALSE). Ограничение считается выполненным, если результат логического выражения – истина. Например, укажем, что название растения должно содержать не менее пяти символов. CREATE TABLE Plants (id_plant int IDENTITY, p_name char(20) , p_definition varchar(200) PRIMARY KEY (id_plant), CHECK (LEN(p_name) > 5)); Ограничение может включать в себя несколько условий. Для их объединения можно использовать операторы AND, OR, NOT. Также в описании условий применяются следующие конструкции: LEN(p_name) BETWEEN 18 AND 100 – длина имени должна быть в пределах от 18 до 100 символов LEN(p_name) IN (10, 20, 30) – длина имени может быть равна 10, 20 или 30 символам. Для объявления внешнего ключа применяют инструкции REFERENCES и FOREIGN KEY. Так же, как и первичный, внешний ключ можно объявить двумя способами: на уровне столбца (для простых ключей) и таблицы (для простых и составных ключей). Объявление на уровне столбца: CREATE TABLE Shop (id_product int IDENTITY, pr_name char(20), pr_definition varchar(150), pr_cost int, fk_plant int REFERENCES Plants (id_plant)); Здесь поле fk_plant – внешний ключ таблицы Shop, который ссылается на поле id_plant (первичный ключ таблицы Plants). Объявление на уровне таблицы: CREATE TABLE Shop (id_product int IDENTITY, pr_name char(20), pr_definition varchar(150), pr_cost int, fk_plant int, FOREIGN KEY (fk_plant) REFERENCES Plants (id_plant)); Пример создания составного внешнего ключа: CREATE TABLE Shop (id_product int IDENTITY, pr_name char(20), pr_definition varchar(150), pr_cost int, fk_plant int, FOREIGN KEY (fk_plant, pr_name) REFERENCES Plants (id_plant, p_name)); Можно определять действия, которые SQL Server будет предпринимать, когда пользователь попытается удалить или обновить ключ, на который указывают еще существующие внешние ключи. Действия указываются с помощью конструкций ON DELETE (при удалении) и ON UPDATE (при обновлении). Существует четыре варианта действий. CASCADE. Внешний ключ будет приведен в соответствие родительскому ключу. При изменении р.к. внешний ключ также изменится, при удалении – будет удалены все содержащие ключ записи. SET NULL. Значения внешнего ключа будут установлены в NULL. SET DEFAULT. Для внешнего ключа будет установлено значение по умолчанию, а при отсутствии такового – NULL. NO ACTION. Применяется по умолчанию. Внешний ключ не меняется, но если в результате применения оператора ссылка может стать недействительной, оператор игнорируется. По умолчанию выполняется действие NO ACTION, если не указано иное. CREATE TABLE Shop (id_product int IDENTITY, pr_name char(20), pr_definition varchar(150), pr_cost int, fk_plant int REFERENCES Plants (id_plant) ON DELETE NO ACTION ON UPDATE CASCADE); Выборка данных Для выборки данных в SQL используется инструкция SELECT. Она состоит из следующих основных частей. Части пишутся в определенном порядке. Список выборки. Здесь указываются столбцы, которые включаются в результат запроса. Столбцы возвращаются в порядке их перечисления в списке выборки. Чтобы список выборки содержал все столбцы таблицы, можно использовать символ *. Раздел FROM. Определяет источник (или источники) данных для выборки (таблицы или представления). SELECT * FROM Films; – выводит все данные из таблицы Films. SELECT FilmName, PublYear FROM Films; – выборка только названия и года выхода фильма. Имена объектов БД можно уточнить. Уточненное имя записывается в форме: ИмяСервера . ИмяБазы . ИмяВладельца . ИмяТаблицы (для таблицы) ИмяСервера . ИмяБазы . ИмяВладельца . ИмяТаблицы . ИмяСтолбца (для столбца) Уточнение имени полезно, например, при выборке данных из нескольких таблиц, если таблицы содержат одноимённые столбцы. Для того чтобы сократить размеры запроса, для громоздких имен объектов в разделе FROM можно задать псевдонимы. После объявления псевдонима нельзя обращаться к объекту по имени в текущем запросе. Служебное слово AS можно опустить. Псевдоним для таблицы: SELECT F.FilmName, F.Duration FROM Films AS F; Псевдоним для поля или функции: SELECT (RTRIM(F.FilmName) + ' - ' + F.Description) AS FilmAbout FROM Films F; RTRIM – функция, которая обрезает все пробелы справа от значения поля. LTRIM – слева. Запрос, возвращающий список формата название фильма (год): SELECT RTRIM(FilmName) + ' (' + CONVERT(char(4), PublYear) + ' г.)' as film FROM Films; Функция CONVERT преобразует выражение одного типа данных в другой: CONVERT ( data_type [ ( length ) ] , expression). Для исключения повторяющихся значений из результата выборки используется функция DISTINCT. SELECT DISTINCT FilmName FROM Films; – выборка названий фильмов без повторений. DISTINCT применяется ко всем столбцам, используемым в предложении SELECT. Альтернатива DISTINCT – функция ALL (повторяющиеся записи сохраняются). ALL применяется по умолчанию. Если требуется вывести на экран определенное число записей, используется предложение TOP. TOP выводит либо точное количество записей, либо процент записей от общего числа. Во втором случае используется аргумент PERCENT (округление в большую сторону). SELECT TOP 50 PERCENT FilmName FROM Films; – выборка половины списка фильмов (по порядку хранения в БД) SELECT TOP 2 FilmName FROM Films ORDER BY FilmName; – выборка первых двух фильмов (по алфавиту) Раздел WHERE. Определяет критерий отбора записей. В разделе можно задавать предикаты – условные выражения, которые могут иметь результат TRUE, FALSE или UNKNOWN для каждой строки таблицы. В результат запроса включаются только те строки, для которых предикат имеет значение TRUE. В предикатах используются операторы отношения (больше, меньше, равно и т.д.) и логические операторы (AND, OR, NOT). SELECT FilmName FROM Films WHERE FilmCompany = 'New Line Cinema' AND PublYear > 2000; Сравниваемые объекты не обязаны присутствовать в списке выборки. Если необходимо выбрать все строки, в которых какое-то поле не содержит значения, используется конструкция IS NULL. Например, выборка всех фильмов, для которых не указан год выпуска: SELECT FilmName FROM Films WHERE PublYear IS NULL; Для выбора строк, в которых поле содержит любое значение, отличное от NULL, используется конструкция IS NOT NULL. Выборка фильмов, для которых год выпуска указан: SELECT FilmName FROM Films WHERE PublYear IS NULL; Раздел GROUP BY. Предназначен для группировки записей и вычисления для них агрегатных (статистических) функций. Группировка происходит по одному или нескольким полям, объединяются записи с одинаковым значением полей. Кроме полей для группировки выделяются поля, для которых будет вычислена агрегатная функция. Агрегатные функции выполняют вычисление на наборе значений и возвращают одиночное значение. Все они, за исключением COUNT, не учитывают значение NULL. В T-SQL используются следующие основные функции: AVG – среднее арифметическое группы значений; SUM – сумма значений; MIN – минимальное значение; MAX – максимальное значение; COUNT – количество элементов в выборке. Агрегатные функции могут использоваться без раздела GROUP BY. Например, можно посчитать, сколько в БД фильмов: SELECT COUNT(FilmCompany) FROM Films; Если нужно узнать более подробную информацию, например, сколько фильмов вышло в каждом году, значения придется сгруппировать по полю год выпуска. Поля, по которым происходит группировка, не обязаны присутствовать в списке выборки (в разделе SELECT поле PublYear можно не использовать). Однако, наоборот, любое поле, которое входит в список выборки и не является агрегатной функцией, обязано входить в раздел GROUP BY. SELECT PublYear, COUNT(FilmID) as Fcount FROM Films GROUP BY PublYear; Подсчитать среднюю продолжительность фильмов компании New Line Cinema: SELECT FilmCompany, AVG(Duration) AS Favg FROM Films WHERE FilmCompany = 'New Line Cinema' GROUP BY FilmCompany; Раздел HAVING. Аналогичен разделу WHERE, но используется только в сочетании с GROUP BY (в WHERE нельзя использовать агрегатные функции). Предназначен для фильтрации записей. Выбрать кинокомпании, которые сняли больше двух фильмов. Такая запись невозможна: SELECT FilmCompany FROM Films WHERE COUNT(FilmID) > 2 GROUP BY FilmCompany; Возможная запись: SELECT FilmCompany FROM Films GROUP BY FilmCompany HAVING COUNT(FilmID) > 2; В разделе HAVING могут использоваться либо агрегатные функции, либо поля, содержащиеся в разделе GROUP BY. Выбрать все кинокомпании, которые сняли больше двух фильмов после 2000 года. Такая запись невозможна: SELECT FilmCompany FROM Films GROUP BY FilmCompany HAVING COUNT(FilmID) > 2 AND PublYear > 2000; Возможная запись: SELECT FilmCompany FROM Films WHERE PublYear > 2000 GROUP BY FilmCompany HAVING COUNT(FilmID) > 2; Группировку можно делать по нескольким столбцам. Посчитать, сколько фильмов выпустила каждая компания в каждом году. SELECT FilmCompany, PublYear, COUNT(FilmID) as Fcount FROM Films GROUP BY FilmCompany, PublYear; Раздел ORDER BY. Предназначен для сортировки записей. Результат можно сортировать по нескольким столбцам. Используются ключевые слова ASC (по возрастанию) и DESC (по убыванию). По умолчанию данные сортируются по возрастанию. Сортировка по возрастанию (в алфавитном порядке) по названию книги: SELECT FilmName FROM Films ORDER BY FilmName; Сортировка по убыванию по названию и году издания: SELECT FilmName, PublYear FROM Films ORDER BY FilmName, PublYear DESC; Объединение таблиц Выборку данных можно делать сразу из нескольких таблиц. В этом случае в разделе FROM используется операция объединения (JOIN). Наиболее часто используются три вида объединений: INNER JOIN (выводятся только те записи, в которых друг другу соответствуют значения связанных полей); LEFT JOIN (возвращаются все записи левой таблицы, даже если соответствующие записи правой таблицы не существуют); RIGHT JOIN (возвращаются все записи правой таблицы, даже если соответствующие записи левой таблицы не существуют); Для каждой пары объединяемых таблиц необходимо указать поля, по которым будет создано соединение. Для этого используется ключевое слово ON. Связанными считаются те записи, которые имеют одинаковые значения связанных полей. Например, вывести список фильмов и режиссера каждого фильма. SELECT FilmName, DName FROM Films INNER JOIN Directors ON Films.DirectorID = Directors.DirectorID; Здесь таблицы Films и Directors связаны связью INNER JOIN по двум полям – DirectorID из таблицы Films и DirectorID из таблицы Directors. Предположим, таблицы заполнены следующим образом: При связи INNER JOIN результат запроса будет следующим: Фильм Звездные войны на экран выведен не будет, т.к. для него не указан код режиссера, т.е. нет связи с таблицей Directors. Аналогично, в выборку не попадет Джордж Лукас, поскольку он не связан ни с одним из фильмов в таблице Films. Если вместо INNER JOIN использовать LEFT JOIN, левой считается та таблица, которая в запросе находится слева (Films LEFT JOIN Directors), в нашем случае таблица Films. Из неё будут выведены все записи, при этом из правой таблицы (Directors) выводятся только те записи, которые связаны с Films. Если связанного значения нет, поле из правой таблицы получает значение NULL. Результат выполнения запроса: При связи RIGHT JOIN, наоборот, выводятся все записи из правой таблицы. Связывать можно сразу несколько таблиц. Например, выбрать актеров, которые играют в фильме Ирония судьбы. SELECT FilmName, ActorName FROM (Films F INNER JOIN Cast C ON F.FilmID = C.FilmID) INNER JOIN Actors A ON C.ActorID = A.ActorID Вывести актеров, которые еще не играли ни в одном фильме. SELECT ActorName FROM Cast C RIGHT JOIN Actors A ON C.ActorID = A.ActorID WHERE C.ActorID IS NULL; Здесь из-за связи LEFT JOIN будут выведены записи обо всех актерах, однако те актеры, ID которых нет в таблице Cast, в поле C.ActorID будут иметь значение NULL. Этим можно воспользоваться при отборе записей (WHERE C.ActorID IS NULL). Добавление данных Для добавления данных в SQL применяется инструкция INSERT. Чаще всего её используют для вставки в таблицу отдельных записей. Инструкция выглядит следующим образом: INSERT INTO имя_таблицы VALUES (значение 1, значение 2, …) После ключевого слова VALUES указывается список значений, которые будут добавлены в таблицу. Значения вставляются в столбцы в порядке расположения столбцов в таблице. Вставка данных в столбец счетчика (с признаком IDENTITY) по умолчанию невозможна, в инструкции SQL это поле пропускается. Например, для добавления новой группы в таблицу Genres (GenreID, GenreName, GenreHistory): INSERT INTO Genres VALUES ('Комиксы', 'Не знаю, какая у них история'); Для вставки данных можно указать конкретные столбцы, если столбца нет в списке выборки, и он не является счетчиком, в него будет записано значение NULL. INSERT INTO Genres (GenreName, GenreHistory) VALUES ('Комиксы', 'Не знаю, какая у них история'); INSERT INTO Genres (GenreName) VALUES ('Комиксы'); В столбцы, для которых значение не указано, будет добавлено значение NULL. Команду INSERT можно также использовать для перемещения данных из одной таблицы в другую. Для этого предложение VALUES нужно заменить запросом SELECT. Таблицы должны быть идентичными – иметь равное количество столбцов с одинаковыми типами данных. Например, можно записать все жанры из таблицы Genres в Genres1. Код жанра (поле IDENTITY) проставляется автоматически. INSERT INTO Genres1 SELECT GenreName, GenreHistory FROM Genres; Добавить записи обо всех читателях г. Ступино из таблицы Readers в таблицу Readers1. INSERT INTO Readers1 (ReaderName, ReaderSurname, Passport, CityID) SELECT ReaderName, ReaderSurname, Passport, c.CityID FROM Readers r INNER JOIN Cities c ON r.CityID = c.CityID WHERE CityName = 'Ступино'; Удаление данных Для добавления данных в SQL применяется инструкция DELETE. Удаляются не отдельные поля, а строки целиком. DELETE FROM Books; - удалить все записи о книгах. Если требуется удалить определенные записи, это можно указать в разделе WHERE. DELETE FROM Books WHERE BookName = 'Мертвые души'; Если необходимо удалить записи по признаку, информация о котором хранится в другой таблице, строится подзапрос в разделе WHERE. Например, удалить записи о книгах детективного жанра. DELETE FROM Books WHERE GenreID IN (SELECT GenreID FROM Genres WHERE GenreName = 'Детектив'); Здесь запрос SELECT возвращает какое-то количество значений GenreID (те, для которых имя жанра – Детектив). После этого значение каждого поля GenreID из таблицы Books сравнивается по очереди со всеми значениями, полученными с помощью SELECT. Если значения совпадают, соответствующая запись удаляется из таблицы Books. IN обозначает вхождение – входит ли GenreID в множество значений, которое вернет SELECT. Если заранее известно, что SELECT способен вернуть только одну запись, вместо оператора IN можно использовать знак =. В нашем случае книг-детективов может быть несколько. Удалить из библиотеки книги Агаты Кристи DELETE FROM Books WHERE BookID IN (SELECT BookID FROM Authors a INNER JOIN AuthorShip ash ON a.AuthorID = ash.AuthorID WHERE AuthorName = 'Агата' AND AuthorSurname = 'Кристи'); Изменение данных Происходит с помощью инструкции UPDATE. Поля, значения которых нужно изменить, указываются в разделе SET. Например, изменить название г. Ступино на Новое Ступино, а его индекс на 142900 UPDATE Cities SET CityName = 'Новое Ступино', PostCode = '142900' WHERE CityName = 'Ступино'; Если необходимо изменить несколько полей, в разделе SET их указывают через запятую. В раздел WHERE можно вставлять подзапросы, так же, как в инструкции DELETE. Например, отметить, что читатель Романов сдал все книги. UPDATE BookLending SET ReturnFlag = 1 WHERE ReaderID = (SELECT ReaderID FROM Readers WHERE ReaderSurname = 'Романов') Подзапрос также может находиться в разделе SET. Изменить жанр книги Мертвые души на Роман. Для этого необходимо изменить поле GenreID в таблице Books, при этом само название жанра менять ни к чему. UPDATE Books SET GenreID = (SELECT GenreID FROM Genres WHERE GenreName = 'Роман') WHERE BookName = 'Мертвые души'; Проектирование баз данных Нельзя просто так взять и построить базу данных. (с) Казалось бы, куда проще. Взял и написал БД по образу и подобию уже существующих. Однако потом выясняется, что хорошей схемы базы нет, БД имеет слишком большой объем или чересчур медленно работает, какие-то возможности потерялись; людям, которые будут базу поддерживать, разобраться в ней очень непросто, да и сам разработчик в какой-то момент забывает, для чего нужно то или иное поле. Чтобы избежать подобных проблем, весь проект условно разделяют на три основных этапа – концептуальное (инфологическое) проектирование, создание логической и физической моделей БД. Концептуальное проектирование и построение ER-модели Концептуальное проектирование – это построение информационной модели наиболее высокого уровня абстракции, без ориентации на какую-либо конкретнуюСУБДимодель данных. На этом этапе проектирования используют так называемые ER-модели (Entity Relationship Model – модель сущность-связь). Модель была предложена американским ученым Питером Ченом в 1976 г. Основные элементы модели – сущность, отношение, атрибут. Сущность – реальный или представляемый объект, информацию о котором необходимо хранить в базе данных (человек, книга, часть света, профессия и т.д.) Сущности состоят из экземпляров данных, т.е. если профессия – это сущность, то повар – экземпляр данной сущности. Сущности могут быть связаны друг с другом. Например, сущность-человек имеет одну или несколько профессий. Различают связи (отношения) трёх типов: один-к-одному, один-ко-многим, многие-ко-многим. Отношение может объединять две и более сущностей или связывать сущность саму с собой. Атрибут – значимое свойство сущности или отношения (название профессии, имя и возраст человека, автор книги). Разным сущностям и отношениям могут принадлежать одноименные атрибуты (название для книги и для профессии). Строки таблицы также называют кортежами. Каждый кортеж – это набор данных об одном экземпляре объекта. Существуют различные нотации (способы графического отображения) концептуальных моделей – ER-диаграммы. Одна из самых известных – нотация Питера Чена. В ней каждая сущность изображается в виде прямоугольника, атрибут – овала, отношение – ромба. Например, попробуем построить возможный вариант фрагмента схемы БД многопользовательской игры Counter Strike. Здесь каждый игрок обладает атрибутами Имя и Внешний вид, у команды есть название и концепция игры, игрок принадлежит к конкретной команде (связь 1 команда – много игроков), также он может покупать оружие (связь много оружия – много игроков), для которого заданы название и стоимость. Логическое проектирование На этом этапе создается схема данных с привязкой к конкретной модели данных, например, реляционной, иерархической, или сетевой. Создадим схему реляционной БД. Для этого будем использовать CASE-средство Erwin Data Modeler. Эта схема – всё ещё ER-модель, она содержит только логические данные, без какой-либо физической структуры. Однако теперь для каждой сущности определены первичные (находятся в верхней части каждой сущности, отделены чертой) и внешние (отметка FK) ключи. Для каждого атрибута может быть указан тип данных, пока довольно абстрактный – текст, число, без указания типа конкретной СУБД. Отношения 1:М на схеме отображаются в виде линий, связывающих первичный и внешний ключи (например, связь Команды.ID команды – Игроки.ID команды (FK)). Отношение М:М представлено в виде новой сущности – Покупка. Физическое проектирование Физическое проектирование – созданиесхемы базы данныхдля конкретнойСУБД. Появляются ограничения на именование объектов базы данных, ограничения на поддерживаемые типы данных и т.п. Общая структура схемы практически не меняется. Физическая схема – это уже схема реальной БД. Сущности превращаются в таблицы, атрибуты – в поля таблиц. ERwin позволяет транслировать созданную схему на сервер выбранной СУБД. Нормализация базы данных Нормализация – приведение БД к правилам, которые помогают сохранить её целостность и удалить избыточные данные. Правила называют также нормальными формами. Всего известно 8 нормальных форм. Первая нормальная форма (1NF) Вторая нормальная форма (2NF) Третья нормальная форма (3NF) Нормальная форма Бойса-Кодда (BCNF) Четвертая нормальная форма (4NF) Первая нормальная форма (5NF) Доменно-ключевая нормальная форма (DKNF) Шестая нормальная форма (6NF) Наиболее часто базы данных приводят к третьей нормальной форме. Дело в том, что с каждой новой НФ увеличивается количество атрибутов и таблиц в БД, в итоге – возрастает сложность запросов и уменьшается скорость работы. Существует такое понятие, как денормализация – это приведение структуры базы данных в состояние, не соответствующее критериямнормализации, с целью увеличения её производительности. Рассмотрим подробно первые три нормальные формы. Первая нормальная форма. Все атрибуты должны быть атомарны, т.е. каждое поле каждой записи должно содержать неделимое значение (при любом делении атрибут теряет смысл). Не должно быть повторяющихся атрибутов. Каждая таблица должна содержать первичный ключ. Рассмотрим таблицу Библиотека. В ней хранятся данные о читателях библиотеки. Что делать, если понадобится поиск по фамилии? Неатомарные значения можно разбивать программно, однако удобнее сделать это сразу в БД. Также каждый читатель может иметь несколько номеров телефонов (сколько – заранее не известно), этот атрибут следует перенести в отдельную сущность. Приведём БД к 1НФ. Вторая нормальная форма. Выполняется первая нормальная форма; неключевые поля должны зависеть от всего первичного ключа таблицы. При выдаче каждой новой книги в таблице Библиотека приходится полностью дублировать данные о читателе, который её взял. Чтобы избежать избыточности данных, разобьём сущность Библиотека на 2 сущности – Читатели и Выдача книг. Обратите внимание на сущность Выдача книг. Название книги зависит от кода книги, но не зависит от кода читателя и даты выдачи. В итоге название книги будет повторяться для каждой новой выдачи книги разным читателям. Перенесем атрибут Название книги в отдельную сущность Книги. Ключ таблицы Книги является подмножеством ключа таблицы Учет книг. Третья нормальная форма. Выполняется вторая нормальная форма; атрибуты не зависят ни от чего, кроме первичного ключа. В сущности Читатели почтовый индекс зависит от названия города, в котором живет читатель, название города – от кода читателя. Появляется транзитивная зависимость Почтовый индекс - Город - Читатель. Для разных читателей город может повторяться, значит, будет повторяться и индекс, информация станет избыточной. При этом, если не введено название города, нельзя ввести в БД почтовый индекс. Перенесем индекс и название города в отдельную сущность Города. В результате всех преобразований и приведения к 3НФ в БД Библиотека вместо одной сущности появилось пять. 3НФ можно описать следующим образом: Каждый атрибут таблицы зависит от ключа, от всего ключа, ни от чего, кроме ключа. Индексы Общие сведения Индексирование таблиц – это способ отсортировать данные по определенной колонке. Когда список отсортирован, намного проще производить поиск необходимых данных. Представьте себе большую книгу, в которой Вам необходимо найти информацию о каком-нибудь предмете, например, варианты приготовления блюда. Можно попробовать быстро пролистать всю книгу и отыскать все необходимые рецепты, однако логичнее открыть предметный указатель, найти нужное название, страницы, на которых оно упоминается, и читать уже только эти страницы. Почему с предметным указателем работать быстрее, чем просто перебирать всю книгу? Во-первых, в указателе описаны только нужные параметры, те, по которым ищут чаще всего. Например, заголовки тем или названия блюд. Во-вторых, данные в указателе отсортированы в алфавитном порядке, что сильно ускоряет поиск. Индексы устроены по такому же принципу. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке. При поиске происходит последовательный просмотр таблицы строка за строкой, это может занимать очень много времени. Если создать индекс – упорядоченный набор ссылок на строки таблицы – поиск данных может стать значительно быстрее. Итак, индекс– это упорядоченный набор значений из индексированногостолбца(или нескольких столбцов) с указателями на места физического размещения строкв структуребазы данных. Индекс – это отдельный объект базы данных, который привязывается к конкретным столбцам таблицы (индексируемым столбцам). Индексы бывают кластерными (CLUSTERED) и не кластерными (NONCLUSTERED). Кластерные индексы Для начала разберемся, как физически хранятся данные в БД. Данные хранятся небольшими блоками – страницами. Каждая страница данных в SQL Server содержит 8 килобайт информации. Группа из восьми стоящих рядом страниц называется пространством. Строки данных не хранятся в каком-либо определенном порядке, также нет определенного порядка для последовательности страниц. Когда строка вставляется в страницу и страница переполнена, страница разделяется. Неотсортированный набор страниц данных, содержащих строки одной таблицы, называется кучей. Рассмотрим таблицу Контакты. Данные хранятся в страницах безо всякой сортировки. При определении кластерного индекса строки физически сортируются на диске в соответствии с индексируемым полем. Кластерный индекс в таблице может быть только один (нельзя же одновременно физически отсортировать данные по двум полям). При добавлении новой строки в таблицу, она дописывается не в конец списка, а в нужное место таблицы, соответствующее ей по сортировке. При необходимости происходит также перестроение верхних уровней индекса. В SQL Serverиндексыхранятся в видеB-деревьев(B-tree). B означает сбалансированное (не путать с бинарным), т.е. длина любых двух путей в дереве от корня (верхнего уровня) до листов (нижних уровней) различается не более, чем на единицу. Рассмотрим процесс добавления данных в таблицу Контакты (для таблицы уже определен кластерный индекс). Изначально таблица содержит три записи, которые физически отсортированы по столбцу Фамилия (на схеме отображен только этот столбец). Будем считать, что в одной странице данных можно разместить не более 4 записей. Тогда при добавлении 4 новых строк страница с данными разделяется на две, причём в том узле (на той странице), где раньше были данные, теперь располагается индекс, охватывающий обе новые страницы. Один элемент индекса указывает на одну страницу данных. При дальнейшем добавлении данных в таблицу индекс разрастается, появляются новые уровни. Использование кластерного индекса подразумевает, что в его листьях хранятся записи таблиц (а не ссылки на них). При создании втаблицепервичного ключа (PRIMARY KEY) сервер автоматически создает для негокластерный индекс, если его не существовало ранее или если при определении ключа не был явно указан другой типиндекса. Некластерные индексы При использовании некластерного индекса строки таблицы могут храниться на диске в любом порядке. Сам индекс представляет собой дерево ссылок на соответствующие записи. Каждой записи в таблице назначается идентификатор. Один листовой элемент индекса соответствует одной записи. По этой причине, а также потому, что указатели некластерного индекса имеют больший размер, чем у кластерных индексов (поскольку содержат идентификатор записи, а не просто идентификатор страницы), некластерные индексы обычно занимают намного больше места. Обратите внимание, в данном примере есть две записи со значением Летов - значит, создается также два указателя в индексе, по одному на каждую запись. При создании втаблицеограничения UNIQUE для ограничиваемого столбца автоматически создается некластерный индекс, если не был явно указан другой типиндекса. Создание индекса Общая схема SQL-инструкции для создания индексов: CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX имя_индекса ON имя_таблицы (столбец [,…]); Команда содержит следующие параметры: UNIQUE – запрещает наличие повторяющихся значений для индекса. Обычно используется для столбцов первичного ключа. По умолчанию индекс не является уникальным. CLUSTERED – создает кластерный индекс. NONCLUSTERED – создает некластерный индекс. имя_индекса – определяет имя индекса, уникальное в пределах таблицы. столбец – определяет столбцы, включаемые в индекс. Например, создадим уникальный кластерный индекс по столбцу Sirname таблицы Contacts. Индекс будет иметь имя index1. CREATE UNIQUE CLUSTERED INDEX index1 ON Contacts (Sirname); Создадим неуникальный некластерный индекс index2. CREATE INDEX index2 ON Contacts (Sirname); При построении индексов можно опираться на следующие правила. Не создавайте редко используемые индексы для таблиц с частой модификацией данных. Особенно не следует создавать кластерные индексы для таких таблиц, т.к. при любом изменении будет происходить физическое перестроение записей в таблице. При извлечении из таблицы очень большого числа строк использование индекса – только потеря времени. Индексы лучше создавать для столбцов, значения в которых редко повторяются. Индекс должен быть максимально узким (меньшего объема). Тогда дерево индекса станет более плоским, и поиск будет происходить быстрее. По возможности нужно использовать типы данных наименьшего размера - числа сравниваются несколько быстрее, чем строки Индексы полезны при сортировке. Если сортировка происходит по индексируемому столбцу (или нескольким столбцам одного индекса), нет необходимости переупорядочивать данные. Кластерные индексы обеспечивают очень высокое быстродействие при выборке по диапазону Многопользовательский доступ к данным Технология клиент-сервер Сервер – компьютер, предназначенный для выполнения определенных служебных задач (напр., файл-сервер). Также сервер – программное обеспечение, которое отвечает на запросы клиентов и предоставляет им доступ к ресурсам или услугам. Виды серверов: FTP-сервер (ftp – протокол для передачи файлов); WEB-сервер (принимает от клиентов http-запросы, возвращает http-ответы); сервер БД; SMTP-сервер; прокси-сервер (позволяет клиентам выполнять косвенные запросы к другим сетевым службам) и др. Клиент – это аппаратный или программный компонент, который отправляет серверу запросы, в ответ получает информацию и предоставляет её пользователю. По сути, сервер владеет и распоряжается информационными ресурсами системы, клиент имеет возможность ими воспользоваться. В случае если обработка и представление данных происходят на машине клиента, это толстый клиент. При использовании тонких клиентов большая часть обработки данных происходит на сервере. В отличие от толстого, компьютеру – тонкому клиенту не требуется специального ПО. При работе с базами данных часто используется следующая схема: Тонкий клиент (веб-браузер) обращается к веб-серверу по протоколу http, веб-сервер отправляет запрос к серверу БД, получает данные, обрабатывает их, формирует html-страницу и возвращает её клиенту. Транзакции Транзакция представляет собой команду или группу команд SQL, которые завершаются или отменяются как единое целое. Классический пример транзакции – пересылка денег через банкомат. Предположим, через банкомат происходит перевод $5000 с накопительного счёта на текущий. Программа вычитает $5000 с накопительного счёта, после чего увеличивает текущий счёт на $5000. Во время работы программы после вычитания денег с накопительного счёта, но до увеличения текущего счёта на банкомате происходит сбой питания. Вопрос: что происходит с $5000? Деньги возвращаются на накопительный счёт, помещаются на текущий счёт или пропадают навеки? Если эти две команды объединены в транзакцию, проблем нет. Это гарантирует, что обе команды либо успешно завершатся, либо не завершится ни одна из них. Без транзакций существует опасность того, что будет выполнена лишь одна команда. Каждый из операторов INSERT, UPDATE, DELETE создаёт транзакцию, которая завершается при завершении оператора. Транзакция может состоять также из нескольких операторов. Для управления транзакциями существуют специальные команды. Синтаксис Применение BEGIN { TRAN | TRANSACTION } [имя_транзакции | переменная] Определение начала транзакции. Имя транзакции может храниться в переменной. COMMIT { TRAN | TRANSACTION } [имя_транзакции | переменная] Отметка об успешном завершении транзакции, изменение данных становится постоянным. COMMIT WORK Завершение транзакции. Работает аналогично COMMIT TRAN, но без имени транзакции. ROLLBACK { TRAN | TRANSACTION } [имя_транзакции | имя_точки_сохранения | переменная] Откат изменений до начала транзакции или до точки сохранения. Команда освобождает ресурсы, занятые транзакцией. ROLLBACK [ WORK ] Работает аналогично ROLLBACK TRAN, но без имени транзакции. SAVE { TRAN | TRANSACTION } { имя_точки_сохранения | переменная } Установка точки сохранения внутри транзакции, к которой может выполняться откат. Точка сохранения определяет место, к которому может возвратиться транзакция, если часть транзакции условно отменена. Если во время транзакции происходят ошибки, выполняется команда ROLLBACK TRAN. При многопользовательском доступе всегда есть возможность параллельного доступа, т.е. одновременного обращения нескольких пользователей к одним и тем же данным. Из-за этого могут возникать проблемы. Проблемы параллельного доступа. При параллельном использовании транзакций могут возникать следующие проблемы: потерянное обновление (lost update); "грязное" чтение (dirty read); неповторяющееся чтение (non-repeatable read); фантомная вставка (phantom insert). Потерянное обновление может возникнуть в ситуации, когда две транзакции обновляют одни и те же данные. Предположим, происходит одновременная работа с таблицей ShopTable. Две транзакции открыты различными приложениями. Транзакция 1 Транзакция 2 Cost SELECT Сost FROM ShopTable WHERE PrName = ‘CD-R’; UPDATE ShopTable SET Сost = Сost*2 WHERE PrName = ‘CD-R’; SELECT Сost FROM ShopTable WHERE PrName = ‘CD-R’; UPDATE ShopTable SET Сost = Сost*2 WHERE PrName = ‘CD-R’; 50 100 200 В транзакции 1 изменяется значение поля Cost, а затем в транзакции 2 также изменяется значение этого поля. В результате изменение, выполненное второй транзакцией, сделает результат неожиданным. Пусть начальное значение Cost = 50, тогда транзакция 1 изменит его на Cost = 100, после чего транзакция 2 увеличит значение поля ещё в два раза, в итоге Cost = 200. Грязное чтение возникает, когда вторая транзакция видит не сохранённые изменения, сделанные первой транзакцией, после чего происходит откат первой транзакции. Транзакция 1 Транзакция 2 BookName SELECT BookName FROM Books WHERE SerialNum = “1234”; UPDATE Books SET BookName = “Азбука” WHERE SerialNum = “1234”; ROLLBACK WORK; SELECT BookName FROM Books WHERE SerialNum = “1234”; “Обломов” “Азбука” “Азбука” “Обломов” В транзакции 1 изменяется значение поля Cost, а затем транзакция 2 выбирает значение этого поля. После этого происходит откат транзакции 1. В результате значение, полученное второй транзакцией, будет отличаться от реального значения, хранимого в базе данных. Неповторяющееся чтение – это ситуация, когда в рамках одной транзакции один и тот же запрос возвращает разные результаты. Транзакция 1 Транзакция 2 Balance SELECT Balance FROM BTable WHERE UserID = 1; UPDATE BTable SET Balance = Balance - 1000 WHERE UserID = 1; SELECT Balance FROM BTable WHERE UserID = 1; SELECT Balance FROM BTable WHERE UserID = 1; 2000 1000 1000 В транзакции 2 выбирается значение поля Balance, это значение используется для расчетов. В это время транзакция 1 изменяет поля Balance. При повторной попытке выбора значения из поля Balance в транзакции 2 будет получен другой результат. Пример: считывается информация о состоянии счета клиента банка, проверяется, достаточно ли средств на счету для выполнения какой-либо операции. В это время другая транзакция переводит деньги на другой счет. Следовательно, данные первой транзакции являются ошибочными, при повторном считывании данных будет получен другой результат, операцию проводить нельзя. Фантомная вставка – частный случай неповторяющегося чтения – заключается в том, что результаты одной и той же выборки в рамках одной транзакции имеют разное количество возвращаемых строк. Транзакция 1 Транзакция 2 INSERT ShopTable (PrName, Cost) VALUES (rake, 1000); SELECT AVG(Cost) FROM ShopTable; SELECT AVG(Cost) FROM ShopTable; В транзакции 2 выполняется запрос, использующий все значения поля cost. Затем транзакция 1 выполняет вставку новой строки, после этого повторное выполнение запроса в транзакции 2 выдаст другой результат. При этом, если выполняемый запрос выбирает не все значения поля cost, а значение только одной строки таблицы, то выполнение оператора INSERT не приведет к ситуации фантомной вставки. Блокировки и уровни изоляции Для предотвращения конфликтов SQL сервер может ограничить одновременный доступ к данным. Запреты на работу с данными называются блокировками. Для поддержки блокировок определены уровни изоляции, которые определяют, какие типы конфликтов допустимы. В табл. 1 перечислены уровни изоляции и операции, разрешенные на каждом уровне. Табл. 1. Разрешенные операции: Уровень изоляции Потерянное обновление Грязное чтение Неповторяющееся чтение Фантомная вставка READ UNCOMMITED нет да да да READ COMMITED нет нет да да REPEATABLE READ нет нет нет да SERIALIZABLE нет нет нет нет READ UNCOMMITED (неподтвержденное чтение). Самый низкий уровень изоляции, поддерживается защита только от потерянного обновления. Если пользователь изменяет данные, другие пользователи не могут изменить их, пока первая транзакция полностью не завершится. Тем не менее, разрешено грязное чтение, т.е. если первая транзакция запишет какие-то данные, вторая их прочитает, а потом первая транзакция будет отменена, то получится, что вторая транзакция прочитала данные, которые никогда не существовали. READ COMMITED (подтвержденное чтение). Запрещено грязное чтение: если пользователь изменяет данные, другие пользователи не могут считать их, пока изменение (транзакция) полностью не завершится. Однако существует проблема неповторяющегося чтения: если первый пользователь начал работу и прочитал данные, после этого второй пользователь изменил эти же данные и успешно зафиксировал изменения – то первый пользователь будет продолжать работу с уже изменёнными данными. REPEATABLE READ (повторяющееся чтение). Повторное чтение строки возвратит первоначально считанные данные, любые обновления, произведенные другими пользователями до завершения транзакции, запрещены. Тем не менее, на этом уровне могут возникать фантомы. SERIALIZABLE (упорядоченность). Самый высокий уровень изоляции. Результат выполнения транзакций такой же, как если бы они выполнялись по очереди. Невозможны никакие ошибки параллельного доступа. Грануляция блокировок (уровни блокирования) Заблокировать можно различные объекты БД. Наиболее часто используются следующие уровни блокирования: уровень БД (база данных, полностью); уровень страницы (на физическом уровне БД поделена на блоки одного размера (страницы), размер страницы для SQL Server по умолчанию – 8 килобайт); уровень таблицы; уровень строки; В зависимости от уровня блокирования будет изменяться быстродействие базы данных. Блокировка при меньшей гранулярности, например на уровне строк, увеличивает параллелизм, но в то же время увеличивает расходы на обработку, поскольку при большом количестве блокируемых строк требуется больше блокировок. Приложения Приложение 1. Основные типы данных Transact-SQL источник: http://msdn.microsoft.com/ru-ru/library/ms187752.aspx Точные числа Тип данных Диапазон Память tinyint от 0 до 255 1 байт smallint от -2^15 (-32 768) до 2^15-1 (32767) 2 байта int от -2^31 (-2 147 483 648) до 2^31-1 (2 147 483647) 4 байта bigint от -2^63 (-9 223 372 036 854 775 808) до 2^63-1 (9 223 372 036 854 775807) 8 байт bit 0; 1 Строковые значения TRUE и FALSE можно преобразовать в значения типа bit: TRUE преобразуется в 1, а FALSE — в 0. Если в таблице имеется 8 или меньше столбцов типа bit, они хранятся как 1 байт. Если имеется от 9 до 16 столбцов типа bit, они хранятся как 2 байта и т.д. decimal, numeric Максимальный диапазон от -10^38+1 до 10^38-1 При максимальной точности – 17 байт smallmoney от -214 748,3648 до 214 748,3647 4 байта money от -922 337 203 685 477,5808 до 922 337 203 685 477,5807 8 байт Приблизительные числа (числовые данные с плавающей запятой) Тип данных Диапазон Память float - 1,79E+308 — -2,23E-308, 0 и 2,23E-308 — 1,79E+308 float(n), Зависит от значения n, 4 или 8 байт. n – количество бит, используемых для хранения мантиссы числа real - 3,40E + 38 — -1,18E - 38, 0 и 1,18E - 38 — 3,40E + 38 4 байта Дата и время Тип данных Диапазон Память date от 1 января 1 года до 31 декабря 9999 года 3 байта time от 00:00:00.0000000 до 23:59:59.9999999 5 байт datetime от 1 января 1753 года до 31 декабря 9999 года от 00:00:00 до 23:59:590,997 8 байт datetime2 от 1 января 1 года до 31 декабря 9999 года От 00:00:00 до 23:59:59.9999999 от 6 до 8 байт в зависимости от точности smalldatetime от 1 января 1900 года до 6 июня 2079 года от 00:00:00 до 23:59:59 4 байта datetimeoffset от 1 января 1 года до 31 декабря 9999 года От 00:00:00 до 23:59:59.9999999 учитывает смещение часового пояса 10 байт Символьные строки Тип данных Диапазон Память char [( n )] Символьные данные фиксированной длины, не в Юникоде, с длиной n байт. Значение n должно находиться в интервале от 1 до 8000. n байт nchar [( n )] Символьные данные в Юникоде длиной в n символов. Аргумент n должен иметь значение от 1 до 4000. n*2 байт. varchar [(n)] Символьные данные переменной длины, не в Юникоде. n от 1 до 8 000. max означает, что фактическая длина данных плюс два байта. nvarchar [(n)] Символьные данные в Юникоде переменной длины. Аргумент n может принимать значение от 1 до 4000. (фактическая длина данных * 2) плюс два байта. Приложение 2. Схемы инструкций SQL Выборка данных. Добавление данных Удаление данных Изменение данных Объединение таблиц. Основная литература: Под ред. А.Д.Хомоненко Базы Данных. учебник для ВУЗов СПб. РиС 2000г. Виктор Пасько Access 97 М. ОАО Спаррк 1997г. (библиотека) Стивен Бобровски Oracle 7 и вычисления клиент/сервер М. Лори 1996г. Мартин Грабер Введение в SQL М. Лори 1996г. Ш.Атре Структурный подход к организации баз данных М. Финансы и статистика 1983г. Дополнительная литература: Мартин Грабер SQL. Справочное руководство М. Лори 2001г. В.В.Фаронов, П.В.Шумаков Delphi 5. Руководство разработчика баз данных М. Нолидж 2000г. Г.Н.Кальянов CASE структурный системный анализ (автоматизация и применение) М. Лори 1996г. Конст. Корольков Windows for Workgroups 3.1/3.11 М. Бином 1994г. П.Лори Базы данных для микро-ЭВМ М. Машиностроение 1988г. Под ред. В.С.Синяка Автоматизированные системы управления и руководитель М. Финансы и статистика 1983г. Под ред. В.Н.Четверикова Базы и банки данных. Учебник для ВУЗов по специальности АСУ М. Высшая школа 1987г. Игрок Команда Оружие Имя Вид Название Название Относиться Стоимость Концепция Покупать М М N 1