Smolinets_PR_3

НАЦІОНАЛЬНИЙ БАНК УКРАЇНИ
УНІВЕРСИТЕТ БАНКІВСЬКОЇ СПРАВИ
ЛЬВІВСЬКИЙ ІНСТИТУТ БАНКІВСЬКОЇ СПРАВИ

Кафедра економічної кібернетики




Практична робота № 3
на тему:
«Проектування запитів до бази даних».


З дисципліни :
«Технологія проектування та адміністрування БД і СД»











Виконав:
студент 301 ЕК групи
Смолинець Ю.А.

Перевірила:
Муравська О.І..












Львів-2011


Мета роботи: Вивчення засобів розробки запитів RQBE, видів запитів, їх виконання та застосування для роботи з реляційними базами даних.
Порядок виконання роботи
1. Побудувати засобами RQBE простий запит на вибір даних з об'єднаних таблиць бази даних.
2. Побудувати засобами RQBE простий запит на вибір даних за складним критерієм.
3. Побудувати засобами RQBE параметричний запит на вибір даних за заданим критерієм із значенням заданим у вигляді параметра.
4. Побудувати засобами RQBE запит на видалення записів за критерієм.
5. Побудувати засобами RQBE запит на модифікацію (оновлення) значень полів записів за заданим критерієм.
6. Побудувати перехресний запит для відображення залежності значень поля від значень іншого.
7. Зберегти створені запити для подальшого використання.
8. Вивчити режим редагування запитів.
9. Внести виправлення в один із запитів для вибору даних із зв'язаних таблиць.
Контрольні запитання
1. Поняття запиту та його застосування для роботи з базами даних.
2. Технологія створення засобів за допомогою RQBE.
3. Види запитів та їх призначення.
4. Призначення та зміст перехресного запиту.
5. Застосування параметрів в запитах.
Теоретичні відомості та методичні рекомендації до виконання лабораторної роботи
У таблицях Access не можна використовувати поля, які отримуються розрахунковим шляхом. Крім того в Access таблиці автоматично упорядковуються первинним ключем. Не існує можливості вибрати інший ключ сортування, наприклад, вторинний. Усі ці проблеми можна вирішити за допомогою запитів. Запити забезпечують швидкий та ефективний доступ до даних, що зберігаються у таблицях. За допомогою запитів можна виконати необхідне сортування, обчислити певний вираз або спільно обробити відразу кілька зв’язаних таблиць. В одному запиті можна використовувати одразу 32 таблиці. При виконанні запиту підсумкові дані зберігаються у вигляді тимчасових таблиць, які не зберігаються і тому носять назву динамічних наборів даних.
Динамічний набір даних – це тимчасова таблиця яка створюється запитом. В цій таблиці розміщуються записи, які задовольняють вимогам певного запиту.
Призначення запитів:
 вибір записів, що відповідають певним критеріям відбору без попереднього відкриття конкретної таблиці або форми;
 вибір таблиць, що містять потрібні записи, з можливістю подальшого додавання інших таблиць;
 відбір полів, що виводяться на екран при відображенні результуючого набору записів;
 створення нової таблиці на основі даних, отриманих з існуючих таблиць;
 оновлення, додавання і видалення записів таблицях;
 виконання обчислень над значеннями полів.
У Access розрізняють наступних типів запитів:
 запит на вибірку даних (Select query) дозволяє витягувати дані з однієї або декількох таблиць згідно із заданим критерієм і відображувати їх в новій таблиці;
 перехресний запит (Crosstab query) підсумовує в електронній таблиці дані з однієї або декількох таблиць. Вони використовуються для аналізу даних, створення діаграм;
 запит з параметрами (Parameter query) дозволяє витягувати дані з однієї або декількох таблиць згодне одному або декільком параметрам. Наприклад, для поля, в якому відображуються дати, можна ввести запрошення наступного вигляду «Введіть початкову дату:» і «Введіть кінцеву дату», аби задати кордони діапазону значень;
 запит на зміну (Action query) створюють нові таблиці із запитів. Вони дозволяють включити нові записи або видалити старі, внести до них зміни за допомогою виразів, вбудованих в запит;
 запит SQL (SQL query) заснований на інструкціях SQL (Structured Query Language мова структурованих запитів). Мова SQL є стандартом для більшості СУБД. У форматі SQL у базі даних зберігаються всі запити.
Реляційна мова програмування QBE – одна з двох основних мов програмування – була розроблена у 70-их роках і має такі три основні характеристики:
Розрахована на непрофесійних користувачів;
Є графічною мовою програмування;
Має спрощений процес розробки запитів.
Для створення запитів використовуються бланки таблиць, в які вписуються характеристики запиту. Інтерпретуюча система мови знаходить усі можливі відповіді на поставлені запитання і видає їх користувачу.
Існує базовий варіант мови і певні її реалізації у кожній конкретній СКБД (MS Access, SQL- сервер тощо).
Загальна методика розробки та реалізації запитів мовою QBE включає наступні етапи:
Сформувати текст запиту;
Аналіз тексту запиту з метою виділення таблиць РБД (звідки вибирається інформація), структури результуючої таблиці, умови вибірки даних і таке інше;
Розробка запиту на мові QBE;
Виконання запиту СКБД;
Аналіз результатів виконання запиту;
Якщо потрібно, коригування тексту запиту;
Збереження тексту запиту.

Характеристика контструктора запитів MS Access: призначення, функції, структура панелі.

Для початку роботи із конструктором запитів необхідно у вікні бази даних виберіть значок Queries (Запити) у списку Objects (Об'єкти) і натисніть кнопку New (Створити новий) на панелі інструментів вікна бази даних. В діалоговому вікні New Query (Новий запит) виберіть пункт Design View (Режим конструктора).

Таблиця



Зв’язок між таблицями

Область виділення поля

Назви комірок бланку запиту



Рис. 1. Структура запиту у режимі конструктора.

Вікно конструктора запиту розділене на дві частини. У верхній частині вказуються усі джерела запиту (таблиці чи інші запити) та зв’язки між ними. Нижня частина – це бланк запиту, в якому перераховується список полів запиту та особливості їх відображення на екрані.
В бланку запиту заповнюються наступні комірки:
Назва комірки
Зміст

Field
Поле (для перейменованих полів чи обчислювальних виразів також вказується нове ім’я)

Table
Таблиця чи запит

Total
Підсумки (для звичайних запитів на вибірку підсумки недоступні)

Sort
Порядок відображення поля

Show
Чи показувати поле в результаті виконання запиту

Criteria, or,
Умови вибору (для кожного поля можна вказати кілька умов, з’єднаних через логічний оператор Or – Або).


Щоб додати в запит таблицю чи інший запит необхідно виконати наступні кроки:
Відкрити запит у режимі конструктора або створити новий.
Натисніть кнопку Show Table (Показати таблицю) на панелі інструмент. На екрані з’явиться відповідне діалогове вікно (при створенні нового запиту воно з’являється автоматично).
У діалоговому вікні Показати таблицю вибрати вкладку, що містить потрібні об'єкти (список таблиць, запитів чи обидва списки).
Вибрати ім'я об'єкта, що додається в запит. Для одночасного вибору декількох об'єктів потрібно вбирати об'єкти при натиснутій клавіші CTRL.
Натиснути кнопку Add (Додати), а потім кнопку Close (Закрити).
рис. 2 Додавання табці у запит.

Можна також додати в запит таблицю чи інший запит шляхом перетягування назви таблиці чи запиту з вікна бази даних у верхню половину вікна запиту в режимі конструктора.
При додаванні полів у бланк запиту варто додавати тільки ті поля, дані з яких виводяться на перегляд, чи використовуються для задавання умов вибору, групування і впорядкування. Для додавання полів необхідно:
Відкрийти запит у режимі конструктора.
При роботі з запитом перевіріти, що список полів необхідної таблиці чи запиту міститься у верхній частині вікна. Якщо необхідного списку полів нема, додати таблицю чи запит.
Два рази клацнути на потрібному полі або вибрати зі списку полів одне чи кілька полів і перетягнути їх у стовпці бланку.
Для введення умови вибору в запиті необхідно:
Відкрити запит у режимі конструктора.
Вибрати першу комірку Criteria (Умова вибору) для поля, для якого необхідно встановити умову вибору.
Ввести вирази для умови вибору.
Для введення другої умови для поля перейти у відповідну комірку Or (Або).

Навчальний приклад.
Для навчального прикладу створена база даних, яка складається із таких таблиць:
Products




Companies



Orders


Customers


1. Створення запиту-вибірки
1.1. Текст запиту:
Вибрати усі записи про товари, що виготовляє фірма Світоч. Вивести назву товару, код товару та ціну.
2.1. Аналіз тексту запиту:
Необхідні записи знаходяться у таблицях Products та Companies. Структура таблиці запиту включає такі поля: Product_Id, Product_Name, Price. Умова вибірки – значення поля Company_Name=”Світоч”.

Вибираємо неохідні нам поля, якщо поле не потрібно виводити у результуючій таблиці, то знімаємо галочку у в рядку Вивод на екран.
В рядку Условие отбора у стовпчику, який містить назви виробників вказуємо, що вибираємо лише записи де виробником є Світоч. Панель конструктора запиту матиме вигляд:


Результат вибірки:


При закритті Access запропонує вказати назву запиту.
Додаткова інформація!!!
В якості умов на вибірку можна використовувати операції порівняня =, >, <, >=, <=, <>. Крім того допускається використання предикатів BETWEEN, IN, LIKE та NULL.

Предикат
Формат запису
Зміст

Between
вираз [Not] Between значення1 And значення2
Перевірка виразу на входження в діапазон значень

In
вираз [Not] In(значення1, значення2, )
Перевірка виразу на входження в список значень

Like
вираз [Not] Like “маска”
Перевірка виразу на подібність до маски

Null
вираз Is [Not] Null
Перевірка виразу на наявність даних

В наступній таблиці наведено приклади умов на вибірку.
Умова
Запис

suma відмінна від 0
suma<>0

evdate не перевищує 30 червня 2000р.
evdate<=#20.06.2000#

evdate приймає значення від 1 січня 2000р. до 30 червня 2000р.
evdate Between #01.01.2000# And #20.
·06.2000#

suma не входить в діапазон від –10 до 10
suma Not Between –10 And 10

optype приймає значення 0 або 1
optype In(0,1)

name починається з “А”
name Like “А*”

name містить “и”
name Like “*и*”

name не містить “и”
name Not Like “*и*”

name закінчується на “к”
name Like “*к”

name містить другу букву “а” та закінчується на “к”
name Like “?а*к”

name містить не менше п’яти символів
name Like “?????*”

fathername містить дані
fathername Is Not Null

fathername не містить дані
fathername Is Null


2. Запити з обчисленнями
У таблицях недоцільно утримувати дані, які можна отримати розрахунковим шляхом. Щоб здійснювати обчислення над даними, що зберігаються у таблицях використовуються запити із обчисленнями.
2.1 Текст запиту
Вибрати усі записи про замовлені товари, назву товару, назву замовника та назву виробника та порахувати витрати замовників.
2.2. Аналіз тексту запиту
Інформація вибирається із таблиць Products, Customers, Orders, Companies. Структура таблиці запиту включає такі поля: Products_Name, Company_Name, Customer_Name, Costs. Де Costs – поле яке є результатом обчислення.
Для обчислення витрат замовника запит відкривають у режимі конструктора і додають до його полів нове поле, що є розрахунковим. Це поле описують рнаступним чином:
Costs: [Price]*[Count]
Панель конструктора матиме вигляд:

Результат вибірки:


Додаткова інформація!!!
Щодо виконання обчислень, то існує ряд обчислень, які можна виконати в запиті, наприклад, знайти суму чи середнє за значеннями одного поля, перемножити значення двох полів чи обчислити дату, що відстає від поточної на три місяці.
Результати обчислень, що виводяться в поле, не запам'ятовуються в базовій таблиці. Обчислення виконуються кожного разу при виконанні запиту, тому результати завжди відображають поточний вміст бази даних. Обновити обчислені результати вручну неможливо.
Для визначення обчислювальних полів можна використовувати вбудовані функції Microsoft Access чи вираження, створювані користувачем. Вбудовані функції дозволяють знайти наступні підсумкові значення для груп записів чи для всіх записів: суму значень поля (Sum), середнє (Avg), число значень у полі (Count), мінімальне значення (Min), максимальне значення (Max), середньоквадратичне відхилення (StdDev) чи дисперсію (Var). Для кожного обчислювального поля варто вибрати один вираз.
Вираження, що визначають обчислення вводяться у рядок Total (Підсумки) бланку запиту.
У бланку запиту задаються умови вибору груп, для яких обчислюються підсумкові значення, і записів, що включаються в обчислення, а також результати, відображувані після виконання розрахунків.
Вирази, задані користувачем, дають можливість виконувати дії з числами, датами і текстовими значеннями в кожному записі з використанням даних з одного чи декількох полів. Наприклад, вирази дозволяють помножити значення одного поля на підсумкове значення, знайти різницю значень двох полів типу Дата/час, з'єднувати стрічкові значення.
Для розрахунків з використанням формул, створених користувачем, потрібно створити нове поле, що обчислюється, прямо в бланку запиту.
Поле, що обчислюється, створюється за допомогою виразу, що вводиться в порожній комірці Field (Поле) в бланку запиту.
При виконанні розрахунків допускаються складні вирази – наприклад, Sum(optype*suma).
Результати обчислень не повинні обов'язково відображатися в полі. Їх можна використовувати в умовах на вибірку записів. Наприклад, в комірці рядка Criteria (Умова вибору) можна вказати умову на вибір записів, що мають значення в полі evdate в інтервалі між поточною датою і датою, що відстає від неї на 90 днів: Between Now()-90 And Now().
Рядок Total (Підсумки) в режимі конструктора запиту з’являється при натиску кнопки Totals (Підсумки) на панелі інструментів.
У бланку запиту дев’ять із дванадцяти елементів випадаючого списку в комірці Total (Підсумки) є статистичними функціями. У наступній таблиці перераховані ці функції з їх можливим застосуванням до типів даних.

Елемент
Результат
Тип поля

Sum
Сума значень полючи.
Числовий, Дата/час, Грошовий, Лічильник

Avg
Середнє від значень поля.
Числовий, Дата/час, Грошовий, Лічильник

Min
Найменше значення поля.
Текстова, Числовий, Дата/час, Грошовий, Лічильник

Max
Найбільше значення поля.
Текстова, Числовий, Дата/час, Грошовий, Лічильник

Count
Число значень поля без обліку порожніх (Null) значень.
Текстова, Числовий, Дата/час, Грошовий, Лічильник, Логічний, Поле об'єкта OLE

StDev
Середньоквадратичне відхилення від середнього значення поля.
Числовий, Дата/час, Грошовий, Лічильник

Var
Дисперсія значень поля.
Числовий, Дата/час, Грошовий, Лічильник

First
Початкове значення
Дата/час

Last
Кінцеве значення
Дата/час


Запити з параметром
Для того щоб кожного разу не створювати для кожного критерію запити-вибірки використовують запити з параметрами. Цей запит пропонує користувачеві діалог для визначення умови пошуку та вибірки в таблиці.

3. 1 Текст запиту
Вивести назви замовників та назви замовлених товарів якщо обсяг замовлення є не меншим числа введеного користувачем.
3.2. Аналіз тексту запиту
Інформація вибирається із таблиць Products, Customers, Orders. Структура таблиці запиту включає такі поля: Products_Name, Customer_Name, Count.

В рядок Условие отбора у стовпці Count вводимо:
>=[Ввести обсяг замовлення]
Таким чином при активізації запиту користувач зможе ввести число з якого обсягу замовлення виводити записи.

Результат запиту при значенні папаметру «90»:


4. Запити на вилучення
За допомогою запитів на вилучення можна вилучати записи із таблиці. Для того щоб створити запит вилучення потрібно на панелі інструментів обрати кнопку Тип Запроса і із випадаючого меню обрати Удаление.

4.1. Текст запиту
Створити запит на вилучення записів із таблиці Products, якщо ціна товару 10 грн.
4.2.Аналіз тексту запиту
Інформація вибирається із таблиці Products. Структура таблиці запиту включає такі поля: Product_Id, Product_name, Companies_Id, Price. Спочатку доцільно здійснити запит вибірку, щоб перевірити чи дійсно видалятимуться потрібні записи. Умова видалення – значення поля Price=”10”. Структура конструктора запиту:

Перед виконанням запиту буде видано 2 діагностичних повідомлення, перше про те що виконання цього запиту призводить до змін у таблиці, друге інформує скільки записів буде вилучено в результаті виконання цього запиту.

5. Запити оновлення
Іноді в таблиці потрібно виконати кілька однакових замін, виконання яких вручну займе багато часу. В даному випадку також доцільно спочатку створити запит-вибірку, за допомогою якого можна переглянути ті записи, в яких проводитимуться заміни. Запит- вибірку необхідно перетворити на запит оновлення наступним чином:


5.1. Текст запиту
Ціни на товари зросли у 1,5 рази, потрібно обновити дані таблиці.

5.2. Аналіз тексту запиту
Оновлювитись буде таблиця Products. Структура нової таблиці залишається попередньою, потрібно оновити поле Price. Це потрібно зробити наступним чином: у рядку Обновление у стовпці Price потрібно зазначити:
[Price]*1,5
Структура конструктора матиме вигляд:

Результати запиту-оновлення:


6. Перехресні запити
Для виконання перехресного запиту потрібно виконати наступні дії:
Створити новий запит включивши до нього необхідні таблиці, в нашому випадку включаємо таблиці Products, Customers, Orders.
До бланку запитів вносимо поля Products_Name, Customer_Name, Count.
В меню Тип Запроса вибираємо Перекресный.

Після цього необхідно визначитись з полями які будуть заголовками рядків, які заголовками стовпців, а які – значеннями. У нашому випадку Customer_Name – заголовки рядків, Products_Name – заголовки стовпців, Count – значення. Відповідно у рядку конструктора Перекрестная таблица зазначаємо це.
У рядку Групповая операция у стовпці Count вибираємо операцію Sum. Структура конструктора матиме вигляд:

Результат запиту:


Для того щоб переглянути створені запити в режимі SQL потрібно обрати меню Вид->Режим SQL:

Групувальні запити
Для побудови групувального запиту на панелі інструментів необхідно натиснути кнопку Групповые операции.

Далі потрібно у рядку Групповая операция у стовпцях по яких здыйснюэмо групування обрати Группировка, а у інших стовпцях зазначити що робити із даними при групуванні. У нашому прикладі ми групуємо за назвою замовника сумарний обсяг замовлення у шт. Структура конструктора матиме вигляд:

Результат запиту:


Хід виконання роботи
Будую засобами RQBE простий запит на вибір даних з об'єднаних таблиць бази даних.

Будую засобами RQBE простий запит на вибір даних за складним критерієм.

Будую засобами RQBE параметричний запит на вибір даних за заданим критерієм із значенням заданим у вигляді параметра.


Будую засобами RQBE запит на видалення записів за критерієм.

Будую засобами RQBE запит на модифікацію (оновлення) значень полів записів за заданим критерієм.

Будую перехресний запит для відображення залежності значень поля від значень іншого.

Зберігаю створені запити для подальшого використання.

Вивчив режим редагування запитів.

Вношу виправлення в один із запитів для вибору даних із зв'язаних таблиць.

Висновки
На даній практичній роботі я вивчив засоби розробки запитів RQBE, видів запитів, їх виконання та застосування для роботи з реляційними базами даних








13PAGE 15


13PAGE 14215




Заголовок 115

Приложенные файлы

  • doc 17999336
    Размер файла: 3 MB Загрузок: 0

Добавить комментарий