Слайд 1Організація інформаційної бази систем опрацювання економічної інформації
Тема 4
Слайд 2План:
Передумови створення та основні переваги баз даних
Системи управління базами даних
Архітектурні
рішення, які використовують для реалізації багатокористувацьких СУБД
Представлення даних в базах
даних
Основні етапи проектування баз даних
Мова структурованих запитів SQL
Слайд 3Передумови створення та основні переваги баз даних
Співвідношення складності подання оброблюваних
даних і алгоритму обчислень визначає два класи задач:
обчислювальні задачі -
досить просте представлення даних і складний, багатоопераційний процес обчислень;
задачі обробки даних - простий алгоритм обробки даних і складне представлення оброблюваних даних.
Слайд 4Приклад 1.
Обчислити Y=X2+5X,
де X і Y - визначені числа, прості
змінні, які мають певний тип значень.
Приклад 2.
Обчислити S=a1+a2+...+aN.
Тут обробляється
не окреме число, а послідовність чисел - масив - сукупність елементів, з кожним з яких пов'язаний впорядкований набір цілих чисел, які називаються індексами.
Слайд 5Приклад 3. (нарахування заробітної плати)
співробітнику нараховується заробітна плата на основі
його окладу:
S=KoO/Kr,
де Kr – кільність робочих днів в місяці.
ніякі податки і відрахування
не враховуються.
Необхідні відомості про співробітника представлені в наступній картці НАРАХУВАННЯ:
Понять "проста змінна" і "масив" недостатньо, щоб представити відповідну картку.
Слайд 6Нові поняття:
Логічний запис - поіменована сукупність елементів даних (полів).
Для представлення
всього набору карток співробітників використовується поняття "Логічний файл"
Логічний файл -
сукупність всіх примірників записів заданого типу.
Приклад логічного файлу
НАРАХУВАННЯ:
Слайд 7Приклад 4. (Облік кадрового складу)
Тут обробляються відомості про співробітника, представлені
в картці СПІВРОБІТНИК:
Рішення задачі складається з наступних етапів:
введення
даних і занесення їх у пам'ять;
зчитування даних із пам'яті з метою коригування, додавання або видалення запису.
Слайд 8Приклад 5. (Облік економії фонду оплати праці (ФОП) у зв'язку
з хворобою співробітників).
Обробляються відомості, представлені записами ЕКОНОМІЯ ФОП:
за
формулою SN=KдвO/Kr.
Розглянемо типовий випадок, коли з-чі 3-5 функціонують в одній організації.
Недолік - інформація дублюється (FIO, O).
Усунути недолік можна, об'єднавши відповідні записи і створивши єдину інформаційну базу:
FIO D O G Ko M Kдв S SN
Слайд 9База даних — впорядкований набір логічно взаємопов'язаних даних, що використовуються
спільно та призначені для задоволення інформаційних потреб користувачів.
Головне завдання БД — гарантоване
збереження значних обсягів інформації (записів даних) та надання доступу до неї користувачеві або прикладній програмі.
БД складається з двох частин: збереженої інформації та системи керування нею.
Слайд 10Системи управління базами даних
СУБД - програмний комплекс підтримки інтегрованої сукупності
даних, призначений для створення, ведення та використання бази даних багатьма
користувачами (прикладними програмами).
Роль інтерфейсу між прикладними програмами і базою даних, що забезпечує їх незалежність, відіграє програмний комплекс - система управління базами даних (СУБД)
Слайд 11Банк даних - система мовних, алгоритмічних, програмних, технічних та організаційних
засобів підтримки інтегрованої сукупності даних, а також самі ці дані,
представлені у вигляді баз даних.
Слайд 12Основні функції системи управління базами даних
Визначення структури створюваної бази даних,
її ініціалізація і початкове введення даних.
Надання користувачам можливості маніпулювання даними
(вибірка даних, виконання обчислень, розробка інтерфейсу введення / виведення, візуалізація).
Забезпечення незалежності прикладних програм і даних.
Слайд 13Захист логічної цілісності бази даних
(перевіряється відповідність даних,
що вводяться їх типу, описаному при створенні структури. Система не
дозволить ввести символ у полі числового типу, не дозволить ввести неприпустиму дату і т.ін.)
Захист фізичної цілісності.
Транзакція - це одиниця дій, що проводиться з базою даних. До складу транзакції може входити декілька операторів зміни бази даних, але - або виконуються всі ці оператори, або не виконується жоден.
СУБД, крім ведення бази даних, веде також журнал транзакцій.
У журнал транзакцій заносяться всі транзакції - і зафіксовані, і ті що завершилися "відкатом".
Ведення журналу транзакцій спільно з створенням резервних копій бази даних дозволяє досягти високої надійності бази даних.
Слайд 14Управління повноваженнями доступу користувачів до бази даних.
Синхронізація роботи декількох користувачів.
Блокування
необхідні для того, щоб заборонити різним користувачам можливість одночасно працювати
з базою даних, оскільки це може призвести до помилок.
СУБД встановлює блокування на об'єкти, які використовує транзакція.
Існують різні типи блокувань - табличні, сторінкові, рядкові і інші, які відрізняються один від одного кількістю заблокованих записів .
Найчастіше використовується рядкове блокування - при зверненні транзакції до одного рядка блокується тільки цей рядок.
Слайд 15Процес внесення змін до бази даних складається з наступної послідовності
дій:
видається оператор початку транзакції
видається оператор зміни даних
СУБД аналізує
оператор і намагається встановити блокування, необхідні для його виконання, в разі успішного блокування оператор виконується, потім процес повторюється для наступного оператора транзакції.
Після успішного виконання всіх операторів в транзакції виконується оператор фіксації транзакції. СУБД фіксує зміни, зроблені транзакцією і знімає блокування.
У разі неуспіху виконання будь-якого з операторів транзакція “відкочується” , дані отримують колишні значення, блокування знімаються.
Слайд 16Управління ресурсами середовища зберігання.
При роботі в БД заносяться нові дані
(займається пам'ять) і видаляються дані (звільняється пам'ять). СУБД виділяє ресурси
пам'яті для нових даних, перерозподіляє звільнену пам'ять, організовує ведення черги запитів до пам'яті і т.ін.
Підтримка діяльності системного персоналу.
При експлуатації бази даних може виникати необхідність зміни параметрів СУБД, вибору нових методів доступу, зміни (у певних межах) структури збережених даних, а також виконання ряду інших загальносистемних дій.
СУБД надає можливість виконання цих та інших дій для підтримки діяльності БД обслуговуючому БД системному персоналу - адміністратору БД.
Слайд 17Архітектурні рішення, які використовують для реалізації багатокористувацьких СУБД
Централізована архітектура: база
даних, СУБД і прикладна програма розміщені на одному комп'ютері.
(перші
версії СУБД DB2, Oracle)
Слайд 182. Архітектура "файл—сервер"
(перші версії популярних так званих настільних СУБД, таких,
як dBase і Microsoft Access)
Слайд 19передбачає наявність комп'ютера, виділеного під файловий сервер, в якому знаходяться
ядро мережної ОС і файли, що централізовано зберігаються.
характерний колективний
доступ до спільної БД на файловому сервері.
від конкретного АРМу на сервер надходить запит, оброблення якого зумовлює передачу по мережі на АРМ всієї інформації запитуваного файла. Вибір записів, що задовольняють умови запиту, буде здійснений на самому АРМі засобами СУБД.
в момент передачі по мережі інформації файла доступ до нього інших АРМів блокується.
Слайд 20Недоліки даної архітектури:
При одночасному зверненні декількох користувачів до одних і
тих самих даних продуктивність роботи різко падає, бо необхідно дочекатися
поки користувач, що працює з даними, завершить свою роботу. В іншому випадку можливе затирання виправлень, зроблених одними користувачами, змінами інших користувачів.
Весь тягар обчислювального навантаження при доступі до БД лягає на додаток клієнта. Таким чином, неоптимально витрачаються ресурси клієнтського комп'ютера і мережі. В результаті зростає мережевий трафік і збільшуються вимоги до апаратних потужностей для користувача комп'ютера.
Слайд 213. Концепція "клієнт—сервер"
MS SQL Server, Oracle, Gupta, Informix, Sybase, DB2,
InterBase
Слайд 22в концепції "клієнт—сервер“ реалізується одночасний доступ багатьох користувачів до інтегрованої
БД.
Запит на оброблення даних посилається клієнтом (АРМом) по мережі
на сервер БД. На сервері здійснюються пошук даних і їх опрацювання засобами СУБД.
Оброблені дані передаються по мережі від серверу до клієнта (на АРМ).
введення-виведення до бази ґрунтується не на фізичному дробленні даних, а на логічному, тобто сервер відправляє клієнтам не повну копію бази, а тільки логічно необхідні порції. Завдяки цьому скорочується трафік мережі.
Сервер обробляє запити клієнтів, вибирає потрібні дані з БД, посилає їх клієнтам по мережі, поновлює інформацію, забезпечує цілісність і збереження даних.
Слайд 234. Триланкова (багатоланкова) архітектура “клієнт-сервер”
Триланкова (у деяких випадках багатоланкова) архітектура
(N- tier або multi - tier ) є подальшим вдосконаленням
технології " клієнт - сервер" .
Архітектура “клієнт - сервер“ є 2-ланкова (two-tier, 2-tier):
перша ланка - клієнтський додаток,
друга ланка - сервер БД + сама БД.
Слайд 24У триланкової архітектурі третьою ланкою в стає сервер додатків, тобто
компоненти розподіляються наступним чином:
Представлення даних - на стороні клієнта.
Прикладний компонент
- на виділеному сервері додатків (як варіант, виконує функції проміжного ПЗ).
Управління ресурсами - на сервері БД, який і представляє запитувані дані.
Слайд 25Триланкового архітектура може бути розширена до багатоланкової (N-tier, Multi-tier) шляхом
виділення додаткових серверів, кожен з яких представлятиме власні сервіси і
користуватися послугами інших серверів різного рівня.
Слайд 26Порівняння архітектур
Дволанкова архітектура простіша , так як всі запити обслуговуються
одним сервером , але саме через це вона менш надійна
і вимагає підвищеної продуктивності сервера.
Триланкова архітектура складніша, але завдяки тому, що функції розподілені між серверами другого і третього рівня , ця архітектура має:
високий ступінь гнучкості і масштабованості;
високу безпеку (тому захист можна визначити для кожного сервісу або рівня);
високу продуктивність (завдання розподілені між серверами ).
Слайд 27Представлення даних в базах даних
Складові БД
Слайд 28Приклад фрагмента ER-діаграми
Локальні моделі об'єднані в єдину концептуальну модель
Слайд 29Основні етапи проектування баз даних
Основні етапи проектування БД:
Перша стадія концептуального
проектування БД:
Предметна область;
Інформаційні потреби користувача;
Проектування узагальненої концептуальної моделі;
Друга стадія концептуального
проектування БД:
Вибір СУБД;
Проектування логічної моделі БД.
Проектування представлення даних в пам'яті ЕОМ;
Оцінка експлуатаційних характеристик прикладних програм на основі побудованих моделей;
Проектування і розробка програми реалізації
Слайд 30Життєвий цикл проектування бази даних:
створення,
апробація,
виправлення помилок і
поліпшення характеристик,
дослідна експлуатація.
Слайд 31Мова структурованих запитів SQL
Мова SQL (Structured Query Language - структурована
мова запитів) застосовується для спілкування користувача з реляційною базою даних
і складається з трьох частин:
• DDL (Data Definition Language) - мова визначення даних. Призначена для створення бази даних (таблиць, індексів і т.д.) і редагування її схеми.
• DCL (Data Control Language) - мова керування даними. Містить оператори для розмежування доступу користувачів до об'єктів бази даних.
• DML (Data Manipulation Language) - мова обробки даних. Містить оператори для внесення змін у вміст таблиць бази даних.
Слайд 32Оператори визначення даних (мова DDL)
CREATE TABLE
таблиці в базі даних.
DROP TABLE
бази даних.
ALTER TABLE <ім'я> - зміна структури існуючої таблиці або обмежень цілісності, що задаються для даної таблиці.
Слайд 33Оператори розмежування доступу користувачів до об'єктів бази даних (DCL)
GRANT -
створення в системі безпеки запису, що дозволяє користувачу працювати з
даними або виконувати визначені операції SQL.
DENY - створення в системі безпеки запису, що забороняє доступ до певного облікового запису.
Слайд 34Оператори маніпулювання даними (мова DML)
SELECT - вибірка рядків, які відповідають
заданим умовам.
UPDATE - зміна значень певних полів у рядках
таблиці, які відповідають заданим умовам.
INSERT - вставка нових рядків у таблицю.
DELETE - видалення рядків таблиці, які відповідають заданим умовам.
Слайд 35Використання мови SQL для вибору інформації з таблиці
Синтаксис оператора SELECT,
має наступний вигляд:
SELECT [ALL / DISTINCT] / *
FROM
<список таблиць>
[WHERE <умова вибірки>]
[ORDER BY <список атрибутів>]
[GROUP BY <список атрибутів>]
[HAVING <умова>]
У квадратних дужках вказані елементи, які можуть бути відсутні у запиті.
Слайд 37Видати список всіх студентів:
SELECT *
FROM student
або
SELECT id_st, surname
FROM student
Якщо додати
до даного запиту пропозицію ORDER BY surname, то список буде
впорядковано за прізвищем.
За замовчуванням вважають, що сортування проводиться за зростанням. Якщо необхідно впорядкування за спаданням, після імені атрибута додається слово DESC.
Слайд 38Видати список оцінок, які отримав студент з кодом “1".
SELECT id_st,
mark
FROM mark_st
WHERE id_st = 1
Видати список кодів студентів, які отримали
на іспитах хоча б одну двійку чи трійку.
У реченні WHERE можна записувати вираз з використанням арифметичних операторів порівняння (<,>, і т.д.) і логічних операторів (AND, OR, NOT)
SELECT id_st, mark
FROM mark_st
WHERE (MARK> = 2) AND (MARK <= 3)
Слайд 39Для складання умов у мові SQL існують ряд спеціальних операторів
:
• IN – належність до множини значень;
• BETWEEN - входження
в деякий діапазон значень;
• LIKE - перевірка на збіг із зразком;
• IS NULL - перевірка на невизначене значення.
Видати список кодів студентів, які отримали на іспитах хоча б одну двійку чи трійку (через in)
SELECT id_st , mark
FROM mark_st
WHERE mark IN (2,3)
Того ж результату можна добитися, використовуючи оператор BETWEEN
SELECT id_st , mark
FROM mark_st
WHERE mark BETWEEN 2 AND 3
Слайд 40Видати список всіх студентів, прізвища яких починаються з літери А.
Оператор LIKE застосовують лише до символьних полів, він дозволяє встановлювати, чи відповідає значення поля зразку.
Зразок може містити
спеціальні символи:
_ (символ підкреслення) - означає будь-який одиночний символ;
% (знак відсотка) - означає послідовність будь-якої кількості символів.
SELECT id_st, surname
FROM student
WHERE surname
LIKE 'А%'
Слайд 41Обчислення мінімальних, максимальних або середніх значень у стовпцях здійснюють за
допомогою спеціальних агрегатних функцій:
• MIN - мінімальне значення в стовпці;
•
MAX - максимальне значення в стовпці;
• SUM - сума значень у стовпці;
• AVG - середнє значення в стовпці;
• COUNT - кількість значень у стовпці, відмінних від NULL.
Слайд 42Розрахувати середній бал здачі іспитів.
SELECT AVG (mark)
FROM mark_st
Що обчислить запит?
SELECT
AVG (mark)
FROM mark_st
WHERE id_st = 100
SELECT AVG(mark)
FROM mark_st
WHERE id_ex =
10
Слайд 43Обчислити середній бал по всіх іспитах для кожного студента:
SELECT id_st
, AVG ( mark )
FROM mark_st
GROUP BY id_st
Обчислити середній бал
за оцінками, отриманими на іспиті з кодом 100, для кожного студента:
SELECT id_st , AVG ( mark )
FROM mark_st
WHERE id_ex = 100
GROUP BY id_st
Слайд 44Включення тексту в запит:
SELECT 'Середній балл=', AVG(mark)
FROM mark_st
WHERE id_ex
= 10
Слайд 45Використання SQL для вибору інформації з декількох таблиць
Потрібно роздрукувати список
студентів з оцінками, які вони отримали на іспитах:
SELECT student.surname, mark_st.id_ex,
mark_st.mark
FROM student, mark_st
WHERE student.id_st = mark_st.id_st
Слайд 46Видалити всі дані з таблиці student:
DELETE
FROM student
Можна обмежити діапазон інформації,
що видаляється наступним чином:
DELETE
FROM student
WHERE surname > 'І'
Змінити на "5"
оцінку у студента з кодом 100 з іспиту з кодом 10:
UPDATE mark_st
SET mark = '5 '
WHERE id_st = 100 AND id_ex = 10