Оптимизација на прашалници и погледи

Во овој дел се прикажани SQL погледите кои се користат во системот за управување со библиотека. Секој поглед е креиран со цел да прикажат одредени податоци на поорганизиран начин и да ја олесни работата со книгите, членовите, позајмувањата, резервациите и финансиските информации.

1. View: vw_books_in_library

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

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

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

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

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

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

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

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

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 ја имплементира логиката за следење на оштетени примероци од книги. Се користи за библиотекарот да има преглед кои копии не се во добра состојба и треба да се поправат, заменат или отстранат од употреба.

Прашалници

Заклучок

Креираните SQL views овозможуваат поедноставен и поорганизиран пристап до податоците во библиотечниот систем. Тие ја поддржуваат главната бизнис логика на апликацијата, како што се следење на достапност на книги, резервации, задоцнети книги, финансиски обврски на членови, историја на позајмувања и анализа на популарност на книги. Со користење на овие погледи, системот станува појасен, полесен за одржување и попрактичен за генерирање извештаи.