Технология создания таблиц. Технология ввода и редактирования данных

⁰

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

1. Проектирование и разработка форм выходных документов (на бумаге), а также алгоритмов получения расчетных данных.

2. Разработка электронной таблицы, т.е. создание заголовка, шапки, внесение формул в расчетные колонки.

3. Ввод данных и получение расчетных значений.

4. Сохранение таблицы на внешнем носителе.

5. Вывод таблицы на печать.

Вычисления в таблицах

Формулы . Все вычисления в электронных таблицах производятся с помощью формул. Любая формула в MS Excel должна начинаться со знаков «равно», «плюс» или «минус». Без этих знаков формула интерпретируется как текст.

Каждая формула состоит из операторов и операндов .

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

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

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

Таблица 1 – Арифметические операторы

Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение ИСТИНА или ЛОЖЬ. Используются следующие операторы сравнения: = (знак равенства), > (знак больше), < (знак меньше), >= (знак больше или равно), <= (знак меньше или равно), <> (знак не равно).

Текстовый оператор конкатенации (амперсанд) используется для объединения нескольких текстовых строк в одну строку и обозначается &.

Для ссылки на диапазон необходимо задать адрес первой и последней ячеек диапазона через двоеточие, например, А1:А5, для ссылки на блок задается адрес верхней левой и нижней правой ячеек блока через двоеточие, например, А1:Е5.

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

; (точка с запятой) – оператор объединения, объединяет несколько ссылок в одну ссылку, например , =СУММ(А2:A10;C2:C10);

(пробел) – оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов, например , (B2:D10 C4:C6).

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

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

Смешанная ссылкана ячейку также использует знак $, например, $A3, B$2, и представляет собой сочетание относительной и абсолютной ссылок. При копировании формулы каждая часть ссылки ведет себя соответственно указанному адресу: абсолютная часть адреса остается неизменной, относительная часть адреса меняется.

Встроенные функции

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

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

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

Таблица 1 – Краткая характеристика категорий функций Excel

Категория	Краткая характеристика
Финансовые	Функции для осуществления типичных финансовых расчетов
Дата и время	Функции для анализа и работы со значениями даты и времени в формулах
Математические	Содержит арифметические и тригонометрические функции, позволяющие производить простые и сложные математические вычисления
Статистические	Функции для выполнения статистического анализа диапазонов данных
Ссылки и массивы	Функции для осуществления поиска в списках или таблицах, нахождения ссылок к ячейке
Работа с базой данных	Функции для работы со списками
Текстовые	Функции для выполнения действий над строками текста
Логические	Функции для проверки выполнения одного или нескольких условий
Проверка свойств и значений	Функции для проверки свойств и значений данных, вводимых в ячейки
Инженерные	Функции для выполнения инженерного анализа. Их можно разделить на три группы: § Функции для работы с комплексными числами § Функции для преобразования чисел из одной системы счисления в другую § Функции для преобразования величин из одной системы мер и весов в другую
Информационные	Для определения типа данных, хранимых в ячейке. Проверяют выполнение какого-то условия и возвращают в зависимости от результата значение ИСТИНА или ЛОЖЬ.

Следует заметить, что пользователь может разрабатывать и собственные функции с помощью языка Visual Basic For Applications. В этом случае формируется категория «Определенные пользователем».

ТЕХНОЛОГИЯ РАБОТЫ В ЭЛЕКТРОННОЙ ТАБЛИЦЕ

Создание диаграммы

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

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

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

Ось х – задает параметры, необходимые для разметки оси х. В нашем примере следует указать ячейки электронной таблицы, содержащие разметку оси х: кв. 1, кв. 2, кв. 3 и кв. 4.

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

Обобщенная технология работы

Проектирование электронной таблицы

Объединение электронных таблиц

Макросы как средство автоматизации работы

Электронная таблица для поддержки принятия решений

Хотя работа в каждой электронной таблице имеет свои особенности, можно говорить о некоторой обобщенной (усредненной) технологии работы с ней. Схема такой технологии приведена на рис. 14.8.

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

Рис. 14.8. Обобщенная технология работы с электронной таблицей

На этапе 2 производится работа с данными, которая состоит в исследовании сформированной таблицы. Такое исследование может быть связано с использованием определенных математических моделей (моделированием), методов одновременной работы с несколькими таблицами и методов работы с базами данных.

Математические модели помогают пользователю на основе имеющейся таблицы получить новую информацию решением таких типовых задач компьютерного моделирования: "Что будет, если?", анализ чувствительности и др. Решение задач типа: "Что будет, если?" – дает возможность пользователю узнать, как изменятся выходные параметры при изменении одной или нескольких входных величин (условий). Расширением таких задач являются задачи анализа чувствительности, позволяющие определить, как изменится решение модели при изменении одной или нескольких входных величин с заданным шагом в определенном диапазоне значений. Обратной по отношению к задаче: "Что будет, если?" – является задача: "Как сделать, чтобы?". Она возникает в случае, если ваша цель – достижение определенного значения модели и вы ищете значения входных параметров, обеспечивающих достижение этой цели. Различные виды анализа данных, содержащихся в исходной таблице, можно проводить с использованием встроенных функций и процедур. Так, входящие в состав электронной таблицы статистические функции могут использоваться в статистическом анализе или для прогноза содержащихся в таблице данных. Использование финансовых функций позволяет произвести анализ эффективности планируемых капитальных вложений, рассчитать стоимость ценных бумаг или размер амортизационных отчислений. Для решения оптимизационных задач используются специальные модели математического программирования.

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

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

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

Этап 4 обеспечивает вывод результирующих данных на печать. При этом результаты могут быть распечатаны в табличном виде или в виде графических диаграмм.

ПРОЕКТИРОВАНИЕ ЭЛЕКТРОННОЙ ТАБЛИЦЫ

Рассмотрим проектирование электронных таблиц. С этой целью проведем формирование прогноза финансовой деятельности некоторой компании за 5 лет (табл. 14.2). Доход в любом году определяется как произведение объема продаж в натуральном выражении на продажную цену. Размер прибыли при этом определяется как разность между доходом и суммой расходов данного года.

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

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

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

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

Далее вводятся данные первого года (в нашем примере 1995), которые частично задаются как числовые данные (объем продаж, продажная цена,), а частично – как формулы. Так, например, в ячейку В5 записывается формула для вычисления дохода +В3*В4, а в ячейку В7 – формула для вычисления прибыли +В5-В6. Одновременно вводятся числовые данные, выражающие прогнозные допущения.

Таблица 14.2. Финансовый прогноз деятельности компании

А	В	С	D	Е	F
Прогноз деятельности компании

Объем продаж, шт.
Цена	$2.00	$2.10	$2.21	$2.32	$2.43
Доход	$20000	$24780	$30702	$38040	$47132
Расходы	$15000	$15750	$16537	$17364	$18232
Прибыль	$5000	$9030	$14165	$20676	$28900

Прогнозные допущения
Рост объема продаж	18.00%
Рост цен	5.00%

Наиболее сложный момент проектирования нашей таблицы – это ввод формул в столбец второго года (1996). Эти формулы учитывают результаты первого года и, кроме того, отражают прогнозные допущения. Так, например, объем продаж в 1996 г. определяется как объем продаж 1995 г., умноженный на процент роста, указанный в прогнозных допущениях (рис. 14.9).

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

Рис. 14.9. Использование абсолютных и относительных адресов

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

Таким образом, остальные столбцы (Д, Е, F) заполняются простым копированием формул, содержащихся в столбце С. Команда копирования при этом автоматически подстроит содержащиеся в них относительные адреса ячеек. В заключение вы можете защитить созданную электронную таблицу от внесения изменений (кроме ячеек, содержащих значения прогнозных допущений).

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

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

Таблица 14.3. Электронная таблица для финансового прогнозирования в режиме просмотра формул

ОБЪЕДИНЕНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ

При работе с электронными таблицами часто возникает необходимость их объединения. Среди инструментов объединения электронных таблиц отметим:

организацию межтабличных связей;

консолидацию электронных таблиц или их частей;

объединение файлов.

Что такое импорт, экспорт и присоединение данных?

Чем отличается импорт данных от присоединения данных?

В каких случаях целесообразно применение импорта и в каких - присоединение данных?

Что такое интеллектуальный анализ данных?

В каких сферах деятельности применяют И АД?

Каковы основные этапы процесса И АД?

С какой целью проводится анализ информации?

Для чего применяют технологию трансформации данных?

Из каких операций состоит задача поиска закономерностей?

Чем обусловлено появление ИАД?

Гла ва 6 ТЕХНОЛОГИЯ РАЗРАБОТКИ ТАБЛИЦ БАЗ ДАННЫХ

6.1. Разработка физической модели данных

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

Затем можно приступить к созданию таблицы в среде Access. Во всех имеющихся версиях этой системы последовательность дей-ствий практически одинакова. Существует лишь некоторая разни-ца в оформлении диалоговых окон. Рассмотрим примеры созда-ния таблиц с использованием версии Microsoft Access 2000.

6.2. Создание таблицы с помощью конструктора таблиц

Для создания таблицы необходимо выполнить следующие дей-ствия:

=> включить компьютер и загрузить программное обеспечение - Windows и Access;

=> в появившемся диалоговом окне дважды щелкнуть кнопкой мыши по меню Файл и выбрать команду Создать;

=> в диалоговом окне Создание (рис. 6.2) активизировать пере-ключатель Базы данных и щелкнуть мышью по кнопке [ОК];

=> в диалоговом окне Файл новой базы данных (рис. 6.3) при-своить имя файлу, указав при этом имя директории (папки), г Де будет храниться БД, и щелкнуть мышью по кнопке Соз-дать;

=> в диалоговом окне База данных активизировать закладку Таб- лица и выбрать команду Создать в режиме конструктора;

=$ в появившемся диалоговом окне конструктора таблиц (рис. 6.4) создать структуру таблицы в соответствии с установ-ленными составом и характеристиками полей.

Конструктор таблиц содержит четыре информационных блока: Имя поля; Тип данных; Описание; Свойства поля.

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

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

имя поля не должно начинаться с пробела;

несмотря на то что имя поля может содержать до 64 (Access 2000) символов, его следует задавать минимальным числом сим-волов с целью обеспечения минимизации объема памяти и вре-мени поиска информации;

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

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

Не допускается повторение имен в таблице.
Технология занесения данных в строки информационных бло-
ков конструктора таблиц аналогична технологии работы с табли-
цами в текстовом редакторе Word. Причем заполнение информа-
ционных блоков следует производить последовательно для каждо-
го поля в следующем порядке:

=> ввести имя поля;

=> выбрать тип данных;

=> ввести в строку блока Описание комментарий, поясняющий характер вводимых значений в ячейку данного поля (в дальней-шем при заполнении таблицы этот комментарий выводится в стро-ку подсказки в нижней части экрана);

=> задать свойства поля;

=> повторить указанные действия для всех остальных полей таб-лицы.

В конструкторе таблиц Microsoft Access выбор типа данных мож-но осуществить через список. Приведем типы данных, входящих в этот список.

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

Мемо. Поле данных этого типа, называемое полем текстовых примечаний, предназначено для содержания текстовой информа-ции длиной более 255 символов; в Access 2000 - до 65 535 симво-лов. Этот тип данных отличается от текстового тем, что представ-ляет собой не сами данные, а ссылки на соответствующие блоки данных, хранящиеся отдельно, что значительно ускоряет обра-ботку таблиц. При этом поля данных типа Мемо не могут быть ключевыми или индексными.

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

Дата/время. Данные такого типа предназначены для указания даты или времени, характеризующих конкретную запись табли-цы, например даты поступления товара на склад или времени начала и окончания работы пользователя в сети Internet. В поле данных этого типа можно вводить даты с 100 по 9999 годы.

Денежный. Данные этого типа аналогичны данным числового типа и отличаются от них только характеристиками вводимых чи-сел. Точность числа данных такого типа не превышает четырех знаков после запятой, а целая часть может содержать до 15 деся-тичных разрядов. В конце числа может быть проставлено обозначе-ние валюты.

Счетчик. Поле содержит уникальный (не повторяющийся) но-дер записи таблицы БД. Значения этого поля не обновляются. Таб-лица Access 2000 может содержать 2 млрд записей.

Логический. Параметры поля данных этого типа могут прини-мать только два значения, интерпретированные как Да/Нет, Ис-тина/Ложь, Включено/Выключено. Поля данных логического типа не могут быть ключевыми, но могут быть индексными.

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

Гиперссылка (Hyperlink). Позволяет вставлять в поле адрес, с помощью которого можно ссылаться на любой файл или фраг-мент любого файла, находящегося в том же компьютере, где на-ходится таблица БД, или в любом компьютере локальной сети или в сети Internet.

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

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

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

Рекомендуется обязательно вводить комментарий, особенно в тех случаях, когда в обозначении имени или подписи поля содер-жится недостаточно информации для правильного ввода данных. Например, при вводе характеристик технологического оборудо-вания в поле Z) max (см. рис. 4.3) пользователь должен знать, что максимальный диаметр заготовки, которая может быть обработа-на на станке данной модели, указан в миллиметрах, т.е. в этом случае в строке блока Описание можно сделать следующий ком-ментарий: максимальный диаметр заготовки в мм.

После ввода комментария необходимо перейти к блоку Свой- ства поля в разделе Общие и задать полю требуемые свойства. В кон-структоре таблиц каждому полю в зависимости от типа данных автоматически (по умолчанию) задается определенный набор свойств. Конструируя таблицу, эти свойства изменяют в соответ-ствии с конкретными требованиями к данным.

На рис. 6.5 показан фрагмент структуры таблицы, созданной в режиме конструктора, с описанием свойств одного из полей.

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

При задании имени таблицы необходимо учитывать следую-щие рекомендации:

имя должно отражать содержание данных в таблице (класс объектов);

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

имя не должно начинаться с пробела;

в одном файле БД не должно быть таблиц с одинаковыми именами.

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

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

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

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

=> открыть созданную ранее таблицу в режиме Конструктор;

=> выделить поле, которое повторяется в другой таблице;

=> скопировать выделенное поле (со всеми его свойствами) в буфер обмена;

=> вставить из буфера обмена характеристики поля в соответ-ствующую строку конструктора таблиц.

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

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

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

=> на панели инструментов окна базы данных соответствую-щим значком активизировать команду Схема данных (рис. 6.6);

=> в открывшееся окно построителя схемы данных (рис. 6.7) ввести главную и подчиненные таблицы;

=> связать таблицы по одинаковому полю.

На рис. 6.8 показано окно построителя схемы данных, в кото-ром установлена связь один - ко многим между двумя таблицами САПР ТП «ЛАЗЕР 2000». В процессе создания связи включен па-раметр Обеспечение целостности данных, при котором не допуска-ется произвольное удаление или изменение записей в главной таблице.

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

Итак, состав таблиц базы данных установлен, структура каж-дой таблицы разработана, определены и установлены связи меж-ду таблицами. Можно приступать к заполнению таблиц данными.

* В информационной системе могут быть таблицы, не связанные с другими таблицами БД.

Рис. 6,8. Окно схемы данных с установленной связью один - ко многим

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

уменьшение вероятности ошибок оператора;

удобство организации самого процесса ввода данных.

Контрольные вопросы

Из каких информационных блоков состоит конструктор таблиц и в какой последовательности следует их заполнять?

Из скольких символов может состоять имя поля?

Может ли имя поля начинаться с пробелов?

Какие символы нельзя использовать при обозначении имени поля?

В чем состоит отличие данных текстового типа отданных типа Мемо?

В чем заключается различие числового и денежного типов данных?

В каких случаях следует применять данные типа OLE?

В каких случаях следует применять данные типа гиперссылки?

В каких случаях полю присваивают свойство ключевого?

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

В каких случаях полю присваивают свойство обязательного?

Какие таблицы называются главными и подчиненными?

Что такое обеспечение целостности данных?

Гл а ва 7

ТЕХНОЛОГИЯ РАЗРАБОТКИ ЗАПРОСОВ В СУБД MICROSOFT ACCESS

7.1. Виды запросов при работе сданными

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

Обработка информации, содержащейся в таблицах БД, осуще-ствляется с помощью запросов, которые представляют собой не-который набор команд, предназначенных для поиска и обработки информации в таблицах по заданным пользователем условиям (зна-чениям полей). В системе Access в зависимости от выполняемых действий можно создавать следующие виды запросов: на выпол-нение действий (выборку); обновление; добавление; удаление; создание таблицы.

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

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

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

7.2. Способы создания запросов

В системе Access пользователю предлагается два способа созда-ния запросов:

конструирование в режиме Меню;

программирование в режиме SQL.

Создание запроса в режиме Меню выполняется с помощью системы мастеров.

В этом случае пользователь, указав параметры запроса в окне конструирования, использует предоставляемые мастером возмож-ности.

Система Access в этом случае автоматически формирует код программы в виде специальной последовательности команд на структурированном языке формирования запросов - SQL.

При программировании в режиме SQL пользователь должен описать все выполняемые при запросе действия с помощью соот-ветствующих команд на языке SQL.

Возможности конструирования запросов в режиме Меню дос-таточны для решения практически любых задач обработки ин-формации в таблицах БД.

Технологию конструирования запросов рассмотрим на приме-ре СУБД Access 2000, в которой проектирование запроса может выполняться двумя способами: самостоятельно и с помощью ма-стеров. При любом способе проектирования необходимо открыть окно Запросы объектов БД (рис. 7.1), активизировав кнопку [Зап-росы]*.

Рис. 7.2. Окно начального диалога при создании запроса

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

щелкнуть мышью по кнопке [Создать] в строке меню окна эле-ментов БД;

щелкнуть мышью по значку [Создание запроса в режиме кон-структора] ;

щелкнуть мышью по значку [Создание запроса с помощью мастера].

При выборе первого способа открывается окно создания запро-са (рис. 7.2), в котором пользователю предлагаются Самостоятельное создание нового запроса и Создание запроса с помощью конструкто-ра в режимах: Простои запрос; Перекрестный; Повторяющиеся за- писи; Записи без подчиненных. производительности и оптимизации...

Практическое занятие №2

Создание, изменение и удаление таблиц. Ввод исходных данных

Цель работы: научиться создавать, изменять, удалять таблицы в SQL Server 2008 с помощью запросов и с использованием инструментальных средств с графическим интерфейсом пользователя.

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

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

Имя создаваемой таблицы.

Имена составляющих ее столбцов.

Тип данных для каждого столбца.

Кроме этих обязательных параметров большинство производителей предусматривают дополнительные возможности для оператора CREATE TABLE, поддерживаемые стандартом SQL-92.

Оператор Create table

Упрощенный синтаксис оператора CREATE TABLE имеет следующий вид:

С REATE TABLE
[, ])

Оператор CREATE TABLE создает новую таблицу с именем tbl_name в текущей базе данных. Чтобы создать таблицу в произвольной базе данных имя таблицы должно быть указано как db_name.tbl_name.

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

Атрибут IF NOT EXISTS подавляет вывод сообщения об ошибке при попытке создать таблицу с уже используемым именем, но новая таблица при этом не создается. Описание столбца col_declaration начинается с имени столбца col_name и его типа col_type и может сопровождаться несколькими необязательными ключевыми словами. В качестве типов столбца можно выбирать любой тип поддерживаемый СУБД.

Атрибут NULL | NOT NULL определяет, допустимы ли для столбца значения NULL.

Атрибут DEFAULT указывает значение, присваиваемое столбцу в случае отсутствия явно заданного значения при вставке. Если значение по умолчанию не указано, то для столбцов, принимающих значения NULL, значением по умолчанию будет NULL, для строковых столбцов - пустая строка, для численных столбцов - ноль.

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

В MS SQL Server столбец идентификатор создается с помощью атрибута IDENTITY в описании столбца:

column_definition::=
column_name [ NULL | NOT NULL ] [ IDENTITY [(seed ,increment) ]]

При добавлении в таблицу новой строки компонент Database Engine формирует для этого столбца последовательное уникальное значение.

Для каждой таблицы можно создать только один столбец идентификаторов. При создании столбца можно указать как начальное значение (параметр seed в описании столбца), так и приращение (параметр increment в описании столбца). Если эти параметры не указаны, применяются значения по умолчанию (1,1).

Добавление новых столбцов в таблицу осуществляется с помощью предложения ADD в операторе ALTER TABLE. Синтаксис оператора ALTER TABLE:

ALTER TABLE < table_name > ADD [, < col_definition >,…. ]

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

Для удаления столбцов таблицы используется предложение DROP в инструкции ALTER TABLE:

ALTER TABLE

Ход работы

ЗапуститеПуск – Программы – Microsoft SQL Server 2008 – Среда

В окне «Соединение с сервером» нечего не менять! (Должен быть выбран режим «Проверка подлинности Windows »). Нажать кнопку Соединить.

В левом окне на строчке «Базы данных» щелкнуть правой кнопкой мыши и выбрать «Создать базу данных».

В строке «Имя базы данных» ввести имя «Сессия». Нажать ОК.

В левом окне раскрыть ветку «Безопасность», нажав на «+».

Выбрать строку «Имена входа» и щелкнуть правой кнопкой мыши. Выбрать «Создать имя входа».

В строке «Имя входа» ввести свою фамилию.

Выбрать режим «Проверка подлинности SQL Server », в строках «Пароль» и «Подтверждение пароля» ввести пароль 123.

Снять галочку с поля «Требовать использование политики паролей».

В строке «База данных по умолчанию» выбрать созданную вами базу.

В правой поставьте галочку в строке «sysadmin ». Нажмите ОК.

Закройте SQL Server Management Studio Express.

Запустите SQL Server Management Studio Express (SSMS ), подключитесь к своему экземпляру и откройте окно для создания нового запроса.

Для создания таблицы воспользуйтесь инструкцией CREATE TABLE .

Создайте с помощью этой инструкции следующие таблицы:

Таблица «Студенты» состоит из следующих столбцов:

ID _Студент – тип данных INTEGER столбец идентификации;

Номер_группы тип данных INTEGER , длина 6;

ID _Дисциплина - тип данных INTEGER столбец идентификации;

Наименование - тип данных VARCHAR , длина 40;

ID _Дисциплина - тип данных INTEGER ;

Семестр – тип данных INTEGER ;

Количество_часов - тип данных INTEGER ;

ID _преподаватель - тип данных INTEGER .

Создайте с помощью средств графического интерфейса SSMS следующие таблицы:

ID _Студент - тип данных INTEGER , столбец идентификации;

ID _План - тип данных INTEGER , столбец идентификации;

Оценка - тип данных INTEGER ;

Дата_сдачи - тип данных DATETIME ;

ID _Преподаватель - тип данных INTEGER ;

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

ID _Преподаватель - тип данных INTEGER , столбец идентификации;

Фамилия – тип данных CHAR , длина 30;

Имя - тип данных CHAR , длина 15;

Отчество - тип данных CHAR , длина 20;

Должность - тип данных CHAR , длина 20;

Кафедра - тип данных CHAR , длина 3;

Адрес – тип данных CHAR , длина 30;

Телефон - тип данных CHAR , длина 8;

На все столбцы таблицы, кроме столбцов Адрес и Телефон, наложены ограничения, запрещающие ввод строки при неопределенном значении столбца.

Измените таблицу «Кадровый_состав» с помощью инструкции ALTER TABLE добавив в нее столбцы:

Зарплата - тип данных INTEGER ;

Налог - тип данных INTEGER ;

Получено – вычисленный столбец (Зарплата - Налог).

Заполните таблицу базы данных «Сессия» следующими данными (недостающие столбцы заполните произвольными данными):

Студенты

Контрольные вопросы:

Приведите обобщенный синтаксис команды CREATE TABLE .

Что определяет ключевое слово DEFAULT ?

Что такое столбец идентификации, и каким ключевым словом он задается?

Какое назначение у опции NULL и NOT NULL ?

Что такое вычисленные столбцы? Приведите синтаксис вычисленного столбца.

Технология разработки однотабличных форм для первого этапа загрузки базы данных

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

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

Рассмотрим технологию разработки однотабличной формы для ввода и корректировки данных на примере таблицы ВИД ПЕЧАТИ. В результате загрузки данных по видам печати формируются записи только таблицы ВИД ПЕЧАТИ (объект загрузки). Таблица ВИД ПЕЧАТИ не является подчиненной другим таблицам. Поэтому в процессе загрузки записей не устанавливаются связи с записями других таблиц. Таким образом, в подсхему для формы включается только одна таблица ВИД ПЕЧАТИ.

Определим структуру экранной формы, которую назовем ВИД ПЕЧАТИ. Форма ввода данных в таблицу ВИД ПЕЧАТИ должна содержать наряду с обычным реквизитом (описательным НП - наименование вида печати) большое поле ОП (описания вида печати) для размещения текста. Тип поля ОП - MEMO. Ключевой реквизит КП (код вида печати) в форму не помещается, так как он имеет тип `Счетчик" и наращивается автоматически для каждой новой записи.

Таким образом, структуру формы ВИД ПЕЧАТИ определяют:

тип формы - однотабличная (простая),

источник записей формы - таблица ВИД ПЕЧАТИ,

наличие, кроме обычного поля, большого поля для текста описания.

В области данных будут размещаться все реквизиты (за исключением уникального ключа) таблицы ВИД ПЕЧАТИ. Сконструированная экранная форма ввода/вывода для работы с данными таблицы ВИД ПЕЧАТИ представлена на Рис 3. Аналогично разработаны остальные экранные формы, соответствующие первому этапу загрузки: ФОРМА СОБСТВЕННОСТИ, СПЕЦИАЛЬНОСТЬ, ВИД ПЕЧАТНОЙ ОСНОВЫ, ВИД ПРОДУКЦИИ, УСЛУГА, ТИП ОРГАНИЗАЦИИ, СЫРЬЕ И ОБОРУДОВАНИЕ.

Рисунок 3 -- Форма ввода-вывода для работы с данными таблицы ВИД ПЕЧАТИ

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

На втором этапе загрузки заполняются одновременно таблицы НАИМЕНОВАНИЕ ОРГАНИЗАЦИИ-РЕКВИЗИТЫ ОРГАНИЗАЦИИ и ГРУППА-СТУДЕНТ. Для заполнения таблиц второго этапа загрузки используются составные многотабличные формы.

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

После выполнения перечисленных пунктов осуществляется конструирование экранных форм средствами конкретной СУБД.

Рассмотрим технологию разработки на примере составной формы для одновременной загрузки и работы с данными двух таблиц - НАИМЕНОВАНИЕ ОРГАНИЗАЦИИ и РЕКВИЗИТЫ ОРГАНИЗАЦИИ (объект загрузки), которые связаны в схеме данных одно-многозначными отношениями.

Документами-источниками такой формы являются «Список организаций печати» и «Список организаций-поставщиков». С этих документов загружаются одновременно две таблицы: НАИМЕНОВАНИЕ ОРГАНИЗАЦИИ и РЕКВИЗИТЫ ОРГАНИЗАЦИИ.