1 Вивчення метод і в збору та накопичення даних

ДЛЯ НАУКОВИХ ДОСЛІДЖЕНЬ ЗАСОБАМИ MYSQL / MS ACCESS

РОЗДІЛ 1 СТВОРЕННЯ СЕРВЕРНИХ БАЗ ДАНИХ МОВОЮ SQL

1.1 Мета роботи

Засвоїти покрокову технологію і придбати навички проектування таблиць даних для створення застосувань баз даних мовними засобами SQL в середовиші Microsoft Access.

1.2 Обладнання для виконання роботи

Робота виконується на такому обладнанні:

• персональний комп'ютер Pentium з мікропроцесором частотою понад 160 МГц, накопичувачем на гнучких дисках високої щільності, жорстким диском та накопичувачем на компакт-дисках;

• пам'ять комп'ютера: оперативна пам'ять понад 32 Мбайт, на жорсткому диску понад 1,5 Гбайт вільного простору;

• операційна система Windows XP.

1.3 Підготовка до виконання роботи

Під час підготовки до лабораторної роботи студентам пропонується:

• повторити відповідний лекційний матеріал і вправи, котрі виконувалися на практичних заняттях;

• засвоїти порядок виконання лабораторної роботи відповідно до цих методичних вказівок, підготувати на робочому місці необхідне обладнання: комп'ютер, дискету, компакт-диск, олівець, аркуш паперу.

1.4 Методичні поради щодо виконання лабораторної роботи

Процес проектування компонентів застосування бази даних передбачає створення таких об'єктів:

- теки (каталога, папки) на жорсткому диску ПК для збереження всіх компонентів бази даних застосування;

- нових таблиць зазначеним методом;

- створення схеми даних – сукупності взаємозв'язаних таблиць.

Завдання 1. Пригадати призначення бази даних, основні об’єкти бази даних і мови для написання баз даних в середовищі MS Access.

Порядок виконання завдання 1:

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

2. Робота з базою даних передбачаєвиконання таких дій: 1) створення таблиці БД; 2) введення даних; 3) редагування структури таблиці і даних; 4) пошук необхідної інформації в БД; 5) оформлення звітів.

3. У MS Access база даних є файлом з розширенням mdb, що містить дані у вигляді декількох таблиць, зазвичай зв'язаних реляційними зв'язками. Окрім таблиць, у файлі БД можуть бути форми, запити, сторінки, що забезпечують Web-доступ до даним, звіти, макроси і модулі.

4. Таблиця складається з рядків і стовпців. Рядки називаються записами. Запис містить інформацію про один елемент бази даних: про одну людину, одну книгу, один рейс і тому подібне. Запис складається з полів. Поле є мінімальною порцією інформації в записі, над яким визначені операції введення, виведення і перетворення. Поле визначається ім'ям і значенням, а характеризується типом і заданими властивостями. Як приклад, можна привести такі імена полів, типи полів і значення полів:

Ім'я поля Тип поля Значення поля

Номер Лічильник/Числовий 1

Прізвище Текстовий Боженко

Ім'я Текстовий Тетяна

SQL Числовий 3

VBA Числовий 4

Basic Числовий 5

Стипендія Грошевий/Числовий $120

Наук_труди Memo Текстовий файл

День_народження Дата/Час 22/04/87

Фотографія Поле об'єкту OLE Графічний файл

Завдання 2. Створити структуру таблиці клієнтської бази даних в режимі Конструктора засобами MS Access.

Порядок виконання завдання 2:

1. Запустити програму MS Access 2000: відкриється головне вікно системи керування базами даних MS Access для вибору режиму роботи системи.

2. Набрати в головному меню команду Файл | Создать  Новая база данных. Відкриється вікно Файл новой базы данных.

3. У вікні Файл новой базы данных вибрати папку для збереження створюваної БД, назвати файл бази даних УЧЕБА і натиснути клавішу СОЗДАТЬ. Відкриється вікно об’єктів бази даних УЧЕБА: база данных (рис. 1).

Рисунок 1 – Вікно об’єктів бази даних MS Access 2000

4. Створити структуру таблиці бази даних в MS Access у такий спосіб:

- вибрати режим створення структури таблиці, наприклад Создание таблицы в режиме конструктора, і двічі клацнути по відповідній закладинці: відкриється вікно конструктора таблиці Таблица1: таблица;

- ввести у вікно конструктора Таблица1: таблица імена полів, типи даних , описання полів, умови на значення вмісту полів, повідомлення про виникнення похибки і позначити ключове поле (рис. 2);

- закрити вікно конструктора таблиці (не переплутати з вікном бази даних) і зберегти створену структуру таблиці на диску під іменем ОЦЕНКИ.

Рисунок 2 – Вікно конструктора таблиці

5. Двічі клацнути по іконці таблиці ОЦЕНКИ у головному вікні бази даних, щоб відкрити цю таблицю для введення даних.

6. Ввести п’ять записів в таблицю ОЦЕНКИ. Для цього слід двічі клацнути на імені таблиці ОЦЕНКИ в головному вікні бази даних. Спробуйте в якесь поле навмисно ввести хибне значення оцінки. Закрити вікно з повідомленням про похибку і ввести правильні дані (рис. 3).

Рисунок 3 – Введення п’яти записів в таблицю ОЦЕНКИ

Завдання 3. Вивчити мовне середовище SQL для створення і ведення серверних баз даних.

Порядок виконання завдання 3:

1. Мова SQL орієнтована на створення та ведення запитів до баз даних. Стандарт SQL ANSI 92 реалізовано на різні програмні середовища і на різні комп’ютерні платформи. Всі запити до серверних баз даних в MS Access створюються мовою SQL (Structured Query Language – структурована мова запитів) в зміненому форматі у порівнянні зі стандартним варіантом, котрий називається SQL Microsoft Jet.

Для роботи з командами SQL слід увійти в редактор SQL, за допомогою якого здійснюється введення команд i виведення результатів виконання запитів SQL. Роль редактора SQL може виконувати утиліта Windows Interactive SQL (WISQL), локальний сервер InterBase системи Delphi Client/Server або Oracle SQL*Plus, що функціонують в стандарті ANSI/ISO SQL 92.

2. Стандартом ANSI/ISO SQL 92 затверджені тільки текстові та числові типи даних. До текстових типів даних належать типи CHAR та VARCHAR, що можуть зберігати рядки символів максимальної або довільної довжини відповідно. До стандартизованих числових типів даних належать INTEGER (цілі, без десяткової крапки), SMALLINT (цiлi, менші за розміром від INTEGER), DECIMAL (десяткові), FLOAT (з плаваючою крапкою), REAL (збігається з FLOAT, точність встановлена за замовчанням), DOUBLE PRECISION (збігається з REAL, має підвищену точність).

Нормуються тальки текстові та числові типи даних. Типи DATE та TIME використовуються за вимогами ринку, але стандартом ANSI/ISO вони не підтримуються. Це саме стосується типів LOGICAL, MEMO, OLE, MONEY та інші. Засіб реалізації цих типів пояснюється в документації до програмного забезпечення SQL-систем.

3. Основною командою мови SQL вважається команда SELECT відбору записів із таблиці, що має такий формат:

SELECT <список полів, що виводяться за запитом>

FROM <список таблиць, з яких виводяться дані>

INTO <ім’я таблиці з результатом запиту>

WHERE <умови (критерії) відбору даних з таблиць>

GROUP BY <умови групування записів в результаті запиту>

HAVING <умови для відбору групи даних>

ORDER BY <порядок виведення даних>

Деякі частини команди в запиті можуть бути відсутні. Якщо в запиті використовується декілька таблиць, то для уточнення імені поля перед ним ставиться ім’я відповідної таблиці в прямокутних дужках з крапкою в якості розмежувача (оскільки різні таблиці можуть мати однакові імена полів). Мовою SQL можна створювати нові таблиці, модифікувати запити на вибірку записів, на обчислення в таблицях тощо.

Завдання 4. Активізувати генератор SQL-команд для створення запитів.

Порядок виконання завдання 4:

1. Відкрити вікно об’єктів бази даних на ім’я УЧЕБА: база данных.

2. Активізувати лінійку запитів і клацнути на кнопці Создать, що розміщується на панелі інструментів. Відкриється вікно Новый запрос (рис. 4).

Рисунок 4 – Підготовка до створення запиту в режимі конструктора

3. У вікні Новый запрос вибрати режим створення запиту Конструктор і цлацнути на кнопці ОК. Після цього відкриється вікно Запрос1: запрос на выборку та вікно Добавление таблицы для створення запиту (рис. 5).

4. Закрити вікно Добавление таблицы кнопкою Закрыть. На панелі інструментів у верхньому лівому куті з’являється позиція меню SQL.

Рисунок 5 – Підготовка до відкриття генератора команд SQL

6. Клацнути на позиції панелі інструментів SQL (або вибрати команду Вид | Режим SQL), а після цього вибрати із випадаючого меню SQL опцію Режим SQL. На екрані з’явиться вікно генератора команд SQL (рис. 6) з назвою Запрос1: запрос на выборку і запрошенням select до введення команди SQL. Саме в це вікно, на місце, де розташоване слово SELECT, слід вводити потрібну інструкцію SQL.

Рисунок 6 – Вікно Запрос1: запрос на выборку з командним

рядком і запрошенням SELECT до введення команди

7. Інструкція SQL може починатися зі слів SELECT, CREATE, INSERT, UPDATE, DELETE, ALTER, DROP і т. д. Завершується інструкція SQL символом детермінатора у вигляді крапки з комою «;». Виконується записана у вікні Запрос1: запрос на выборку інструкція SQL командою Выполнить або натисненням на кнопку запуску ! на панелі інструментів.

8. Ввести в редактор SQL найпростіший запит такою інструкцією:

SELECT * FROM ОЦЕНКИ;

яка пропонує вивести на екран «*» – усі записи усіх стовпців таблиці ОЦЕНКИ.

9. Запустити інструкцію SQL натисненням клавіші Enter або клацанням на кнопці запуску ! . На екран виводиться результативна таблиця запиту. При спробі закрити таблицю задається питання: Чи зберігти внесені зміни структури об’єкта «запиту ‘Запрос1’»? Слід відповісти Да. На екран виводиться вікно з пропозицією надати ім’я щойно створеному запиту. Після надання запиту імені Успеваемость, слід клацнути на кнопці ОК. Таким результатом може бути таблиця, приведена на рис. 7.

Рисунок 7 – Результати запиту, створеного мовою SQL

Завдання 5. Створити структуру таблиці СТИПЕНДИЯ для серверної бази даних УЧЕБА.

Порядок виконання завдання 5:

1. Вивчити формат інструкції CREATE TABLE для створення структури таблиці бази даних засобами SQL:

CREATE TABLE <ім’я таблиці> (<ім’я_стовпця> <тип_стовпця> <ширина_-стовпця> [NOT NULL] [UNIQUE | PRIMARY KEY], [FOREIGN KEY <(ім'я стовпця)> REFERENCES <ім'я_базової_таблиці> [<ім'я_стовпця>]],...);

Обов’язковими параметрами інструкції CREATE TABLE вважаються:

- ім’я таблиці і список імен полів,

- список типів полів і завершувач команди,

- а також можуть бути зазначені необов’язкові параметри:

- NOT NULL – елементи стовпця повинні мати ненульове значення;

- UNIQUE – значення кожного елемента стовпця має бути унікальним;

- PRIMARY KEY – стовпець є первинним ключем;

- FOREIGN KEY <(ім'я_стовпця)> REFERENCES <ім'я_базової табли-ці> [<ім'я_стовпця>] – ця конструкція визначає зовнішній ключ таблиці по відношенню до тої таблиці, на яку посилається ключ базової таблиці.

Увага! Інструкція CREATE TABLE в СКБД Access, не підтримує пропозиції DEFAULT (значення за замовчанням) і CHECK (перевірити значення).

2 Активізувати генератор команд SQL такими діями: 1) у вікні об’єктів бази даних вибрати закладинку Запросы; 2) вибрати команду Создать запрос с помощью конструктора; 3) у вікні Запрос1: запрос на выборку з інформаційним повідомленням Добавити таблицю активізувати кнопку Закрыть; 4) виконати команду Вид  Режим SQL.

3. У вікно генератора команд SQL з запрошенням select ввести інструкцію для створення таблиці СТИПЕНДИЯ (рис. 8) у форматі:

CREATE TABLE СТИПЕНДИЯ (Ном_студ INT, Фамилия Text (20), Имя Text (20), Рейтинг Varchar (5), Стипендия Varchar (5), Надбавка Varchar (5));

Рисунок 8 – Підтвердження про створення таблиці СТИПЕНДИЯ

4. Активізувати кнопку запуску команди ! і отримати підтвердження Таблица Стипендия уже существует (рис. 8). Активізувати закладинку ТАБЛИЦЫ у вікні об’єктів бази даних і впевнитися в наявності щойно створеної таблиці.

5. Записати в редактор SQL інструкцію Select * from СТИПЕНДИЯ; і виконати її. Створеному запиту надати ім’я СТИПЕНДИАТЫ (рис. 9).

Рисунок 9 – Запит СТИПЕНДИАТЫ до таблиці СТИПЕНДИИ

Завдання 6. Заповнити даними (записами) таблицю СТИПЕНДИЯ серверної бази даних УЧЕБА.

Порядок виконання завдання 6:

1. Вивчити формати введення записів в таблицю через командне вікно на запрошення select. Інструкція INSERT INTO введення даних в таблицю може мати розширений або стислий формати. Розширений формат інструкції введення даних в таблицю має такий вигляд:

INSERT INTO <ім’я_таблиці> (<ім’я_стовпця_1>, …, < ім’я_стовпця_N>) VALUES (<значення_стовпця_1>, …, <значення_стовпця_N>);

а стислий формат інструкції введення даних в таблицю має такий вигляд:

INSERT INTO <ім’я_таблиці> VALUES (<значення_стовпця_1>, …, <значення_стовпця_N>);

2. Ввести записи в таблицю СТИПЕНДИЯ інструкцією INSERT INTO у повному форматі:

INSERT INTO СТИПЕНДИЯ (Ном_студ, Фамилия, Имя, Рейтинг,

Стипендия, Надбавка) VALUES (1, 'Даренко', 'Данило', 97, 1000, 250);

INSERT INTO СТИПЕНДИЯ (Ном_студ, Фамилия, Имя, Рейтинг,

Стипендия, Надбавка) VALUES (1, 'Шевчук', 'Микола', 90, 7500, 250);

3. Виконати цю інструкцію подвійним клацанням по кнопці ! (знак оклику) на панелі інструментів. На екрані з’явиться вікно Введите значение параметра. Система очікує підтвердження параметра ‘Шевчук’. Введіть в рядок текстового редактора параметр Шевчук і клацніть на кнопці ОК. Аналогічно слід підтвердити правильність введення інших параметрів.

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

Рисунок 10 – Діалогове вікно підтвердження числа записів

5. Відкрийте таблицю СТИПЕНДИЯ і впевніться в правильному введенні записів даних.

Завдання 7. Створити дві взаємозв‘язані таблиці мовою SQL.

Порядок виконання завдання 7:

1. Створити першу таблицю з первинним ключем SQL-інструкцією CREATE TABLE в форматі:

CREATE TABLE <ім’я таблиці> (<ім’я_стовпця_1> <тип_стовпця_1> <ширина_стовпця_1> primary key, …, <ім’я_стовпця_N> <тип_стовпця_N> <ширина_стовпця_N>);

2. Створити першу таблицю табл1 SQL-інструкцією:

CREATE TABLE табл1 (id_num int primary key, nnn1 varchar (5));

3. Cтворити другу таблицю з первинним ключем і взаємозв‘язком з першою таблицею SQL-інструкцією CREATE TABLE в форматі:

CREATE TABLE <ім’я другої таблиці> (<ім’я_стовпця_1> <тип_стовпця_1> <ширина_стовпця_1> primary key, …, <ім’я_стовпця_N> <тип_стовпця_N> <ширина_стовпця_N>), foreign key (ім’я_стовпця другої таблиці, через який здійснюється зв‘язок з першою таблицею) references <ім’я першої таблиці> (ім’я_стовпця першої таблиці, через який здійснюється зв‘язок з другою таблицею);

4. Створити другу таблицю табл2 SQL-інструкцією:

CREATE TABLE табл2 (id_num int primary key, nnn2 varchar (5), foreign key (id_num) references табл1 (id_num));

5. Активізуйте кнопку СХЕМА ДАННЫХ і перевірте існування зв‘язку між таблицями. При відсутності у вікні СХЕМА ДАННЫХ таблиць і зв‘язку між ними, виберіть позицію вспливаючого меню ОТОБРАЗИТЬ ВСЕ. На екрані з’явиться вікно схеми даних, подібне до рис. 11.

Рисунок 11 – Автоматичне створення схеми даних

Завдання 1 для самостійної роботи на тему:

«Створити дві взаємозв‘язані таблиці з встановленими первинними

ключами за допомогою мовних засобів SQL такими діями»:

Процедура 1: відкрити редактор інструкцій SQL такими діями:

1) вибрати кнопку ПУСК на робочому столі;

2) в меню MS ACCESS вибрати команду СОЗДАНИЕ: Новая база данных;

3) надати ім‘я створюваній базі даних МЫ;

4) активізувати об‘єкт ЗАПРОС і вибрати режим Конструктор;

5) у вікні Выбрать таблицу активізувати кнопку Закрыть;

6) на панелі інструментів активізувати кнопку SQL.

На екран виводиться редактор інструкцій SQL з запрошенням SELECT;

Процедура 2: створити першу таблицю Наши оценки такими діями:

1) на місце, де розташоване запрошення SELECT, ввести таку інструкцію для створення першої таблиці з первинним ключем:

CREATE TABLE [Наши оценки] (Номер INT primary key, Фамилия Text (20), Имя Text (20), SQL Varchar (5), VBA Varchar (5), BASIC Varchar (5));

2) виконати цю інструкцію подвійним клацанням по кнопці ! (знак оклику) панелі інструментів.

На екран виводиться вікно з інформацією Таблица [Наши оценки] уже существует, тобто вона вже створена. Пропонується перевірити і впевнитись, що таблиця Наши оценки вже створена.

Процедура 3: створити другу таблицю Наши адреса такими діями:

1) в редактор SQL ввести таку інструкцію для створення другої таблиці з первинним ключем і взаємозв‘язком з першою таблицею:

CREATE TABLE [Наши адреса] (Номер INT primary key, Фамилия Text (20), Имя Text (20), Город Varchar (20), Адрес Varchar (20), Телефон Varchar (11), foreign key (Номер) references [Наши оценки] (Номер));

2) виконати цю інструкцію подвійним клацанням по кнопці ! (знак оклику) панелі інструментів.

На екран виводиться вікно повідомлення з інформацією Таблица [Наши адреса] уже существует, тобто вона вже створена. Пропонується перевірити і впевнитись, що таблиця Наши адреса вже створена.

Процедура 4: активізувати кнопку СХЕМА ДАННЫХ на інструментальній панелі і впевнитися, що зв‘язок між таблицями вже встановлено (рис. 12).

Рис. 12. Встановлення зв‘язку між таблицями.

Завдання 2 для самостійної роботи на тему:

«Створити шість взаємозв‘язаних таблиць бази даних publications з

встановленими первинними ключами засобами SQL такими діями»:

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));

Після виконання запитів на створення таблиць "Схема даних" в середовищі Microsoft Access повинна мати вигляд, як на рисунку 13.

Рисунок 13 – Схема бази даних publications