НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ УНИВЕРСИТЕТ
Институт № 3 МАИ
Системы управления, информатики и электроэнергетики
Кафедра 305
«Автоматизированные комплексы систем ориентации и навигации»
Методическое пособие по курсу:
«УИРС»
SQL Server 2012/2014
Москва 2018
Оглавление
1 Цель работы.. - 4 -
2 Содержание отчета. - 4 -
3 Установка SQL Server 2012. - 5 -
4 Многопользовательские базы данных. - 19 -
5 Создание баз данных. - 24 -
5.1 Добавление нового пользователя. - 24 -
5.2 Этапы проектирования базы данных. - 27 -
5.3 Создание новой базы данных. - 27 -
5.4 Установка свойств базы данных. - 31 -
5.5 Управление базами данных. - 32 -
5.6 Удаление базы данных. - 32 -
5.7 Построение базы данных. - 33 -
5.8 Типы данных. - 35 -
5.9 Создание новой таблицы.. - 37 -
6 Добавление новых записей таблиц. - 46 -
6.1 Создание запросов. - 47 -
6.2 Создание диаграммы базы данных. - 48 -
6.3 Система безопасности и защиты данных в SQL Server 2012 with Tools. - 49 -
6.4 Тип подключения к SQL Server. - 49 -
6.5 Пользователи и их права доступа. - 50 -
6.6 Создание учетных записей. - 55 -
6.7 Настройка регулярного резервного копирования БД MS SQL Server. - 58 -
7 Подключение БД SQL Server 2012 к ACCESS 2010. - 69 -
7.1 Отображение элементов в проекте. - 72 -
Cписок использованных источников. - 73 -
Цель работы
Цель работы: ознакомление с принципами работы многопользовательских баз данных, изучение метода создания таблиц и связей между ними в реляционной СУБД MS SQL Server 2012 with Tools .
Задание на практическое занятие:
1. Ознакомиться с предлагаемым материалом по оценке преимуществ использования многопользовательских БД.
2. Изучить предлагаемый материал по установке и принципам работы SQL Server 2012 with Tools. При необходимости, возникшие при изучении материала вопросы задать преподавателю.
3. Создать свою базу данных в MS SQL Server 2012 with Tools, руководствуясь методическим материалом. Внимание! При создании имени БД необходимо использовать кодировку следующего вида, обеспечивающую различные имена: Project _ GrSt. Здесь Project ёопределяет смысловое название БД, Gr - сокращенный номер группы, St - номер студента в списке группы. Т.е. GrSt - это код студента-разработчика. Например, для студента из группы 03-408, имеющего в списке группы номер 11, название БД должно быть Project 811.
4. Создать таблицы в своей базе данных SQL Server 2012 with Tools, руководствуясь методическим материалом.
5. Внести данные в таблицы. Рекомендация: внести 3 - 4 записи в каждую таблицу. Данные лучше вводить после создания схемы данных.
6. Создать диаграмму базы данных, руководствуясь методическим материалом.
7. Присоединить свою базу данных к ACCESS 2010 для дальнейшей работы с клиентским приложением.
Содержание отчета
1. Обоснование целесообразности перехода от разработки БД посредством Access к БД SQL Server 2012 with Tools.
2. Пример таблицы в режиме конструктора (с описанием свойств полей).
3. Пример заполненной таблицы
4. Диаграмма (схему данных) БД (с объяснением причин наличия или отсутствия каскадных связей).
5. Демонстрация преподавателю своего варианта таблиц и диаграммы БД на компьютере, а также подтверждение возможности каскадных операций.
Установка SQL Server 2012
Примечание.
В начале работы с SQL Server 2012 Вам необходимо будет скачать его с официального сайта https://www.dreamspark.com/Default.aspx. Этот сайт характеризуется тем, что в период обучения любой студент может скачать лицензионные продукты Microsoft, предварительно необходимо зарегистрироваться, создать свою учебную запись.
Что нужно для того, что бы зарегистрироваться в Dreamspark .
1. Для начала зайдите на сайт dreamspark.com.
2. Выберите закладку вверху «Учащиеся».
3. Перейдите внизу страницы по ссылке «Создать учетную запись».
4. Заполняете учетные данные. Заполняйте так, как указано в студенческом билете, т.к. далее это понадобится. Потом, «Продолжение проверки».
5. Далее, выбираем слева вариант «Я могу предоставить документы».
6. Далее, указываем нужную информацию, прикрепляем фотографию студенческого билета, отправляем.
7. В течение нескольких дней вам на почту придёт уведомление, что вы можете скачивать бесплатно ПО от Microsoft. Скачивать можно на том же сайте.
Итак, когда Мы уже скачали SQL Server 2012 c DreamSpark переходим к установке:
· Запускаем файл «setup.exe» с установочного диска.
· Откроется «Центр установки SQL Server» (SQL Server Installation Center), переходим на вкладку «Установка» (Installation) и нажимаем на «Новая установка изолированного экземпляра SQL Server или добавление компонентов к существующей установке» (New SQL Server stand-alone installation or add features to an existing installation).
· Запустится «Программа установки SQL Server 2012» (SQL Server 2012 Setup). Убедимся что нет проблем, которые могут возникнуть во время установки файлов поддержки программы установки и нажмем «ОК». Если все же возникли какие-либо проблемы, необходимо устранить их и повторить процедуру проверки нажав «Включить заново» (Re-run).
· Теперь необходимо ввести ключ продукта (если он у вас есть) или выбрать свободный выпуск SQL Server. Это может быть:
o Evaluation Edition — полный набор компонентов, с ограничением использования в 180 дней.
o Express Edition — бесплатная версия с ограниченным функционалом.
Ключ продукта можно будет ввести в любое время, после установки SQL Server. О том как это сделать читайте в статье Изменение версии или ввод ключа Microsoft SQL Server 2012. После выбора параметров нажимаем «Далее» (Next).
· Принимаем условия лицензионного соглашения, соглашаемся (или нет) на передачу данных об использовании компонентов в корпорацию Microsoft и жмем «Далее» (Next).
· Если имеются обновления программы установки, включаем обновление продукта, нажав «Включить обновления продукта SQL Server» (Include SQL Server product updates) и нажимаем «Далее» (Next).
· Дожидаемся окончания загрузки обновлений и установки файлов установки SQL Server.
· Вот и закончился подготовительный этап, и мы приступаем к непосредственной установке и выбору параметров SQL Server. Убеждаемся что нет проблем, которые могут возникнуть во время установки и нажмем «Далее» (Next). Если же все имеются какие-либо проблемы, необходимо устранить их и повторить процедуру проверки нажав кнопку «Включить заново» (Re-run).
· Вариант установки выбираем «Установка компонентов SQL Server» (SQL Server Feature Installaion) и нажимаем «Далее» (Next).
· Теперь выбираем компоненты для установки. Для большинства случаев (например для работы и администрирования сервера 1С:Предприятие), вполне достаточно установки компонент:
o Службы компонента Database Engine (Database Engine Services)
o Репликация SQL Server (SQL Server Replication)
o Полнотекстовый и семантический поиск (Full-Text and Semantic Extractions for Search)
o Служба Data Quality Services (Data Quality Services)
o Средства управления — основные (Management Tools — Basic)
o Средства управления — полный набор (Management Tools — Complete)
Все компоненты всегда можно доставить / удалить, просто заново запустив установку SQL Server. Выбрав необходимые компоненты, нажимаем «Далее» (Next).
· Опять убеждаемся, что процесс установки не будет заблокирован, исправляем проблемы в противном случае и нажимаем «Далее» (Next).
· Теперь необходимо ввести имя именованного экземпляра SQL Server (рекомендую) или оставить имя экземпляра по умолчанию. Определившись с именем (Named instance) и идентификатором (Instance ID) экземпляра SQL Server нажимаем «Далее» (Next).
· Убедимся, что имеется достаточно свободного пространства для установки выбранных компонент SQL Server и снова нажмем «Далее» (Next).
· Теперь выберем режим запуска служб SQL Server. Поменяем параметр запуска Агента SQL Server (SQL Server Agent) на «Авто» (Automatic) (для того чтобы отрабатывали регламентированные задания агента SQL) и перейдем на вкладку «Параметры сортировки» (Collation).
· Здесь необходимо определиться с параметрами сортировки баз данных.
Это очень важно, данный параметр нельзя будет изменить позднее!!!
Для большинства программ, которые будут работать с SQL сервером (например для систем1С:Предприятие) необходимо выбрать «Cyrillic_General_CI_AS». Указав параметры сортировки нажимаем «Далее» (Next).
· Теперь следует выбрать режим проверки подлинности (Authentication Mode). Я рекомендую выбрать «Смешанный режим» (Mixed Mode), ввести пароль на встроенную учетную запись «sa», а также добавить администраторов SQL Server из числа имеющихся пользователей Windows с помощью кнопок «Добавить текущего пользователя» (Add Current User) и «Добавить…» (Add..). Установив все параметры переходим на вкладку «Каталоги данных» (Data Directories).
· Здесь можно выбрать каталог хранения баз данных (Data root directory). Если в конфигурации сервера присутствует RAID-массив из жестких дисков или SSD-диски, разумно расположить файлы данных именно на нем. Указав необходимый путь, жмем «Далее» (Next).
· Принимаем решение об отправке (или не отправке) отчетов об ошибках в компанию Microsoft и снова жмем «Далее» (Next).
· В очередной раз убеждаемся что процесс установки не будет заблокирован, исправляем ошибки, если они имеют место быть, и нажимаем «Далее» (Next).
· Проверяем все введенные на предыдущих шагах параметры и нажимаем «Установить» (Install), для запуска процесса установки SQL Server.
· Установка может быть довольно продолжительной по времени. Дожидаемся ее окончания. 
· Затем убеждаемся, что все компоненты установлены успешно и завершаем работу мастера нажав на кнопку «Закрыть» (Close).
· Установка Microsoft SQL Server 2012 завершена.
· Для администрирования (добавления баз данных, пользователей и пр.) используется программа «SQL Server Management Studio» («Пуск» - «Microsoft SQL Server 2012» - «Среда SQL Server Management Studio»).
Создание баз данных
После установки SQL Server запустит все необходимые службы автоматически. Поэтому вручную запускать ничего не надо.
Для запуска Management Studio перейдите в меню ПУСК – все программы – Microsoft SQL Server 2012 - Среда Microsoft SQL Management Studio .
Для каждой логической базы данных SQL Server создает два физических файла: один для объектов, а другой для журнала транзакций (операций).
Примечание. По умолчанию SQL Server подразумевает одинаковое местонахождение для файла базы данных и журнала транзакций. В рабочих системах вам следует всегда хранить журнал транзакций в другом месте, желательно, на другой машине. Это позволит восстановить базу данных в случае отказа аппаратных средств, в результате которого чтение диска станет невозможным. Рекомендация для сохранения в целостности ваших данных, каждый раз, завершая работу со своей базой данных не поленитесь скопировать файлы журнала и метаданных на съемный носитель(например флешка - USB Flash Drive). Это убережет Вас от многочасового труда по восстановлению БД, в случае аппаратного сбоя.
Создание новой базы данных
Новая база данных (New Database) создается путем вызова контекстного меню в программе Microsoft SQL Server Management Studio Express, вид которой показан на рис. 20. Оно вызывается нажатием правой кнопки мыши (ПКМ) в разделе «Обозреватель объектов» на папке «Базы данных» рис. 6
Рис. 6. Контекстного меню Microsoft SQL Server Management Studio
Рис. 6. Раздел «Обозреватель объектов»
После выбора «Создание базы данных» программа отобразит страницу, запрашивающую имя и местонахождение файлов, создаваемой БД рис. 7. На этой странице также есть возможность выбора начального размера, создаваемой БД, параметров авторасширения для обоих создаваемых файлов, которое вызывается путем нажатия на кнопку «многоточие» напротив имени файла. Местонахождение файлов выбирается также путем нажатия на кнопку «многоточие»
Рис. 7. Создание базы данных
Введите имя вашей базы данных в поле «Имя базы данных». Имя Базы данных должно быть уникально и выглядеть так: Project (Проект) + Номер группы + Номер студента в списке группы. Пример для студента под номером 11 в группе 03-408: Project 811. Далее нажав на кнопку «многоточие 
» напротив имения файла для выбора места его размещения отобразится дерево каталогов как показано на рис. 8. В учебных целях примем параметры создания по умолчанию, так как они подходят для реализации учебной БД. После нажатия на кнопку OK будет происходить создание БД, время выполнения этого процесса зависит от мощности каждого конкретного компьютера, но обычно не занимает много времени. После успешного завершения создания БД щелкните левой кнопкой мыши (ЛКМ) в окне «обозреватель объектов» на папке «база данных» увидите только что созданную вами
БД (Рис. 8)
Рис. 8. Обозреватель объектов
После создание БД нажав на ней ПКМ и выбрав из меню «свойства» Вы можете изменить свойства созданной ранее базы данных (Рис. 9)
Рис. 9. Свойства базы данных
Управление базами данных
С помощью Management Studio вы можете изменять свойства базы данных, а также удалять их.
Удаление базы данных
Если база данных, которую вы создали, вам больше не нужна, вы можете удалить ее с сервера. При удалении базы данных удаляются все физические файлы, связанные с базой данных, и все ссылки на базу данных в системных таблицах.
1. Выделите свою базу данных в «Обозревателе объектов»
2. Нажмите кнопку | Delete |. SQL Server отобразит сообщение, запрашивающее у вас подтверждение удаления (Рис. 11).
Рис. 11. Удаление объектов
- Нажмите ОК (Да). SQL Server удалит базу данных и все связанные с ней ссылки с сервера.
Построение базы данных
Таблицы представляют собой основные единицы хранения данных в реляционной базе данных. Наиболее распространенный метод описания баз данных использует концепцию сущностей (entities) и атрибутов (attributes). Когда вы переходите от логического конструирования к физической реализации, сущности реализуются в виде таблиц, а атрибуты — в виде столбцов (они также называются полями)[3].
База данных, создание которой и является целью нашего практического занятия, состоит, как и прежде, из следующих 7 сущностей:
- Группа
- Кафедра
- Предмет
- Студент
- Преподаватель
- Изучение
- Успеваемость
В названия всех сущностей и атрибутов, как и прежде, входит идентификационный номер студента (номер группы, порядковый номер студента по списку). Ход выполнения практического занятия будем рассматривать на примере студента, учащегося в 8 группе и имеющего порядковый номер по списку 11, т.е. идентификационный номер студента 811. В отличие от работ, выполненных чисто на Access , внесем некоторые изменения в состав используемых таблиц: дополним таблицы Группа, Студент, Изучение и Успеваемость идентификационными полями с целью дальнейшего использования этих полей в качестве первичных ключей. Это делается, чтобы избежать использования составных ключей, усложняющих и замедляющих работу создаваемой базы данных.
ГРУППА811 ( G_ID811 , НГ811 , Кол811, Пбалл811)
СТУДЕНТ811 ( ID_ ST811 , G _ ID 811, НС811, ФИО811, Годр811, Адрес811, ПрохБалл811)
ИЗУЧЕНИЕ811 ( ID811 , G _ ID 811, КП811,Табн811, Видз811, ЧАСЫ811, СбаллГр811)
УСПЕВАЕМОСТЬ811 ( ID811 , ID ST811 , Оценка811, ДатаСдачи 811)
Таблицы Кафедра, Предмет и Преподаватель оставим без изменений.
КАФЕДРА811 (Ккаф811, Нкаф811, Тел811, Зав811, Фото811)
ПРЕДМЕТ811 (КП811, НП811, Часы811, Лек811, Пр811, ЧС811, Прогр811)
ПРЕПОДАВАТЕЛЬ811 (Табн811, ФИО811, Ст811, Зв811, Ккаф811)
Типы данных
Каждый столбец в таблице имеет определенные свойства, которые описывают его в SQL Server. Наиболее важное свойство – тип данных, который является описанием типа информации, которая будет храниться в столбце. SQL Server предоставляет обширный перечень типов данных, представленных в таблице 1.
Таблица 1. Типы данных в SQL Server 2012 with Tools
| Тип данных | Допустимые значения |
| Bigint | Целые числа от -2^63 до 2^63-1 |
| Int | Целые числа от -2^31 до 2^31-1 |
| Тип данных | Допустимые значения |
| Smallint | Целые числа от -2^15 до 2^15-1 |
| Tinyint | Целые числа от 0 до 255 |
| Bit | Целые числа, принимающие значения 0 или 1 |
| Decimal | Десятичные числа с фиксированным количеством знаков до и после запятой в диапазоне от -10^38+1 до 10^38-1. (Десятичные числа decimal также могут быть описаны как «numeric»; диапазон значений при этом одинаковый) |
| Money | Числа в денежном формате в диапазоне от -2^63 до 2^63-1. (Значения в денежном формате имеют точность, равную одной десятитысячной (.0001) от денежной единицы) |
| Smailmoney | Числа в денежном формате в диапазоне от -214748,3648 до 214748,3647 (Точность этих чисел также равна одной десятитысячной (.0001) от денежной единицы) |
| Float | Числа с плавающей запятой в диапазоне от -1.79Е+308 до 1.79Е+308 (Значения являются лишь приблизительными) |
| real | Числа с плавающей запятой в диапазоне от -3.40Е+38 до 3.40Е+38 (Значения являются лишь приблизительными) |
| Значения даты и времени | |
| datetime | Значения даты и времени начиная с 1 января 1753 г. До 31 декабря 9999 г. (Значения даты и времени имеют точность, равную трем сотым секунды или 3,33 мс.) |
| smalldatetime | Значения даты и времени начиная с 1 января 1900 г. До 6 июня 2079 г. (Точность значений составляет 1 мин.) |
| Символьные значения | |
| char | Символьные значения (не Unicode) фиксированной длины, максимально 8000 символов |
| varchar | Символьные значения (не Unicode) переменной длины, максимальная длина составляет 8000 символов |
| Text | Данные (не Unicode) переменной длины, максимальная длина составляет 2^31-1 (2147483647) символов |
| Nchar | Данные (Unicode) фиксированной длины, максимальная длина составляет 4000 символов |
| nvarchar | Данные (Unicode) переменной длины, длина составляет 4000 символов |
| Тип данных | Допустимые значения |
| ntext | Данные (Unicode) переменной длины, максимальная длина составляет 2^30-1 (1073741823) символов |
| Двоичные (бинарные) значения | |
| binary | Двоичные данные фиксированной длины, максимальная длина составляет 8000 байт |
| Varbinary | Двоичные данные переменной длины, максимальная длина составляет 8000 байт |
| image | Двоичные данные переменной длины, максимальная длина составляет 2^31-1 (2147483647) |
| Другие значения | |
| cursor | Ссылка на курсор (Курсор представляет собой сущность, которая устанавливает ссылку на определенную строку в результирующем наборе) |
| rowversion | Уникальный используемый для всей базы данных номер, который обновляется каждый раз при модификации строки |
| sql_variant | Значения любого типа, за исключением text, ntext, rowversion и sql_variant |
| uniquedentifier | Глобальный уникальный идентификатор (GUID) |
Таблица 2. Соответствие типов данных Microsoft Access и Microsoft SQL
| № | Тип данных Microsoft Access | Тип данных Microsoft SQL | Описание типа данных Microsoft SQL |
| 1 | Текстовый | Nvarchar | Тип данных для хранения текста до 4000 символов |
| 2 | Поле МЕМО | Ntext | Тип данных для хранения символов в кодировке Unicode до 1 073 741 823 символов |
| 3 | Числовой | Int | Численные значения (целые) в диапазоне от -2 147 483 648 до +2 147 483 647 |
| 4 | Дата/время | smalldatetime | Дата и время от 1 января 1900 г. до 6 июня 2079 года с точностью до одной минуты |
| 5 | Денежный | Money | Денежный тип данных, значения которого лежат в диапазоне от -922 337 203 685 477.5808 до +922 337 203 685 477.5807, с точностью до одной десятитысячной |
| 6 | Счетчик | Int | См. пункт 3 |
| 7 | Логический | Bit | Переменная, способная принимать только два значения - 0 или 1 |
| 8 | Поле объекта OLE | image | Переменная для хранения массива байтов от 0 до 2 147 483 647 байт |
| 9 | Гиперссылка | ntext | См. пункт 2 |
| 10 | Мастер подстановок | nvarchar | См. пункт 1 |
Создание новой таблицы
Таблицы создаются и обслуживаются с помощью конструктора таблиц Table Designer SQL Server Manager Studio. Первым шагом является создание и присвоение имени таблицы путем открытия конструктора таблиц Table Designer для новой таблицы.
Примечание.
Описание столбца
Ряд расширенных свойств, таких как описание столбца, были созданы Microsoft как составная часть стандартной установки сервера, и вы можете создавать дополнительные расширенные свойства для хранения специфичной для приложения или для сайта информации об объектах базы данных.
Каждое расширенное свойство обладает задаваемым пользователем именем и значением. Значение расширенного свойства имеет тип sql_variant и может содержать до 7500 байт данных. Вы можете определить несколько расширенных свойств для любого объекта с использованием хранимых процедур.
Точность и масштаб
Использование полей точность (precision) и масштаб (scale) становится возможным при выборе типа данных Decimal.
Точность числового значения представляет собой максимальное количество десятичных разрядов, которые представляют значение как слева, так и справа от десятичной точки. Масштаб значения представляет собой количество знаков справа от десятичной точки. Например, значение 3647,311 имеет точность 7 (общее количество знаков) и масштаб 3 (количество знаков справа от десятичной точки).
Важно понимать, что точность и масштаб числового значения не влияют на длину столбца. Длина столбца определяется типом данных. Точность и масштаб указывают, как SQL Server будет интерпретировать данные, хранящиеся в столбце.
Нулевое значение ( Null )
Значение Null представляет собой особый вид значений в реляционной технологии, которое используется для указания, что значение либо отсутствует (т.е. неизвестно), либо не существует. Использование значений типа Null в ряде случаев является сомнительным и спорным.
Определение первичного ключа. Для этого наведите мышкой на столбец который хотите сделать первичным ключом (Primary Key). Щелкните на нем ПКМ и выберите «Задать первичный ключ» (Рис. 20)
Рис. 20. Контекстное меню
6. Для примера рассмотрим добавление следующего столбца НГ811 в таблицу Группа811 Для него в поле Тип данных установим тип данных text; в поле Разрешить значение NULL флажок, выставленный по умолчанию можно оставить. В поле Description (Описание) введем описание столбца Номер группы. (Рис. 21)
Рис. 21. Значение Null для НГ811
Аналогично предыдущему создайте еще 2 столбца ПБАЛЛ811 и КОЛ811
Окончательный вид таблицы группа должен быть следующим (Рис. 22)
Рис. 22. Значение Null для ПБАЛЛ811
Управление таблицами
Хотя «принципы разработки» требуют, чтобы структура вашей базы данных была окончательно определена до начала реализации, на практике это не всегда удается осуществить. SQL Server облегчает выполнение задач по сопровождению баз данных.
Изменение столбцов
Вы можете повторно открыть конструктор таблиц Table Designer для таблицы, щелкнув правой кнопкой мыши на имени таблицы в рабочей панели и выбрав Проект из контекстного меню. После того как конструктор таблиц будет открыт, вы можете изменить свойства имеющихся столбцов, удалять их или добавлять новые.
Удаление таблицы
Удаление таблицы происходит точно также как и удаление БД, путем нажатие клавиши DELETE или выбора из контекстного меню «Удалить», а затем подтверждение
Примечание. Когда вы удаляете таблицу, таблица и все ее данные окончательно убираются из базы данных. Единственный способ восстановить ее – это воспользоваться резервной копией.
По описанному руководству создайте следующие таблицы:
1. КАФЕДРА811 (Рис. 23)
Ккаф811(Код кафедры) – первичный ключ
Нкаф811(Название кафедры)
Тел811(Телефон кафедры)
Зав811(Заведующий кафедры)
Фото811(Фотография заведующего кафедры)
Рис. 23. Таблица Кафедра811
2. ПРЕДМЕТ811 (Рис. 24)
КП811(Код предмета) – первичный ключ
НП811(Название предмета)
Часы811(Кол-во часов по предмету)
Лек811(Часы, отведенные для лекций по предмету)
Пр811(Часы, отведенные для практики по предмету)
ЧС811(Число семестров)
Прогр811(Описание программы предмета)
Рис. 24. Таблица Предмет811
3. СТУДЕНТ811 (Рис. 25)
ID_ ST811 (номер зачетки) – первичный ключ. Индексируемое поле
G _ ID 811(Идентификатор группы)
НС811(Номер студента по списку)
ФИО811(ФИО студента)
Годр811(Год рождения)
Адрес811(Адрес студента)
ПрохБалл811(Проходной балл студента)
Рис. 25. Таблица Студент811
4. ПРЕПОДАВАТЕЛЬ811 (Рис. 26)
Табн811(Табельный номер преподавателя) – первичный ключ
ФИО811(ФИО преподавателя)
Ст811(Ученая степень преподавателя)
Зв811(Ученое звание преподавателя)
Ккаф811(Код кафедры)
Рис. 26. Таблица Преподаватель811
5. ИЗУЧЕНИЕ811 (Рис. 27)
ID811(Идентификатор изучения) – первичный ключ. Индексируемое поле
G _ ID 811(Идентификатор группы)
КП811(Код предмета)
Табн811(Табельный номер преподавателя)
Видз811(Вид занятий)
ЧАСЫ811(Часы)
СбаллГр811(Средний балл группы)
Рис. 27. Таблица Преподаватель811
6. УСПЕВАЕМОСТЬ811 (Рис. 28)
ID811 , ID ST811 – первичный ключ
Оценка811(Оценка)
ДатаСдачи 811(Дата сдачи)
Рис.28. Таблица Успеваемость811
Создание запросов
Чтобы создать запрос вам нужно запустите Microsoft SQL Server Management Studio, далее, просто нажать кнопку New Querty (Новый запрос) на панеле инструментов.
Рис. 31. Создание запросов
В окне SQL запроса в верхнем полу пишется текст запроса, а в нижнем - выводится таблица результата выполнения запроса. Чтобы запустить запрос на выполнение нужно нажать кнопку Execute(Выполнить код SQL) на панели инструментов.
Рис. 32. Вывод запроса
Роли уровня сервера
С помощью этих ролей предоставляются различные степени доступа к операциям и задачам сервера. Если отдельному пользователю назначена некоторая роль, то он может выполнять все функции разрешенные этой ролью.
Роли уровня сервера заранее определены и действуют в пределах сервера. Они не зависят от конкретных баз данных, и модифицировать их нельзя.
В таблице 3 описаны различные типы ролей уровня сервера.
Таблица 3. Роли уровня сервера
| Роль | Описание |
| sisadmin | Возможность выполнять любое действие в SQL Server |
| serveradmin | Возможность изменить параметры SQL Server и завершить его работу |
| setupadmin | Возможность инсталлировать систему репликации и управлять выполнением расширенных хранимых процедур |
| securityadmin | Возможность контролировать параметры учетных записей для подключения к серверу и предоставлять права доступа к базам данных |
| processadmin | Возможность управлять ходом выполнения процессов в SQL Server |
| dbcreator | Возможность создавать и модифицировать базы данных |
| diskadmin | Возможность управлять файлами баз данных на диске |
| bulkadmin | Возможность выполнять операции bulk и insert |
Роли уровня базы данных
Роли этого типа позволяют назначить набор прав доступа для работы с конкретной базой данных отдельному пользователю или группе. Роли уровня базы данных можно назначать учетным записям пользователей в режиме аутентификации Windows NT или SQL Server. При использовании режима аутентификации Windows NT роли уровня базы данных можно назначать отдельным пользователям системы Windows NT или целым NT-группам. Роли могут быть и вложенными, так что учетным записям можно назначить иерархическую группу прав доступа.
Роли уровня баз данных зависят от конкретных баз данных. В SQL Server существует два типа таких ролей.
Заранее определенные роли
Это стандартные роли уровня базы данных SQL Server. Эти роли имеет каждая база данных SQL Server.Они позволяют легко и просто передавать обязанности.
Заранее определенные роли зависят от конкретной базы данных и не могут быть изменены. В таблице 4 перечислены стандартные роли уровня базы данных и дано описание каждой из них.
Таблица 4. Заранее определенные роли уровня базы данных
| Роль | Описание |
| db_owner | Имеет полный доступ ко всем объектам базы данных, может удалять и воссоздавать объекты, а также присваивать объектные права другим пользователям. Может изменять параметры базы данных и выполнять ее обслуживание. Охватывает все функции, перечисленные ниже для других стандартных ролей уровня базы данных |
| db_accessadmin | Осуществляет контроль за доступом к базе данных путем добавления или удаления пользователей в режимах аутентификации Windows NT и SQL Server |
| db_datareader | Имеет полный доступ к выборке данных (с помощью оператора SELECT) из любой таблицы базы данных. Не дает разрешения на выполнение операторов INSERT, DELETE и UPDATE для любой таблицы базы данных |
| db_datawriter | Может выполнять операторы INSERT, DELETE и UPDATE для любой таблицы базы данных. Не дает разрешения на выполнение оператора SELECT для любой таблицы базы данных |
| db_ddladmin | Имеет возможность создавать, модифицировать и удалять объекты из базы данных |
| db_ securityadmin | Управляет системой безопасности базы данных, а также назначением объектных решений и ролей для базы данных |
| Роль | Описание |
| db_ backupoperator | Позволяет создавать резервные копии базы данных |
| db_ denydatareader | Отказывает в разрешении на выполнение оператора SELECT для всех таблиц базы данных. Позволяет пользователям изменять существующие структуры таблиц, но не позволяет создавать или удалять существующие таблицы |
| db_ denydatawriter | Отказывает в разрешении на выполнение операторов модификации данных (INSERT, DELETE,UPDATE) для любых таблиц базы данных |
| public | Как только пользователю предоставляется право доступа к базе данных, ему автоматически назначается роль public |
Стандартная роль
Назначение этой роли – логически сгруппировать пользователей в соответствии с их правами доступа. Существует несколько типов уровней безопасности, рассчитанных на три категории пользователей.
· Опытный пользователь ( Power user ). Может выполнять в базе данных любые операции. Этой группе пользователей обычно дается разрешение на выполнение операторов SELECT/INSERT/DELETE/ UPDATE во всех таблицах базы данных.
· Обычный пользователь ( Normal user ). Может модифицировать некоторые типы данных. Этой группе пользователей обычно дается разрешение на выполнение в одних таблицах базы данных операторов SELECT/INSERT/DELETE/UPDATE, а в других таблицах – только оператора SELECT.
· Неквалифицированный пользователь ( Bonehead user ). Ему не разрешается модифицировать никакие типы данных. Из-за отсутствия квалификации у этих пользователей администраторы часто беспокоятся о том, что они могут все испортить. Этой группе обычно дается разрешение на выполнение только оператора SELECT.
В таблице 5 приведена примерная схема распределения ролей между пользователями, реализованная на основе описанного выше принципа/
Таблица 5. Пример распределения ролей
| Тип пользователя | Назначение ролей |
| Опытный | Роли db_datareader и db_datawriter; эти заранее определенные роли уровня базы данных позволяют пользователям делать запросы и модифицировать любые таблицы базы данных |
| Обычный | Роль standard_user; эта определяемая пользователем роль уровня базы данных создается администратором базы данных. Обычно ей соответствуют разрешения на выполнение операторов SELECT/INSERT/DELETE/ UPDATE |
| Неквалифицированный | Роль db_datareader; эта заранее определенная роль уровня базы данных позволяет пользователям выполнять в базе данных только операторы SELECT |
Роли уровня приложения
Это особый тип роли, который позволяет пользователю брать на себя характеристики некоторой роли. Когда пользователь принимает роль уровня приложения, он берет на себя выполнение новой роли и временно отказывается от всех других назначенных ему прав доступа к конкретной базе данных. Но, возникает вопрос: зачем пользователю брать на себя новую роль? Дело в том, что, когда роль уровня приложения активна, пользователь может выдавать запросы в базу данных и выполнять обработку данных, контролируя этот процесс на высоком уровне. А когда роль неактивна, пользователь ограничен стандартными правами доступа к базе данных.
Ниже перечислены различия между ролями уровня приложения и стандартными ролями:
· Роли уровня приложения могут быть защищены паролем, а стандартные роли, определяемые пользователем, – не могут. Если роль уровня приложения защищена паролем, то для ее активации необходимо ввести пароль.
· И роли уровня приложения, и стандартные роли определяются пользователем и связаны с конкретной базой данных.
· Роли уровня приложения не назначаются пользователям непосредственно. Пользователи просто активизируют роль в пределах конкретной базы данных.
Роль уровня приложения имеет применять в среде, где пользователи делают запросы и модифицируют данные с помощью клиентского приложения, созданного независимым пользователем.
Создание учетных записей
Безопасность в пределах SQL Server 2012 with Tools обеспечивается с помощью нескольких объектов безопасности. Самым верхним уровнем здесь являются учетные записи, которые идентифицируют для сервера пользователя SQL Server 2012 with Tools. Учетные записи пользователей SQL Server создаются вручную. Для создания учетных записей вручную необходимо выполнить ряд действий (рис. 35).
1) В SQL Server Manager надо щелкнуть на знаке “плюс” (+), расположенном рядом с именем сервера на котором содержится учетная запись.
2) После этого щелкнуть на знаке “плюс” (+), который находится рядом с папкой Безопасность.
3) Затем щелкнуть правой кнопкой мыши на пиктограмме Учетные данные. Из контекстного меню выбрать Создать учетные данные.
Рис. 35. Создание учетной записи
4) После этого на экране появится диалоговое окно SQL Server Login Properties – New Login (Свойства учетных записей SQL Server – создание учетных записей). В этом окне вводим все необходимые данные (рис. 36).
Рис. 36. Ввод данных
5) Затем нужно выбрать вкладку Data Access, а в ней выбрать нужную БД и тип пользователя (в данном случае db_datareader (чтение данных)).
6) Далее, нужно перейти в область БД, для которой создается учетная запись. Там, в разделе Users появился созданный пользователь Belobj.
Нажимаем на значок Belobj и получаем окно со свойствами этого пользователя:
В качестве роли для этого пользователя может быть назначена любая роль из списка. В данном случае назначены роли public (роль по умолчанию, к которой принадлежат все пользователи базы данных) и роль db_datareader (чтение данных).
7) В клиентской части Access при соединении с серверной частью необходимо указать вновь созданного пользователя, его пароль и выбрать базу данных, в которой создана учетная запись для этого пользователя. Теперь пользователь будет работать с таблицами в соответствии с указанными для него правами на них.
Рис. 37. Свойства связи с данными
Теперь, с подключенной таким образом клиентской частью базы данных сможет работать только пользователь Belobj и только после введения своего личного пароля. Но этот пользователь сможет работать с базой данных только в соответствии с определенными для него правами, т.е. сможет выполнять в таблицах только те операторы, на выполнение которых он имеет право.
Таким образом, обеспечение безопасности базы данных с помощью создания учетных записей пользователей – это наиболее удобный и надежный способ защиты данных от несанкционированного к ним доступа и внесения ненужных изменений
НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ УНИВЕРСИТЕТ
Институт № 3 МАИ
Системы управления, информатики и электроэнергетики
Кафедра 305
«Автоматизированные комплексы систем ориентации и навигации»
Методическое пособие по курсу:
«УИРС»
SQL Server 2012/2014
Москва 2018
Оглавление
1 Цель работы.. - 4 -
2 Содержание отчета. - 4 -
3 Установка SQL Server 2012. - 5 -
4 Многопользовательские базы данных. - 19 -
5 Создание баз данных. - 24 -
5.1 Добавление нового пользователя. - 24 -
5.2 Этапы проектирования базы данных. - 27 -
5.3 Создание новой базы данных. - 27 -
5.4 Установка свойств базы данных. - 31 -
5.5 Управление базами данных. - 32 -
5.6 Удаление базы данных. - 32 -
5.7 Построение базы данных. - 33 -
5.8 Типы данных. - 35 -
5.9 Создание новой таблицы.. - 37 -
6 Добавление новых записей таблиц. - 46 -
6.1 Создание запросов. - 47 -
6.2 Создание диаграммы базы данных. - 48 -
6.3 Система безопасности и защиты данных в SQL Server 2012 with Tools. - 49 -
6.4 Тип подключения к SQL Server. - 49 -
6.5 Пользователи и их права доступа. - 50 -
6.6 Создание учетных записей. - 55 -
6.7 Настройка регулярного резервного копирования БД MS SQL Server. - 58 -
7 Подключение БД SQL Server 2012 к ACCESS 2010. - 69 -
7.1 Отображение элементов в проекте. - 72 -
Cписок использованных источников. - 73 -
Цель работы
Цель работы: ознакомление с принципами работы многопользовательских баз данных, изучение метода создания таблиц и связей между ними в реляционной СУБД MS SQL Server 2012 with Tools .
Задание на практическое занятие:
1. Ознакомиться с предлагаемым материалом по оценке преимуществ использования многопользовательских БД.
2. Изучить предлагаемый материал по установке и принципам работы SQL Server 2012 with Tools. При необходимости, возникшие при изучении материала вопросы задать преподавателю.
3. Создать свою базу данных в MS SQL Server 2012 with Tools, руководствуясь методическим материалом. Внимание! При создании имени БД необходимо использовать кодировку следующего вида, обеспечивающую различные имена: Project _ GrSt. Здесь Project ёопределяет смысловое название БД, Gr - сокращенный номер группы, St - номер студента в списке группы. Т.е. GrSt - это код студента-разработчика. Например, для студента из группы 03-408, имеющего в списке группы номер 11, название БД должно быть Project 811.
4. Создать таблицы в своей базе данных SQL Server 2012 with Tools, руководствуясь методическим материалом.
5. Внести данные в таблицы. Рекомендация: внести 3 - 4 записи в каждую таблицу. Данные лучше вводить после создания схемы данных.
6. Создать диаграмму базы данных, руководствуясь методическим материалом.
7. Присоединить свою базу данных к ACCESS 2010 для дальнейшей работы с клиентским приложением.
Содержание отчета
1. Обоснование целесообразности перехода от разработки БД посредством Access к БД SQL Server 2012 with Tools.
2. Пример таблицы в режиме конструктора (с описанием свойств полей).
3. Пример заполненной таблицы
4. Диаграмма (схему данных) БД (с объяснением причин наличия или отсутствия каскадных связей).
5. Демонстрация преподавателю своего варианта таблиц и диаграммы БД на компьютере, а также подтверждение возможности каскадных операций.
Установка SQL Server 2012
Примечание.
В начале работы с SQL Server 2012 Вам необходимо будет скачать его с официального сайта https://www.dreamspark.com/Default.aspx. Этот сайт характеризуется тем, что в период обучения любой студент может скачать лицензионные продукты Microsoft, предварительно необходимо зарегистрироваться, создать свою учебную запись.
Что нужно для того, что бы зарегистрироваться в Dreamspark .
1. Для начала зайдите на сайт dreamspark.com.
2. Выберите закладку вверху «Учащиеся».
3. Перейдите внизу страницы по ссылке «Создать учетную запись».
4. Заполняете учетные данные. Заполняйте так, как указано в студенческом билете, т.к. далее это понадобится. Потом, «Продолжение проверки».
5. Далее, выбираем слева вариант «Я могу предоставить документы».
6. Далее, указываем нужную информацию, прикрепляем фотографию студенческого билета, отправляем.
7. В течение нескольких дней вам на почту придёт уведомление, что вы можете скачивать бесплатно ПО от Microsoft. Скачивать можно на том же сайте.
Итак, когда Мы уже скачали SQL Server 2012 c DreamSpark переходим к установке:
· Запускаем файл «setup.exe» с установочного диска.
· Откроется «Центр установки SQL Server» (SQL Server Installation Center), переходим на вкладку «Установка» (Installation) и нажимаем на «Новая установка изолированного экземпляра SQL Server или добавление компонентов к существующей установке» (New SQL Server stand-alone installation or add features to an existing installation).
· Запустится «Программа установки SQL Server 2012» (SQL Server 2012 Setup). Убедимся что нет проблем, которые могут возникнуть во время установки файлов поддержки программы установки и нажмем «ОК». Если все же возникли какие-либо проблемы, необходимо устранить их и повторить процедуру проверки нажав «Включить заново» (Re-run).
· Теперь необходимо ввести ключ продукта (если он у вас есть) или выбрать свободный выпуск SQL Server. Это может быть:
o Evaluation Edition — полный набор компонентов, с ограничением использования в 180 дней.
o Express Edition — бесплатная версия с ограниченным функционалом.
Ключ продукта можно будет ввести в любое время, после установки SQL Server. О том как это сделать читайте в статье Изменение версии или ввод ключа Microsoft SQL Server 2012. После выбора параметров нажимаем «Далее» (Next).
· Принимаем условия лицензионного соглашения, соглашаемся (или нет) на передачу данных об использовании компонентов в корпорацию Microsoft и жмем «Далее» (Next).
· Если имеются обновления программы установки, включаем обновление продукта, нажав «Включить обновления продукта SQL Server» (Include SQL Server product updates) и нажимаем «Далее» (Next).
· Дожидаемся окончания загрузки обновлений и установки файлов установки SQL Server.
· Вот и закончился подготовительный этап, и мы приступаем к непосредственной установке и выбору параметров SQL Server. Убеждаемся что нет проблем, которые могут возникнуть во время установки и нажмем «Далее» (Next). Если же все имеются какие-либо проблемы, необходимо устранить их и повторить процедуру проверки нажав кнопку «Включить заново» (Re-run).
· Вариант установки выбираем «Установка компонентов SQL Server» (SQL Server Feature Installaion) и нажимаем «Далее» (Next).
· Теперь выбираем компоненты для установки. Для большинства случаев (например для работы и администрирования сервера 1С:Предприятие), вполне достаточно установки компонент:
o Службы компонента Database Engine (Database Engine Services)
o Репликация SQL Server (SQL Server Replication)
o Полнотекстовый и семантический поиск (Full-Text and Semantic Extractions for Search)
o Служба Data Quality Services (Data Quality Services)
o Средства управления — основные (Management Tools — Basic)
o Средства управления — полный набор (Management Tools — Complete)
Все компоненты всегда можно доставить / удалить, просто заново запустив установку SQL Server. Выбрав необходимые компоненты, нажимаем «Далее» (Next).
· Опять убеждаемся, что процесс установки не будет заблокирован, исправляем проблемы в противном случае и нажимаем «Далее» (Next).
· Теперь необходимо ввести имя именованного экземпляра SQL Server (рекомендую) или оставить имя экземпляра по умолчанию. Определившись с именем (Named instance) и идентификатором (Instance ID) экземпляра SQL Server нажимаем «Далее» (Next).
· Убедимся, что имеется достаточно свободного пространства для установки выбранных компонент SQL Server и снова нажмем «Далее» (Next).
· Теперь выберем режим запуска служб SQL Server. Поменяем параметр запуска Агента SQL Server (SQL Server Agent) на «Авто» (Automatic) (для того чтобы отрабатывали регламентированные задания агента SQL) и перейдем на вкладку «Параметры сортировки» (Collation).
· Здесь необходимо определиться с параметрами сортировки баз данных.
Это очень важно, данный параметр нельзя будет изменить позднее!!!
Для большинства программ, которые будут работать с SQL сервером (например для систем1С:Предприятие) необходимо выбрать «Cyrillic_General_CI_AS». Указав параметры сортировки нажимаем «Далее» (Next).
· Теперь следует выбрать режим проверки подлинности (Authentication Mode). Я рекомендую выбрать «Смешанный режим» (Mixed Mode), ввести пароль на встроенную учетную запись «sa», а также добавить администраторов SQL Server из числа имеющихся пользователей Windows с помощью кнопок «Добавить текущего пользователя» (Add Current User) и «Добавить…» (Add..). Установив все параметры переходим на вкладку «Каталоги данных» (Data Directories).
· Здесь можно выбрать каталог хранения баз данных (Data root directory). Если в конфигурации сервера присутствует RAID-массив из жестких дисков или SSD-диски, разумно расположить файлы данных именно на нем. Указав необходимый путь, жмем «Далее» (Next).
· Принимаем решение об отправке (или не отправке) отчетов об ошибках в компанию Microsoft и снова жмем «Далее» (Next).
· В очередной раз убеждаемся что процесс установки не будет заблокирован, исправляем ошибки, если они имеют место быть, и нажимаем «Далее» (Next).
· Проверяем все введенные на предыдущих шагах параметры и нажимаем «Установить» (Install), для запуска процесса установки SQL Server.
· Установка может быть довольно продолжительной по времени. Дожидаемся ее окончания.
· Затем убеждаемся, что все компоненты установлены успешно и завершаем работу мастера нажав на кнопку «Закрыть» (Close).
· Установка Microsoft SQL Server 2012 завершена.
· Для администрирования (добавления баз данных, пользователей и пр.) используется программа «SQL Server Management Studio» («Пуск» - «Microsoft SQL Server 2012» - «Среда SQL Server Management Studio»).
Дата: 2019-11-01, просмотров: 180.
