1.5 Контрольні питання:
1. Як задається первинний ключ?
2. Чи можуть ключові поля мати невизначені значення?
3. Як задаються зв'язки між таблицями БД? Як встановити зв'язок від однієї таблиці до декількох?
3. Чим визначається тип зв'язку між таблицями?
5. У чому полягає підтримка посилальної цілісності? Основні властивості зовнішнього ключа.
6. Яка реакція системи на видалення записів посилального відношення?
7. Яка реакція системи на додавання записів посилального відношення?
1.6 Літературні джерела:
1. Моркес Д. Access 2003. Эффективный самоучитель. – СПб.: Наука и техника, 2006. – 352 с.
2. Золотова С.В. Практикум по Access. – М.: Финансы и статистика, 2004. – 144 с.
3. Чаповська Р.Б., Жмуркевич А.Є. Робота з базами даних MS ACCESS 2000. Навчальний посібник. – Київ: ЦУЛ, 2003. – 324 с.
1 Вивчення метод і в збору та накопичення даних
ДЛЯ НАУКОВИХ ДОСЛІДЖЕНЬ ЗАСОБАМИ MYSQL / MS ACCESS
РОЗДІЛ 2 МОДИФІКАЦІЯ СЕРВЕРНИХ БАЗ ДАНИХ МОВОЮ SQL
2.1 Мета роботи
Засвоїти методологію і технологію модифікації (тобто внесення змін) в таблиці даних з метою зменшення або збільшення кількості стовпців в таблиці або вилучення таблиці з бази даних мовними засобами MS Access SQL.
2.2 Обладнання для виконання роботи
Робота виконується на такому обладнанні:
• персональний комп'ютер Pentium з мікропроцесором частотою понад 160 МГц, накопичувачем на гнучких дисках високої щільності, жорстким диском та накопичувачем на компакт-дисках;
• пам'ять комп'ютера: оперативна пам'ять понад 32 Мбайт, на жорсткому диску понад 1,5 Гбайт вільного простору;
• операційна система Windows XP.
2.3 Підготовка до виконання роботи
Під час підготовки до лабораторної роботи студентам пропонується:
• повторити відповідний лекційний матеріал і вправи, котрі виконувалися на практичних заняттях;
• засвоїти порядок виконання лабораторної роботи відповідно до цих методичних вказівок, підготувати на робочому місці необхідне обладнання: комп'ютер, дискету, компакт-диск, олівець, аркуш паперу.
2.4 Методичні поради щодо виконання лабораторної роботи
Процес проектування компонентів застосування бази даних передбачає:
- створення теки (каталога, папки) на жорсткому диску ПК для збереження всіх компонентів бази даних застосування;
- створення нових таблиць зазначеним методом;
- створення схеми даних як сукупності взаємозв'язаних таблиць;
- вилучення таблиць з серверної бази даних засобами SQL;
- добавлення полів в таблиці серверної бази даних засобами SQL.
Завдання 1. Запустити генератор команд SQL для створення запитів до об’єктів бази даних засобами MS Access SQL.
Порядок виконання завдання 1:
1. Запустити СКБД Access. У діалоговому вікні MS Access вибрати перемикач Новая база данных і клацнути на кнопці ОК. Відкриється диалогове вікно Файл нової бази даних.
2. В список Папка записати ім’я папки, в якій розміщуватиметься створювана БД, а в список Ім’я файла записати ім’я файла створюваної БД. Клацнути на кнопці Создать, відкриється вікно «Имя_файла_БД»: база данных, в якому надається ім’я створюваній БД.
3. Для запуска Конструктора запитів слід перейти у вікно об’єктів БД на вкладинку «Запросы» і клацнути на кнопці Создать, відкриється діалогове вікно «Новый запрос». Вибрати опцію Конструктор і клацнути на кнопці ОК, відкриється бланк запиту і діалогове вікно «Добавление таблицы».
4. Закрити діалогове вікно «Добавление таблицы». Клацнути на кнопці SQL у верхньому лівому куті. Відкриється вікно «Запрос: запрос на выборку» з запрошенням select ввести потрібну інструкцію SQL. Після введення команди SQL слід виконати запит клацанням на кнопці «!» (знак оклику).
5. Результатом виконання запиту на створення таблиці буде піктограма (іконка) таблиці у вікні об’єктів бази даних на лінійці «Таблицы» з іменем, котре зазначено в операторі CREATE TABLE.
Завдання 2. Пригадати синтаксис інструкцій SQL для створення таблиць бази даних засобами MS Access SQL.
Порядок виконання завдання 2:
1. Для створення таблиці використовується інструкція SQL CREATE TABLE в форматі:
CREATE TABLE <ім’я таблиці> (<ім’я стовпця> <тип стовпця> <ширина стовпця> [NOT NULL] [UNIQUE | PRIMARY KEY], [FOREIGN KEY <(ім’я стовпця)> REFERENCES <ім’я базової таблиці> [<ім’я_стовпця>]], ...);
Обов’язковими параметрами інструкції CREATE TABLE є: ім’я таблиці і список стовпців; для кожного стовпця зазначається його ім’я і тип, а також можуть бути зазначені такі параметри:
- NOT NULL – елементи стовпця повинні мати певне значення (не NULL);
- UNIQUE – значення елементу стовпця повинне бути унікальним;
- PRIMARY KEY – стовпець є обмеженням – первинним ключем.
Конструкція FOREIGN KEY <(ім'я стовпця)> REFERENCES <ім'я базової таблиці> [<ім'я стовпця>] являє собою обмеження – зовнішній ключ, що вказує на ключ базової таблиці, на яку він посилається.
Завдання 3. Створити базу даних publications засобами MS Access SQL.
Порядок виконання завдання 3:
CREATE TABLE authors (au_id INT PRIMARY KEY,
author VARCHAR(25) NOT NULL);
CREATE TABLE publishers (pub_id INT PRIMARY KEY,
publisher VARCHAR(255) NOT NULL, url VARCHAR(255));
CREATE TABLE titles (title_id INT PRIMARY KEY,
title VARCHAR(255) NOT NULL, yearpub INT, pub_id INT,
FOREIGN KEY (pub_id) REFERENCES publishers(pub_id));
CREATE TABLE titleautors (au_id INT REFERENCES authors(au_id),
title_id INT, FOREIGN KEY (title_id) REFERENCES titles(title_id));
CREATE TABLE wwwsites (site_id INT PRIMARY KEY,
site VARCHAR(255) NOT NULL, url VARCHAR(255));
CREATE TABLE wwwsiteauthors (au_id INT, site_id INT,
FOREIGN KEY (au_id) REFERENCES authors(au_id),
FOREIGN KEY (site_id) REFERENCES wwwsites(site_id));
Після виконання запитів на розробку таблиць створити Схему даних в Access, яка повинна мати вигляд як на рисунку 1.
Завдання 4. Перевірка адекватності даних за допомогою обмежень.
При створенні таблиць бази даних і полів можна вказати обмеження щодо того, які дані можуть вводитися в поле. Необхідність такої перевірки полягає в тому, що користувач бази даних може випадково ввести одне і те ж значення двічі. При цьому порушиться унікальність первинного ключа, що може привести до порушення цілісності даних і до неправильних результатів запитів, залежних від унікальності первинного ключа.
Рисунок 1 – Схема бази даних publications
Виявлення пошкоджених даних може виявитися непростою справою, особливо для бази даних з великим числом записів. Краще не допускати порушення цілісності даних, вибравши відповідне обмеження при створенні таблиці, завдяки якому стовпець завжди міститиме унікальні значення. При спробі додати в такий стовпець два ідентичні значення, база даних повідомить про помилку і не допустить виконання таких дій.
Захист цілісності даних з використанням СКБД дозволяє організувати такі обмеження:
• NOT NULL – обмеження відсутності даних;
• UNIQUE – обмеження унікальності даних;
• CHECK – перевірка умови при введенні даних;
• PRIMARY KEY – первинні ключі і обмеження;
• FOREIGN KEY – зовнішні ключі і обмеження.
Первинні ключі PRIMARY KEY – це один або декілька стовпців таблиці даних, які виступають у вигляді обмежень і забезпечують зв'язок між таблицями. Серед всіх обмежень PRIMARY KEY є найбільш важливим і часто використовуваним обмеженням. Згідно першій нормальній формі кожна таблиця даних повинна мати первинний ключ. Для таблиці authors бази даних publications первинним ключем може бути стовпець au_id, яке забезпечує зв'язок з таблицями titleauhtors і wwwsiteauthors.
Щоб зв'язок між таблицями працював, первинний ключ повинен уні-кальним чином ідентифікувати записи, а значить, для нього допускаються тільки унікальні значення і не допускається NULL. По суті, обмеження PRIMARY KEY є комбінацією двох обмежень UNIQUE і NOT NULL. В таблиці може існувати декілька обмежень UNIQUE або CHECK, але допускається тільки одне обмеження PRIMARY KEY на таблицю.
Створення обмеження PRIMARY KEY відбувається при формуванні таблиці програмним кодом SQL. Задавати первинний ключ можна одним стовпцем або кількома стовпцями. Пропозиція про створення первинного ключа на основі одного стовпця розміщується в кінці визначення стовпця:
CREATE TABLE authors (au_id INT PRIMARY KEY
author VARCHAR(25) NOT NULL);
Первинний ключ на основі двох стовпців може створюватися опцією PRIMARY KEY з використанням слова CONSTRAINT (обмеження), яке записується після визначення всіх стовпців таблиці:
CREATE TABLE titls (au_id INT NOT NULL, site_id NOT NULL
CONSTRAINT booking PRIMARY KEY (au_id, site_id);
Завдання 5. Вилучити таблицю з бази даних publications інструкцією SQL.
Порядок виконання завдання 5:
1. Відкрити базу даних publicatIons і вилучити з неї таблицю titles або authors, скориставшись інструкцією:
DROP TABLE <ім’я_таблиці>
Приклади: DROP TABLE titles або DROP TABLE authors
Завдання 6. Модифікувати таблицю даних authors бази даних publications.
Порядок виконання завдання 6:
Модифікувати структуру таблиці серверної бази даних можна за допомогою інструкції ALTER TABLE. Ситуації змінення таблиці і відповідний синтаксис інструкцій ALTER TABLE може бути такий:
1. Формат інструкції Добавити стовпці:
ALTER TABLE <ім’я_таблиці> ADD COLUMN <ім’я_стовпця>
<тип_стовпця>[NOT NULL][UNIQUE | PRIMARY KEY] ,...;
Приклад 1:
ALTER TABLE authors ADD COLUMN salary int, married text;
2. Формат інструкції Вилучити стовпці:
ALTER TABLE <ім’я_таблиці> DROP COLUMN <ім’я_столбця>,...;
Приклад 2:
ALTER TABLE authors DROP COLUMN author, married;
3. Формат інструкції Модифікувати тип даних в стовпцях:
ALTER TABLE <ім’я_таблиці> ALTER COLUMN <ім’я_стовпця> <тип_стовпця> [NOT NULL] [UNIQUE | PRIMARY KEY],...;
Приклад 3:
ALTER TABLE authors ALTER COLUMN au_id int, married int;
4. Формат інструкції Добавити первинний ключ:
ALTER TABLE ім’я_таблиці_в_яку_встановлюється_первинний_ключ
ADD CONSTRAINT ім’я_обмеження_тобто_первинного_ключа
PRIMARY KEY (ім’я_стовпця_що_задає_первинний_ключ);
Приклад 4:
ALTER TABLE Клієнти ADD PRIMARY KEY (КодКлієнта);
ALTER TABLE publishers ADD CONSTRAINT aa PRIMARY KEY (pub_id);
5. Формат інструкції Вилучити первинний ключ:
ALTER TABLE ім’я_таблиці_в_яку_встановлюється_первинний_ключ
DROP CONSTRAINT ім’я_обмеження_тобто_первинного_ключа
PRIMARY KEY (ім’я_стовпця_що_задає_первинний_ключ);
Приклад 5:
ALTER TABLE Клієнти DROP PRIMARY KEY (КодКлієнта);
ALTER TABLE publishers DROP CONSTRAINT bb PRIMARY KEY
(pub_id );
6. Формат інструкції Визначити зовнішній ключ:
ALTER TABLE ім’я_таблиці_для_добавлення_зовнішнього_ключа
ADD CONSTRAINT ім’я_обмеження_у_вигляді_зовнішнього_ключа
FOREIGN KEY (ім’я_стовпця_що_задає_зовнішній_ключ)
REFERENCES ім’я_базової_таблиці (ім’я_столбца_для_посилання);
Зовнішні ключі FOREIGN KEY вказують зв’язки стовпця поточної таблиці з стовпцями інших таблиць через посилання на їх первинні ключі.
Приклад 6:
ALTER TABLE titles ADD FOREIGN KEY (pub_id ) REFERENCES publishers (pub_iid);
ALTER TABLE authors ADD CONSTRAINT A1 FOREIGN KEY (author)
REFERENCES spr_authors (author);
Завдання 7. Добавити до існуючої бази даних publications нову таблицю spr_authors (довідки про авторів).
Порядок виконання завдання 7:
Щоб добавити таблицю spr_authorsдо існуючої бази даних publications, слід створити цю таблицю і встановити зв'язок між нею і однією з таблиць бази даних, призначивши зовнішнім ключем поле зв'язної таблиці:
1. Створити нову таблицю-довідник spr_authors для поля author таблиці authors існуючої бази даних можна інструкцією:
CREATE TABLE spr_authors (author VARCHAR(25) PRIMARY KEY);
2. Модифікувати тип стовпця – змінити властивість поля author таблиці authors, призначивши йому статус “зовнішнього ключа”інструкцією:
ALTER TABLE authors ADD CONSTRAINT A1 FOREIGN KEY (author) REFERENCES spr_authors (author_spr);
Після створення таблиці-довідника і визначення зовнішнього ключа “Схема даних” поповниться таблицею spr_authors як показано на рис. 2.
Рисунок 2 – Таблиця-довідник spr_authors в базі даних publications
Завдання 1 для самостійної роботи. Створити структуру бази даних POST_IZDEL (POST_VYROB – поставка виробів) з трьох таблиць IZDELIE (VYROBY – вироби), ZAKAZ (ZAMOV – замовлення), POSTAVKA (поставка), до яких слід добавити дві таблиці-довідника NAIMENOV (NAIMENUV – найменування) та ZAKAZCHIK (ZAMOVNYK – замовник).
Порядок виконання завдання 1:
1. Скласти інструкцію CREATE TABLE для створення таблиці IZDELIE (VYROBY) згідно заданим найменуванням полів та заданим типам даних:
IZDELIE = (kod_modeli int, naimenov char (50), harakteristika char (80), cena money) + PK - kod_modeli, де PK - первинний та FK - зовнішній ключі.
CREATE TABLE izdelie (kod_modeli int PRIMARY KEY, naimenov
char (50), harakteristika char (80), cena money);
2. Скласти інструкцію для створення таблиці ZAKAZ (ZAMOV) згідно заданим найменуванням полів і заданим типам даних:
ZAKAZ = (nomer_zakaza int, zakazchik char (50), adres char (50), data_zakaza datetime) + PK - nomer_zakaza.
CREATE TABLE zakaz (nomer_zakaza int PRIMARY KEY, zakazchik
char (50), adres char (50), data_zakaza datetime);
3. Скласти інструкцію для створення таблиці POSTAVKA згідно заданим найменуванням полів і заданим типам даних:
POSTAVKA = (nomer_zakaza int, kod_modeli int, kolichestvo int) + PK - nomer_zakaza, kod_modeli + FK - nomer_zakaza для таблиці ZAKAZ та kod_modeli для таблиці IZDELIE.
CREATE TABLE postavka (nomer_zakaza int PRIMARY KEY,
kod_modeli int PRIMARY KEY, kolichestvo int, FOREIGN KEY
(nomer_zakaza) REFERENCES zakaz (nomer_zakaza), FOREIGN
KEY (kod_modeli) REFERENCES izdelie (kod_modeli));
4. Створити зв’язки між таблицями izdelie → postavka (1:n) та zakaz → postavka (1:n) такими діями:
1) створити таблиці-довідники інструкцією CREATE TABLE;
2) добавити обмеження зовнішнього ключа до зв’язаного поля зв’язаної таблиці за допомогою ALTER TABLE;
3) для таблиць-довідників NAIMENOV та ZAKAZCHIK зв’язними таблицями вважати таблиці IZDELIE та ZAKAZ відповідно. Зв’язки між таблицями встановлюються по полям naimenov та zakazchik з відповідних таблиць;
4) створити такі таблиці-довідники:
NAIMENOV (naimenov (char (50)) + PK (naimenov);
ZAKAZCHIK (zakazchik (char (50)) + PK (zakazchik).
5. Перевірте правильність виконаних дій і побудуйте схему даних:
CREATE TABLE izdelie (kod_modeli int PRIMARY KEY, naimenov char (50), harakteristika char (80), cena money);
CREATE TABLE zakaz (nomer_zakaza int PRIMARY KEY, zakazchik char (50), adres char (50), data_zakaza datetime );
CREATE TABLE postavka (nomer_zakaza int PRIMARY KEY,
kod_modeli int PRIMARY KEY, kolichestvo int,
FOREIGN KEY (nomer_zakaza) REFERENCES zakaz (nomer_zakaza),
FOREIGN KEY (kod_modeli) REFERENCES izdelie (kod_modeli));
Завдання 2 для самостійної роботи. Створити базу даних торгового підприємства на мові SQL.
Порядок виконання завдання 2:
1. Встановити обмеження цілісності та обмежуючі посилочні дії для таблиць даних CUSTOMER (покупці), STOCK (запаси), ORDER (замовлення) та ITEM (товари). Встановити первинні та зовнішні ключі, а також обмеження значень та цілісності такими командами:
CREATE TABLE CUSTOMER (id_num INT PRIMARY KEY,
companyname VARCHAR(20), lastname VARCHAR(20) NOT NULL,
firstname VARCHAR(20) NOT NULL, city VARCHAR(20));
CREATE TABLE STOCK (id_num INT PRIMARY KEY,
unitprice INT NOT NULL, onhand INT, reorder INT,
description VARCHAR (50));
CREATE TABLE ORDER (id_num INT PRIMARY KEY,
customerid INT NOT NULL, orderdate DATE NOT NULL, shipdate DATE,
paiddate DATE, FOREIGN KEY(customerid) REFERENCES customer);
CREATE TABLE ITEM (id_num INT, stockid INT NOT NULL,
quantity INT NOT NULL, total INT, PRIMARY KEY(id_num, orderid),
FOREIGN KEY(orderid) REFERENCES orders,
FOREIGN KEY(stockid) REFERENCES stock);