Лабораторна робота №1-7 на тему Створення модельної АІС за допомогою реляційної СУБД MS Access (DataEase)
« Назад
Лабораторна робота №1-7 (10 год) Тема: Створення модельної АІС за допомогою реляційної СУБД MS Access (DataEase)Мета: Навчитись формувати БД, створювати запити за зразком (QBE-запити) та мовою SQL (DQL), а також регламентувати доступ до БД. Практична робота №1 (2 год.) Тема: Формування нерегламентних простих запитів до бази даних (на прикладі СУБД Access). Мета: Навчитись формувати запити до бази даних Access’у для виборки даних з однієї таблиці. 1. На першому занятті: a) на навчальному диску Eчи D створіть власну папку з іменем ПІБ-№групи, де ПІБ – прізвище та ініціали студента; b) скопіюйте із сервера базу даних Economica.mdb у власну папку ПІБ-№групи; c) якщо БД Economicaзадана у вигляді архівного файла, то розархівуйте його; d) запустіть програму Access (msaccess.exe), завантажте БД Economica.mdbв режимі «Преобразовать базу данных» ізбережіть перетворену БД під іменем Еко-ПІБ-№гр у власну папку. 2. На повторних заняттях: a) скопіюйте із сервера власну папку ПІБ-№групи на навчальний диск EчиD; b) запустіть програму Access (msaccess.exe) і завантажте власну БД Еко-ПІБ-№гр в режимі «Открыть базу даннях”.
Практична робота №2 (2 год) Тема: Використання реляційних і булевих операторів для створення нерегламентних запитів. Використання спеціальних операторів в “умовах” відбору (IN, BETWEEN, IS NULL) та NOT із спеціальними операторамиМета: Набути навичок написання запитів з використанням реляційних і булевих операторів. Для написання запитів і одержання результатів скористаємось програмою MSQRY32.EXE, яка є складовою частиною пакету MSOffice. Як правило, вона розташована в каталозі: C:\Program Files\Microsoft Office\Office, а її ярлик - в каталозі: C:\Program Files\Microsoft Office\Office. Запитами написаними в цій програмі легко скористатись офісними програмами Excel, Word. 1. На першому занятті: a) на навчальному диску Eчи D створіть власну папку з іменем ПІБ-№групи, де ПІБ – прізвище та ініціали студента; b) скопіюйте із сервера базу даних Economica.mdb у власну папку ПІБ-№групи; c) якщо БД Economicaзадана у вигляді архівного файла, то розархівуйте його. 2. На повторних заняттях скопіюйте із сервера власну папку ПІБ-№групи на навчальний диск EчиD. 3. Запустіть програму MSQRY32.EXE. Виберіть пункт меню Файл – Створити запит. 4. У вікні «Вибір джерела даних» 4.1. Виберіть пункт «Нове джерело даних» 4.2. Зніміть галочку з «Використовувати майстер запитів» і натисніть кнопку «ОК» 5. У вікні «Створення нового джерела даних» 5.1. В полі 1 наберіть довільне ім’я (латинськими буквами), наприклад Еко-ПІБ-№гр. 5.2. В полі 2 розкрийте список, натиснувши справа на кнопку і виберіть пункт “MicrosoftAccessDriver(*.mdb)”. 5.3. Натисніть кнопку “Підключить” 5.3.1. У вікні “ODBC Microsoft Access Setup” натисніть кнопку “Select” 5.3.1.1. У вікні “Select Database” виберіть базу Еко-ПІБ-№гр.mdb чи Economica.mdb, яка знаходиться в каталозі E:\ ПІБ-№групи чи D:\ ПІБ-№групи і натисніть кнопку ОК. 5.3.2. У вікні “ODBC Microsoft Access Setup” натисніть кнопку ОК 5.4. У вікні «Створення нового джерела даних» натисніть кнопку ОК 6. У вікні «Вибір джерела даних» список поновиться створеним джерелом Eko. Натисніть кнопку ОК. З’явиться вікно “Додати таблицю”. В ньому натисніть на кнопку ОК 7. В результаті Ви повернулись до основного вікна програми MSQRY32, в якому ми будемо писати запити до бази Economica.mdb. 8. Натисніть кнопку SQL. З’явиться вікно “Запит SQL”. 9. Створіть новий запит результатом якого є перелік підприємств, коди (поле KODUST) яких належать множині {2001, 2023, 2057, 2101, 2120} і збережіть його під іменем Запит-№Пр/№Завдання у власну папку. 10. Створітьновий запит результатом якого є перелік підприємств, кодияких належать множині {2001,2023,2057,2101,2120} і поле OKPO(код ЄДРПОУ) заповнене(якщо воно містить 0, воно вважається незаповненим) ізбережіть його у власну папку. 11. Створітьновий запит результатом якого є перелік підприємств, вяких поле DIRECTORзаповнене (не є пустим NOT IS NULL) ізбережіть його . 12. Створітьновий запит результатом якого є перелік підприємств, кодияких лежать в межах від 2010 до 2101 (використавши операторBETWEEN) і поле DIRECTOR не заповнене ізбережіть його. 13. Створітьновий запит результатом якого є перелік підприємств, сума коду підприємства і коду OKPO містить 8 цифр ізбережіть його. 14. Оформіть звіт. 15. Закрийте програму MSQRY32.EXE. 16. Перенесіть власну папку з іменем ПІБ-№групи із базою даних Еко-ПІБ-№гр.mdbнасервер для збереження практичних робіт.
Практична робота №3 (4 год) Тема: Використання множини таблиць в команді SELECT. З’єднання (join) таблиць, еквіз’єднання таблиць (на основі рівності). Поняття аліасу. Операція з’єднання, операнди якої є однією таблицею (2 копії однієї таблиці)Пояснення. В подальшому нас цікавитимуть таблиці – Ustanova, Limit та Rechovyna. В таблиці Limit зберігається інформація про викиди в атмосферу шкідливих речовин підприємствами м.Києва. В таблиці Rechovyna зберігається довідник шкідливих речовин. В подальшому нас цікавитимуть лише поля: KODUST (код підпрємства), NAZVA (назва підприємства) i ADRESA (адреса підприємства) з таблиці Ustanova; KODUST (код підпрємства), RIK(рік, в якому здійснювався викид), KODRECH(код шкідливої речовини), MVLT (маса викиду в т/рік) з таблиці Limit; KODRECH(код шкідливої речовини), NAZVA (назва речовини), NORM (норматив плати за викид) з таблиці Rechovyn.
Практична робота №4 (4 год) Тема: Використання агрегатних функцій (MAX, MIN, COUNT, AVG, SUM) у запитах. Групування і упорядкування вихідних даних. Використання підзапитів
Практична робота №5 (2 год) Тема: Побудова запитів з використанням агрегатних функцій в умовах відбору. Використання HAVING у запитах. Використання підзапитівСтворіть запит, який повертає перелік речовин з масою викиду вище середнього для підприємства з кодом 2001 за 2001 рік. Результат запиту має містить такі колонки: назва речовини, маса викиду.
Практична робота №6 (2 год) Тема: Побудова запитів з використанням з’єднання зліва (LEFTJOIN), внутрішнього з'єднання (INNER JOIN) і з’єднання зправа (RIGHTJOIN)
Практична робота №7 (2 год) Тема: Використанням команд модифікації даних Бази Даних (UPDATE, INSERT, DELETE, SELECT…INTO)
Лабораторна робота №8 Тема: Створення баз данихТеоретичні відомості Визначення даних
Мова DDL (Data Definition Language), як частина мови SQL, дозволяє створювати і видаляти різні об’єкти бази даних – наприклад, таблиці, представлення, індекси тощо. Основними операторами мови SQL, призначеними для визначення даних, є:
Створення баз данихВ різних СУБД процедура створення баз даних різна. В клієнт-серверних системах право створення баз даних як правило закріплене за адміністратором бази даних (АБД). В персональних системах база даних створюється в процесі інсталяції і настройки параметрів самої СУБД. Стандарт ISO не визначає процедури створення екземпляру бази даних, тому в кожному з діалектів мови SQL використовується свій власний підхід. Наприклад, в СУБД ORACLE створення екземпляру бази є частиною процесу інсталяції системи. У відповідності із стандартом ISO, таблиці та інші об’єкти бази даних існують в деякому середовищі(environment). Крім того, кожне середовище містить в собі один або більше каталогів (catalog), а кожний каталог – набір схем (shema). Схема – це поіменована сукупність взаємопов’язаних об’єктів бази даних ( всі об’єкти бази даних повинні бути описані в тій чи іншій схемі). Об’єктами схеми можуть бути таблиці, представлення, домени і набори символів. Стандарт залишає право вибору конкретного механізму створення і знищення каталогів за розробником СУБД, але регламентує механізм створення і знищення схем. Оператор визначення схеми має такий формат: CREATE SHEMA [name | AUTHORIZATION creator-identifier] Таким чином, якщо творцем схеми під іменем sql_test буде користувач Ivanov, то даний оператор виглядатиме таким чином: CREATE SHEMA sql_test AUTHORIZATION Ivanov Схема видаляється з допомогою оператора DROP SHEMA, який має такий формат: DROP SHEMA name [RESTRICT | CASCADE] Якщо вказано ключове слово RESTRICT (саме воно діє за змовчуванням), схема повинна бути пустою(не містити жодного об’єкту), інакше виконання цієї операції буде відмінено. Якщо вказано ключове слово CASCADE, автоматично будуть видалені всі пов’язані з цією схемою об’єкти. Якщо ж в ході виконання цього оператора якась операція закінчилась невдало, буде відмінено виконання всього оператора DROP SHEMA. На сьогоднішній день оператори CREATE SHEMA і DROP SHEMA реалізовані в небагатьох СУБД. Створення таблиць (оператор CREATE TABLE)Після створення загальної структури бази даних можна приступати до створення таблиць, які представляють відношення, що входять до складу проекту бази даних. Для цього використовується оператор CREATE TABLE (спрощений варіант), який має формат: CREATE TABLE table_name (column_name data_type [NULL | NOT NULL][,…]) В результаті виконання цього оператора буде створена таблиця з іменем table_name, яка матиме один або більше стовпчиків з іменами що задаються параметрами column_name і матимуть тип, що задається параметрами data_type. Ключове слово NULL визначає, що дане поле може містити значення NULL. Ключове слово NOT NULL визначає, що дане поле не може містити значення NULL. За змовчуванням, стандарт ISO визначає, що діє ключове слово NULL. Стовпчики первинних ключів завжди визначаються з ключовим словом NOT NULL. Приклад використання оператора CREATE TABLE. Оператор CREATE TABLE Osoba ( kod INTEGER NOT NULL, priz VARCHAR(50) NOT NULL) створить таблицю Osoba, в якій є 2 стовпчики – kod i priz. Поле kod має цілочисельний тип, а поле priz має тип VARCHAR, тобто символьний і яке містить не більше 50 символів. Оператор, який створить таблицю table_a, якою ми користувались на попередніх лекціях, матиме вид CREATE TABLE table_a( kod INTEGER NOT NULL, suma DECIMAL(8,2) NOT NULL, data DATE ) В стандарті ISO наведено декілька варіантів визначення цього оператора. Базовий формат цього оператора має вид: CREATE TABLE table_name {(column_name data_type [NOT NULL] [UNIQUE] [DEFAULT default_option] [CHECK (search_condition)] [,…]} [PRIMARY KEY (list_of_columns),] {[UNIQUE (list_of_columns)] [,…]} {FOREIGN KEY (list_of_foreign_key_columns) REFERENCES parent_table_name [list_of_candidate_key_columns)], [MATCH {partial | FULL}] [ON UPDATE referential_action] [ON DELETE referential_action] [,…]} {[CHECK (searh_condition)] [,…])} Дана версія оператора CREATE TABLE містить не тільки визначення даних (параметри table_name, column_name, data_type і специфікатор NOT NULL, які ми розглядали у спрощеному варіанті) але й засоби визначення цілісності бази даних, ключів і таке інше. Необов’язкова фраза DEFAULT задає значення - default_option, яке буде вставлятись оператором INSERT для невизначеного значення даного стовпчика (default_option може бути літералом). Необов’язкова фраза UNIQUE задає унікальність значення цього поля, тобто не повинно існувати двох і більше рядків в таблиці з одноковим значення цього поля. Для стовпчиків, які вказані у фразі UNIQUE повинен застосовуватись режим NOT NULL для їх можливих значень. Система заблокує виконання любого оператора INSERT аба UPDATE який створює рядок з дубльованими значеннями стовпчиків, які вказані у фразі UNIQUE. Необов’язкова фраза CHECK (search_condition). Кожний стовпчик має власний домен, тобто набір допустимих значень. Наприклад, для визначення наукового ступеня співробітника достатньо всього 3 значення – не має, кадидат і доктор. Тому домен для поля Naukov_stupin можна визначити як набір з 3-х значень- ‘не має’, ‘кадидат’, ‘доктор’. Фраза CHECK (Naukov_stupin IN(‘не має’, ‘кадидат’, ‘доктор’)) задаватиме допустимі значення для поля Naukov_stupin. Тобто ніякі інші значення в це поле СУБД не дозволить внести. Всі інші фрази оператора CREATE TABLE, що залишились називають обмеженнями для таблиці і можуть відокремлюватись (не обов’язково) фразою CONSTRAINT constraint_name. Використання цієї фрази дозволить в подальшому відмінити те чи інше обмеження за допомогою оператора ALTER TABLE. Фраза PRIMARY KEY визначає стовпчик або стовпчики, які утворюють первинний ключ таблиці. Визначення любої таблиці повинно містити не більше однієї фрази PRIMARY KEY. Система заблокує виконання любого оператора INSERT аба UPDATE який створює рядок з дубльованими значеннями в стовпчиках, вказаних у фразі PRIMARY KEY. Наприклад, якщо S1 i S2 – стовпчики, які вказані у фразі PRIMARY KEY, і в таблиці вже є рядок зі значеннями a i b у цих стовпчиках відповідно, то спроба вставити новий рядок зі значеннями a i b у цих стовпчиках буде заблокована. Фраза FOREIGN KEY дозволяє визначити зовнішні ключі таблиці. Фраза містить такі елементи:
Правила підтримки цілісності посилань, які задаються у фразі FOREIGN KEY після ON UPDATE і ON DELETE:
Приклади. Оператор CREATE TABLE Vidom( kod INTEGER NOT NULL, year SMALLINT NOT NULL, mounth SMALLINT NOT NULL, D REAL NOT NULL DEFAULT 0 , CONSTRAINT ind PRIMARY KEY (kod,year, mounth)) створить таблицю з назвою Vidom, яка матиме стовпчики - kod, year, mounth, D і первинний ключ з іменем ind, який визначається стовпчиками kod,year, mounth. Причому поля kod,year, mounth, D не можуть приймати значення NULL, а поле D за змовчуванням прийматиме значення 0, тобто оператор INSERT, що не містить явного задання значення поля D, автоматично підставлятиме значення 0. Приклад використання CHECK для недопущення вводу помилкових значень. CREATE TABLE Osoba (ID_OSOBA INTEGER NOT NULL, PRIZ VARCHAR(50) NOT NULL, NAME VARCHAR(50) NOT NULL, POBAT VARCHAR(50) NOT NULL, SEX CHAR(4) CHECK (SEX IN (‘жін.’,’чол.’)), ID_FACULT SMALLINT NOT NULL) Цей оператор створить таблицю Osoba з відповідними стовпчиками, причому в стовпчик SEX система не дозволить ввести нічого крім значень ‘жін.’і ’чол.’. Слід зауважити, що не всі діалекти SQL підтримують фразу DEFAULT (Access 97 не підтримує цієї фрази). ХІД ВИКОНАННЯ РОБОТИ
Практична та лабораторна робота №9 Тема: Використанням команди видалення таблиці
Теоретичні відомості Видалення таблиць (оператор DROP TABLE)
Непотрібні таблиці видаляються з бази з допомогою оператора DROP TABLE, який має формат: DROP TABLE table_name [RESTRICT | CASCADE] Наприклад, для видалення таблиці Vidomist з бази необхідно застосувати оператор: DROP TABLE Vidomist Якщо в операторі вказано ключове слово RESTRICT і в базі існує хоча б один об’єкт, який пов’язаний з таблицею table_name (наприклад, VIEW в якому використовується ця таблиця), операція видалення буде відхилена. Якщо в операторі вказано ключове слово CASCADE, автоматично буде видалена таблиця table_name і всі об’єкти, які пов’язані з цією таблицею. ХІД ВИКОНАННЯ РОБОТИ
Практична та лабораторна робота №10 Тема: Використанням команди модифікації структури таблиць
Теоретичні відомості Модифікація структури таблиці (оператор ALTER TABLE)Для зміни структури існуючої таблиці стандартом ISO передбачено оператор ALTER TABLE. Визначення цього оператора містить шість параметрів для виконання таких операцій:
Оператор ALTER TABLE має такий формат: ALTER TABLE table_name [ADD [COLUMN] column_name data_type [NOT NULL] [UNIQUE] [DEFAULT default_option] [CHECK (search_condition)]] [DROP [COLUMN] column_name [RESTRICT | CASCADE]] [ADD [CONSTRAINT [constrain_name]] table_constraint_definition] [DROP CONSTRAINT constrain_name [RESTRICT | CASCADE]] [ALTER [COLUMN] SET DEFAULT default_option] [ALTER [COLUMN] DROP DEFAULT] Тут параметри мають ті ж призначення, що і в операторі CREATE TABLE. Параметр table_constraint_definition може приймати одне з таких значень: PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK. У фразі DROP COLUMN column_name задається ім’я стовпчика, який видаляється з таблиці. Кваліфікатор RESTRICT означає, що якщо на даний стовпчик посилається який небудь інший об’єкт бази, він з таблиці не видалиться; кваліфікатор CASCADE означає, що крім стовпчика вказаної таблиці будуть видалені всі посилання на даний стовпчик в базі. За змовчуванням діє кваліфікатор RESTRICT. Оператор ALTER TABLE реалізовано не у всіх діалектах SQL. В деяких діалектах підтримується урізаний варіант цього оператора (не дозволяється видаляти вже існуючі стовпчики). ХІД ВИКОНАННЯ РОБОТИ
Практична та лабораторна робота №11 Тема: Використання транзакцій
Теоретичні відомості Використання транзакцій
Стандарт ISO визначає модель транзакцій, яка використовує оператори COMMIT і ROLLBACK. Більшість (але не всі) комерційних реалізацій мови SQL підтримують цю модель, яка вперше була реалізована в СУБД DB2 корпорації IBM. Транзакцією називається логічна одиниця роботи, що складається з одного або більше SQL-операторів, яка обробляється системою як єдина і неподільна дія. В стандарті вказується, що в мові SQL транзакція автоматично запускається любим ініціалізуючого транзакцію SQL-оператором (наприклад, SELECT, INSERT, UPDATE). Зміни, що внесені в базу даних в ході виконання даної транзакції, не будуть видимі для любих інших транзакцій, що виконуються паралельно, до тих пір поки ця транзакція не буде завершена. Завершення транзакції може бути виконано одним з наступних способів:
В мові SQL заборонено використовувати вкладені транзакції. За допомогою оператора SET TRANSACTION користувачі мають змогу налаштовувати процедуру обробки транзакцій. Основний формат цього оператора має вид: SET TRANSACTION [READ ONLY | READ WRITE] | [ISOLATION LEVEL READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZED] Кваліфікатори READ ONLY і READ WRITE вказують, що в транзакціях допускається лише виконання операцій читання або читання і записування відповідно. За змовчуванням використовується кваліфікатор READ WRITE (якщо тільки не вибрано рівень ізоляції READ UNCOMMITTED). Показник рівня ізоляції визначає степінь взаємодії з іншими транзакціями. Властивості транзакцій. Існують деякі властивості, які властиві кожній транзакції. Нижче наведено чотири основних властивостей.
ХІД ВИКОНАННЯ РОБОТИ
З повагою ІЦ “KURSOVIKS”! |