Добавил:

Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Московский государственный университет приборостроения и информатики

Предмет:

[НЕСОРТИРОВАННОЕ]

Файл:

методичка.doc

Скачиваний:

Добавлен:

09.04.2015

Размер:

1.89 Mб

Скачать

☆

1 / 21 2 > Следующая >>>

Базы данных

Введение

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

Основу любой информационной системы составляет база данных.

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

С помощью алгоритмических языков программирования, таких как Basic, Pascal, C++ и т.д. Данный способ применяется для создания уникальных баз данных.
С помощью прикладной среды, например Visual Basic. С его помощью можно создавать базы данных, требующие каких-то индивидуальных особенностей построения.
С помощью специальных программных сред, которые называются системами управления базами данных.

В настоящее время существует несколько видов СУБД. Наиболее известными и популярными СУБД являются Access, FoxPro, Paradox, MS SQL Server.

Правила выполнения лабораторных работ

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

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

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

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

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

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

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

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

Тема: Проектирование однотабличной базы данных.

Цели:

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

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

Знать:

основные понятия БД
принцип создания файла БД
принципы заполнения БД информацией

Уметь:

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

Ход выполнения лабораторной работы:

1.Проектирование структуры базы данных

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

2.Конструирование структуры будущих таблиц БД.

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

3.Создание схемы базы данных.

При наличии одной таблицы схема БД тоже простая: состоит из одной таблицы, т.е. ее можно специально не делать.

4.Ввод данных в таблицы.

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

Ход работы:

Задание 1. Создайте новую БД и изготовьте структуру таблицы с информацией о студентах.

Порядок работы:

1.Вызовите программу MS Access. Перед вами откроется окно СУБД, в котором появится меню, представленное на рисунке 1.

Рисунок 1. Окно СУБД MS Access

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

Рисунок 2. Диалоговое окно Создание файла БД.

3.После этого появится диалоговое окно представленное на рис.3. В поле Имя файла в качестве имени БД можете оставить имя, которое предлагает СУБД (db1), а можете ввести свою фамилию. Это будет название вашей личной БД.

Рисунок 3. Создание и сохранение файла БД.

Замечание: Можете оставить БД в текущей папке (на рис.3 – это папка Мои документы), а можете сменить папку на вашу личную.

1.Щелкните на кнопке Создать.

2.В следующем окне (рис.4) выберите тип создаваемого документа (выберите соответствующую закладку). Вы создаете таблицу, поэтому выберите закладку Таблица (скорее всего, вы в ней и находитесь). Щелкните по кнопке Создать.

Рисунок 4. Закладка Таблицы. Создание таблицы

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

Рисунок 5. Выбор создания таблицы в режиме Конструктор

Рисунок 6. Окно Конструктора

8. В верхней левой клетке введите имя поля (набирайте слово «фамилия», а не свою фамилию) и нажмите на клавишу Enter. В соседней клетке появится тип данных, по умолчанию он задается Текстовый (рисунок 7). Любой другой тип выбирается с помощью ниспадающего меню.

Замечание: переход от клетки к клетке осуществляется одним из нескольких способов: мышкой, нажатием клавиши Enter, стрелками, клавишей Tab.

9.Заполните поля в Конструкторе данными из таблицы 1. Общие свойства полей по умолчанию (какие задает сама программа).

Таблица 1.

Имя поля	Тип данных
Фамилия	Текстовый
Имя	Текстовый
Отчество	Текстовый
Год рождения	Текстовый
Учебное заведение	Текстовый
Группа	Текстовый

Рисунок 7. Заполнение полей таблицы в режиме Конструктор

10.Закройте таблицу. В появившемся окне (рис.8) наберите имя таблицы Список и щелкните по кнопке Сохранить (рис.9). Появится запрос на создание ключевого поля – уникального поля записи, по которому удобно связывать таблицы. В данном варианте будут самостоятельно появляться числа – номера записей (рис.10). Ответьте Да.

Рисунок 8. Сохранение таблицы

Рисунок 9. Присвоение имени таблице.

Рисунок 10. Задание ключевого поля.

Замечание: Выбор режима работы: Таблица или Конструктор – осуществляется с помощью пункта меню Вид.

11.Перейдите в режим таблицы. Ввод данных будет производиться в этом режиме, заполняя клетки таблицы. Значение Код будет меняться автоматически. Если закончить ввод в ячейку нажатием на клавишу Enter, то маркер перейдет в следующую ячейку.

12.Заполните БД значениями из таблицы 2.

Таблица 2.

Код	Фамилия	Имя	Отчество	Год рождения	Учебное заведение	Группа
1	Константинов	Антон	Юрьевич	1989	КОЭК	38
2	Кондратьев	Павел	Анатольевич	1988	КОЭК	38
3	Изотова	Екатерина	Олеговна	1989	КОЭК	37
4	Шикин	Максим	Сергеевич	1989	КОЭК	37
5	Абрашкин	Андрей	Анатольевич	1987	ХТКЭМ	31
6	Алешков	Александр	Викторович	1987	ХТКЭМ	32
7	Волков	Геннадий	Игоревич	1987	ХТКЭМ	31
8	Осипова	Ирина	Ивановна	1988	ХТКЭМ	32
9	Примудров	Сергей	Александрович	1989	КОЭК	37
10	Дьяченко	Артем	Сергеевич	1990	КОЭК	38

Рисунок 11. Заполнение таблицы данными

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

Задание 2. Выполните редактирование ячеек.

Порядок работы:

Замените фамилию Осипова на Осипенкова. Для чего выделите редактируемую ячейку и наберите новую фамилию.
Замените год рождения на 1983. Для этого следует щелкнуть мышкой по нужной ячейке, и она откроется для редактирования. Удалите цифру 8 и введите вместо нее цифру 3.

Самостоятельное задание: Внимательно просмотрите таблицу и исправьте свои ошибки.

Задание 3. Отсортируйте значения таблицы.

Порядок работы:

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

Отсортируйте:

1.Фамилии – по алфавиту (поставьте маркер на любую фамилию в столбце с названием Фамилия и щелкните п.м…. Сортировка по возрастанию).

2.Имя – по алфавиту

3. Номер группы – по возрастанию

4.Год рождения – по убыванию

Задание 4. Познакомьтесь с функциями: сохранить, закрыть, открыть.

Самостоятельно.

Задание 5. Выполнение поиска записей по образцу.

Порядок работы:

1.Установите текстовый курсор в поле Фамилия.

2.Щелкните п.м. Правка, Найти, которая позволяет найти запись по введенному значению. В результате появится диалоговое окно рис.20.

Рисунок 12. Поиск записей по образцу.

3.Наберите в поле Образец фамилию Изотова и щелкните кнопке найти рис.13.

Рисунок 13. Поиск записей по фамилии «Осипова»

Замечание: Если требуется найти следующую подобную запись, то щелкните мышкой по кнопке Найти далее. По окончании работы нажмите кнопку Закрыть.

Завершите работу с MS Access.

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

Дайте определение понятий «база данных», «предметная область»
Каковы предпосылки создания баз данных
Определите соотношение понятий «информация» и «данные».
Какие технические средства используются для создания баз данных.
Дайте определение системе управления базами данных.
Определите основные функции и назначение СУБД
Перечислите основные категории пользователей баз данных.
Дайте определение понятий «база данных», «предметная область»
Каковы предпосылки создания баз данных
Определите соотношение понятий «информация» и «данные».
Какие технические средства используются для создания баз данных.
Дайте определение системе управления базами данных.
Определите основные функции и назначение СУБД
Перечислите основные категории пользователей баз данных.

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

Тема: Создание и использование фильтров

Цели:

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

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

Знать:

основные понятия БД
принцип создания файла БД
принципы заполнения БД информацией

Уметь:

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

Ход выполнения лабораторной работы:

Задание 1. Создать однотабличную базу данных в соответствии со следующей таблицей:

Код	Фамилия	Имя	Телефон	Город	Страна	Фирма	Должность
1	Доценко	Елена	563-25-45	Красногорск	Россия	ОАО Сименс	Бухгалтер
…	…	…	…	…	…	…	…
…	…	…	…	…	…	…	…

Используйте следующие типы полей:

Поле	Тип	Ограничения
Код	Счетчик
Фамилия	Текстовый	15 символов
Имя	Текстовый	10 символов
Телефон	Текстовый	9 символов
Город	Текстовый	15 символов
Страна	Текстовый	13 символов
Фирма	Текстовый	30 символов
Должность	Текстовый	20 символов

Задание 2. Заполните таблицу информацией в количестве 25 строк. Причем в нескольких записях значения должны обязательно повторяться.

Задание 3. Создание и использование фильтров.

Фильтр – это набор условий, применяемых для отбора подмножества записей. В MS Access существуют фильтры четырех типов:

- фильтр по выделенному;

- обычный фильтр (фильтр по форме);

- расширенный фильтр;

- фильтр по вводу.

Фильтрование данных в Access производится с помощью кнопок Фильтр по выделенному или Изменить фильтр. После нажатия второй кнопки от таблицы остается одна запись. Каждое поле становится полем со списком (когда в нем находится курсор), в котором можно выбрать из списка все значения данного поля. После щелчка по кнопке Применить фильтр будут выбраны записи, соответствующие измененному фильтру. Еще более сложные условия фильтрации можно задать командой меню Записи, Фильтр, Расширенный.

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

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

В форме или в таблице существуют разные способы применения фильтров: фильтр по выделенному, фильтр по форме (обычный фильтр). «Фильтр для» (фильтр по вводу).

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

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

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

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

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

В форме или в таблице существую разные способы применения фильтров: фильтр по выделенному, фильтр по форме и поле «Фильтр для».

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

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

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

Для создания сложных фильтров следует использовать окно расширенного фильтра.

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

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

Задание 4. Создать все 4 формы фильтров для созданной базы данных.

Результат представить преподавателю.

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

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

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

Тема: Создание многотабличной базы данных. Установление взаимосвязей между таблицами.

Цели:

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

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

Знать:

основные понятия БД
принцип создания файла БД
принципы заполнения БД информацией
типы взаимосвязей таблиц

Уметь:

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

Ход выполнения лабораторной работы:

Задание 1. Создать базу данных, состоящих из трех таблиц:

Таблица 1. Студенты

Код	Фамилия	Имя	Год_рождения	Учебное_заведение	Группа	Адрес

Типы полей для таблицы 1. Студенты:

Поле	Тип	Ограничение
Код	Счетчик
Фамилия	Текстовый	15 символов
Имя	Текстовый	10 символов
Год_рождения	Дата/Время	Маска ввода - краткий формат даты: 00/00/0000;0;_
Учебное_заведение	Текстовый	Значение по умолчанию «КОЭК»
Группа	Текстовый	4 символа Подстановка – Список – Список значений – перечислить через точку с запятой номера групп 27/1; 27/2; 27/3; 37; 38; 39; 46; 47; 48;
Адрес	Текстовый	40 символов

Рисунок 14. Подстановка

Таблица 2. Предметы

Код	Название_предмета	Семестр

Типы полей для таблицы 2. Предметы:

Поле	Тип	Ограничение
Код	Счетчик
Название_предмета	Текстовый	30 символов
Семестр	Числовой	Подстановка – Список – Список значений – через точку с запятой перечислите I; II;

Рисунок 15. Заполнение БД информацией

Заполните таблицу информацией о пяти предметах.

Таблица 3. Преподаватели

Фамилия	Имя	Отчество	Код_предмета

Типы полей для таблицы 1. Студенты:

Поле	Тип	Ограничение
Фамилия	Текстовый	15 символов
Имя	Текстовый	10 символов
Отчество	Текстовый	10 символов
Код_предмета	Числовой	Длинное целое

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

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

Задание 2. Список подстановки

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

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

Откройте таблицу Преподаватели в режиме Конструктора.
В столбце Тип данных для поля Код_предмета выберите пункт Мастер подстановок.

Рисунок 16. Выбор типа поля Мастер подстановок

3. В первом окне мастера оставьте выбранным положение переключателя Объект «Столбец подстановки» будет использовать значение из таблицы или запроса и щелкните на кнопку Далее – Далее.

Рисунок 17.

Рисунок 18.

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

5. Выберите это поле. Щелкните на кнопку Далее.

Рисунок 19.

6. Следующее окно мастера демонстрирует столбец таблицы источника.

Рисунок 20

7. В поле последнего окна введите название Предмет, которое заменит имя столбца Код_предмета и щелкните на кнопке Готово.

Рисунок 21.

8. Зайдите в таблицу Преподаватели и проверьте правильность выполненных действий.

Рисунок 22. проверка правильности работы мастера подстановок

Задание 3. Создание схемы данных

Зайдите в пункт меню Сервис – Схема данных – Добавление таблицы :

Вкладка таблицы – Предметы – Добавить.
Вкладка таблицы – Преподаватели – Добавить.

Рисунок 23. Создание схемы

Рисунок 24

Задание 4. Установка взаимосвязей

Для установления взаимосвязи между этими двумя таблицами необходимо:

Поместить указатель мыши на пункт Код таблицы Предметы.
Нажмите кнопку мыши и перетащите указатель на поле Код_предмета таблицы Преподаватели.
В открывшемся окне диалога установите флажок «Обеспечение целостности данных» и щелкните на кнопке Создать. Между двумя таблицами в окне Схема данных появится линия связи типа «один-ко-многим».
Закройте окно, подтвердив сохранение.

Рисунок 25.

Рисунок 26. Установление связи один – к –одному.

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

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

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

Тема: Создание экранной формы

Цели:

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

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

Знать:

основные понятия БД
принцип создания файла БД
принципы заполнения БД информацией
типы взаимосвязей таблиц
принципы создания экранной формы

Уметь:

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

Ход выполнения лабораторной работы:

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

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

Задание 1. Создать базу данных, состоящих из трех таблиц (см. предыдущую лабораторную работу)

Задание 2. Обратите внимание, что таблица Студенты не связана в схеме с двумя другими таблицами! Необходимо установить эту связь, добавив дополнительное поле в таблицу Студенты.

Задание 3. Для созданной базы данных необходимо создать формы с помощью Мастера форм ля ввода данных в таблицы:

- студенты

- преподаватели

- предметы

Рисунок 27. Схема данных

Задание дано на самостоятельное изучение.

Результат представить преподавателю в следующем виде:

Рисунок 28. Экранная форма.

Рисунок 29. Экранная форма

Рисунок 30. Экранная форма

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

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

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

Тема: Создание элементов управления на форме.

Цели:

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

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

Знать:

основные понятия БД
принцип создания файла БД
принципы заполнения БД информацией
типы взаимосвязей таблиц
принципы создания экранной формы

Уметь:

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

Ход выполнения лабораторной работы:

Задание 1. Создайте кнопочные формы как в предыдущей лабораторной работе.

Задание 2. Откройте форму Преподаватели в режиме Конструктора

Рисунок 31. Экранная форма в режиме Конструктора

На панели элементов нажать кнопку «кнопка» и в поле формы создать ее нажатием левой кнопки мыши. Откроется мастер создания кнопок.

1. Выбираем категорию: Переход по записям – Действия – Следующая запись (рис 32) . В следующем окне выбираем внешний вид кнопки (рис. 33 ).

Рисунок 32. Диспетчер кнопочных форм

Рисунок 33. Создание кнопок

Рисунок 34. Экранная форма с кнопкой

2. Аналогично делаем другие кнопки в категории Переход по записям, Работа с формой, Приложения.

В результате имеем:

Рисунок 35. Законченная экранная форма.

Задание 3. Самостоятельно сделайте аналогичные кнопки для других форм.

Результат предъявите преподавателю на экране монитора.

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

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

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

Тема: Создание главной кнопочной формы.

Цели:

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

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

Знать:

основные понятия БД
принцип создания файла БД
принципы заполнения БД информацией
типы взаимосвязей таблиц
принципы создания экранной формы

Уметь:

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

Ход выполнения лабораторной работы:

Задание 1. Создайте все основные кнопочные формы как в предыдущей лабораторной работе.

Задание 2. Через п..м. Сервис – Служебные программы – Диспетчер кнопочных форм – создайте главную кнопочную форму созданной вами базы данных.

Рисунок 36. Меню создания главной кнопочной формы

Рисунок 37. Изменение страницы главной кнопочной формы

Рисунок 38. Изменение элемента

Рисунок 39. Изменение элемента

Рисунок 40. Изменение элемента

В результате построения главной кнопочной формы имеем:

При нажатии на каждую из кнопок открывается соответствующая подчиненная форма:

Рисунок 41. Главная кнопочная форма

Рисунок 42. Открытие подчиненной формы

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

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

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

Тема: Создание отчета

Цели:

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

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

Знать:

основные понятия БД
принцип создания файла БД
принципы заполнения БД информацией
типы взаимосвязей таблиц
принципы создания экранной формы
принципы построения отчетов

Уметь:

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

Ход выполнения лабораторной работы:

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

MS Access имеет следующие режимы создания отчетов:

- Конструктор

- Мастер отчетов

- Автоотчет: в столбец, Автоотчет: ленточный

- Мастер диаграмм

- Почтовые наклейки

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

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

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

Общие сведения об отчетах

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

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

Большинство отчетов являются присоединенными к одной или нескольким таблицам и запросам из базы данных. Источником записей отчета являются поля в базовых таблицах и запросах. Отчет не должен включать все поля из каждой таблицы или запроса, на основе которых он создается.

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

Заголовок отчета и заголовки столбцов сохраняются в макете отчета.
Данные поступают из выражений, которые сохраняются в макете отчета.
Данные поступают из полей базовой таблицы, запроса или инструкции SQL.
Итоговые значения поступают из выражений, которые сохраняются в макете отчета.

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

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

Создание отчета

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

Настройка отчета

Можно настроить отчет следующими способами.

Источник записей. Измените таблицу или запрос, на котором основан отчет.

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

Окно отчета. Можно добавить или удалить кнопки Развернуть и Свернуть, изменить текст заголовка и другие элементы окна отчета.

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

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

Задание на лабораторную работу:. К созданной в предыдущих лабораторных работах базы данных построить различные формы отчетов.

Рисунок 43. Отчет

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

Что такое база данных
Перечислите требования, предъявляемые к базе данных
Дайте определение реляционной модели данных
Каковы ограничения целостности реляционной модели данных
Что такое сущность. Что такое экземпляр сущности. Приведите пример.
Что такое атрибут сущности.
Что называется связью.
Как изображаются сущности и связи на ER – диаграмме
Как определить степень связи
Какие типы связей существуют
Какова роль флажков «Обеспечение целостности данных», «Каскадное обновление связанных полей» и «Каскадное удаление связанных полей» в диалоговом окне «Связи».
Как назначить сортировку в алфавитном порядке при создании отчетов по одному полю, по двум полям.
Как с помощью Мастера отчетов сгруппировать записи по дате.
Для чего создаются межтабличные связи при объединении таблиц и создании семы данных.

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

Тема: Создание подчиненного отчета. Вычисления в отчетах.

Цели:

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

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

Знать:

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

Уметь:

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

Ход выполнения лабораторной работы:

MS Access имеет следующие режимы создания отчетов:

- Конструктор

- Мастер отчетов

- Автоотчет: в столбец, Автоотчет: ленточный

- Мастер диаграмм

- Почтовые наклейки

Подчиненные отчеты

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

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

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

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

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

В главном отчете перечислены ежедневные продажи.

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

В главном отчете перечислены мероприятия, в которых участвуют сотрудники.

В подчиненном отчете перечислены представители, участвующие в каждом мероприятии.

Подчиненные формы в отчете

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

Уровень 1	Уровень 2
Подчиненный отчет 1	Подчиненный отчет 2
Подчиненный отчет 1	Подчиненная форма 1
Подчиненная форма 1	Подчиненная форма 2

Синхронизация отчета и подчиненного отчета

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

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

Определены отношения для выбранных таблиц или для базовых таблиц выбранных запросов.

Главный отчет основан на таблице с первичным ключом, а подчиненный отчет основан на таблице, содержащей поле с таким же именем, как и у этого первичного ключа, и с таким же или совместимым типом данных. Например, если первичный ключ базовой таблицы главной формы имеет тип данных «Счетчик», а его свойство Размер поля (FieldSize) имеет значение Длинное целое, то соответствующее поле в базовой таблице подчиненной формы должно иметь тип данных «Числовой» со свойством Размер поля (FieldSize), имеющим значение Длинное целое. При выборе запроса или запросов базовые таблицы этих запросов должны соответствовать тем же условиям.

Примечание. Подчиненные отчеты используются в отчетах «Продажи по типам» и «Продажи по годам» в учебной базе данных «Борей». Для просмотра этих отчетов откройте базу данных Борей.mdb из подпапки Samples папки программ Microsoft Office, а затем откройте отчет в режиме конструктора.

Создание подчиненного отчета

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

Создание подчиненного отчета в существующем отчете

Откройте отчет, который должен быть главным отчетом, в режиме конструктора.

Убедитесь, что кнопка Мастера элементов на панели элементов нажата.

Нажмите кнопку Подчиненная форма/отчет на панели элементов.

Выберите в отчете место, в которое нужно поместить подчиненный отчет.

Следуйте инструкциям диалоговых окон мастера.

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

Добавление существующего отчета в другой отчет в качестве подчиненного отчета

Откройте отчет, который должен быть главным отчетом, в режиме конструктора.

Для перехода в окно базы данных нажмите клавишу F11.

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

В отчет добавляется элемент управления подчиненного отчета.

Задание на лабораторную работу:.

К созданной в предыдущих лабораторных работах базы данных создать подчиненный отчет.
Создать отчет с вычислениями.

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

Как определить степень связи
Какие типы связей существуют
Какова роль флажков «Обеспечение целостности данных», «Каскадное обновление связанных полей» и «Каскадное удаление связанных полей» в диалоговом окне «Связи».
Как назначить сортировку в алфавитном порядке при создании отчетов по одному полю, по двум полям.
Как с помощью Мастера отчетов сгруппировать записи по дате.
Для чего создаются межтабличные связи при объединении таблиц и создании семы данных.
Перечислите этапы создания подчиненного отчета.
Назовите принципы вычислений в отчетах.
С помощью чего создаются отчеты.

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

Тема: Создание и управление базой данных с помощью SQL – операторов

Цели:

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

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

Знать:

типы взаимосвязей таблиц
основные конструкции SQL – операторов
принципы создания базы данных при помощи SQL

Уметь:

создавать базу данных при помощи SQL
строить запросы при помощи языка запросов SQL

Ход выполнения лабораторной работы:

Язык SQL стал фактически стандартным языком доступа к базам данных. Все СУБД, претендующие на название "реляционные", реализуют тот или иной диалект SQL. Многие нереляционные системы также имеют в настоящее время средства доступа к реляционным данным. Целью стандартизации является переносимость приложений между различными СУБД.

Нужно заметить, что в настоящее время, ни одна система не реализует стандарт SQL в полном объеме. Кроме того, во всех диалектах языка имеются возможности, не являющиеся стандартными. Таким образом, можно сказать, что каждый диалект - это надмножество некоторого подмножества стандарта SQL. Это затрудняет переносимость приложений, разработанных для одних СУБД в другие СУБД.

Язык SQL оперирует терминами, несколько отличающимися от терминов реляционной теории, например, вместо "отношений" используются "таблицы", вместо "кортежей" - "строки", вместо "атрибутов" - "колонки" или "столбцы".

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

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

Операторы SQL

Основу языка SQL составляют операторы, условно разбитые не несколько групп по выполняемым функциям.

Можно выделить следующие группы операторов (перечислены не все операторы SQL):

Операторы DDL (Data Definition Language) - операторы определения объектов базы данных

CREATE SCHEMA - создать схему базы данных

DROP SHEMA - удалить схему базы данных

CREATE TABLE - создать таблицу

ALTER TABLE - изменить таблицу

DROP TABLE - удалить таблицу

CREATE DOMAIN - создать домен

ALTER DOMAIN - изменить домен

DROP DOMAIN - удалить домен

CREATE COLLATION - создать последовательность

DROP COLLATION - удалить последовательность

CREATE VIEW - создать представление

DROP VIEW - удалить представление

Операторы DML (Data Manipulation Language) - операторы манипулирования данными

SELECT - отобрать строки из таблиц

INSERT - добавить строки в таблицу

UPDATE - изменить строки в таблице

DELETE - удалить строки в таблице

COMMIT - зафиксировать внесенные изменения

ROLLBACK - откатить внесенные изменения

Операторы защиты и управления данными

CREATE ASSERTION - создать ограничение

DROP ASSERTION - удалить ограничение

GRANT - предоставить привилегии пользователю или приложению на манипулирование объектами

REVOKE - отменить привилегии пользователя или приложения

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

Наиболее важными для пользователя являются операторы манипулирования данными (DML).

Примеры использования операторов манипулирования данными

INSERT - вставка строк в таблицу

Пример 1. Вставка одной строки в таблицу:

INSERT INTO

P (PNUM, PNAME)

VALUES (4, "Иванов");

Пример 2. Вставка в таблицу нескольких строк, выбранных из другой таблицы (в таблицу TMP_TABLE вставляются данные о поставщиках из таблицы P, имеющие номера, большие 2):

INSERT INTO

TMP_TABLE (PNUM, PNAME)

SELECT PNUM, PNAME

FROM P

WHERE P.PNUM>2;

UPDATE - обновление строк в таблице

Пример 3. Обновление нескольких строк в таблице:

UPDATE P

SET PNAME = "Пушников"

WHERE P.PNUM = 1;

DELETE - удаление строк в таблице

Пример 4. Удаление нескольких строк в таблице:

DELETE FROM P

WHERE P.PNUM = 1;

Пример 5. Удаление всех строк в таблице:

DELETE FROM P;

Примеры использования оператора SELECT

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

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

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

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

Отбор данных из одной таблицы

Пример 6. Выбрать все данные из таблицы поставщиков (ключевые слова SELECT… FROM…):

SELECT *

FROM P;

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

Пример 7. Выбрать все строки из таблицы поставщиков, удовлетворяющих некоторому условию (ключевое слово WHERE…):

SELECT *

FROM P

WHERE P.PNUM > 2;

Замечание. В качестве условия в разделе WHERE можно использовать сложные логические выражения, использующие поля таблиц, константы, сравнения (>, <, = и т.д.), скобки, союзы AND и OR, отрицание NOT.

Пример 8. Выбрать некоторые колонки из исходной таблицы (указание списка отбираемых колонок):

SELECT P.NAME

FROM P;

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

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

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

SELECT DISTINCT P.NAME

FROM P;

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

Пример 10. Использование скалярных выражений и переименований колонок в запросах (ключевое слово AS…):

SELECT

TOVAR.TNAME,

TOVAR.KOL,

TOVAR.PRICE,

"=" AS EQU,

TOVAR.KOL*TOVAR.PRICE AS SUMMA

FROM TOVAR;

В результате получим таблицу с колонками, которых не было в исходной таблице TOVAR:

TNAME	KOL	PRICE	EQU	SUMMA
Болт	10	100	=	1000
Гайка	20	200	=	4000
Винт	30	300	=	9000

Пример 11.Упорядочение результатов запроса (ключевое слово ORDER BY…):

SELECT

PD.PNUM,

PD.DNUM,

PD.VOLUME

FROM PD

ORDER BY DNUM;

В результате получим следующую таблицу, упорядоченную по полю DNUM:

PNUM	DNUM	VOLUME
1	1	100
2	1	150
3	1	1000
1	2	200
2	2	250
1	3	300

Пример 12. Упорядочение результатов запроса по нескольким полям с возрастанием или убыванием (ключевые слова ASC, DESC):

SELECT

PD.PNUM,

PD.DNUM,

PD.VOLUME

FROM PD

ORDER BY

DNUM ASC,

VOLUME DESC;

В результате получим таблицу, в которой строки идут в порядке возрастания значения поля DNUM, а строки, с одинаковым значением DNUM идут в порядке убывания значения поля VOLUME:

PNUM	DNUM	VOLUME
3	1	1000
2	1	150
1	1	100
2	2	250
1	2	200
1	3	300

Замечание. Если явно не указаны ключевые слова ASC или DESC, то по умолчанию принимается упорядочение по возрастанию (ASC).

Отбор данных из нескольких таблиц

Пример 13. Естественное соединение таблиц (способ 1 - явное указание условий соединения):

SELECT

P.PNUM,

P.PNAME,

PD.DNUM,

PD.VOLUME

FROM P, PD

WHERE P.PNUM = PD.PNUM;

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

PNUM	PNAME	DNUM	VOLUME
1	Иванов	1	100
1	Иванов	2	200
1	Иванов	3	300
2	Петров	1	150
2	Петров	2	250
3	Сидоров	1	1000

Замечание. Соединяемые таблицы перечислены в разделе FROM оператора, условие соединения приведено в разделе WHERE. Раздел WHERE, помимо условия соединения таблиц, может также содержать и условия отбора строк.

Пример 14. Естественное соединение таблиц (способ 2 - ключевые слова JOIN… USING…):

SELECT

P.PNUM,

P.PNAME,

PD.DNUM,

PD.VOLUME

FROM P JOIN PD USING PNUM;

Замечание. Ключевое слово USING позволяет явно указать, по каким из общих колонок таблиц будет производиться соединение.

Пример 15. Естественное соединение таблиц (способ 3 - ключевое слово NATURAL JOIN):

SELECT

P.PNUM,

P.PNAME,

PD.DNUM,

PD.VOLUME

FROM P NATURAL JOIN PD;

Замечание. В разделе FROM не указано, по каким полям производится соединение. NATURAL JOIN автоматически соединяет по всем одинаковым полям в таблицах.

Пример 16. Естественное соединение трех таблиц:

SELECT

P.PNAME,

D.DNAME,

PD.VOLUME

FROM

P NATURAL JOIN PD NATURAL JOIN D;

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

PNAME	DNAME	VOLUME
Иванов	Болт	100
Иванов	Гайка	200
Иванов	Винт	300
Петров	Болт	150
Петров	Гайка	250
Сидоров	Болт	1000

Пример 17. Прямое произведение таблиц:

SELECT

P.PNUM,

P.PNAME,

D.DNUM,

D.DNAME

FROM P, D;

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

PNUM	PNAME	DNUM	DNAME
1	Иванов	1	Болт
1	Иванов	2	Гайка
1	Иванов	3	Винт
2	Петров	1	Болт
2	Петров	2	Гайка
2	Петров	3	Винт
3	Сидоров	1	Болт
3	Сидоров	2	Гайка
3	Сидоров	3	Винт

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

Пример 18. Соединение таблиц по произвольному условию. Рассмотрим таблицы поставщиков и деталей, которыми присвоен некоторый статую (см. пример 8 из предыдущей главы):

PNUM	PNAME	PSTATUS
1	Иванов	4
2	Петров	1
3	Сидоров	2

Таблица 1 Отношение P (Поставщики)

DNUM	DNAME	DSTATUS
1	Болт	3
2	Гайка	2
3	Винт	1

Таблица 2 Отношение D (Детали)

Ответ на вопрос "какие поставщики имеют право поставлять какие детали?" дает следующий запрос:

SELECT

P.PNUM,

P.PNAME,

P.PSTATUS,

D.DNUM,

D.DNAME,

D.DSTATUS

FROM P, D

WHERE P.PSTATUS >= D.DSTATUS;

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

PNUM	PNAME	PSTATUS	DNUM	DNAME	DSTATUS
1	Иванов	4	1	Болт	3
1	Иванов	4	2	Гайка	2
1	Иванов	4	3	Винт	1
2	Петров	1	3	Винт	1
3	Сидоров	2	2	Гайка	2
3	Сидоров	2	3	Винт	1

Использование имен корреляции (алиасов, псевдонимов)

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

Пример 19. Отобрать все пары поставщиков таким образом, чтобы первый поставщик в паре имел статус, больший статуса второго поставщика:

SELECT

P1.PNAME AS PNAME1,

P1.PSTATUS AS PSTATUS1,

P2.PNAME AS PNAME2,

P2.PSTATUS AS PSTATUS2

FROM

P P1, P P2

WHERE P1.PSTATUS1 > P2.PSTATUS2;

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

PNAME1	PSTATUS1	PNAME2	PSTATUS2
Иванов	4	Петров	1
Иванов	4	Сидоров	2
Сидоров	2	Петров	1

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

Номер контрагента NUM	Наименование контрагента NAME
1	Иванов
2	Петров
3	Сидоров

Таблица 3 Отношение CONTRAGENTS

Номер детали DNUM	Наименование детали DNAME
1	Болт
2	Гайка
3	Винт

Таблица 4 Отношение DETAILS (Детали)

Номер поставщика PNUM	Номер получателя CNUM	Номер детали DNUM	Поставляемое количество VOLUME
1	2	1	100
1	3	2	200
1	3	3	300
2	3	1	150
2	3	2	250
3	1	1	1000

Таблица 5 Отношение CD (Поставки)

В таблице CD (поставки) поля PNUM и CNUM являются внешними ключами, ссылающимися на потенциальный ключ NUM в таблице CONTRAGENTS.

Ответ на вопрос "кто кому что в каком количестве поставляет" дается следующим запросом:

SELECT

P.NAME AS PNAME,

C.NAME AS CNAME,

DETAILS.DNAME,

CD.VOLUME

FROM

CONTRAGENTS P,

CONTRAGENTS C,

DETAILS,

WHERE

P.NUM = CD.PNUM AND

C.NUM = CD.CNUM AND

D.DNUM = CD.DNUM;

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

Наименование Поставщика PNAME	Наименование получателя CNAME	Наименование детали DNAME	Поставляемое количество VOLUME
Иванов	Петров	Болт	100
Иванов	Сидоров	Гайка	200
Иванов	Сидоров	Винт	300
Петров	Сидоров	Болт	150
Петров	Сидоров	Гайка	250
Сидоров	Иванов	Болт	1000

Замечание. Этот же запрос может быть выражен очень большим количеством способов, например, так:

SELECT

P.NAME AS PNAME,

C.NAME AS CNAME,

DETAILS.DNAME,

CD.VOLUME

FROM

CONTRAGENTS P,

CONTRAGENTS C,

DETAILS NATURAL JOIN CD

WHERE

P.NUM = CD.PNUM AND

C.NUM = CD.CNUM;

Использование агрегатных функций в запросах

Пример 21. Получить общее количество поставщиков (ключевое слово COUNT):

SELECT COUNT(*) AS N

FROM P;

В результате получим таблицу с одним столбцом и одной строкой, содержащей количество строк из таблицы P:

Пример 22. Получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM, MAX, MIN, AVG):

SELECT

SUM(PD.VOLUME) AS SM,

MAX(PD.VOLUME) AS MX,

MIN(PD.VOLUME) AS MN,

AVG(PD.VOLUME) AS AV

FROM PD;

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

SM	MX	MN	AV
2000	1000	100	333.33333333

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

Пример 23. Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY…):

SELECT

PD.DNUM,

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM;

Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результирующую таблицу будет включена одна строка:

DNUM	SM
1	1250
2	450
3	300

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

SELECT

PD.PNUM,

PD.DNUM,

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM;

Причина ошибки в том, что в список отбираемых полей включено поле PNUM, которое не входит в раздел GROUP BY. И действительно, в каждую полученную группу строк может входить несколько строк с различными значениями поля PNUM. Из каждой группы строк будет сформировано по одной итоговой строке. При этом нет однозначного ответа на вопрос, какое значение выбрать для поля PNUM в итоговой строке.

Замечание. Некоторые диалекты SQL не считают это за ошибку. Запрос будет выполнен, но предсказать, какие значения будут внесены в поле PNUM в результатирующей таблице, невозможно.

Пример 24. Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING…):

Замечание. Условие, что суммарное поставляемое количество должно быть больше 400 не может быть сформулировано в разделе WHERE, т.к. в этом разделе нельзя использовать агрегатные функции. Условия, использующие агрегатные функции должны быть размещены в специальном разделе HAVING:

SELECT

PD.DNUM,

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM

HAVING SUM(PD.VOLUME) > 400;

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

DNUM	SM
1	1250
2	450

Замечание. В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY.

Задание на лабораторную работу:

1 .На языке SQL написать команду создания таблицы с именем TABL1 со следующими характеристиками столбцов и ограничений целостности:

А – целый

В – символьный (25 символов)

С – символьный (3 символа)

2. На языке SQL написать команду добавления в таблицу TABL1 столбца со следующими характеристиками:

Имя столбца – D
Тип данных – строковый
10 символов

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

Кратко охарактеризуйте технологию создания запроса
Что такое запрос на удаление
Что такое запрос на изменение
Что такое запрос на обновление записей
Что такое запрос на создание таблицы
Что такое перекрестный запрос

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

Тема: Построение запросов вычисления и подведения

итогов к учебной базе данных

Цели:

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

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

Знать:

типы взаимосвязей таблиц
основные конструкции SQL – операторов
принципы создания базы данных при помощи SQL

Уметь:

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

Ход выполнения лабораторной работы:

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

Построение вычисляемых полей

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

Стандарты SQL позволяют явным образом задавать имена столбцов результирующей таблицы, для чего применяется фраза AS.

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

SELECT Товар.Название, Товар.Цена,

Сделка.Количество,

Товар.Цена*Сделка.Количество AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

Пример 2. Получить список фирм с указанием фамилии и инициалов клиентов.

SELECT Фирма, Фамилия+""+

Left(Имя,1)+"."+Left(Отчество,1)+"."AS ФИО

FROM Клиент

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

Пример 3. Получить список товаров с указанием года и месяца продажи.

SELECT Товар.Название, Year(Сделка.Дата)

AS Год, Month(Сделка.Дата) AS Месяц

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

В запросе использованы встроенные функции Year и Month для выделения года и месяца из даты.

Использование итоговых функций

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

Пользователю доступны следующие основные итоговые функции:

Count (Выражение) - определяет количество записей в выходном наборе SQL-запроса;
Min/Max (Выражение) - определяют наименьшее и наибольшее из множества значений в некотором поле запроса;
Avg (Выражение) - эта функция позволяет рассчитать среднее значение множества значений, хранящихся в определенном поле отобранных запросом записей. Оно является арифметическим средним значением, т.е. суммой значений, деленной на их количество.

Sum (Выражение) - вычисляет сумму множества значений, содержащихся в определенном поле отобранных запросом записей.

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

Все эти функции оперируют со значениями в единственном столбце таблицы или с арифметическим выражением и возвращают единственное значение. Функции COUNT, MIN и MAX применимы как к числовым, так и к нечисловым полям, тогда как функции SUM и AVG могут использоваться только в случае числовых полей, за исключением COUNT(*). При вычислении результатов любых функций сначала исключаются все пустые значения, после чего требуемая операция применяется только к оставшимся конкретным значениям столбца. Вариант COUNT(*) - особый случай использования функции COUNT, его назначение состоит в подсчете всех строк в результирующей таблице, независимо от того, содержатся там пустые, дублирующиеся или любые другие значения.

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

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

Пример 4. Определить первое по алфавиту название товара.

SELECT Min(Товар.Название) AS Min_Название

FROM Товар

Пример 5. Определить количество сделок.

SELECT Count(*) AS Количество_сделок

FROM Сделка

Пример 6. Определить суммарное количество проданного товара.

SELECT Sum(Сделка.Количество)

AS Количество_товара

FROM Сделка

Пример 6.7. Определить среднюю цену проданного товара.

SELECT Avg(Товар.Цена) AS Avg_Цена

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара;

Пример 8. Подсчитать общую стоимость проданных товаров.

SELECT Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

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

Часто в запросах требуется формировать промежуточные итоги, что обычно отображается появлением в запросе фразы "для каждого...". Для этой цели в операторе SELECT используется предложение GROUP BY. Запрос, в котором присутствует GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT, после чего для каждой отдельной группы создается единственная суммарная строка. Стандарт SQL требует, чтобы предложение SELECT и фраза GROUP BY были тесно связаны между собой. При наличии в операторе SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Более того, предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции, константы и выражения, включающие комбинации перечисленных выше элементов.

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

Если совместно с GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группированию подвергаются только те строки, которые удовлетворяют условию поиска.

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

Пример 9. Вычислить средний объем покупок, совершенных каждым покупателем.

SELECT Клиент.Фамилия, Avg(Сделка.Количество)

AS Среднее_количество

FROM Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента

GROUP BY Клиент.Фамилия

Фраза "каждым покупателем" нашла свое отражение в SQL-запросе в виде предложения GROUP BY Клиент.Фамилия.

Пример 10. Определить, на какую сумму был продан товар каждого наименования.

SELECT Товар.Название,

Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Товар.Название

Пример 11. Подсчитать количество сделок, осуществленных каждой фирмой.

SELECT Клиент.Фирма, Count(Сделка.КодСделки)

AS Количество_сделок

FROM Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента

GROUP BY Клиент.Фирма

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

SELECT Клиент.Фирма, Sum(Сделка.Количество)

AS Общее_Количество,

Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN

(Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента)

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Клиент.Фирма

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

SELECT Товар.Название, Month(Сделка.Дата)

AS Месяц,

Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Товар.Название, Month(Сделка.Дата)

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

SELECT Товар.Название, Month(Сделка.Дата)

AS Месяц,

Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

WHERE Товар.Сорт="Первый"

GROUP BY Товар.Название, Month(Сделка.Дата)

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

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

Условия в HAVING отличаются от условий в WHERE:

HAVING исключает из результирующего набора данных группы с результатами агрегированных значений;

WHERE исключает из расчета агрегатных значений по группировке записи, не удовлетворяющие условию;

в условии поиска WHERE нельзя задавать агрегатные функции.

Пример 15. Определить фирмы, у которых общее количество сделок превысило три.

SELECT Клиент.Фирма, Count(Сделка.Количество)

AS Количество_сделок

FROM Клиент INNER JOIN Сделка

ON Клиент.КодКлиента=Сделка.КодКлиента

GROUP BY Клиент.Фирма

HAVING Count(Сделка.Количество)>3

Пример 16.Вывести список товаров, проданных на сумму более 10000 руб.

SELECT Товар.Название,

Sum(Товар.Цена*Сделка.Количество)

AS Стоимость

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Товар.Название

HAVING Sum(Товар.Цена*Сделка.Количество)>10000

Пример 17. Вывести список товаров, проданных на сумму более 10000 без указания суммы.

SELECT Товар.Название

FROM Товар INNER JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара

GROUP BY Товар.Название

HAVING Sum(Товар.Цена*Сделка.Количество)>10000

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

Кратко охарактеризуйте технологию создания запроса
Что такое запрос на удаление
Что такое запрос на изменение
Что такое запрос на обновление записей

1 / 21 2 > Следующая >>>

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

#
09.04.2015446.33 Кб14Методичка по организации.rtf
#
27.08.20191.31 Mб22Методичка по РЦБ для финансистов.doc
#
09.04.2015758.24 Кб78Методичка по Соц.упр.pdf
#
09.04.2015161.28 Кб20Методичка Практика.doc
#
04.12.2018263.68 Кб3Методичка ФК и ПИЭ для написания курсовой БУХГА....doc
#
09.04.20151.89 Mб19методичка.doc
#
19.11.20191.27 Mб12Методичка_2005 (Excel,Word и Курс_работа)_2.doc
#
15.03.201617.28 Кб38Методология судебной экспертизы.docx
#
25.08.20191.49 Mб29Методуказания Лабораторки ОДД.doc
#
09.04.2015177.15 Кб7МетодУказЛабРабот.doc
#
12.09.2019698.88 Кб109методы исследования сырья лекции.doc