БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ © К.Ю. Поляков, 2007 Переклад В.Семенюка Інформаційнй системи Бази даних (БД) Реляційній БД Робота з таблицями Проектування таблиць Форми Макроси Запити Звіти

БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ Тема 1. Інформаційні системи

3 Означення База даних (БД) – це сховище даних про деяку предметну область, організоване у вигляді спеціальної структури. Важливо: дані про деяку область (не про все) впорядковані Система керування базою даних (СУБД) – це програмне забезпечення для роботи з БД. Функції: пошук інформації в БД виконання нескладних розрахунків виведення звітів на друк редагування БД Інформаційна система – це БД + СУБД.

4 Типи інформаційних систем локальні ІС БД і СУБД знаходяться на одному комп’ютерi. файл-сервернi БД знаходиться на сервері мережі (файловому сервері), а СУБД на комп’ютерi користувача. клієнт-серверні БД і основна СУБД знаходяться на сервері, СУБД на робочій станції посилає запит і виводить на екрані результат.

5 Локальні ІС автономність (незалежність) з БД працює тільки одна людина складно поновлювати при великій кількості користувачів практично неможливо «стикувати» зміни, що вносяться декільками користувачами

6 Файл-сервернi IС декілька чоловік працюють з одною базою основна робота виконується робочою станцією (РС), вони повинні бути потужними для пошуку рядка на РС копіюється вся БД – навантаження на мережу слабкий захист від взлому (тільки на РС) проблеми при одночасній зміні з різних РС

7 Клієнт-серверні ІС СУБД-клієнт СУБД-клієнт СУБД-клієнт основну роботу виконує сервер, робочі станції можуть бути малопотужні простіша модернізація (тільки сервер) по мережі йдуть тільки потрібні дані захист і права доступу ставляться на сервері (складно взломати) розділені доступи (черга завдань) запит на SQL відповідь SQL (Structured Query Language) – мова структурних запитів складність налаштування висока вартість ПЗ (тисячі $)

БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ Тема 2. Бази даних

9 Типи баз даних табличні БД дані у вигляді одної таблиці мережеві БД набір вузлів, в яких кожний може бути зв’язаний з кожним. iєрархічні БД у вигляді багаторівневої структури реляційні БД (99,9%) набір взаємозв’язаних таблиць

10 Табличнi БД Модель – картотека Приклади: записна книжка каталог в бібліотеці сама проста структура всі інші типи БД використовують таблиці в багатьох випадках – дублювання даних:

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

12 Табличні БД Кількість полів визначається розробником і не може змінюватися користувачем. Будь-яке поле повинне мати унікальне ім’я. Поля можуть мати різний тип: рядок символів (довжиною до 255 символів) дійсне число (з дробовою частиною) ціле число грошова сума дата, час, дата і час логічне поле (істинно або хибно, так або ні) багаторядковий текст (МЕМО) рисунок, звук або інший об’єкт (об’єкт OLE) Поля можуть бути обов’язковими для заповнення або ні. Таблиця може містити скільки завгодно записів (ця кількість обмежена тільки об’ємом диска); записи можнa додавати, вилучати, редагувати, сортувати, шукати.

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

14 Ієрархічна БД Ієрархічна БД – це набір даних у вигляді багаторівневої структури. Прайс-лист: Продавець (рівень 1) Товар (рівень 2) Модель (рівень 4) Ціна (рівень 5) Виробник (рівень 3) $306 $312 S93 X93B Sony Phillips Samsung Монітори Принтери Кей

15 Ієрархічна БД Приведені до табличної форми: дублювання даних при зміні адреси фірми потрібно змінювати його у всіх рядках немає захисту від помилок введення оператора (Кей – Key), краще було б вибирати із списку

БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ Тема 3. Реляційні бази даних

17 Реляційні БД 1970-і р. Е. Кодд, англ. relation – відношення. Реляційна база даних – це набір простих таблиць, між якими встановлені зв’язки (відношення) за допомогою числових кодів.

18 Реляційні БД немає дублювання інформації; при зміні адреси фірми, достатньо змінити її тільки в таблиці Продавці; захист від неправильного введення: можна вибрати тільки фірму, яка ще раніше уведена в таблицю Продавці; механізм транзакцій: будь-які зміни вносяться в базу тільки тоді, коли вони повністю завершені. складність структури (не більше 40-50 таблиць); при пошуку потрібно звертатися до декількох таблиць; потрібно підтримувати цілісність: при вилучені фірми продавця потрібно вилучати всі зв’язані записи з всіх таблиць (в СУБД – автоматично, каскадне вилучення).

19 Зв’язки мiж таблицями Один до одного («1-1») – один запис в першій таблиці відповідає рівно один запис в другій. Застосування: виділення даних, які часто використовуються. 1 1 Один до багатьох («1- ») – один запис в першій таблиці відповідає скільки завгодно записів у другій. 1  товари прайс-лист

20 Зв’язок між таблицями Багато до багатьох (« - ») – один запис в першій таблиці відповідає скільки завгодно записів у другій, і навпаки. Реалізація – через третю таблицю і два зв’язки «1-». розклад учителі предмети     1 1

21 Нормалізація бази даних Нормалізація – це розробка такої структури БД, в якій немає надлишкових даних і зв’язків. Основнi принципи: Будь-яке поле повинне бути неподільним. Не повинно бути полів, які позначають різні види одного і того ж, наприклад, товару.  1

22 Нормалізація бази даних Основні принципи: Будь-яке поле повинне залежати тільки від ключа (ключ – це поле або комбінація полів, що однозначно визначає запис). Не повинно бути полів, які можуть бути знайдені за допомогою решти. товари залежить не тільки від назви товару! прайс-лист

23 Пошук в базах даних Лінійний пошук – це перебір всіх записів до тих пір, поки не буде знайдена потрібна. Іванків? 1024 порівнянь! дані не потрібно попередньо готувати низька швидкість пошуку

24 Двійковий пошук Розділити область пошуку на дві рівні частини. Визначити, в якій половині знаходиться потрібний об’єкт. Перейти до кроку 1 для цієї половини. Повторювати кроки 1-3 поки об’єкт не буде «зловлений».

25 Пошук в базах даних Двійковий пошук в БД – вимагає попереднього сортування. Іванів? записи потрібно відсортувати по потрібному полю; можна використовувати тільки для одного поля. швидкий пошук 11 порівнянь!

26 Пошук по індексам Індекс – це допоміжна таблиця, яка призначена для швидкого пошуку в основній таблиці по вибранному стовпцю. Таблиця Індекси: по даті по товару по кількості

27 Пошук по індексам Алгоритм пошуку: двійковий пошук по індексу – знайти номера потрібних записів; вибрати ці записи по номерам з основної таблиці. індекси займають місце на диску; при зміні таблиці потрібно перелаштовувати всі індекси (в СУБД – автоматично). двійковий пошук по всім стовпцям, для яких побудовані індекси

БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ Тема 4. Бази даних Access. Робота з таблицями

29 Бази даних Access (Microsoft Office) Расширення: *.mdb, один файл Склад: таблиці; форми – діалогові вікна для введення і редагування даних; запити – звертання до бази даних для вибору потрібної інформації або зміни бази; звіти – документи для виведення на друк; макроси – засоби автоматизації роботи; модулі – додаткові процедури на мові Visual Basic.

30 Початок роботи Пуск – Програми – Microsoft Office – Microsoft Access 2003

31 Сервіс – Схема даних таблиця ключеве поле зв’язок «1-» Вилучити зв’язок: ЛКМ + Delete. Створити зв’язок: перетягнути потрібне поле на відповідне поле другої таблиці.

32 Робота з таблицями Таблиці – Замовлення – або подвійне клацання ЛКМ

33 Робота з таблицями поля записи текучий запис область виділення новий запис на 1-ий запис попередній запис номер текучого запису наступний запис останній запис перейти на новий запис всього записів текуче поле

34 Сортування і пошук Сортування по текучому полю (стовпцу): по зростанню (в алфавітному порядку) по спаданню (в зворотньому алфавітному порядку) Пошук і заміна: текуче поле або всі поля ціле, з будь-якою частиною, з початком все, вверх, вниз

35 Фільтрація Фільтрація – це відбір записів, що задовільняють деяку умову (фільтр). Решта записів тимчасово приховують, поки фільтр не буде знято. Фільтр по виділеному Клацнути в потрібній комірці або виділити частину текста. Клацнути по кнопці . Зняття фільтра .

36 Фільтрація Складні умови Одночасно (операція І) Розпочинається з ‘С’ нова умова, зв’язана через АБО Записи – Фільтр – Змінити фільтр Повний варіант: Записи – Фільтр – Розширенний фільтр можна переставляти стовпці можна виводити не всі стовпці можна встановлювати порядок сортування Точне співпадіння

37 Службові операції Сервіс – Службові програми: Перетворити базу даних у формат Access-97 у формат Access-2000 Стиснути і поновити базу даних (фізично вилучити зайві записи) Резервна копія бази даних Установка пароля: Сервіс – Захист – Задати пароль бази даних

БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ Тема 5. Проектування таблиць

39 Стоворення таблиць введення даних і назв полів (режим таблиці); конструктор – ручне налаштування; майстер таблиць – створення таблиць стандартних типів (Товари, Клієнти, Співробітники, …); імпорт таблиць – завантаження даних з інших джерел (БД інших форматів, Excel, текстові файли, …).

40 Створення таблиць Введення даних Майстер Імпорт з інших БД Access з БД інших форматів (*.db, *.dbf) з таблиць Excel з документів XML з текстових БД (CSV – comma separated values)

41 Конструктор таблиць перейти в конструктор перейти в режим таблиці

42 Конструктор таблиць тип поля (вибір зі списку) властивості текучого поля ключ текуче поле

43 Властивості полів Розмір поля: байт, ціле, дійсне, … Формат поля: як виводити на екран. Маска введення: шаблон (введення телефона). Підпис: як називається стовпець при виведенні на екран (можна використовувати дужки, знаки і т.д. («Населення, млн. чол.») Значення за замовчуванням: (вписується автоматично). Умова на значенням: захист від помилок введення («>18»). Повідомлення про помилку («Повернення повинне бути більше 18 років!») Обов’язкове поле (так/ні) Індексоване поле (так/ні)

44 Операції з полями зробити поле ключовим (відмінити…) додати поле вище текучого вилучити текуче поле (або всі виділені) індекси поле таблиці (вибір зі списку) Primary Key: ключ таблиці назва індекса

45 Підстановки Мета: зробити захист від помилок введення. Розв’язок: вибір зі списку = поле підстановки Варіанти: заданний список («так» або «ні», «Ч» або «Ж») з іншої таблиці (наприклад, вибір назви фірми) Як зробити:

46 Майстер підстановок

БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ Тема 6. Форми

48 Форми Форма – це діалогове вікно для перегляду і редагування даних введення нових записів керування ходом роботи (кнопки) виведення допоміжної інформації Створення форм: джерело даних

49 Створення форм Конструктор – повністю вручну. Майстер форм – режим «питання – відповіді». Автоформа в стовпець: Автоформа в рядок: Автоформа таблична: Діаграма:

50 Конструктор форм перейти в конструктор перейти в режим форми область виділення кнопки переходу

51 Конструктор форм вибраний елемент заголовок форми область даних примітка форми

52 Властивості форми Вікно властивостей Правка – Виділити форму область виділення Макет кнопки переходу Дані Джерело – таблиця або запит Фільтр – умова відбору записів Сортування Події (призначення макросів) натиск на клавіши, дія мишою, … відкриття, закриття, зменіння запису, …

53 Зв’язані елементи напис (текст можнa змінювати) поле (назва поля таблиці) маркери (зміна розмірів) Незалежне переміщення клацнути всередині, щоб змінити текст клацнути на рамці, щоб виділити елемент

54 Властивості елементів Панель форматування вибраний елемент назва шрифта розмір шрифта жирний, курсив, підкреслення вирівнювання колір фону Колір тексту колір рамки товщина рамки стиль Стиль оформлення: нормальний, припіднятий, притопленний, втиснутий, з тінью, рельєфний Виділення елементів: ЛКМ на рамці елемента + Shift = виділити декілька елементів

55 Властивість елементів Вікно властивостей Макет: розміри оформлення Дані: Дані – назва поля Маска введення – шаблон (для телефона) Значення за замовчуванням Умова на значення Повідомлення про помилку Подія дія користувача (клавіатура, миша) зміна даних ПКМ – Властивості

56 Додавання нових елементів Вивести (сховати) панель елементів вибір об’єктів майстер напис поле (інформація з бази даних) рисунок незалежний об’єкт (наприклад, діаграма Excel) елементи інтерфейса зв’язанний об’єкт (зберігається в БД) розрив сторінки підлегла форма інші елементи

БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ Тема 7. Макроси

58 Макроси Макрос – це набір макрокоманд. Макрокоманда описує дії, які потрібно виконувати: відкриття і закриття таблиць, звітів, форм виконання запитів установка значень полів пошук даних керування виведенням на екран друк даних повідомлення користувачу запуск інших програм Події (для запуску макроса) дія користувача (клавіатура, миша) внутрішні події (відкриття і закриття форми зміни запису і т.д.)

59 Простий макрос Конструктор

60 Макроси Відлагодження макроса перейти в конструктор Запуск – По крокам запустити на виконання Використання макросів AutoExec – виконується автоматично при відкриті бази призначення подіям вибрати ім’я макроса зi списку

61 Макроси Групи макросів Вигляд – Імена макросів Запуск Макрос1: QQQ Макрос1: MMM Макрос1 тільки перша група Умовне виконання Вигляд – Умова або

БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ Тема 8. Запити

63 Запити «Ну і запити у вас!» - сказала база даних і «зависла». (Фольклор) Запит – це звертання до СУБД для виконання яких-небудь операцій з даними. Типи запитів: вибірка створення таблиці поновлення (зміна даних) додавання записів вилучення записів …

64 Запит і фільтрація В запиті можна: відібрати інформацію з декількох зв’язаних таблиць використовувати складні умови відбору користувач може сам увести параметри (суму грошей) додати обчислювальні поля виконати підсумкові розрахунки

65 Створення запитів

66 Майстер запитів

67 Запуск запита

68 Конструктор перейти в конструктор перейти в режим таблиці

69 Конструктор таблиця (ПКМ – Додати таблицю) перетягнути ЛКМ всі поля по зростанню по спаданню відсутня фільтр

70 Робота з стовпцями виділити (ЛКМ) ПКМ: вирізати копіювати вставити властивості перетягнути (ЛКМ)

71 Умова відбору Співпадіння Нерівність Шаблон * будь-яка кількість довільних символів ? один будь-який символ # будь-яка цифра

72 Побудова виразів Forms – форми Reports – звіти Функції: 1) вмонтовані; 2) функції користувача (VB) Константи: пустий рядок, Істинно, Хибно Оператори: + - * / < > <= >= = <> Not And Or Xor Загальні вирази – час, дата, нумерація рядків ПКМ

73 Обчислювальні поля ввести Ціна*Кількість Вираз1: [Ціна]*[Кількість] замінити Вираз1 на Сума ввести Year(Дата) Вираз1: Year([Дата]) замінити Вираз1 на Рік або ПКМ - Побудувати

74 Запити з параметрами Задача: вводити числові дані для фільтра не в конструкторі, а при виконанні запитів. Приклад: «На яку суму Ви розраховуєте?» Будь-який невідомий рядок викликає запит

75 Підсумковий запит Конструктор Таблиця Підсумковий запит Групові операції Групування Групування Sum – сума

76 Підсумковий запит (майстер) ЛКМ

77 Перехрестний запит Таблиці Перехрестний запит

78 Перехрестний запит Конструктор: Запит – Перехрестний Заголовки рядків Заголовки стовпців Значення Майстер – не дозволяє вибрати інформацію з декількох таблиць (тільки через зайвий запит). 

79 Запит на поновлення (зміни) Задача: у всіх записах, що відносяться до 2007 року, змінити дату в полі Дата на сьогоднішне число. Розв’язок: запит, який відбирає всі потрібні записи резервна копія таблиці (Копіювати – Вставити) меню Запити – Поновлення в рядку Поновлення ввести нове значення поля: виконати запит Поновлення

80 Інші типи запитів Вилучення: запит на вибірку потрібних записів резервна копія таблиці Запит – Вилучення виконати запит Створення таблиці: Запит – Створення таблиці Додавання даних в підсумкову таблицю: Запит – Додавання Записи, що повторюються: Запити – Створити – Записи, що повторюються Записи без підлеглих: Запити – Створити – Записи без підлеглих

БАЗИ ДАНИХ. ІНФОРМАЦІЙНІ СИСТЕМИ Тема 9. Звіти

82 Створення звітів Звіт – це документ, що містить інформацію з бази даних і призначений для виведення на друк. Джерело: таблиця або запит

83 Автозвіти Автозвіт в стовпці Автозвіт в рядках

84 Режим перегляду звіту 2 x ЛКМ

85 Режим перегляду звіту – перейти в конструктор – друк – масштаб (переключення режима) – одна сторінка – дві сторінки – декілька сторінок – масштаб в процентах – закрити вікно перегляду – налаштування (поля, формат паперу)

86 Конструктор звітів Заголовок звіту – один раз на початку звіту. Верхній колонтитул – на початку кожної сторінки. Область даних – інформація з БД. Нижній колонтитул – в кінці кожної сторінки. Примітка звіту – один раз в кінці звіту. ="Страница " & [Page] & " из " & [Pages] =Now()

87 Редагування звітів – вивести (приховати) панель елементів – властивості вибранного елемента Так само, як в конструкторі форм: ЛКМ – виділити елемент

88 Звіти з групуванням групування по посадам загальна зарплата

89 Звіти з групуванням (крок I) Створення запитів, що включають всі дані: & - з’єднання символьних рядків поля таблиць

90 Звіти з групуванням (крок 2) Використання майстра звітів: Підсумкові дані

91 Звіти з групуванням (конструктор) Заголовок групи Примітка групи

92 Звіти з групуванням (конструктор) сортування і групування

93 Кінець