Для выполнения практических работ

Для выполнения практических работ

 

 

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

 

для специальности Информационные системы (по отраслям)

 

 

2014

 

ОДОБРЕНЫ Предметной (Цикловой) комиссией специальностей «Информационные системы» и «прикладная информатика»   Протокол заседания № _____ от «____»____________20__ г.   Председатель ПЦК _______________ (              ) «____»_____________20___ г.

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

 

Данное методическое пособие предназначено для развития:

- Навыков создания Баз данных и проектирования их объектов (запросы, формы, отчеты, макросы, модули) в среде СУБД.

- Умений применять инструментальные средства СУБД для разработки прикладных программ.

- Создавать запросы с помощью языка запросов SQL

Навыков управления базами данных

 

Составитель: Бордовских Валерия Алексеевна, преподаватель

 

 

Рецензенты:

 

 

Оглавление

1. Пояснительнаязаписка.. 3

2. МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ ПО РАЗРАБОТКЕ БАЗЫ ДАННЫХ.. 3

2.1 Создание таблиц БД.. 3

2.2 Основы разработки запросов. 3

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

3.1 Синтаксис команды SELECT. 3

3.2 Создание таблиц. 3

3.3  Команды манипулирования данными. 3

3.3.1. Вставка в таблицу новой строки. 3

3.3.2. Удаление строк из таблицы.. 3

3.3.3. Командаupdate. 3

3.4. Формирование запросов (команда SELECT) 3

3.4.1. Выбор по критерию.. 3

3.4.2. Исключение дублирующих значений. 3

3.4.3. Операторы сравнения. 3

3.4.4. Булевы операторы.. 3

3.4.5. Специальные операторы в условиях. 3

3.4.6. Подведение итогов с помощью функций агрегирования. 3

3.4.7. Упорядочение выходных полей. 3

3.4.8. Соединение таблиц. 3

3.5. Объединение таблицы по принципу «сама с собой». 3

3.6. Вложение запросов. 3

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

3.8. Связанные подзапросы в HAVING.. 3

Список использованных источников. 3

 

 

1. Пояснительная записка

 

      

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

 

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

1. Студент должен прийти на практическое занятие подготовленным к выполнению работы.
2. Каждый студент после выполнения работы должен представить отчет о проделанной работе с выводом о работе.
3. Отчет о проделанной работе следует делать в тетради по выполнению практических работ по дисциплине. Содержание отчета указано в практической работе.
4. В случае невыполнения практической работы (части) работа может быть выполнена во внеурочное время по согласованию с преподавателем.

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

1. При выставлении оценки по практическим работам учитывается:

- работа выполнена правильно и в полном объеме;

- студент может пояснить выполнение любого задания;

- на все контрольные вопросы имеются верные ответы;

- работа выполнена в соответствии с требованиями к выполнению работы.

 

Содержание отчета

Отчет должен содержать:

1. Название работы.
2. Цель работы.
3. Ответы на контрольные вопросы.

 

Перечень практических работ

№	Название работы	Кол-во часов
1	Создание таблиц средствами СУБД MS Access	2
2	Создание форм ввода	2
3	Использование элементов управления в формах	2
4	Создание запросов	2
5	Создание запросовна выборку из 2 и более таблиц	2
6	Создание отчета - сделки за последнюю неделю	2
7	Создание отчета текущее состояние склада	2
8	Создание отчета список стандартов	2
9	Построение графиков и диаграмм	2
10	Создание кнопочной формы	2
11	Принципы построения и взаимодействия таблиц	2
12	Ввод, изменение и удаление данных с помощью SQL	2
13	Выборка данных	2
14	Подведение итогов.	2
15	Составление запросов по нескольким таблицам	2

 

                  

МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ ПО РАЗРАБОТКЕ БАЗЫ ДАННЫХ

    2.1 Создание таблиц БД

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

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

 

Таблица - Способы создания таблиц в СУБД Access

Способ создания	Описание
Режим таблицы	Для ввода данных предоставляется таблица с 30 полями. После ее сохранения Access сам решает какой тип данных присвоить каждому полю. Недостаток- невозможность создания поля примечаний
Конструктор таблиц	Предоставляет возможность самостоятельно создавать поля, выбирать типы данных, размер полей и устанавливать свойства полей
Мастер таблиц	Представляет набор таблиц, из которых можно создавать таблицы по своему вкусу. Некоторые таблицы из этого набора могут полностью подойти для Вашего приложения. Тип данных и другие свойства уже определены
Импорт таблиц	Создает в Вашем приложении копии таблиц других приложений источников данных. Иногда после импорта требуется изменить размер поля и некоторые другие свойства. Новой таблице присваивается имя,определяется ключевое поле или Access делает это автоматически
Связь с таблицами	Устанавливается автоматическая непосредственная связь Вашего приложения с данными таблиц других приложений. При этом таблица остается в приложении-источнике и может использоваться несколькими приложениями. При этом экономится объем памяти, поскольку храняться данные только одной таблицы, и время, так как информация обновляется только в одной таблице-источнике

    Если выбран режим “Конструктор таблиц” то появляется окно Таблица: таблица, в котором определяется структура таблицы БД

    Для определения поля в окне Таблица задаются:

                              Имя поля

                              Тип данных

                              Описание- краткий комментарий

                              Свойства поля (Общие свойства – на вкладке Общие и тип элемента управления- на вкладке Подстановка)

    Имя поля. Каждое поле в таблице должно иметь уникальное имя(идентификатор), удовлетворяющее соглашениям об именах объектов в Access. Оно является комбинацией из букв, цифр, пробелов(что не желательно), специальных символов(исключая.! “”).Максимальная длина имени 64 символа.

    Рекомендация: Старайтесь создавать идентификатор поля коротким, без пробелов и отражающим содержание реквизита в записи)

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

1. Текстовый – тип данных по умолчанию. Число символов в поле не должно превышать 255

2. Поле МЕМО. Текстовое данное до 64000 символов

1. Числовой. Числовые данные, используемые в математических вычислениях. Конкретные варианты числового типа и их длина задаются в свойстве Размер поля

2. Денежный. Денежные значения и числовые данные используемые в расчетах с точностью до 15 знаков в целой и до 4 знаков в дробной части. Длина поля 8 байт

3. Дата/время. Значения даты или времени, относящиеся к годам с 100 по 9999 включительно. Длина поля 8 байт.

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

5. Логический. Логические данные, которые могут иметь одно из двух возможных значений Да/Нет(истин/лож, Вкл/Выкл.).Длина поля 1 бит.

6. Поле объекта OLE Объект связанный или внедренный в таблицу Access. Длина поля до 1 Гигабайта.

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

8.

Бланк запроса

При заполнении бланка запроса необходимо:

1. В строке Поле(Field) включить имена полей,используемых в запросе

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

3. В строке Условие отбора(Criteria) задать условия отбора записей

4. В строке Сортировка(Sort) выбрать порядок сортировки записей результата

 

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

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

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

 

В списке полей каждый таблицы на первом месте стоит символ *, который означает “все поля таблицы”. Этот пункт выбирается,если в запрос включаются все поля.

Условия отбора записей

Условия отбора записей могут задаваться для одного или нескольких полей в соответствующей строке бланка запроса.

 

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

В качестве операндов выражения могут использоваться: литералы, константы, идентификаторы(ссылки)

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

Константами являются не изменяющиеся  значения

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

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

 Например [Имя_таблицы]! [Имя_Поля]

 

В выражении условия отбора допускается  использование операторов сравнения и логических операторов: = > < <> Between, In, Like, And, Or, Not

 

Оператор Between позволяет задавать интервал для числового значения. Например, Between 10 And 100 (интервал от 10 до 100)

 

Текстовые значения в выражении вводятся в кавычках, если они содержат пробелы или знаки препинания

 

Вычисляемые поля

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

Выражения

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

Выражения вводятся в бланк запроса в пустую ячейку строки Поле В этой ячейке после нажатия <Enter> или перевода курсора в другую ячейку формируется имя поля и его значение. Например, Выражение1:[Цена]*[Количество]

 

Имя вычисляемого поля – Выражение1 – становиться заголовком столбца в таблице с результатами выполнения запроса. Это имя можно изменить таким образом:

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

Изменение подписи поля в свойствах поля.установить курсор на вычисляемое поле в бланке запроса и открыть окно Свойства поля, щелкнув правой кнопкой мыши и выбрать в контекстном меню Свойства(Properties). В окне Свойства поля (Field Properties) на закладке Общие (General) ввести нужный текст подписи поля встроку Подпись(Caption)

Параметры запроса

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

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

 

Для того чтобы иметь возможность ввести несколько значений для одного поля при выполнении запроса, можно в условии отбора этого поля можно записать два параметра связанных логической операцией OR Например, [Кцеха] OR [Еще_КодЦеха] 

 

Запрос на обновление

Для обновления данных в полях базовых таблиц может быть использован Запрос на обновление (Update Query) создается как З апрос на выборку(Select Query), а затем вокне конструктора запросов превращается в запрос на обновление с помощью кнопки Обновление (Update / Query) на панели инструментов или команды меню Запрос/Обновление (Query/Update). После выполнения этой команды в бланке запроса появляется строка Обновление(Update To)

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

Для обновляемого поля в строку Обновление (Update TO)надо ввести значение или выражение,определяющее новое значение поля. Выражение можно создать с помощью построителя выражений.

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

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

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

 

Запрос на добавление

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

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

Первоначально Запрос на добавление (Append Query) создается как запрос на выборку(Select Query) на одной или нескольких взаимосвязанных таблицах. Затем в окне конструктора запросов он превращается в запрос на добавление с помощью кнопки Добавление (Append Query) на панели инструментов или команды меню Запрос/Добавление(Query/ Append). При этом открывается окно диалога Добавление (Append

Практическая работа1

Создание таблиц средствами СУБД MS Access

 

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

                                                                                                        

                                                                                                           

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

 

Задание 1: Создание справочников                                               

1. Создать два файла в EXCEL и сохранить их в каталоге

D:\номер группы \ ACCESS_ DB\ SOURSE под именами Расположение и Склад

2. В файле Расположение создать таблицу с полями “Код”, “Город”, “Область” и заполните несколько строк

3. В файле Склад создать таблицу с полями: “Код материала”, “Материал”, “Количество” и заполнить несколько строк

Задание 2: Создание структуры данных                                                      

1. Создать базу данных в MS ACCESS и сохранить её в каталоге

D:-номер группы \ ACCESS_ DB\ под именем строительный склад.

2. Таблицы “Расположение” и “Склад” импортировать из ранее созданных файлов MS Excel.

3. Создать таблицы согласно схеме данных и установить между ними соответствующие связи (см. Приложение 1)

                                                                                                                               

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

 

1. Перечислить режимы создания таблиц.

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

3. Файлы, каких типов могут быть использованы при создании таблицы?

4. Опишите действия для создания таблиц из файлов MS Excel?

Практическая работа 2

 

Создание форм ввода

 

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

 

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

Задание 1 Создайте форму на основе таблицы «Склад».

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

 

Задание 2 Создайте форму для таблицы «Расположение»

и примените ранее созданное оформление.

 

Задание 3 Создайте форму для таблицы «Стандарт»,

которая будет отображать материал при выборе кода материала.

 

Задание 4Создайте форму для таблицы «Клиент»,

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

 

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

1.Перечислить способы создания форм.

2.Как создать собственный стиль оформления?

3.На основании каких объектов можно создать форму?

 

 

Практическая работа 3

 

Использование элементов управления в формах

 

Цель работы: Приобрести практические навыки использования  элементов управления в формах и автоматизировать их.

 

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

Задание 1 Создайте форму для таблицы «Заказы», удовлетворяющую условия:

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

·  количество материала для его изготовления

·  количество материала на складе.

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

· количество материала на весь заказ

· остаток на складе, после продажи товара.

Задание 3 Создайте кнопку, которая, будет выполнять проводку накладной (статус- проведен, скад-уменьшение).

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

 

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

1.Опишите ваши действия при создании кнопки.

 

2.Напишите программу с помощью которой вы автоматизировали работу кнопки.

 

Практическая работа 4

 

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

 

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

 

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

 

Задание 1 Создать запрос, показывающий сделки за последнюю неделю.

Задание 2Создать запрос, для поиска клиента по его атрибутам. Три варианта.

Задание 3Создать запрос, который будет находить все сделки совершенные за прошедший месяц 

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

 

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

1. Для чего используются запросы?

2. Какие бывают типы запросов?

Практическая работа 5

 

Создание запросовна выборку из 2 и более таблиц

 

Цель работы: Приобрести практические навыки по  созданию сложных запросов

 

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

 

Задание 1 Напишите запрос, выводящий список товаров, приобретенные заданным клиентом.

 

Задание 2 Напишите запрос, выводящий всех клиентов, которые приобрели товары за определенную дату

 

Задание 3 Напишите запрос, выводящий всех клиентов, которые приобрели товары стоимостью от 20 ты. рублей.

 

Задание 4 Напишите запрос, выводящий стоимость всех товаров, заказанных определенным клиентом в определенном месяце

 

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

1. Как создать параметрический запрос?

2. Как создать вычисляемые поля в запросах?

3. Построитель выражений его назначение.

 

Практическая работа 6

 

Создание отчета сделки за последнюю неделю

Цель работы: Научиться создавать отчеты, сортировать данные в отчете, группировать данные.

 

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

 

Задание 1 Создать отчет, который будет отображать сделки за последнюю неделю, распределяя по стандарту и дате (использовать запрос из прошлой практической работы)

 

Отгрузка готовой продукции по клиенту за период времени          

Дата актуальности                                                                         

Наименование изделия

Цена

Количество

Сумма

Клиент

                   Изделие1              X                           X                           X

                   Изделие2              X                           X                           X

Изделие3              X                           X                           X

       Итого по клиенту                                             XX                     XXX

Клиент

                   Изделие3              X                           X                           X

                   Изделие4              X                           X                           X

                   Изделие8              X                           X                           X

       Итого по клиенту                                              XX                        XXX

       Итого по отчету                                                XXX                     XXXX

 

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

 

1. Каково назначение отчетов?

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

3. Что может служить источником данных для отчета.

Практическая работа 7

 

Создание отчета текущее состояние склада, заказов, клиентов, стандартов

Цель работы: Научиться создавать отчеты, сортировать данные в отчете, группировать данные.

 

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

 

Задание 1. Создать отчет, который будет отображать текущее состояние склада

(использовать запрос из прошлой практической работы)

 

Текущее состояние склада за период времени          

Дата актуальности                                                                         

Наименование изделия

Клиент

дата

Цена

Количество

Сумма

Материал количество

       Изделие1  Клиент                  дата X                X                           X

       Изделие2  Клиент                  дата X                X                            X

      Итого по материалу                                                    XX                     XXX

Материал количество

       Изделие1  Клиент                  дата X                X                           X

       Изделие2  Клиент                  дата X                X                            X

      Итого по материалу                                                    XX                     XXX

       Итого по отчету                                                   XXX              XXXX

 

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

 

1. Как создать оформление для отчета?

2. Как осуществляется группировка данных в отчете?

 

 

Практическая работа 8

 

Создание отчета список стандартов

Цель работы: Научиться создавать отчеты, сортировать данные в отчете, группировать данные.

 

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

(Предварительно создать запрос из таблицы стандарты)

 

Список стандартов  

Дата актуальности  

                                                     

Материал

Параметры

Цена

Количество

Сумма

Наименование изделия

       материал1          Х        X                X                  X

       материал2        Х             X               X                  X

      Итого по изделию                     Х                XX       XXX

Наименование изделия

       материал1          Х        X                X                  X

       материал2             Х             X               X                  X

      Итого по изделию                     Х                XX       XXX

       Итого по отчету                                                   XXX              XXXX

 

 

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

 

1. В какой области собираются итоги?

2. Как осуществляется сортировка данных в отчете? 

 

 

Практическая работа 9

 

Построение графиков и диаграмм

Цель работы: Приобрести практический навык по  созданию графиков и диаграмм.

 

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

 

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

 

Задание3. Создать диаграмму, которая будет отображать в процентном отношении - какой материал пользуется большим спросом

 

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

 

1. Какие типы сводных диаграмм, поддерживаются в Access?

2. Как создать сводную таблицу?

Практическая работа 10

Создание кнопочной формы

Цель работы: Приобретение практического навыка по созданию кнопочной формы.

 

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

 

 

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

 

Задача должна быть снабжена главной кнопочной формой и подчиненными кнопочными формами

    Главная кнопочная форма содержит кнопки

1. Справочная информация

2. Формы ввода

3. Формы просмотра информации

4. Печать и просмотр отчетов

5. Кнопка выхода из задачи

 

 

    Кнопка “Справочная информация” содержит подчиненную кнопочную форму, которая имеет кнопки

1. Стандарты

2. Клиенты

3. Расположение

4. Склад

5. Выход(возврат в главную кнопочную форму)

 

    Кнопка “Формы ввода” содержит подчиненную кнопочную форму, которая имеет кнопки

1. Заказы

2. Выход(возврат в главную кнопочную форму)

 

    Кнопка “Печать и просмотр отчетов” содержит подчиненную кнопочную форму, которая имеет кнопки

1. Список стандартов

2. Текущее состояние склада

3. Отгрузка готовой продукции в разрезе клиенту за период  времени

4. Выход(возврат в главную кнопочную форму)

 

    Требования к контрольному примеру

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

Каждый заказ содержит не менее трех видов изделий

Отгрузка изделий производиться в течении 5 дней

Нумерация заказов сквозная за весь период

Один и тот же клиент может обратится за покупкой изделий в течении периода не менее двух раз (посчитайте количество записей в контрольном примере)

 

 

Приложение 1

Схема данных

Стандарты