1. Оновлення єдиною записи

Змінити назву страви з кодом БЛ = 5 на Форшмак, збільшити його вихід на 30 г і встановити NULL-значення в стовпець Праця.

UPDATE Страви

SET Блюдо = 'Форшмак', Вихід = (Вихід + 30), Праця = NULL

WHERE БЛ = 5;

2. Оновлення безлічі записів

Потроїти ціну всіх продуктів таблиці поставки (крім ціни кави - ПР = 17).

UPDATE Поставки

SET Ціна = Ціна * 3

WHERE ПР <> 17;

3. Оновлення з підзапитом

Встановити рівною нулю ціну і К_во продуктів для постачальників з Паневежиса та Резекне.

UPDATE Поставки

SET Ціна = 0, К_во = 0

WHERE ПС IN

(SELECT ПС

FROM Постачальники

WHERE Місто IN ('Паневежис', 'Резекне'));

28.Етап фізичного проектування. Основні структури зберігання та методи доступу до даних. Основні поняття. Невпорядковані послідовні файли.

Фізичне проектування бази даних. Процес підготовки опису реалізації бази даних зовнішньої пам'яті; опис повинен включати основні відношення, файлову організаціюб, індекси, що забезпечують ефективний доступ до даних, а також всі відповідні обмеження цілісності й засоби захисту.

У цій главі описуються наступні етапи методології фізичного проектування баз даних.

1.Перенос глобальної логічної моделі даних у середовище цільової СКБД.

2.Проектування основних відношень.

3.Розробка способів одержання похідних даних.

4.Реалізація обмежень предметної області.

5.Проектування фізичного представлення бази даних.

6.Аналіз транзакцій.

7.Вибір файлової структури.

8.Визначення індексів.

9.Визначення вимог до дискової пам'яті.

10.Проектування користувальницьких представлень.

11.Розробка механізмів захисту.

12.Обґрунтування необхідності введення контрольованої надлишковості.

13.Поточний контроль і настроювання операційної системи.

 

Методологія фізичного проектування баз даних реляційного типу

Етап 4. Перенос глобальної логічної моделі даних у середовище цільової СКБД

Ціль  Створення базової функціональної схеми реляційної бази даних на основі глобальної логічної моделі даних, що може бути реалізована в цільовий СКБД.

Найпершим завданням на етапі фізичного проектування баз даних є перетворення відношень, створених на основі глобальної логічної моделі даних, у таку форму, що може бути реалізована в середовищі цільової СКБД. Цей процес вимагає наявності глибоких знань про функціональні можливості, що надаються цільовою СКБД. Зокрема, розробник повинен знати наступне:

-  способи створення основних відношень;

-  чи підтримує система визначення первинних, зовнішніх й альтернативних ключів;

-  чи підтримує система визначення обов'язкових даних (тобто чи допускає система вказувати у визначенні атрибута, що для нього заборонене використання значення NULL);

-  чи підтримує система визначення доменів;

-  чи підтримує система реляційні обмеження цілісності;

-  чи підтримує система визначення обмежень предметної області.

На цьому  етапі процедури розробки баз даних виконуються наступні дії.

1.Проектування основних відношень.

2.Розробка способів одержання похідних даних.

3.Реалізація обмежень предметної області.

Етап 4.1      Проектування основних відношень

Ціль  Визначення способу подання в цільовій СКБД         відношень, визначених у глобальній логічній моделі даних.

Приступаючи до фізичного проектування, насамперед необхідно проаналізувати й добре засвоїти інформацію про відношення, зібрану на етапі побудови логічної моделі бази даних. Ця інформація може міститися в словнику даних й у визначеннях відношень, записаних мовою DBDL. Визначення кожного виділеного в глобальній логічній моделі даних відношення включає наступні елементи:

-  ім'я відношення;

-  список простих атрибутів, взятий у круглі дужки;

-  визначення первинного ключа й (якщо такі існують) альтернативних (АК) і зовнішніх (FK) ключів;

-  список похідних атрибутів й опис способів їхнього обчислення;

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

Для кожного атрибута в словнику даних повинна бути наступна інформація;

-  визначення його домену, що включає зазначення типу даних, розмірність внутрішнього представлення атрибута й будь-які необхідні обмеження на припустимі значення;

-  прийняте за замовчуванням значення атрибута (необов'язково);

-  допустимість значення NULL для даного атрибута.

Реалізація основних відношень

Тепер необхідно прийняти рішення про спосіб реалізації основних відношень. Це рішення залежить від типу обраної цільової СКБД – при визначенні основних відношень деякі системи надають більше можливостей, ніж інші. Наприклад існує три способи реалізації основних відношень: з використанням стандарту ISO SQL, Microsoft Access і Oracle.

Документальне оформлення проекту основних відношень

Підготовлений проект основних відношень повинен бути докладно описаний в окремій документації із зазначенням причин, по яких був обраний даний конкретний проект. Зокрема, необхідно вказати, чому був обраний саме цей підхід, якщо є цілий ряд інших варіантів.

Етап 4.2. Розробка способів одержання похідних даних

Ціль  Визначення способу представлення в СКБД всіх похідних атрибутів які включені в глобальну логічну модель даних.

Похідними, або розрахунковими називаються атрибути, значення яких можна визначити з використанням значень інших атрибутів. Наприклад, похідними є всі перераховані нижче атрибути:

-  кількість співробітників, що працюють у конкретному відділенні;

-  загальна сума щомісячної зарплати всіх співробітників;

Іноді похідні атрибути не включають у логічну модель даних, а описують у словнику даних. На етапі фізичного проектування бази даних необхідно визначити, чи належний похідний атрибут зберігатися в базі даних або обчислюватися щораз, коли в ньому виникає необхідність. Проектувальник повинен розрахувати наступне:

-  додаткові витрати на зберігання похідних даних і підтримку їхньої узгодженості з реальними даними, на основі яких вони обчислюються;

-  витрати на обчислення похідних даних, якщо їхнє обчислення виконується в міру необхідності.

Із цих двох варіантів вибирається найменш дорогий з урахуванням вимог до продуктивності.

Етап 4.3. Реалізація обмежень предметної області

Ціль       Розробка обмежень предметної області для цільової СКБД.

Оновлення інформації у відношеннях може регламентуватися обмеженнями предметної області, що регулюють виконання тих реальних транзакцій, які пов'язані із проведенням таких оновлень. Спосіб реалізації зазначених обмежень знов-таки буде залежати від типу обраної цільової СКБД, оскільки одні системи для реалізації обмежень предметної області надають більш широкі можливості, ніж інші. Як і на попередньому етапі, якщо цільова СКБД підтримує стандарт мови SQL, то реалізувати певні типи обмежень буде набагато простіше

Етап 5. Проектування фізичного представлення бази даних

Однією з найважливіших цілей фізичного проектування бази даних є організація ефективного зберігання даних. Існує кілька показників, які можуть бути використані для оцінки досягнутої ефективності.

-  Продуктивність виконання транзакцій. Цей показник являє собою кількість транзакцій, які можуть бути оброблені за заданий інтервал часу. У деяких системах, наприклад у службах резервування авіаквитків, забезпечення високої продуктивності виконання транзакцій є вирішальним чинником успішної експлуатації всієї системи.

-  Час відповіді. Характеризує часовий проміжок, необхідний для виконання однієї транзакції. З погляду користувача бажано зробити час відповіді системи мінімальним. Однак існують деякі фактори, які впливають на швидкодію системи, але не можуть контролюватися розроблювачами, наприклад, рівні завантаження системи або час, затрачуваний на передачу даних.

-  Дискова пам'ять. Цей показник являє собою обсяг дискового простору, необхідного для розміщення файлів бази даних. Розроблювач повинен прагнути мінімізувати обсяг використовуваної дискової пам'яті.

Однак жоден із цих факторів не є самодостатнім. Як правило, розроблювач змушений шукати компроміс між цими показниками для досягнення прийнятного балансу.

Взявши до уваги все вищесказане, приступимо до обговорення тих дій, які повинні бути виконані на п'ятому етапі розробки бази даних.

1.Аналіз транзакцій.

2.Вибір файлової структури.

3.Визначення індексів.

4.Визначення вимог до дискової пам'яті.

Етап 5.1. Аналіз транзакцій

Ціль    Визначення функціональних характеристик транзакцій, які будуть виконуватися в проектованій базі даних, і виділення найбільш важливих

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

-  транзакції, виконувані найбільше часто і які найістотніше впливають на продуктивність;

-  транзакції, найбільш важливі для роботи організації;

-  періоди часу протягом доби/тижнів, у які навантаження бази даних зростає до максимуму (називані періодами пікового навантаження).

Етап 5.2 Вибір файлової структури

Ціль  Визначення найбільш ефективного файлового подання для кожного з основних відношень.

Одним з головних завдань фізичного проектування бази даних є забезпечення ефективного зберігання даних. Наприклад, якщо вибірка рядків з даними про співробітників компанії повинна виконуватися по іменах за абеткою, те найбільш підходящою структурою для зберігання цих даних є файл, відсортований по іменах співробітників. А якщо повинна виконуватися вибірка даних про всіх співробітників, зарплата яких перебуває в певному діапазоні, файл, відсортований по іменах співробітників, не підходить для виконання такого завдання. Положення ще більше ускладнюється у зв'язку з тим, що деякі способи організації файлів добре підходять для масового завантаження даних у базу даних, але їхнє застосування надалі стає неефективним. Іншими словами, завдання полягає у використанні ефективної структури зберігання даних на зовнішньому пристрої, що забезпечує швидке завантаження бази даних, а потім її перетворення в структуру, більше підходящу для повсякденної експлуатації.

Тому ціль етапу – у визначенні оптимальної файлової організації для кожного відношення, якщо цільова СКБД це дозволяє. У багатьох випадках реляційна СКБД може надавати лише обмежений вибір або навіть взагалі не дозволяти вибрати певну файлову організацію, але в деяких СКБД на організацію розміщення даних на зовнішніх пристроях можна деякою мірою вплинути шляхом завдання індексів. Проте далі буде наведено рекомендації з вибору файлової організації на основі типів файлів, перерахованих нижче. Це зроблено для того, щоб краще зрозуміти особливості файлової організації й використання індексів.

Послідовні файли.

-  Хешовані файли.

-  Індексно-послідовні файли (ISAM - Indexed Sequential Access Method).

-  Удосконалені збалансовані дерева (В+-Tree).

-  Кластери.

Якщо цільова СКБД не дозволяє вибрати певну файлову організацію, цей етап може бути пропущений.

Етап 5.3. Визначення індексів

Ціль  Визначення того, чи буде додавання індексів сприяти підвищенню продуктивності системи.

Один з варіантів вибору підходящої файлової структури для відношення полягає в тому, що рядки залишаються неупорядкованими й створюється будь-яка необхідна кількість додаткових індексів. Для вибору атрибута, по якому виконується впорядкування або рядків, застосовуються наступні критерії:

-  вибирається атрибут, найбільше часто застосовуваний в операціях з'єднання, оскільки саме він дозволяє підвищити ефективність таких операцій;

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

Якщо обраний атрибут, по якому відбувається впорядкування, є ключем відношення, то створюваний індекс служить як первинний індекс, а якщо атрибут, по якому відбувається впорядкування, не є ключем, те створюваний індекс служить кластеризуючим індексом. Варто враховувати, що в кожнім відношенні повинен застосовуватися або первинний, або кластеризуючий індекс.

Вибір додаткових індексів

Додаткові індекси надають можливість визначати додаткові ключі для базового відношення, які можуть застосовуватися для підвищення ефективності вибірки даних.

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

-  Ввід індексного запису в кожен додатковий індекс при вставці рядка у відношення.

-  Оновлення додаткового індексу при оновленні відповідного рядка у відношенні.

-  Збільшення потреби в дисковому просторі у зв'язку з необхідністю зберігання додаткового індексу.

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

Етап 5.4. Оцінка потреби в дисковому просторі

Ціль. Визначити обсяг дискового простору, що потрібно для бази даних.

Етап 6. Проектування представлень користувача (переглядів, поданнів).

Ціль  Спроектувати представлення, потреба у створенні яких була виявлена на стадії збору й аналізу вимог, що становить частину життєвого циклу додатка реляційної бази даних.

В автономної СКБД на персональному комп'ютері представлення звичайно застосовуються тільки для зручності, оскільки вони часто дозволяють спростити запити до бази даних. Але в багатокористувацьких СКБД такі представлення відіграють головну роль при визначенні структури бази даних і дотриманні правил захисту.

Етап 7. Проектування засобів захисту

Ціль  Проектування засобів захисту для бази даних у відповідності до вимог користувачів.

База даних являє собою винятково важливий корпоративний ресурс і тому захист цього ресурсу має виняткове значення. На стадії збору й аналізу вимог, що становить частину життєвого циклу додатка бази даних, повинні бути враховані й відбиті в специфікації вимог до системи конкретні вимоги до захисту. Призначення цього етапу складається у визначенні способів реалізації вимог до захисту. Склад засобів захисту залежить від конкретної системи. Тому проектувальник повинен знати, які можливості пропонує розглянута ним цільова СКБД. Реляційні СКБД, як правило, надають засоби захисту бази даних наступних типів:

-  захист системи;

-  захист даних.

Засоби захисту системи регламентують доступ й експлуатацію бази даних на рівні системи; до них, зокрема, належить аутентифікація користувача за іменем та паролем. Засоби захисту даних регламентують доступ і використання об'єктів бази даних (таких як відношення й представлення), а також дії, які можуть бути виконані користувачами з конкретними об'єктами. І в цьому випадку проект реалізації правил доступу залежить від цільової СКБД, оскільки можливості реалізації правил доступу залежать від системи.

Основні поняття

База даних у вторинному пристрої зберігання організована у вигляді одного або декількох файлів, кожен з яких складається з однієї або декількох записів, а кожен запис – з одного або декількох полів. Як правило, запис відповідає деякому єству, а поле – атрибуту. Розглянемо скорочений варіант відношення Staff з учбового проекту DreamHome, приведений в таблицю. В1.

Невпорядковані файли

Невпорядкований файл (який інколи називають купою) має просту структуру. Записи розміщуються у файлі в тому порядку, в якому вони в нього вставляються. Кожен новий запис поміщається на останню сторінку файлу, а якщо на останній сторінці для неї не вистачає місця, то у файл додається нова сторінка. Це дозволяє дуже ефективно виконувати операції вставки. Але оскільки файл подібного типа не володіє жодним впорядкуванням по відношенню до значень полів, для доступу до його записів потрібно виконувати лінійний пошук. При лінійному пошуку всі сторінки файлу послідовно прочитуються до тих пір, поки не буде знайдений потрібний запис. Тому операції витягання даних з невпорядкованих файлів, що мають декілька сторінок, виконуються відносно повільно, за винятком тих випадків, коли витягувані записи складають значну частину всіх записів файлу.

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

Невпорядковані файли краще за всіх останніх типів файлів личать для виконання масового завантаження даних в таблиці, оскільки записи завжди вставляються в кінець файлу, що виключає які-небудь додаткові дії з обчислення адреси сторінки, в яку слід помістити той або інший запис.