Projektowanie baz danych: Kurs od podstaw do projektu

Zadanie 1: Instalacja i onfiguracja środowiska pracy (XAMPP)

Cel zadania

Celem tego fundamentalnego zadania jest przygotowanie lokalnego środowiska deweloperskiego, które będzie służyło do realizacji wszystkich kolejnych ćwiczeń. Student nauczy się, jak pobrać i zainstalować pakiet XAMPP, który zawiera serwer WWW Apache, serwer bazy danych MariaDB (kompatybilny z MySQL) oraz interpreter PHP. Kluczowym elementem jest zrozumienie, jak uruchamiać i zatrzymywać poszczególne usługi za pomocą panelu kontrolnego XAMPP. Najważniejszym krokiem będzie podstawowe zabezpieczenie serwera baz danych poprzez ustawienie hasła dla głównego użytkownika (`root`), co jest absolutną podstawą dobrych praktyk w pracy z bazami danych. To zadanie nie wymaga pisania kodu SQL, ale buduje solidny fundament pod dalszą pracę.

Scenariusz

Jesteś początkującym administratorem baz danych w małej firmie IT. Twoim pierwszym zadaniem jest stworzenie bezpiecznego, lokalnego środowiska testowego na swoim komputerze. Musisz zainstalować wszystkie niezbędne narzędzia, które pozwolą Ci na tworzenie i zarządzanie bazami danych. Wybór padł na pakiet XAMPP, ponieważ jest on darmowy, łatwy w instalacji i zawiera wszystko, czego potrzebujesz. Twoim obowiązkiem jest nie tylko uruchomienie usług, ale także zapewnienie, że nikt nieautoryzowany nie będzie miał dostępu do serwera MariaDB. Domyślnie, użytkownik `root` nie ma hasła, co stanowi poważną lukę bezpieczeństwa, nawet w środowisku lokalnym. Twoim celem jest zamknięcie tej luki poprzez nadanie mu silnego hasła. Prawidłowe wykonanie tego zadania otworzy Ci drogę do świata baz danych i zapewni stabilne miejsce do nauki i eksperymentów.

Pomocnik Wykonania (Kroki)

1. Pobieranie XAMPP: Wejdź na oficjalną stronę apachefriends.org.
2. Wybór wersji: Pobierz instalator XAMPP odpowiedni dla Twojego systemu operacyjnego (Windows, Linux lub macOS).
3. Instalacja: Uruchom pobrany plik i postępuj zgodnie z instrukcjami instalatora. Możesz pozostawić domyślne komponenty do instalacji.
4. Uruchomienie Panelu Kontrolnego: Po zakończeniu instalacji, uruchom "XAMPP Control Panel".
5. Startowanie usług: W panelu kontrolnym, kliknij przycisk "Start" obok modułów Apache oraz MySQL.
6. Weryfikacja działania: Obok nazw modułów powinny pojawić się zielone tła oraz numery portów (np. 80, 443, 3306).
7. Dostęp do phpMyAdmin: Otwórz przeglądarkę internetową i wejdź na adres `http://localhost/phpmyadmin/`. Powinieneś zobaczyć interfejs graficzny do zarządzania bazą danych.
8. Nawigacja do kont użytkowników: W górnym menu phpMyAdmin kliknij w zakładkę "Konta użytkowników" (lub "User accounts").
9. Identyfikacja użytkownika root: Zobaczysz listę użytkowników. Zlokalizuj użytkownika `root` dla hosta `localhost` lub `127.0.0.1`.
10. Edycja uprawnień: Kliknij "Edytuj uprawnienia" (lub "Edit privileges") przy użytkowniku `root`.
11. Zmiana hasła: W nowym oknie przejdź do sekcji "Zmiana hasła" (lub "Change password").
12. Wprowadzenie nowego hasła: Wpisz nowe, silne hasło w obu polach i zatwierdź, klikając przycisk "Wykonaj" (lub "Go").
13. Błąd dostępu (oczekiwany!): Po zmianie hasła, phpMyAdmin prawdopodobnie wyświetli błąd dostępu. To normalne, ponieważ jego konfiguracja nie zna jeszcze nowego hasła.
14. Aktualizacja konfiguracji phpMyAdmin: Odszukaj plik `config.inc.php` w katalogu `phpMyAdmin` wewnątrz folderu instalacyjnego XAMPP.
15. Edycja pliku konfiguracyjnego: Otwórz plik w edytorze tekstu. Znajdź linię `$cfg['Servers'][$i]['password'] = '';`
16. Wpisanie nowego hasła: Zmień tę linię, wpisując swoje nowe hasło między apostrofy: `$cfg['Servers'][$i]['password'] = 'TwojeNoweHaslo';`
17. Zapisanie pliku: Zapisz i zamknij plik `config.inc.php`.
18. Ponowne załadowanie phpMyAdmin: Odśwież stronę `http://localhost/phpmyadmin/` w przeglądarce. Teraz powinieneś mieć pełny dostęp.
19. Zakończenie: Gratulacje! Twoje środowisko jest gotowe i zabezpieczone.

Zadanie 2: Pierwsze Kroki z Linią Poleceń MySQL

Cel Zadania

Zadanie to ma na celu oswojenie studenta z podstawowym, ale niezwykle potężnym narzędziem każdego administratora i dewelopera baz danych – klientem linii poleceń (CLI). Student nauczy się, jak uruchomić terminal, nawigować do odpowiedniego katalogu i połączyć się z serwerem MariaDB, używając danych uwierzytelniających skonfigurowanych w poprzednim zadaniu. Głównym celem jest opanowanie fundamentalnych komend, które nie manipulują danymi, ale pozwalają na orientację w środowisku serwera: wyświetlanie istniejących baz danych, wybieranie aktywnej bazy do pracy oraz tworzenie nowej, pustej bazy. Pomyślne ukończenie tego zadania zbuduje pewność siebie w pracy z terminalem, który jest często szybszy i bardziej elastyczny niż narzędzia graficzne.

Scenariusz

Po udanym skonfigurowaniu środowiska, przyszedł czas, aby poczuć się jak prawdziwy profesjonalista i zacząć pracować z bazą danych bezpośrednio przez terminal. Twoim zadaniem jest zalogowanie się do serwera MariaDB jako użytkownik `root`. Chcesz rozejrzeć się po serwerze, aby zobaczyć, jakie bazy danych są na nim domyślnie zainstalowane przez XAMPP. Nie chcesz jednak niczego w nich modyfikować. Aby przygotować sobie miejsce do dalszej pracy, musisz stworzyć swoją pierwszą, osobistą bazę danych. Nazwij ją `moja_pierwsza_baza`, aby jednoznacznie wskazywała na swoje przeznaczenie. Na koniec upewnisz się, że nowo utworzona baza jest widoczna na liście i ustawisz ją jako aktywny kontekst do dalszych działań. Praca w konsoli może wydawać się na początku onieśmielająca, ale jest to kluczowa umiejętność, która odróżnia amatora od specjalisty.

Pomocnik Wykonania (Kroki)

1. Uruchomienie terminala: Otwórz terminal systemowy (Wiersz polecenia/CMD lub PowerShell na Windows, Terminal na macOS/Linux).
2. Nawigacja do katalogu XAMPP: Użyj komendy `cd` (change directory), aby przejść do folderu, gdzie zainstalowany jest XAMPP.
3. Wejście do katalogu `bin` MySQL: Ścieżka docelowa to zazwyczaj `C:\xampp\mysql\bin` na Windows. Wpisz `cd C:\xampp\mysql\bin`.
4. Polecenie logowania: Główna komenda do logowania to `mysql`. Musimy dodać flagi, aby określić użytkownika i poprosić o hasło.
5. Wpisanie komendy logowania: Wpisz w terminalu następującą komendę i naciśnij Enter: `mysql -u root -p`.
6. Podanie hasła: Terminal poprosi Cię o hasło. Wpisz hasło dla użytkownika `root`, które ustawiłeś w zadaniu 1. Pamiętaj, że podczas wpisywania hasła znaki nie będą widoczne!
7. Potwierdzenie zalogowania: Jeśli hasło jest poprawne, zobaczysz powitanie MariaDB i znak zachęty `MariaDB [(none)]>`.
8. Wyświetlanie baz danych: Aby zobaczyć listę wszystkich baz danych na serwerze, wpisz komendę: `SHOW DATABASES;`. Pamiętaj o średniku na końcu!
9. Analiza wyniku: Zobaczysz listę baz, takich jak `information_schema`, `mysql`, `performance_schema` i `phpmyadmin`.
10. Tworzenie nowej bazy danych: Teraz stwórz swoją własną bazę. Użyj polecenia `CREATE DATABASE`.
11. Wpisanie komendy tworzenia: Wpisz: `CREATE DATABASE moja_pierwsza_baza CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;`.
12. Znaczenie dodatkowych parametrów: `CHARACTER SET` i `COLLATE` zapewniają prawidłowe wsparcie dla polskich znaków.
13. Potwierdzenie wykonania: Po naciśnięciu Enter powinieneś zobaczyć komunikat `Query OK, 1 row affected`.
14. Weryfikacja: Aby upewnić się, że baza została stworzona, ponownie wyświetl listę baz: `SHOW DATABASES;`.
15. Sprawdzenie listy: Twoja nowa baza `moja_pierwsza_baza` powinna teraz znajdować się na liście.
16. Wybór aktywnej bazy: Aby zacząć pracować w kontekście nowej bazy, użyj komendy `USE`. Wpisz: `USE moja_pierwsza_baza;`.
17. Zmiana znaku zachęty: Zwróć uwagę, że znak zachęty zmienił się na `MariaDB [moja_pierwsza_baza]>`, co informuje Cię, w której bazie aktualnie pracujesz.
18. Wylogowanie: Aby bezpiecznie zakończyć sesję z serwerem, wpisz `exit` lub `quit` i naciśnij Enter.

Przykładowy Kod (Konsola)

-- Krok 5: Logowanie do serwera MySQL/MariaDB
C:\xampp\mysql\bin> mysql -u root -p
Enter password: ****

-- Krok 8: Wyświetlanie istniejących baz danych
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+

-- Krok 11: Tworzenie nowej bazy danych
MariaDB [(none)]> CREATE DATABASE moja_pierwsza_baza CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected

-- Krok 17: Wybór nowej bazy jako aktywnej
MariaDB [(none)]> USE moja_pierwsza_baza;
Database changed
MariaDB [moja_pierwsza_baza]> 

-- Krok 18: Wyjście z klienta
MariaDB [moja_pierwsza_baza]> exit
Bye

Zadanie 3: Twoja Pierwsza Tabela - Język Definicji Danych (DDL)

Cel Zadania

Celem zadania jest praktyczne zapoznanie się z podstawową komendą języka DDL (Data Definition Language) – `CREATE TABLE`. Student nauczy się, jak w ramach istniejącej bazy danych zdefiniować strukturę tabeli, która będzie przechowywać konkretne informacje. Zadanie obejmuje wybór odpowiednich typów danych dla różnych atrybutów (liczby całkowite, tekst, daty), definiowanie ograniczeń, takich jak `NOT NULL`, aby zapewnić integralność danych, oraz, co najważniejsze, ustanowienie klucza głównego (`PRIMARY KEY`), który jednoznacznie identyfikuje każdy rekord. Student dowie się również, jak używać komendy `DESCRIBE` do weryfikacji poprawności stworzonej struktury, co jest kluczowe w procesie projektowania i debugowania.

Scenariusz

Wyobraź sobie, że tworzysz prostą aplikację do zarządzania kolekcją książek. Na razie potrzebujesz tylko jednego miejsca do przechowywania podstawowych informacji o każdej książce. Twoim zadaniem jest stworzenie w bazie `moja_pierwsza_baza` tabeli o nazwie `Ksiazki`. Tabela ta musi przechowywać kilka kluczowych informacji. Każda książka musi mieć unikalny identyfikator (ID), który będzie automatycznie nadawany. Musisz również zapisać jej tytuł, autora, datę publikacji oraz numer ISBN. Tytuł i autor są informacjami wymaganymi i nie mogą być puste. Identyfikator ID ma być kluczem głównym, gwarantującym, że nigdy nie pojawią się dwie książki o tym samym identyfikatorze. Po stworzeniu tabeli musisz sprawdzić, czy jej struktura zgadza się z Twoimi założeniami, zanim przejdziesz do wstawiania do niej jakichkolwiek danych.

Schemat Tabeli

Pomocnik Wykonania (Kroki)

1. Zaloguj się do konsoli MySQL: Użyj komendy `mysql -u root -p`, tak jak w poprzednim zadaniu.
2. Wybierz bazę danych: Upewnij się, że pracujesz w odpowiednim kontekście, wpisując `USE moja_pierwsza_baza;`.
3. Zaplanuj strukturę tabeli: Zastanów się, jakie kolumny są potrzebne i jakie typy danych będą dla nich najlepsze.
   • ID: Liczba całkowita, która sama się zwiększa.
   • Tytuł/Autor: Zmiennej długości ciąg znaków.
   • Data publikacji: Typ przechowujący tylko datę.
   • ISBN: Ciąg znaków o stałej długości.
4. Rozpocznij tworzenie komendy: Składnia zaczyna się od `CREATE TABLE nazwa_tabeli (`.
5. Definicja pierwszej kolumny (klucz główny): Wpisz `id_ksiazki INT PRIMARY KEY AUTO_INCREMENT,`.
   • `INT`: typ danych (liczba całkowita).
   • `PRIMARY KEY`: to jest klucz główny.
   • `AUTO_INCREMENT`: serwer sam będzie nadawał wartości (1, 2, 3...).
6. Definicja kolumny 'tytul': Dodaj `tytul VARCHAR(255) NOT NULL,`.
   • `VARCHAR(255)`: tekst o maksymalnej długości 255 znaków.
   • `NOT NULL`: ta kolumna musi mieć wartość.
7. Definicja kolumny 'autor': Dodaj `autor VARCHAR(255) NOT NULL,` na tej samej zasadzie co tytuł.
8. Definicja kolumny 'data_publikacji': Dodaj `data_publikacji DATE,`. Ta kolumna może być pusta (NULL).
9. Definicja kolumny 'isbn': Dodaj `isbn VARCHAR(13)`. Nie dodajemy przecinka, bo to ostatnia kolumna.
10. Zamknięcie komendy: Zakończ definicję nawiasem zamykającym i średnikiem: `);`.
11. Kompletna komenda: Zbierz wszystko w jedną całość i wykonaj w konsoli.
12. Weryfikacja utworzenia: Po wykonaniu komendy, serwer powinien odpowiedzieć `Query OK`.
13. Sprawdzenie listy tabel: Wpisz `SHOW TABLES;`, aby zobaczyć, czy tabela `Ksiazki` pojawiła się w bazie.
14. Inspekcja struktury tabeli: Aby dokładnie zobaczyć, jak wygląda struktura, użyj komendy `DESCRIBE` (lub `DESC`).
15. Wykonanie inspekcji: Wpisz `DESCRIBE Ksiazki;`.
16. Analiza wyniku `DESCRIBE`: Porównaj wynik z założeniami. Zwróć uwagę na kolumny: `Field` (nazwa), `Type` (typ), `Null` (czy dozwolony `NULL`), `Key` (czy jest kluczem, np. `PRI`), `Default` (wartość domyślna) i `Extra` (np. `auto_increment`).
17. Zrozumienie klucza głównego: Zauważ, że przy `id_ksiazki` w kolumnie `Key` widnieje `PRI` – to oznacza Primary Key.
18. Wylogowanie: Zakończ pracę z konsolą, wpisując `exit`.

Przykładowy Kod SQL

-- Pełna komenda do stworzenia tabeli 'Ksiazki'
CREATE TABLE Ksiazki (
    id_ksiazki      INT PRIMARY KEY AUTO_INCREMENT,
    tytul           VARCHAR(255) NOT NULL,
    autor           VARCHAR(255) NOT NULL,
    data_publikacji DATE,
    isbn            VARCHAR(13)
);

-- Komenda do weryfikacji struktury tabeli
DESCRIBE Ksiazki;

-- Oczekiwany wynik polecenia DESCRIBE
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id_ksiazki        | int(11)      | NO   | PRI | NULL    | auto_increment |
| tytul             | varchar(255) | NO   |     | NULL    |                |
| autor             | varchar(255) | NO   |     | NULL    |                |
| data_publikacji   | date         | YES  |     | NULL    |                |
| isbn              | varchar(13)  | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

Zadanie 4: Manipulacja Danymi - Język DML (INSERT, SELECT, UPDATE, DELETE)

Cel Zadania

Celem jest praktyczne zastosowanie czterech podstawowych poleceń Języka Manipulacji Danymi (DML): `INSERT`, `SELECT`, `UPDATE` i `DELETE`. Student nauczy się, jak dodawać nowe wiersze (rekordy) do istniejącej tabeli, jak odpytywać tabelę, aby wyświetlić zapisane dane, a także jak je modyfikować i usuwać. Zadanie kładzie szczególny nacisk na użycie klauzuli `WHERE`, która jest kluczowa dla precyzyjnego wskazywania rekordów do aktualizacji lub usunięcia, co pozwala uniknąć przypadkowych zmian w całej tabeli. Opanowanie tych czterech komend jest absolutnym fundamentem pracy z danymi w każdej relacyjnej bazie danych. 📖

Scenariusz

Twoja tabela `Ksiazki` jest już gotowa, ale na razie jest pusta. Czas tchnąć w nią życie, dodając kilka pozycji ze swojej domowej biblioteczki. Twoim pierwszym zadaniem jest wstawienie do tabeli trzech różnych książek. Po dodaniu danych musisz upewnić się, że wszystko zostało poprawnie zapisane, wyświetlając całą zawartość tabeli. Niestety, zauważasz, że przy jednej z książek popełniłeś błąd w dacie publikacji – musisz go poprawić. Następnie decydujesz, że jedna z dodanych książek jednak nie pasuje do kolekcji i chcesz ją trwale usunąć z bazy danych. Każda z tych operacji wymaga użycia innego polecenia DML. Musisz działać ostrożnie, zwłaszcza przy modyfikacji i usuwaniu, aby przypadkiem nie usunąć lub nie zmienić wszystkich swoich danych!

Pomocnik Wykonania (Kroki)

1. Połącz się z bazą danych: Zaloguj się do konsoli `mysql -u root -p` i wybierz swoją bazę `USE moja_pierwsza_baza;`.
2. Dodawanie pierwszego rekordu: Użyj komendy `INSERT INTO`. Składnia to `INSERT INTO nazwa_tabeli (kolumna1, kolumna2, ...) VALUES (wartosc1, wartosc2, ...);`.
3. Wstaw pierwszą książkę: Wpisz `INSERT INTO Ksiazki (tytul, autor, data_publikacji, isbn) VALUES ('Wiedźmin: Ostatnie życzenie', 'Andrzej Sapkowski', '1993-01-01', '9788375780635');`. Zwróć uwagę, że teksty i daty są w apostrofach.
4. Wstaw kolejne książki: Dodaj jeszcze dwie książki w ten sam sposób. Możesz wymyślić własne lub użyć przykładów z sekcji "Przykładowy Kod".
5. Wyświetlanie wszystkich danych: Aby zobaczyć, co znajduje się w tabeli, użyj `SELECT`. Najprostsza forma to `SELECT * FROM nazwa_tabeli;`.
6. Sprawdź zawartość tabeli: Wpisz `SELECT * FROM Ksiazki;`. Zobaczysz wszystkie dodane rekordy wraz z ich automatycznie nadanymi `id_ksiazki`.
7. Wyświetlanie wybranych kolumn: Aby wyświetlić tylko tytuł i autora, wpisz `SELECT tytul, autor FROM Ksiazki;`.
8. Identyfikacja rekordu do modyfikacji: Załóżmy, że chcesz zmienić datę publikacji książki "Wiedźmin". Sprawdź jej `id_ksiazki` (zapewne będzie to 1).
9. Modyfikacja rekordu: Użyj komendy `UPDATE`. Składnia to `UPDATE nazwa_tabeli SET kolumna = nowa_wartosc WHERE warunek;`.
10. Napisz komendę `UPDATE`: Wpisz `UPDATE Ksiazki SET data_publikacji = '1993-11-10' WHERE id_ksiazki = 1;`. Klauzula `WHERE` jest tu kluczowa!
11. Weryfikacja zmiany: Ponownie wykonaj `SELECT * FROM Ksiazki;`, aby zobaczyć, czy data została zaktualizowana.
12. Identyfikacja rekordu do usunięcia: Załóżmy, że chcesz usunąć książkę o `id_ksiazki` równym 3.
13. Usuwanie rekordu: Użyj komendy `DELETE FROM`. Składnia to `DELETE FROM nazwa_tabeli WHERE warunek;`.
14. Napisz komendę `DELETE`: Wpisz `DELETE FROM Ksiazki WHERE id_ksiazki = 3;`. Ponownie – `WHERE` jest absolutnie niezbędne!
15. Weryfikacja usunięcia: Wykonaj `SELECT * FROM Ksiazki;` po raz ostatni. Rekord o ID 3 powinien zniknąć.
16. Eksperyment (opcjonalny): Spróbuj wykonać `SELECT * FROM Ksiazki WHERE autor = 'Andrzej Sapkowski';`. Zobaczysz, jak `WHERE` filtruje wyniki.
17. Zrozumienie ryzyka: Pomyśl, co by się stało, gdybyś wykonał `UPDATE Ksiazki SET autor = 'Anonim';` bez klauzuli `WHERE`. (Odpowiedź: wszystkie książki miałyby tego samego autora!).
18. Zakończ sesję: Wyloguj się z konsoli, wpisując `exit`.

Przykładowy Kod SQL

-- Krok 3 & 4: Wstawianie danych (przykłady)
INSERT INTO Ksiazki (tytul, autor, data_publikacji, isbn) 
VALUES ('Wiedźmin: Ostatnie życzenie', 'Andrzej Sapkowski', '1993-01-01', '9788375780635');

INSERT INTO Ksiazki (tytul, autor, data_publikacji, isbn) 
VALUES ('Diuna', 'Frank Herbert', '1965-08-01', '9788373015537');

INSERT INTO Ksiazki (tytul, autor, isbn) 
VALUES ('Solaris', 'Stanisław Lem', '9788308049141'); -- Zauważ, że data jest pomijana (dopuszczalny NULL)

-- Krok 6: Wyświetlanie wszystkich danych
SELECT * FROM Ksiazki;

-- Krok 10: Aktualizacja rekordu
UPDATE Ksiazki SET data_publikacji = '1993-11-10' WHERE id_ksiazki = 1;

-- Krok 14: Usunięcie rekordu
DELETE FROM Ksiazki WHERE id_ksiazki = 3;

Zadanie 5: Projektowanie Bazy Danych "Akademik" - Relacje i Klucze Obce

Cel Zadania

Celem zadania jest przejście od pracy z jedną tabelą do zaprojektowania małego, ale kompletnego schematu bazy danych składającego się z wielu powiązanych tabel. Student nauczy się kluczowej koncepcji relacyjnych baz danych: modelowania związków typu "jeden-do-wielu" (np. jeden budynek ma wiele pokoi) oraz "wiele-do-wielu" (np. jeden student może mieszkać w wielu pokojach w swojej historii, a jeden pokój może gościć wielu studentów na przestrzeni lat). Najważniejszym elementem jest praktyczne zastosowanie kluczy obcych (`FOREIGN KEY`) do zapewnienia integralności referencyjnej, czyli gwarancji, że dane w powiązanych tabelach są spójne. To zadanie buduje fundament pod umiejętność myślenia w kategoriach relacji, a nie tylko płaskich struktur.

Scenariusz

Zostałeś poproszony o stworzenie prostej bazy danych do zarządzania zakwaterowaniem w sieci akademików. System musi przechowywać informacje o budynkach, pokojach w tych budynkach oraz studentach, którzy w nich mieszkają. Każdy budynek ma unikalną nazwę (np. "Dom Studenta nr 1 'Muchomorek'") i adres. W każdym budynku znajduje się wiele pokoi, a każdy pokój ma swój numer, liczbę miejsc (pojemność) i oczywiście musi być przypisany do konkretnego budynku. Należy również przechowywać dane studentów: imię, nazwisko i numer albumu. Najważniejsza część systemu to ewidencja mieszkańców. Musisz stworzyć mechanizm, który jednoznacznie przypisuje studenta do konkretnego pokoju. Pamiętaj, że jeden student może mieszkać tylko w jednym pokoju w danym momencie, ale jeden pokój może mieścić kilku studentów (do limitu jego pojemności). Twoim zadaniem jest zaprojektowanie tabel, zdefiniowanie relacji między nimi za pomocą kluczy głównych i obcych, a następnie wypełnienie ich przykładowymi danymi.

Schemat Bazy Danych

Pomocnik Wykonania (Kroki)

1. Utwórz nową bazę danych: W konsoli `mysql` stwórz bazę `akademik_db`. `CREATE DATABASE akademik_db;` i przełącz się na nią `USE akademik_db;`.
2. Zaprojektuj tabelę "nadrzędną": Zacznij od tabeli, która nie zależy od żadnej innej. W tym przypadku jest to `Budynki`.
3. Napisz DDL dla `Budynki`: Stwórz tabelę `Budynki` z kolumnami `id_budynku` (PK, AUTO_INCREMENT), `nazwa_budynku` (VARCHAR, NOT NULL) i `adres_budynku` (VARCHAR).
4. Zaprojektuj tabelę "podrzędną": Tabela `Pokoje` zależy od `Budynki`. Musi zawierać kolumnę, która będzie "wskazywać" na konkretny budynek.
5. Napisz DDL dla `Pokoje`: Stwórz tabelę `Pokoje` z kolumnami `id_pokoju` (PK, AUTO_INCREMENT), `numer_pokoju` (INT), `pojemnosc` (INT).
6. Dodaj klucz obcy do `Pokoje`: W definicji tabeli `Pokoje` dodaj kolumnę `id_budynku INT`.
7. Zdefiniuj relację: Na końcu definicji tabeli `Pokoje` dodaj klauzulę `FOREIGN KEY (id_budynku) REFERENCES Budynki(id_budynku);`.
8. Napisz DDL dla `Studenci`: Stwórz tabelę `Studenci` z kolumnami `id_studenta` (PK, AUTO_INCREMENT), `imie`, `nazwisko`, `nr_albumu` (UNIQUE).
9. Dodaj klucz obcy do `Studenci`: Podobnie jak poprzednio, dodaj kolumnę `id_pokoju INT` oraz klauzulę `FOREIGN KEY (id_pokoju) REFERENCES Pokoje(id_pokoju);`.
10. Wstaw dane do `Budynki`: Dodaj 2-3 budynki za pomocą `INSERT INTO Budynki ...`.
11. Wstaw dane do `Pokoje`: Dodaj kilka pokoi, upewniając się, że `id_budynku` w każdym pokoju odpowiada istniejącemu ID w tabeli `Budynki`.
12. Spróbuj wstawić błędne dane: Wykonaj `INSERT` do tabeli `Pokoje` z nieistniejącym `id_budynku` (np. 99). Serwer powinien zwrócić błąd – to działanie klucza obcego!
13. Wstaw dane do `Studenci`: Dodaj kilku studentów, przypisując im `id_pokoju`, które istnieją w tabeli `Pokoje`.
14. Sprawdź integralność: Spróbuj usunąć budynek, w którym są pokoje. Co się stanie? Dlaczego? (Błąd, bo istnieją rekordy zależne).
15. Wyświetl dane: Na razie użyj prostych `SELECT * FROM ...` na każdej tabeli, aby zobaczyć, jak wyglądają dane.
16. Zrozumienie relacji: Patrząc na tabelę `Studenci`, widzisz tylko `id_pokoju`. Jak dowiedzieć się, w którym budynku mieszka student? (Odpowiedź: do tego służą złączenia - `JOIN`, które będą tematem kolejnego zadania).
17. Dodaj ograniczenie `ON DELETE`: (Teoretycznie) Zastanów się, co by oznaczało dodanie `ON DELETE SET NULL` do definicji klucza obcego w tabeli `Studenci`. (Odpowiedź: usunięcie pokoju spowodowałoby "wykwaterowanie" studenta, ustawiając jego `id_pokoju` na NULL).
18. Zakończ sesję: Wyloguj się z bazy.

Przykładowy Kod SQL

-- Tworzenie tabeli Budynki
CREATE TABLE Budynki (
    id_budynku      INT AUTO_INCREMENT PRIMARY KEY,
    nazwa_budynku   VARCHAR(100) NOT NULL,
    adres_budynku   VARCHAR(255)
);

-- Tworzenie tabeli Pokoje z kluczem obcym
CREATE TABLE Pokoje (
    id_pokoju       INT AUTO_INCREMENT PRIMARY KEY,
    numer_pokoju    INT NOT NULL,
    pojemnosc       INT NOT NULL,
    id_budynku      INT,
    FOREIGN KEY (id_budynku) REFERENCES Budynki(id_budynku)
);

-- Tworzenie tabeli Studenci z kluczem obcym
CREATE TABLE Studenci (
    id_studenta     INT AUTO_INCREMENT PRIMARY KEY,
    imie            VARCHAR(50),
    nazwisko        VARCHAR(50),
    nr_albumu       VARCHAR(10) NOT NULL UNIQUE,
    id_pokoju       INT,
    FOREIGN KEY (id_pokoju) REFERENCES Pokoje(id_pokoju)
);

-- Przykładowe INSERTy
INSERT INTO Budynki (nazwa_budynku, adres_budynku) VALUES ('DS-1 Alkatraz', 'ul. Akademicka 1');
INSERT INTO Pokoje (numer_pokoju, pojemnosc, id_budynku) VALUES (101, 2, 1);
INSERT INTO Studenci (imie, nazwisko, nr_albumu, id_pokoju) VALUES ('Jan', 'Kowalski', '123456', 1);

Zadanie 6: Łączenie Danych - Potęga Złączeń (`JOIN`)

Cel Zadania

Zadanie to ma na celu nauczenie studenta najważniejszej i najczęściej używanej operacji w języku SQL – złączania danych z wielu tabel. Student pozna składnię i zastosowanie klauzuli `JOIN`, koncentrując się na dwóch jej fundamentalnych typach: `INNER JOIN` (złączenie wewnętrzne), które zwraca tylko pasujące do siebie wiersze z obu tabel, oraz `LEFT JOIN` (złączenie zewnętrzne lewostronne), które zwraca wszystkie wiersze z lewej tabeli i pasujące wiersze z prawej. Celem jest zrozumienie, jak za pomocą jednego zapytania uzyskać kompleksową informację, która w bazie danych jest logicznie rozproszona po różnych tabelach zgodnie z zasadami normalizacji. To umiejętność kluczowa do tworzenia użytecznych raportów i widoków. 🔗

Scenariusz

Pracujesz z bazą danych `akademik_db` z poprzedniego zadania. Kierownik administracji akademików poprosił Cię o przygotowanie kilku ważnych raportów. Po pierwsze, potrzebuje pełnej listy wszystkich zakwaterowanych studentów, ale zamiast tajemniczych numerów ID pokoju i budynku, chce widzieć pełne dane: imię i nazwisko studenta, numer jego pokoju oraz nazwę i adres budynku, w którym mieszka. Po drugie, administracja chce zobaczyć listę WSZYSTKICH pokoi w systemie, a obok każdego z nich informację o studencie, który go zamieszkuje. Jeśli pokój jest pusty, w raporcie również ma się pojawić, z pustymi polami dla danych studenta. Na koniec, potrzebny jest raport specjalny: lista studentów mieszkających tylko w budynku o nazwie "DS-1 Alkatraz". Wykonanie tych zadań jest niemożliwe bez umiejętnego łączenia tabel.

Pomocnik Wykonania (Kroki)

1. Połącz się z bazą danych: Zaloguj się i wybierz bazę `akademik_db`.
2. Przeanalizuj problem: Aby uzyskać imię studenta i nazwę budynku, potrzebujesz danych z tabel `Studenci`, `Pokoje` i `Budynki`.
3. Zacznij od dwóch tabel: Napisz zapytanie, które połączy studentów z pokojami. Składnia to `SELECT ... FROM TabelaA JOIN TabelaB ON TabelaA.klucz = TabelaB.klucz;`.
4. Połącz `Studenci` i `Pokoje`: Wpisz `SELECT * FROM Studenci INNER JOIN Pokoje ON Studenci.id_pokoju = Pokoje.id_pokoju;`. Przeanalizuj wynik.
5. Dodaj trzecią tabelę: Do istniejącego `JOIN` można dołączyć kolejny. Składnia pozostaje podobna.
6. Napisz pełne zapytanie z trzema tabelami: `SELECT * FROM Studenci JOIN Pokoje ON Studenci.id_pokoju = Pokoje.id_pokoju JOIN Budynki ON Pokoje.id_budynku = Budynki.id_budynku;`.
7. Wybierz konkretne kolumny: Zmodyfikuj poprzednie zapytanie, aby wyświetlało tylko te kolumny, o które prosił kierownik: `SELECT Studenci.imie, Studenci.nazwisko, Pokoje.numer_pokoju, Budynki.nazwa_budynku, Budynki.adres_budynku FROM ...`.
8. Użyj aliasów tabel: Długie nazwy tabel są niewygodne. Użyj aliasów: `FROM Studenci AS s JOIN Pokoje AS p ON s.id_pokoju = p.id_pokoju ...`. To dobra praktyka.
9. Drugi raport - puste pokoje: Teraz potrzebujesz listy wszystkich pokoi. Tabela `Pokoje` musi być "tabelą lewą" w złączeniu.
10. Użyj `LEFT JOIN`: Napisz zapytanie: `SELECT p.numer_pokoju, b.nazwa_budynku, s.imie, s.nazwisko FROM Pokoje AS p JOIN Budynki AS b ON p.id_budynku = b.id_budynku LEFT JOIN Studenci AS s ON p.id_pokoju = s.id_pokoju;`.
11. Analiza wyniku `LEFT JOIN`: Zauważ, że w wyniku pojawią się wszystkie pokoje. Te, które nie mają przypisanych studentów, będą miały wartości `NULL` w kolumnach `imie` i `nazwisko`.
12. Trzeci raport - filtrowanie: Potrzebujesz studentów tylko z jednego budynku. Użyj zapytania z `INNER JOIN` i dodaj na końcu klauzulę `WHERE`.
13. Napisz zapytanie z filtrowaniem: Wykorzystaj zapytanie z kroku 7 i dodaj na końcu: `WHERE Budynki.nazwa_budynku = 'DS-1 Alkatraz';`.
14. Zrozum różnicę: Porównaj jeszcze raz wyniki `INNER JOIN` i `LEFT JOIN`. Kiedy `INNER JOIN` byłby lepszy, a kiedy `LEFT JOIN`?
15. Zadanie dodatkowe: Spróbuj napisać zapytanie, które pokaże tylko puste pokoje. (Wskazówka: użyj `LEFT JOIN` i warunku `WHERE` sprawdzającego, czy pole z tabeli `Studenci` jest `NULL`).
16. Dobra praktyka: Zawsze określaj, z której tabeli pochodzi kolumna, gdy łączysz tabele (np. `Studenci.imie`, a nie samo `imie`), aby uniknąć niejednoznaczności, zwłaszcza gdy kolumny mają te same nazwy.
17. Wyloguj się: Zakończ pracę.

Przykładowy Kod SQL

-- Krok 7 & 8: Pełna lista studentów z danymi o ich miejscu zamieszkania (z użyciem aliasów)
SELECT
    s.imie,
    s.nazwisko,
    p.numer_pokoju,
    b.nazwa_budynku,
    b.adres_budynku
FROM
    Studenci AS s
INNER JOIN
    Pokoje AS p ON s.id_pokoju = p.id_pokoju
INNER JOIN
    Budynki AS b ON p.id_budynku = b.id_budynku;

-- Krok 10: Lista wszystkich pokoi i ich mieszkańców (jeśli są)
SELECT
    b.nazwa_budynku,
    p.numer_pokoju,
    s.imie,
    s.nazwisko
FROM
    Pokoje AS p
JOIN
    Budynki AS b ON p.id_budynku = b.id_budynku
LEFT JOIN
    Studenci AS s ON p.id_pokoju = s.id_pokoju
ORDER BY
    b.nazwa_budynku, p.numer_pokoju;

-- Krok 15 (dodatkowe): Zapytanie o wolne pokoje
SELECT
    b.nazwa_budynku,
    p.numer_pokoju,
    p.pojemnosc
FROM
    Pokoje AS p
JOIN
    Budynki AS b ON p.id_budynku = b.id_budynku
LEFT JOIN
    Studenci AS s ON p.id_pokoju = s.id_pokoju
WHERE
    s.id_studenta IS NULL;

Zadanie 7: Agregacja i Grupowanie Danych (`GROUP BY`, `HAVING`)

Cel Zadania

Celem zadania jest wprowadzenie studenta w świat analizy danych za pomocą funkcji agregujących oraz grupowania. Student nauczy się używać podstawowych funkcji takich jak `COUNT()` (liczenie wierszy), `SUM()` (sumowanie), `AVG()` (średnia), `MIN()` (minimum) i `MAX()` (maksimum). Kluczowym elementem jest zrozumienie i zastosowanie klauzuli `GROUP BY`, która pozwala na agregowanie danych w podziale na kategorie (np. zliczanie studentów w każdym budynku). Dodatkowo, student pozna klauzulę `HAVING`, która działa jak `WHERE`, ale służy do filtrowania wyników już po wykonaniu grupowania, co pozwala na zadawanie bardziej zaawansowanych pytań analitycznych. 📊

Scenariusz

Kierownictwo sieci akademików jest zadowolone z Twoich poprzednich raportów, ale teraz potrzebuje danych statystycznych. Musisz odpowiedzieć na kilka pytań. Po pierwsze: ilu łącznie studentów mieszka we wszystkich akademikach? Po drugie: jak rozkłada się liczba studentów na poszczególne budynki? Kierownik chce zobaczyć tabelę, w której będzie nazwa budynku i liczba zamieszkujących go osób. Po trzecie, interesuje ich, jaka jest maksymalna i minimalna pojemność pokoju w całej sieci. Na koniec, chcą otrzymać specjalny raport: listę budynków, w których mieszka WIĘCEJ NIŻ 5 studentów. Ostatnie zadanie jest podchwytliwe i nie da się go rozwiązać za pomocą samej klauzuli `WHERE`. Do wykonania tych zadań niezbędne będzie wykorzystanie funkcji agregujących i grupowania.

Pomocnik Wykonania (Kroki)

1. Połącz się z bazą danych: Użyj `akademik_db`.
2. Pierwsze pytanie - całkowita liczba studentów: To najprostsza agregacja. Użyj funkcji `COUNT()`.
3. Napisz zapytanie zliczające: Wpisz `SELECT COUNT(*) FROM Studenci;`. Gwiazdka `*` oznacza zliczanie wszystkich wierszy. Można też użyć `COUNT(id_studenta)`.
4. Użyj aliasu kolumny: Aby wynik był czytelniejszy, użyj aliasu: `SELECT COUNT(*) AS Calkowita_Liczba_Studentow FROM Studenci;`.
5. Drugie pytanie - studenci per budynek: Tu potrzebujesz `GROUP BY`. Musisz połączyć tabele, aby dostać się do nazwy budynku.
6. Złącz tabele: Zacznij od złączenia `Studenci -> Pokoje -> Budynki` tak, jak w poprzednim zadaniu.
7. Dodaj `GROUP BY`: Do zapytania dodaj na końcu `GROUP BY Budynki.nazwa_budynku;`.
8. Wybierz dane do wyświetlenia: W klauzuli `SELECT` możesz umieścić tylko kolumny, po których grupujesz, oraz funkcje agregujące. Napisz: `SELECT b.nazwa_budynku, COUNT(s.id_studenta) AS Liczba_Mieszkancow FROM Studenci s JOIN Pokoje p ON s.id_pokoju = p.id_pokoju JOIN Budynki b ON p.id_budynku = b.id_budynku GROUP BY b.nazwa_budynku;`.
9. Trzecie pytanie - min/max pojemność: To zadanie nie wymaga grupowania. Użyj funkcji `MIN()` i `MAX()` na tabeli `Pokoje`.
10. Napisz zapytanie min/max: `SELECT MIN(pojemnosc) AS Min_Pojemnosc, MAX(pojemnosc) AS Max_Pojemnosc FROM Pokoje;`.
11. Czwarte pytanie - filtrowanie po agregacji: Potrzebujesz listy budynków, gdzie liczba mieszkańców jest większa niż 5. Nie możesz użyć `WHERE COUNT(...) > 5`.
12. Użyj klauzuli `HAVING`: Weź zapytanie z kroku 8 i dodaj na końcu klauzulę `HAVING`.
13. Napisz pełne zapytanie z `HAVING`: `... GROUP BY b.nazwa_budynku HAVING COUNT(s.id_studenta) > 5;`.
14. Zrozum różnicę `WHERE` vs `HAVING`: `WHERE` filtruje wiersze PRZED grupowaniem. `HAVING` filtruje grupy PO zagregowaniu.
15. Eksperyment: Zmodyfikuj zapytanie z kroku 8, aby pokazywało także średnią pojemność pokoi w każdym budynku (`AVG(p.pojemnosc)`).
16. Dodaj więcej danych: Jeśli w Twojej bazie jest za mało studentów, aby `HAVING > 5` cokolwiek zwróciło, dodaj więcej rekordów za pomocą `INSERT`, aby móc przetestować zapytanie.
17. Sortowanie wyników: Do zapytania z grupowaniem możesz dodać `ORDER BY`, aby posortować wyniki, np. `ORDER BY Liczba_Mieszkancow DESC;`.
18. Zakończ pracę.

Przykładowy Kod SQL

-- Krok 8: Zliczanie studentów w podziale na budynki
SELECT
    b.nazwa_budynku,
    COUNT(s.id_studenta) AS Liczba_Mieszkancow
FROM
    Studenci AS s
JOIN
    Pokoje AS p ON s.id_pokoju = p.id_pokoju
JOIN
    Budynki AS b ON p.id_budynku = b.id_budynku
GROUP BY
    b.nazwa_budynku
ORDER BY
    Liczba_Mieszkancow DESC;

-- Krok 13: Wyświetlanie tylko tych budynków, które mają więcej niż X mieszkańców
SELECT
    b.nazwa_budynku,
    COUNT(s.id_studenta) AS Liczba_Mieszkancow
FROM
    Studenci AS s
JOIN Pokoje AS p ON s.id_pokoju = p.id_pokoju
JOIN Budynki AS b ON p.id_budynku = b.id_budynku
GROUP BY
    b.nazwa_budynku
HAVING
    COUNT(s.id_studenta) > 5; -- Zmień 5 na mniejszą liczbę do testów, np. 1

-- Krok 15 (eksperyment): Statystyki dla każdego budynku
SELECT
    b.nazwa_budynku,
    COUNT(s.id_studenta) AS Liczba_Mieszkancow,
    AVG(p.pojemnosc) AS Srednia_Pojemnosc_Pokoju
FROM
    Budynki AS b
JOIN
    Pokoje AS p ON b.id_budynku = p.id_budynku
LEFT JOIN
    Studenci AS s ON p.id_pokoju = s.id_pokoju
GROUP BY
    b.nazwa_budynku;

Zadanie 8 & 9: Wprowadzenie do Normalizacji (1NF, 2NF, 3NF)

Cel Zadania

Jest to kluczowe zadanie teoretyczno-praktyczne, którego celem jest nauczenie studenta fundamentalnej koncepcji projektowania baz danych – normalizacji. Student otrzyma źle zaprojektowaną, "płaską" tabelę, która łamie podstawowe zasady normalizacji, i jego zadaniem będzie przeprowadzenie jej przez kolejne etapy: od identyfikacji problemów (grupy powtarzające się, zależności częściowe i przechodnie) aż po jej rozbicie na mniejsze, logicznie powiązane tabele spełniające wymogi Trzeciej Postaci Normalnej (3NF). Celem jest zrozumienie, DLACZEGO normalizacja jest ważna: eliminuje redundancję danych, zapobiega anomaliom (problemom przy wstawianiu, aktualizacji i usuwaniu danych) oraz prowadzi do stworzenia elastycznego i łatwego w utrzymaniu schematu bazy danych. 🤓

Scenariusz

Dział sprzedaży w Twojej firmie do tej pory przechowywał dane o zamówieniach w jednym, wielkim arkuszu kalkulacyjnym. Poproszono Cię o przeniesienie tych danych do bazy danych. Otrzymujesz plik, który po zaimportowaniu wygląda jak jedna, wielka tabela `Zamowienia_Plaskie`. Tabela zawiera numer zamówienia, datę, dane klienta (ID, imię, nazwisko, adres), a także listę zakupionych produktów w tym zamówieniu (ID produktu, nazwa, cena, ilość). Już na pierwszy rzut oka widać problemy: dane klienta powtarzają się przy każdym jego zamówieniu. Co gorsza, jeśli klient zmieni adres, trzeba go aktualizować w wielu miejscach! Nazwa i cena produktu również są powielane w każdym zamówieniu, w którym dany produkt występuje. Twoim zadaniem jest posprzątanie tego bałaganu. Musisz zaprojektować nowy, znormalizowany schemat bazy danych i napisać skrypty DDL, które go utworzą, a następnie (teoretycznie) opisać, jak przenieść dane ze starej tabeli do nowej struktury.

Schemat Początkowy (Problem)

Pomocnik Wykonania (Kroki do 3NF)

1. Analiza tabeli: Spójrz na tabelę `Zamowienia_Plaskie` i zidentyfikuj główne problemy (redundancja danych klienta, redundancja danych produktu).
2. Cel - Pierwsza Postać Normalna (1NF): 1NF wymaga, aby każda komórka tabeli zawierała pojedynczą, atomową wartość, a każdy wiersz był unikalny. Nasza tabela już to prawie spełnia, ale potrzebuje klucza głównego. Kluczem głównym nie może być `id_zamowienia`, bo się powtarza. Kluczem mogłaby być para `(id_zamowienia, id_produktu)`. To prowadzi nas do kolejnego kroku.
3. Identyfikacja encji: Jakie główne "obiekty" lub "koncepcje" ukrywają się w tej tabeli? Są to: Klienci, Produkty, Zamówienia oraz Pozycje na zamówieniu.
4. Krok do 2NF - Wydzielenie Klientów: Stwórz tabelę `Klienci` z kolumnami `id_klienta` (PK), `imie_nazwisko_klienta`, `adres_klienta`.
5. Krok do 2NF - Wydzielenie Produktów: Stwórz tabelę `Produkty` z kolumnami `id_produktu` (PK), `nazwa_produktu`, `cena_produktu`.
6. Pozostałe dane - Zamówienia: Co zostało? `id_zamowienia`, `data_zam`, `id_klienta`. Te dane opisują samo zamówienie. Stwórz tabelę `Zamowienia`.
7. Struktura tabeli `Zamowienia`: Tabela `Zamowienia` powinna mieć `id_zamowienia` (PK), `data_zam` oraz klucz obcy `id_klienta` wskazujący na tabelę `Klienci`.
8. Cel - Druga Postać Normalna (2NF): 2NF jest spełniona, jeśli tabela jest w 1NF i wszystkie atrybuty niekluczowe są w pełni zależne od CAŁEGO klucza głównego. Nasze nowe tabele `Klienci` i `Produkty` mają proste klucze, więc automatycznie spełniają 2NF. Tabela `Zamowienia` również.
9. Łączenie Zamówień i Produktów: Jak teraz połączyć zamówienie z produktami, które zawiera? Potrzebujemy tabeli łączącej (asocjacyjnej). Nazwijmy ją `Pozycje_Zamowienia`.
10. Struktura `Pozycje_Zamowienia`: Tabela ta będzie implementować relację wiele-do-wielu między zamówieniami a produktami. Będzie zawierać klucze obce: `id_zamowienia` i `id_produktu`. Kluczem głównym tej tabeli będzie para `(id_zamowienia, id_produktu)`.
11. Dodanie atrybutu relacji: Gdzie przechowywać informację o ilości? `ilosc` zależy zarówno od zamówienia, jak i od produktu. Jest to idealny atrybut do umieszczenia w tabeli `Pozycje_Zamowienia`.
12. Cel - Trzecia Postać Normalna (3NF): 3NF jest spełniona, jeśli tabela jest w 2NF i nie ma zależności przechodnich (tranzytywnych). Zależność przechodnia występuje, gdy atrybut niekluczowy zależy od innego atrybutu niekluczowego.
13. Analiza 3NF: Sprawdźmy nasze nowe tabele:
    • `Klienci`: `adres` zależy od `id_klienta`, `imie_nazwisko` zależy od `id_klienta`. OK.
    • `Produkty`: `nazwa` i `cena` zależą od `id_produktu`. OK.
    • `Zamowienia`: `data` i `id_klienta` zależą od `id_zamowienia`. OK.
    • `Pozycje_Zamowienia`: `ilosc` zależy od pary `(id_zamowienia, id_produktu)`. OK.
14. Wniosek: Nasz nowy schemat składający się z 4 tabel jest w 3NF!
15. Napisz skrypty DDL: Stwórz finalne komendy `CREATE TABLE` dla czterech nowych tabel, pamiętając o kluczach głównych i obcych.

Schemat Wynikowy (po normalizacji do 3NF)

Przykładowy Kod SQL (Wynikowy)

-- Tabela Klientów
CREATE TABLE Klienci (
    id_klienta INT AUTO_INCREMENT PRIMARY KEY,
    imie_nazwisko VARCHAR(255) NOT NULL,
    adres_klienta TEXT
);

-- Tabela Produktów
CREATE TABLE Produkty (
    id_produktu INT AUTO_INCREMENT PRIMARY KEY,
    nazwa_produktu VARCHAR(255) NOT NULL,
    cena_produktu DECIMAL(10, 2) NOT NULL
);

-- Tabela Zamówień
CREATE TABLE Zamowienia (
    id_zamowienia INT AUTO_INCREMENT PRIMARY KEY,
    data_zam DATE NOT NULL,
    id_klienta INT,
    FOREIGN KEY (id_klienta) REFERENCES Klienci(id_klienta)
);

-- Tabela łącząca (Pozycje Zamówienia)
CREATE TABLE Pozycje_Zamowienia (
    id_zamowienia INT,
    id_produktu INT,
    ilosc INT NOT NULL,
    PRIMARY KEY (id_zamowienia, id_produktu),
    FOREIGN KEY (id_zamowienia) REFERENCES Zamowienia(id_zamowienia),
    FOREIGN KEY (id_produktu) REFERENCES Produkty(id_produktu)
);

Zadanie 10: Projekt Końcowy - Baza Danych "Mała Biblioteka" 📚

Cel Zadania

Jest to zadanie podsumowujące, które wymaga od studenta zastosowania całej wiedzy zdobytej w poprzednich ćwiczeniach. Celem jest samodzielne zaprojektowanie od zera, a następnie zaimplementowanie w pełni funkcjonalnego, znormalizowanego (do 3NF) schematu bazy danych dla małej, osiedlowej biblioteki. Student musi przeanalizować wymagania, zidentyfikować kluczowe encje i atrybuty, poprawnie zamodelować relacje między nimi (jeden-do-wielu i wiele-do-wielu), napisać skrypty DDL do stworzenia struktury, a na koniec przygotować kilka przykładowych zapytań `SELECT`, które mogłyby być używane w systemie bibliotecznym. To zadanie sprawdza nie tylko techniczną umiejętność pisania kodu SQL, ale przede wszystkim zdolność analitycznego myślenia i projektowania systemów bazodanowych.

Scenariusz

Zostałeś zatrudniony jako konsultant IT w lokalnej bibliotece, która do tej pory prowadziła cały swój katalog i ewidencję wypożyczeń w papierowych kartotekach. Twoim zadaniem jest stworzenie dla nich cyfrowego systemu opartego na bazie danych MariaDB/MySQL. System musi przechowywać informacje o posiadanych książkach (tytuł, rok wydania, ISBN). Biblioteka chce również mieć katalog autorów (imię, nazwisko, data urodzenia), wiedząc, że jedna książka może mieć wielu autorów, a jeden autor może napisać wiele książek. Należy również prowadzić rejestr czytelników (imię, nazwisko, numer karty bibliotecznej, adres email). Najważniejszym elementem systemu jest moduł wypożyczeń. System musi rejestrować, który czytelnik wypożyczył którą książkę, datę wypożyczenia oraz oczekiwaną datę zwrotu. Należy również przewidzieć pole na faktyczną datę zwrotu, które początkowo będzie puste. Twoim zadaniem jest zaprojektowanie kompletnego schematu bazy, który będzie wydajny, spójny i wolny od anomalii.

Schemat Bazy Danych (Propozycja do wykonania)

Pomocnik Wykonania (Kroki)

1. Analiza wymagań i identyfikacja encji: Wypisz główne obiekty systemu: `Ksiazki`, `Autorzy`, `Czytelnicy`, `Wypozyczenia`.
2. Modelowanie relacji: Zastanów się nad związkami:
   • Książka-Autor: Jeden autor może napisać wiele książek, jedna książka może mieć wielu autorów. To jest relacja wiele-do-wielu (M:N). Wymaga tabeli łączącej `Ksiazki_Autorzy`.
   • Czytelnik-Wypożyczenie: Jeden czytelnik może mieć wiele wypożyczeń. To jest relacja jeden-do-wielu (1:N).
   • Książka-Wypożyczenie: Jedna książka (jeden egzemplarz) może być w wielu wypożyczeniach (ale nie w tym samym czasie - upraszczamy ten model). To jest relacja jeden-do-wielu (1:N).
3. Projektowanie tabel i atrybutów: Rozpisz kolumny dla każdej z pięciu tabel (`Autorzy`, `Ksiazki`, `Ksiazki_Autorzy`, `Czytelnicy`, `Wypozyczenia`). Wybierz odpowiednie typy danych.
4. Utwórz nową bazę danych: `CREATE DATABASE biblioteka_db;` i `USE biblioteka_db;`.
5. Napisz DDL dla tabel bez zależności: Zacznij od tabel `Autorzy`, `Ksiazki` i `Czytelnicy`.
6. Napisz DDL dla tabeli łączącej: Stwórz tabelę `Ksiazki_Autorzy`. Jej klucz główny powinien być złożony z dwóch kluczy obcych: `PRIMARY KEY (id_ksiazki, id_autora)`.
7. Napisz DDL dla tabeli `Wypozyczenia`: Stwórz tę tabelę, dodając klucze obce do `Czytelnicy` i `Ksiazki`. Pamiętaj, że `data_faktycz_zwrotu` powinna dopuszczać `NULL`.
8. Wstaw dane testowe: Dodaj co najmniej 3 autorów, 5 książek, 4 czytelników.
9. Powiąż książki z autorami: Wypełnij tabelę `Ksiazki_Autorzy`. Przypisz jedną książkę do dwóch autorów.
10. Zarejestruj wypożyczenia: Dodaj kilka rekordów do tabeli `Wypozyczenia`. Część z nich pozostaw bez daty zwrotu (`NULL`).
11. Napisz zapytanie #1 (Lista książek i ich autorów): Użyj `JOIN` przez tabelę `Ksiazki_Autorzy`, aby wyświetlić listę tytułów książek wraz z imionami i nazwiskami ich autorów.
12. Napisz zapytanie #2 (Aktywne wypożyczenia): Stwórz zapytanie, które wyświetli wszystkie aktualnie wypożyczone książki (te, gdzie `data_faktycz_zwrotu` jest `NULL`). Zapytanie powinno pokazywać tytuł książki oraz imię i nazwisko czytelnika.
13. Napisz zapytanie #3 (Książki po terminie): Napisz zapytanie, które znajdzie wypożyczenia przetrzymane. Warunek to `data_faktycz_zwrotu IS NULL AND data_oczek_zwrotu < CURDATE()`. `CURDATE()` to funkcja MySQL zwracająca bieżącą datę.
14. Napisz zapytanie #4 (Historia wypożyczeń czytelnika): Zapytanie, które dla danego `id_czytelnika` pokaże wszystkie książki, jakie kiedykolwiek wypożyczył, wraz z datami.
15. Napisz zapytanie #5 (Najpopularniejsi autorzy): Użyj `GROUP BY` i `COUNT`, aby policzyć, ile razy książki danego autora były wypożyczane.
16. Zastanów się nad ulepszeniami: Jak można by rozbudować ten schemat? (Np. dodać tabelę `Egzemplarze` pozwalającą na posiadanie wielu kopii tej samej książki, kategorie książek, system rezerwacji).

Przykładowy Kod SQL (Zapytania analityczne)

-- Zapytanie #2: Lista aktualnie wypożyczonych książek
SELECT
    k.tytul,
    c.imie,
    c.nazwisko,
    w.data_wypozyczenia,
    w.data_oczek_zwrotu
FROM
    Wypozyczenia AS w
JOIN
    Ksiazki AS k ON w.id_ksiazki = k.id_ksiazki
JOIN
    Czytelnicy AS c ON w.id_czytelnika = c.id_czytelnika
WHERE
    w.data_faktycz_zwrotu IS NULL;

-- Zapytanie #3: Lista książek przetrzymanych (po terminie)
SELECT
    c.imie,
    c.nazwisko,
    k.tytul,
    w.data_oczek_zwrotu
FROM
    Wypozyczenia AS w
JOIN Czytelnicy AS c ON w.id_czytelnika = c.id_czytelnika
JOIN Ksiazki AS k ON w.id_ksiazki = k.id_ksiazki
WHERE
    w.data_faktycz_zwrotu IS NULL AND w.data_oczek_zwrotu < CURDATE();