Об’єктно-реляційна система управління базами даних PostgreSQL

Размещено на http://www.allbest.ru/

МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ

КРЕМЕНЧУЦЬКИЙ НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ

ІМЕНІ МИХАЙЛА ОСТРОГРАДСЬКОГО

ФАКУЛЬТЕТ ЕЛЕКТРОНІКИ ТА КОМП'ЮТЕРНОЇ ІНЖЕНЕРІЇ

КАФЕДРА ІНФОРМАТИКИ І ВИЩОЇ МАТЕМАТИКИ

КУРСОВА РОБОТА

З ДИСЦИПЛІНИ «БАЗИ ДАНИХ

ТЕМА: «Об'єктно-реляційна система управління базами даних PostgreSQL»

ЗАВІДУВАЧ КАФЕДРИ В.П. Ляшенко

КЕРІВНИК РОБОТИ А.І. Дерієнко

ВИКОНАЛА студентка групи І-11-1

Роман О.В.

КРЕМЕНЧУК 2014

РЕФЕРАТ

Пояснювальна записка до курсової роботи: 34 сторінки, 11 рисунків, 8 літературних джерел.

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

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

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

РЕФЕРАТ

Пояснительная записка к курсовой работе: 34 страницы, 11 рисунков, 8 литературных источников.

Целью работы является изучение объектно-реляционной модели данных и ее особенностей. Объект исследования - объектно-реляционная модель данных, а предмет - реализация базы данных факультета на основе этой модели.

Исследования объектно-реляционной модели и собственно построение базы данных проводились в инструментальной системе управления базами данных PostgreSQL.

Практическим результатом данной работы является готовая функционирующая база данных факультета, что имеет в подчинении несколько кафедр, на которых работают преподаватели, преподавая различные предметы различным академическим группам. Каждый из студентов группы получает оценки по разным предметам, которые также фиксируются в базе. База данных заполнена реальными данными, протестирована и готовая для работы.

ЗМІСТ

• ВСТУП
• 1 Система управління базами даних
• 1.1 Основні функції
• 1.2 Класифікація СУБД
• 1.2.1 По моделі даних
• 1.2.2 За ступенем роздрібненості
• 1.2.3 За способом доступу до БД
• 2 Об'єктно-реляційна система управління базами даних PostgreSQL
• 2.1 Історія розвитку
• 2.2 Основні можливості
• 2.2.1 Функції
• 2.2.2 Тригери
• 2.2.3 Правила і представлення
• 2.2.4 Індекси
• 2.2.5 Механізм «Multiversion Concurrency Control»
• 2.2.6 Типи даних
• 2.2.7 Користувальницькі об'єкти
• 2.2.8 Наслідування
• 2.2.9 Інші можливості
• 2.2.10 Надійність
• 3 Реалізація бази даних факультету засобами ОРСУБД PostgreSQL
• 3.1 Постановка задачі
• 3.2 Розробка бази даних
• 3.2.1 Концептуальна модель даних

3.2.2 Фізична модель даних

3.3 Тестування БД

• ВИСНОВКИ
• ПЕРЕЛІК ПОСИЛАНЬ

ВСТУП

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

Фіксація даних традиційно відбувається через конкретні засоби спілкування - природної мови чи зображень, на конкретному носії: камені чи папері. Враховуючи той факт, що природна мова володіє достатньою гнучкістю, дані та їх інтерпретація (семантика) фіксуються спільно. Наприклад, розглянемо твердження "Вартість квитка на електричку 147". Тут "147" - дане, а "Вартість квитка на електричку" - його семантика.

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

Поділ даних та інтерпретації стає ще більш відчутним, коли ЕОМ застосовується для введення й обробки даних. Це відбувається тому, що ЕОМ може мати справу тільки з даними. Велика частина інформації, що інтерпретується, не фіксується в явній формі (ЕОМ не "розуміє", "22.50" є вартістю авіаквитка або часом вильоту). Чому так відбувається?

Існують як мінімум дві історичні причини, що сприяють тому, що активне використання ЕОМ сприяло тому, що відбулося розділення даних та інтерпретації:

– ЕОМ не мала достатніх можливостей, щоб обробляти тексти природною мовою - переважно мовою інтерпретації даних;

– висока вартість пам'яті ЕОМ.

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

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

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

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

Система управління базами даних (СУБД) призначена для надання доступу до даних різних користувачів, причому, навіть для тих, які не мають уявлення про те, якими функціями володіють СУБД.

1 СИСТЕМА УПРАВЛІННЯ БАЗАМИ ДАНИХ

1.1 Основні функції

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

Основними функціями СУБД є:

– керування даними, що знаходяться в зовнішній пам'яті (на дисках);

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

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

– підтримка мов БД (мова визначення даних, мова маніпулювання даними).

Зазвичай сучасна СУБД містить наступні компоненти:

– ядро, яке відповідає за управління даними у зовнішній і оперативній пам'яті, та журналізацію;

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

– підсистему підтримки часу виконання, яка інтерпретує програми маніпуляції даними, створюють користувальницький інтерфейс з СУБД;

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

1.2 Класифікація СУБД

1.2.1 За моделлю даних

За моделлю даних СУБД поділяються на:

– ієрархічні;

– мережеві;

– реляційні;

– об'єктно-орієнтовані;

– об'єктно-реляційні.

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

Мережеві розширюють ієрархічну модель СУБД, дозволяючи групувати зв'язки між записами у множину. З логічної точки зору зв'язок - це не сам запис. Зв'язки лише виражають відносини між записами. Як і в ієрархічній моделі, зв'язки ведуть від батьківського запису до дочірнього, але на цей раз підтримується множинне спадкування. Слідуючи специфікації CODASYL, мережева модель підтримує DDL (Data Definition Language - мова визначення даних) і DML (Data Manipulation Language - мова обробки даних). Це спеціальні мови, призначені для визначення структури бази даних і складання запитів. Незважаючи на їх наявність програміст раніше повинен знати структуру бази даних.

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

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

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

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

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

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

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

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

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

З допомогою ООСУБД вирішуються дві проблеми. По-перше, складні інформаційні структури виражаються у них краще, ніж у реляційних базах даних, а по-друге, усувається необхідність транслювати дані з формату, який підтримується в СУБД. Наприклад, в реляційній СУБД розмірність цілих чисел може становити 11 цифр, а у використовуваній мові програмування - 16. Програмісту доведеться враховувати цю ситуацію[4].

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

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

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

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

Перебудова архітектури СУБД з метою включення в неї підтримки нового типу даних - не кращий вихід з положення. Замість цього об'єктно-реляційна СУБД дозволяє завантажувати код, призначений для обробки "нетипових" даних. Таким чином, база даних зберігає свою табличну структуру, але спосіб обробки деяких полів таблиць визначається ззовні, тобто програмістом[6].

1.2.2 За ступенем роздрібненості

За ступенем роздрібненості СУБД поділяються на:

– локальні СУБД (всі частини локальної СУБД розміщуються на одному комп'ютері);

– розподілені СУБД (частини СУБД можуть розміщуватися на двох та більше комп'ютерах).

postgresql база дані

1.2.3 За способом доступу до БД

За способом доступу до БД СУБД поділяються на:

– файл-серверні;

– клієнт-серверні;

– вбудовані.

У файл-серверних СУБД файли даних розташовуються централізовано на файл-сервері. СУБД розташовується на кожному клієнтському комп'ютері (робочої станції). Доступ до СУБД даними здійснюється через локальну мережу. Синхронізація читань і оновлень здійснюється за допомогою файлових блокувань. Перевагою цієї архітектури є низька навантаження на ЦП сервера. Недоліки: потенційно висока завантаження локальної мережі; утрудненість централізованого управління; утрудненість забезпечення таких важливих характеристик як висока надійність, висока доступність і висока безпека. Застосовуються найчастіше в локальних додатках, які використовують функції управління БД. На даний момент файл-серверна технологія вважається застарілою. Приклади: Microsoft Access, Paradox, dBase, FoxPro, Visual FoxPro.

Клієнт-серверна СУБД розташовується на сервері разом з БД і здійснює доступ до БД безпосередньо, у монопольному режимі. Всі клієнтські запити на обробку даних обробляються клієнт-серверної СУБД централізовано. Недолік клієнт-серверних СУБД полягає в підвищених вимогах до сервера. Переваги: потенційно більш низьке завантаження локальної мережі; зручність централізованого управління; зручність забезпечення таких важливих характеристик як висока надійність, висока доступність і висока безпека. Приклади: Oracle, Firebird, Interbase, IBM DB2, Informix, MS SQL Server, Sybase Adaptive Server Enterprise, PostgreSQL, MySQL, Cachй, ЛІНТЕР.

Вбудована СУБД - СУБД, яка може поставлятися як складова частина деякого програмного продукту, не вимагаючи процедури самостійної установки. Вбудована СУБД призначена для локального зберігання даних програми і не розрахована на колективне використання в мережі. Фізично вбудована СУБД найчастіше реалізована у вигляді підключається бібліотеки. Доступ до даних з боку програми може відбуватися через SQL або через спеціальні програмні інтерфейси. Приклади: OpenEdge, SQLite, BerkeleyDB, Firebird Embedded, Sav Zigzag, Microsoft SQL Server Compact, ЛІНТЕР.

Основні ідеї сучасної інформаційної технології базуються на концепції баз даних (БД). Відповідно до даної концепції основою інформаційної технології є дані, організовані в БД, адекватно відбивають реалії дійсності в тій або іншій предметній області і забезпечують користувача актуальною інформацією у відповідній предметній області. Перші БД з'явилися вже на початку 1-го покоління ЕОМ, представляючи собою окремі файли даних або їх прості сукупності. По мірі збільшення об'єму і структурної складності збереженої інформації, а також розширення кола споживачів; інформації визначилася необхідність створення зручних ефективних систем інтеграції збережених даних і управління ними. В кінці 60-х років це призвело до створення перших комерційних систем управління базами даних (СУБД), які підтримують організацію і ведення БД. Перед обговоренням подальшого матеріалу, нам буде потрібно ряд основних понять, які використовуються в інформаційних системах різного призначення.

2 ОБ'ЄКТНО-РЕЛЯЦІЙНА СИСТЕМА УПРАВЛІННЯ БАЗАМИ ДАНИХ POSTGRESQL

2.1 Історія розвитку

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

PostgreSQL - це вільно розповсюджувана ОРСУБД та найбільш розвинута з відкритих баз даних у світі і є реальною альтернативою комерційним базам даних.

Спочатку була некомерційна СУБД Postgres, розроблена, як і багато open-source проектів, в Каліфорнійському університеті в Берклі. До розробки Postgres, що почалася в 1986 році, мав безпосереднє відношення Майкл Стоунбрейкера, керівник більш раннього проекту Ingres, на той момент вже придбаного компанією Computer Associates. Сама назва «Postgres» розшифровується як «Post Ingres», відповідно, при створенні Postgres були застосовані раніше зроблені напрацювання.

Стоунбрейкер і його студенти розробляли нову СУБД протягом восьми років, з 1986 по 1994 рік. За цей період в синтаксис були введені процедури, правила, користувацькі типи й багато інших компонентів. Робота не пройшла марно - в 1995 році розробка знову розділилася: Стоунбрейкер використовував отриманий досвід у створенні комерційної СУБД Illustra, а його студенти розробили нову версію Postgres - Postgres95, в якій мова запитів POSTQUEL - спадщина Ingres - була замінена на SQL.

У цей момент розробка Postgres95 була виведена за межі університету і передана команді ентузіастів. З цього моменту СУБД отримала ім'я, під яким вона відома і розвивається в поточний момент - PostgreSQL.

2.2 Основні можливості ОРСУБД PostgreSQL

2.2.1 Функції

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

– вбудованої процедурної мови PL/pgSQL, яка багато в чому аналогічна мові PL/SQL, що використовується в СУБД Oracle;

– скриптової мови - PL/Lua, PL/LOLCODE, PL/Perl, plPHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl і PL/Scheme;

– класичної мови - C, C++, Java (через модуль PL/Java);

– статистичної мови R (через модуль PL/R).

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

Функції можуть виконуватися як з правами їх творця, так і з правами поточного користувача.

Іноді функції ототожнюються з збереженими процедурами, однак між цими поняттями є різниця.

2.2.2 Тригери

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

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

2.2.3 Правила та представлення

Механізм правил (англ. rules) являє собою механізм створення користувацьких обробників не тільки DML-операцій, але і операції вибірки. Основна відмінність від механізму тригерів полягає в тому, що правила спрацьовують на етапі розбору запиту, до вибору оптимального плану виконання і самого процесу виконання. Правила дозволяють змінити поведінку системи при виконанні SQL-операції до таблиці.

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

2.2.4 Індекси

В PostgreSQL є підтримка індексів наступних типів: B-дерево, хеш, R-дерево, GiST, GIN(4). При необхідності можна створювати нові типи індексів, хоча це далеко не тривіальний процес. Індекси в PostgreSQL володіють наступними властивостями:

– можливий перегляд індексу не тільки в прямому, але й у зворотному порядку - створення окремого індексу для роботи конструкції ORDER BY ... DESC не потрібно;

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

– індекси можуть бути функціональними, тобто будуватися не на базі набору значень певного стовпця/стовпців, а на базі набору значень функції від набору значень;

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

– планувальник запитів може використовувати кілька індексів одночасно для виконання складних запитів.

2.2.5 Механізм «Multiversion Concurrency Control»

PostgreSQL підтримує одночасну модифікацію БД багатьма користувачами з допомогою механізму Multiversion Concurrency Control (MVCC). Завдяки цьому дотримуються вимоги ACID, і практично відпадає потреба у блокуванні читання [8].

2.2.6 Типи даних

PostgreSQL підтримує великий набір вбудованих типів даних:

1. Числові типи

а) Цілі

б) З фіксованою точкою

в) З плаваючою точкою

г) Грошовий тип (відрізняється спеціальним форматом виводу, а в іншому аналогічний числам з фіксованою точкою з двома знаками після коми)

2. Символьні типи довільної довжини

3. Двійкові типи (включаючи BLOB)

4. Типи дата/час» (повністю підтримують різні формати, точність, формати виводу, включаючи останні зміни в часових поясах)

5. Булевий тип

6. Перерахування

7. Геометричні примітиви

8. Мережеві типи

а) IP і IPv6-адреси

б) CIDR-формат

в) MAC-адресу

9. UUID-ідентифікатор

10. XML дані

11. Масиви

12. OID-типи

13. Псевдотипи

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

2.2.7 Користувальницькі об'єкти

PostgreSQL може бути розширений користувачем для власних потреб практично в будь-якому аспекті. Є можливість додавати власні:

– перетворення типів;

– типи даних;

– домени (користувальницькі типи спочатку з накладеними обмеженнями);

– функції (включаючи агрегатні);

– індекси;

– оператори (включаючи перевизначення вже існуючих);

– процедурні мови.

2.2.8 Наслідування

Таблиці можуть успадковувати характеристики та набори від інших таблиць (батьківських). При цьому дані, додані в породжену таблицю, автоматично будуть брати участь (якщо це не зазначено окремо) в запитах до батьківської таблиці [8].

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

2.2.9 Інші можливості

Також існують інші можливості:

– дотримання принципів ACID;

– відповідності стандартам ANSI SQL-92 і SQL-99;

– підтримки запитів з OUTER JOIN, UNION, UNION ALL EXCEPT, INTERSECT і підпорядкованого;

– послідовності;

– контроль цілісності;

– реплікації;

– загальні табличні вирази і рекурсивні запити;

– аналітичні функції;

– підтримка Юнікоду (UTF-8);

– підтримка регулярних виразів в стилі Perl;

– вбудована підтримка SSL і Kerberos;

– протокол поділюваних блокувань;

– завантажувані розширення, що підтримують SHA1, MD5, XML та іншу функціональність (API відкритий);

– засоби для генерації сумісного з іншими системами SQL-коду та імпорту з інших систем.

2.2.10 Надійність

Згідно з результатами автоматизованого дослідження різного ПЗ на предмет помилок, у вихідному коді PostgreSQL було знайдено 20 проблемних місць на 775 000 рядків вихідного коду (в середньому, одна помилка на 39 000 рядків коду). Для порівняння: MySQL - 97 проблем, одна помилка на 4 000 рядків коду; FreeBSD (цілком) - 306 проблем, одна помилка на 4 000 рядків коду; Linux (тільки ядро) - 950 проблем, одна помилка на 10 000 рядків коду.

3 РЕАЛІЗАЦІЯ БАЗИ ДАНИХ ФАКУЛЬТЕТУ ЗАСОБАМИ ОРСУБД POSTGRESQL

3.1 Постановка задачі

Необхідно спроектувати базу даних факультету (електроніки та комп'ютерної інженерії), яка б задовольняла наступним умовам:

1. Факультет представлений чотирма кафедрами.

2. За кожною кафедрою закріплені певні викладачі, причому один викладач може працювати одночасно на кількох кафедрах.

3. Кожен викладач викладає певний набір дисциплін, причому одна дисципліна може викладатися кількома викладачами.

4. На факультеті є кілька академічних груп.

5. Студенти закріплені за певною групою.

6. Кожен студент під час сесії отримує оцінки з певних дисциплін. Одній дисципліні може відповідати кілька оцінок одного й того ж студента (наприклад, якщо дисципліна викладається кілька семестрів).

Інформація про викладача має включати прізвище, ім'я та по-батькові, дату народження, дату початку та кінця роботи. Також має вказуватися, на якій кафедрі працює викладач та які дисципліни викладає.

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

Для дисципліни обов'язковими даними є її назва та кількість годин.

Оцінки мають бути виставлені за трьома шкалами: національною (5-бальною), 100-бальною, та шкалою ECTS. Також необхідно знати, з якої дисципліни ця оцінка, якою була форма контролю та хто її отримав.

3.2 Розробка бази даних

3.2.1 Концептуальна модель даних

Таблиця «teachers» (викладачі) має основну інформацію про викладачів: прізвище, ім'я та по-батькові, дата народження, початок роботи на кафедрі та у випадку звільнення чи виходу на пенсію - дату закінчення роботи. Для роботи з нашою базою цієї інформації буде достатньо, тому інші дані є другорядними, і ми їх враховувати не будемо.

Зрозуміло, що для таблиці «students» (студенти) поля будуть майже ті самі, проте слід ще вказати стать. Це поле буде корисним, наприклад, для запитів про хлопців призовного віку.

Наступна таблиця «departments» (кафедри) буде зберігати відомості про кафедру. Проте нам треба зафіксувати не усі відомі дані, а лише ті, які будуть використовуватися для досягнення певних цілей, що ми визначили у постановці задачі. Назва кафедри - це все, що нам потрібно знати про неї.

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

Визначені об'єкти не є незалежними, вони мають певні зв'язки. Визначимо останні, розглядаючи отриману базу даних. Очевидно, що таблиця «departments» пов'язана з викладачами, що працюють на ній, при чому тип цього зв'язку - «багато до багатьох». В свою чергу викладачі пов'язані не лише з кафедрою або кафедрами, за якими вони закріплені, а й з дисциплінами, які викладають. Зі студентами, групами чи оцінками за даною постановкою задачі у викладачів немає прямого зв'язку. Отже, переходимо до таблиці «subject». Як було визначено вище, за дисципліну виставляється оцінка (одна або декілька), тому маємо зв'язок між дисциплінами та оцінками («один до багатьох»). Оцінки виставляються за дисципліну певному студенту, отже, наступним буде зв'язок - «оцінка - студент». Оскільки один студент може мати кілька оцінок, то маємо зв'язок типу «один до багатьох». Студенти закріплені за певною академічною групою, тому також треба встановити зв'язок «один до багатьох».

Таким чином, ми виокремили сім таблиць, визначили їх структуру та зв'язки між ними.

3.2.2 Фізична модель даних

Метою проектування на даному етапі є створення опису СУБД орієнтованої моделі БД. Дії, що виконуються на цьому етапі, занадто специфічні для різних моделей даних. Спираючись на створену концептуальну модель, будемо створювати базу даних у СУБД PostgreSQL.

Спочатку створимо всі таблиці: «teachers», «students», «marks», «subject», «subject_teacher», «groups», «departments».

Рисунок 3.1 - Створення таблиці «groups»

На рисунку 3.1 представлена команда SQL, яка створює таблицю «groups». В ній буде два поля: group_id (типу integer), в якому записується порядковий номер групи, та name (типу varchar), у якому записується назва групи.



Рисунок 3.2 - Таблиці бази даних «faculty_eki»

На рисунку 3.2 представлені вже створені таблиці в модулі pgAdminIII. Розглянемо, наприклад, таблицю «students». На даному рисунку показано, що вибране посилання «students», а за ним - «Columns(10)». Число в дужках (в даному випадку - 10) показує скільки полів має таблиця. Розглянемо дані поля:

1. «student_id» - порядковий номер студента

2. «fname» - прізвище студента

3. «lname» - ім'я

4. «group_id» - номер групи, в якій навчається студент

5. «birthday» - дата народження

6. «sex» - стать

7. «startday» - дата початку навчання в університеті

8. «stud_number» - номер залікової книжки

9. «endday» - дата випуску з університету

10. «scholarship» - стипендія (якщо студент отримує стипендію - 1, якщо ні - 0)

Якщо ми хочемо переглянути тип якого-небудь поля таблиці, то нам потрібно натиснути на ім'я поля (рисунок 3.3).



Рисунок 3.3 - Тип поля «sex»

Також у вікні «Properties» можна побачити всі властивості поля. Наприклад, дане поле не є первинним ключем.

Із рисунку 3.3 видно, що тип «sex» - не вбудований, насправді він є типом, що перераховується. Його можливі значення можна побачити на рисунку 3.4.

Рисунок 3.4 - Значення, які може приймати тип «sex»

Вище ми говорили, що наші таблиці повинні мати зв'язок. Для цього існують первинні та зовнішні ключі. Переглянути ми їх можемо окремо для кожної таблиці, натиснувши на посилання «Constraints» (рисунок 3.5).

Рисунок 3.5 - Первинні та зовнішні ключі таблиці «students»

І нарешті, для того щоб база даних могла бути нам корисною, потрібно її наповнити даними. Для цього відкриваємо вікно «Query tool» та створюємо запит на мові SQL(рисунок 3.6).

Рисунок 3.6 - Заповнення таблиці «subject»

Для того, щоб переглянути дані, також пишемо SQL-запит (рисунок 3.7).

Рисунок 3.7 - Таблиця «subject»

Отже, після всіх пройдених кроків ми отримали базу даних факультету(рисунок 3.8).

Рисунок 3.8 - Фізична модель

3.3 Тестування БД

Для того, щоб перевірити правильність розробки БД нам потрібно зробити декілька запитів.

Задача № 1

Вивести назви предметів, форму контролю та оцінки за національною шкалою, які отримала студентка Роман Олена.

Для цього зробимо такий SQL-запит:

select subject.subject_name, subject.kontrol, marks.mark5

from marks

inner join subject on subject.subject_id = marks.subject_id

where student_id = (

select student_id

from students

where (fname = 'Роман' and lname = 'Олена'))

Рисунок 3.9 - Результат запиту для задачі № 1

Задача № 2

Вивести всіх викладачів, які почали працювати на кафедрі «Інформатики та вищої математики» пізніше 2009 року.

Для цього виконаємо запит:

select fname, lname, post, startdate

from teachers

where (department_id = 1 and startdate>'31-12-2009')

Рисунок 3.10 - Результат запиту для задачі № 2

Задача № 3

Вивести студентів, які впродовж навчання отримали більш ніж 5 п'ятірок.

Для цього виконаємо запит:

select fname,count(mark5)

from students, marks

where (mark5=5 and students.student_id=marks.student_id)

group by (fname) having count(mark5)>5

order by (fname)

Рисунок 3.11 - Результат запиту для задачі № 3

Отже, результати запитів показали, що ми досягли мети, тим самим довівши правильність побудованої структури БД.

ВИСНОВКИ

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

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

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

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

Робота може бути удосконалена шляхом створення форм введення даних, з можливістю виконувати запити, проводити масову корекцію даних та створювати звіти. Також можна забезпечити захист системи та оптимізувати її роботу.

ПЕРЕЛІК ПОСИЛАНЬ

1. Дейт К. Дж. Введение в системы баз данных / К. Дж. Дейт. - М.: Издательский дом «Вильямс», 2005. - 1329 с.

2. Бураков П. В. Введение в системы баз данных: учебное пособие / П. В. Бураков, В. Ю. Петров. - СПб, 2010. - 129 с.

3. Конноли Т. Базы данных: проектирование, реализация и сопровождение. Теория и практика / Т. Конноли, К. Бегг, А. Страчан. - 2-е изд., испр. - М.: Издательский дом «Вильямс», 2003. - 1120 с.

4. Ульман Дж. Введение в системы баз данных / Дж. Ульман, Дж. Уидон. - М.: Изд-во «Лори», 2006. - 379 с.

5. Celko's J. Trees and hierarchies in SQL for smarties / Joe Celko's. - Elsevier, 2012. - 277 p.

6. Кузнецов С. Д. Основы баз данных: учебное пособие / С. Д. Кузнецов - М.: Интернет-Университет информационных технологий, 2007. - 484 с.

7. Тиори Т. Проектирование структур баз данных. Книга 1 / Т. Тиори, Дж. Фрай. - М.: Мир, 1985. - 287 с.

8. http://www.postgresql.org/docs/8.4/interactive/index.html - PostgreSQL.

Размещено на Allbest.ru