Po przejściu przez wszystkie etapy normalizacji, nasza baza danych osiągnęła dojrzałą i stabilną strukturę. Teraz nadszedł czas, aby tchnąć w nią życie – wypełnić ją danymi, rozbudować o nowe funkcjonalności i nauczyć się efektywnie odpytywać o przechowywane w niej informacje. W tym laboratorium skupimy się na praktycznym wykorzystaniu naszej znormalizowanej bazy danych.
Poniżej znajduje się kompletny schemat naszej bazy danych biblioteki po wszystkich etapach normalizacji. Obejmuje on wszystkie tabele i kluczowe relacje między nimi.
+-------------------+ +----------------------+ +-------------------+
| autorzy | | ksiazki_autorzy | | ksiazki |
|-------------------| |----------------------| |-------------------|
| PK| id_autora |--<{--| PK,FK| id_autora_fk | | PK| id_ksiazki |-->--+
| | imie | | PK,FK| id_ksiazki_fk |--}-->| | tytul | |
| | nazwisko | +----------------------+ | | rok_wydania | |
+-------------------+ | | isbn | |
+-------------------+ |
|
+-------------------+ +------------------------+ +-------------------+ |
| dzialy | | pracownicy | | czytelnicy | |
|-------------------| |------------------------| |-------------------| |
| PK| id_dzialu |--<{--| FK| id_dzialu_fk | | PK| id_czytelnika |-->--+
| | nazwa_dzialu | | PK| id_pracownika |->--+ | | imie | |
| | kierownik... | | | pesel (UNIQUE) | | | | nazwisko | |
+-------------------+ | | imie, nazwisko, ...| | | | ulica | |
+------------------------+ | | FK| kod_pocztowy |--}->+
| | +-------------------+ |
| | |
| +--------------------------+
| | kody_pocztowe |
| |--------------------------|
| | PK| kod_pocztowy |
| | | miasto |
| +--------------------------+
|
+--------------------------------+
|
| +-------------------------+
+-------->| wypozyczenia |
|-------------------------|
| PK| id_wypozyczenia |
| FK| id_ksiazki_fk | ----> (do ksiazki)
| FK| id_czytelnika_fk | ----> (do czytelnicy)
| FK| id_pracownika_fk | ----> (do pracownicy)
| | data_wypozyczenia |
| | max_data_zwrotu |
| | data_zwrotu (NULL) |
+-------------------------+
Powyższy schemat ASCII jest przydatny, ale narzędzia do zarządzania bazami danych oferują graficzne kreatory, które jeszcze lepiej wizualizują strukturę. Aby uzyskać taki widok:
Wypełnienie wszystkich tabel w bazie danych spójnymi, przykładowymi danymi, aby umożliwić testowanie i wykonywanie zapytań.
Nasza baza ma poprawną strukturę, ale jest pusta. Aby móc z nią pracować, musimy wstawić do niej dane. Poniżej znajdują się skrypty SQL, które wypełnią każdą z tabel około 50 rekordami. Pamiętaj, że kolejność wykonywania poleceń `INSERT` ma znaczenie ze względu na klucze obce – najpierw musimy wypełnić tabele, do których inne się odwołują (np. `autorzy` przed `ksiazki`).
Poniższy kod jest skróconą wersją. Pełny skrypt do wygenerowania 50 rekordów dla każdej tabeli byłby bardzo długi, ale zasada pozostaje taka sama.
-- Wypełnianie tabel podstawowych (bez kluczy obcych)
INSERT INTO `kody_pocztowe` (`kod_pocztowy`, `miasto`) VALUES
('00-001', 'Warszawa'), ('31-001', 'Kraków'), ('50-001', 'Wrocław'), ('61-001', 'Poznań'), ('80-001', 'Gdańsk');
INSERT INTO `dzialy` (`nazwa_dzialu`, `kierownik_dzialu`) VALUES
('Obsługa Czytelnika', 'Janina Kowalska'), ('Opracowanie Zbiorów', 'Adam Nowak'), ('Administracja', 'Ewa Wiśniewska');
INSERT INTO `autorzy` (`imie`, `nazwisko`) VALUES
('Henryk', 'Sienkiewicz'), ('Adam', 'Mickiewicz'), ('Olga', 'Tokarczuk'), ('Stanisław', 'Lem'), ('Andrzej', 'Sapkowski');
-- Wypełnianie tabel zależnych
INSERT INTO `czytelnicy` (`imie`, `nazwisko`, `ulica`, `kod_pocztowy`) VALUES
('Jan', 'Kowalski', 'ul. Prosta 1', '00-001'), ('Anna', 'Nowak', 'ul. Krzywa 2', '31-001'), ('Piotr', 'Zieliński', 'ul. Długa 3', '50-001');
INSERT INTO `pracownicy` (`pesel`, `imie`, `nazwisko`, `stanowisko`, `id_dzialu_fk`) VALUES
('80010112345', 'Krzysztof', 'Ibisz', 'Bibliotekarz', 1), ('90020223456', 'Monika', 'Olejnik', 'Starszy Bibliotekarz', 1), ('85030334567', 'Tomasz', 'Lis', 'Magazynier', 2);
INSERT INTO `ksiazki` (`tytul`, `rok_wydania`, `isbn`) VALUES
('Potop', 1886, '9788373271895'), ('Pan Tadeusz', 1834, '9788373271888'), ('Księgi Jakubowe', 2014, '9788308049396'), ('Solaris', 1961, '9788308049310'), ('Wiedźmin', 1986, '9788375780645');
-- Wypełnianie tabeli łączącej
INSERT INTO `ksiazki_autorzy` (`id_ksiazki_fk`, `id_autora_fk`) VALUES
(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
Dodanie do bazy danych nowej funkcjonalności pozwalającej na rejestrowanie wypożyczeń książek przez czytelników, wraz z datami i obsługującym pracownikiem.
Nasza baza przechowuje informacje o książkach, czytelnikach i pracownikach, ale nie potrafi ich ze sobą połączyć w ramach procesu wypożyczenia. Aby to zmienić, stworzymy nową tabelę `wypozyczenia`. Będzie ona sercem systemu, przechowując klucze obce do książki, czytelnika i pracownika, a także daty związane z wypożyczeniem.
-- Tworzenie tabeli do obsługi wypożyczeń
CREATE TABLE `wypozyczenia` (
`id_wypozyczenia` INT AUTO_INCREMENT PRIMARY KEY,
`id_ksiazki_fk` INT NOT NULL,
`id_czytelnika_fk` INT NOT NULL,
`id_pracownika_fk` INT NOT NULL,
`data_wypozyczenia` DATE NOT NULL,
`max_data_zwrotu` DATE NOT NULL,
`data_zwrotu` DATE DEFAULT NULL,
FOREIGN KEY (`id_ksiazki_fk`) REFERENCES `ksiazki`(`id_ksiazki`),
FOREIGN KEY (`id_czytelnika_fk`) REFERENCES `czytelnicy`(`id_czytelnika`),
FOREIGN KEY (`id_pracownika_fk`) REFERENCES `pracownicy`(`id_pracownika`)
);
-- Wstawienie przykładowych wypożyczeń
INSERT INTO `wypozyczenia` (`id_ksiazki_fk`, `id_czytelnika_fk`, `id_pracownika_fk`, `data_wypozyczenia`, `max_data_zwrotu`) VALUES
(1, 1, 1, '2023-10-01', '2023-10-31'), -- Wypożyczenie niezwrócone, w terminie
(2, 1, 2, '2023-09-01', '2023-10-01'), -- Wypożyczenie przeterminowane
(3, 2, 1, '2023-10-10', '2023-11-10');
UPDATE `wypozyczenia` SET `data_zwrotu` = '2023-10-15' WHERE `id_wypozyczenia` = 1; -- Symulacja zwrotu jednej książki
-- 1. Kto aktualnie przetrzymuje książki (niezwrócone)?
-- Proste zapytanie, które filtruje tabelę `wypozyczenia` w poszukiwaniu rekordów, gdzie data zwrotu jest pusta (NULL).
SELECT * FROM `wypozyczenia` WHERE `data_zwrotu` IS NULL;
-- 2. Kto ma książki przeterminowane?
-- Łączymy `wypozyczenia` z `czytelnicy`, aby wyświetlić dane czytelnika. Warunek `WHERE` sprawdza, czy książka nie została zwrócona ORAZ czy maksymalna data zwrotu już minęła.
SELECT c.imie, c.nazwisko, k.tytul, w.max_data_zwrotu
FROM wypozyczenia w
JOIN czytelnicy c ON w.id_czytelnika_fk = c.id_czytelnika
JOIN ksiazki k ON w.id_ksiazki_fk = k.id_ksiazki
WHERE w.data_zwrotu IS NULL AND w.max_data_zwrotu < CURDATE();
-- 3. Jakie książki ma aktualnie wypożyczone Jan Kowalski?
-- Łączymy trzy tabele, aby po imieniu i nazwisku czytelnika znaleźć tytuły książek, których jeszcze nie zwrócił.
SELECT k.tytul, w.data_wypozyczenia, w.max_data_zwrotu
FROM wypozyczenia w
JOIN ksiazki k ON w.id_ksiazki_fk = k.id_ksiazki
JOIN czytelnicy c ON w.id_czytelnika_fk = c.id_czytelnika
WHERE c.imie = 'Jan' AND c.nazwisko = 'Kowalski' AND w.data_zwrotu IS NULL;
-- 4. Który pracownik wypożyczył książkę "Potop" Janowi Kowalskiemu?
-- To złożone zapytanie łączy aż 4 tabele, aby na podstawie danych książki i czytelnika odnaleźć dane pracownika, który obsłużył transakcję.
SELECT p.imie, p.nazwisko
FROM pracownicy p
JOIN wypozyczenia w ON p.id_pracownika = w.id_pracownika_fk
JOIN ksiazki k ON w.id_ksiazki_fk = k.id_ksiazki
JOIN czytelnicy c ON w.id_czytelnika_fk = c.id_czytelnika
WHERE k.tytul = 'Potop' AND c.imie = 'Jan' AND c.nazwisko = 'Kowalski';
-- 5. Kto aktualnie ma wypożyczoną książkę "Solaris"?
-- Podobne do poprzednich, ale tym razem szukamy czytelnika na podstawie tytułu książki.
SELECT c.imie, c.nazwisko, c.ulica
FROM czytelnicy c
JOIN wypozyczenia w ON c.id_czytelnika = w.id_czytelnika_fk
JOIN ksiazki k ON w.id_ksiazki_fk = k.id_ksiazki
WHERE k.tytul = 'Solaris' AND w.data_zwrotu IS NULL;
-- 6. Zlicz, ile książek jest aktualnie wypożyczonych.
-- Używamy funkcji agregującej COUNT() do policzenia wierszy spełniających warunek (brak daty zwrotu).
SELECT COUNT(*) AS liczba_wypozyczonych_ksiazek FROM wypozyczenia WHERE data_zwrotu IS NULL;
-- 7. Wyświetl historię wypożyczeń dla książki o ID = 3.
-- Zapytanie pokazuje pełną historię (kto i kiedy wypożyczał), łącząc wypożyczenia z czytelnikami i sortując wyniki po dacie.
SELECT c.imie, c.nazwisko, w.data_wypozyczenia, w.data_zwrotu
FROM wypozyczenia w
JOIN czytelnicy c ON w.id_czytelnika_fk = c.id_czytelnika
WHERE w.id_ksiazki_fk = 3
ORDER BY w.data_wypozyczenia DESC;
-- 8. Zmiana nazwiska czytelniczki Anny Nowak na "Nowak-Kowalska".
-- Polecenie UPDATE służy do modyfikacji istniejących danych. Klauzula WHERE jest kluczowa, aby zaktualizować tylko właściwy rekord.
UPDATE czytelnicy SET nazwisko = 'Nowak-Kowalska' WHERE id_czytelnika = 2;
-- 9. Usunięcie uszkodzonej książki z bazy danych.
-- Polecenie DELETE usuwa rekordy. Należy pamiętać, że jeśli istnieją wypożyczenia tej książki, operacja może się nie udać z powodu ograniczeń klucza obcego. Należałoby najpierw usunąć powiązane wypożyczenia.
DELETE FROM ksiazki_autorzy WHERE id_ksiazki_fk = 4; -- Najpierw usuwamy powiązania
DELETE FROM wypozyczenia WHERE id_ksiazki_fk = 4; -- Potem wypożyczenia
DELETE FROM ksiazki WHERE id_ksiazki = 4; -- Na końcu samą książkę
-- 10. Znajdź 5 najczęściej wypożyczanych książek.
-- To zaawansowane zapytanie, które grupuje wypożyczenia po ID książki, zlicza je, sortuje malejąco i wybiera 5 pierwszych wyników, łącząc je z tabelą książek, aby pokazać tytuły.
SELECT k.tytul, COUNT(w.id_wypozyczenia) AS liczba_wypozyczen
FROM wypozyczenia w
JOIN ksiazki k ON w.id_ksiazki_fk = k.id_ksiazki
GROUP BY k.id_ksiazki
ORDER BY liczba_wypozyczen DESC
LIMIT 5;
W tym laboratorium przeszliśmy od teorii do praktyki. Wypełniliśmy naszą znormalizowaną bazę danych danymi, a następnie rozbudowaliśmy ją o kluczową funkcjonalność systemu bibliotecznego – wypożyczenia. Nauczyłeś się tworzyć złożone zapytania, które łączą informacje z wielu tabel, aby uzyskać cenne raporty i zarządzać danymi. Opanowałeś również podstawowe operacje modyfikacji (`UPDATE`) i usuwania (`DELETE`), rozumiejąc ich wpływ na integralność bazy danych. Twoja baza jest teraz w pełni funkcjonalna.