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

Створення таблиць

Створення бази даних.

Запустіть програму Microsoft Access: ПУСК – Програми - Microsoft Access

У вікні Microsoft Access виберіть Нова база даних і клацніть на кнопці OK. У вікні Файл нової бази виберіть власну папку, в якій буде міститися БД, а в нижній частині вікна дайте ім'я файлу Бібліотека. mdb (розширення mdb система додасть автоматично). Клацніть на кнопці Створити. Після цього відкриється вікно бази даних, де відображені всі її компоненти.

Створимо три таблиці, які містять інформацію про роботу бібліотеки:

· Книги – містить інформацію про книги, які зберігаються в бібліотеці;

· Читкниги – містить інформацію про книги, які отримані читачами;

· Читачі – містить інформацію про читачів бібліотеки.

Для створення таблиці клацніть на кнопці Створення таблиці в режимі конструктора. У вікні конструктора таблиць введіть інформацію, представлену на мал. 1.

Одне з полів таблиці призначається ключовим. Значення в цьому полі однозначно визначає запис. Це поле повинне бути призначене Обов'язковим і необхідно вказати, що це поле є Індексованим (без повторень). Таким полем в таблиці Книги є поле Інв№.

Щоб призначити це поле ключовим, відзначте поле і клацніть на інструменті Ключ (він виділений на малюнку 1). Закрийте вікно Конструктора таблиць для збереження структури таблиці і дайте їй ім'я у діалоговому вікні, що з’явиться на екрані.

Тепер потрібно ввести дані в таблицю. Це можна зробити різними способами. Приведемо один з них - перегляд і введення даних в режимі таблиці: для цього у вікні бази даних необхідно виділити таблицю і натиснути на кнопку Відкрити. Ви побачите порожню таблицю. Введіть в неї декілька записів про книги вашої бібліотеки як вказано в таблиці 1.

Введіть не менше 10 записів в таблицю Книги.

Можна використати як власні назви книжок так і дані з таблиці 1.

Таблиця 1- Список книг з таблиці Книги.

Інв№	Шифр	Автор	Назва	Видавництво	Місто	Рік	Вартість	Анотація
	681.3	Пінчук В.П.	Об'єктно-орієнтоване програмування	ЗНТУ	Запоріжжя		10,00 грн.	Конспект лекцій.
	681.3	Белая Н.І.	Програмування на VBA.	ЗНТУ	Запоріжжя		10,00 грн.	Конспект лекцій.
	681.3	Гарнаєв А.	Використання Excel в економіці і фінансах.	БХВ	Санкт-Петербург		23,00 грн.
	681.3	Вейськас Дж.	Ефективна робота з Microsoft Access 2000	Пітер	Санкт-Петербург		35,00 грн.	Нова версія популярної системи управління базами даних.
	681.3	Керрі Н.Праг	Біблія користувача Microsoft Office 2000	Діалектика	Київ		43,00 грн.
		Ефімов Д.В.	Збірка завдань по вищій математиці	Наука	Москва		12,00 грн.	Для студентів технічних вузів
	681.3.06	БухваловА.В., Ідельсон А.В.	Фінансові обчислення для професіоналів.	БХВ	Санкт-Петербург		23,00 грн.
	681.3	Додж М., Стінсон Д.	Ефективна робота з Microsoft Excel 2000.	Пітер	Санкт-Петербург		28,00 грн.
	681.3	Гарнаєв А.	Самовчитель VBA.	БХВ	Санкт- Петербург		14,00 грн.
	657.1	Овчаренко Е.К.	Фінансово-економічні розрахунки в Excel	Філін	Москва		8,00 грн.
	681.3	Праг, Каррі Н. і ін.	Секрети Access 97	Діалектика	Київ		24,00 грн.	Особливості використання Access

Створіть нову таблицю Читкниги з такими полями (Мал. 2):

- Інв№ - інвентарний номер книги, виданої читачеві;

- NB – номер читацького квитка читача;

- Дата видачі - дата видачі книги читачеві;

- Дата повернення – дата, коли читач повинен повернути книгу в бібліотеку.

Виберіть відповідні змісту типи полів і їх розміри. Поле Інв№ повинне бути типу Текстовий і мати розмір 6 байтів як і в таблиці Книги.

Поле NB теж зробіть текстовим. Поля Інв№ і NB повинні бути індексованими і обов'язковими. У цій таблиці можна не призначати ключового поля.

Заповніть таблицю даними про видані книги (Таблиця 2). Стежите за тим, щоб дані у всіх трьох таблицях були узгоджені, тобто не «видавайте» книг, яких немає в бібліотеці.

Таблиця 2. Список книг, які видані, до таблиці Читкниги.

Інв№	NB	Дата видачі	Дата повернення	Примітка
		3.01.09	14.02.09
		20.01.08	13.02.09
		2.12.08	6.01.09
		21.12.08	23.01.09
		3.12.08	4.01.09
		10.12.08	11.01.09
		2.12.08	3.01.09

Створіть нову таблицю Читачі (Мал.3) з полями:

- NB – номер читацького квитка читача;

- Прізвище – прізвище читача;

- Кафедра – кафедра, на якій працює читач, або група, в якій вчиться читач;

- Телефон – робочий телефон читача.

Ключовим полем в таблиці буде поле NB тому, що воно однозначно визначає кожен запис, тобто дані в ньому ніколи не повторюються.

Заповніть і цю таблицю даними (Таблиця 3). Стежте за тим, щоб не видавати книги неіснуючим читачам, оскільки в цьому випадку ви не зможете встановити необхідні зв'язки між таблицями.

Таблиця 3. Список читачів до таблиці Читачі

NB	Прізвище	Кафедра	Телефон
	Корніч В.Г.	Фізика	2-92
	Нечай І.С.	Фізика	2-91
	Цокотун П.В.	ВМ	4-14
	Романіченко Г.В.	ВМ	4-14
	Белая Н.І.	ВМ	4-14

1.3 Створення зв'язків між таблицями

Проектування нашої бази даних можна вважати завершеним. Залишилося тільки встановити постійні зв'язки між таблицями для того, щоб можна було вибирати дані з декількох таблиць у відповідності із значеннями співпадаючих полів.

Для цього клацніть на кнопці Схема даних ідодайте у вікно схеми даних три створені таблиці. На екрані з'явилося схематичне зображення трьох таблиць.

Зв'язки між ними встановлюються за допомогою миші по методу «зачепити і перетягнути». «Зачепіть» поле Інв№ в таблиці Книги та перетягніть до такого ж поля в таблиці Читкниги.

У вікні діалогу поставте прапорець для цілісності даних (Малюнок 4).

На схемі з'явиться лінія, яка сполучає ці поля. Аналогічно встановіть зв'язок для інших таблиць по полю NB (Малюнок5).

1.4 Робота з даними в режимі таблиці.

1) Пошук і заміна даних у всіх записах. Для заміни даних в декількох записах однаковим значенням необхідно виділити першу клітку в стовпці, який підлягає редагуванню. Виберіть команду Правка / Заменить. У вікні діалогу наберіть значення, яке потрібно змінити, і вірне значення (на яке потрібно замінити). Натисніть кнопку Заменить або Заменить все.

2) Копіювання даних, копіювання окремих записів в резервну копію. Для копіювання даних, їх необхідно виділити. Далі вибрати команду Правка/Копировать. Вибрати місцеположення даних (наприклад, іншу таблицю) і команду Правка/Вставить.

Резервну копію роблять так: перейдіть у вікно бази даних, виберіть необхідну таблицю і виконайте команду Правка/Копировать. Далі виберіть команду Правка/Вставить і у вікні діалогу дайте копії таблиці нове ім'я.

3) Видалення рядків. Для видалення записів необхідно їх виділити і натиснути клавішу Delete. У вікні діалогу підтвердити своє рішення.

4) Сортування даних. Сортування даних виконується декількома засобами.

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

Другий спосіб: виконати сортування за допомогою Розширеного фільтру. Цей спосіб допомагає при сортуванні по двох і більше стовпцях. Відкрийте Фильтр/Расширенный фильтр. Виберіть необхідні поля в порядку сортування і для кожного вкажіть порядок сортування. Далі виберіть команду Фильтр/Применить фильтр.

Контрольні питання

1) Визначити основні поняття: база даних, таблиця, поле, запис.

2) Типи полів і обмеження на значення і використання.

3) Перерахуйте властивості полів і їх застосування.

4) Ключові поля: їх призначення і застосування. Створення ключових полів.

5) Поняття фільтру. Створення і застосування фільтрів.

6) Пошук і заміна даних в таблицях.

7) Що таке зв'язки між таблицями? Типи зв'язків.

Тема: Створення запитів.

Теоретичні відомості

Запит – це об'єкт бази даних, який дозволяє відбирати дані з однієї або декількох таблиць за умовами. Крім того, використовуючи запит можна створювати обчислювані поля для кожного запису або для групи записів. Це робиться в запитах “на вибірку”. Запити на оновлення даних дозволяють вносити зміни відразу в декілька записів, які задовольняють вказаним умовам. Запит – найважливіший об'єкт роботи з даними. Розглянемо послідовно всі типи запитів.

Групові операції для підсумкових запитів:

SUM- обчислення суми значень для групи;

AVG - середнє значення поля для даних з групи записів;

MIN - мінімальне значення для даних з групи записів;

MAX - максимальне значення для даних з групи записів;

COUNT- кількість записів, в яких є значення;

Запити, які відбирають дані з декількох таблиць.

При створенні таких запитів до верхньої частини вікна конструктора запитів вибираємо декілька таблиць. Якщо зв'язки між таблицями були створені у вікні “Схема даних”, то в цьому вікні вони з'являться автоматично. Тепер можна вибирати в один запит поля з декількох таблиць.

Створення полів, які обчислюються. У таблицях баз даних (на відміну від електронних таблиць) ніколи не зберігають дані, які можуть бути обчислені по даним з таблиць. В більшості випадків тому, що це вимагає значних витрат пам'яті і уповільнює пошук необхідних даних і обробку даних в таблицях. Всі необхідні обчислення виконують в запитах, створюючи поля, які обчислюються. Для створення таких полів у вільному стовпці конструктора запитів записують нове ім'я поля, знак “: ”, а потім вираз, який використовує імена інших полів і обчислює необхідне значення. Наприклад, обчислимо вартість замовлення, якщо є поля Вартість одиниці і Кількість одиниць. У новому стовпці запишемо:

Вартість замовлення: [ Вартість одиниці ]*[ Кількість одиниць ].

Імена полів записуються в квадратних дужках.

При записі виразів можна використовувати вбудовані функції Access. Імена цих функцій, на відміну від Excel, не перекладені на російську чи українську мову, вони пишуться по-англійськи, але по смислу багато хто з них співпадає з функціями Excel. Приведемо декілька функцій:

Iif (умова; вираз1; вираз2) – обчислює вираз1, якщо умова виконується, і вираз2, якщо умова не виконується (співпадає з функцією ЕСЛИ в Excel).

DateDiff(“d”; дата1; дата2) - обчислює різницю між двома датами в днях. Якщо перший аргумент “m” – різниця дат обчислюється в місяцях, якщо “y” - в роках.

Date() – визначення поточної дати

Повний список всіх функцій ви знайдете у вікні «Построитель выражений».

Вказівки до виконання

2.1 Створення запитів з однієї таблиці.

По-перше, потрібно навчитися записувати умови відбору даних в запитах. Умови записуємо в рядку “Умова відбору:”. Для числових полів умови мають вигляд, наприклад, < 100, або > 40 And <60. Тобто, записуємо знак відношення і числове значення. Можна також використовувати логічні функції And, Or, Not. Функція

Beetween нижняя_граница And верхняя_граница

використовується для завдання умови попадання в інтервал. Наприклад, Beetwen 40 And 60 попадання значення поля від 40 до 60.

Умови для текстових полів записуються з використанням функції

Like “ Рядок пошуку з символами шаблону”

Символ шаблону * замінює довільну кількість символів в даній позиції, символ? замінює один який-небудь символ в позиції, символ # указує, що в даній позиції повинна стояти цифра. Наприклад, умова Like “CA-#######” дозволить відібрати записи, в яких записані номери паспортів серії СА.

2.1.1 Запит, що відбирає дані з однієї таблиці по умові

Розглянемо приклад створення запиту, який відбирає з таблиці Книги ті екземпляри, ціна яких більше 20 гривень і прізвище автора починається або з букви “Б”, або з букви “В”.

Перейдіть на вкладку Запити. Натисніть кнопку Створити. Виберіть в меню Конструктор. У вікні діалогу Додавання таблиці на вкладці Таблиці виберіть таблицю, з якої потрібно відібрати дані. Для нашого випадку це - Книги. Натисніть кнопку Додати. Закрийте вікно Додавання таблиці. Тепер потрібно вибрати ті поля, які необхідно включити в запит. Виберемо поля: Автор, Назва, Рік, Вартість. Для того, щоб помістити ці поля в бланк запиту, потрібно двічі натиснути кнопкою миші на імені поля в таблиці. Є і інші можливості: перетягнути назву поля з таблиці в бланк запиту або вибрати необхідні поля в списку назв полів в бланку запиту.

Далі необхідно поставити умови, по яких вестиметься відбір. Для цього в рядку Умову відбору для Вартості ставимо умова ”>20”.

Для поля Автор ставимо умову “Б*” Or “В*”, де * означає всі символи після першої Б. Функция Like буде додана автоматично. Для проглядання результату двічі натисніть на назві запиту або виберіть в пункті меню ВИД -режим таблиці.

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

Такі запити створюють групи записів, для яких співпадають значення вказаних полів (для таких полів вибираємо Групповая операция – Группировка), і обчислюють підсумкові значення для інших полів (для таких полів вибираємо Групповая операция – Sum, Avg, Count.)

Створимо такий підсумковий запит: порахуємо кількість читачів кожної кафедри. Вибираємо таблицю Читачі, включаємо поля Кафедра і Прізвище. Для створення підсумкового запиту на панелі інструментів вибираємо піктограму Групповая операция (із значком S). У бланку запиту з'являється рядок Групповая операция.

За значенням першого поля Кафедра створюємо групу, а для другого вибираємо COUNT (підрахунок кількості записів в групі). Для того, щоб при прогляданні запиту кожне поле мало зрозумілий підпис, визначимо властивості поля. Для цього вибираємо ВИД - Свойства і ставимо Подпись – Кількість читачів.

2.1.3 Перехресний запит -особливий тип підсумкового запиту, який дозволяє вивести обчислені значення в перехресній таблиці, яка нагадує звідну таблицю в Excel.

Наприклад, підрахуємо сумарну вартість книжок для кожного року видання по видавництвах. Для цього створимо перехресний запит майстром запитів Запрос-создать-перекрестный запрос. У вікні діалогу вибираємо послідовно таблицю, назви рядків, назви стовпців, функцію обробки даних.

Для нашого випадку це таблиця Книги; назви рядків - Рік; назви стовпців - Видавництво; функція - Sum(Вартість). Встановимо підсумкове значення - Итоговое значение по рядкам.

2.2 Запити, які відбирають дані з декількох таблиць

2.2.1 Створимо запит, який відбирає і об'єднує записи з двох таблиць, - Книги і Читкниги, щоб показати, які книги були видані читачам. Створимо обчислюване поле з ім'ям Пеня, яка нараховується на кожну книгу, яка не повертається своєчасно. Розмір пені рівний 1% від вартості книги за кожен прострочений день. Створимо запит з ім'ям Список1. Для створення запиту Список1 перейдіть на вкладку Запити і виберіть кнопку Створити.

Додайте в запит таблиці Книги і Читкниги.

У вікні схеми даних повинен бути показаний зв'язок між таблицями по полю Інв№ - інвентарний номер книги. Перетягніть мишею в нижню половину вікна поля, які необхідно включити в запит: Автор, Назва, Вартість, Інв№, Дата видачі, Дата повернення, NB. Останнє поле буде потрібно для зв'язку запиту з таблицею Читачі.

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

Пеня: iif([Дата повернення] < Date(); DateDiff ("d"; [Дата повернення]; Date())*0,01*[Вартість]; 0)

!!! УВАГА

Для створення такого виразу можна використовувати Построитель выражений. Для цього натисніть кнопку Построить на панелі інструментів. На екрані відкривається вікно Построителя выражений. У цьому вікні створюється вираз послідовним додаванням до нього полів таблиць або запитів, функцій, констант і операторів (малюнок 11).

Імена полів записуються в квадратних дужках.

Збережіть запит під ім'ям Список1. Продивіться запит, клацнувши на кнопці Открыть. Розмір пені, зрозуміло, залежить від поточної дати, на яку він обчислювався.

2.2.2 Створимо підсумковий багатотабличний запит.

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

Для цього виберіть таблиці Книги, Читачі і запит Список1. Убланк запиту додайте поля Прізвище з таблиці Читачі, Інв№ з таблиці Книги, Вартість з таблиці Книги і Пеня із запиту Список1.

Для створення підсумкового запиту на панелі інструментів вибираємо піктограму Групові операції - кнопка . У бланку запиту з'являється рядок Групповая операція (див. рисунок). Для першого поля запиту Прізвище з таблиці Читачі вибираємо Группировка, для Вартість з таблиці Книги – SUM (підрахунок суми), для поля Інв№ з таблиці Книги - Count і для Пеня із запиту Список1 - SUM. Для того, щоб при перегляді запиту кожне поле мало зрозумілий підпис, визначимо властивості полів. Для цього вибираємо Вид - Свойства і задаємо Подпись для кожного поля – Кількість книг, Загальна вартість книг і Загальна сума пені. Назвемо цей запит Загальна пеня.

2.2.3 Створення запиту на пошук записів, які не мають зв'язаних в іншому списку. Такі запити називаються «Записи без підлеглих». Для створення таких запитів необхідно змінювати тип об'єднання таблиць на зовнішній. При такому типі об'єднання в запит обов'язково включаються всі записи з першої таблиці і до них додаються поля з другої таблиці, якщо в другій таблиці є запис, для якого значення зв'язаних полів співпадають. Якщо в другій таблиці такого запису немає, то до запису з першої таблиці додаються поля з порожніми значеннями. Зовнішнє об'єднання в конструкторі таблиць зображається лінією із стрілкою.

Наприклад, створимо список книг, які не видані на руки читачам. У запит включимо дві таблиці Книги і Читкниги. Включимо в запит поля Інв№, Шифр, Автор, Назва з таблиці Книги і Інв№ з таблиці Читкниги. Знайдемо ті записи, які присутні в таблиці Книги і відсутні в таблиці Читкниги. Змінимо Параметри об'єднання. Необхідно створити Зовнішнє об'єднання. Для цього двічі натисніть на лінії зв'язки між таблицями. У вікні діалогу виберіть другий тип – “ Объединение всех записей с ‘Книги' і только тех записей с 'Читкниги', в которых связанные поля совпадают ”. Тепер створено зовнішнє об'єднання, в яке включені всі книги з таблиці Книги. Для тих книг, які не мають записів з таблиці Читкниги встановлюється значення ‘Null’, тому для поля Інв№ з таблиці Читкниги встановимо такі параметри Условие отбора – Is Null і Выведение на екран відсутній (зняти "галочку").

2.3 Модифікація даних за допомогою запитів

2.3.1 Запити, які змінюють значення групи записів

Такі запити називаються запити на оновлення даних. Наприклад, за допомогою такого запиту можна здійснювати зміну вартості книг по даті їх видання або за іншими умовами. Наприклад, збільшити вартість на 20% для книг 1998 року видання.

Цей запит змінює таблицю, тому для повного захисту від можливих помилок зробіть резервну копію таблиці. Для цього перейдіть у вікно бази даних, виберіть необхідну таблицю і виконайте команду Правка - Копировать. Далі виберіть команду Правка - Вставить і у вікні діалогу дайте копії таблиці нове ім'я, наприклад – " Книги_нова ціна ".

Перш, ніж відновити дані, створимо запит на вибірку. Перевіримо, чи вибираються необхідні записи, а після перебудуємо його в запит на оновлення. У запит включимо поля Рік і Вартість з таблиці Книги. Надамо можливість вводити рік видання як параметр. Для цього в квадратних дужках введемо [Введіть рік] в рядку Условие отбора... Під час виконання запиту цей текст з'явиться в діалоговому вікні для введення року видання. Перевіримо, чи вибираються необхідні записи. Далі перетворимо цей запит в запит на оновлення. Для цього в основному меню вибираємо Запрос - Обновление. У рядку Обновление вводимовираз: [Вартість]*([націнка]+100)/100 для перерахунку вартості книг.

Цей вираз використовує ще один параметр [націнка], що дає можливість вводити розмір націнки у відсотках. На екрані з'являється вікно діалогу, яке повідомляє про кількість оновлених записів. Після підтвердження таблиця оновлюється, і доступу до початкових даних вже немає.

2.3.2. Запит на створення нових таблиць. Такі запити створюють нову таблицю і записують в неї дані з інших наявних таблиць або запитів.

Наприклад, створимо таблицю, яка містить інформацію про читачів, які не повернули книги своєчасно.

Перед тим, як зробити запит на зміну даних, створимо запит на вибірку, щоб переконатися, що робота йде з необхідними даними. У запит включимо таблицю Читачі і запит, який для кожного читача підраховує суму пені (Загальна_пеня). Виберіть поля NB, Прізвище, Кафедра, Телефон з таблиці Читачі, Count-Инв№, Sum-вартість, Sum-пеня із запиту Загальна пеня. З'єднайте таблицю і запит по полю Прізвище. Для поля Sum-пеня задайте умову “>0” Ця умова вибиратиме тих читачів, у яких є пеня (див. рис.)

Переконаєтеся, що запит вибирає необхідну інформацію. Для перебудови запиту на створення таблиці перейдіть до команди Запрос - Создание таблицы. У вікні діалогу наберіть ім'я нової таблиці (наприклад, “Боржники на 25 січня”). Після запуску запиту на екрані з'явиться вікно діалогу з повідомленням про кількість записів, які будуть вставлені в таблицю. Для підтвердження створення таблиці натисніть кнопку Да. На вкладці Таблицы вікна бази даних перевірте наявність таблиці з цим ім'ям і прогляньте її вміст.

2.3.3. Створення запитів на видалення даних. Такі запити дозволяють видаляти з таблиць групи записів, які задовольняють яким-небудь умовам.

Наприклад, створимо запит, який видаляє запис з таблиці Читкниги на момент повернення читачем книги. Для повного захисту від помилок зробіть резервну копію таблиці. Для цього перейдіть у вікно бази даних, виберіть необхідну таблицю і виконайте команду Правка - Копировать. Далі виберіть команду Правка - вставить і у вікні діалогу дайте копії таблиці нове ім'я.

Перед тим, як зробити запит на зміну даних, створимо запит на вибірку, щоб переконатися, що робота йде з необхідними даними. У запит включимо таблицю Читкниги: поля Інв№ і NB. Поставимо умови на значення цих полів через параметри [Введіть Інв№ книги] [Введіть номер читацького квитка]. При виконанні цього запиту з'являться послідовно два вікна діалогу для введення необхідної інформації. Переконаєтеся, що запит вибирає необхідну інформацію.

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

Контрольні питання

1. З якою метою створюються запити?

2. Правила запису умов відбору в запитах. Що таке параметричний запит?

3. Як створюється обчислюване поле в запиті?

4. Як можна змінити властивості полів, які включені в запит?

5. Що таке запит з групуванням даних? Приведіть приклад. Назвіть підсумкові функції, що вам відомі. Як записують умови відбору в такому запиті?

6. Які типи запитів на зміну даних ви знаєте? Що таке запит на оновлення?

Дата добавления: 2017-01-14