Запити Access в Борей

Схема бази даних Борей

Що таке схема

Схема бази даних

Тут ми обмежимося коротким викладом концепції схем баз даних, і потім відразу перейдемо до бази даних Борей.

В Оксфордському словнику англійської мови слово схема визначається як «синопсис, начерк, діаграма, пропонований пристрій». Ми будемо виходити із цього визначення при вивченні схем баз даних.

Схема бази даних – це, власне кажучи, синопсис або начерк її структури. Іноді схема буває представлена у формі блок-схеми. Схема бази даних подібна до кістяка, оскільки ніяк не пов’язана з тим, яка саме інформація може бути записана в самій базі даних. Що ж, у такому випадку, вона собою представляє? Схема бази даних містить, головним чином, дані про наступні об’єкти:

P таблиці;

P запити;

P збережені процедури.

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

Тут самий час уточнити поняття запитів, подань і збережених процедур.

P Термін «запит» стосується звичайного SQL-оператора, застосовуваному для звертання до бази даних.

P Подання (в Access іменовані запитами), можна визначити як віртуальні таблиці, вміст яких визначається запитами.

P Збережена процедура – об’єкт, що складається з деякого числа SQL-операторів і застосовуваний для керування доступом і маніпулювання даними (в Access не підтримується).

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

Що стосується таблиць (без яких база даних позбавлена всякого змісту), то інформація, що зберігається про ці об‘єкти, досить різноманітна й включає:

P імена полів або стовпців;

P тип даних кожного поля або стовпця – наприклад, чисельний, текстовий, дата;

P обмеження:

P відношення й інформація, необхідна для збереження цілісності даних посилань;

P первинні ключі;

P значення за замовчуванням для кожного поля або стовпця, дані про те, чи дозволені значення NULL і порожні рядки й т.д.;

P індекси.

Все це разом становить опис основної структури бази даних.

2.2 У чому важливість схеми

Схема важлива з кількох причин. По-перше, вона дає якісь уявлення про те, як працює база даних. Уявимо собі, на самому примітивному рівні, базу даних з таблицею, у якій записані дані про товари, продані продуктовим магазином. Тепер припустимо, що нам знадобилося довідатися, скільки коштує коробка кукурудзяних пластівців. Нам доведеться дуже постаратися, щоб досягти успіху в цьому, якщо не знати, що ім’я цієї таблиці – Товари і що в ній є поля (стовпці) з іменами, приміром, [Найменування товару] і [Ціна одиниці товару]. Якщо трохи ускладнити завдання, нам буде потрібно знати запаси кукурудзяних пластівців на складі. І тут знову не обійтися без знання того, що існує таблиця з ім’ям [Рух товарів], у якій є поле [Код товару], зв’язане відношенням з первинним ключем таблиці „Товари”. Лише озброєні цією інформацією, ми зможемо здійснювати якісь осмислені дії.

Отже, ми освіжили в пам’яті зміст і призначення схеми бази даних, і тепер можемо впритул зайнятися схемою бази даних Борей.

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

3.1 Таблиці й відношення бази даних Борей

Беручи до уваги важливу роль, яку відводить у базі даних таблицям, ми почнемо з вивчення конструкції кожної з них. Це, однак, потребує чималих зусиль; до того ж, важко буде відслідковувати численні відношення. На щастя, Access має засіб подання структур таблиць і встановлених між ними відношень у вигляді блок-схем. Тому розглянемо цю блок-схему.

3.2 Вікно відношень в Access

Щоб вивести на екран блок-схему бази даних, клацніть на кнопці Схема данных або виконаєте команду Схема данных меню Сервис. Відкриється вікно Схема данных бази даних Борей, показане на наступному рисунку. Зауважимо, що Access не генерує ці блок-схеми автоматично. Кожна таблиця розміщується у це вікно користувачем. На щастя, в Борей блок-схема вже побудована, і це дає нам можливість заощадити сили:

Вікно Схема данных бази даних Борей особливо корисне тим, що в ньому представлені не тільки всі таблиці бази даних, але також всі поля кожної таблиці. Більше того, лініями, що з’єднують таблиці, позначені відношення між ними. Розглянемо деякі особливості цієї блок-схеми:

P На блок-схемі показані вісім таблиць. Таблиця Сотрудники складається з декількох полів – КодСотрудника, Фамилия, Имя, і т.д.

P Поля первинних ключів виведені жирним шрифтом. Наприклад, первинним ключем таблиці Сотрудники є поле КодСотрудника.

P Таблиця Сотрудники зв’язана відношенням «один-багато» з таблицею Заказы. Відношення позначене лінією, що з’єднує ці таблиці. Таблиця Сотрудники розташовується з боку «один» відношення, як зазначено цифрою 1. Що стосується таблиці Заказы, то вона позначена знаком нескінченності (∞), що позначає сторону «багато» відношення.

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

Очевидно, що дана блок-схема відображає головні властивості схеми бази даних Борей, а тому особливо корисна для її документування. Більше того, Access забезпечує можливість докладного дослідження об’єктів блок-схеми. Наприклад, якщо клацнути правою кнопкою в будь-якому місці таблиці Сотрудники у блок-схемі й виконати команду Конструктор таблиц контекстного меню, відкриється вікно конструктора для таблиці Сотрудники, у якому буде показана схема таблиці (імена полів, типи даних і т.п.). Ми не будемо тут докладно розглядати схеми конкретних таблиць, задовольнившись тим, що знаємо, де при необхідності можна знайти потрібну інформацію:

Аналогічним чином, якщо двічі клацнути на лінії, що представляє відношення між двома таблицями, відкриється діалогове вікно з параметрами цього відношення. Інакше це вікно можна відкрити, клацнувши на лінії відношення правою кнопкою й виконавши команду Изменить свіязь... контекстного меню. У діалоговому вікні, зображеному на наступному рисунку, показані параметри відношення «один-багато» між таблицями Сотрудники й Заказы:

У цьому вікні показано відношення між двома таблицями й поля, за якими воно встановлено. Крім того, тут можна встановити кілька додаткових параметрів. У цьому випадку ми бачимо встановлений прапорець Обеспечение целостности данных. Це означає, що в таблицю Заказы не можна вставити новий запис, поки не введено потрібне значення в поле КодСотрудника. Toбто, поле зовнішнього ключа КодСотрудника у таблиці Заказы повинне відповідати одному зі значень первинного ключа КодСотрудника з таблиці Сотрудники. І це дуже розумно: не можна створювати замовлень, не вказуючи співробітників, відповідальних за їхнє виконання.

3.3 Процес розміщення замовлення

Ну що ж, тепер ми знаємо, як виглядають таблиці й відношення в Борей. Тепер подивимося, як, властиво, працює база даних, зосередившись, головним чином, на восьми таблицях, показаних на блок-схемі. Зверніть увагу, що в SQL Server база даних Борей містить трохи більше число таблиць. Втім, для нас це не має особливого значення.

Почнемо з розгляду розміщення клієнтом замовлення в Борей Traders. При цьому нам буде зручно використати деякі функції зовнішнього інтерфейсу, убудовані в Access. Колись, однак, розглянемо дані, записані в таблиці Заказы.

СПРОБУЙТЕ – зчитування й введення даних

1 Припустимо, СКБД Access запущена, і база даних Борей у неї завантажена. Якщо вікно Схема данных дотепер відкрите, можна закрити його.

2 В Access повинне бути відкрите вікно Database (База даних), у якому представлені всі об’єкти бази даних.

3 У меню Об’єкты, у лівій частині вікна, виділяємо рядок Таблицы і відкриваємо таблицю Заказы. Переходимо до останнього запису таблиці. Запишіть значення поля Код заказа останнього запису. Якщо це перше відкриття бази даних, у таблиці повинне бути 830 записів, а в поле Код заказа останнього запису повинне бути записане значення, рівне 11077. Природно, це число може бути іншим, якщо в таблицю вносилися зміни.

4 Закрийте таблицю Заказы і спробуйте вставити з таблицю запис, скориставшись убудованою формою Access. Для цього виділяємо в меню, розташованому в лівій частині вікна бази даних, рядок Формы, після чого активізуємо головну кнопкову форму. Цю форму можна вважати головною в базі даних Борей, оскільки вона служить в якості входу в додаток Access, розробленого навколо таблиць бази даних Борей. Пізніше ми скористаємося цією формою для більш ґрунтовного вивчення таблиць, а поки натискаємо кнопку Заказы.

5 Відкриється форма Заказы:

6 Нам потрібно створити новий запис замовлення, тому – Вставка/Новая запись Ctr++. Відкриється порожня форма. Почніть заповнювати форму якоюсь інформацією. На наступному рисунку показано замовлення від клієнта по імені Alfreds Futterkiste, який купив сироп Aniseed Syrup і м’ясо краба Boston Crab Meat. Замовлення виконала співробітниця Інна Ясенєва. Там, де це можливо, потрібно використати для введення даних значення, що вже є у полях зі списками:

7 Закінчивши введення даних, закрийте форми.

Як це працює – результат

Отже, що ж властиво відбувається з даними в таблицях бази даних Борей, коли ми розміщаємо це замовлення? Всі операції виконуються у двох таблицях - Заказы й Заказано. Це головні таблиці бази даних Борей. Простежимо весь процес:

P Як тільки ми вибрали в списку К оплате: компанію Alfreds Futterkiste, у таблиці Заказы з’явився новий рядок. У результаті замовлення автоматично був привласнений ідентифікатор Код заказа, рівний 11078 (первинний ключ). Код заказа – це поле автонумерації, значення якого автоматично генерує Access.

P Назва компанії замовника – Alfreds Futterkiste, – не було занесена в таблицю! У таблицю Заказы було записано тільки значення коду клієнта (у полі КодКлиента), що відповідає значенню в полі первинного ключа Код клиента таблиці Клиенты. Нагадаємо, що це реляційна база даних, у якій дані, на зразок імен й адрес клієнтів, не дублюються від таблиці до таблиці. Маючи тільки текстове значення в полі КодКлиента, ми можемо витягти іншу інформацію з таблиці Клиенты, використовуючи відношення, встановлене між таблицями Клиенты й Заказы.

P Аналогічно, при введенні Ясенєвої Інни як продавця, у таблицю Заказы був занесений тільки її номер КодСотрудника. Точно та ж ситуація й з компанією, що виконувала доставку: у таблицю був записаний тільки її номер, іншу ж інформацію, якщо буде потреба, можна знайти в таблиці Доставка.

Отже, ми закінчили з введенням замовлення, і тепер відкриємо ще раз таблицю Заказы і перевіримо результат. Ми повинні побачити, що значення поля Код заказа в останньому записі на одиницю більше, ніж у попередній (11078, якщо ми працювали з базою даних Борей уперше):

Як це працює – таблиця Заказано

Отже, ми переконалися, що введене замовлення присутнє у таблиці Заказы. Однак постійте! Адже ми, здається, оформляли замовлення на два товари! Куди ж поділися дані ці про? Якщо придивитися до таблиці Заказы або її схемі, не вдасться виявити ніяких полів про ці дані. Справа в тому, що дані про замовлені товари, записуються в іншу таблицю – Заказано, – яка зв’язана відношеннями з таблицями Заказы й Товары.

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

P Введення нового рядка для кожного замовленого товару в таблицю Заказы. Варіант вже поганий, оскільки для кожного товару потрібно було б дублювати інформації про клієнта та співробітника.

P Визначення в таблиці Заказы декількох полів товарів – наприклад, Товар1, Товар2, Товар3 і т.д. Цей варіант також неприйнятний, оскільки невідомо, скільки повинно бути таких полів.

Ми маємо справу з реалізацією рішення, при якому всі дані про замовлені товари розміщаються в окремій таблиці Заказано, а між таблицями встановлюються відношення. Це є елегантно й, що особливо важливо, ефективно. Кожному товару виділяється окремий запис у таблиці Заказано, у якій передбачені поля КодЗаказа й КодТовара (замість останнього в режимі таблиці виводиться найменування товару). Більше того, комбінація цих полів є первинний ключ таблиці.

Як це працює – відношення між таблицями Заказы й Товари

Розглянемо відношення між таблицями Заказы й Товары. Кожне замовлення може оформлено на будь-яку кількість товарів. З іншого боку, будь-який товар може входити в будь-яке число замовлень. Таким чином, між двома таблицями встановлюється відношення «багато-багато». Але реалізувати прямий зв’язок «багато-багато» між двома таблицями фізично не можливо.

Єдиний спосіб установки відношення такого типу – зв’язування двох таблиць через додаткову таблицю перехресних посилань. Саме такою є таблиця Заказано, зв’язана відношеннями «один-багато» з таблицями Заказы і Товари. Таким чином ми отримуємо відношення «багато-багато» між таблицями Заказы й Товари.

Щоб зрозуміти, як це працює, потрібно знову звернутися до блок-схеми відношень Access.

У завершення дослідження процесу розміщення замовлення, потрібно буде розглянути записи таблиці Заказано, що з’явилися в ній у результаті розміщення нами замовлення номером 11078. В Борей ця таблиця перехресних посилань використовується також для зберігання інформації про замовлення, такі як кількість кожного товару і його ціна на момент замовлення. Відкриємо таблицю Заказано і прогорнемо її до кінця:

Як бачимо, в останніх двох рядках записано саме це значення коду замовлення, і саме стільки товарів (тобто два) було у нашому замовленні.

Отже, головні таблиці бази даних – це, власне кажучи, Заказы й Заказано. Саме в ці таблиці вносяться записи при розміщенні кожного замовлення. Що стосується інших таблиць, то в них зберігається довідникова інформація, звертання до яких обмежуються, найчастіше, зчитуванням. Звичайно, час від часу ці таблиці також модифікуються – наприклад, з появою нового товару на ринку або нового співробітника в компанії, але таке трапляється значно рідше, ніж модифікація таблиць Заказы й Заказано. Щоб вивчити базу даних Борей більш ґрунтовно, потрібно буде відкрити по черзі кожну з таблиць і подивитися, якого роду інформація в ній зберігається.

Як уже було сказано, запити Access – це, власне кажучи, SQL-оператори, які використовуються для отримання даних з таблиць і маніпулювання цими даними відповідно до виконаного завдання. База даних реагує на запит створенням таблиці результату. Запит Access часто йменується віртуальною таблицею, оскільки результат не залишається фізично в базі даних.

Розглянемо деякі запити Access, що входять у комплект бази даних Борей.

У вікні бази даних клацніть на рядку Запросмы у меню Объекты ліворуч. Відкриється список запитів бази даних Борей:

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

У верхній частині вікна таблиці запитів розташований список таблиць, зазначених у запиті. У цьому випадку зазначена тільки таблиця Товары. У нижній частині вікна представлені поля, включені в запит, а також додаткова інформація на зразок критеріїв і порядку сортування. У даному прикладі ми бачимо, що:

P таблиця результату включає тільки поля КодТовара й Марка;

P таблиця результату сортується по полю Марка, у порядку зростання;

P будуть виводитися тільки товари, у яких у поле ПоставкиПрекращ записано значення Нет;

P поле ПоставкиПрекращ не буде представлено в результаті (у запиті це поле зазначене тільки як вказівка не виводити товарів, яких немає в продажі).

Іншими словами, даний запит застосовується для вибірки тільки певних наборів даних. При додаванні в таблицю конструктора нових полів і таблиць, Access, власне кажучи, редагує відповідний SQL-оператор. Щоб побачити SQL-код даного запиту, виконаєте команду Вид/Режим SQL. Замість таблиці конструктора з’явиться наступний SQL-оператор:

SELECT [Список товаров].КодТовара, [Список товаров].Марка

FROM Товары AS [Список товаров]

WHERE ((([Список товаров].ПоставкиПрекращены)=No))

ORDER BY [Список товаров].Марка;

Потрібно пам‘ятати, що це дуже простий оператор. Тут відбувається проста вибірка значень полів КодТовара й Марка зі списку товарів, які є в продажу; крім цього, результату привласнюється аліас і задається впорядкування по найменуванню товару

Щоб переглянути результат виконання запиту, виконаєте команду Запрос/Запуск:

Як і слід було сподіватися, у результаті представлені тільки поля Код товара й Марка. Усього виведено 69 записів. Якщо заглянути в таблицю Товары, там виявиться всього 77 записів, але частина з них виявилася відфільтрованої умовою, по якому значення поля ПоставкиПрекращ повинне бути Нет.

Як це працює – запит «Запрос Заказы»

Тепер спробуємо розглянути кілька складніших запитів. Читач, можливо, звернув увагу на те, що форма Заказы, що ми розглядали трохи раніше, складається власне кажучи із двох форм. У головній формі представлена інформація, узята не з таблиці Заказы, а із запиту з ім’ям Запрос Заказы. Друга форма, вкладена в першу, містить дані про фактично замовлені товари. Знову ж, і в цій формі дані узяті не безпосередньо з таблиці Заказано, а із запиту з ім’ям Сведения о заказах. Потрібно буде розглянути запит докладніше.

Якщо відкрити запит Запрос Заказы в режимі конструктора, неважко помітити, що в ньому відбувається зчитування полів таблиць Заказы й Сотрудники з використанням установленого між ними відношення. Щоб побачити ці поля, скористаємось лінійкою прокрутки:

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

Тепер переведемо запит у режим SQL. Незважаючи на деяку ускладненість оператора SELECT, запит, власне кажучи, виводить вміст полів, зазначених у списку. Головна частина запиту викладена в його останньому рядку:

SELECT Заказы.КодЗаказа, Заказы.КодКлиента, Заказы.КодСотрудника, Заказы.ДатаРазмещения, Заказы.ДатаНазначения, Заказы.ДатаИсполнения, Заказы.Доставка, Заказы.СтоимостьДоставки, Заказы.НазваниеПолучателя, Заказы.АдресПолучателя, Заказы.ГородПолучателя, Заказы.ОбластьПолучателя, Заказы.ИндексПолучателя, Заказы.СтранаПолучателя, Клиенты.Название, Клиенты.Адрес, Клиенты.Город, Клиенты.Область, Клиенты.Индекс, Клиенты.Страна

FROM Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента;

У цьому рядку дається команда СКБД на вибірку даних з таблиць Сотрудники й Заказы, з’єднаних по полю КодКлиента. Наприклад, якщо в одному рядку даних, обраних з таблиці Заказы, значення поля КодКлиента дорівнює ALFKI (це клієнт на ім‘я Alfreds Futterkiste), відбувається пошук всіх таких кодів клієнта у таблиці Заказы, зі значенням у полі Заказы.КодКлиента, рівним ALFKI.

Частина результату, генерованого запитом Запрос Заказы, показана на наступному рисунку:

Поля Индекс получателя й Страна получателя належать таблиці Заказы, а поля Название, Адрес, Город – таблиці Клиенты.

Здатність поєднувати дані з декількох таблиць – дуже важлива властивість SQL і ця можливість використовується в багатьох запитах бази даних Борей й, природно, інших баз даних.

Як це працює – «Сведения о заказах»

Джерелом записів для вкладеної форми Заказы (Подчиненная форма заказов) є запит Сведения о заказах, який надає цій формі дані про замовлені товари. Причина звертання форми до цього запиту – надане ним обчислюване поле, ОтпускнаяЦена:

Розглянемо запит у режимі SQL:

SELECT Заказано.КодЗаказа, Заказано.КодТовара, Товары.Марка, Заказано.Цена, Заказано.Количество, Заказано.Скидка, CCur(Заказано.Цена*[Количество]*(1-[Скидка])/100)*100 AS ОтпускнаяЦена

FROM Товары INNER JOIN Заказано ON Товары.КодТовара = Заказано.КодТовара

ORDER BY Заказано.КодЗаказа;

Зверніть увагу на цікаву особливість оператора SELECT:

CCur(Заказано.Цена*[Количество]*(1-[Скидка])/100)*100 AS ОтпускнаяЦена

Вираз, зазначений як параметр функції CCur, власне кажучи, обчислює вартість кожного замовленого товару, множачи значення полів Цена й Количество таблиці Заказано і віднімаючи з результату знижку (Скидка). Щоб було зрозуміліше, розглянемо це по частинам. Головна частина формули:

Цена * Количество * (1 – Скидка)

Зверніть увагу, що в запиті імена полів вкладені у квадратні дужки. Більше того, поле Цена позначене як [Заказано].[Цена]. Оскільки дві таблиці (Товары й Заказано) через відношення утворюють одну розширену Сведения о заказах, остання містить два поля Цена – по одному від кожної із таблиць. Отже, потрібно вказати, якій із таблиць належить зазначене в запиті поле.

Очевидно, що добуток Цена * Количество утворить основну вартість замовленого товару. Його вартість із урахуванням знижки визначається множником (1-Скидка).

Чому знижка віднімається з одиниці? Тому що в таблиці Заказано вона зберігається саме як частка одиниці. Наприклад, знижка в 15 % записана як 0.15.

Функція CCur виконує перетворення отриманої суми вартості товару у грошовий формат.

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

Висновки

Отже, ми більш-менш докладно розглянули базу даних Борей, і тепер готові приступити до складання найпростіших SQL-операторів – операторів SELECT. Ми навчимося зчитувати дані різноманітних форм із бази даних, що вже побудована й заповнена даними. У нашому випадку це буде база даних Борей.

Дата добавления: 2014-12-07