Zrozumienie procesu zbierania wymagań i przełożenie ich na jedną, płaską strukturę danych (formularz, arkusz kalkulacyjny), która stanowi punkt wyjścia do normalizacji. Identyfikacja powtarzających się grup danych.
Jesteś deweloperem, który otrzymał zadanie stworzenia prototypu bazy danych dla dziekanatu małej uczelni. Obecnie wszystkie dane dotyczące zapisów studentów na przedmioty i uzyskanych ocen są przechowywane w jednym, ogromnym arkuszu kalkulacyjnym, który z każdym semestrem staje się coraz trudniejszy w zarządzaniu. Dziekanat dostarczył Ci przykładowy wydruk, który zawiera wszystkie niezbędne informacje: dane studenta, jego kierunek i wydział, a także listę przedmiotów, na które jest zapisany, wraz z informacjami o prowadzącym dany przedmiot, jego stopniu naukowym, a także oceną końcową i datą jej wystawienia. Twoim pierwszym zadaniem nie jest jeszcze tworzenie bazy, ale dokładna analiza tego "dokumentu". Musisz zidentyfikować wszystkie pojedyncze informacje (atrybuty) i zaprojektować strukturę jednej, wielkiej tabeli, która odzwierciedlałaby ten arkusz. Celem jest stworzenie cyfrowej wersji tego, co istnieje na papierze, aby na jej podstawie móc zademonstrować problemy wynikające z braku organizacji danych. Musisz zastanowić się, jak przedstawić fakt, że jeden student może uczęszczać na wiele kursów, a jeden wykładowca może prowadzić wiele przedmiotów. Twoja analiza ma być podstawą do dalszych prac i pierwszym krokiem w uświadomieniu pracownikom dziekanatu, dlaczego profesjonalna baza danych jest im absolutnie niezbędna do dalszego funkcjonowania i rozwoju.
zapisy_na_przedmioty musiałyby znaleźć się wszystkie informacje o kursach, np. w formie tekstu: "KOD1, Nazwa1, ID_WYKŁ, Imie_Wykł, Nazwisko_Wykł, Stopień, Ocena, Data; KOD2, Nazwa2...".Postać nienormalizowana (0NF) jest najprostszą, ale i najgorszą formą przechowywania danych. Charakteryzuje się tym, że w jednej komórce (jednym atrybucie) próbujemy przechowywać wiele wartości, często o złożonej strukturze. Taka organizacja jest nieefektywna, trudna w przeszukiwaniu i prowadzi do wielu problemów, zwanych anomaliami, które będziemy rozwiązywać w kolejnych krokach. Jest to jednak ważny punkt wyjścia, ponieważ odzwierciedla sposób, w jaki ludzie często organizują dane w prostych narzędziach, takich jak arkusze kalkulacyjne.
Zidentyfikowałeś wszystkie niezbędne atrybuty i stworzyłeś koncepcyjny model tabeli w postaci nienormalizowanej. Zrozumiałeś, na czym polega problem atrybutów wielowartościowych i grup powtarzających się, co stanowi idealny wstęp do wprowadzenia pierwszej postaci normalnej.
Praktyczne stworzenie w bazie danych tabeli w postaci nienormalizowanej oraz zasilenie jej danymi w celu namacalnego zademonstrowania problemów z redundancją i anomaliami (aktualizacji, wstawiania, usuwania).
Po analizie teoretycznej nadszedł czas na praktykę. Twoim zadaniem jest stworzenie w systemie MariaDB nowej bazy danych o nazwie uczelnia_0nf i zaimplementowanie w niej zaprojektowanej wcześniej, nienormalizowanej tabeli. Aby uniknąć przechowywania wielu wartości w jednej komórce, co jest trudne w SQL, pójdziesz na pewien kompromis: dla każdego zapisu studenta na przedmiot stworzysz osobny wiersz, ale będziesz w nim powtarzać wszystkie dane studenta, kierunku i wydziału. Stworzysz więc jedną, bardzo szeroką tabelę o nazwie rejestr_ocen. Po zdefiniowaniu jej struktury, wstawisz do niej kilka przykładowych rekordów, które wyraźnie pokażą istniejące problemy. Na przykład, zapiszesz Jana Kowalskiego na trzy różne przedmioty, co spowoduje, że jego imię, nazwisko, email, kierunek i dane wydziału zostaną powtórzone trzy razy. Następnie, na podstawie tej tabeli, masz za zadanie przygotować dla dziekanatu krótką analizę. Musisz odpowiedzieć na pytania: co się stanie, jeśli Jan Kowalski zmieni adres e-mail? W ilu miejscach trzeba będzie dokonać zmiany? Co jeśli zechcemy dodać nowego studenta, który jeszcze nie zapisał się na żaden przedmiot? Czy będzie to możliwe? A co się stanie, gdy usuniemy ostatni wpis o studencie, który był zapisany tylko na jeden przedmiot? Czy stracimy o nim wszystkie informacje? Twoja demonstracja ma być ostatecznym dowodem na to, że obecna struktura jest nie do przyjęcia.
CREATE DATABASE uczelnia_0nf CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci;
rejestr_ocen.USE uczelnia_0nf;
CREATE TABLE rejestr_ocen (
nr_albumu INT,
imie_studenta VARCHAR(50),
nazwisko_studenta VARCHAR(50),
email_studenta VARCHAR(100),
nazwa_kierunku VARCHAR(100),
nazwa_wydzialu VARCHAR(100),
adres_wydzialu VARCHAR(150),
kod_przedmiotu VARCHAR(10),
nazwa_przedmiotu VARCHAR(100),
id_wykladowcy INT,
imie_wykladowcy VARCHAR(50),
nazwisko_wykladowcy VARCHAR(50),
stopien_naukowy VARCHAR(50),
ocena DECIMAL(2,1),
data_wystawienia DATE
);
INSERT INTO rejestr_ocen VALUES (12345, 'Jan', 'Kowalski', 'j.kowalski@student.pl', 'Informatyka', 'Wydział Informatyki i Zarządzania', 'ul. Akademicka 1, Wrocław', 'INF01', 'Bazy Danych', 1, 'Adam', 'Nowak', 'dr hab.', 5.0, '2023-06-20'), (12345, 'Jan', 'Kowalski', 'j.kowalski@student.pl', 'Informatyka', 'Wydział Informatyki i Zarządzania', 'ul. Akademicka 1, Wrocław', 'INF02', 'Sieci Komputerowe', 2, 'Anna', 'Zielińska', 'prof. dr hab.', 4.5, '2023-06-22'), (54321, 'Anna', 'Nowakowska', 'a.nowakowska@student.pl', 'Informatyka', 'Wydział Informatyki i Zarządzania', 'ul. Akademicka 1, Wrocław', 'INF01', 'Bazy Danych', 1, 'Adam', 'Nowak', 'dr hab.', 4.0, '2023-06-20');
-- Poprawna aktualizacja wymaga zmiany w wielu miejscach UPDATE rejestr_ocen SET email_studenta = 'jan.kowalski@nowy.pl' WHERE nr_albumu = 12345;
-- Usunięcie tego wiersza kasuje wszystkie dane o Annie Nowakowskiej! DELETE FROM rejestr_ocen WHERE nr_albumu = 54321 AND kod_przedmiotu = 'INF01';
Praktycznie zademonstrowałeś, że przechowywanie wszystkich danych w jednej tabeli prowadzi do masowej redundancji i jest źródłem poważnych problemów (anomalii). Masz teraz solidne argumenty, aby rozpocząć proces normalizacji i uporządkować tę strukturę.
Zrozumienie koncepcji atomowości atrybutów oraz przeprowadzenie pierwszego kroku normalizacji, czyli przekształcenia tabeli z postaci 0NF do pierwszej postaci normalnej (1NF) poprzez eliminację atrybutów wielowartościowych.
Po udanej demonstracji problemów z jedną, wielką tabelą, dziekanat jest przekonany o konieczności zmian. Twój przełożony wyjaśnia Ci pierwszą, fundamentalną zasadę projektowania baz danych: zasadę atomowości. Mówi ona, że każda komórka w tabeli powinna zawierać tylko jedną, niepodzielną informację. Patrząc na pierwotny projekt z zadania 1, gdzie w jednej komórce miały znaleźć się dane o wszystkich kursach studenta, od razu widzisz naruszenie tej zasady. Twoim zadaniem jest teraz formalne zdefiniowanie, czym jest pierwsza postać normalna (1NF) i jak ją osiągnąć. Musisz wziąć pierwotną, nienormalizowaną strukturę i rozbić ją w taki sposób, aby każda komórka zawierała dokładnie jedną wartość. W praktyce oznacza to, że zamiast jednego wiersza dla studenta z listą kursów, stworzysz wiele wierszy dla tego samego studenta – po jednym dla każdego kursu, na który jest zapisany. To jest dokładnie to, co intuicyjnie zrobiłeś w zadaniu 2, tworząc tabelę rejestr_ocen. Teraz musisz jednak sformalizować ten proces. Musisz zdefiniować klucz główny dla tej nowej tabeli. Szybko orientujesz się, że sam numer albumu studenta nie wystarczy, bo się powtarza. Podobnie kod przedmiotu. Musisz więc stworzyć klucz złożony, który jednoznacznie zidentyfikuje każdy wiersz w tabeli.
rejestr_ocen z poprzedniego zadania. Potwierdź, że wszystkie jej atrybuty są atomowe (każda komórka przechowuje jedną wartość). Tabela ta już spełnia warunek atomowości.Tabela znajduje się w pierwszej postaci normalnej (1NF), jeśli spełnia dwa podstawowe warunki:
rejestr_ocen. Sam nr_albumu się powtarza. Sam kod_przedmiotu też może się powtórzyć (różni studenci na tym samym przedmiocie). Unikalną kombinacją, która identyfikuje konkretny zapis na kurs, jest para (nr_albumu, kod_przedmiotu).uczelnia_1nf i odtwórz w niej tabelę, tym razem definiując złożony klucz główny.CREATE DATABASE uczelnia_1nf CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci;
USE uczelnia_1nf;
CREATE TABLE rejestr_ocen_1nf (
nr_albumu INT,
imie_studenta VARCHAR(50),
nazwisko_studenta VARCHAR(50),
email_studenta VARCHAR(100),
nazwa_kierunku VARCHAR(100),
nazwa_wydzialu VARCHAR(100),
adres_wydzialu VARCHAR(150),
kod_przedmiotu VARCHAR(10),
nazwa_przedmiotu VARCHAR(100),
id_wykladowcy INT,
imie_wykladowcy VARCHAR(50),
nazwisko_wykladowcy VARCHAR(50),
stopien_naukowy VARCHAR(50),
ocena DECIMAL(2,1),
data_wystawienia DATE,
PRIMARY KEY (nr_albumu, kod_przedmiotu)
);
INSERT INTO rejestr_ocen_1nf VALUES (12345, 'Jan', 'Kowalski', 'j.kowalski@student.pl', 'Informatyka', 'Wydział Informatyki i Zarządzania', 'ul. Akademicka 1, Wrocław', 'INF01', 'Bazy Danych', 1, 'Adam', 'Nowak', 'dr hab.', 5.0, '2023-06-20'), (12345, 'Jan', 'Kowalski', 'j.kowalski@student.pl', 'Informatyka', 'Wydział Informatyki i Zarządzania', 'ul. Akademicka 1, Wrocław', 'INF02', 'Sieci Komputerowe', 2, 'Anna', 'Zielińska', 'prof. dr hab.', 4.5, '2023-06-22'), (54321, 'Anna', 'Nowakowska', 'a.nowakowska@student.pl', 'Informatyka', 'Wydział Informatyki i Zarządzania', 'ul. Akademicka 1, Wrocław', 'INF01', 'Bazy Danych', 1, 'Adam', 'Nowak', 'dr hab.', 4.0, '2023-06-20');
Pierwsza postać normalna (1NF) to fundamentalny krok w projektowaniu relacyjnych baz danych. Wymusza ona usunięcie grup powtarzających się i atrybutów wielowartościowych. Osiąga się to poprzez "spłaszczenie" struktury, tak aby każdy wiersz i każda kolumna zawierały pojedynczą, atomową wartość. Kluczową częścią tego procesu jest zdefiniowanie klucza głównego, który jednoznacznie identyfikuje każdy wiersz. W przypadku, gdy żaden pojedynczy atrybut nie jest unikalny, musimy stworzyć klucz złożony (kompozytowy) z dwóch lub więcej atrybutów.
Przekształciłeś nienormalizowaną strukturę do pierwszej postaci normalnej. Tabela ma teraz atomowe wartości i zdefiniowany złożony klucz główny. Mimo to, wciąż obserwujesz te same anomalie co poprzednio (redundancja, problemy z aktualizacją, wstawianiem i usuwaniem), co dowodzi, że 1NF to dopiero początek drogi do dobrze zaprojektowanej bazy.
Zrozumienie pojęcia zależności funkcyjnej, która jest teoretyczną podstawą do dalszej normalizacji. Nauczenie się, jak identyfikować pełne i częściowe zależności funkcyjne w tabeli posiadającej złożony klucz główny.
Twój przełożony jest zadowolony z postępów, ale wskazuje, że kluczem do dalszej optymalizacji jest zrozumienie, jak dane są od siebie zależne. Wprowadza Cię w pojęcie "zależności funkcyjnej", tłumacząc, że jest to relacja między atrybutami, gdzie wartość jednego atrybutu (lub grupy atrybutów) determinuje wartość innego. Twoim zadaniem jest teraz wcielenie się w rolę analityka danych i dokładne zbadanie tabeli rejestr_ocen_1nf pod kątem tych zależności. Musisz wziąć na warsztat złożony klucz główny, czyli parę (nr_albumu, kod_przedmiotu), i przeanalizować wszystkie pozostałe atrybuty. Dla każdego z nich musisz odpowiedzieć na pytanie: czy jego wartość zależy od całego klucza złożonego, czy tylko od jego części? Na przykład, czy nazwisko_studenta zależy od tego, na jaki przedmiot się zapisał, czy wystarczy nam sam nr_albumu, aby je poznać? A jak jest z nazwa_przedmiotu? Czy zależy ona od studenta, czy tylko od kodu przedmiotu? Twoim zadaniem jest stworzenie formalnej listy wszystkich zidentyfikowanych zależności funkcyjnych i podzielenie ich na dwie grupy: te, które są "pełne" (zależą od całego klucza) i te, które są "częściowe" (zależą tylko od fragmentu klucza). Ta analiza jest absolutnie krytyczna, ponieważ to właśnie częściowe zależności są przyczyną anomalii w tabeli 1NF i to ich eliminacja jest celem drugiej postaci normalnej.
rejestr_ocen_1nf i jej klucz główny: PRIMARY KEY (nr_albumu, kod_przedmiotu).nr_albumu:
nr_albumu, znamy imie_studenta? Tak.nr_albumu, znamy nazwisko_studenta? Tak.email_studenta, nazwa_kierunku, nazwa_wydzialu, adres_wydzialu.Są to zależności częściowe, ponieważ te atrybuty zależą tylko od części klucza głównego.
kod_przedmiotu:
kod_przedmiotu, znamy nazwa_przedmiotu? Tak.kod_przedmiotu, znamy id_wykladowcy, imie_wykladowcy, nazwisko_wykladowcy, stopien_naukowy? Tak (zakładając, że jeden przedmiot prowadzi jeden wykładowca).To również są zależności częściowe.
ocena zależy tylko od studenta? Nie. Czy tylko od przedmiotu? Nie. Zależy od konkretnego studenta i konkretnego przedmiotu.data_wystawienia.Są to zależności pełne.
Analiza zależności funkcyjnych to formalny proces odkrywania reguł rządzących danymi. W kontekście normalizacji, kluczowe jest rozróżnienie między zależnościami częściowymi a pełnymi. Zależność częściowa występuje, gdy atrybut niekluczowy zależy tylko od fragmentu złożonego klucza głównego. To właśnie te zależności są bezpośrednią przyczyną redundancji i anomalii w tabeli 1NF. Na przykład, dane studenta powtarzają się w każdym wierszu jego zapisów, ponieważ zależą tylko od nr_albumu, a nie od całego klucza (nr_albumu, kod_przedmiotu).
Zidentyfikowałeś i sklasyfikowałeś wszystkie zależności funkcyjne w tabeli. Odkryłeś istnienie licznych zależności częściowych, które są źródłem problemów. Ta analiza jest mapą drogową do osiągnięcia drugiej postaci normalnej, która polega na wyeliminowaniu tych właśnie zależności.
Praktyczne zastosowanie wiedzy o zależnościach częściowych do dekompozycji (podziału) tabeli 1NF na kilka mniejszych, połączonych relacjami tabel, które spełniają wymogi drugiej postaci normalnej (2NF).
Twoja analiza zależności funkcyjnych okazała się strzałem w dziesiątkę. Teraz dokładnie wiesz, które dane są ze sobą powiązane i dlaczego w tabeli panuje bałagan. Nadszedł czas na wielkie porządki. Twoim zadaniem jest przeprowadzenie dekompozycji bezstratnej, czyli podziału jednej, wielkiej tabeli rejestr_ocen_1nf na kilka mniejszych, wyspecjalizowanych tabel. Każda zidentyfikowana w poprzednim kroku zależność częściowa ma stać się podstawą do stworzenia nowej tabeli. Musisz więc stworzyć osobną tabelę tylko na dane studentów, gdzie kluczem głównym będzie nr_albumu. Następnie, osobną tabelę na dane o przedmiotach i ich wykładowcach, gdzie kluczem głównym będzie kod_przedmiotu. Na końcu pozostanie tabela łącząca, która będzie przechowywać tylko te informacje, które zależą od obu kluczy – czyli oceny. Musisz starannie zaprojektować struktury tych nowych tabel, zdefiniować w nich klucze główne, a co najważniejsze, połączyć je ze sobą za pomocą kluczy obcych. Twoim celem jest stworzenie nowej struktury bazy danych, w której informacja o Janie Kowalskim będzie zapisana tylko w jednym miejscu, informacja o przedmiocie "Bazy Danych" również tylko w jednym miejscu, a anomalie, z którymi walczyłeś do tej pory, znikną.
uczelnia_2nf.CREATE DATABASE uczelnia_2nf CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci; USE uczelnia_2nf;
Studenci.CREATE TABLE Studenci (
nr_albumu INT PRIMARY KEY,
imie_studenta VARCHAR(50),
nazwisko_studenta VARCHAR(50),
email_studenta VARCHAR(100),
nazwa_kierunku VARCHAR(100),
nazwa_wydzialu VARCHAR(100),
adres_wydzialu VARCHAR(150)
);
Przedmioty.CREATE TABLE Przedmioty (
kod_przedmiotu VARCHAR(10) PRIMARY KEY,
nazwa_przedmiotu VARCHAR(100),
id_wykladowcy INT,
imie_wykladowcy VARCHAR(50),
nazwisko_wykladowcy VARCHAR(50),
stopien_naukowy VARCHAR(50)
);
Oceny, która przechowa atrybuty w pełni zależne od klucza złożonego.CREATE TABLE Oceny (
nr_albumu INT,
kod_przedmiotu VARCHAR(10),
ocena DECIMAL(2,1),
data_wystawienia DATE,
PRIMARY KEY (nr_albumu, kod_przedmiotu),
FOREIGN KEY (nr_albumu) REFERENCES Studenci(nr_albumu),
FOREIGN KEY (kod_przedmiotu) REFERENCES Przedmioty(kod_przedmiotu)
);
Tabela znajduje się w drugiej postaci normalnej (2NF), jeśli spełnia następujące warunki:
Oznacza to, że w tabeli ze złożonym kluczem głównym nie mogą istnieć żadne zależności częściowe, czyli sytuacje, w których atrybut niekluczowy zależy tylko od fragmentu klucza głównego.
Druga postać normalna (2NF) wymaga, aby tabela była w 1NF i aby wszystkie atrybuty niekluczowe były w pełni funkcyjnie zależne od całego klucza głównego. Oznacza to, że musimy wyeliminować wszystkie zależności częściowe. Robimy to poprzez dekompozycję: tworzymy nowe tabele dla każdej grupy atrybutów zależnych od tej samej części klucza. Oryginalna tabela zostaje zredukowana do tabeli łączącej (asocjacyjnej), która zawiera klucz złożony i tylko te atrybuty, które zależą od niego w całości. Klucze obce (FOREIGN KEY) zapewniają integralność referencyjną, czyli utrzymanie logicznych powiązań między nowo powstałymi tabelami.
Pomyślnie przekształciłeś bazę danych do drugiej postaci normalnej. Wyeliminowałeś zależności częściowe, co znacząco zredukowało redundancję danych. Zmiana e-maila studenta wymaga teraz modyfikacji tylko jednego rekordu w tabeli Studenci. Jednakże, w nowo powstałych tabelach mogą czaić się kolejne, bardziej subtelne problemy.
Migracja danych z tabeli 1NF do nowo utworzonych struktur 2NF. Praktyczna weryfikacja, czy anomalie aktualizacji, wstawiania i usuwania, które występowały wcześniej, zostały wyeliminowane.
Nowa, lepsza struktura bazy danych jest już gotowa, ale na razie jest pusta. Twoim zadaniem jest teraz przeniesienie danych ze starej, wielkiej tabeli rejestr_ocen_1nf do trzech nowych, wyspecjalizowanych tabel: Studenci, Przedmioty i Oceny. Musisz napisać serię zapytań INSERT INTO ... SELECT, które w inteligentny sposób skopiują odpowiednie kolumny do nowych lokalizacji. Musisz przy tym uważać na duplikaty – na przykład dane Jana Kowalskiego powinny zostać wstawione do tabeli Studenci tylko raz, mimo że w tabeli źródłowej występowały wielokrotnie. Po pomyślnej migracji danych, musisz przeprowadzić serię testów, które udowodnią, że normalizacja do 2NF rozwiązała dotychczasowe problemy. Spróbujesz ponownie zmienić e-mail Jana Kowalskiego i sprawdzisz, czy wystarczy do tego modyfikacja jednego rekordu. Następnie dodasz nowego studenta, który nie jest jeszcze zapisany na żaden przedmiot, co wcześniej było niemożliwe. Na koniec, usuniesz jedyną ocenę Anny Nowakowskiej i zweryfikujesz, czy jej dane wciąż istnieją w tabeli Studenci, co potwierdzi, że nie tracisz już informacji o studentach przy usuwaniu ich zapisów na kursy. Twoje testy mają być ostatecznym dowodem na skuteczność przeprowadzonej normalizacji.
Studenci unikalnymi danymi studentów z tabeli rejestr_ocen_1nf. Użyj SELECT DISTINCT, aby uniknąć duplikatów.INSERT INTO uczelnia_2nf.Studenci (nr_albumu, imie_studenta, nazwisko_studenta, email_studenta, nazwa_kierunku, nazwa_wydzialu, adres_wydzialu) SELECT DISTINCT nr_albumu, imie_studenta, nazwisko_studenta, email_studenta, nazwa_kierunku, nazwa_wydzialu, adres_wydzialu FROM uczelnia_1nf.rejestr_ocen_1nf;
Przedmioty unikalnymi danymi przedmiotów.INSERT INTO uczelnia_2nf.Przedmioty (kod_przedmiotu, nazwa_przedmiotu, id_wykladowcy, imie_wykladowcy, nazwisko_wykladowcy, stopien_naukowy) SELECT DISTINCT kod_przedmiotu, nazwa_przedmiotu, id_wykladowcy, imie_wykladowcy, nazwisko_wykladowcy, stopien_naukowy FROM uczelnia_1nf.rejestr_ocen_1nf;
Oceny danymi o ocenach.INSERT INTO uczelnia_2nf.Oceny (nr_albumu, kod_przedmiotu, ocena, data_wystawienia) SELECT nr_albumu, kod_przedmiotu, ocena, data_wystawienia FROM uczelnia_1nf.rejestr_ocen_1nf;
UPDATE uczelnia_2nf.Studenci SET email_studenta = 'jan.kowalski@nowy.pl' WHERE nr_albumu = 12345;
INSERT INTO uczelnia_2nf.Studenci (nr_albumu, imie_studenta, nazwisko_studenta, email_studenta, nazwa_kierunku) VALUES (99999, 'Piotr', 'Nowicki', 'p.nowicki@student.pl', 'Socjologia');
Studenci.DELETE FROM uczelnia_2nf.Oceny WHERE nr_albumu = 54321; -- Sprawdzenie: ten rekord wciąż istnieje SELECT * FROM uczelnia_2nf.Studenci WHERE nr_albumu = 54321;
Pomyślnie zmigrowałeś dane do struktury 2NF i udowodniłeś, że główne anomalie zostały wyeliminowane. Redundancja danych jest znacznie mniejsza, a operacje na danych są bezpieczniejsze i bardziej logiczne. Baza danych jest w znacznie lepszym stanie, ale wciąż może kryć w sobie pewne ukryte wady.
Zrozumienie koncepcji zależności przechodniej (transitive dependency), która jest przyczyną anomalii w niektórych tabelach 2NF, oraz nauczenie się, jak ją identyfikować.
Jesteś dumny z osiągnięcia drugiej postaci normalnej. Baza danych wygląda o wiele czyściej, a podstawowe problemy zniknęły. Pokazujesz wyniki swojemu przełożonemu, który chwali Twoją pracę, ale od razu zwraca uwagę na kolejny, bardziej subtelny problem. Prosi Cię, abyś przyjrzał się tabeli Studenci. Wskazuje, że chociaż wszystkie atrybuty w tej tabeli zależą od klucza głównego, czyli nr_albumu, to niektóre z nich zależą od siebie nawzajem. Zadaje Ci pytanie: czy adres_wydzialu zależy bezpośrednio od studenta? A może jest tak, że student jest przypisany do kierunku, kierunek do wydziału, a dopiero wydział ma swój adres? To jest właśnie "zależność przechodnia". Twoim zadaniem jest ponowne wcielenie się w rolę analityka i zbadanie tabel Studenci oraz Przedmioty w poszukiwaniu takich ukrytych, pośrednich zależności. Musisz zidentyfikować atrybuty niekluczowe, które zamiast zależeć bezpośrednio od klucza głównego, zależą od innego atrybutu niekluczowego. Ta analiza jest niezbędna do osiągnięcia trzeciej postaci normalnej, która jest uważana za standard dla większości dobrze zaprojektowanych baz danych.
Studenci. Kluczem głównym jest nr_albumu.nr_albumu -> nazwa_kierunku (Student jest na jednym kierunku)nazwa_kierunku -> nazwa_wydzialu (Kierunek należy do jednego wydziału)nazwa_wydzialu -> adres_wydzialu (Wydział ma jeden adres)nr_albumu determinuje nazwa_kierunku, a nazwa_kierunku determinuje nazwa_wydzialu. Oznacza to, że nazwa_wydzialu zależy od klucza głównego nr_albumu w sposób przechodni (tranzytywny). To samo dotyczy adres_wydzialu.Przedmioty. Kluczem głównym jest kod_przedmiotu.kod_przedmiotu -> id_wykladowcy (Przedmiot jest prowadzony przez jednego wykładowcę)id_wykladowcy -> imie_wykladowcy, nazwisko_wykladowcy, stopien_naukowy (ID wykładowcy jednoznacznie go identyfikuje)kod_przedmiotu nie bezpośrednio, ale poprzez atrybut niekluczowy id_wykladowcy.Zależność przechodnia (tranzytywna) występuje, gdy atrybut niekluczowy A zależy od atrybutu niekluczowego B, a atrybut B zależy od klucza głównego tabeli. Taka sytuacja wciąż prowadzi do anomalii. Na przykład, jeśli zmienimy nazwę wydziału, musimy to zrobić we wszystkich rekordach studentów z tego wydziału. Nie możemy też dodać nowego wydziału, dopóki nie przypiszemy do niego co najmniej jednego kierunku (i studenta). Trzecia postać normalna (3NF) ma na celu wyeliminowanie właśnie tych zależności.
Zidentyfikowałeś zależności przechodnie w obu głównych tabelach. Zrozumiałeś, że nawet po osiągnięciu 2NF baza danych może wciąż zawierać ukryte wady projektowe. Jesteś gotowy do ostatniego, kluczowego kroku normalizacji – osiągnięcia trzeciej postaci normalnej.
Praktyczne wyeliminowanie zależności przechodnich poprzez dalszą dekompozycję tabel 2NF, co doprowadzi do uzyskania struktury bazy danych w trzeciej postaci normalnej (3NF).
Uzbrojony w wiedzę o zależnościach przechodnich, przystępujesz do finalnego etapu projektowania schematu bazy danych. Twoim zadaniem jest teraz ostateczne "posprzątanie" struktury poprzez rozbicie tabel, w których zidentyfikowałeś te zależności. Musisz wziąć na warsztat tabelę Studenci i wydzielić z niej osobne byty: Wydziały i Kierunki. Stworzysz więc nową tabelę na wydziały, która będzie przechowywać tylko ich nazwy i adresy. Następnie tabelę na kierunki, która połączy kierunek z wydziałem za pomocą klucza obcego. W tabeli Studenci pozostanie już tylko odwołanie do kierunku, na którym studiuje dany student. Analogiczną operację musisz przeprowadzić dla tabeli Przedmioty. Wydzielisz z niej nową, niezależną tabelę Wykładowcy, która będzie zawierać wszystkie informacje o prowadzących. W tabeli Przedmioty pozostanie jedynie klucz obcy wskazujący na wykładowcę prowadzącego dany przedmiot. Po tej operacji każda tabela w Twojej bazie danych będzie opisywać tylko jeden, konkretny byt (studentów, wykładowców, przedmioty, wydziały), a wszystkie atrybuty niekluczowe będą zależeć tylko od klucza głównego, bezpośrednio i nieprzechodnio.
uczelnia_3nf.CREATE DATABASE uczelnia_3nf CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci; USE uczelnia_3nf;
Studenci z 2NF. Stwórz tabelę Wydzialy.CREATE TABLE Wydzialy (
id_wydzialu INT AUTO_INCREMENT PRIMARY KEY,
nazwa_wydzialu VARCHAR(100) UNIQUE,
adres_wydzialu VARCHAR(150)
);
Kierunki, powiązaną z wydziałami.CREATE TABLE Kierunki (
id_kierunku INT AUTO_INCREMENT PRIMARY KEY,
nazwa_kierunku VARCHAR(100) UNIQUE,
id_wydzialu INT,
FOREIGN KEY (id_wydzialu) REFERENCES Wydzialy(id_wydzialu)
);
Studenci, aby zawierała tylko dane o studentach i klucz obcy do kierunku.CREATE TABLE Studenci (
nr_albumu INT PRIMARY KEY,
imie_studenta VARCHAR(50),
nazwisko_studenta VARCHAR(50),
email_studenta VARCHAR(100),
id_kierunku INT,
FOREIGN KEY (id_kierunku) REFERENCES Kierunki(id_kierunku)
);
Przedmioty z 2NF. Stwórz tabelę Wykladowcy.CREATE TABLE Wykladowcy (
id_wykladowcy INT PRIMARY KEY,
imie_wykladowcy VARCHAR(50),
nazwisko_wykladowcy VARCHAR(50),
stopien_naukowy VARCHAR(50)
);
Przedmioty.CREATE TABLE Przedmioty (
kod_przedmiotu VARCHAR(10) PRIMARY KEY,
nazwa_przedmiotu VARCHAR(100),
id_wykladowcy INT,
FOREIGN KEY (id_wykladowcy) REFERENCES Wykladowcy(id_wykladowcy)
);
Oceny, dostosowując klucze obce do nowych tabel.CREATE TABLE Oceny (
nr_albumu INT,
kod_przedmiotu VARCHAR(10),
ocena DECIMAL(2,1),
data_wystawienia DATE,
PRIMARY KEY (nr_albumu, kod_przedmiotu),
FOREIGN KEY (nr_albumu) REFERENCES Studenci(nr_albumu),
FOREIGN KEY (kod_przedmiotu) REFERENCES Przedmioty(kod_przedmiotu)
);
Tabela znajduje się w trzeciej postaci normalnej (3NF), jeśli spełnia następujące warunki:
Oznacza to, że każdy atrybut niekluczowy musi zależeć bezpośrednio i wyłącznie od klucza głównego, a nie od innego atrybutu niekluczowego.
Trzecia postać normalna (3NF) wymaga, aby tabela była w 2NF i aby nie istniały w niej żadne zależności przechodnie. Oznacza to, że każdy atrybut niekluczowy musi zależeć bezpośrednio od klucza głównego, a nie od innego atrybutu niekluczowego. Osiągamy to poprzez wydzielenie grup atrybutów tworzących zależność przechodnią do osobnych tabel. W rezultacie otrzymujemy schemat bazy danych, który jest wysoce znormalizowany, elastyczny i odporny na większość typowych anomalii.
Zaprojektowałeś ostateczny, znormalizowany do 3NF schemat bazy danych. Składa się on z wielu małych, wyspecjalizowanych tabel, połączonych siecią relacji. Taka struktura minimalizuje redundancję, eliminuje anomalie i stanowi solidny fundament pod budowę aplikacji systemu dziekanatowego.
Zasilenie danymi w pełni znormalizowanej struktury 3NF, co wymaga wykonania wieloetapowej migracji z tabel 2NF i poprawnego uzupełnienia kluczy obcych.
Stworzyłeś idealną, znormalizowaną strukturę, ale jest ona pusta. Twoim zadaniem jest teraz przeprowadzenie finalnej migracji danych ze schematu 2NF do schematu 3NF. To zadanie jest bardziej złożone niż poprzednie, ponieważ musisz wypełnić danymi kilka tabel w odpowiedniej kolejności, dbając o zachowanie relacji. Najpierw musisz wyodrębnić unikalne dane wydziałów i wstawić je do tabeli Wydzialy. Następnie, korzystając z nowo wygenerowanych identyfikatorów wydziałów, musisz wypełnić tabelę Kierunki. Podobnie postąpisz z wykładowcami – najpierw zasilisz tabelę Wykladowcy, a potem, używając ich ID, uzupełnisz tabelę Przedmioty. Dopiero mając przygotowane te tabele "słownikowe", będziesz mógł wypełnić główną tabelę Studenci, poprawnie przypisując każdego studenta do jego kierunku. Na samym końcu przeniesiesz dane do tabeli Oceny. Ten wieloetapowy proces migracji danych jest typowym zadaniem w projektach refaktoryzacji baz danych i wymaga starannego planowania oraz precyzyjnego wykonywania zapytań SQL.
Wydzialy unikalnymi danymi z tabeli uczelnia_2nf.Studenci.INSERT INTO uczelnia_3nf.Wydzialy (nazwa_wydzialu, adres_wydzialu) SELECT DISTINCT nazwa_wydzialu, adres_wydzialu FROM uczelnia_2nf.Studenci;
Kierunki, łącząc dane z tabel uczelnia_2nf.Studenci i uczelnia_3nf.Wydzialy, aby pobrać id_wydzialu.INSERT INTO uczelnia_3nf.Kierunki (nazwa_kierunku, id_wydzialu) SELECT DISTINCT s.nazwa_kierunku, w.id_wydzialu FROM uczelnia_2nf.Studenci s JOIN uczelnia_3nf.Wydzialy w ON s.nazwa_wydzialu = w.nazwa_wydzialu;
Wykladowcy.INSERT INTO uczelnia_3nf.Wykladowcy (id_wykladowcy, imie_wykladowcy, nazwisko_wykladowcy, stopien_naukowy) SELECT DISTINCT id_wykladowcy, imie_wykladowcy, nazwisko_wykladowcy, stopien_naukowy FROM uczelnia_2nf.Przedmioty;
Przedmioty.INSERT INTO uczelnia_3nf.Przedmioty (kod_przedmiotu, nazwa_przedmiotu, id_wykladowcy) SELECT DISTINCT kod_przedmiotu, nazwa_przedmiotu, id_wykladowcy FROM uczelnia_2nf.Przedmioty;
Studenci.INSERT INTO uczelnia_3nf.Studenci (nr_albumu, imie_studenta, nazwisko_studenta, email_studenta, id_kierunku) SELECT DISTINCT s.nr_albumu, s.imie_studenta, s.nazwisko_studenta, s.email_studenta, k.id_kierunku FROM uczelnia_2nf.Studenci s JOIN uczelnia_3nf.Kierunki k ON s.nazwa_kierunku = k.nazwa_kierunku;
Oceny.INSERT INTO uczelnia_3nf.Oceny (nr_albumu, kod_przedmiotu, ocena, data_wystawienia) SELECT nr_albumu, kod_przedmiotu, ocena, data_wystawienia FROM uczelnia_2nf.Oceny;
Przeprowadziłeś skomplikowaną, wieloetapową migrację danych do w pełni znormalizowanej struktury. Zrozumiałeś, jak ważna jest kolejność operacji i jak używać złączeń do uzupełniania kluczy obcych. Baza danych jest teraz nie tylko dobrze zaprojektowana, ale również wypełniona spójnymi danymi.
Potwierdzenie, że proces normalizacji był bezstratny. Nauczenie się, jak za pomocą zapytań JOIN odtworzyć pierwotny, płaski widok danych z nowej, znormalizowanej struktury składającej się z wielu tabel.
Proces normalizacji został zakończony. Dziekan jest pod wrażeniem nowej, eleganckiej struktury, ale ma jedną, kluczową obawę: czy w trakcie tego całego podziału na małe tabelki nie utracono jakichś informacji? Czy nadal będzie mógł uzyskać raport, który wygląda dokładnie tak, jak jego stary, wielki arkusz kalkulacyjny, zawierający wszystkie informacje w jednym wierszu? Twoim zadaniem jest rozwianie tych wątpliwości. Musisz napisać jedno, duże zapytanie SQL, które połączy ze sobą wszystkie nowo utworzone tabele (Studenci, Kierunki, Wydzialy, Oceny, Przedmioty, Wykladowcy) i odtworzy pierwotny, denormalizowany widok danych. Wynik Twojego zapytania ma mieć dokładnie te same kolumny i wiersze, co tabela rejestr_ocen z samego początku Twojej pracy. Pomyślne wykonanie tego zadania będzie ostatecznym dowodem na to, że normalizacja jest procesem bezstratnej dekompozycji – porządkuje dane i eliminuje redundancję, ale nie usuwa żadnych informacji ani możliwości ich powiązania.
uczelnia_3nf i zaplanuj, w jakiej kolejności połączysz tabele.SELECT
s.nr_albumu,
s.imie_studenta,
s.nazwisko_studenta,
s.email_studenta,
k.nazwa_kierunku,
w.nazwa_wydzialu,
w.adres_wydzialu,
p.kod_przedmiotu,
p.nazwa_przedmiotu,
wy.id_wykladowcy,
wy.imie_wykladowcy,
wy.nazwisko_wykladowcy,
wy.stopien_naukowy,
o.ocena,
o.data_wystawienia
FROM
Studenci s
JOIN Kierunki k ON s.id_kierunku = k.id_kierunku
JOIN Wydzialy w ON k.id_wydzialu = w.id_wydzialu
JOIN Oceny o ON s.nr_albumu = o.nr_albumu
JOIN Przedmioty p ON o.kod_przedmiotu = p.kod_przedmiotu
JOIN Wykladowcy wy ON p.id_wykladowcy = wy.id_wykladowcy
ORDER BY s.nr_albumu, p.kod_przedmiotu;
uczelnia_0nf.rejestr_ocen. Powinien być identyczny.Kluczową cechą dobrze przeprowadzonej normalizacji jest jej bezstratność. Oznacza to, że w dowolnym momencie możemy odtworzyć dowolny widok danych, który był możliwy do uzyskania przed normalizacją. Robimy to za pomocą złączeń (JOIN), które "składają" dane z powrotem w jedną całość na podstawie zdefiniowanych relacji (kluczy obcych). Chociaż dane są fizycznie przechowywane w wielu miejscach w sposób zoptymalizowany, logicznie wciąż stanowią spójną całość.
Udowodniłeś, że proces normalizacji jest w pełni odwracalny na poziomie zapytań. Potrafisz efektywnie pobierać i łączyć dane ze złożonej, znormalizowanej struktury, aby dostarczyć użytkownikowi końcowemu informacje w dowolnej, pożądanej przez niego formie. Cały proces projektowania bazy danych został zakończony sukcesem.
Nauczenie się tworzenia i wykorzystywania widoków (VIEW) w celu uproszczenia skomplikowanych zapytań, ukrycia złożoności schematu bazy danych przed użytkownikami końcowymi i zapewnienia spójnego dostępu do danych.
Zapytanie, które napisałeś w poprzednim zadaniu, działa doskonale, ale jest bardzo długie i skomplikowane. Pracownicy dziekanatu, którzy mają podstawową znajomość SQL, byliby przerażeni, gdyby musieli pisać je za każdym razem, gdy potrzebują pełnego raportu. Twój przełożony sugeruje rozwiązanie: stworzenie "wirtualnej tabeli", znanej jako widok. Twoim zadaniem jest zapisanie skomplikowanego zapytania łączącego wszystkie tabele jako widok o nazwie Pelny_Rejestr_Ocen. Po stworzeniu widoku, każdy użytkownik bazy danych będzie mógł odpytać go tak, jakby to była zwykła, pojedyncza tabela, nie martwiąc się o skomplikowane złączenia. Musisz najpierw stworzyć widok, a następnie zademonstrować, jak proste staje się teraz pobieranie danych. Na przykład, pokażesz, jak za pomocą prostego zapytania SELECT * FROM Pelny_Rejestr_Ocen WHERE nazwisko_studenta = 'Kowalski'; można uzyskać wszystkie potrzebne informacje, bez pisania ani jednego JOIN-a. To rozwiązanie znacznie ułatwi pracę mniej zaawansowanym użytkownikom i zmniejszy ryzyko popełnienia przez nich błędu.
CREATE VIEW, aby zapisać zapytanie z poprzedniego zadania jako widok.CREATE VIEW Pelny_Rejestr_Ocen AS
SELECT
s.nr_albumu,
s.imie_studenta,
s.nazwisko_studenta,
s.email_studenta,
k.nazwa_kierunku,
w.nazwa_wydzialu,
w.adres_wydzialu,
p.kod_przedmiotu,
p.nazwa_przedmiotu,
wy.id_wykladowcy,
wy.imie_wykladowcy,
wy.nazwisko_wykladowcy,
wy.stopien_naukowy,
o.ocena,
o.data_wystawienia
FROM
Studenci s
JOIN Kierunki k ON s.id_kierunku = k.id_kierunku
JOIN Wydzialy w ON k.id_wydzialu = w.id_wydzialu
JOIN Oceny o ON s.nr_albumu = o.nr_albumu
JOIN Przedmioty p ON o.kod_przedmiotu = p.kod_przedmiotu
JOIN Wykladowcy wy ON p.id_wykladowcy = wy.id_wykladowcy;
SELECT * FROM Pelny_Rejestr_Ocen;
SELECT imie_studenta, nazwisko_studenta, nazwa_przedmiotu, ocena FROM Pelny_Rejestr_Ocen WHERE nazwisko_wykladowcy = 'Nowak';
Widok (VIEW) nie przechowuje fizycznie danych. Jest to zapisane w bazie danych zapytanie SELECT, które jest wykonywane za każdym razem, gdy odwołujemy się do widoku. Widoki są potężnym narzędziem do upraszczania dostępu do danych, zapewniania bezpieczeństwa (można dać użytkownikowi dostęp tylko do widoku, a nie do tabel źródłowych) oraz do zachowania wstecznej kompatybilności, gdy zmienia się struktura tabel bazowych.
Nauczyłeś się tworzyć i używać widoków, aby uprościć pracę ze złożonym, znormalizowanym schematem bazy danych. Potrafisz teraz dostarczać dane użytkownikom w prostej i przystępnej formie, jednocześnie zachowując wszystkie zalety wynikające z normalizacji.
Ulepszenie istniejącego schematu 3NF poprzez zastąpienie kluczy głównych opartych na danych biznesowych (jak kod_przedmiotu) kluczami sztucznymi (surogatowymi) z mechanizmem AUTO_INCREMENT, co zwiększa elastyczność i wydajność bazy danych.
System działa dobrze, ale pojawia się nowy problem. Władze uczelni postanowiły zmienić system kodowania przedmiotów. Stary kod 'INF01' ma zostać zastąpiony nowym 'CS101'. Z przerażeniem odkrywasz, że kod_przedmiotu jest kluczem głównym w tabeli Przedmioty i kluczem obcym w tabeli Oceny. Jego zmiana wymagałaby kaskadowej aktualizacji w wielu miejscach i jest operacją ryzykowną. Twój mentor wyjaśnia, że używanie "naturalnych" kluczy, czyli danych, które mają znaczenie biznesowe (jak kod kursu, numer PESEL, NIP), jako kluczy głównych jest często złą praktyką, właśnie z powodu ich potencjalnej zmienności. Proponuje refaktoryzację schematu. Twoim zadaniem jest zmodyfikowanie tabel Przedmioty i Wykladowcy (oraz opcjonalnie innych), aby ich kluczem głównym stała się nowa, techniczna kolumna, np. id_przedmiotu, typu INT z atrybutem AUTO_INCREMENT. Dawny klucz naturalny, kod_przedmiotu, pozostanie w tabeli jako zwykła, unikalna kolumna. Musisz przeprowadzić tę zmianę, dostosować klucze obce w tabelach zależnych i zrozumieć, dlaczego takie podejście, zwane używaniem kluczy surogatowych, jest w większości przypadków preferowane.
uczelnia_3nf_final, aby przeprowadzić refaktoryzację.CREATE DATABASE uczelnia_3nf_final CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci; USE uczelnia_3nf_final;
Wydzialy i Kierunki, które już używają kluczy surogatowych.Wykladowcy, zmieniając klucz główny.-- Stara wersja używała id_wykladowcy, które musieliśmy podawać ręcznie
-- Nowa wersja z AUTO_INCREMENT
CREATE TABLE Wykladowcy (
id_wykladowcy INT AUTO_INCREMENT PRIMARY KEY,
-- Można dodać stary identyfikator jako zwykłe pole, jeśli jest potrzebny
-- stary_id_wykladowcy INT UNIQUE,
imie_wykladowcy VARCHAR(50),
nazwisko_wykladowcy VARCHAR(50),
stopien_naukowy VARCHAR(50)
);
Przedmioty, dodając nowy klucz główny i zachowując stary jako unikalny identyfikator biznesowy.CREATE TABLE Przedmioty (
id_przedmiotu INT AUTO_INCREMENT PRIMARY KEY,
kod_przedmiotu VARCHAR(10) UNIQUE NOT NULL,
nazwa_przedmiotu VARCHAR(100),
id_wykladowcy INT,
FOREIGN KEY (id_wykladowcy) REFERENCES Wykladowcy(id_wykladowcy)
);
Oceny, aby używała nowego klucza obcego id_przedmiotu.-- Kopiujemy strukturę tabel Studenci
CREATE TABLE Studenci LIKE uczelnia_3nf.Studenci;
-- ...i Kierunki, Wydzialy...
-- A teraz Oceny
CREATE TABLE Oceny (
id_oceny INT AUTO_INCREMENT PRIMARY KEY, -- Dodanie klucza głównego do tabeli Oceny
nr_albumu INT,
id_przedmiotu INT,
ocena DECIMAL(2,1),
data_wystawienia DATE,
FOREIGN KEY (nr_albumu) REFERENCES Studenci(nr_albumu),
FOREIGN KEY (id_przedmiotu) REFERENCES Przedmioty(id_przedmiotu),
UNIQUE (nr_albumu, id_przedmiotu) -- Zapewnienie, że student ma jedną ocenę z przedmiotu
);
Klucz surogatowy (sztuczny) to klucz główny, który nie ma żadnego znaczenia biznesowego – jest to zazwyczaj liczba całkowita, której wartość jest automatycznie nadawana przez system bazy danych (AUTO_INCREMENT). Jego główną zaletą jest stabilność. Ponieważ nie jest on powiązany z żadną rzeczywistą cechą opisywanego obiektu, nigdy nie będzie potrzeby jego zmiany. Upraszcza to zarządzanie relacjami i operacje aktualizacji. Klucz naturalny (np. kod_przedmiotu) wciąż pozostaje w tabeli, ale jako zwykły atrybut z ograniczeniem UNIQUE, aby zapewnić unikalność na poziomie biznesowym.
Ulepszyłeś swój schemat bazy danych, wprowadzając klucze surogatowe. Twoja baza jest teraz bardziej elastyczna, odporna na zmiany w logice biznesowej i często bardziej wydajna w operacjach złączeń. Zrozumiałeś różnicę między kluczem naturalnym a surogatowym i wiesz, kiedy stosować każde z tych rozwiązań.
Zrozumienie, jak modelować i implementować relację wiele-do-wielu (N:M) za pomocą tabeli pośredniczącej (łączącej), co jest bardzo częstym przypadkiem w projektowaniu baz danych.
Pojawia się nowe wymaganie od dziekanatu. Okazuje się, że jeden przedmiot może być prowadzony przez kilku wykładowców jednocześnie (np. wykład prowadzi profesor, a ćwiczenia doktor). Dotychczasowy model, w którym tabela Przedmioty miała jedno pole id_wykladowcy, zakładał relację jeden-do-wielu (jeden wykładowca mógł mieć wiele przedmiotów, ale przedmiot miał tylko jednego wykładowcę). Ta architektura jest już niewystarczająca. Twoim zadaniem jest przebudowanie schematu bazy danych, aby poprawnie obsłużyć nową relację: wiele-do-wielu. Musisz zrozumieć, że nie da się tego zrobić, po prostu dodając więcej kolumn w tabeli Przedmioty. Prawidłowym rozwiązaniem jest stworzenie nowej, trzeciej tabeli, zwanej tabelą asocjacyjną (łączącą), na przykład o nazwie Prowadzacy_Przedmioty. Tabela ta będzie zawierać tylko dwa klucze obce: jeden wskazujący na przedmiot (id_przedmiotu) i drugi na wykładowcę (id_wykladowcy). Razem będą one tworzyć złożony klucz główny. Musisz usunąć starą kolumnę id_wykladowcy z tabeli Przedmioty i zaimplementować nową strukturę, która pozwoli na przypisanie dowolnej liczby wykładowców do dowolnej liczby przedmiotów.
Przedmioty, usuwając z niej bezpośrednie powiązanie z wykładowcą.-- W bazie uczelnia_3nf_final ALTER TABLE Przedmioty DROP FOREIGN KEY Przedmioty_ibfk_1; ALTER TABLE Przedmioty DROP COLUMN id_wykladowcy;
Prowadzacy_Przedmioty.CREATE TABLE Prowadzacy_Przedmioty (
id_przedmiotu INT,
id_wykladowcy INT,
PRIMARY KEY (id_przedmiotu, id_wykladowcy),
FOREIGN KEY (id_przedmiotu) REFERENCES Przedmioty(id_przedmiotu),
FOREIGN KEY (id_wykladowcy) REFERENCES Wykladowcy(id_wykladowcy)
);
-- Zakładając, że mamy przedmioty i wykładowców z odpowiednimi ID INSERT INTO Prowadzacy_Przedmioty (id_przedmiotu, id_wykladowcy) VALUES (1, 1), (1, 2); -- Powyższy zapis oznacza, że przedmiot o ID=1 jest prowadzony przez wykładowców o ID=1 i ID=2
SELECT p.nazwa_przedmiotu, w.imie_wykladowcy, w.nazwisko_wykladowcy FROM Przedmioty p JOIN Prowadzacy_Przedmioty pp ON p.id_przedmiotu = pp.id_przedmiotu JOIN Wykladowcy w ON pp.id_wykladowcy = w.id_wykladowcy WHERE p.kod_przedmiotu = 'CS101'; -- lub inny kod
Relacji wiele-do-wielu (N:M) nie da się zaimplementować bezpośrednio w modelu relacyjnym. Rozwiązuje się ją zawsze za pomocą dodatkowej tabeli, zwanej tabelą pośredniczącą, łączącą lub asocjacyjną. Taka tabela przechowuje pary kluczy obcych z dwóch tabel, które chcemy połączyć. Złożony klucz główny tej tabeli, składający się z obu kluczy obcych, zapewnia unikalność powiązań. Tabela pośrednicząca może również zawierać dodatkowe atrybuty opisujące samą relację (np. rolę wykładowcy na danym przedmiocie: 'wykładowca', 'prowadzący ćwiczenia').
Nauczyłeś się modelować i implementować najbardziej elastyczny typ relacji – wiele-do-wielu. Twój schemat bazy danych jest teraz w stanie obsłużyć bardziej złożone i realistyczne scenariusze biznesowe, co świadczy o jego dojrzałości i poprawności projektowej.
Dokonanie przeglądu całego procesu normalizacji, od postaci nienormalizowanej do zrefaktoryzowanej trzeciej postaci normalnej. Podsumowanie zalet finalnego schematu i zrozumienie kompromisów związanych z normalizacją.
Twoja praca nad bazą danych dobiegła końca. Przeszedłeś długą drogę od jednego, chaotycznego arkusza kalkulacyjnego do eleganckiej, w pełni znormalizowanej i elastycznej struktury relacyjnej. Twoim ostatnim zadaniem jest przygotowanie prezentacji dla całego zespołu IT oraz pracowników dziekanatu, w której podsumujesz cały proces. Musisz przedstawić "przed" i "po" – pokazać schemat początkowy i wszystkie jego wady, a następnie zaprezentować finalny diagram bazy danych, omawiając każdą tabelę i jej przeznaczenie. Musisz jasno wyjaśnić, jakie korzyści przyniosła normalizacja: minimalizację redundancji, eliminację anomalii, integralność danych dzięki kluczom obcym, elastyczność na przyszłe zmiany (jak dodanie relacji N:M czy zmiana kodów przedmiotów). Musisz również wspomnieć o potencjalnych kompromisach. Czy jest coś, co stało się trudniejsze po normalizacji? Z pewnością zapytania stały się bardziej skomplikowane i wymagają wielu złączeń. Musisz jednak wykazać, że korzyści płynące z dobrze zorganizowanej struktury wielokrotnie przewyższają tę niedogodność, którą zresztą można zniwelować za pomocą widoków. Twoje podsumowanie ma być dowodem na to, że normalizacja nie jest celem samym w sobie, ale potężnym narzędziem prowadzącym do stworzenia solidnej, niezawodnej i łatwej w utrzymaniu bazy danych.
rejestr_ocen) i wypunktuj jego wady:
uczelnia_3nf_final, składający się z tabel: Wydzialy, Kierunki, Studenci, Wykladowcy, Przedmioty, Oceny, Prowadzacy_Przedmioty.JOIN).VIEW) pomagają zniwelować problem złożoności zapytań dla użytkowników końcowych.Przeprowadziłeś kompleksowy proces normalizacji bazy danych, przechodząc przez wszystkie kluczowe etapy. Zrozumiałeś teoretyczne podstawy (zależności funkcyjne) i potrafisz zastosować je w praktyce do dekompozycji tabel. Ostateczny schemat jest zgodny z najlepszymi praktykami projektowania baz danych, co czyni go solidnym fundamentem dla każdej aplikacji.