Практическая работа 1. 2 Описание проектирования базы данных 2 практическая работа 2 10

Название	Практическая работа 1. 2 Описание проектирования базы данных 2 практическая работа 2 10
Анкор	MS SQL
Дата	20.04.2022
Размер	2.52 Mb.
Формат файла
Имя файла	ms-sql-server-pr.1-5.docx
Тип	Практическая работа #487708
страница	6 из 12

1 2 3 4 5 6 7 8 9 ... 12

2.6.4. Подключение к базе данных
Чтобы подключиться к зарегистрированной базе данных, надо выбрать нужную базу данных в списке (рис. 5) и сделать двойной щелчок мышкой на выбранной базе

данных.
Если все параметры подключения были введены правильно, то произойдет подключение к базе данных, название подключенной базы данных в окне "Обозревателя объектов" будет выделено жирным шрифтом, а также появятся вложенные узлы c объектами, содержащимися в подключенной базе данных (рис. 7).

Рис. 7. Зарегистрированные базы данных в SQL Server Management Studio
После подключения к базе данных можно просматривать имеющиеся объекты, создавать новые, вносить и просматривать данные, а также проводить операции с имеющимися объектами.
После создания БД в окне Обозревателя объектов (его можно вызвать по ) выбираем DataBases (Базы данных) и откроется список БД, в котором откроем созданную БД (если она не появилась, то в окне Object Explorer нажать для обновления списков), которая состоит из восьми вложенных разделов (некоторые содержат еще дополнительные разделы), соответствующих объектам СУБД SQL Server:

Database Diagrams	Views (Представления)	Programmability
(Диаграммы БД)		(Объекты
		программирования)
Tables (Таблицы)	Synonyms (Синонимы)	Security (Безопасность)

Service Broker	Storage

На начальном этапе раздел созданной БД пуст, за исключением некоторых объектов, которые создаются по умолчанию, например в разделе Security/ Users создаются пользователи, которые имеют право на доступ к объектам БД, их можно изменить.
2.6.5. Удаление базы данных

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

Выполнить в программе " SQL Server Management Studio " команду контекстного меню "Удалить", выбрав перед этим в списке базу данных, а затем подтвердить свое желание в диалоговом окне.

Выполнить оператор DROP DATABASE в SQL-редакторе.
Удалить файл с базой данных.

Синтаксис оператора DROP DATABASE:
DROP DATABASE database_name;

2.6.6. Резервное копирование и восстановление
Резервное копирование (backup)базы данных и восстановление из резервнойкопии (restore) – два важнейших и наиболее частых процесса, осуществляемых

администраторами баз данных.
Резервное копирование базы данных – единственный надежный способ предохранить данные от потери в результате поломки диска, сбоев электропитания, действий злоумышленников и ошибок в программах. В процессе резервного копирования создается независимый от платформы "снимок" базы данных, с помощью которого можно перенести данные на другую операционную систему или даже другую платформу.
Полный цикл: резервное копирование и восстановление из резервной копии приводит к корректировке статистической информации, является средством от излишнего "разбухания" базы данных и необходимой операцией обслуживания базы данных. Кроме того, миграция от одной версии сервера к другой также происходит при помощи процесса backup/restore.

Для создания резервной копии базы данных с помощью программы " SQL Server Management Studio " необходимо подключиться к базе данных, выбрать из контекстного меню базы данных Задачи/ Создать резервную копию. В открывшемся диалоговом окне "Мастер резервного копирования" задать несколько параметров и нажать кнопку [Выполнить], см. рис.8.
После выбора пути и файла для резервной копии в окне Back Up Database нажатием на OK запускаем процесс создания резервной копии. В случае успешной работы появится сообщение.

результате будет создан файл с резервной копией. Стандартным расширением таких файлов для " SQL Server Management Studio " является "*.bak". Файл с резервной копией базы данных обычно на порядок меньше оригинала.


Рис.8. Создание резервной копии базы	Рис.9. Восстановление базы данных
данных

Для восстановления базы данных из резервной копии используется команда "База данных/ Восстановление базы данных. В результате откроется диалоговое окно "Мастер восстановления баз данных", в котором надо выбрать имя БД куда будет восстанавливаться база данных, в которую будет помещен результат, способ восстановления, файл, из которого будет восстанавливаться база данных, отмечаем выбранную резервную копию, и нажать кнопку [Восстановить], см.рис.9. Запускаем процесс восстановления. В случае успешного выполнения получим сообщение.
Резервное копирование и восстановление базы данных, наряду с процессом извлечения метаданных и последующего выполнения полученного сценария, можно использовать при переносе разрабатываемой базы данных между различными компьютерами для обеспечения самостоятельной работы студентов над практическими работами и курсовым проектом.
Самостоятельно. Выполните вначале резервирование, а затем восстановление базы данных.
Удалите базу данных Educator c помощью скрипта, сохраните sql-запрос.
2.7. Копирование и перенос на другой сервер БД
Для просмотра, запуска, остановки служб MS SQL Server необходимо запустить утилиту SQL Server Configuration Manager (рис. 10).

Рис.10. Список служб сервера БД
Для того чтобы скопировать БД необходимо остановить службу SQL Server (в ее контекстном меню выбрать Stop). Далее в подпапке …\MSSQL.1\MSSQL\Data\ скопировать файлы с вашим названием БД (по умолчанию их два). Не забудьте потом снова запустить службу SQL Server (в ее контекстном меню выбрать Start).
Для того чтобы подключить скопированную БД на другом сервере, нужно предварительно скопировать ваши файлы в папку …\MSSQL.1\MSSQL\Data\ соответствующего сервера. Далее запустить утилиту SQL Server Management Studio. В появившемся окне с названием Object Explorer Проводник объектов (его можно вызвать по <F8>) выбираем DataBases (Базы данных) и по <правой кнопке мыши> в контекстном меню (рис. 5) выбираем Attach… (Присоединить…). В появившемся окне Attach DataBases (Присоединение базы данных) нажать <Add> и выбрать ваш файл БД с расширением .mdf.
2.8. Системные базы данных
Системные базы данных сервера, создаваемые при установке, и их файлы представлены в таблице.

Все системные и пользовательские базы данных содержат в обязательном порядке 18 системных таблиц, которые хранят информацию, определяющие структуру и организацию соответствующей базы данных.
MSSQL Server поддерживает два основных класса приложений клиентского типа:

приложения реляционных баз данных, использующие команды Transact - SQL с расширениями ODBC и набор стандартных функций и объектно-ориентированных методов;

web - приложения, использующие команды Transact - SQL или запросы на языке Xpath и документы XML.

Оба класса приложений используют API интерфейс баз данных типа OLE DB или
ODBC.
2.2. Основные принципы управления учетными записями и ролями в MS SQL Server
2.2.1. Список системных процедур и команд, которые позволяют реализовать политику разделения прав между пользователя БД.

Название встроенной процедуры	Описание
sp_grantlogin	– позволяет использовать пользователей или группы ОС для
	соединения с Microsoft SQL Server™ , используя Windows
	Authentication.
	Этот пример позволяет пользователю Windows NT
	Corporate\BobJ соединяться с SQL Server. Например,
	EXEC sp_grantlogin 'Corporate\BobJ'

sp_defaultdb	Изменяет для пользователя БД по умолчанию
	Этот пример устанавливает БД по умолчанию pubs для
	пользователя Victoria. Например,
	EXEC sp_defaultdb 'Victoria', 'pubs'

sp_grantdbaccess	Добавляет учетную запись из раздела security в текущую БД,
	для учетных записей Microsoft Windows также дает разрешение
	на доступ к текущей БД.
	Синтаксис:
	EXEC sp_grantdbaccess [@loginame =] 'login'
	[,[@name_in_db =] 'name_in_db' [OUTPUT]]
	Этот пример добавляет учетную запись Corporate\GeorgeW в
	текущую БД и присваивает псевдоним внутри БД Georgie.
	Например,
	EXEC sp_grantdbaccess 'Corporate\GeorgeW', 'Georgie'

sp_revokedbaccess	Удаляет информацию об учетной записи из текущей БД.
	Синтаксис:
	EXEC sp_revokedbaccess [ @name_in_db = ] 'name'
	Этот пример удаляет учетную запись Corporate\GeorgeW из
	текущей БД.
	EXEC sp_revokedbaccess 'Corporate\GeorgeW'

sp_addrole	Создает новую роль в текущей БД.
	Этот пример создает новую роль в текущей БД с названием
	Managers.
	EXEC sp_addrole 'Managers'

sp_addrolemember	В текущей БД назначает роль конкретному пользователю.
	Пример A.
	Этот пример добавляет учетную запись Corporate\JeffL из
	Windows NT в БД Sales как пользователя Jeff. Jeff затем
	получает роль Sales_Managers в БД Sales.
	USE Sales --сделать текущей БД Sales
	GO –выполнить команду, а потом запустить следующую
	EXEC sp_grantdbaccess 'Corporate\JeffL', 'Jeff'
	GO
	EXEC sp_addrolemember 'Sales_Managers', 'Jeff'
	Пример B.
	Этот пример добавляет пользователя SQL Server с именем
	Michael к роли Engineering в текущей БД.
	EXEC sp_addrolemember 'Engineering', 'Michael'

sp_helprotect	Показывает список привилегий, ассоциированных с ролью.
sp_helprolemember	Показывает список пользователей БД, входящих в указанную
	роль
sp_addsrvrolemember	Присвоение встроенной серверной роли для существующей
	учетной записи
	sp_addsrvrolemember [ @loginame = ] 'login' , [ @rolename =
	] 'role'
	Например:
	sp_addsrvrolemember 'Admin_DB', 'sysadmin'

sp_dropsrvrolemembe	Удаление встроенной серверной роли для учетной записи или
r	группы
	sp_dropsrvrolemember [ @loginame = ] 'login' , [ @rolename =
	] 'role'
	Например:
	sp_dropsrvrolemember 'Admin_DB' , 'sysadmin'

sp_helpsrvrole	Описание только встроенных ролей в SQL Server
	sp_helpsrvrole [ [ @srvrolename = ] 'role' ]
	Например:
	sp_helpsrvrole 'sysadmin'

sp_helpsrvrolemember	Возвращает список ролей и учетных записей, которым
	присвоены эти роли
	sp_helpsrvrolemember [ [ @srvrolename = ] 'role' ]
	Например:
	sp_helpsrvrolemember 'sysadmin'

sp_srvrolepermission	Возвращает список ролей и разрешений, которые присвоены
	этим ролям
	sp_srvrolepermission [[@srvrolename =] 'role']
	Например:
	sp_srvrolepermission 'sysadmin'

sp_addlogin	Создание новой учетной записи в SQL Server в разделе Sequrity:
	sp_addlogin [ @loginame = ] 'login'
	[ , [ @passwd = ] 'password' ]
	[ , [ @defdb = ] 'database' ]
	[ , [ @deflanguage = ] 'language' ]
	[ , [ @sid = ] sid ]
	[ , [ @encryptopt = ] 'encryption_option' ]
	Например:
sp_adduser	sp_addlogin 'login1',sysname, 'DB_Books'
sp_adduser	Создает пользователя в SQL Server без PUBLIC в БД '
	Создает пользователя в SQL Server без PUBLIC в БД '
	DB_Books'.
	Нужно еще использовать
	sp_adduser [ @loginame = ] 'login'
	[ , [ @name_in_db = ] 'user' ]
	[ , [ @grpname = ] 'group' ],
	Пример:
	Создана база данных DB_Books. В ней создан пользователь
	Admin_DB с серверной ролью sysadmin, с ролью в БД db_owner.
	Cоздать в QueryAnalyzer нового пользователя с именем Public_
	и паролем Public_1 (пароль не должен совпадать с именем
	пользователя) с помощью следующих команд (не забудьте
	нажать F5 для запуска команд на выполнение):
	EXEC sp_addlogin 'Public_','Public_1', 'DB_Books'
	use DB_Books
	EXEC sp_adduser 'Public_','Public_'
	В БД DB_Books создан пользователь Public_ с ролью в БД
	DB_Books public.
Deny (отрицание)	Этот пример запрещает несколько системных привилегий для
	нескольких пользователей.
	Пользователи не могут использовать системные привилегии
	CREATE DATABASE or CREATE TABLE, если они не
	наделены имим через команду GRANT.
	Пример:
	DENY CREATE DATABASE, CREATE TABLE
	TO Mary, John, [Corporate\BobJ]
	DENY SELECT, INSERT, UPDATE, DELETE ON authors TO
	Mary, John, Tom
Grant	This example grants multiple statement permissions to the users
(предоставлять)	Mary and John, and the Corporate\BobJ Windows NT group.
	GRANT CREATE DATABASE, CREATE TABLE
	TO Mary, John, [Corporate\BobJ]
	Назначение разрешения на выборку (SELECT) для роли
	PUBLIC в таблице Authors:
	GRANT SELECT ON Authors TO public

Revoke (отменять)	This example revokes multiple statement permissions from multiple
	users.
	REVOKE CREATE TABLE, CREATE DEFAULT
	FROM Mary, John
	This example removes the denied permission from Mary and,
	through the SELECT permissions applied to the Budget role, allows
	Mary to use the SELECT statement on the table.
	REVOKE SELECT ON Budget_Data TO Mary

2.2.2. Создание пользователей для доступа к серверу через утилиту Microsoft SQL Server Management Studio
Создадим новую учетную запись для нашей базы данных University. Для этого выберите в Обозревателе объектов раздел Безопасность/Имена входа. Добавьте новое имя входа – Proba, установите опцию Проверка подлинности SQL Server, присвойте свой пароль, примените к выбранной базе данных, установите язык по умолчанию – русский.

Рис. 11. Раздел Безопасность (Security) для работы с пользователями и создание нового пользователя (при SQL Server аутентификации нужно снять галочки с Enforce passwordpolicy)
Прежде чем добавлять нового пользователя просмотрите его назначенные серверные роли. Для этого в этом же окне выберите раздел Роли сервера. Установите для пользователя Proba роль sysadmin.

Рис. 12. Настройка серверной роли для нового пользователя (весь список серверных ролей с их привилегиями в конце работы)
Далее просмотрите раздел Сопоставление пользователя. Установите для базы данных University у пользователя Proba права доступа Db_owner, означающие, что пользователь может выполнять любые действия с БД. Ниже перечислены все возможные варианты прав доступа.

Рис. 13. Настройка роли базы данных для нового пользователя (весь список ролей баз данных с их привилегиями ниже)
Перечень ролей БД:

Public – минимальные права доступа к БД (на просмотр)

Db_owner – может выполнять любые действия с БД
Db_accessadmin – добавляет и удаляет пользователей БД
Db_sequrityadmin – управляет ролями в БД и разрешениями на запуск команд и работу с объектами БД
Db_ddladmin – добавляет, изменяет и удаляет объекты БД

Db_backupoperator – осуществляет резервное копирования БД
Db_dataSTUDENT – может просматривать все данные в каждой таблице в БД Db_datawriter - может добавлять, удалять и изменять данные в каждой таблице в

БД
Db_denydataSTUDENT – запрет на просмотр всех данных в каждой таблице в БД Db_denydatawriter - запрет на добавление, удаление и изменение всех данных в
каждой таблице в БД

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

Рис. 14. Разблокирование создаваемой учетной записи
После нажатия на в БД появится пользователь Proba с правами собственника БД, который может выполнять все манипуляции с БД University.
Откройте в окне обозревателя объектов БД University и перейдите на вкладку Безопасность, там вы найдите только что созданного пользователя.
2.2.3. Создание ролей программно
Для упрощения управления правами доступа в системе создаются роли, которые затем можно назначать группе пользователей.

Создадим для нашего примера роли декана (DEKAN) и студента (STUDENT).

Пример создания роли декана:

USE University --сделать текущей БД University EXEC sp_addrole 'DEKAN'

Эти операторы набрать на странице, вызванной нажатием кнопки <Создать запрос>.

Для запуска команд на выполнение нажать

Сохраните запрос.

Повторный запуск тех же команд сгенерирует ошибки типа «В БД уже существует роль DEKAN».
Чтобы просмотреть, что роль добавлена, откройте вкладку Безопасность/Роли/Роли базы данных.

Пример создания роли студента:
USE University --сделать текущей БД university EXEC sp_addrole 'STUDENT'
Декан должен обладать правами на чтение, удаление, изменение, добавление во все таблицы БД University, а также должен иметь возможность запускать на исполнение процедуры и функции БД University. Поэтому роли декана из системных привилегий назначаем EXECUTE, а из привилегий доступа к объектам назначаем
DELETE, INSERT, UPDATE, SELECT.
Студент должен обладать правами на чтение из таблиц. Поэтому роли читателя из привилегий доступа к объектам назначаем SELECT.
Оператор представления привилегий

Синтаксис:
GRANT <привилегия>, ...

ON < объект >, …

TO <имя>

[WITH grant option];
Атрибут WITH GRANT OPTION дает право пользователю самому раздавать права, которые он получил.

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

Например (не выполнять):
GRANT select, update (Sales, num) ON Sales_data TO user1 WITH GRANT OPTION

Пользователь, предоставивший привилегию другому, называется грантор (grantor
— предоставитель). Привилегия является предоставляемой, если право на нее можно предоставить другим пользователям.
PUBLIC — имя роли, которую получает пользователь при добавлении в список пользователей конкретной БД, включает в себя минимальный набор прав на чтение данных из таблиц и представлений в БД.
Для примера (немного забегая вперед) создадим таблицу Discuplinu. Без объяснения синтаксиса выполните следующий sql-запрос:
USE University --сделать текущей БД university create table Discuplinu (
Kod_Discuplinu int NOT NULL primary key, name_Discuplinu nchar(30) NULL, kol_chasov int NULL
);
Выполните код и обновите вкладку Таблицы. Вы должны увидеть созданную таблицу для сохранения данных о всех дисциплинах. Эта таблица пока пустая с тремя столбцами Kod_Discuplinu, name_Discuplinu, kol_chasov.
Роль декана названа DEKAN. Операторы назначения прав доступа для этой роли представлены ниже:
GRANT DELETE, INSERT, UPDATE, SELECT ON Discuplinu TO DEKAN GRANT EXECUTE TO DEKAN
Роль студента названа STUDENT. Операторы назначения прав доступа для этой роли представлены ниже:
GRANT SELECT ON Discuplinu TO STUDENT Примените роли декена и студента к созданной таблице.
Создание пользователей с определенной ролью Пример создания декана Ivanov_Dek и присвоения ему роли:
EXEC sp_addlogin 'Ivanov_Dek','Ivanov', 'University' use University
EXEC sp_adduser 'Ivanov_Dek','Ivanov_Dek' EXEC sp_addrolemember 'DEKAN', 'Ivanov_Dek'
Пример создания студент Petrov_Stud и присвоения роли:
EXEC sp_addlogin 'Petrov_Stud','Petrov', 'University' use University
EXEC sp_adduser 'Petrov_Stud','Petrov_Stud' EXEC sp_addrolemember 'STUDENT', 'Petrov_Stud'
Выполните команды. Перейдите в окне Обозреватель объектов на Роли/Роли базы данных/Dekan и просмотрите его свойства. Просмотрите назначенные общие свойства, защищаемые объекты и расширенные свойства.

Самостоятельно просмотрите свойства роли базы данных Student. Просмотрите назначенные общие свойства, защищаемые объекты и расширенные свойства.

Оператор отмены привилегий
Синтаксис отмены привилегий:

REVOKE [with grant option]

привилегии >,… ON < объект >,…

FROM <имя_пользователя>;
Предложение with grant option сохраняет за пользователем перечисленные

привилегии, но отменяет его право передавать их кому-либо другому.
Пример:
REVOKE SELECT ON Discuplinu FROM STUDENT Выполните команду.
Оператор изымания роли у пользователя Revoke <список ролей> from <список пользователей>.
Пример:
use University

EXEC sp_droprolemember 'STUDENT', 'Petrov_Stud' Выполните команду и просмотрите результат.

ПРАКТИЧЕСКАЯ РАБОТА №3

РАЗРАБОТКА ТАБЛИЦ И ОГРАНИЧЕНИЙ
3.1. Цель практической работы
Изучить способы создания, изменения и удаления таблиц. Получить навыки использования приложения " SQL Server Management Studio " для создания, удаления и изменения структуры таблиц. Изучить SQL-операторы для работы с таблицами и индексами. Изучить используемые в SQL Server типы ограничений. Получить навыки использования программы " SQL Server Management Studio " для создания, изменения и удаления ограничений. Изучить SQL-операторы для работы с ограничениями.
Задание для практической работы №3
Для ранее созданной базы данных в СУБД SQL Server Management Studio:

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

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

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

Заполните таблицы данными не менее 5 записей в каждой.

Создать текстовый с именем отчет ПР3.doc (.docx), в котором отобразить скриншоты результатов работы в СУБД SQL Server Management Studio (окно с базой данных с перечнем всех таблиц, проекты таблиц с перечнем столбцов, окна ограничений внешних ключей (создание), окно с перечнем ключей для каждой таблицы, окна с данными для каждой таблицы, диаграмма базы данных).
Выслать отчет на электронную почту sine-tatyana@yandex.ru (1 подгруппа) и ulizakharova@mail.ru (2 подгруппа) до 26 апреля 2020 г. В теме письма указать свою группу и фамилию.

3.2. Исходные данные
Исходными данными является результат предыдущих практических работ.
3.3. Используемые программы
Программа " SQL Server Management Studio " и установленный сервер Microsoft SQL Server.

1 2 3 4 5 6 7 8 9 ... 12