Разработка серверной части системы Клиент-серверная архитектура это

Разработка серверной части системы Клиент-серверная архитектура — это решение задачи интеграции данных при условии приемлемой скорости работы приложения. Данное решение основано на следующих положениях: - коллективное использование данных, которые являются общими для всех структур; - исполнение сервером совместно используемых программируемых объектов базы данных. Под объектами базы данных понимают таблицы, связи между таблицами, триггеры, индексы. Под программируемыми объектами базы данных понимают просмотры и хранимые процедуры. 1

Создание базы данных Создание «контейнера» базы данных Серверная база данных — это «набор» объектов (таблицы, просмотры, хранимые процедуры триггеры и т. д. ), которые хранятся в файле. Этот "пустой" файл называется «контейнер» базы данных. 1. Создать папку, где будет храниться база данных. 2. Запустить утилиту SQL Server Management Studio. 3. В появившемся окне выбрать нужный сервер баз данных и способ идентификации. 2

4. После выполнения операции соединения с выбранным сервером откроется дерево объектов, где необходимо выбрать пункт Database New Database. 5. В открывшемся окне в поле Database Name ввести имя базы данных. При этом автоматически будут определены имена файлов (системный (Log) и базы данных(Data)). При необходимости имена можно отредактировать щелкнув мышкой на поле Logical Name. 3

9. Завершить работу, щелкнув мышью на кнопке ОК. Как только база данных будет создана, сервер откроет дерево объектов базы данных. Создание таблиц базы данных 1. Выбрать объект Tables New Table. 2. В открывшемся окне ввести название полей определить их тип, ограничения, первичный ключ. 4

a) Создание первичного ключа: щелкнув правой кнопкой мыши по полю, которое должно быть первичным ключом, выбрать команду Remove Primary Key. a) Создание триггера для первичного ключа: в свойстве поля первичного ключа раскрыть свойство Identity Specification и установить соответствующие значения согласно рисунку 5

3. После создания полей таблицы закроем соответствующее окно. Сервер предложит сохранить созданную таблицу. Нажмем кнопку ОК и введем название таблицы. После сохранения можно отобразить созданную структуру: Проделаем то же самое и для других таблиц. 6

1. 2. Создание связей базы данных Выбрать объект Database Diagrams New Database Diagram. В открывшемся окне необходимо выделить нужные таблицы для создания диаграммы (в нашем случае все) и нажать клавишу Add. 7

3. Расположить таблицы (по возможности) главные и вокруг справочные. 4. Уставить связи между таблицами. Для этого выбираем мышкой поле которое является первичным ключом в справочной таблице и ведем мышку на поле которое является вторичным ключом в главной таблице. 8

5. После отпускания клавиши мыши, в появившемся окне проверяем правильность установки первичных и вторичных ключей и нажимаем клавишу ОК. 6. Определяем характер связи для этого в поле Insert and Update Specification устанавливаем директивы при обновлении и удалении данных 9

7. Сохраним полученную диаграмму 10

Просмотры (view) — один из способов реализации запросов. Для создания просмотра применяют команду: CREATE VIEW имя. Просмотра [ (cтoлбeц_view [, столбец_view …])] AS <onepaтор_select> [WITH CHECK OPTION]; Обозначения: (cтолбец_view [, столбец_view. . . ]) — необязательный список столбцов; <onepamop_select> — критерий выбора строк, которые будут включены в просмотр; with check option — предотвращение ввода записей, не удовлетворяющих критерию выбора для обновляемых просмотров. 11

Просмотр (view) — это логический метод отображения набора данных. Физически — это откомпилированный SQL-запрос. Использование просмотров — это уменьшение размера оператора SQL, посылаемого приложением-клиентом серверу. Сервер теперь получает лишь команду "исполнить просмотр". Просмотр — это виртуальная таблица, которую можно изменять в зависимости от типа просмотра. Типы просмотров Запрос называют модифицируемый (updatable) если: - просмотр формируется из записей только одной таблицы; - в просмотр включен каждый столбец таблицы, имеющий атрибут not null; 12

- SELECT-оператор просмотра не содержит подзапросы, агрегатные функции, хранимые процедуры и функции, определенные пользователем. Если в определении просмотра нарушено хотя бы одно из выше перечисленных условий, то он — немодифицируемый (read-only). Пример Модифицируемый запрос Usp_ocenka. Можно переопределить оценку зачет на удовлетворительно. UPDATE Usp_ocenka SET NFOc = 2 WHERE NFOc = 1 13

Нижеприведенный просмотр позволяет просмотреть студентов и их оценки по данным таблиц Stydent, Ocenki и Uspevaemost. Он не является модифицируемым. Пример: Создадим немодифицируемый просмотр Styd_ocenki, используя следующую команду: CREATE VIEW Styd_ocenki AS SELECT Fam, Imya, Otch, Ocenka FROM Stydent, Ocenki, Uspevaemost WHERE (Uspevaemost. NFSt = Stydent. NSt) and (Uspevaemost. NFOc = Ocenki. NOc) 14

Создание просмотра с помощью утилиты SQL Server Management Studio 1. Выбрать объект Views New View. 2. В открывшемся окне выбрать необходимые таблицы для создания просмотра (в нашем случае Stydent, Ocenki и Uspevaemost). 3. Выбрать из таблиц поля для отображения (Fam, Imya, Otch, Ocenka) 4. Для отображения данных просмотра необходимо нажать кнопку Execute SQL (!). 15

5. Сохранить полученный просмотр. 16

Хранимые процедуры (stored procedure), которые еще часто называют sproc, представляют собой пакет (batch), который хранится в базе данных, а не в отдельном файле. Хранимые процедуры имеют входные и выходные параметры, могут возвращать значения. Хранимая процедура — это отдельная программа, написанная на процедурном языке используемого сервера баз данных. Существует две разновидности хранимых процедур: процедуры выбора (аналог SELECT - запросов) и исполняемые процедуры. Процедуры выбора возвращают наборы данных, которые состоят из строк или отдельных значений. Исполняемые процедуры не возвращают данные. Они предназначены для исполнения команд, например, delete. 17

Синтаксис объявления хранимой процедуры: CREATE PROCEDURE | PROC <имя_процедуры> [<имя_параметра> <тип_данных> [VARYING] [= <значение_ по_умолчанию>] [OUTPUT] [, … ]] AS BEGIN <код> END GO 18

Пример: Процедура возвращает все поля из таблицы Ocenki CREATE PROC P_Ocenki AS BEGIN SELECT * FROM Ocenki END GO Для выполнения хранимой процедуры необходимо записать: EXEC P_Ocenki 19

Изменение и удаление хранимых процедур Хранимые процедуры можно изменить при помощи оператора ALTER. Замечание: при редактировании хранимых процедур полностью заменяется существующая хранимая процедура. Различия между использованием ALTER PROC и CREATE PROC состоят в следующем: - ALTER PROC подразумевает, что хранимая процедура уже существует, a CREATE - нет. - ALTER PROC оставляет неизменными все установленные для хранимой процедуры права доступа, сохраняет идентификатор ID объекта и позволяет сохранять зависимости (отношения). Например: если процедура А вызывает процедуру В, но при этом процедура В создается заново, то отношение между этими процедурами не будет больше действительным. Однако если используется ALTER, то отношения сохранятся. 20

- ALTER PROC оставляет неизменной любую информацию об отношениях с другими объектами, которые могут вызвать изменяемую процедуру. Использование оператора DROP с последующим выполнением оператора CREATE идентично использованию ALTER PROC, но после выполнения DROP, a потом CREATE необходимо заново устанавливать для процедуры все права доступа. Удалить хранимую процедуру можно при помощи следующей строки кода: DROP PROC I PROCEDURE <имя_процедуры> 21

Создание хранимых процедур с помощью утилиты SQL Server Management Studio 1. Выбрать объект Programmability Stored Procedures New Stored Procedure. 2. В появившемся окне ввести код процедуры. 3. Выполнить запрос выбрав в меню Query Execute или нажав клавишу F 5. 4. Закрыть окно с кодом хранимой процедуры при необходимости сохранив ее код. 22

Для работы приложения необходимо создать следующие хранимые процедуры: 1. Добавление данных (В базе 4 таблицы – 4 хранимых процедуры). В качестве входных параметров хранимой процедуре передаются все значения полей , кроме первичного ключа. Шаблон процедуры добавления данных: CREATE PROCEDURE <название процедуры> <название параметра 1> <тип параметра>, …. . <название параметра. N> <тип параметра> AS BEGIN INSERT INTO <название таблицы>(<название поля 1>, …, <название поля. N>) 23

VALUES (<название параметра 1>, …, <название параметра. N>) END Пример: Создать хранимую процедуру для добавления данных в таблицу Uspevaemost: CREATE PROCEDURE Insert_Usp @Data. Oc datetime, @NFPred int, @NFOc int, @NFSt int AS BEGIN INSERT INTO Uspevaemost(Data. Oc, NFPred, NFOc, NFSt) VALUES (@Data. Oc, @NFPred, @NFOc, @NFSt) END 24

Аналогично необходимо создать хранимые процедуры добавления данных для таблиц: Stydent, Predmet и Ocenki. 2. Редактирование данных (В базе 4 таблицы – 4 хранимых процедуры). В качестве входных параметров хранимой процедуре передаются все значения полей. Шаблон процедуры изменения данных: (в качестве значения первичного ключа используется <название параметра 1> ) CREATE PROCEDURE <название процедуры> <название параметра 1> <тип параметра>, …. . <название параметра. N> <тип параметра> AS BEGIN 25

UPDATE <название таблицы> SET <название поля 2> = <название параметра 2>, …, <название поля. N>= <название параметра. N> WHERE <название поля 1>= <название параметра 1> END Пример: Создать процедуру для редактирования данных таблицы Stydent: CREATE PROCEDURE Edit_Styd @NSt int, @Fam varchar(10), @Imya varchar(10), @Otch varchar(10), @Data. R datetime, @Kurs varchar(1) 26

AS BEGIN UPDATE Stydent SET Fam = @Fam, Imya = @Imya, Otch = @Otch, Data. R = @Data. R, Kurs = @Kurs WHERE @NSt=NSt END Аналогично необходимо создать хранимые процедуры редактирования данных для таблиц: Uspevaemost, Predmet и Ocenki. 27

3. Удаление данных (В базе 4 таблицы – 4 хранимых процедуры). В качестве входного параметра хранимой процедуре передается один параметр – значение первичного ключа. Шаблон процедуры удаления данных: (в качестве значения первичного ключа используется <название параметра 1> ) CREATE PROCEDURE <название процедуры> <название параметра 1> <тип параметра> AS BEGIN DELETE FROM <название таблицы> WHERE <название поля 1>= <название параметра 1> END 28

Пример: Создать хранимую процедуру удаления данных из таблицы Predmet. CREATE PROCEDURE Del_Pred @NPr int AS BEGIN DELETE FROM Predmet WHERE @NPr=NPr END 29

4. Процедуры возвращения набора данных: 1. Выборка данных для отчета № 1 (необходимо определить у каких студентов и в каком количестве та или иная оценка). 2. Выборка данных для отчета № 2 (необходимо вывести табель успеваемости студента). Шаблон процедуры выборки данных: (в качестве значения первичного ключа используется <название параметра 1> ) CREATE PROCEDURE <название процедуры> <название параметра 1> <тип параметра> = <значение по умолчанию>, …. . <название параметра. N> <тип параметра>= <значение по умолчанию> 30

AS SELECT <название полей выборки, вычисляемые поля> FROM <название таблиц выборки> WHERE <условие выборки> END Примеры созданных процедур: 1. В качестве входного параметра хранимой процедуре передается один параметр – значение первичного ключа записи в таблице Ocenki, а возвращается набор данных состоящий из фамилии, имени, отчества студента и количества оценок данного вида. Студенты должны выводится в отсортированном виде. 31

CREATE PROCEDURE Otchet 1 @Param 1 int AS BEGIN SELECT Fam, Imya, Otch, COUNT(NFOc) AS Kol FROM Stydent, Uspevaemost WHERE (@Param 1=Uspevaemost. NFOc) and (Uspevaemost. NFSt=Stydent. NSt) GROUP BY Fam, Imya, Otch order by fam, imya, otch END 32

2. В качестве входного параметра хранимой процедуре передается один параметр – значение первичного ключа записи в таблице Stydent, а возвращается набор данных состоящий из предмета и оценки. CREATE PROCEDURE Otchet 2 @Param int AS BEGIN SELECT Pred, Ocenka FROM Uspevaemost, Ocenki, Predmet WHERE (@Param =Uspevaemost. NFSt) and (Uspevaemost. NFPred=Predmet. NPr) and (Uspevaemost. NFOc=Ocenki. NOc) END 33

В результате получим следующий набор хранимых процедур: 34

Для отображения данных создадим два просмотра: 1. Отображение таблицы Uspevaemost с сортировкой по фамилии имени отчеству студентов. SELECT Uspevaemost. NUs, Stydent. Fam, Stydent. Imya, Stydent. Otch, Uspevaemost. Data. Oc, Predmet. Pred, Ocenki. Ocenka FROM Uspevaemost, Stydent, Predmet, Ocenki WHERE (Ocenki. NOc =Uspevaemost. NFOc) and (Uspevaemost. NFPred = Predmet. NPr) and (Uspevaemost. NFSt = Stydent. NSt) ORDER BY Stydent. Fam, Stydent. Imya, Stydent. Otch, Predmet. Pred 35

2. Отображение таблицы Stydent, отсортированных по фамилии, имени и отчеству для выборки из ниспадающего списка. SELECT NSt, Fam + ' ' + Imya + ' ' + Otch AS FIO FROM Stydent ORDER BY FIO 36