Oracle - MS Server / BD

1.Основные объекты базы данных, управляемой системой SQL

Server.

Таблицы (Tables)

Ключи (Keys)

Индексы (Indexes)

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

Синонимы (Synonyms)

Ограничения (Constraints)

Умолчания (Details)

Триггеры (Triggers)

Функции (User Functions)

Процедуры (Stored Procedures)

2.Трехуровневая модель управления элементами данных в БД.

База данных. Включает все объекты БД. Ее владельцем является конкретный пользователь.

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

Объект, содержащийся в схеме. Владельцем объекта является схема, в которой содержится объект.

3.Средства и объекты управления доступом к данным: логин, пользователь, разрешение, роль.

Логин (Login) – учетная запись, которая используется для подключения к серверу SQLServer.

Типы логинов:

Логин Windows.

Логин SQL Server.

Пользователь БД (User) – специальный объект, который создается на уровне БД и используется для предоставления разрешений в БД (на таблицы, представления, хранимые процедуры).

Встроенные пользователи БД.

dbo (databaseowner) – пользователь-владелец БД. Он автоматически создается для того логина, от имени которого была создана БД.

guest (гость)

INFORMATION_SCHEMA – владелец схемы, в которой хранятся представления системной информации для БД.

sys – владелец схемы sys, к которой принадлежат системные объекты БД.

Разрешения (Permissions) – дают право выполнять определенный тип операторов SQLили право доступа к объекту другого владельца (пользователя).

Команды:

GRANT (предоставить разрешение)

DENY (явно запретить что-то делать)

REVOKE (отменить право предоставлять разрешение (запретить))

Роль – группа из нескольких разрешений, которые могут быть назначены или отменены всей группой одновременно; это специальный объект, который используется для упрощения предоставления разрешений в БД.

Встроенные роли:

public (всем)

db_owner (предоставить полное право на БД)

…

db_datareaderиdb_dataowner – даетправо на просмотр и изменение любой информации в БД.

4.Понятие транзакции, распределенной базы данных и репликации.

Основа обеспечения целостности БД.

Основа изолированности пользователей в многопользовательских системах.

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

При выполнении транзакции возможно два итога:

результаты всех операторов, входящих в транзакцию, соответствующим образом отображаются в БД.

воздействие всех операторов на БД полностью отсутствует.

Распределенная БД – набор логически связанных между собой частей данных, которые одновременно доступны многим пользователям и физически распределены в компьютерной сети.

Сохранность данных и обеспечение необходимой скорости доступа к информации, расположенной в БД, обеспечивается с помощью репликации.

Репликация – предусмотренное размещения компонентов БД (фрагментов) в нескольких узлах сети и поддержание одинакового содержимого всех копий.

Распределенные транзакции – процесс одновременной модификации данных в одном узле сети и передачи измененных данных другим копиям БД с помощью триггеров и процедур с использованием механизма фиксации.

BEGINTRANSACTION – явный способ.

T: запуск сразу после предыдущей – неявный способ.

COMMIT – результаты будут зафиксированы в БД.

ROLLBACK – воздействия транзакции не будет.

5. Обеспечение изолированности пользователей: виды блокировок, протокол доступа к данным.

Обеспечение изолированности пользователей – это создание такого режима работы, чтобы каждому из пользователей казалось, что он работает с БД в одиночку.

Параллелизм транзакций.

Проблема потери результатов обновлений.

Проблема незафиксированности зависимостей.

Проблема несовместимого анализа.

Для решения – блокировка – метод управления параллельными процессами, при котором объект БД не может быть изменен без ведома транзакции.

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

Виды блокировок.

Блокировка чтения.

Блокировка записи.

Протокол доступа к данным.

транзакция, результат действия которой на объект является извлечение (чтение), обязана наложить блокировку чтения на этот объект.

транзакция, предназначенная для модификации объекта, обязана наложить блокировку записи на данный объект.

в случае, если запрашиваемая блокировка на объект отвергается из-за того, что на объект уже наложена блокировка, то транзакция переводится в режим ожидания до тех пор, пока блокировка не будет снята.

блокировка на запись сохраняется вплоть до завершения транзакции, т.е. до выполнения операторов

COMMITили ROLLBACK.

8. Архитектура базы данных в SQL Server: элементы логического и физического уровней.

Архитектура – основные модели представления данных, используемые в соответствующей СУБД, а также взаимосвязи между этими моделями.

Уровни абстракции архитектуры БД.

Логический – соответствует даталогической модели, принятой СУБД.

Физический – отражает внутреннее представление данных в памяти компьютера – физ. модель БД.

Логический уровень – таблицы, типы данных, представления и т.д.

Физический уровень – группа файлов, файлы данных со страничной организацией, файлы журнала транзакций.

Архитектура на логическом уровне.

Таблицы и типы данных.

Ключи.

Индексы.

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

Ограничения.

Умолчания.

Архитектура на физическом уровне.

Файл.

Единица обмена между внешней и оперативной памятью. Отдельные файлы используются только одной БД.

9. Файловые группы, типы файлов, страницы, экстенты, типы экстентов.

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

Типы файлов.

первичные файлы данных (.mdf)

вторичные файлы данных (.ndf)

файлы журналов(.ldf)

Файлы журналов содержат сведения для восстановления БД. В каждой БД – не менее одного журнала.

Файлы журналов не могут входить в состав файловых групп.

Информация о расположении всех файлов БД записывается в первичный файл БД и в служебную структуру СУБД SQLServer, называемую БДmaster.

SQLServerDataEngine (машинаБД) использует эту информацию для работы с БД.

Размер файла.

1.Можно указать максимальный размер каждого файла.

2.Если максимальный размер не указан, файлы могут автоматически увеличиваться, превосходя первоначальные заданные показания, пока не займут все доступное место на диске.

3.Для файла можно указать требуемую скорость. Каждый раз при заполнении файла его размер увеличится на указанный шаг роста.

4.Если в файловой группе существует несколько файлов, их автоматический рост начинается после заполнения всех файлов. Затем файлы увеличивают размеры в кольцевом порядке.

Страницы – основные единицы хранения данных и обмена информацией между внешней и оперативной памятью. Размер – 8Кб.

Место на диске для размещения файла данных (.mdf, .ndf) в БД, логически разделяется на страницы, пронумерованных от 0 до n.

Дисковые операции ввода-вывода выполняются на уровне страницы.

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

Номер страницы.

Тип страницы.

Объем свободного места на странице.

Идентификатор единицы распределения объекта, который принадлежит странице.

8 типов страниц.

1.Данные с типом небольшого размера.

2.Данные с типом большого размера.

3.Записи индекса.

4.Сведения о размещении страниц и доступном на них свободном месте.

5.…

Экстенты.

Для эффективного управления памятью страницы объединяются в экстенты, которые являются основными единицами организации пространства.

Экстент – коллекция, состоящая из 8 физически непрерывных следующих друг за другом страниц (64Кбайт). Все страницы хранятся в экстентах.

Чтобы сделать распределение места эффективным, СУБД не выделяет целые экстенты для таблиц с небольшими данными.

1.Однородные экстенты. Принадлежат объекту. Все 8 страниц могут быть использованы только владеющим объектом.

2.Смешанные экстенты. Могут находиться в общем пользовании у не более чем 8 объектов. Каждая из 8 страниц находится во владении разных объектов.

10. Многосекционное хранение таблиц и индексов.

Таблицы (индексы) хранятся в виде набора страниц 8Кб, которые содержатся в одной или нескольких секциях.

Секция – пользовательская единица организации данных.

По умолчанию таблица (индекс) имеет единственную секцию, которая содержит все страницы таблицы (индекса).

Каждая секция – в одной файловой группе.

Если таблица (индекс) используют несколько секций, данные секционируются горизонтально так, что группы строк сопоставляют отдельным секциям, основанных на значениях данных в уникальном столбце. Разные секции хранятся в одной или нескольких файловых группах в БД.

Секция состоит из фрагментов одного или нескольких файлов.

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

IN_ROW_DATA (данные с типами небольших размеров)

LOB_DATA (данные с типами больших размеров)

ROW_OWERFLOW_DATA (данные переменной длины – переполнение строки)

Каждая секция содержит строки данных либо в куче, либо в структуре кластеризованного индекса.

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

Кластеризованный индекс – сбалансированное дерево, которое поддерживает быстрый поиск строк по ключу. Страницы в каждом уровне связаны в двунаправленный индекс.

Управление работой с экстентами и свободным местом. Сведения о свободном месте плотно упакованы.

небольшое количество страниц.

увеличение скорости из-за уменьшения операций чтений диска для получения сведений об экстенте.

увеличение вероятности, что сведения будут оставаться в памяти и повторной операции чтения не потребуется.

2 типа карт.

Глобальная карта распределения (GlobalAllocationMap (GAM))

Общая глобальная карта распределения (SharedGlobalAllocationMap (SGAM)).

ВGAM указано: какие экстенты были задействованы. В каждой карте GAM содержатся сведения об использовании 64000 экстентов или о размещении почти 4ГБ данных по 1 биту на каждый экстент. Если бит=1, то экстент свободен.

Если бит=0, то экстент задействован.

ВSGAM: какие используются в качестве смешанных экстентов и имеют как минимум одну не использованную страницу.

Вкаждой карте SGAM – сведения об использовании 64000 экстентов или о 4ГБ данных.

По 1 биту на экстент.

Бит=1 – смешанный экстент, 1 свободная страница.

Бит=0 – не используется как смешанный экстент или все страницы используются. Для освобождения экстента устанавливаем бит GAM=1, а соотв. SGAM=0.

Отслеживание свободного места.

PFS (PageFreeSpace) – записывается состояние каждой страницы, информация о том, была ли отдельная страница использована или нет, а так же кол-во свободного места на каждой странице.

В PFSна каждую страницу – 1 байт, хранящий информацию о том, была ли использована страница или нет. Если была, то – пустая она или ее заполнение находится в промежутке от 1% до 50%, от 51% до 80%, от 80%

до 95%, от 96% до 100%.

После размещения в экстент используется PFS-страница для записи информации о том, какие используются, какие нет.

Эти сведения используются при выборе новой страницы для размещения.

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

Заголовок – страница 0. PFS – страница 1. GAM–страница 2. SGAM– страница 3.

и т.д.

Файлы журналов хранят в себе последовательность операций, а не страницы данных.

Для указания порядка, в котором операции выполняются в БД, каждая запись в журнале транзакций содержит порядковый номер LSN.

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

Это обеспечивается возможностью отката транзакции.

Логически, журнал транзакций состоит из виртуальных логов virtuallog, заполнение которых происходит в кольцевом порядке.

11. Взаимодействие между дисковой и оперативной памятью, основные состояния буферов оперативной памяти.

Области памяти. BPool (пул буферов)

MemToLeave(зарезервированная память)

Физическая память.

BPool – основной пул распределения памяти. MemToLeave – пространство виртуальной памяти.

Физическая память – расширение области BPool, предоставляющей дополнительное пространство для страниц данных и страниц индексов.

Область BPool – состоит из отдельных областей памяти, организованных в виде буферов с объемом 8 КБ.

1.СУБД вычисляет максимальный размер области BPool.

2.СУБД резервирует область MemToLeave.

3.СУБД предпринимает попытку зарезервировать память для области BPool.

4.СУБД выполняет операцию резервирования, задавая каждый раз все меньший объем в запросе на резервирование.

5.СУБД освобождает память MemToLeave.

Хэширование.

СУБД SQLServerхэширует страницы в области BPool.

На основе сведений о буферах в области BPool создается хэш-таблица, с помощью которой СУБД может быстро определить по данным об идентификаторе базы данных, номер файла и номер страницы данных, содержится ли в области BPool искомая страница данных и где она находится.

Операции распределения.

Необходимо распределить вспомогательные структуры, которые требуются для управления областью BPool.

1.Глобальная переменная BufferPool, предназначенная для хранения ссылки на экземпляр класса, определяющего область BPool.

2.Массив структур BUF.

Каждый буфер области BPool имеет соотв. ему структуру BUFразмером 64 байта.

Структура BUF содержит:

1.Указатель на фактическую страницу, размещенной в области BPool.

2.Кол-во ссылок на эту страницу.

3.Защелку страницы.

4.Биты статуса.

Сброс на диск и освобождение буфера.

В массиве BUF содержится по одному экземпляру для каждого буфера в области BPool. Каждая структура BUF содержит данные о кол-ве ссылок на соотв. структурную страницу.

После формирования каждой новой ссылки на странице указанное кол-во ссылок увеличивается.

Массив BUFпериодически просматривается. Кол-во ссылок, написанное в каждой структуре BUF делится на 4, остаток отбрасывается.

Когда кол-во ссылок на странице достигает 0, страница проверяется для определения того, является ли она незафиксированной («грязной»). В случае положительного ответа планируется операция записи для сброса незафиксированной страницы из буфера в диск.

Если кол-во ссылок на странице достигает 0, а сама страница не является «грязной», то она просто освобождается, т.е. перемещается в список доступных страниц без записи каких-либо данных на диск.

После того, как незафикс. страница успешно записывается на диск, для нее отменяется хэширование (она удаляется из хэш-таблицы), а буфер, занятый этой страницей, добавляется к списку свободных буферов.

16. Обеспечение безопасности при работе с базой данных: создание логина, создание пользователя, создание схемы, предоставление пользователю разрешений.

1.Создаем логин для подключения к СУБД SQLServer.

2.Создаем пользователя БД, которому соответствует логин.

3.Предоставить пользователю необходимые разрешения.

Логин (учетная запись) Типы.

1.логин Windows

2.логин SQL Server

CREATE LOGIN Vera

WITH PASSWORD=’1234’,

DEFAULT_DATABASE=newDB,

CHECK_POLICY=OFF;

ALTER LOGIN Vera WITH PASSWORD=’newPaasss’;

DROPLOGINVera

Пользователь БД. Встроенныепользователи. dbo(databaseowner)

guest INFORMATION_SCHEMA sys

ALTERUSER

DROPUSER

USE newDB;

CREATE USER Vera FOR LOGIN Vera;

Нельзя удалить пользователя, если у него во владении объект. Нужно отдать объект другому пользователю.

sysadmin – неограниченные права в пределах

Administrators

Область видимости –scope Право доступа (разрешения)

GRANT SELECT

ON PRODUCTS TO IVAN

Иерархия объектов – иерархия областей видимости. Сервер

База данных Схема

GRANT – дает

REVOKE – отменяет разрешение

DENY – не дает право доступа к объекту

CREATE PROCEDURE GetStock

WITH EXECUTE AS ‘IVAN’

AS

SELECT * FROM Stock

GRANT EXECUTE ON dbo.GetStock TO Vera