|
|
ОСНОВИ ПОБУДОВИ РЕЛЯЦІЙНИХ БАЗ ДАНИХ В СЕРЕДОВИЩІ MS ACCESS 2003Стр 1 из 5Следующая ⇒ Ільєнко Ю.І.
І45 Створення баз даних в середовищі MS Access 2003 [Текст]: метод. вказ. і індивід. завд. для проведення лаб. і самост. робіт студ. екон. спец. ден. і заоч. форм. навчання/ Ю.І. Ільєнко, Г.В. Шершньова; М-во освіти і науки України, Донец. нац. ун-т економіки і торгівлі ім. М. Туган-Барановського, Каф. інформ. систем і технологій упр.;– Донецк: [ДонНУЕТ], 2011. – 79 с.
Методичні вказівки і індивідуальні завдання призначені для проведення лабораторних занять та самостійної роботи з дисципліни «Інформатика» для студентів економічних спеціальностей денної та заочної форм навчання. Методична розробка містить теоретичний матеріал та методичні рекомендації для виконання практичних завдань за модулем «Створення баз даних в середовищі MS Access 2003». За темами наведені індивідуальні завдання і приклади тестових завдань.
ББК 65.386.8я73
© Ільєнко Ю.І., Шершньова Г.В. 2011 © Донецький національний університет економіки і торгівлі імені Михайла Туган-Барановського, 2010 Зміст
ВВЕДЕННЯ Сучасні інформаційні системи характеризуються великими об’ємами даних, їх складною організацією, а також високими вимогами до швидкості та ефективності обробки цих даних. Це стає можливим при використанні спеціальних програмних засобів - систем управління базами даних (СУБД). Система управління базами даних - це комплекс програмних і мовних засобів, необхідних для створення, обробки баз даних і підтримки їх в актуальному стані. Метою модуля «Створення баз даних в середовищі MS Access 2003» є вивчення принципів побудови баз даних, освоєння правил створення і редагування таблиць, запитів, форм та звітів у СУБД Access 2003. Завданням є одержання знань, умінь та навичок з основ побудови реляційних баз даних. Для виконання індивідуальних завдань за темами модуля студент повинен пройти попередню теоретичну та практичну підготовку на лекційних та лабораторних зайняттях, самостійно працювати з літературними джерелами. В результаті виконання індивідуальних завдань студент оформлює звіт на листах формату А4, який захищається викладачу (робота показується на екрані ПК і студент відповідає на питання викладача).
ОСНОВИ ПОБУДОВИ РЕЛЯЦІЙНИХ БАЗ ДАНИХ В СЕРЕДОВИЩІ MS ACCESS 2003 Мета: Сформувати у студентів теоретичні знання про основи побудови реляційних баз даних в середовищі MS Access 2003.
1.1. Теоретичний матеріал з теми Основні поняття реляційних баз даних: нормалізація, зв’язки та ключі Бази даних — це сукупність відомостей про реальні об'єкти, процеси, події або явища, що відносяться до певної теми або задачі, організована таким чином, щоб забезпечити зручне представлення цієї сукупності як в цілому, так і будь-якої її частини. Існують 4 основні моделі даних – списки (плоскі таблиці), реляційні бази даних, ієрархічні і мережні структури. Протягом багатьох років переважно використовувалися плоскі таблиці типу списків в Excel. В даний час найбільше розповсюдження при розробці баз даних отримали реляційні моделі даних. Реляційна модель даних є сукупністю найпростіших двовимірних таблиць, що мають назву відношення (англ. relation). Цей спосіб організації даних дозволяє пов'язувати дані в одній таблиці з даними в інших таблицях через унікальні ідентифікатори (ключі) або ключові поля. Реляційна база даних є безліччю взаємозв'язаних таблиць, кожна з яких містить інформацію про об'єкти певного типу. Кожний рядок таблиці включає дані про один об'єкт (наприклад, клієнті, автомобілі, документі), а стовпці таблиці містять різні характеристики цих об'єктів — атрибути (наприклад, найменування і адреси клієнтів, марки і ціни автомобілів). Рядки таблиці називаються записами; всі записи мають однакову структуру — вони складаються з полів, в яких зберігаються атрибути об'єкту. Кожне поле запису містить одну характеристику об'єкту і має строго певний тип даних (наприклад, текстовий рядок, число, дата). Всі записи мають одні і ті ж поля, тільки в них містяться різні значення атрибутів. Структура таблиці
Для структуризації інформації, розміщення її в таблицях і маніпулювання даними використовуються системи управління базами даних (СУБД). Основні функції СУБД — це визначення даних (опис структури баз даних), обробка даних і управління даними. В даний час налічується більше 50 типів СУБД для персональних комп'ютерів. До найпоширеніших типів СУБД відносяться: MS SQL Server, Oracle, Informix, Sybase, DB2, MS Access тощо. 1) Принципи нормалізації: - в кожній таблиці БД не повинно бути полів, що повторюються; - в кожній таблиці повинен бути унікальний ідентифікатор (первинний ключ); - кожному значенню первинного ключа повинна відповідати достатня інформація про тип сутності або про об’єкт таблиці (наприклад, інформація про успішність, про групу або студентів); - зміна значень в полях таблиці не повинна впливати на інформацію в інших полях (крім змін в полях ключа). 2) Види логічного зв’язку. Зв’язок встановлюється між двома загальними полями (стовбцями) двох таблиць. Існуючі зв’язки з відношенням «один-до-одного», «один-до-багатьох» та «багато-до-багатьох». Відносини, що можуть існувати між записами двох таблиць: - один–до–одного, кожному запису з однієї таблиці відповідає однин запис в іншій таблиці; - один–до–багатьох, кожному запису з однієї таблиці відповідає декілька записів іншої таблиці; - багато–до–одного, множині записів з однієї таблиці відповідає один запис в іншій таблиці; - багато-до-багатьох, множині записів з однієї таблиці відповідає декілька записів в іншій таблиці. Тип відношень у зв’язку, що створюється, залежить від способу визначення полів, що зв’язуються: Відношення «один-до-багатьох» створюється в тому випадку, коли тільки одне з полів є полем первинного ключа або унікального індексу. Відношення «один-до-одного» створюється в тому випадку, коли обидва полів, що зв’язуються, є ключовими або мають унікальні індекси. Відношення «багато-до-багатьох» фактично є двома відношеннями «один-до-багатьох» з третьою таблицею, первинний ключ якої складається з полів зовнішнього ключа двох інших таблиць. Ключі. Ключ – це поле (або декілька полів), що додаються до таблиці і дозволяють встановити зв’язок з записами в іншій таблиці. Існують ключі двох типів: первинні та вторинні (або зовнішні). Первиннийключ – це одно або декілька полів, комбінація значень яких однозначно визначають кожний запис в таблиці. Первинний ключ не допускає значень Null та завжди повинен мати унікальний індекс. Первинний ключ використовується для зв’язування таблиці з зовнішніми ключами і інших таблицях. Зовнішній (вторинний) ключ - це одно або декілька полів (стовбців) в таблиці, що містить посилання на поле або поля первинного ключа в іншій таблиці. Зовнішній ключ визначає спосіб об’єднання таблиць. З двох логічно зв’язаних таблиць одну називають таблицею первинного ключа або головною таблицею, а іншу таблицею вторинного (зовнішнього) ключа або підлеглою таблицею. СУБД дозволяють зіставити споріднені записи з обох таблиць і спільно вивести їх у формі, звіті або запиті. Існує три типи первинних ключів: ключові поля лічильника, простий ключ або складовий ключ. Поле лічильника (Тип даних «Лічильник»). Тип даних поля в базі даних, в якому для кожного запису, що додається таблицю, в поле автоматично заноситься унікальне числове значення. Простий ключ. Якщо поле містить унікальні значення, такі як коди або інвентарні номери, то це поле можна визначити як первинний ключ. У якості ключа можна визначити будь-яке поле, що містить дані, якщо це поле не містить значення Null або значення, що повторюються. Складовий ключ. У випадках, коли неможливо гарантувати унікальність значень кожного поля, існує можливість створити ключ, що складається з декількох полів. Частіше за все така ситуація виникає для таблиці, що використовується для скріплення двох таблиць «багато-до-багатьох». Якщо виникають утруднення з вибором відповідного типа первинного ключа, то в якості ключа доцільно вибрати поле лічильника.
Типи даних MS Access 2003
Серед типів даних Access є спеціальний тип - Счетчик. В поле цього типу Access автоматично нумерує строки таблиці у зростаючій послідовності. Редагувати значення такого поля неможна. Кожне поле має індивідуальний набір властивостей, за якими можна встановити, як повинні зберігатися, відображатися і оброблятися данні. Набір властивостей поля залежить від обраного типу даних. Для визначення властивостей поля використовується бланк Свойства поля у нижній частині вікна конструктора таблиць. Загальні властивості полів
Крім вищеперелічених типів даних в списку є елемент Майстер підстановок, який дозволяє представити значення полів у вигляді простого або комбінованого списку. Додаткові властивості такого поля представлені на вкладці Подстановка вікна конструктора таблиць. Після визначення структури таблиць БД необхідно вказати Access спосіб, яким ці таблиці зв’язані. Для визначення зв’язків потрібно перейти у вікно бази даних і виконати команду Сервис-Схема данных або на панелі інструментів натиснути кнопку У вікні Добавление таблицы потрібно виділити імена таблиць, що будуть добавлені в схему даних, і натиснути на кнопку Добавить. У вікні Схема данных імена первинних ключів виділені полужирним шрифтом. Для зв’язку таблиць необхідно виділити ключове поле таблиці, натиснути ліву кнопку миші, перетягнути її курсор на аналогічне поле у другій таблиці, після чого кнопку миші відпустити. У результаті з’явиться діалогове вікно Связи. В цьому вікні відображаються імена полів, за якими зв’язуються таблиці. Для збереження цілісності даних у зв’язуваних таблицях необхідно включити прапорець Після установки цілісності даних СУБД Access включає дві додаткові опції: Каскадное обновление связанных полей і Каскадное удаление связанных полей. При зміні будь-якого значення ключового поля в ключовій таблиці автоматично оновлюються значення цього поля для відповідних записів у всіх зв’язаних таблицях, а видалення одного з записів в ключовій таблиці приведе до видалення тих записів, що мають таке ж значення ключа. В результаті між таблицями відображається лінія виду
2.2. Приклад створення таблиць для бази данних «Реалізація продукції публічним акціонерним товариством "НОРД"»
Визначаємо ім’я бази даних та місце її зберігання. Створювана база даних повинна відповідати структурі (рис. 1):
Рисунок 1 – Схема даних бази «Реалізація продукції ПАТ "НОРД"»
2.2.1. Створення таблиці «Довідник товарних груп». Вибрати «Создание таблицы путем ввода данных». Перейменувати перший стовпчик таблиці Поле1 у «Товарна група», клацнувши двічі по назві стовпця. Заповнити таблицю даними:
Зберегти таблицю під ім’ям «Довідник товарних груп». На запит СУБД про створення ключового поля натиснути ДА. Перейменувати нове поле Код у Код_групи. Отримуємо:
2.2.2. Створення таблиці «Довідник замовників». Вибрати у вікні «Создание таблицы путем ввода данных». Перейменувати стовпчики таблиці Поле1, Поле2, Поле3, Поле4 у «Замовник», «Юридична адреса», «Телефон», «Номер рахунка» відповідно (клацнувши двічі по назві стовпців). Послідовно заповнити таблицю даними:
Зберегти таблицю під ім’ям «Довідник замовників». На запит СУБД про створення ключового поля натиснути ДА. Перейменувати нове поле Код у Код_замовника. Отримуємо:
2.2.3. Створення таблиці «Довідник товарів». Вибрати спосіб створення «Создание таблицы в режиме конструктора». У вікні конструктора перерахувати поля та визначити їх властивості.
Для поля Код_товару указати, що воно є ключовим (клацнувши по ньому правою кнопкою миші або натиснувши по кнопці Для поля Характеристика встановити у загальних властивостях розмір поля 100. Для поля Код_групи виконати підстановку на закладці «ПОДСТАНОВКА»
Зберегти таблицю під ім’ям «Довідник товарів» та заповнити даними:
2.2.4. Створення таблиці «Реалізація». Вибрати спосіб створення «Создание таблицы в режиме конструктора». У вікні конструктора перерахувати поля та визначити їх властивості.
Для поля Код_товару зробити підстановку на закладці «ПОДСТАНОВКА»
Для поля Код_замовника зробити підстановку на закладці «ПОДСТАНОВКА»
Для поля Кількість визначити властивості на вкладці «Общие»
Для поля Дата визначити властивості на вкладці «Общие»
Зберегти таблицю під ім’ям «Реалізація». Заповнити таблицю даними:
2.2.5. Створення схеми даних (встановлення зв’язків між таблицями).
Виконати команду із головного меню Сервис – Схема данных… Додати у вікно всі таблиці. Лівою кнопкою миші потягнути від первинного (виділений жирним) ключа до вторинного (див. рис. 1). В діалоговому вікні вказати:
Создать. Закрити вікно схеми даних, зберегти. 2.3. Тестові питання з теми
Основні оператори відбору
При роботі з датою та часом значення повинні бути заключені між символами #. Наприклад: #10 мая 1998# >#31.12.2010# В СУБД Access використовується ряд інших функцій, які допоможуть задати умови відбору для дати та часу: Day (дата) – повертає значення дня в діапазоні від 1 до 31. Month (дата) – повертає значення місяця в діапазоні від 1 до 12. Year (дата) – повертає значення року в діапазоні від 100 до 9999 Date () – повертає значення поточної дати. Обчислювані поля. Над будь-якими полями можна задати обчислення та зробити обчислюване значення новим полем в запиті. Для цього в рядку Поле бланку запита через Построитель Наприклад: Вартість замовлення: [Реалізація]![Кількість]*[Довідник товарів]![Ціна] У виразах можна використовувати таких операторів: - арифметичні: * множення; + додавання; - віднімання; / ділення; ^ зведення в ступінь; - з'єднання частин тексту за допомогою знаку &, наприклад: =[ Прізвище] & “ “&[Ім'я]
Підсумкові запити значно відрізняються від звичайних. В них поля поділяються на 2 типи: - поля, за якими здійснюється групування даних; - поля, для яких проводяться обчислення. Для створення підсумкового запита, знаходячись у режимі конструктора, необхідно натиснути кнопку Групповые операции Групування в підсумковому запиті здійснюються тільки за одним полем. У решті необхідно визначити підсумкові функції. Основні групові функції, які можна застосувати: SUМ - загальна сума всіх значень заданого поля; AVG - середнє значення; MIN - мінімальне значення; MAX – максимальне значення; COUNT – кількість записів за визначеним полем; FIRST - перше значення серед відібраних записів; LAST - останнє значення серед відібраних записів.
Перехресні запити. За допомогою перехресного запиту можна більш наглядно представити дані підсумкових запитів, що передбачають групування за декількома признаками. В цьому випадку значення полів за першою ознакою групування можуть стати заголовками рядків, а за другою - заголовками стовбців. Для створення перехресного запиту необхідно в режимі конструктора виконати команду Запрос→Перекрестный. В рядку Перекрестная таблица необхідно вказати як буде використано дане поле: у вигляді заголовка рядків, заголовка стовбців або значень. Параметричний запит дозволяє користувачу задати критерій відбору через введення відповідного параметра при визові запиту. При створенні запита в конструкторі для ключового поля в рядку Условие отбора необхідно в квадратних дужках ввести [Введіть назва_поля] (повідомлення, яке буде виводиться на екран при виконанні запита). Виконання запита. Готовий запит виконується після клацання по кнопці Завдання для виконання лабораторної роботи
Згідно запропонованої структури реляційної бази даних побудувати необхідні об’єкти бази даних: таблиці, запити, форми (у тому числі і головну), звіти. Роздруковані об’єкти бази даних та опис виконання роботи сформувати у звіт. Варіант 1 Створити базу даних «Агентство з продажів авіаквитків» Агентство займається продажами авіаквитків на різні рейси, веде облік проданих квитків і облік пасажирів, що придбали білети. Тому виникає необхідність у зберіганні та обробці даних, що згруповані таким чином: Розклад рейсів
Інформація про пасажирів
Придбання квитків
Зв’язати таблиці бази даних та заповнити їх даними. Створення запитів 1. Створити запит на вибірку з наступною інформацією: тип літака, пункт відправлення, пункт призначення, ПІБ, вартість квитків. 2. Створити запит на вибірку, в результаті якого можна отримати дані (тип літака, пункт відправлення, пункт призначення, ПІБ, кількість та вартість квитків) про клієнтів, що придбали квитки до Москви у поточному місяці. 3. Створити підсумковий запит, який би відображав загальну кількість проданих білетів на рейси поточного місяця. 4. Створити параметричний запит, в результаті якого буде відображена інформація (тип літака, пункт відправлення, пункт призначення, ПІБ, кількість та вартість квитків) за конкретними пунктами відправлення та призначення. 5. Створити запит, що здійсніть видалення інформації про клієнтів, що придбали білети на рейси від __.__.20__ р. 6. На основі першого запиту створити запит на створення таблиці. 7. Створити перехресний запит по рейсу, даті, середній вартості квитків. Робота з формами 1. В режимі майстра створити форму «Довідник рейсів» на основі таблиці «Розклад рейсів». За її допомогою додати до таблиці 5 записів. 2. В режимі конструктора на основі таблиці «Клієнти агентства» створити форму «Інформація про пасажирів». За її допомогою додати до таблиці 5 нових записів. Створення звітів 1. За допомогою майстра звітів створити звіт «Реалізація квитків». Здійснити угрупування даних по пункту відправлення, підсумкові значення для груп і звіту в цілому повинні містити загальну вартість проданих квитків. 2. За допомогою конструктора створити звіт «Реалізація квитків у поточному місяці». У звіті виконати угрупування по рейсах, всередині по даті. Підсумкові значення для груп і звіту в цілому повинні містити мінімальну та середню кількість проданих квитків. Варіант 2 Створити базу даних «Проектна організація» Проектна організація проектуванням будівель як для промислових, так і для непромислових цілей. Тому виникає необхідність у зберіганні та обробці даних, що згруповані таким чином: Зберігати дані про співробітників, проекти та терміни їх виконання. У виконанні одного проекту можуть приймати участь декілька співробітників. Необхідно вести облік виконання етапів проекту.
Довідник посад
Довідник співробітників
Довідник проектів
Виконання проектів
Зв’язати таблиці бази даних та заповнити їх даними. Створення запитів 1. Створити запит на вибірку з такою інформацією: назва проекту, керівник, дата початку і закінчення, ПІБ співробітника, найменування посади, опис етапу роботи, дата закінчення етапу. 2. Створити запит на вибірку, в результаті якого можна отримати дані (назва проекту, керівник, дата початку і закінчення, ПІБ співробітника, найменування посади, опис етапу роботи, дата закінчення етапу) про співробітників, що закінчують виконання етапів роботи над проектами у поточному місяці. 3. Створити підсумковий запит, який би відображав загальну кількість співробітників, що працюють над проектами. 4. Створити параметричний запит, в результаті якого буде відображена інформація (назва проекту, дата початку і закінчення, ПІБ співробітника, опис етапу роботи, дата закінчення етапу) за конкретним керівником. 5. Створити запит, що збільшить посадовий оклад співробітників на 250 грн. 6. На основі першого запиту створити запит на створення таблиці. 7. Створити перехресний запит по проекту, керівника, середньому бюджету. Робота з формами 1. В режимі майстра створити форму «Реєстр співробітників» на основі таблиці «Довідник співробітників». За її допомогою додати до таблиці 5 записів. 2. В режимі конструктора на основі таблиці «Довідник проектів» створити форму «Проекти». За її допомогою додати до таблиці 5 нових записів. Створення звітів 1. За допомогою майстра звітів створити звіт «Виконання проектів». Здійснити угрупування даних по назві проекту, посаді, підсумкові значення для груп і звіту в цілому повинні містити загальну кількість співробітників. 2. За допомогою конструктора створити звіт «Виконання проектів у поточному місяці». У звіті виконати угрупування назві проекту, посаді. Підсумкові значення для груп і звіту в цілому повинні містити мінімальну та середню кількість задіяних співробітників. Варіант 3 Створити базу даних «Спортивні змагання» Спортсмени під керівництвом тренерів займаються окремими видами спорту та приймає участі у різних спортивних заходах. При цьому у рамках одного змагання спортсмен може брати участі у декількох секціях. Всі результати змагань заносяться у таблицю. За результатами участі спортсменів у змаганнях відбувається нагородження. Секції
Спортивні заходи
Учасники
Результати змагання
Зв’язати таблиці бази даних та заповнити їх даними. Створення запитів 1. Створити запит на вибірку з наступною інформацією: П.І.Б. учасника, тренера, клуб, назва заходу, секція, зайняте місце. 2. Створити запит на вибірку, в результаті якого можна отримати дані (назва заходу, П.І.Б. учасника, тренера, зайняте місце, секція) про конференції, що відбулися у поточному році. 3. Створити підсумковий запит, який би відображав загальну кількість учасників по кожній секції. 4. Створити параметричний запит, в результаті якого буде відображена інформація (назва заходу, секція, місце) по конкретному місту проведення. 5. Створити запит, в результаті якого в таблиці «Спортивні заходи» для заходів, що відбудуться у наступному місяці місто проведення м. Донецьк зміниться на м. Макіївка. 6. На основі першого запиту створити запит на створення таблиці. 7. Створити перехресний запит по назв   Система охраняемых территорий в США Изучение особо охраняемых природных территорий(ООПТ) США представляет особый интерес по многим причинам...  Что делает отдел по эксплуатации и сопровождению ИС? Отвечает за сохранность данных (расписания копирования, копирование и пр.)...  ЧТО ПРОИСХОДИТ, КОГДА МЫ ССОРИМСЯ Не понимая различий, существующих между мужчинами и женщинами, очень легко довести дело до ссоры...  Что способствует осуществлению желаний? Стопроцентная, непоколебимая уверенность в своем... Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте:
|
Схема данных.
. Після цього СУБД Access зробить неможливим запис у таблицю такого значення загального поля, якого немає у ключовій таблиці.
.
Завантажуємо СУБД Access. Створюємо нову базу даних командою головного меню Файл → Создать… В області задач вибираємо Створити нову базу даних…
на панелі інструментів).
вводиться формула для обчислення. Ім’я полів беруться у квадратні дужки.
на панелі інструментів або скористатися командою Групповые операции із меню Вид. В результаті чого в бланку запиту з’явиться рядок Групповая операция.
панелі інструментів в режимі конструктора запитів або при активізації команди Запуск із меню Запрос. У результаті буде отримана таблиця з відповіддю на задані умови.