Changes between Initial Version and Version 1 of QueryOptimization

Timestamp:

05/29/26 18:20:46 (3 weeks ago)

Author:

231101

Comment:

--

QueryOptimization

@@ +1 @@
+== SQL Views во системот за библиотека ==
+
+Во овој дел се прикажани SQL погледите кои се користат во системот за управување со библиотека.
+Секој поглед е креиран со цел да прикаже одредени податоци на поорганизиран начин и да ја олесни работата со книгите, членовите, позајмувањата, резервациите и финансиските информации.
+
+---
+
+=== 1. View: vw_books_in_library ===
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_books_in_library AS
+SELECT
+b.book_id,
+b.title,
+COUNT(bc.copy_id) AS book_copy_count,
+
+```
+CASE 
+    WHEN COUNT(CASE WHEN bc.available = 1 THEN 1 END) > 0 
+    THEN 'Available'
+    ELSE 'Not Available'
+END AS availability_status
+```
+
+FROM book b
+LEFT JOIN bookcopy bc
+ON b.book_id = bc.book_id
+GROUP BY b.book_id, b.title;
+}}}
+
+'''Опис:'''
+Овој поглед прикажува информации за книгите во библиотеката, бројот на копии за секоја книга и статусот на достапност.
+Со помош на `COUNT` се пресметува колку копии постојат за секоја книга, а со `CASE` се одредува дали книгата е достапна или не.
+
+'''Употреба / бизнис логика:'''
+Овој view ја имплементира логиката за преглед на достапност на книги во библиотеката.
+Се користи кога библиотекарот или корисникот треба брзо да провери дали одредена книга има достапни копии за позајмување.
+
+---
+
+=== 2. View: vw_member_financial_summary ===
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_member_financial_summary AS
+SELECT
+m.member_id,
+m.first_name,
+m.last_name,
+
+```
+COALESCE(fee_sum.total_fees, 0) AS total_fees,
+COALESCE(fine_sum.total_fines, 0) AS total_fines,
+
+COALESCE(fee_sum.total_fees, 0) + COALESCE(fine_sum.total_fines, 0) AS total_due
+```
+
+FROM Member m
+
+LEFT JOIN (
+SELECT
+mb.member_id,
+SUM(f.amount) AS total_fees
+FROM Fee f
+JOIN Membership mb
+ON mb.membership_id = f.membership_id
+GROUP BY mb.member_id
+) fee_sum
+ON fee_sum.member_id = m.member_id
+
+LEFT JOIN (
+SELECT
+mb.member_id,
+SUM(f.amount) AS total_fines
+FROM Fine f
+JOIN Borrowing b
+ON b.borrowing_id = f.borrowing_id
+JOIN Membership mb
+ON mb.membership_id = b.membership_id
+GROUP BY mb.member_id
+) fine_sum
+ON fine_sum.member_id = m.member_id;
+}}}
+
+'''Опис:'''
+Овој поглед прикажува финансиски преглед за секој член на библиотеката.
+Се пресметуваат вкупните членарини, вкупните казни и вкупниот долг на членот.
+Функцијата `COALESCE` се користи за да се прикаже вредност 0 кога членот нема членарини или казни.
+
+'''Употреба / бизнис логика:'''
+Овој view ја имплементира бизнис логиката за финансиска состојба на членови.
+Се користи за проверка колку вкупно треба да плати одреден член, земајќи ги предвид и членарините и казните.
+
+---
+
+=== 3. View: vw_most_popular_books_per_month ===
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_most_popular_books_per_month AS
+SELECT *
+FROM (
+SELECT
+b.book_id,
+b.title,
+DATE_TRUNC('month', br.borrow_date) AS month,
+COUNT(*) AS total_borrowings,
+RANK() OVER (
+PARTITION BY DATE_TRUNC('month', br.borrow_date)
+ORDER BY COUNT(*) DESC
+) AS rnk
+FROM borrowing br
+JOIN book b ON br.book_id = b.book_id
+GROUP BY b.book_id, b.title, DATE_TRUNC('month', br.borrow_date)
+) sub
+WHERE rnk = 1;
+}}}
+
+'''Опис:'''
+Овој поглед ги прикажува најпопуларните книги по месец, односно книгите кои биле најмногу позајмувани во секој месец.
+Со `DATE_TRUNC('month', br.borrow_date)` позајмувањата се групираат по месец, а со `RANK()` се рангираат книгите според бројот на позајмувања.
+
+'''Употреба / бизнис логика:'''
+Овој view ја имплементира логиката за анализа на популарност на книги.
+Се користи за извештаи кои покажуваат кои книги биле најбарани во одредени месеци, што може да помогне при набавка на нови копии или планирање на библиотечниот фонд.
+
+---
+
+=== 4. View: vw_reservations ===
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_reservations AS
+SELECT
+r.reservation_id,
+r.member_id,
+m.first_name,
+m.last_name,
+r.book_id,
+b.title,
+r.requested_date,
+r.expected_date,
+r.status
+FROM reservation r
+JOIN member m ON r.member_id = m.member_id
+JOIN book b ON r.book_id = b.book_id;
+}}}
+
+'''Опис:'''
+Овој поглед прикажува информации за резервациите направени од членовите.
+Се прикажуваат податоци за членот, книгата, датумот на барање, очекуваниот датум и статусот на резервацијата.
+
+'''Употреба / бизнис логика:'''
+Овој view ја имплементира логиката за следење на резервации во библиотеката.
+Се користи за библиотекарот да има јасен преглед кои книги се резервирани, од кои членови и во каков статус се резервациите.
+
+---
+
+=== 5. View: vw_overdue_books ===
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_overdue_books AS
+SELECT
+m.member_id,
+m.first_name,
+m.last_name,
+b.title AS book_title,
+br.due_date,
+
+```
+CURRENT_DATE - br.due_date AS days_overdue
+```
+
+FROM borrowing br
+
+JOIN member m
+ON m.member_id = br.membership_id
+
+JOIN book b
+ON b.book_id = br.book_id
+
+WHERE
+br.return_date IS NULL
+AND br.due_date < CURRENT_DATE;
+}}}
+
+'''Опис:'''
+Овој поглед ги прикажува книгите кои се позајмени, но не се вратени навреме.
+Се прикажува членот кој ја има позајмено книгата, насловот на книгата, рокот за враќање и бројот на денови на доцнење.
+Условот `br.return_date IS NULL` значи дека книгата сè уште не е вратена.
+
+'''Употреба / бизнис логика:'''
+Овој view ја имплементира логиката за следење на задоцнети книги.
+Се користи за библиотекарот да може лесно да провери кои членови доцнат со враќање на книги и колку дена е поминат рокот.
+
+---
+
+=== 6. View: vw_low_stock_books ===
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_low_stock_books AS
+SELECT
+b.book_id,
+b.title,
+
+```
+COUNT(DISTINCT bc.copy_id) AS total_copies,
+
+COUNT(DISTINCT CASE 
+    WHEN br.return_date IS NULL THEN bc.copy_id 
+END) AS borrowed_copies,
+
+(COUNT(DISTINCT bc.copy_id) 
+ - COUNT(DISTINCT CASE 
+    WHEN br.return_date IS NULL THEN bc.copy_id 
+   END)
+) AS available_copies
+```
+
+FROM book b
+
+JOIN bookcopy bc
+ON b.book_id = bc.book_id
+
+LEFT JOIN borrowing br
+ON bc.copy_id = br.copy_id
+
+GROUP BY
+b.book_id, b.title
+
+HAVING
+(COUNT(DISTINCT bc.copy_id)
+- COUNT(DISTINCT CASE
+WHEN br.return_date IS NULL THEN bc.copy_id
+END)
+) <= 1;
+}}}
+
+'''Опис:'''
+Овој поглед ги прикажува книгите кои имаат мал број достапни копии.
+Се пресметува вкупниот број копии, бројот на позајмени копии и бројот на достапни копии.
+Со `HAVING` условот се прикажуваат само книгите кај кои бројот на достапни копии е помал или еднаков на 1.
+
+'''Употреба / бизнис логика:'''
+Овој view ја имплементира логиката за следење на книги со мала достапност.
+Се користи за библиотеката да може навреме да утврди за кои книги треба да се набават дополнителни копии.
+
+---
+
+=== 7. View: vw_member_borrowing_history ===
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_member_borrowing_history AS
+SELECT
+m.member_id,
+m.first_name,
+m.last_name,
+b.title,
+br.borrow_date,
+br.return_date,
+br.due_date,
+br.return_status
+FROM borrowing br
+JOIN member m ON m.member_id = br.membership_id
+JOIN book b ON br.book_id = b.book_id;
+}}}
+
+'''Опис:'''
+Овој поглед ја прикажува историјата на позајмувања за членовите.
+За секој запис се прикажува членот, книгата, датумот на позајмување, датумот на враќање, рокот за враќање и статусот на враќање.
+
+'''Употреба / бизнис логика:'''
+Овој view ја имплементира логиката за преглед на позајмени книги по член.
+Се користи кога библиотекарот сака да ја провери историјата на позајмувања на одреден корисник.
+
+---
+
+=== 8. View: vw_book_catalog ===
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_book_catalog AS
+SELECT
+b.book_id,
+b.title,
+b.isbn,
+p.name AS publisher,
+
+```
+STRING_AGG(DISTINCT a.first_name || ' ' || a.last_name, ', ') AS authors,
+
+STRING_AGG(DISTINCT g.name, ', ') AS genres,
+
+STRING_AGG(DISTINCT l.name, ', ') AS languages
+```
+
+FROM Book b
+
+LEFT JOIN Publisher p
+ON b.publisher_id = p.publisher_id
+
+LEFT JOIN Author_Book ab
+ON b.book_id = ab.book_id
+
+LEFT JOIN Author a
+ON ab.author_id = a.author_id
+
+LEFT JOIN BookGenre bg
+ON b.book_id = bg.book_id
+
+LEFT JOIN Genre g
+ON bg.genre_id = g.genre_id
+
+LEFT JOIN BookLanguages bl
+ON b.book_id = bl.book_id
+
+LEFT JOIN Language l
+ON bl.language_id = l.language_id
+
+GROUP BY
+b.book_id, b.title, b.isbn, p.name;
+}}}
+
+'''Опис:'''
+Овој поглед претставува каталог на книги во библиотеката.
+За секоја книга се прикажуваат основните информации како наслов, ISBN, издавач, автори, жанрови и јазици.
+Функцијата `STRING_AGG` се користи за повеќе автори, жанрови или јазици да се прикажат во една колона, одделени со запирка.
+
+'''Употреба / бизнис логика:'''
+Овој view ја имплементира логиката за приказ на комплетен каталог на книги.
+Се користи за пребарување и прикажување на детални информации за книгите во библиотечниот систем.
+
+---
+
+=== 9. View: vw_damaged_books ===
+
+{{{#!sql
+CREATE OR REPLACE VIEW vw_damaged_books AS
+SELECT
+b.book_id,
+b.title,
+bc.copy_id
+FROM book b
+JOIN bookcopy bc ON b.book_id = bc.book_id
+WHERE bc.status_id = 2;
+}}}
+
+'''Опис:'''
+Овој поглед ги прикажува оштетените книги во библиотеката.
+Се прикажуваат идентификаторот на книгата, насловот и конкретната копија која е означена како оштетена.
+Условот `bc.status_id = 2` се користи за филтрирање на копии со статус оштетена книга.
+
+'''Употреба / бизнис логика:'''
+Овој view ја имплементира логиката за следење на оштетени примероци од книги.
+Се користи за библиотекарот да има преглед кои копии не се во добра состојба и треба да се поправат, заменат или отстранат од употреба.
+
+---
+
+== Тестирање и оптимизација ==
+
+За проверка на перформансите на погледите се користи командата `EXPLAIN ANALYZE`, со која се добива времето на извршување и начинот на кој базата го извршува пребарувањето.
+
+Пример:
+
+{{{#!sql
+EXPLAIN ANALYZE
+SELECT * FROM vw_member_financial_summary;
+}}}
+
+Дополнително, за подобрување на перформансите може да се користат индекси на колоните кои често се користат во `JOIN`, `WHERE` и `GROUP BY` услови.
+
+Пример:
+
+{{{#!sql
+CREATE INDEX idx_borrowing_membership
+ON borrowing(membership_id);
+}}}
+
+'''Објаснување:'''
+Овој индекс се користи за побрзо поврзување на табелата `borrowing` со други табели преку колоната `membership_id`.
+Индексите помагаат да се намали времето на пребарување, особено кога табелите содржат голем број записи.
+
+---
+
+== Заклучок ==
+
+Креираните SQL views овозможуваат поедноставен и поорганизиран пристап до податоците во библиотечниот систем.
+Тие ја поддржуваат главната бизнис логика на апликацијата, како што се следење на достапност на книги, резервации, задоцнети книги, финансиски обврски на членови, историја на позајмувања и анализа на популарност на книги.
+Со користење на овие погледи, системот станува појасен, полесен за одржување и попрактичен за генерирање извештаи.