2017-12-14
493
Создание таблиц и схемы данных.
1.Краткие теоретические сведения
О реляционных БД
|
|
|
|
циальное поле, например, поле
КодТовара - к таблице Товар
(см. рис. 4.1).
При связывании таблиц клю-
Рис. 4.1.
чевое поле 1-ой таблицы может включаться во 2-ую таблицу, для которой оно называется вторичным ключом, в отличие от основного
первичного ключа 2-ой таблицы (см. таблицы Отделы и Сотрудни- ки на рис. 4.7 и рис. 4.8).
Создание таблицы БД в режиме конструктора
Создание макета таблицы БД в режиме конструктора наиболее универсальный и хорошо контролируемый пользователем способ. Кроме этого, таблицу можно создавать в режиме таблицы, на основе шаблона и импорта внешних данных.
Пример 1. В СУБД Access создать макет таблицы (см. рис. 4.7), представляющей отношение Отделы (КодОтдела, НазвОтдела).
 |
Рис. 4.2. Общий вид окна Access
При первом входе в Access следует щелкнуть (нажать и быстро от- пустить левую клавишу мышки) по пиктограмме Новая база данных, ввести имя файла БД и щелкнуть по кнопке Создать. Откроется об- щий вид окна приложения Access с Таблицей1 (см. рис. 4.2). Затем следует щелкнуть по кнопке Режим, а потом выбрать Конструктор. В появившемся окне Сохранение надо ввести имя таблицы Отделы, нажать OK, и конструктор готов к созданию этой таблицы.
|
|
|
В 1-ую строку макета таблицы Отделы (см. рис. 4.3) вводят имя первичного ключа - КодОтдела и сразу выбирают тип данных - здесь
подойдѐт числовой (используется для всех чисел, кроме денежных сумм). В разделе Свойства поля указывают Размер поля: целый – от- делов не много (до 8). Полезно для свойства Условие на значение задать, например, выражение [ КодОтдела ]>0 And [ КодОтдела ]<9
(или >0 And <9 без указания поля), ко- торое задаѐт, что число отделов больше 0 и меньше 9. В пункте Обязательное поле выбирают Да. Следует отметить, что некоторые поля полезно индексировать, т.е. связать с полем вспомогательный
«вектор» номеров строк, облегчающий поиск и сортировку записей.
Во 2-ую строку макета таблицы вводят имя поля: НазвОтдела. Выбирают тип
поля – текстовой, а в свойствах поля
Рис. 4.3.
указывают длину, например, 30 (по самому длинному из названий от- делов), и отмечают, что это поле обязательное – оно не должно оста- ваться незаполненным. Этим завершается создание макета таблицы Отделы. Закрывая окно таблицы (нажав 
), следует подтвердить сохранение таблицы.
Кроме типов, выбранных выше, используют такие типы данных: Денежный – он точен, его применяют для финансовых расчѐтов.
Логический – имеет значения «Истина/Ложь», «Да/Нет», «Вкл/Выкл».
Дата/Время – значения дат и/или времени; допускают вычисления.
Объект OLE – для подключения объектов других приложений. Гиперссылка – для ссылки на Web-страницу, файл, адрес E-mail. Поле Memo – для хранения текста большого объѐма, комментария.
Счѐтчик – имеет натуральное значение, автоматически увеличиваю- щееся, когда вставляется новая запись; используется как ключ.
Раздел конструктора Свойства поля содержит, кроме упомянутых, и другие средства, позволяющие не допустить в таблицы БД невер-
ные данные. К таким средствам относятся: проверки на совпадение, обязательные поля, значения по умолчанию, маски ввода для типов ДатаВремя и текстовый (см. замечание перед разделом 2.Задание), подстановки (мастерподстановок использован в решении примера 2).
Пример 2. В СУБД Access создать таблицу (отношение) Сотрудники (КодСотрудника, ФамилияИО, КодОтдела), связанную с таблицей Отделы из примера 1.
Связи таблиц БД
Особенность примера 2 в том, что каждый из нескольких сотрудни- ков связан со своим отделом. Такую связь называют один ко многим
(и обозначают 1:µ). Для еѐ реализации в БД КодОтдела указывается
в каждой записи таблицы Сотрудники, т.е. для каждого сотрудника.
Также существует связь один к одному (она обозначается 1:1), ко-
гда каждому значению атрибута 1-ой таблицы соответствует одно значение атрибута 2-ой таблицы. Например, можно выделять началь- ников отделов в отдельную таблицу. Тогда отдел и начальник отдела являют пример такой связи. Эту связь не обязательно реализовывать специально, достаточно в таблице отделов добавить новый столбец НачальникОтдела.
Встречается связь таблиц многие ко многим (обозначается
µ:µ),
например, именно так связаны Товары и Поставщики. Каждый товар могут поставлять различные поставщики, и наоборот. Эта связь мо- делируется в реляционных БД введением дополнительной таблицы ТоварыПоставщиков. В ней указывают ключ, составленный из двух полей КодТовара и КодПоставщика, а каждая из таблиц Товары и Поставщики реализует с дополнительной таблицей связь один ко многим. В Access 2007 связь многие ко многим можно организовать с использованием множественного поля. Там рассмотренный меха- низм связи через дополнительную таблицу скрыт от пользователя.
|
|
|
Решение примера 2. Итак, создавая макет таблицы Сотрудники примера 2, действуют как и раньше, но после ввода имени КодОтде- ла поля вторичного ключа в типе данных целесообразно выбрать Мастер подстановок. В нѐм надо указать таблицу Отделы и перене-
сти из неѐ ключевое поле КодОтдела (нажав световую кнопку со значком >) и (для удобства) НазваниеОтдела. При заполнении данны- ми таблицы Сотрудники наименование НазваниеОтдела будет предла- гаться для заполнения значением КодОтдела соответствующего столбца.
Рекомендация. В первую очередь создавайте и заполняйте таблицы, данные которых используют (подставляются в) другие таблицы.
О проектировании и нормализации БД. Схема данных.
Для проектирования БД необходимо чѐтко представлять (а лучше и иметь описание) выбранной предметной области. Перед использова- нием СУБД пользователь должен определить то, какие таблицы бу- дут в его БД, какие данные в них хранить, как связывать таблицы.
При проектировании БД стремятся избежать дублирования данных, устранить нежелательные зависимости между атрибутами и анома- лии, которые могут приводить к нарушениям целостности данных – потерям данных и противоречиям в них при модификации данных в отдельных таблицах. Нормализация таблиц помогает устранить опре- делѐнные виды аномалий модификации. Выделяют шесть нормаль- ных форм, которым должна удовлетворять любая таблица БД.
Первая нормальная форма (1НФ) требует, чтобы таблица удовле- творяла определению отношения (см. выше) и, в частности, чтобы данные каждого столбца соответствовали только одному из атрибу- тов отношения, а в каждой ячейке находилось единственное значе- ние. Если это так, то говорят, что таблица находится в 1НФ.
Вторая нормальная форма (2НФ) требует, чтобы не ключевые столбцы таблицы (находящейся в 1НФ) однозначно определялись всеми компонентами составного ключа, а не некоторыми из них.
Третья нормальная форма (3НФ) требует, чтобы таблица (находя- щаяся в 2НФ) не содержала транзитивно зависящих атрибутов (т.е. зависящих 1-ый от 2-го, 2-ой от 3-го и т.д.).
|
|
|
Нормальная форма Бойса-Кодда требуют наличия в таблице (нахо- дящейся в 3НФ) только одного потенциального ключа. Четвѐртая и пятая нормальные формы связаны с преобразования- ми таблиц для устранения в них многозначных зависимостей.
Эти требования необходимо учитывать при разработке БД.
Задача для разработки БД
Для служебных целей сотрудникам отделов фирмы выданы мо- бильные телефоны (некоторым - более одного телефона). Периодиче- ски поступает таблица («распечатка») с указанием полных данных о продолжительности, времени и стоимости каждого вызова с номеров служебных телефонов сотрудников. Требуется разработать БД
«ТфСвязь», по запросам к которой можно анализировать затраты на связь и то, как сотрудники используют эту телефонную связь.
Решение задачи требует использовать таблицы, рассмотренные в примерах 1 и 2. Эти таблицы представляют отношения:
Отделы (КодОтдела, НазвОтдела),
Сотрудники (КодСотрудника, ФамилияИО, Должность, КодОтдела).
Третья таблица – это таблица-«распечатка». Она представляет сле- дующее отношение: ТфРазговоры (НомВызова, НомТелефона,
ДлитТфР, СтоимТфР, ДатаВремяВызова),
где ДлитТфР, СтоимТфР - соответственно длительность и стоимость телефонного разговора.
Необходима также таблица номеров телефонов – они закреплены за сотрудниками. Таблица представляет отношение:
Телефоны (НомТелефона, КодСотрудника, ДатаЗакрепления).
Важно отметить, что при увольнении сотрудника КодСотрудника следует очистить - заменить «пустым» значением null, которое при- писывается резервным номерам телефонов. Если его номер дадут но- вому сотруднику, то, чтобы судить о времени пользования номером новым сотрудником, полезно добавить поле ДатаЗакрепления.
Между таблицами Отделы и Сотрудники есть связь один ко мно- гим по полю КодОтдела. Таблицы Сотрудники и Телефоны также имеют связь один ко многим по полю КодСотрудника. И такая же связь по полю КодТелефона существует между таблицами Телефоны и ТфРазговоры. Макеты этих таблиц и связи составляют схему дан-
ных БД. Таблицы удовлетворяют приведѐнным нормальным формам, и можно в Access, создав макеты всех таблиц, построить схему дан- ных и заполнить эти таблицы данными.
Построение схемы данных
После создания макетов всех таблиц БД могут оказаться намечен- ными связи, использованные в мастере подстановок.
Но они требуют уточнения, а возможно потребуется установить и другие связи между таблицами.
Для этого на вкладке Работа с базами данных в группе Пока- зать или скрыть надо щелкнуть по кнопке Схема данных. Затем необходимо поместить в окно схемы данных все связываемые таблицы. Для этого в окне До-
бавление таблицы (рис. 4.4) на-
до указать таблицу или, нажав
Рис. 4.4.
Ctrl, несколько таблиц, а затем нажать кнопку Добавить. Завершив добавление, следует Закрыть окно Добавление таблиц. Для получе- ния большей наглядности схемы данных расположение и размеры макетов таблиц в окне Схема данных можно настроить как у обычных окон.
Если связь между полями таблиц намечена (есть соединительная
линия без маркировки 1:1 или 1:µ), то еѐ следует уточнить, кликнув
(дважды щелкнув) по соединительной линии. В открывшемся окне
Изменение связей (рис. 4.5) следует уточнить связываемые поля, под-
твердить требование Обеспечения це- лостности данных, а также выбрать Каскадное обновление и Каскадное удаление связанных полей. Опция Каскадное обновление (удаление) оз- начает, что при изменении (удалении) ключевого поля в главной таблице оно автоматически будет изменяться
Рис. 4.5. 48
(удаляться) во всех подчинѐнных таблицах. Это исключает появление в поле не одинакового значения. После щелчка по кнопке OK окно
закрывается и на линии связи появляется маркировка 1:1или
На рис. 4.6 показана схема данных для разбираемой задачи.
1:µ.
Для установления (не намеченной ранее) связи таблиц надо навести курсор на одно из связываемых полей, нажать левую клавишу мышки и, не отпуская еѐ, перевести курсор на второе связываемое поле, а за-
 |
Рис. 4.6. Схема данных
тем отпустить клавишу. В открывшемся окне Изменение связей сле- дует произвести описанные выше действия.
Связи таблиц можно удалять. Для этого надо щелкнуть по линии связи, а затем нажать клавишу Del и подтвердить удаление связи.
Заполнение таблиц данными
Заполнение таблицы данными производится в режиме таблицы по- строчно. На рис. 4.6 показано за-
полнение данными таблицы От- делы. Последняя строка таблицы на рис. 4.7. отмечена звѐздочкой
*. Эта строка предназначена для ввода новой записи – еѐ ещѐ нет в таблице.
При заполнении следующей
Рис. 4.7.
таблицы Сотрудники принимается во внимание еѐ связь с таблицей Отделы (по полю КодОтдела). Для ввода значения поля КодОтдела будет предложено выбрать номер из списка подстановок – списка с
ранее введѐнными номерами отделов. Этим не следует пренебрегать, так как СУБД всѐ равно не позволит ввести в таблицу номер, которо- го нет в списке. На рис. 4.8 и 4.9 показаны заполненные данными остальные таблицы БД «ТфСвязь».
 |
Рис. 4.8.
 |
Рис. 4.9.
Замечание. Для наглядности ввода номера телефона в конструкторе полезно задать Свойство маска ввода: 0\-0000\-00\-00. Цифра 0 ко- дирует обязательный ввод в эту позицию цифры, 9 – не обязатель- ный, а знак правее \ будет стоять в поле при вводе. Номер автомобиля (например, А123мк177) в вариантах задания удобно вводить по маске
>L000<LL009. Символы, вводимые правее > и <, преобразуются со- ответственно в прописные и строчные, а L кодирует ввод буквы.
2.Задание
Для предложенных в варианте задания отношений подготовить маке- ты таблиц и схему данных БД (с указанием в отчѐте типов полей таб- лиц). Заполнить строки таблицы (по 8-10 строк на таблицу) заранее заготовленными данными, соответствующими предметной области.
После проверки преподавателем перенести в отчѐт окончательный вид схемы данных и таблиц с тремя заполненными строками.
Варианты заданий
1. Компьютеры (КодКомпьютера, Марка, КодПоколения, КодФирмы, Описание), Поколения (КодПоколения, ЭлемБаза, ТактЧастота, ВремяПоявления), Разработчики (КодФирмы, НазвФирмы, Страна).
2. Автомобили (КодАвто, Марка, Класс, КодФирмы, КодСтраны, ГодВыпуска, Описание), Фирмы (КодФирмы, НазвФирмы, Го- род), Страны (КодСтраны, НазвСтраны, Столица).
3. Методички (НомМет, НазвМет, ГодИзд, Стр, КодАвтора), Заказы (КодЗаказа, СтудФИО, НомМет, КодДисц, КодАвтора), Дисциплины (КодДисц, НазвДисц, НомХранилища), Авторы (КодАвтора, ФамилияИО, Кафедра).
4. Видеофильмы (КодФильма, НазвФильма, КодЖанра, КодСтраны, ГодВыпуска, КодКассеты), Жанр (КодЖанра, НазвЖанра, Описа- ние), Страны (КодСтраны, НазвСтраны, Столица), Кассеты (КодКассеты, МаркаКассеты, Длительность).
5. Собаки (КодСобаки, Кличка, Возраст, КодПороды, КодХозяина), Собаководы (КодХозяина, Фамилия, Имя, Отчество, ГодРожде- ния), ВидыПородСобак (КодВида, НазвВида, Описание), ПородыСобак (КодПороды, НазвПороды, РостСм, ВесКг, Заводчик, КодВида).
6. Книги (КодКниги, НазвКниги, КодАвтора, КодИздательства, ЖанрКниги), Авторы (КодАвтора, Фамилия, Имя, Отчство, ГодРо- ждения, ГодСмерти), Издательства (КодИздательства, НазвИзда- тельства,КодГорода, Примечание), Города (КодГорода, НазвГоро- да, Страна).
7. Двигатели Внутреннего Сгорания (ДВС): ДВС (КодДВС, МаркаДВС, Мощность, ЧислоОбМин, КодТипаДВС, КодИзготовителя), ТипыДВС (КодТипа, НазвТипа, Описание), Изготовители (КодИзготовителя, НазвФирмы, Страна, Город).
8. ЗемКадастр (КадастрНомер, АдресУчастка, Площадь, КодКатего- рииУчастка, ИННВладельца), Землевладелец (ИННВладельца, ИмяВладельца, Адрес), ПлощадиСтроений (КадастрНомер, Дом, Сарай, Баня, ХозБлок, Прочее), КатегорииУчастков (КодКатего- рииУчастка, НазвКатегории, СтоимЗаГа).
9. НалогДекларации (КодДекларации, ИННПлательщика, ДатаПла- тежа, ОблагаемаяСумма, КодНалога, КодЛьготы), Налогоплательщик (ИННПлательщика, Фамилия, Имя, Отчество, ГодРождения, Адрес), Налоги (КодНалога, НазвНалога, Про- цСтавка), Льготы (КодЛьготы, НазвЛьготы, ПроцСтавка).
10. Авиация (КодАппарата, Марка, КодТипа, КодФирмы, КодСтраны, ГодВыпуска), ТипыАппарата (КодТипа, НазвТипа, Описание), Фирмы (КодФирмы, НазвФирмы, Город), Страны (КодСтраны, НазвСтраны, Столица).
11. Автопарк (КодАвто, НомерАвто, Марка, Пробег, ГодВыпуска), Водители (КодВодителя, ФамилияИО, Класс, Стаж), Маршруты (НомМаршрута, Описание, Длина), Перевозки (Код, КодВодителя, КодМаршрута, Дата). Указание: КодВодителя и КодАвто совпадают, т.е. за водителем закреплен его автомобиль.
12. Гаражи (НомГаража, КодТипаГаража, КодВладельца), Владель- цы (КодВладельца, ФамилияИО, ГодРождения), ТипыГара- жей (КодТипаГаража, Площадь, Этаж, Описание), Авто (КодАвто, НомАвто, Марка, Цвет, КодВладельца).
13. ФутбольныеКлубы (КодКлуба, НазвКлуба, Город, Прези- дентКлуба), Тренеры (КодТренера, Фамилия, Имя, Отчество, КодКлуба, Должность), Игроки (КодИгрока, Фамилия, Имя, ГодРождения, Амплуа, КодКлуба).
14. Заказы (КодЗаказа, ФИОЗаказчика, КодКатегории, КодТовара, КодИзгот), Категории (КодКатегории, НазвКатегории, НомСкла-
да), Товары (КодТовара, НазвТовара, ДатаИзгот, Количество, Ко- дИзгот), Изготовители (КодИзгот, НазвФирмы, Страна).
15. Изделия (КодИзделия, НазвИзделия, КодТипоразмера, КодМате- риала, ОбъѐмМатериала%, КодИзготовителя), Матери- ал (КодМатериала, НазвМатериала, Плотность, ЦенаЗаКубометр), Изготовители (КодИзгот, НазвФирмы, Адрес, Телефон), Типо- размеры (КодТипоразмера, Длина, Высота, Ширина).
16. РежущийИнструмент (КодИнстр, НазвИнстр, КодТипоРазм, КодМеталла, КодИзготовителя), Типоразмеры (КодТипоРазм, Длина, ДлинаРабЧасти, Диаметр, ДиамПосадочный, Ширина, Шаг), Металл (КодМеталла НазвМеталла, УдВес, КоэффПрочно- сти), Изготовители (КодИзгот, НазвФирмы, Страна, Город).
17. Договоры (НомДоговора, НазвДоговора, ДатаНачала, ДатаОкон- чания, КодЗаказчика, КодОтвИсполнителя, Стоимость), Отдел (КодОтдела,НазвОтдела), ОтвИсполнители (КодОтвИс- полнителя, ФамилияИО, КодОтдела), Заказчики (КодЗаказчика, ИмяЗаказчика, Адрес, Телефон).
18.Железобетонные изделия: ЖБИ (КодИзделия, НазвИзделия, Код- Состава, КодТипоразмера, КодИзготовителя), ОбъѐмНаЕдЖБИ (КодСостава, Цемент, Песок, Гравий, Арматура), Типоразме- ры (КодТипоразмера, Длина, Ширина, Высота, ВнешнДиаметр, ВнутрДиаметр), Изготовители (КодИзготовителя, Фирма, Адрес).
19. МузПроизведения (КодПроизведения, НазвПроизведения, КодАв- тора, ГодСоздания, КодВидаПроизведения), ВидыПроизведений (КодВидаПроизведения, НазвВида, Характеристика), Композито- ры (КодКомпозитора, Фамилия, Имя, Отчесто, Страна, ГодРожде- ния, ГодСмерти), ЧислоПроизведений (КодКомпозитора, Симфо- ний, Опер, Концертов, Сонат, Увертюр, Этюдов, Романсов, Песен).
20. НобелевскиеЛауреаты (КодУчѐного, Фамилия, Имя, ГодРожде- ния, ГодСмерти, КодСтраны, ГодВрученияПремии), Авторские- Работы (КодРаботы, НазвРаботы, КодНауки, КодАвтора), Науки (КодНауки, НазваниеНауки, ВремяПоявления), Страны (КодСтраны, НазвСтраны, Столица).
№5. Анализ информации таблиц и БД Access.
Создание запросов.
1.Краткие теоретические сведения
Сортировка данных
При анализе информации удобно пользоваться отсортированными данными. Для сортировки данных таблицы необходимо выделить один или несколько соседних столбцов и на вкладке Главная, в груп- пе (см. рис. 5.1) Сортировка и фильтр (или в контекстном меню, по- сле нажатия правой клавиши мышки), щелкнуть по кнопке 
По возрастанию или 
По убыванию. После этого записи таблицы будут отсортированы сначала по крайнему левому выделенному столбцу, затем (для строк с одинаковыми значениями в левом столб- це) по столбцу, расположенному
правее, и так до крайнего право- го выделенного столбца. Для восстановления исходного по- рядка следования записей в
группе Сортировка и фильтр
надо щелкнуть по кнопке Очи- стить все сортировки.
Рис. 5.1. Группа Сортировка и
фильтр вкладки Главная
Фильтрация данных
При анализе информации можно установить фильтр, который по- зволит отображать лишь те записи, которые удовлетворяют некото- рому условию фильтрации. Это условие зависит от типа и значения данных. Обычный фильтр позволяет выбрать одно из условий фильт- рации, которые предлагает Access в ориентации на ячейку столбца, выделенную первой. То есть для его применения следует выделить ячейку столбца и нажать правую клавишу мышки, а затем (в контек- стном меню) выбрать требуемое из предложенных условий. Вместо этого в группе Сортировка и фильтр можно щелкнуть по кнопке Вы- деление и выбирать там. Например, в подпункте Между.. задать диа- пазон числовой величины или иные условия для дат. Для конкретно- сти ниже рассмотрен пример.
Пример установки фильтра для вывода номеров телефонов, с которых выполнялись звонки 29.05.10 и 30.05.10 (суббота и воскресенье).
Решение. В таблице ТфРазговоры БД «ТфСвязь» выделим поле Да- таВремяВызова. В группе Сортировка и фильтр щелкнем по Фильтр у. В меню и подменю выбираем Фильтры дат и Между.. В окне Диапазон дат вводим вручную (или с помощью календаря) даты Не ранее: 29.05.10 и Не позднее: 30.05.10 и щелкаем по кнопке OK.
Если предлагаемые Access условия не подходят, то можно в Рас- ширенном фильтре подготовить своѐ собственное условие. Для этого в группе Сортировка и фильтр надо выбрать Дополнительно, а в от- крывшимся подменю - Расширенный фильтр. После составления ус- ловия выполняется команда (в группе или в подменю) Применить фильтр. Для отмены фильтрации в секции Сортировка и фильтр, щелкнув по Дополнительно, надо выбрать Отменить все фильтры.
Следует отметить, что подготовка собственного условия для Рас- ширенного фильтра аналогична созданию запроса в режиме конст- руктора, что подробно разбирается далее.
Запросы и их создание
Вообще, запрос на выборку – это процедура, в которой описано (в
виде условий отбора) то, какие записи надо получить пользователю из БД. Пример условия отбора из таблицы с полем t1 – это логическое
выражение [t1]>0 And
[t1]< 9
(или
> 0 And < 9
без явного указания
поля) со значением истина, позволяющее получить записи, для
которых
t1Î(0;9). Результатом выполнения запроса является
множество записей (кортежей), удовлетворяющих условиям отбора, то есть по существу это таблицы (отношения). В Access запрос на выборку - это объект базы данных, который из взаимосвязанных таблиц БД (и других запросов) создаѐт новую, соответствующую условиям отбора, таблицу. Правда эта таблица (отображаемая в режиме таблицы) временная – она существует лишь до закрытия запроса (таблицы).
Простые запросы на выборку можно создавать с помощью мастера (1-ый способ), в режиме конструктора (2-ой способ) предоставляет- ся самый общий метод построения запросов, а в режиме SQL (3-ий
способ) можно увидеть скрытое описание запроса для Access – текст на языке Struqtured Query Language (структурированный язык запросов).
Создание запросов на выборку в режиме конструктора
 |
Дла вызова конструктора запросов на вкладке Создание в группе Другие щелкают по кнопке Конструктор запросов. В верхней части окна конструктора (см. рис. 5.2) отображается часть схемы данных со связями и таблицами, поля которых будут использоваться в запросе. В нижней части – бланк запроса, в котором отображаются поля ис-
Рис. 5.2. Добавление таблиц в конструктор запросов пользуемых таблиц и условия выборки записей. В диалоговом окне
Добавление таблицы следует указать таблицы и запросы, которые
будут использоваться в создаваемом запросе. После их выбора и щелчка по кнопке Добавить (или кликов по таблицам) они перено- сятся в верхнюю часть окна конструктора, а окно Добавление табли- цы можно Закрыть. (Для новых добавлений можно во вкладке Соз- дание в группе Настройка запроса выбрать Отобразить таблицу.)
Затем необходимо выбрать поля таблиц и включить их в запрос. Это можно сделать одним из способов: кликнув по выделенному по- лю, перетаскиванием полей в бланк запроса или использовать рас- крывающийся список в ячейках верхней строки бланка запросов. Кликнув по звѐздочке можно перенести все поля таблицы в бланк за- проса. Однако рекомендуется включать в запрос лишь самые необхо- димые поля. Не следует дважды включать одно и то же поле.
Выделенный столбец с полем можно удалить, нажав клавишу Del. Для выделения столбца курсор подводят к верху столбца до появле- ния стрелки, указывающей вниз, и щелкают мышкой. Поля следует располагать в бланке запроса слева направо в таком порядке, в каком вы хотите их видеть на экране в результатах выполнения запроса. Для изменения порядка следования столбец можно перетащить – выде- лить его, а затем нажать левую клавишу мышки и, не отпуская еѐ, от- буксировать чѐрную полоску-указатель в нужное место. Имя выво- димого поля можно заменить псевдонимом. Для этого имя-псевдоним с символом двоеточия «:» в конце надо набить перед наименованием поля в верхней строке бланка запроса.
В строке Сортировка можно указать поле и порядок сортировки по этому полю. Если ни для одного поля не сделано отметок, записи бу- дут выводиться в порядке их следования в БД.
Если вам не нужно выводить на экран одно или несколько полей, то следует сбросить для них флажок Вывод на экран.
В строке Условие отбора и в строке Или задают логическими вы- ражениями условия отбора записей. Логические выражения, записан- ные в одной строке бланка, но в разных столбцах, соединяются опе- ратором And (И). Если нужно получить объединение строк, задавае- мое применением к выражениям операторов Or (ИЛИ), то эти выра- жения размещают в разных строках бланка запроса.
В условиях отбора используются выражения. Они составляются из операндов, операторов (знаков операций), возможно, круглых скобок и задают правило вычисления этого выражения. Операндами - объек- тами, участвующими в операциях - могут быть константы, идентифи- каторы, функции и выражения. Примеры выражений:
[ТфРазговоы]![Дата] = Date() ‒ 7 [Тариф]*[ ДлитТфР] > 25,00р.
В приведѐнных выражениях: [ТфРазговоы]![Дата], [Тариф], [ДлитТфР]
– идентификаторы, первый включает имя таблицы и имя поля, два других – только имена полей (подразумеваемой таблицы);
Date() – функция, вычисляющая текущую дату;
=, ‒, > операторы отношения (по краям) и вычитания (в середине); 7 и 25,00р. – константы.
При составлении выражений можно использовать Построитель выражений (рис. 5.3). Для этого надо на вкладке Конструктор, в группе Настройка запроса, вы-
брать Построитель или щѐлкнуть
Рис. 5.3. Построитель выражений
правой клавишей мышки ячейку, куда требуется поместить выраже- ние, и выбрать Построить… Построитель выражений позволяет вставлять имена таблиц и полей без их набивки с клавиатуры, нахо- дить с помощью обзора требуемые функции. Построитель содержит перечень операторов, включающий: арифметические, логические, операторы слияния и специальные операторы.
Арифметические операторы используются для вычисления значений от двух или более чисел или для изменения знака числа.
| Оператор | Описание | Пример |
| + | Сложение | [Сумма]+25,00р. |
| ‒ | Вычитание или минус | Date() ‒ 7 |
| * | Умножение | [Тариф] * [Расход] |
| / | Деление | [Сумма] / [Количество] |
| \ | Деление нацело | [Количество] \ 3 |
| Mod | Остаток от деления | 12 Mod 5 |
| ^ | Возведение в степень | [Длина]^2 |
Логические операторы дают значения True (истина) или False (ложь).
| Оператор | Описание | Пример |
| And | Конъюнкция (И) | a And b |
| Or | Дизъюнкция (ИЛИ) | a Or b |
| Eqv | Эквивалентность | a Eqv b |
| Not | Отрицание (НЕ) | Not a |
| Xor | Исключающее ИЛИ | a Xor b |
Операторами слияния объединяют текстовые значения в единое.
| Оператор | Описание | Пример |
| & | Конкатенация | "Смирнов" & "и сыновья" |
| + | Конкатенация | "Смирнов" + "и сыновья" |
Специальные операторы используются так, как показано ниже:
| Оператор | Определяет: | Пример |
| Is | Равен ли или не равен аргумент пусто- му значению Null? | Is Null или Is Not Null |
| Between | Принадлежит ли числовое значение указанному оnрезку [10; 20]? | Between 10 And 20 |
| In | Содержится ли строковое значение в списке? | In("Рим", "Париж") |
| Like | Содержит ли строковое значение ука- занные символы? 1 | Like "Видео*" Like "???db" |
| 1 Значение включает подстановочные знаки:? - любой символ, * - произвольное число любых символов. |
Примеры построения запросов на выборку
Построение Запроса1 на выборку из одной таблицы ТфРазговоры БД
«ТФСвязь», рассмотренной в лабораторной работе №4. Пусть требу- ется вывести в возрастающем порядке все номера телефонов, с кото- рых инициировались разговоры длительностью более 10 минут, и да- ты этих разговоров.
Решение. При построении запроса таблицу ТфРазговоры переносим в верхнее окно конструктора. В столбцах 1, 2 и 3 указываем соответст- венно поля: НомТелефона, ДлитТфР, ДатаВремяВызова. Для 1-го поля в строке Сортировка указываем: по возрастанию. Для 2-го столбца в строке Условие отбора задаѐм: >10.
 |
ния в меню Office выбираем Сохранить и затем в окне Сохранение подтверждаем имя Запрос1 или вводим новое имя. Запрос с указан- ным именем появится в области переходов. Для выполнения запроса достаточно клик-
нуть по нему. Ре- зультат выполне- ния запроса пока- зан на рис. 5.5.
Полезно отметить, что таким же обра-
зом устанавливает-
ся расширенный
Рис.5.5. Запрос1 (режим таблицы)
фильтр, дающий такую же выборку. Разница в том, что запрос сохра- няется, и для изменѐнной таблицы его можно перезапустить, а фильтр приходится устанавливать заново.
Запрос можно корректировать. Для этого следует выбрать запрос в области переходов и, нажав затем правую кнопку мышки, щелкнуть в контекстном меню по позиции Конструктор. Или при выполненном запросе на вкладке Главная в группе Режимы выбрать Конструктор.
Режим SQL можно выбрать там же, в группе Режимы. Для нашего запроса оператор языка SQL имеют следующий вид:
SELECT ТфРазговоры.НомТелефона, ТфРазговоры. ДлитТфР FROM ТфРазговоры
WHERE (((ТФРазговоры. ДлитТфР)>10)), ORDER BY ТфРазговоры.НомТелефона;
SQL декларативный язык – он описывает то, что требуется отобрать из БД, но он не задаѐт последовательность вызовов процедур, кото- рые должны выполнять этот отбор. Оператор SELECT (Выбрать) ука- зывает идентификаторы полей, по которым будет производиться от- бор, из (FROM) таблицы. А после служебного слова WHERE (где) за- писывается условие отбора – фильтр записей. И наконец, после ORDER BY (упорядочить по) указано поле, по которому выполняется сортировка. Вообще говоря, запросы можно писать и сразу на SQL в окне редактора. Но чаще используют конструктор, а при необходимо- сти составленное им корректируют в режиме SQL. Например, если
использовать все столбцы таблицы, то можно 1-ую строку сократить до SELECT *. При выборке из нескольких таблиц их имена перечис- ляются через запятую после FROM.
Построение Запроса2 на выборку из БД «ТФСвязь», рассмотренной в лабораторной работе №4. Пусть требуется определить фамилии и отделы всех сотрудников, у которых за последнюю неделю длитель- ность хотя бы одного телефонного разговора (с вызовом) превышала 10 минут. Фамилии вывести в алфавитном порядке.
Решение. Для построения запроса используем все таблицы: Отделы, Сотрудники, ТфРазговоры, Телефоны. В столбцах указываем поля: ФамилияИО, ДлитТфР, НазвОтдела, ДатаВремяВызова, а для на- глядности вывода к ним слева (без кавычек) приписываем псевдони- мы: «Фамилия_И_О:», «Время (мин):», «Отдел:», «Дата:».
В 1-ом столбце (с полем ФамилияИО) в строке Сортировка указыва- ем по возрастанию. Во 2-ом столбце (с полем ДлитТфР) записываем условие: >10. Когда сравнивается значение поля столбца, в котором записано условие, то явно указывать идентификатор не надо, и нет необходимости писать: [ ДлитТфР ] > 10. В 4-ом столбце записываем условие: Date() – 7, по которому отбираются только записи с датами
 |
Рис. 5.6. Запрос2 в режиме конструктора
вызова, превышающими текущую дату минус семь дней (неделю). Для выбираемых строк эти условия должны выполняться одновре- менно, то есть рассматривается пересечение множеств записей, отби-
раемых по каждому из условий. Поэтому условия размещаются в од- ной строке, то есть используется оператор And (И).
Запрос в режиме конструктора (представленный на рис. 5.6) сохраня- ем в БД под именем Запрос2.
В режиме SQL Запрос2 имеет следующий вид:
SELECT Сотрудники.ФамилияИО AS [Фамилия_И_О], ТфРазговоры.ДлитТфР AS [Время(мин)], Отделы.НазвОтдела AS Отдел, ТфРазговоры.ДатаВремяВызова AS Дата
FROM ((Отделы INNER JOIN Сотрудники ON Отделы.КодОтдела = Сотрудники.КодОтдела) INNER JOIN Телефоны ON Сотрудники.КодСотрудника = Телефоны.КодСотрудника) INNER JOIN ТфРазговоры ON Телефоны.НомТелефона = ТфРазговоры.НомТелефона WHERE (((ТфРазговоры. ДлитТфР)>10) AND ((ТфРазговоры.
ДатаВремяВызова) > Date()-7)) ORDER BY Сотрудники.ФамилияИО;
Можно заметить, что при указании полей добавились альтернатив- ные имена после слова AS (как). А после FROM для таблиц описаны внутренние связи (INNER JOIN) по (ON) указанным полям.
Построение Запроса3 с вычисляемым полем.
Пусть требуется из БД «ТфСвязь» по звонкам сотрудников 1-го отде- ла с 30.05.10 по 02.06.10 сверить тарифы, то есть получить стоимость одной минуты телефонного разговора.
Решение. Здесь также как в предыдущем запросе используем все таб- лицы. Понятно, что в столбце с полем КодОтдела надо записать Ус- ловие отбора отдела: =1. А для столбца с полем ДатаВремяВызова в строке Условие отбора: Between #30.05.10# And #02.06.10# (или
 |
поле пустого столбца следует набить выражение: [ СтоимТфР ]/[ ДлитТфР ], а вместо выведенного слева слова Выра- жение1: записать название поля Тариф: (рис. 5.7). Полезно назначить формат вывода в это поле. Для этого надо встать в ячейку этого столбца, на вкладке Создание в группе Показать или скрыть щелк- нуть по кнопке Страница свойств, на вкладке Общие Окна свойств выбрать Формат поля: фиксированный и Число десятичных знаков: 2, а потом закрыть окно свойств и закрыть (с сохранением запроса) конструктор. При выполнении этого запроса в выводимой таблице
 |
Рис. 5.8. Запрос3 (режим таблицы)
появится новое поле Тариф (рис. 5.8). Важно отметить, что в исходных таблицах никаких новых полей заведено не будет.
Построение Запроса4 с параметром. Пусть требуется из БД
«ТфСвязь» вывести сведения по телефонным звонкам какого-либо сотрудника. Фамилия_И_О сотрудника должна указываться в про- цессе выполнения запроса.
Решение. Для выполнения требований задачи в столбец с полем Фа- милияИО, в строку Условие отбора, необходимо записать параметр – заключѐнный в квадратные скобки текст, который будет использо- ваться как подсказа при вводе. В данном случае уместно записать [ Фамилия_И_О: ], а после текста поставить двоеточие, точки внутри имени не допустимы. При выполнении такого запроса в появившееся окно Введите значение параметра будет выведена наша подсказка Фамилия_И_О:. В окне ввода (см. рис. 5.10) следует задать фамилию и инициалы конкретного сотрудника в том виде, как они записаны в БД, и щелкнуть по кнопке OK. Введѐнное в запрос значение будет использовано для отбора записей.
Итоговые запросы (запросы с групповыми операциями)
Для вычисления итоговых значений по группам данных использу- ются запросы с групповыми операциями. Строка Групповая операция в бланке запроса позволяет выделить группы записей с одинаковыми значениями в указанных полях и вычислить итоги для этих групп по одной из статистических функций.
Построение Запроса5 с групповыми операциями. Пусть требуется из БД «ТфСвязь» вывести для какого-либо сотрудника итоговые сведе- ния: общий счѐт и общее время по его телефонным разговорам, а также среднюю продолжительность одного, инициированного им, те- лефонного разговора. Фамилия_И_О содрудника должна указываться в процессе выполнения запроса.
Решение. Фамилия сотрудника может фигурировать неоднократно в результирующей таблице предыдущего Запроса4. Поэтому возможно произвести группировку по полю ФамилияИО и выполнить требуе- мые подсчѐты в соответствующих полях сгруппированных записей. Сначала помещаем используемые (связанные) таблицы в верхнюю часть окна Конструктора. Затем в ленте, в группе кнопок Показать или скрыть, щелкаем по кнопке Итоги (со значком S), и в бланке за- проса появляется новая строка Групповая операция:. В 1-ый столбец помещаем поле ФамилияИО – столбец заполняется как в предыдущем запросе, но в строке Групповая операция: должна стоять Группировка. В первую строку 2-го столбца помещаем (вместе с псевдонимом) по- ле СтоимТфР, то есть ОбщСумма: СтоимТфР, а в строке Групповая операция выбираем функцию Sum для суммирования значений стои- мостей разговоров группы. Таким же образом заполняем 3-ий стол- бец с псевдонимом и полем ОбщееВремя(мин): ДлитТфР, в котором функция Sum вычисляет общее время по всем записям группы. И следующий 4-ый столбец заполняем именами СрВремя(мин): ДлитТфР, но в строке Групповая операция указываем функцию Avg, которая вычисляет среднее арифметическое время одного разговора. Для полей столбцов со 2-го по 4-ый целесообразно установить фор- мат вывода. Для иллюстрации вычислений с функциями заполним первую строку 5-го столбца так:
СрВремя: Sum([ДлитТфР])/Count([ДлитТфР]),
а в строке Групповая операция укажем Выражение. Для удобства на- бивки в поле длинных выражений (дав Shift+F2) можно использовать окно Область ввода или Построитель выражений. В приведѐнном выражении среднее время определяется как вычисляемое поле с ис-
 |
Рис. 5.9. Запрос5 в режиме конструктора
пользованием функций Sum и Count (количество) от поля ДлитТфР.
Вид Запроса5 в Конструкторе дан на рис. 5.9; ввод значения параметра показан на рис. 5.10, а результат вы-
полнения представлен на рис. 5.11. Рис. 5.10. Ввод значения параметра
 |
Рис. 5.11. Запрос5 (режим таблицы)
2.Задание
Для получения информации из БД, созданной в предыдущей лабора- торной работе, подготовить, согласовать с преподавателем и затем построить в конструкторе три запроса:
· запрос на выборку с критериями поиска;
· итоговый запрос или запрос, формирующий вычисляемое поле;
· параметрический запрос.
Предъявить преподавателю результаты выполнения этих запросов. Бланки запросов (режим конструктора) изобразить в отчѐте. Один из запросов рассмотреть в режиме SQL, списать текст запроса в от- чѐт. В отчѐте пояснить смысл элементов SQL -запроса.
№6. Начала алгоритмизации. Обработка в Excel табличных данных с использованием макросов на VBA.
1.Краткие теоретические сведения
Макросы и автоматизация обработки данных
При работе, например, в Проводнике, в Word или Excel для выпол- нения какого-либо действия нажимают соответствующую кнопку, инициирующую это действие. Однако при частом нажатии одинако- вых последовательностей кнопок рутинная работа утомляет и отни- мает неоправданно много времени. В таких случаях целесообразно уяснить последовательности повторяющихся действий (алгоритм) и записать программу – последовательность команды для выполнения этих действий компьютером. Программу можно оформить в виде так называемого макроса, « запомнив» производимые нажатия клавиш в режиме записи макроса или составив макрос самостоятельно из ко- манд внутреннего языка приложений. Выполнение программы (мак- роса) позволит получить результат в автоматическом режиме. Встре- чаются действия, для которых кнопка вообще не предусмотрена. В этих ситуациях также выручает алгоритмический подход – состав- ление алгоритма и соответствующей программы (макроса) для вы- полнения требуемого действия. Для запуска макроса можно создать новую кнопку или связать запуск с нажатием комбинации клавиш.
Основы алгоритмизации с использованием макросов
Последовательность действий для решения, например, вычисли- тельной задачи, то есть алгоритм, можно записать обычными словами с использованием математической символики. Часто алгоритмы представляют в виде блок-схем (наглядного графического изображе- ния). Важно отметить, что такие записи непосредственно не воспри- нимаются компьютером, однако является основой для написания программы, которую может выполнить компьютер. Далее рассматри- ваются на примерах важнейшие типы алгоритмов.
Основные типы вычислительных алгоритмов Линейные вычислительные процессы
Пример 1. Заданы высота H и радиус R основания прямого кругового конуса. Вычислить объѐм V конуса.
Основу алгоритма вычисления составляет формула
V = H × 2 p R 2, а
все действия (алгоритм), связанные с расчѐтом, можно представить так:
Начало алгоритма
