Prezentacja WidOK (Widok Ogólny) stanowi kompendium wiedzy o schemacie bazy danych systemu bibliotecznego. Znajdziesz tu wszystkie tabele powstałe w procesie normalizacji od 1NF do 5NF, wraz z ich kolumnami, kluczami i relacjami. To idealne miejsce do szybkiego przypomnienia sobie struktury bazy przed egzaminem lub przed przystąpieniem do implementacji.

W przeciwieństwie do pozostałych prezentacji z cyklu, które skupiają się na konkretnym etapie normalizacji, WidOK prezentuje całościowy obraz. Możesz go traktować jako mapę drogową po bazie danych – zobaczysz, które tabele są ze sobą połączone, jakie klucze obce łączą poszczególne encje i jak wygląda ostateczna struktura po przejściu wszystkich postaci normalnych.

Zachęcam do korzystania z tej prezentacji jako punktu odniesienia podczas pracy z pozostałymi materiałami. Jeśli w trakcie czytania którejś z poprzednich prezentacji zastanawiasz się, jak wygląda końcowy schemat – wróć tutaj. WidOK to Twoja ściągawka do całego cyklu normalizacji.

Agenda prezentacji WidOK została zaprojektowana tak, abyś mógł szybko znaleźć interesujące Cię informacje. Część I pokazuje ewolucję schematu – jak z jednej, chaotycznej tabeli powstawało osiem czystych, wyspecjalizowanych struktur. To kluczowa część, która pomaga zrozumieć, dlaczego normalizacja jest ważna i jak zmienia się projekt bazy na każdym etapie.

Część II koncentruje się na relacjach między tabelami – kluczach obcych, integralności referencyjnej i sposobach łączenia danych z różnych tabel. To praktyczna wiedza niezbędna do pisania zapytań JOIN. Część III przedstawia każdą tabelę z osobna, z pełną listą kolumn, typami danych i ograniczeniami. To z kolei przyda się przy tworzeniu zapytań INSERT i definiowaniu schematu w SQL.

Ostatnia część to podsumowanie całego cyklu normalizacji w formie jednego, kompletnego diagramu ERD. Możesz go wydrukować i mieć zawsze pod ręką podczas projektowania własnych baz danych. WidOK to nie tylko podsumowanie, ale przede wszystkim praktyczne narzędzie do nauki i codziennej pracy.

Stan po 1NF to baza danych w najprostszej możliwej formie. Pojedyncza tabela Wypozyczenia zawiera wszystkie informacje potrzebne do działania systemu bibliotecznego: dane osobowe czytelnika, metadane książki oraz informacje o konkretnym zdarzeniu wypożyczenia. Choć 1NF zapewnia atomowość komórek i istnienie klucza głównego, struktura ta jest daleka od optymalnej ze względu na wymieszanie trzech odrębnych encji.

W tabeli widać wyraźne pogrupowanie kolumn na trzy kategorie: sześć kolumn dotyczy czytelnika (od Czytelnik do Email), sześć kolumn dotyczy książki (od Tytul do Wydawnictwo), a cztery kolumny (plus klucz główny) dotyczą wypożyczenia. To wymieszanie jest źródłem redundancji – dane czytelnika powtarzają się przy każdym jego wypożyczeniu, a dane książki przy każdym wypożyczeniu tej książki.

Konsekwencje tej struktury to klasyczne anomalie: INSERT (nie można dodać czytelnika ani książki bez wypożyczenia), UPDATE (zmiana adresu wymaga modyfikacji wielu wierszy) i DELETE (usunięcie ostatniego wypożyczenia usuwa dane czytelnika i książki). Normalizacja do 2NF zaczyna rozwiązywać te problemy poprzez podział tabeli na trzy odrębne encje.

Druga postać normalna przynosi fundamentalną zmianę w strukturze bazy danych. Z jednej, uniwersalnej tabeli Wypozyczenia powstają trzy wyspecjalizowane tabele, z których każda przechowuje dane jednej encji. Czytelnicy przechowują dane osobowe, Ksiazki przechowują metadane książek, a nowa tabela Wypozyczenia zawiera tylko informacje specyficzne dla zdarzenia wypożyczenia oraz klucze obce łączące ją z dwiema pozostałymi tabelami.

Kluczowym elementem nowej struktury są klucze obce. ID_Czytelnika w tabeli Wypozyczenia odwołuje się do klucza głównego tabeli Czytelnicy, a ISBN w tabeli Wypozyczenia odwołuje się do klucza głównego tabeli Ksiazki. Dzięki temu dane czytelnika i książki są przechowywane dokładnie raz, a w tabeli Wypozyczenia pojawia się jedynie identyfikator – bez powielania adresów, tytułów czy innych danych.

Efekt tej dekompozycji to natychmiastowa eliminacja anomalii INSERT, UPDATE i DELETE dla danych czytelnika i książki. Można dodać nowego czytelnika bez żadnego wypożyczenia, zmiana adresu wymaga modyfikacji jednego wiersza, a usunięcie wypożyczenia nie kasuje danych osobowych. To pierwszy krok w kierunku czystej, wydajnej struktury.

Trzecia postać normalna wprowadza subtelną, ale ważną zmianę: wyodrębnienie danych miasta i kodu pocztowego do osobnej tabeli słownikowej Miasta. W 2NF tabela Czytelnicy przechowywała bezpośrednio nazwę miasta i kod pocztowy. To powodowało zależność przechodnią: ID_Czytelnika → Miasto → KodPocztowy. Oznaczało to, że kod pocztowy zależał od miasta, które mogło się powtarzać dla wielu czytelników.

Nowa tabela Miasta pełni funkcję słownika – każda kombinacja (nazwa miasta, kod pocztowy) występuje dokładnie raz. W tabeli Czytelnicy zamiast nazwy miasta i kodu pocztowego pojawia się ID_Miasta jako klucz obcy. Dzięki temu zmiana kodu pocztowego dla miasta wymaga modyfikacji tylko jednego wiersza w tabeli Miasta, a nie wszystkich czytelników mieszkających w tym mieście.

Warto zwrócić uwagę, że tabele Ksiazki i Wypozyczenia nie ulegają zmianie w 3NF – ich struktura z 2NF była już zgodna z trzecią postacią normalną. To pokazuje, że nie każda tabela wymaga modyfikacji na każdym etapie normalizacji. Proces ten dotyczy tylko tych tabel, które wykazują konkretny typ zależności do wyeliminowania.

BCNF (Boyce-Codd Normal Form) to odpowiedź na ograniczenia 3NF w przypadku nakładających się kluczy kandydujących. W 3NF tabela Przypisania z kluczami (ID_Czytelnika, ISBN) i (ISBN, ID_Bibliotekarza, DataPrzypisania) spełniała warunki 3NF, ale wciąż wykazywała redundancję. Zależność ID_Czytelnika → ID_Bibliotekarza była przepuszczana przez 'furtkę' 3NF, ponieważ ID_Bibliotekarza jest atrybutem kluczowym (część drugiego klucza kandydującego).

Rozwiązanie BCNF polega na wyodrębnieniu tej zależności do osobnej tabeli Opiekunowie, gdzie ID_Czytelnika jest kluczem głównym, a ID_Bibliotekarza zwykłym atrybutem. W tabeli Przypisania_BCNF pozostaje tylko ID_Czytelnika (jako klucz obcy) wraz z ISBN i datą. Dzięki temu każdy czytelnik ma swój ID_Bibliotekarza przechowywany dokładnie raz, co eliminuje redundancję.

Warto podkreślić, że BCNF jest ostrzejszą wersją 3NF. Każda tabela w BCNF jest automatycznie w 3NF, ale nie odwrotnie. Różnica dotyczy tylko sytuacji z nakładającymi się kluczami kandydującymi, co w praktyce zdarza się stosunkowo rzadko. Większość tabel po 3NF jest już w BCNF – nasze tabele Czytelnicy, Miasta, Ksiazki i Wypozyczenia są w BCNF bez żadnych zmian.

Czwarta postać normalna rozwiązuje problem niezależnych list atrybutów – sytuacji, w której jeden czytelnik może mieć wiele numerów telefonów i wiele adresów email, a te listy są od siebie niezależne. W BCNF takie dane byłyby przechowywane w jednej tabeli, co prowadziłoby do efektu produktu kartezjańskiego: jeśli czytelnik ma 3 telefony i 2 emaile, w tabeli pojawiłoby się 6 wierszy (3 x 2).

Rozwiązaniem 4NF jest rozdzielenie każdej niezależnej listy do osobnej tabeli. Tabela Telefony przechowuje numery telefonów czytelnika (jeden wiersz na numer), a tabela Emaile przechowuje adresy email (jeden wiersz na adres). Dla czytelnika z 3 telefonami i 2 emailami będziemy mieć 3 wiersze w Telefony i 2 wiersze w Emaile - łącznie 5 wierszy zamiast 6, bez zbędnych kombinacji.

W nowej strukturze każda tabela zawiera również kolumnę TypTel/TypEmail, która określa rodzaj kontaktu (np. 'komórkowy', 'domowy', 'prywatny', 'służbowy'). To pozwala na elastyczne modelowanie danych kontaktowych bez utraty informacji. Klucz obcy ID_Czyt w obu tabelach łączy je z tabelą Czytelnicy, umożliwiając łatwe pobranie wszystkich kontaktów danego czytelnika.

Piąta postać normalna stanowi teoretyczne domknięcie procesu normalizacji relacyjnych baz danych. W praktyce 5NF rzadko wymaga zmian w schemacie, który jest już zgodny z 4NF. W naszym systemie bibliotecznym wszystkie osiem tabel po 4NF jest automatycznie w 5NF, ponieważ nie występują w nich zależności złączeniowe (JD) niewynikające z kluczy kandydujących.

Dla przypomnienia: zależność złączeniowa (JD) występuje, gdy relację można bezstratnie rozłożyć na trzy lub więcej projekcji. Typowym przykładem jest tabela (ISBN, Autor, ID_Bibliotekarza), gdzie każda para atrybutów jest niezależna. W naszym schemacie nie ma takiej sytuacji, ponieważ wszystkie relacje trójskładnikowe (jak Wypozyczenia łączące czytelnika, książkę i datę) mają dodatkowe atrybuty specyficzne dla całej trójki.

Osiem tabel w finalnym schemacie reprezentuje wszystkie encje i relacje w systemie bibliotecznym. Czytelnicy, Ksiazki i Miasta to tabele słownikowe przechowujące dane referencyjne. Wypozyczenia i Przypisania_BCNF to tabele zdarzeń. Opiekunowie, Telefony i Emaile to tabele atrybutów wielowartościowych. Każda z nich spełnia wszystkie wymagania 5NF, co gwarantuje brak anomalii przy operacjach INSERT, UPDATE i DELETE.

Mapa relacji między tabelami ukazuje architekturę całego systemu. Centralnym punktem jest tabela Czytelnicy, która łączy się z sześcioma innymi tabelami za pomocą kluczy obcych. To naturalne, ponieważ czytelnik jest kluczową encją w systemie bibliotecznym – to wokół niego koncentrują się wszystkie operacje: wypożyczenia, dane kontaktowe, opieka bibliotekarza i przypisania.

Druga pod względem liczby połączeń jest tabela Ksiazki, która łączy się z Wypozyczenia i Przypisania_BCNF. Miasta to słownik, który łączy się tylko z Czytelnikami. Wszystkie połączenia są typu jeden-do-wielu (N:1), z wyjątkiem Opiekunowie, które są jeden-do-jednego (1:1) – każdy czytelnik ma dokładnie jednego opiekuna-bibliotekarza.

Znajomość tych relacji jest kluczowa przy pisaniu zapytań SQL. Aby wyświetlić pełną informację o wypożyczeniu wraz z danymi czytelnika i książki, potrzebujemy JOIN łączący trzy tabele: Wypozyczenia JOIN Czytelnicy JOIN Ksiazki. Do pobrania kontaktów czytelnika potrzebujemy JOIN Czytelnicy LEFT JOIN Telefony LEFT JOIN Emaile. Każda relacja to potencjalny JOIN w zapytaniu.

Integralność referencyjna to mechanizm baz danych, który zapewnia, że relacje między tabelami są zawsze spójne. Gdy definiujemy klucz obcy, baza danych automatycznie sprawdza każdą operację INSERT, UPDATE i DELETE pod kątem naruszenia tej spójności. Nie można dodać wypożyczenia dla nieistniejącego czytelnika, ani usunąć czytelnika, który ma aktywne wypożyczenia.

W systemie bibliotecznym stosujemy następujące reguły integralności. Dla relacji Wypozyczenia → Czytelnicy oraz Wypozyczenia → Ksiazki używamy ON DELETE RESTRICT – nie pozwalamy usunąć czytelnika, który ma historię wypożyczeń. Dla relacji Telefony → Czytelnicy oraz Emaile → Czytelnicy używamy ON DELETE CASCADE – usunięcie czytelnika automatycznie usuwa jego dane kontaktowe. Dla Miasta → Czytelnicy używamy ON DELETE RESTRICT – nie można usunąć miasta, w którym mieszkają czytelnicy.

Dzięki integralności referencyjnej programiści nie muszą ręcznie sprawdzać spójności danych przy każdej operacji. Baza danych robi to automatycznie, co eliminuje całą klasę błędów związanych z osieroconymi rekordami (ang. orphan records). To jeden z najważniejszych powodów, dla których warto projektować bazę danych z prawidłowo zdefiniowanymi kluczami obcymi, a nie polegać wyłącznie na logice aplikacji.

Tabela Czytelnicy to podstawowa encja systemu, przechowująca dane wszystkich osób korzystających z biblioteki. Każdy czytelnik ma unikalny identyfikator numeryczny (ID_Czytelnika) oraz unikalny adres email. Email ma założone ograniczenie UNIQUE, co oznacza, że może służyć jako alternatywny klucz naturalny do identyfikacji czytelnika – żadnych dwóch czytelników nie może mieć tego samego adresu email.

Tabela Miasta to słownik (tabela referencyjna), która przechowuje pary (nazwa miasta, kod pocztowy). Dzięki wyodrębnieniu tej tabeli w 3NF unikamy powtarzania nazw miast i kodów pocztowych w każdym wierszu Czytelnicy. Jeśli miasto zmieni kod pocztowy, wystarczy zaktualizować jeden wiersz w Miasta, a wszyscy czytelnicy automatycznie 'odziedziczą' nowy kod.

Warto zwrócić uwagę, że tabela Czytelnicy zawiera również kolumny Telefon i Email, które przechowują główne dane kontaktowe. Szczegółowe dane kontaktowe (wiele telefonów, wiele emaili) są przechowywane w osobnych tabelach Telefony i Emaile (zgodnie z 4NF). To rozwiązanie typu 'podstawowe dane w głównej tabeli, dodatkowe w osobnych' jest powszechnie stosowane w praktyce.

Tabela Ksiazki stanowi katalog wszystkich książek dostępnych w bibliotece. Kluczem głównym jest ISBN (International Standard Book Number) – unikalny, międzynarodowy identyfikator książki. Użycie naturalnego klucza zamiast sztucznego ID ma tu sens, ponieważ ISBN jest globalnie unikalny, znany i używany w całym przemyśle wydawniczym. Nie ma ryzyka kolizji ani potrzeby tworzenia dodatkowego identyfikatora.

Kolumna Gatunek pozwala na kategoryzację książek według rodzaju literatury (np. powieść, poezja, dramat, literatura naukowa, poradnik). Kolumna RokWydania przechowuje rok publikacji, a Wydawnictwo nazwę wydawcy. Te trzy kolumny są opcjonalne – mogą pozostać puste, jeśli dane nie są dostępne. NULL w tych kolumnach oznacza 'brak danych', co jest lepszym rozwiązaniem niż wpisywanie wartości domyślnych, które mogłyby wprowadzać w błąd.

W tabeli Ksiazki zakładamy, że każda książka ma dokładnie jednego autora. W rzeczywistości wiele książek ma wielu autorów, co wymagałoby dodatkowej tabeli asocjacyjnej AutorzyKsiazek w bardziej złożonym modelu. Dla uproszczenia przykładu dydaktycznego przyjmujemy, że każda książka ma jednego głównego autora, co jest akceptowalnym uproszczeniem na potrzeby nauki normalizacji.

Tabela Wypozyczenia to centralna tabela zdarzeń w systemie bibliotecznym. Każdy wiersz reprezentuje jedno zdarzenie wypożyczenia – konkretny fakt oddania książki czytelnikowi w określonym dniu. Kluczem głównym jest sztuczny identyfikator ID_Wypozyczenia, ale istnieje również naturalny klucz alternatywny: para (ID_Czytelnika, ISBN, DataWyp) – ten sam czytelnik nie może wypożyczyć tej samej książki dwa razy tego samego dnia.

Kolumna DataZwrotu przechowuje datę zwrotu książki. Wartość NULL w tej kolumnie oznacza, że książka nie została jeszcze zwrócona. To typowy wzorzec w systemach bibliotecznych – NULL jako 'w trakcie'. Kara jest naliczana automatycznie na podstawie daty zwrotu i regulaminu biblioteki, a Status przyjmuje wartości: 'wypozyczona' (książka w rękach czytelnika, jeszcze przed terminem), 'zwrocona' (książka oddana w terminie) lub 'przetrzymana' (książka oddana po terminie).

W tabeli obowiązuje ważne ograniczenie biznesowe: DataZwrotu >= DataWyp (książka nie może być zwrócona przed wypożyczeniem). To ograniczenie może być wymuszone przez constraint CHECK w SQL: CHECK (DataZwrotu IS NULL OR DataZwrotu >= DataWyp). Podobnie Status powinien być spójny z DataZwrotu – jeśli DataZwrotu IS NOT NULL i DataZwrotu <= termin, Status = 'zwrocona'; jeśli po terminie, Status = 'przetrzymana'.

Tabele pomocnicze powstałe w BCNF i 4NF pełnią kluczową rolę w modelowaniu specyficznych aspektów systemu bibliotecznego. Opiekunowie przechowują informację o tym, który bibliotekarz opiekuje się którym czytelnikiem. Jest to relacja 1:1 – każdy czytelnik ma dokładnie jednego opiekuna, ale jeden bibliotekarz może opiekować się wieloma czytelnikami. ID_Czytelnika jest kluczem głównym, co gwarantuje unikalność.

Przypisania_BCNF rejestrują, który bibliotekarz przypisał którą książkę któremu czytelnikowi i kiedy. Klucz złożony (ID_Czytelnika, ISBN) zapewnia, że czytelnik nie może mieć dwóch przypisań tej samej książki. DataPrzypisania jest dodatkowym atrybutem opisującym to zdarzenie. W przeciwieństwie do Opiekunowie, ta tabela nie przechowuje ID_Bibliotekarza – został on przeniesiony do Opiekunowie w ramach dekompozycji BCNF.

Telefony i Emaile to tabele powstałe w 4NF do przechowywania niezależnych list kontaktów. Każdy czytelnik może mieć wiele numerów telefonów (różnych typów) i wiele adresów email. Rozdzielenie tych list do osobnych tabel eliminuje problem produktu kartezjańskiego, który występowałby, gdyby wszystkie dane kontaktowe były w jednej tabeli. TypTel i TypEmail pozwalają na kategoryzację kontaktów.

Podsumowując cały proces normalizacji systemu bibliotecznego, przeszliśmy długą drogę od jednej, chaotycznej tabeli z 17 kolumnami do ośmiu czystych, wyspecjalizowanych tabel spełniających wszystkie wymagania piątej postaci normalnej. Każdy krok normalizacji eliminował konkretny typ problemu: 1NF zapewniła atomowość, 2NF usunęła zależności częściowe, 3NF wyeliminowała zależności przechodnie, BCNF zamknęła furtkę nakładających się kluczy, 4NF rozdzieliła niezależne listy, a 5NF potwierdziła brak zależności złączeniowych.

Efektem końcowym jest baza danych, w której każdy fakt jest przechowywany dokładnie raz, co eliminuje redundancję i ryzyko niespójności. Operacje INSERT, UPDATE i DELETE są bezpieczne i nie powodują utraty danych. Relacje między tabelami są jawnie zdefiniowane za pomocą kluczy obcych, a integralność referencyjna jest automatycznie egzekwowana przez system bazy danych.

Znajomość tego schematu jest kluczowa przed przystąpieniem do implementacji w SQL. Pamiętaj o kolejności tworzenia tabel: najpierw tabele nadrzędne (Miasta, Czytelnicy, Ksiazki), potem zależne (Wypozyczenia, Opiekunowie, Przypisania_BCNF, Telefony, Emaile). Przy usuwaniu tabel kolejność jest odwrotna. Powodzenia w implementacji!

Dane w formacie CSV: Pliki CSV z danymi dla każdej tabeli znajdują się w katalogu csv/. Aby zaimportować je do bazy, zaloguj się do MariaDB, wybierz bazę biblioteka i wykonaj: LOAD DATA LOCAL INFILE 'csv/miasta.csv' INTO TABLE Miasta FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 ROWS; (powtórz dla każdej tabeli w odpowiedniej kolejności).