Разработка клиент-серверных приложений

КЫРГЫЗСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ им. И.Раззакова

ФАКУЛЬТЕТ ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ

Кафедра ПРОГРАММНОГО ОБЕСПЕЧЕНИЯ КОМПЬЮТЕРНЫХ

СИСТЕМ

РАЗРАБОТКА КЛИЕНТ-СЕРВЕРНЫХ ПРИЛОЖЕНИЙ

MS SQL SERVER 2014 + MS ACCESS 2013

МЕТОДИЧЕСКИЕ УКАЗАНИЯ К ВЫПОЛНЕНИЮ

ЛАБОРАТОРНЫХ РАБОТ ПО БАЗАМ ДАННЫХ

Для студентов направления 710400

«Программная инженерия»

Бишкек 2014

РАССМОТРЕНО ОДОБРЕНО

На заседании кафедры Методическим советом ФИТ

«Программное обеспечение Прот. №2 от 29.09.2014

компьютерных систем»

Прот. №1 от 26.08.2014

УДК 004.65

Составитель – доц. Раматов К.С.

Разработка клиент-серверных приложений SQL SERVER 2014+Access 2013: Методические указания к выполнению лабораторных работ по базам данных /Кырг. техн. ун-т, Бишкек, 2014.-48 с.

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

Предназначено для студентов направления «Программная инженерия» всех форм обучения.

Табл.4. Рис.3. Библиогр. 5 назван.

Рецензент: профессор кафедры «Программное обеспечение компьютерных систем» Кыргызского государственного технического университета им. И.Раззакова, к.т.н. Тен И.Г.

ВВЕДЕНИЕ

Методические указания по выполнению лабораторных работ по дисциплине «Базы данных» ориентированы на студентов III курса. Они охватывают ряд вопросов по программе данной дисциплины.

Указания содержат:

· основные теоретические сведения;

· перечень обязательных заданий для выполнения лабораторных работ;

· указания к выполнению лабораторных заданий;

В методических указаниях приводятся наиболее полное представление основных понятий таких разделов курса «Базы данных» как “Таблицы”, “Представления”, “Ограничения целостности”, “Хранимые процедуры”, “Триггеры”, а также примеры написания кодов и решения задач с использованием языка Transact-SQL. Это позволит студентам как самостоятельно изучить теоретический материал по данным разделам, так и получить задание и решить задачи по лабораторным занятиям.

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

Лабораторная работа №1

Создание базы данных, таблиц и диаграмм (4 часа)

Цель работы

Освоение навыков построения архитектуры и обеспечения целостности баз данных (БД) с помощью СУБД SQL SERVER 2014.

Теоретические сведения

В реляционной модели достигается гораздо более высокий уровень абстракции данных, чем в иерархической или сетевой. В статье Е.Ф.Кодда утверждается, что "реляционная модель предоставляет средства описания данных на основе только их естественной структуры, т.е. без потребности введения какой-либо дополнительной структуры для целей машинного представления". Другими словами, представление данных не зависит от способа их физической организации. Это обеспечивается за счет использования математической теории отношений (само название "реляционная" происходит от английского relation - "отношение").

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

Определения:

• Декартово произведение: Для заданных конечных множеств (не обязательно различных) декартовым произведением называется множество произведений вида , где

Пример: если даны два множества A (a₁,a₂,a₃) и B (b₁,b₂), их декартово произведение будет иметь вид С=A*B (a₁*b₁, a₂*b₁, a₃*b₁, a₁*b₂, a₂*b₂, a₃*b₂)

• Отношение: Отношением R, определенным на множествах , называется подмножество декартова произведения . При этом:

• множества называются доменами отношения;
• элементы декартова произведения называются кортежами;
• число n определяет степень отношения ( n=1 - унарное, n=2 - бинарное, ..., n-арное);
• количество кортежей называется мощностью отношения;

Пример: на множестве С из предыдущего примера могут быть определены отношения R₁ (a₁*b₁, a₃*b₂) или R₂ (a₁*b₁, a₂*b₁, a₁*b₂)

Отношения удобно представлять в виде таблиц. На рис. 1 представлена таблица (отношение степени 5), содержащая некоторые сведения о работниках гипотетического предприятия. Строки таблицы соответствуют кортежам. Каждая строка фактически представляет собой описание одного объекта реального мира (в данном случае работника), характеристики которого содержатся в столбцах. Можно провести аналогию между элементами реляционной модели данных и элементами модели "сущность-связь". Реляционные отношения соответствуют наборам сущностей, а кортежи - сущностям. Поэтому, также как и в модели "сущность-связь" столбцы в таблице, представляющей реляционное отношение, называют атрибутами.

Рис. 1 Основные компоненты реляционного отношения.

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

Несколько атрибутов одного отношения и даже атрибуты разных отношений могут быть определены на одном и том же домене. В примере, показанном на рис.1 атрибуты "Оклад" и "Премия" определены на домене "Деньги". Поэтому, понятие домена имеет семантическую нагрузку: данные можно считать сравнимыми только тогда, когда они относятся к одному домену. Таким образом, в рассматриваемом нами примере сравнение атрибутов "Табельный номер" и "Оклад" является семантически некорректным, хотя они и содержат данные одного типа.

Именованное множество пар "имя атрибута - имя домена" называется схемойотношения. Мощность этого множества - называют степенью или "арностью" отношения. Набор именованных схем отношений представляет из себя схему базы данных.

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

Отношение может содержать несколько ключей. Всегда один из ключей объявляется первичным(primary key, в дальнейшем будем обозначать PK), его значения не могут обновляться. Все остальные ключи отношения называются возможными ключами.

Комплекс программных средств, осуществляющих управление БД, основанных на реляционной модели, называют системой управления реляционными БД (СУРБД),

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

Рис. 2. База данных о подразделениях и сотрудниках предприятия.

Например, связь между отношениями ОТДЕЛ и СОТРУДНИК создается путем копирования первичного ключа "Номер_отдела" из первого отношения во второе. Таким образом:

• для того, чтобы получить список работников данного подразделения, необходимо

1. из таблицы ОТДЕЛ установить значение атрибута "Номер_отдела", соответствующее данному "Наименованию_отдела"

2. выбрать из таблицы СОТРУДНИК все записи, значение атрибута "Номер_отдела" которых равно полученному на предыдушем шаге.

• для того, чтобы узнать в каком отделе работает сотрудник, нужно выполнить обратную операцию:

1. определяем "Номер_отдела" из таблицы СОТРУДНИК

2. по полученному значению находим запись в таблице ОТДЕЛ.

Атрибуты, представляющие собой копии ключей других отношений, называются внешними ключами(foreign key, в дальнейшем будем обозначать FK).

Связи или отношения между таблицами (relationships) позволяют обеспечить целостность данных. Например, связь между таблицами ОТДЕЛ и СОТРУДНИК обеспечивает целостность поля "Номер_отдела" в СОТРУДНИКе (а именно, в таблицу СОТРУДНИК нельзя будет ввести номер отдела, которого нет в ОТДЕЛе). Кроме того, в СУРБД предусмотрены различные настройки каждой связи:

1) Каскадное обновление связанных полей. Эта настройка позволяет автоматически обновлять связанное поле в FK при изменении значения PK. Если данную настройку не применять, то система не позволит обновлять значения PK, пока не произвести соответствующих изменений в FK.

2) Каскадное удаление связанных записей. При установлении данного свойства связи удаление записей из таблицы с PK производится удаление соответствующих записей из таблицы FK. Аналогично, если эта настройка не установлена, то невозможно удаление записей с PK, пока существуют записи с FK.

Связи в СУРБД, как правило, имеют тип «один-ко-многим», т.е. для одной записи в таблице с PK соответствует несколько записей в таблице с FK.

Свойства отношений.

1. Отсутствие кортежей-дубликатов. Из этого свойства вытекает наличие у каждого кортежа первичного ключа. Для каждого отношения, по крайней мере, полный набор его атрибутов является первичным ключом. Однако, при определении первичного ключа должно соблюдаться требование "минимальности", т.е. в него не должны входить те атрибуты, которые можно отбросить без ущерба для основного свойства первичного ключа - однозначно определять кортеж.

2. Отсутствие упорядоченности кортежей.

3. Отсутствие упорядоченности атрибутов. Для ссылки на значение атрибута всегда используется имя атрибута.

4. Атомарность значений атрибутов, т.е. среди значений домена не могут содержаться множества значений (отношения)

В СУБД SQL Server, как и во многих других системах управления базами данных, отношение называется таблицей, кортеж записью и атрибут – полем.

В реляционной базе данных данные хранятся в базовых таблицах. В одной базе данных SQL Server их может быть до двух миллиардов. Для создания новой таблицы используется инструкция CREATE TABLE со следующими опциями:

· имя базы данных, которая будет содержать создаваемую таблицу;

· владелец таблицы;

· имя таблицы, которое должно быть уникальным среди имен базовых таблиц и представлений в этой базе данных, принадлежащих одному владельцу;

· спецификации от 1 до 1024 столбцов;

· ограничение первичного ключа (не обязательно);

· от 1 до 250 ограничений уникальности (не обязательно);

· от 1 до 253 ограничений внешнего ключа (не обязательно);

· одно (или более) ограничение на значения (CHECK), определяющее, какие данные могут быть добавлены в таблицу (не обязательно);

· группа файлов, в которой будет храниться таблица (не обязательно).

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

Следующая инструкция создает базовую таблицу для хранения сведений о клиентах:

CREATE TABLE Customer

( CustID int NOT NULL,

Name char ( 30) NOT MULL,

ShipLine varchar(100),

ShipCity char ( 30),

ShipState char ( 2),

Status char ( 1),

CONSTRAINT CustomerPk PRIMARY KEY (CustID))

Задание к работе

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

Лабораторная работа №2

Создание представлений (Views) (4 часа)

Цель работы

Освоение навыков создания представлений в СУБД SQL SERVER 2014 для выборки данных и демонстрация результатов их работы в клиентском приложении.

Теоретические сведения

Согласно терминологии SQL представление (view) – это объект, который для пользователей и приложений, выполняющих запросы, почти ничем не отличается от таблицы. Представления можно использовать для просмотра и обновления данных, но на самом деле никаких данных они не содержат. Представление лишь предоставляет доступ к.данным одной или нескольких таблиц, на которых оно основано. Вот что можно делать с помощью представлений:

o отбирать из базовой таблицы подмножества строк;

o отбирать из базовой таблицы подмножества столбцов;

o формировать новые вычисляемые столбцы на основе одного или нескольких столбцов базовой таблицы;

o объединять связанные строки нескольких базовых таблиц в одну строку представ­ления;

o объединять наборы строк нескольких базовых таблиц с помощью операции UNION.

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

CREATE VIEW CuatHighCredit AS

SELECT * FROM Customer WHERE CreditLimit >= 5000

Получив такую инструкцию, SQL Server создаст в каталоге базы данных определение представления CustHighCredit. После этого представление CustHighCredit можно будет использовать в SQL-инструкциях как самую обыкновенную таблицу. Например, можно выполнить вот такое обновление его строк:

UPDATE CustHighCredit

SET Status = ‘В’ WHERE ShipCity = ‘Seattle’ AND Status = ‘X’

Однако эта инструкция не так проста, как кажется на первый взгляд. Во-первых, важно понимать, что она обновляет таблицу Customer, лежащую в основе представ­ления CustHighCredit. А во-вторых, она обновляет только строки, соответствующие трем условиям: CreditLimit >= 5000, ShipCity = ‘Seattle’ и Status = 'X'. Выполняя эту инструкцию, SQL Server обрабатывает только строки, удовлетворяющие критерию отбора, заданному в представлении CustHighCredit. Эти строки он проверяет на соответствие условиям, заданным в инструкции UPDATE, и выясняет, какие из них нужно обновить, а какие нет. Как видите, о представлении можно думать как о таблице, содержащей только те строки, которые соответствуют указанному в нем критерию.

Инструкция CREATE VIEW – это одна из самых сложных инструкций языка определения данных в SQL, и поэтому мы будем рассматривать ее по частям. После ключевых слов CREATE VIEW задается имя создаваемого представления, которое при желании можно уточнить именем его владельца. Имя базы данных в инструкции CREATE VIEW не задается, поскольку представления можно создавать только в текущей базе данных. Имена таблиц и представлений, на основе которых создается новое представ­ление, можно уточнять и именами владельцев, и именами фаз данных, эти объекты могут находиться и в других базах данных. Имя представления не может совпадать с именем другой .таблицы или представления, принадлежащих тому же владельцу и хранящихся в той же базе данных.

Примечание. Если не указать имя владельца создаваемого представления, то им станет текущий пользователь.

Следующей частью инструкции CREATE VIEW является необязательный список столбцов представления (их может быть до 1024). Если не задать список столбцов, как в предыдущем примере, в представление войдут столбцы, указанные в предложении SELECT. Предложение SELECT, по своей структуре подобное инструкции SELECT задается после ключевого слова AS. Оно определяет результирующую таблицу представления, т.е. столбцы и строки исходных таблиц и представлений, которые войдут в новое представление. Результирующая таблица представления — это абстрактная таблица, которой вовсе не обязательно должна соответствовать реальная таблица, хранящаяся на диске или в памяти. В следующем примере предложение SELECT определяет результирующую таблицу, содержащую все столбцы таблицы Customer и только те ее строки, в которых лимит кредита больше или равен $5000:

SELECT * FROM Customer WHERE CreditLimit >= 5000

Символ звездочки (*), следующий за ключевым словом SELECT, означает "все столбцы". SQL Server генерирует список столбцов представления при его создании, и если в дальнейшем в исходную таблицу будут добавлены новые столбцы, они не войдут в представление.

Агрегатные функции

Скалярные функции обрабатывают значения из одной строки таблицы или объединения таблиц. В SQL определен и другой тип функций, предназначенных для вычислений на основе значений столбца в целом наборе строк. Такие функции называются агрегатными (статистическими) функциями (aggregate function) или функция­ми столбца (column function). Список всех агрегатных функций SQL приведен в таблице (Рис.4). С функциями AVG, CHECKSUM_AGG, COUNT, MAX, MIN и SUM может использоваться ключевое слово DISTINCT, означающее, что перед вычислением значения функции из набора обрабатываемых ею данных должны быть исключены все повторяющиеся значения.

Таблица 4. Агрегатные функции SQL Server

Функция	Описание
AVG (выражение)	Среднее значение набора непустых значений выражения
CHECKSUM_AGG (выражение)	Контрольная сумма непустых значений выражения
COUNT(*) COUNT(выражение) COUNT(DISTINCT выра­жение)	COUNT(*) возвращает количество строк в заданном наборе строк. В этой форме инструкции COUNT ключевое слово DISTINCT использоваться не может. COUNT (выражение) возвращает количество непустых значений выражения. Если задано ключевое слово DISTINCT, функция COUNT возвращает количество уникальных непустых значений выражения. Все формы функции COUNT возвращают значение типа int
COUNT_BIG(*) COUNT_BIG(выражение) COUNT_BIG(DISTINCT выра­жение)	Функция COUNT_BIG подобна функции COUNT с той разницей, что COUNT_BIG возвращает значение типа bigint
GROUPING (столбец)	Возвращает 1, если агрегируемая строка добавлена оператором CUBE или ROLLUP, в противном случае возвращает 0
МАХ (выражение)	Максимальное значение из всех непустых значений выражения
MIN(выражение)	Минимальное значение из всех непустых значения выражения
SUM(выражение)	Сумма всех непустых значений выражения
STDEV (выражение)	Среднеквадратичное отклонение непустых значений выражения
STDEVР(выражение)	Смещенное среднеквадратичное отклонение непустых значений выражения
VAR {выражение)	Дисперсия непустых значений выражения
VARP (выражение)	Смещенная дисперсия непустых значений выражения

Следующая инструкция SELECT возвращает одну строку с общим количеством клиентов и средней скидкой для них:

SELECT ‘Average discount for’, COUNT(*), ‘ customers is’, AVG(Discount) FROM Customer

Агрегатная функция COUNT(*) возвращает количество строк в исходной таблице, а агрегатная функция AVG(Discount) возвращает среднее значение набора непустых значений заданного столбца.

Со столбцами, допускающими значение NULL, агрегатные функции должны использоваться очень осторожно. Предположим, например, что столбец Discount допускает значения NULL и такое значение имеется, по меньшей мере, в одной строке таблицы Customer. В этом случае следующая инструкция вернет неожиданный результат:

SELECT COUNT(*), AVG(Discount), SUM(Discount)

FROM Customer

Сумма окажется не равной средней скидке, помноженной на количество строк. Дело в том, что функция COUNT(*) сосчитает все строки исходной таблицы, а функции AVG и SUM проигнорируют те строки, в которых столбец Discount имеет значение NULL.

Чтобы избежать такого расхождения данных, можно воспользоваться альтернативной формой функции COUNT, которая тоже пропустит строки с пустым столбцом Discount:

SELECT COUNT(Discount), AVG(Discount), SUM(Discount )

FROM Customer

Возможно также более универсальное и наглядное решение – вовсе исключить из обработки все строки с пустым столбцом Discount при помощи предложения WHERE:

SELECT COUNT(*), AVG(Discount), SUM( Discount )

FROM Customer

WHERE Discount IS NOT NULL

Когда в списке столбцов инструкции SELECT задается агрегатная функция, и при этом в инструкции отсутствует предложение GROUP BY (о котором рассказывается в следующем разделе), агрегатная функция' обрабатывает весь набор строк, определяемый предложением WHERE, и результирующая таблица запроса всегда содержит только одну строку. Если же задать предложение GROUP BY, результирующая таблица будет содержать по одной строке на каждую группу исходных строк, или же, если задано еще и предложение HAVING, - по одной строке на каждую группу, удовлетворяющую условию HAVING.

Если агрегатная функция применяется к пустому набору строк (т.е. не содержащему ни одной строки), ее результатом будет NULL. Исключение составляет только функция COUNT(*), которая для пустого набора строк возвращает ноль.

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

SELECT COUNT(DISTINCT ShipCity) FROM Customer

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

Предложение GROUP BY

Предложение GROUP BY используется для применения агрегатных функций к подгруппам строк, отобранных инструкцией SELECT. Например, следующая инструкция

SELECT ShipCity, COUNT(*) AS "Customer Count",

AVG(Discount) AS "Average Discount"

FROM Customer

GROUP BY ShipCity

возвращает по одной строке на каждую группу клиентов, проживающих в одном городе.

В этом примере ShipCity играет роль столбца группировки, по значениям которого строки таблицы Customer разделяются на группы — отдельная группа для каждого значения столбца ShipCity. Агрегатные функции COUNT и AVG по очереди приме­няются к каждой группе, так что в результирующей таблице оказывается по одной строке на каждую группу. Если столбец группировки допускает пустые значения, для значения NULL тоже создается отдельная группа.

Примечание. В данном примере намеренно не исключены строки с пустыми

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

В предложении GROUP BY указывается список столбцов и скалярных выражений. Например, в следующей инструкции SELECT создается отдельная группа для каждой комбинации вычисляемого столбца DaysToShip и столбца SaleDate:

SELECT SaleDate,

DATEDIFF(Day, SaleDate, ShipDate) AS DaysToShip,

AVG(TotalAmt) AS DaysToShip

FROM Sale

WHERE ShipDate IS NOT NULL

GROUP BY SaleDate, DATEDIFF(Day, SaleDate, ShipDate)

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

Если для отбора строк используется предложение WHERE, оно может исключить строки с некоторым значением столбца (или столбцов) группировки. Если в группу попала ни одна строка, SQL Server не генерирует для нее строку результирующей таблицы. Таким образом, следующая инструкция

SELECT ShipCity, COUNT(*) AS "Customer Count",

FROM Customer

WHERE Discount > .01

GROUP BY ShipCity

сгенерирует таблицу, не имеющую строк с данными для Albany и Seattle

Для того чтобы получить строку для каждой группы, даже если она окажется пустой, поместите за ключевыми словами GROUP BY ключевое слово ALL:

SELECT ShipCity, COUNT(*) AS "Customer Count",

FROM Customer

WHERE Discount > .01

GROUP BY ALL ShipCity

Предложение HAVING

Предложение HAVING используется для отбора строк результирующей таблицы уже после применения к сгруппированным строкам агрегатных функций. Оно похоже на предложение WHERE, служащее для отбора строк перед группировкой. Например, следующая инструкция SELECT возвращает данные о городах, в которых средняя скидка клиентам превысила один процент:

SELECT ShipCity, COUNT(*) AS "Customer Count",

AVG(Discount) AS "Average Discount"

FROM Customer

WHERE Discount IS NOT NULL

GROUP BY ShipCity

HAVING AVG(Discount) > .01

Условие отбора, заданное в предложении HAVING, может содержать столбцы группировки, как например ShipCity, или агрегатные функции, как например AVG(Discount). .

Примечание. Хотя предложение HAVING может быть задано и без предложения GROUP BY, так поступают редко.

Предложение ORDER BY

Предложение ORDER BY служит для сортировки результирующей таблицы инструкции SELECT перед ее возвращением приложению. В этом предложении задается список столбцов и порядок сортировки значений каждого из этих столбцов: по возрастанию (задается ключевым словом ASC и подразумевается по умолчанию) или по убыванию (задается ключевым словом DESC).

Примечание. Предложение ORDER BY не является частью предложения SELECT, включаемого в другие инструкции, оно используется только в инструкции SELECT. Его можно включать в инструкции SELECT, INSERT, но не в определения представлений.

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

SELECT CustId, OrderId, SaleDate, ShipDate,

DATEDIFF(Day, SaleDate, ShipDate)

FROM Sale

WHERE ShipDate IS NOT NULL

ORDER ВY Custld, 5 DESC

В этом примере безымянный пятый столбец, получающийся путем вычисления выражения DATEDIFF(Day, SaleDate, ShipDate), используется для сортировки по убыванию строк с одинаковыми значениями столбца Custld.

Чтобы сделать эту инструкцию более понятной, можно определить для вычисляе­мого столбца псевдоним-и использовать его в предложении ORDER BY:

SELECT CustId, OrderId, SaleDate, ShipDate,

DATEDIFF(Day, SaleDate, ShipDate) AS DaysToShip

FROM Sale

WHERE ShipDate IS NOT NULL

ORDER ВY Custld,

DaysToShip DESC

Задание к работе

Создать представления с помощью мастера SQL Server и языка Transact-SQL для выборки данных и формирования отчетов.

Лабораторная работа №3

Создание клиентского приложения к базе данных (4 часа)

Цель работы

Освоение навыков использования инструментов Microsoft Access 2013 для создания интерфейса пользователя к построенной базе данных в СУБД SQL SERVER 2014.

Теоретические сведения

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

Работа с формами может происходить в трех режимах: в режиме Формы, в режиме Таблицы, в режиме Конструктора. Выбрать режим работы можно при помощи кнопки Вид панели инструментов, Конструктор форм либо с помощью команды меню Вид.

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

В режиме Конструктор можно производить изменение внешнего вида формы, добавление и удаление элементов управления, разработку.

Виды форм. В Access можно создать формы следующих видов:

• форма в столбец или полноэкранная форма;
• ленточная форма;
• табличная форма;
• форма главная/подчиненная;
• сводная таблица;
• форма-диаграмма.

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

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

Табличная форма отображает данные в режиме таблицы.

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

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

Форма с диаграммой.В Access в форму можно вставить диаграмму, созданную Microsoft Graph. Graph является внедряемым OLE-приложением и может быть запущен из Access. С внедренной диаграммой можно работать так же, как и с любым объектом OLE.

Структуры формы. Любая форма может включать следующие разделы:

заголовок формы — определяет верхнюю часть формы и может содержать текст, графику и другие элементы управления;

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

область данных — определяет основную часть формы, содержащую поля, полученные из источника данных;

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

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

Форма может содержать все-разделы или некоторые из них.

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

Окно свойств выделенного объекта содержит следующие вкладки:

Макет — с помощью этих свойств задается макет формы;

Данные — с помощью этих свойств задается источник данных;

События — содержит перечень свойств, связанных с объектом;

Другие — перечень остальных свойств;

Все — перечень всех свойств.

Основные свойства формы:

ü подпись — позволяет задать название формы, которое будет выводиться в области заголовка;

ü режим по умолчанию — определяет режим открытия формы (простая, ленточная, табличная формы);

ü допустимые режимы — свойство, которое определяет, можно ли с помощью команд меню Вид переходить из режима формы в режим конструктора;

ü свойства полосы прокрутки, область выделения, кнопки перехода, разделительные линии, кнопка оконного меню, размеров окна, кнопка закрытия, кнопка контекстной справки, тип границы — определяют, будут ли выводиться эти элементы в окно формы;

ü свойства разрешить добавления, разрещить удаления, разрешить изменения — определяют, можно ли пользователю редактировать данные через форму. Эти свойства могут принимать значения Да/Нет;

ü ввод данных — определяет режим открытия формы и принимает значения Да/Нет. Режим Да — открытие формы только для добавления новых записей. Режим Нет — просмотр существующих записей и добавление новых;

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

Для создания форм в Access используются следующие виды.

Автоформа — автоматизированное средство для создания форм трех стандартных типов: в столбец, ленточная, табличная. При этом в форму вставляются все поля источника данных. ,

Мастер форм — программное средство, которое позволяет создавать структуру одного из трех стандартных типов формы в режиме диалога с разработчиком формы. При этом в форму вставляются выбранные пользователем поля из источника данных.

Конструктор форм — позволяет конструировать форму пользователем в окне конструктора форм.

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

Источником данных формы являются одна или несколько связанных таблиц и/или запросов.

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

Связанный (присоединенный) элемент управления присоединен к полю базовой таблицы или запроса. При вводе значения в связанный элемент управления поле таблицы текущей записи автоматически обновляется. Поле таблицы является источником данных связанного элемента управления.

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

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

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

Задание к работе

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

Лабораторная работа №4

Создание хранимых процедур (4 часа)

Цель работы

Освоение навыков создания хранимых процедур в СУБД SQL SERVER 2014 для выборки данных и демонстрация результатов их работы в клиентском приложении.

Теоретические сведения

Хранимая процедура (stored procedure) — это несколько последовательных инст­рукций Transact-SQL, которые во время ее создания компилируются в специальный формат (план выполнения). Хранимые процедуры — это мощный и гибкий инструмент, использующийся для реализации различных функций администрирования базой данных, управления и обработки данных, например, для создания таблиц, предоставления полномочий или изменения базы данных. Хотя сам язык SQL является непроцедурным языком, его диалект Transact-SQL для SQL Server содержит несколько дополнительных возможностей, включающих использование ключевых слов управления потоком данных Это, позволяет применять в хранимых процедурах SQL Server сложную логику обработки данных и решать с их помощью, самые разнообразные задачи.

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

Для создания хранимой процедуры используется инструкция CREATE PROCEDURE, а для выполнения хранимой про­цедуры — инструкция EXECUTE или соответствующая функция используемого прило­жением программно