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

 

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.

 

7. Користувальницькі об’єкти

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

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

 

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

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

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

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

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

• дотримання принципів 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, підтримує великий набір вбудованих типів даних, більш того, користувач може створювати нові необхідні йому типи.