АЛМАТИНСКИЙ ИНСТИТУТ ЭНЕРГЕТИКИ И СВЯЗИ 

Кафедра инженерной кибернетики 

Проектирование баз данных 

Методические указания

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

Алматы 2010 

СОСТАВИТЕЛЬ: В.М. Тарасов. Проектирование баз данных. Методические указания к выполнению лабораторных работ для студентов всех форм обучения специальности 050718 – Электроэнергетика. – Алматы: АИЭС, 2010. – 30 с. 

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

Введение

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

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

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

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

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

Требования к содержанию состава отчета:

1.        Титульный лист.

2.        Название и цель лабораторной работы.

3.        ER диаграмма, если она требуется по заданию.

4.        Созданные таблицы с указанием свойств полей.

5.        Значения полей по умолчанию.

6.        Схема данных.

7.        Запросы и отчеты согласно заданию.

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

Отчет  печатается на листах А4 с полями: левое – 25 мм, правое – 18 мм, верхнее – 20 мм, нижнее – 25 мм. Текст выполняется с помощью Word черным шрифтом с кеглем не менее 12 на белом фоне. Отступ первой строки 5 символов. В таблицах и рисунках допустим размер шрифта 11 кеглей. Листы должны быть пронумерованы. Отчет студентом защищается. Студент должен знать основные положения БД, примененных в лабораторных работах и ответить на контрольные вопросы.

1 Лабораторная работа. Создание таблиц и автоформ

Цель работы: создание простой таблицы и автоформы. Знакомство с типами данных. Выполнение поиска, сортировки и фильтрации.

Теоретическая часть

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

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

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

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

Создание простой БД

Запуск базы данных: Пуск – программы – Microsoft Access – Новая база данных. Программа сразу предложит сохранить БД с именем db1. Изменив это имя  на удобное для вас, выполните команду Создать.

В появившемся окне Access, выделив объект Таблица,  выполните команды: Создать – Конструктор таблиц.  Заполните окно конструктора по образцу. Для установки типа данных - Числовой,  нажмите на треугольную кнопку и выберите Числовой.

Т а б л и ц а  1.1 – Отношение Студенты

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

Ключевое поле однозначно определяет запись базы данных. В данной таблице это Счетчик. Замените предлагаемое программой имя  таблицы на имя Т1–Студенты.

Далее следует открыть таблицу Т1–Студенты и заполнить 5 записей  для учащихся первой группы. Поле Код студеннта не изменять!!  Закройте таблицу.

В окне базы данных выберите объект Форма. Далее  выберите кнопки Создать – Автоформа: в столбец. Установите в качестве источника данных созданную вами таблицу. Заполните дополнительно 5 новых записей для студентов другой группы. Передвижение по полям выполняется клавишей Tab, по записям кнопками снизу формы. Рекомендуется одинаковые значения полей заполнять с помощью копирования, чтобы избежать разного написания одинаковых по смыслу атрибутов, которые будут являться ошибками в БД. Нажмите на кнопку Закрыть. Сохраните форму, изменив предлагаемое имя на Ф1-Студенты. В окне объекты выберите объект Таблица, затем - кнопку Открыть. Убедитесь, что в таблице Т1–Студенты стало 10 записей.

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

Конструирование. Откройте таблицу Т1–Студенты, включите конструктор таблицы, исправьте имя поля Год рождения на имя Дата рождения, и измените тип данных на Дата/Время. Добавьте поле Телефон и установите тип данных Текстовый, с маской 000-00-00. Маска не позволит неверно заполнить в поле количество цифр. Закройте конструктор и откройте таблицу.

В поле Дата рождения появились странные даты, например, 5.09.1900 г. Почему? В ПК установлено, что число 1 обозначает дату 31.12.1899 г, 2 – 1.01.1900 г. 3 – 2.01.1900 г и т.д.

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

Поиск.  Поиск данных начинается  командами Правка – Найти. В редактируемом поле «В образце» пишут точное  сочетание символов, которые требуется найти в выделенном поле. Если точное написание не известно, то можно применить специальные символы: «?» – обозначает один любой символ, «*» - обозначает любое количество любых символов.

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

-  «Поле целиком». Совпадение образца с полем целиком;

- «С любой частью поля». Совпадение образца с любой частью поля;

- то же, с учетом регистра образца и символов в поле (установите галочку).

Примеры: При выборе «Поле целиком»

- образец «Ива» найдет только указанное сочетание;

- образец «Ива?» найдет  «Иван»;

- образец «Ива*» найдет «Иван», «Иванушка» и т.д.

Фильтрация. Фильтрация выполняется командами Записи – Фильтр – Изменить фильтр.

Снизу окна фильтра имеются закладки Найти, ИЛИ. Сначала задаются условия поиска для закладки Найти, необходимо выбрать поле и выбрать из перечня нужное значение. Например, укажите Иванов в поле Фамилия. Затем можно указать дополнительное условие строкой ниже, например, номер  телефона, второе условие ищется по принципу ИЛИ, то есть это не обязательно номер телефона Иванова. После задания условий поиска выполните команды Фильтр – Применить фильтр. На экране останутся видимыми только записи, удовлетворяющие заданным условиям отбора. Возврат на экран всех записей выполняется командами Записи – Удалить фильтр

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

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

1.            Что такое база данных?

2.            Что описывает таблица?

3.            Что описывает поле?

4.            Что описывает запись?

5.            Назовите известные типы данных.

6.            Поясните назначение формы.

7.            Как в ПК хранятся даты?

8.            Назовите специальные символы, применяющиеся при поиске данных.

9.            Расскажите о фильтрации.

10.       Как работает сортировка?

2 Лабораторная работа. Списки, сортировка, схема данных

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

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

Создание полей со списками. Откройте свою базу данных. Представьте таблицу Т1–Студенты в режиме конструктора командами Таблица – Конструктор.  Выделите строку Пол и удалите ее. Снова создадим  поле Пол, но будем заполнять его с помощью списка. Для этого установите курсор в первую, пустую ячейку списка полей. Введите имя поля Пол. Установите курсор в колонку Тип данных, нажмите на кнопку выбора (треугольник) и выберите Мастер подстановок. В окне Создание подстановок выберите пункт Будет введен фиксированный список значений и нажмите кнопку Далее. Число столбцов примите равным единице и введите «Муж» и «Жен» на разных строках. Нажмите Готово.  Нажмите закладку Подстановка,  и вы увидите созданный список подстановки. Закройте Конструктор и сохраните изменения. Заполните созданное поле в таблице, используя список.

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

Выделите все поля и выполните команды Формат – Ширина столбца – По ширине данных.

Выполните фильтрацию базы данных, оставив видимыми только, например, сотрудников женского пола и сотрудников одной из должностей. Примените команды Записи – Фильтр – Изменить фильтр. Для этого с помощью закладки Найти найдите записи с сотрудниками женского или мужского пола выбранной должности, выполнив команды Фильтр – Применить фильтр. Сделайте все записи видимыми командами Записи – Удалить фильтр.

Перемещение полей. Выделите поле Должность, поместив в него курсор, повторно нажмите мышкой на наименование поля и, если появится прямоугольник со стрелкой, переместите его между полями  Код студента и Фамилия. Должно переместиться  все поле Должность. Выделите поле Имя и сделайте его невидимым командами Формат – Скрыть столбцы.

Сортировка по двум полям. Выделите рядом расположенные  поля Должность и Фамилия и выполните сортировку по возрастанию. Внутри определенных значений должности фамилии будут отсортированы также по возрастанию. Командами Формат – Отобразить столбцы верните таблицу к исходному состоянию.

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

Создание связи между таблицами Т1-Студенты и Т2-Сессия.

1)        Закройте объект Т1–Студенты, отражающий сущность Студент. Снимите выделение с существующей таблицы, щелкнув мышью на пустое место.

2)        Во второй таблице отразим результаты экзаменов в сессии, т.е. это будет сущность Сессия. В ней должны отражаться фамилии  студентов и их оценки на экзамене. Однако, если создавать вторую таблицу с полями и необходимыми типами данных Фамилия  – текстовый, Иностранный язык – числовой, Физика - числовой, Математика – числовой, то в этом случае пришлось бы заполнять поле Фамилия, уже существующее в таблице Т1–Студенты.  То пришлось бы дважды заполнять одинаковыми фамилиями разные таблицы. Такая БД имеет избыточность и потенциальную противоречивость, и при неверном случайном написании какой либо фамилии возникает противоречивость данных. При  поиске информации в такой БД могут выдаваться неверные сведения.

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

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

4)        Для установки связей переместите мышью поле Код студента из первой таблицы Т1–Студенты  в таблицу Т2–Сессия на место поля Код студента. В появившемся окне диалога Связи нажмите кнопку Создать. Подтвердите целостность БД и каскадное удаление записей. В окне Схема данных появятся стрелки связи Один ко многим (см. рисунок 2.1). Эта связь обозначает, что один студент из первой таблицы Т1–Студенты  в таблице Т2–Сессия может быть записан несколько раз, например, при пересдаче экзамена или после неявки. Таблица Т1-Студенты называется главной таблицей, а таблица Т2-Сессия – подчиненной. Закройте окно, сохранив изменения.

Обратите внимание, что в таблице Т1-Студенты тип поля Код студента –счетчик, а в подчиненной таблице тип поля Код студента - числовой.

Откройте таблицу Т1–Студенты, в левой ее части появились новые кнопки в виде знака плюс и, нажав на кнопку на определенной записи, мы увидим номер записи для выбранного студента в подчиненной таблице Т2-Сессия  и оценки, которые он получил. Здесь также  можно и заполнить подчиненную таблицу Т2–Сессия. Удалите 1-2 записи в таблице Т1–Студенты,  откройте таблицу Т2–Сессия и посмотрите результат.

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

Т а б л и ц а  2.1 – Варианты заданий

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

1.         Поясните назначение фиксированного набора списка значений.

2.         Поясните назначение столбца подстановок из таблицы или запроса в списке.

3.         Назначение и работа фильтра.

4.         Двойная сортировка.

5.         Чем отличается подпись поля от его имени?

6.         Поясните термин отношение один ко многим.

7.         Что показывается на схеме данных?

8.         Как устанавливается связь в схеме данных?

9.         Как можно заполнять подчиненную таблицу из главной таблицы?

10.    Что такое целостность базы данных?

11.    Что такое каскадное удаление связанных записей?

3 Лабораторная работа. Создание запроса. Вычисления в БД

Цель работы: Создание связи между сущностями, выполнение расчетов.

Связь сущностей. В лабораторной работе 2 была создана база данных, состоящая из двух таблиц. Эти таблицы отражали две сущности: Сессия и Студент. Сущность Сессия имела атрибуты: Код студента, ФИО студента, Иностранный язык, Физика, Математика. Сущность Студент имела атрибуты: Код студента, Группа, Дата рождения, Телефон, Пол, Должность, Зарплата. Между любыми сущностями в базе данных  существуют связи, часто обозначаемые глаголами. Для связи  наших сущностей подходит связь Сдает. На диаграммах сущности и связи отображаются, как показано на рисунке 3.1, такие схемы называются ER-диаграммы, от слов entity (сущность) и relationship (связь).

Рисунок 3.1

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

Создайте запрос для вывода сведений из 2-х таблиц: из таблицы Т1-Студенты с анкетными данными и таблицы Т2-Сессия. Для этого выполните действия, описанные ниже.

Откройте свою базу данных. В окне База данных выберите вкладку Запрос. Нажмите кнопку Создать.

Выберите вариант Простой запрос, в таком запросе не устанавливают условий отбора информации внутри полей и выводятся все записи таблиц. В окне Создание простых запросов из перечня Таблицы/запросы выберите таблицу Т1-Студенты и в ней поля: ФИО студента, Группа. Переместите их по очереди кнопкой “>” в окно Выбранные поля.

В окне Таблицы/Запросы перейдите на таблицу Т2-Сессия и переместите поля Иностранный язык, Физика, Математика. Нажмите кнопку Далее и присвойте запросу имя Результаты экзаменов, нажмите кнопку Готово. Откроется окно с результатами запроса.

Если имя запроса не соответствует имени Результаты экзаменов, переименуйте имя запроса командами Правка – Переименовать. Если в запросе Результаты экзаменов не будет данных, то это значит, что в таблице нет данных, и для 10 учащихся заполните сведения по оценкам. Измените оценки в таблице Т2-Сессия и посмотрите, как изменился запрос.

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

Запрос с вычислениями по всему полю

Добавьте в таблицу Т1-Студенты поле Зарплата с типом данных Денежный, установите ограничения по величине, например, от 0 до 100000 тенге, и заполните ее.

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

Создание запроса с помощью мастера.

1.       Выберите поле Зарплата из таблицы Т1-Студенты.

2.       Включите мастер по созданию запросов.

Выберите далее Итоговый отчет  и подсчет Sum, Avg, Min, Max и обязательно подсчет количества записей в таблице Т1-Студенты. Присвойте запросу имя Студенты: Зарплата.

Значение зарплаты в таблице Т1-Студенты измените и вновь откройте запрос Студенты Зарплата. Какие есть изменения?

Создание запроса с помощью конструктора.

1.         Включите конструктор для объекта Запрос.

Добавьте в запрос поле Зарплата из таблицы Т1-Студенты и поля Физика, Иностранный язык и Математика из таблицы Т2-Сессия.

2.         Щелкните правой кнопкой мыши и выберите команду Групповые операции.

3.         Замените слово Группировка в разных полях на формулы Max, Avg, Min. У вас должен получиться запрос, похожий на запрос на рисунке 3.2.

Рисунок 3.2 – Запрос с вычислениями

Выполнение вычислений в БД. Вычисления выполняются при создании запроса с помощью вычисляемых полей.

Пример выполнения. Создайте простой запрос с полями ФИО студента, Должность, Зарплата. Откройте его в конструкторе и в свободной колонке создайте вычисляемое поле, напечатав - Доллары:[Зарплата]/150. Здесь Доллары - имя нового поля, Зарплата - имя существующего поля. Имя существующего поля необходимо печатать точно. Примерный вид запроса с вычисляемым полем показан в таблице 3.1.

Т а б л и ц а  3.1- Создание вычисляемого поля

Выполните задания по вариантам, приведенным в таблице 3.2.

Т а б л и ц а  3.2 – Варианты создания вычисляемых полей

Продолжение таблицы 3.2

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

1.         Назначение запросов. Поясните термин Простой запрос.

2.         Как создаются условия для выборки записей?

3.         Какой тип данных выбирается  для поля Зарплата?

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

5.         Чем заменяются слова Групповые операции в запросе при  вычислениях?

6.         Что такое сущность?

7.         Какие атрибуты у сущности Студент?

8.         Какие атрибуты у сущности Сессия?

9.         Как показывается связь сущностей?

10.    Как создается вычисляемое поле?

11.    С какой точностью выполняются вычисления?

4 Лабораторная работа. Выполнение запросов и отчетов

Цель работы: Создание БД с заданными сущностями. Выполнение отчетов и запросов с условиями.

Сущности базы данных определяются, исходя из ее назначения, и того, какие именно сведения должны затем выбираться из базы данных. Создадим базу данных Списки студентов. В ней будут сущность Группа с атрибутами: Группа, Курс, Кол-во студентов, ФИО куратора - и сущность Студент с атрибутами: ФИО студента, Дата рождения, Пол. Связь между этими сущностями определим как «Учится» (см рисунок 4.1). Сущность Студент будет подчиненная, сущность Группа –главная.

Рисунок 4.1

Для создания связи и схемы данных добавим первичные и вторичные ключи (в таблицах ключевые поля).

Выполнение работы:

1.    Откройте новую базу данных, присвойте ей имя Списки.

2.    Создайте главную таблицу Группы.

Внимание! База данных из условия сохранения целостности данных заполняется только после создания схемы данных.

Т а б л и ц а  4.1 – Отношение Группы

3.    Создайте подчиненную таблицу Студенты.

Т а б л и ц а  4.2 – Отношение Студент

4.   Командами Сервис – Схема данных создайте схему данных. Добавьте в схему обе таблицы, перетащите поле Код группы из одной таблицы в другую. Установите галочку во флажке Целостность данных.  Должна установиться связь один ко многим (1..). Одному названию группы в таблице Группы соответствует несколько записей в таблице Студенты. Действительно, в одной группе учатся несколько студентов. Если связь не устанавливается, следовательно, в таблицах есть ошибки. Проверьте типы полей.

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

6.    Заполните таблицу Группы, причем она должна содержать 2 курса и по 2 группы на каждом курсе.

7.          Вернитесь в таблицу Студенты. Заполните в таблице 5-6 записей для каждой группы.

8.         Создайте простой запрос на выборку с помощью мастера, данные выберите из 2-х таблиц.

Рисунок 4.3

9.          Создайте с помощью конструктора запрос на выборку мужчин 1-го курса. Откройте конструктор и добавьте в запрос обе таблицы. Щелкая 2 раза на поля таблиц, сделайте выборку полей. В поле Курс поставьте условие отбора - «1», в поле Пол – «Муж». Назовите запрос Мужчины 1-го курса.

Рисунок 4.4

10.               Создайте запрос с помощью конструктора на выборку студентов 2 курса с фамилией на букву ...(выберите сами). Для этого в условии отбора напишите, например, К*. Назовите запрос Студенты на букву К.

11.  Действуя по аналогии, создайте запрос на выборку студентов 1992 года рождения. Используйте символы >.. and ..< для условия отбора года. Например, >1990 and  <1993

Рисунок 4.5

Создание отчета с итогами.

В таблицу Студенты добавьте денежное поле Стипендия и заполните его.

Для создания отчета с итогами выберите Отчеты - Создать - Мастер отчетов. Источник данных - таблицы Студенты и Группы. Выберите поля: Курс, Группа, ФИО студента, Стипендия. Нажмите кнопку Далее. Выберите для первого уровня поле Курс, для второго уровня Группа. Отсортируйте данные по полю ФИО студента. Нажмите кнопку Итоги. Установите для поля Стипендия значение Sum, Max, включите кнопку показать Данные и итоги. Далее выберите Макет ступенчатый, Просмотр отчетов. Перешлите отчет в Word. Отредактируйте документ по приведенному образцу и сохраните файл в свою папку в формате Doc. Должен получиться отчет, подобный отчету, приведенному ниже.

Стипендия 1 и 2 курсов

Курс                          Группа            ФИО студента       Стипендия

1                                   БЭ-09-1                           Силин И.          12 000,00 тн.

                                  БЭ-09-1                  Семенова Н.     11 000,00 тн.

                                  БЭ-09-1                  Лебедев М.       10 000,00 тн.

          Итоги по 1 Курсу

                                                                            Sum             33000 тн.

Курс                                                                    Max             12000 тн.

2                                  БЭ-08-3                          Зайцева          Е.          12 000,00 тн.

                                  БЭ-08-3                  Пак К.               13 000,00 тн.

                                  БЭ-08-2                  Те Н.                   9 000,00 тн.

                                  БЭ-08-2                  Туров А.           12 000,00 тн.

          Итоги по 2 Курсу

                                                                            Sum             46000 тн.

                                                                            Max             13000 тн.

ИТОГО по 1 и 2 курсам                                                      79000 тн.

                                                                              18 февраля 2010 г.    

Выполнение работы. Создайте базу данных по заданным сущностям по таблице 4.1 и выполните запросы и отчеты аналогичные приведенным выше.

Т а б л и ц а  4.1 – Варианты заданий

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

1.         Что такое сущность?

2.         Что такое связь сущностей?

3.         Что представляют собой атрибуты сущностей в таблице?

4.         Что такое целостность данных?

5.         Что такое каскадное удаление данных?

6.         Что такое условия отбора?

7.         Как создается отчет с итогами?

8.         Какие стандартные формулы можно включить в отчет?

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

10.    Какие спецсимволы применяются в условии запроса?

11.    Какие ключи бывают в БД?

5 Лабораторная работа. Построение диаграмм

Цель работы: создание запросов по условиям, создание диаграмм. Экспорт объектов в HTML. Редактирование отчета в конструкторе.

Выполнение работы.

1.    Создайте  таблицу Студенты с полями: № зачетки (тип –числовой, первичный ключ), Группа, ФИО студента, Год рождения (тип - числовой), Зарплата (тип - денежный). Заполните 10 записей. Количество групп должно быть две.

2.    Отсортируйте таблицу Студенты по ФИО студента. Создайте такой запрос по таблице Студенты, чтобы в нем отразился размер зарплаты только 4-5 студентов, задав условие отбора по начальным буквам фамилии. Например, ФИО >"С*" And <"И*" или ФИО <"С*" or >"И*". Назовите его Запрос по буквам.

3.    Отсортируйте таблицу Студенты по зарплате. Создайте такой запрос по таблице Студенты, чтобы в нем отразился размер зарплаты только 4 -5 студентов, задав условие отбора по размеру зарплаты.

4.    Перейдите в объекты Формы. Командами Создать – Диаграмма  запустите Мастер диаграмм. В качестве источника данных выберите  Запрос по буквам. Перенесите из запроса поля ФИО студента, Зарплата на поля создаваемой диаграммы. Далее выберите круговую диаграмму. Посмотрите образец. Завершите работу мастера, назовите форму Отбор по ФИО (см. рисунок 5.1).

5.    Откройте полученную форму в режиме конструктора. Увеличьте размер поля конструктора и размер диаграммы. Щелкните правой кнопкой по диаграмме и выберите Объект – Диаграмма – Изменить. Далее щелкните на диаграмму, и в окне Формат ряда данных в закладке Подписи данных выберите Доли. Измените надпись диаграммы. Щелкните на круг, выберите тип диаграммы. Закройте конструктор формы.

6.    Откройте полученную форму (см. рисунок 5.1), скопируйте диаграмму и вставьте в Word.

7.     Самостоятельно постройте линейчатую диаграмму на основе запроса по величине зарплаты.

Экспорт объекта в HTML файл

1.       В окне БД выберите таблицу или запрос. В контекстном меню (правая кнопка мыши) выберите команду Экспорт.

2.       В списке типов файлов выберите пункт Документ HTML. Установите флажки Сохранить формат и  затем Автозагрузка. Выберите и откройте папку для сохранения web- страницы и затем щелкните на команду Экспорт. Далее в окне Параметры ввода в формате HTML нажмите кнопку OK. На экране появится web–страница

Редактирование отчета в режиме конструктора

1.       Создайте отчет с полями Группа, ФИО студента, Год рождения, Зарплата. В поле Итог вычислите сумму зарплаты по группам. Просмотрите отчет.

2.       Откройте отчет в конструкторе. Поля в конструкторе представлены двумя частями. В одной части Имя поля, в другой части Данные. Удалите обе части поля Год рождения полностью. Просмотрите отчет. В нем образовался большой промежуток между полями ФИО студента и Зарплата. Откройте отчет в конструкторе и переместите обе части поля Зарплата влево.

В образце ниже приведенного отчета оставлена только одна группа, остальные удалены, поэтому подсчитана сумма зарплаты только для группы БЭ-09-1, а в поле Итого подсчитана суммарная зарплата для студентов всех групп.

Зарплата по группам (образец)

Код группы                          ФИО             Зарплата

БЭ-09-1                         Ахметов А.             13000 тн

                                    Игнатьев И.             10000 тн

                                      Люсьен Н.             12000 тн

Итоги для группы   БЭ-09-1 (3 записи)   Сумма = 35000 тн

(Пропущенная группа БЭ-09-2)

(Пропущенная группа БЭ-09-3)

ИТОГО по всем группам     119400 тн

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

1.    Могут ли поле Фамилия или поле Номер зачетки быть ключевыми?

2.    Напишите условие отбора фамилий на заданную букву?

3.    Напишите условие отбора фамилий на несколько букв алфавита?

4.    Напишите условие отбора фамилий на буквы «К» и «С»?

5.    Какие формулы можно использовать в отчете?

6.    Какие типы диаграмм возможны в Access?

7.    Как построить диаграмму?

8.    Как переслать документ в HTML файл?

9.    Что можно редактировать в отчете в режиме конструктора?

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

6 Лабораторная работа. Значения полей по умолчанию и условию

Цель работы: научиться управлять свойствами полей.

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

Ниже перечислены свойства полей в Access.

Имя служит для управления БД как переменная в программировании.

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

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

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

Обязательное поле служит для установления требования обязательного заполнения всех его ячеек.

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

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

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

Создайте таблицу Студенты, установив указанные свойства полей.

Т а б л и ц а  6.1 – Отношение Студенты

Создайте таблицу Экзамены с тремя дисциплинами, установив указанные свойства полей. Предусмотрите 3 экзамена. Даты сдачи экзаменов установите с разностью в 3 дня, начиная с текущей даты.

Т а б л и ц а  6.2 - Экзамены

Создайте таблицу Пересдача аналогичную таблице Экзамены. Для этого скопируйте в буфер обмена таблицу Экзамены.  Далее вставьте ее копию, выбрав пункт Только структура. Переименуйте таблицу. Поле Дата экзамена переименуйте в поле Дата пересдачи. Укажите для свойства Значение по умолчанию новый оператор Date()+10.

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

Создание запроса

Создайте запрос с полями ФИО студента, Группа, Дата ТОЭ, ТОЭ, Физика для выборки сведений, из 2-х таблиц: Студенты и Экзамены.

Для этого выполните действия:

1.      В окне База данных выберите вкладку Запрос. Нажмите кнопку Создать.

2.      Выберите вариант Простой запрос. В окне Создание простых запросов из перечня Таблицы/запросы выберите таблицу Студенты и в ней поля: Фамилия, Группа. Переместите их по очереди кнопкой “>” в окно Выбранные поля.

3.      В окне Таблицы/Запросы перейдите на таблицу Экзамены и переместите все поля с датами и дисциплинами. Нажмите кнопку Далее и задайте имя для запроса Результаты экзаменов, нажмите кнопку Готово. Откроется окно с результатами запроса. Если в запросе Результаты экзаменов не будет данных, то проверьте заполнение таблиц и типы данных.

4.      Скопируйте в буфер обмена запрос Результаты экзаменов.  Далее вставьте его копию с названием Наши отличники.

5.      Измените с помощью конструктора условия отбора записей в запросе Наши отличники, например, выберите оценку по английскому языку >7, оценку по физике >=8.

6.      Откройте запрос, и командами Вид – SQL посмотрите, как выглядит запрос на языке запросов SQL, который применяется, когда мы хотим запросить данные из БД, находящейся на удаленном сервере. Перепишите текст запроса.

7.      Создайте запрос Пересдача с полями Фамилия, Группа из таблицы Студенты, Дата ТОЭ, ТОЭ, Дата физики, Физика из таблицы Экзамены и полями Дата ТОЭ, ТОЭ из таблицы Пересдача для вывода результатов экзамена и пересдачи. Примените условие на выборку, чтобы были видны записи только тех, кто на первом экзамене получил 0 баллов (см. таблицу 6.3).

Т а б л и ц а  6.3 – Запрос Пересдача

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

1.    В чем разница между именем поля и его подписью?

2.    Как назначается условие на значение?

3.    Как устанавливается дата по умолчанию?

4.    Какие функции можно установить по умолчанию?

5.    Что такое целостность базы данных?

6.    Как создать несколько таблиц с одинаковой структурой?

7.    Как найти тех студентов, которые сдали экзамены на удовлетворительно?

8.    Как найти тех студентов, которые сдали экзамены на отлично?

9.    Как найти тех студентов, которые сдали экзамены на хорошо?

10.    Назначение языка программирования SQL?

7 Лабораторная работа. Создание перекрестного запроса

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

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

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

Исходя из задач  базы данных, определяем 2 сущности: Студент и Табель для учета часов. Связь между сущностями – Запись часов или просто Часы.. ER – диаграмма базы данных представлена на рисунке 7.1, на ней показаны и атрибуты сущностей.

Рисунок 7.1

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

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

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

Т а б л и ц а  7.1- Отношение Табель

Создайте схему базы данных, устанавливая флажок Целостность базы данных. Заполните таблицы.

Т а б л и ц а   7.2  - Фрагмент заполненной таблицы Табель

Перекрестный запрос создается на основе таблицы или простого запроса. Создайте простой запрос с вычислением зарплаты. Для этого в конструкторе запросов в пустой клетке на строке поля добавьте Зарплата:[Часы работы]*500 - это будет вычисляемое поле. Надпись Часы работы должна точно соответствовать имени поля в таблице с учетом регистра и пробелов. Надпись Зарплата – имя нового вычисляемого поля.

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

Т а б л и ц а  7.3 -  Простой запрос (фрагмент)

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

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

Шаг 2 . Выберите поля для наименования строк. В окне мастера мы сразу увидим, какие будут наименования строк (см. рисунок 7.2).

Рисунок 7.2

Шаг 3. Выберите поле для наименования столбцов перекрестного запроса. В окне мастера вы увидите наименования столбцов (см. рисунок 7.3).

Рисунок 7.3

Шаг 4. Выберите требуемые вычисления. Из набора предложенных функций выберите функцию Сумма (см. рисунок 7.4).

Рисунок 7.4

Вид полученного запроса по учету часов приведен ниже в таблице 7.4.

Т а б л и ц а  7.4 - Перекрестный запрос после редактирования.

Самостоятельно вычислите зарплату по группам студентов.

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

1.    Что такое сущность?

2.    Что такое связь сущностей?

3.    Расскажите о свойствах полей.

4.    Как создается вычисляемое поле?

5.    Что такое перекрестный запрос?

6.    На какой основе создается перекрестный запрос.

7.    Какие шаги выполняются при создании перекрестного запроса?

8.    Какие функции могут быть использованы в перекрестном запросе?

9.    Как подсчитать зарплату каждого студента?

10.              Как подсчитать зарплату по каждой группе?

8 Лабораторная работа. Декомпозиция

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

Рассмотрим два примера нормализации до третьей нормальной формы (3НФ) двух сущностей Учебный план и Преподаватель (см. рисунок 8.1).

Рисунок 8.1

Пример 1. Рассмотрим сущность Учебный план, которая находится в 1НФ и 2НФ, поскольку нет не атомарных данных в аргументах и все неключевые аргументы зависят от ключевых аргументов. Первичным ключом сущности служит атрибут Код плана. Он однозначно определяет все остальные атрибуты. Однако аргумент Кол. Студентов зависит не только от ключевого свойства Код плана, но и от неключевого аргумента Поток. Следовательно, сущность не находится в 3НФ, и для нормализации сущности Учебный план необходимо выделить новую сущность Поток со свойством Кол студентов. При этом из сущности Учебный план удалятся свойства Поток и Кол. студентов. Между сущностями Учебный план и Поток необходимо установить связь Включить с отношением один ко многим. Один поток может быть несколько раз записан в учебном плане. Например, поток БЭ-09-1,2,3,4 слушает лекции по физике, химии, информатике (см. рисунок 8.2).

Рисунок 8.2

Указанную связь при представлении сущностей  в виде отношений, реализуем ключевым свойством Код потока (см. рисунок 8.3).

Следующий этап проектирования - преобразова­ние ER-диаграммы в таблицы.

Рисунок 8.3 -  Декомпозиция сущности Учебный план

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

Т а б л и ц а  8.1 – Отношение Преподаватель

Эта таблица находится в 1НФ и во 2НФ, но не находится в 3НФ, поскольку поля Зав. кафедрой и Телефон  кафедры зависят от неключевого поля Кафедра. Выполним декомпозицию таблицы по варианту 2, рассмотренному в лекциях, выделив новую таблицу с полями: Кафедра (РК), Зав. кафедрой, Телефон кафедры. Поля Зав. кафедрой и Телефон кафедры при этом удалятся  из первоначальной таблицы. Для связи таблиц создадим, согласно предложению Бойса и Кода, второй возможный первичный ключ Код кафедры. Теперь у нас будут две таблицы  8.2 и 8.3, находящиеся в 3НФ. В таблице 8.2 поля Должность и Код кафедры зависят от поля ФИО преподавателя. Считая это поле вторым возможным первичным ключом, убеждаемся, что она находится в 3НФ. Однако, если вспомнить о полных возможных тезках, получаем окончательно, что в таблице одно ключевое поле и нет полей, зависящих от других неключевых полей в таблице. Таким образом, таблица действительно находится в 3НФ.

Т а б л и ц а  8.2 – Отношение Преподаватель

Т а б л и ц а  8.3 – Отношение Кафедра

Построение реляционной схемы

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

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

Результат выполненных действий для обеих первоначальных сущностей показан на рисунке 8.4.

Рисунок 8.4 – Результат нормализации

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

Задание. Выполните нормализацию заданного отношения.

Т а б л и ц а  8.4 – Варианты заданий

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

1.    Что такое декомпозиция таблицы?

2.    Что можно получить после объединения таблиц, полученных в результате декомпозиции?

3.    Что такое полная функциональная зависимость?

4.    Что такое многозначная зависимость?

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

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

7.    Что такое избыточность и противоречивость данных?

8.    Что такое нормальные формы?

9.    Назначение процедуры нормализации таблицы или сущности.

10.    Особенность 3НФ или НФБК?

11.    Что такое возможный первичный ключ?

Литература

1. О. Голицына, Н. Максимов, И. Попов. Базы данных. -М.: “Форум”, 2005.

2. С. Робинсон. Microsoft Access 2000: учебный курс. - СПБ: Питер, 2001.

3. Н. Вирт. Алгоритмы и структуры данных. -М.: Мир, 1989.

4. Дейт К.Дж. Введение в системы баз данных. / Перевод с англ. 7-е издание. М.: Вильямс, 2001.

5. Ю. Шафрин.  Курс компьютерной технологии. Учебное пособие. -М.: АБФ, 1998.

6. MS Access. Меню справка. - Примеры баз данных. – Учебная база данных БОРЕЙ.

Содержание

Введение

3

1 Лабораторная работа. Создание таблиц и автоформ

3

2 Лабораторная работа. Списки, сортировка, схема данных

6

3 Лабораторная работа. Создание запроса. Вычисления в БД

9

4 Лабораторная работа. Выполнение запросов и отчетов

12

5 Лабораторная работа. Построение диаграмм

16

6 Лабораторная работа. Значения полей по умолчанию и условию

18

7 Лабораторная работа. Создание перекрестного запроса

20

8 Лабораторная работа. Декомпозиция

24

Литература

28