Wprowadzenie do baz danych i model ER

Po co się tego uczymy?

Każda poważniejsza aplikacja — dziennik elektroniczny, sklep internetowy, system rezerwacji — musi gdzieś trwale przechowywać dane i wiedzieć, jak te dane się ze sobą łączą. Zanim napiszesz pierwszą linijkę SQL, musisz umieć to zaprojektować na papierze. Bez tego kroku bazy danych szybko zamieniają się w chaos.

Teoria

Baza danych to zorganizowany zbiór danych przechowywanych w tabelach. W relacyjnej bazie danych (a takich uczysz się w szkole i takie są na egzaminie) dane w różnych tabelach łączą się ze sobą przez klucze.

Najważniejsze pojęcia:
- Encja — rzecz albo obiekt, o którym przechowujemy dane, np. UCZEŃ, KURS, PRODUKT. W SQL encja zwykle staje się tabelą.
- Atrybut — cecha encji, np. imię, nazwisko, cena. W SQL atrybut staje się kolumną.
- Klucz główny (PRIMARY KEY) — kolumna, która jednoznacznie identyfikuje każdy wiersz w tabeli (nie mogą się powtarzać, nie może być pusty).
- Klucz obcy (FOREIGN KEY) — kolumna, która wskazuje na klucz główny w innej tabeli. To właśnie klucze obce tworzą relacje między tabelami.

Zanim zaczniesz pisać SQL, rysuje się model E-R (Entity-Relationship) — graficzny plan tego, jakie będą encje, jakie atrybuty i jak się łączą. To jest dokładnie to samo, co architekt robi z planem domu, zanim wyleje fundamenty.

Są trzy podstawowe typy relacji między encjami:
1:1 (jeden do jednego) — np. jeden uczeń ma dokładnie jedną legitymację.
1:N (jeden do wielu) — np. jedna klasa ma wielu uczniów, ale każdy uczeń należy tylko do jednej klasy.
N:M (wiele do wielu) — np. jeden uczeń może chodzić na wiele kursów, a jeden kurs ma wielu uczniów. Relację N:M zawsze realizuje się przez dodatkową tabelę pośredniczącą (na diagramie niżej to tabela ZAPISY).

Schemat

Przykład z życia

Pomyśl o dzienniku elektronicznym szkoły. Są w nim uczniowie, klasy, przedmioty i oceny. Uczeń należy do jednej klasy (1:N), ale może mieć oceny z wielu przedmiotów, a każdy przedmiot mają oceny wielu uczniów — to już relacja N:M, którą w bazie realizuje dodatkowa tabela (np. Oceny), tak jak na diagramie powyżej tabela ZAPISY łączy uczniów z kursami.

Kod (SQL)

-- Tabela nadrzędna: klasa (musi powstać PIERWSZA)

CREATE TABLE Klasa (
    id_klasy INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(10) NOT NULL
);

-- Tabela podrzędna: uczeń, powiązana z klasą kluczem obcym

CREATE TABLE Uczen (
    id_ucznia INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50) NOT NULL,
    nazwisko VARCHAR(50) NOT NULL,
    id_klasy INT NOT NULL,
    FOREIGN KEY (id_klasy) REFERENCES Klasa(id_klasy)
);

Komentarz i wyjaśnienie kodu

Zwróć uwagę na kolejność: tabelę Klasa trzeba utworzyć PRZED tabelą Uczen, bo Uczen odwołuje się do niej przez FOREIGN KEY — nie da się wskazać kluczem obcym na tabelę, która jeszcze nie istnieje.

id_klasy w tabeli Uczen to klucz obcy — każdy uczeń "wie", do jakiej klasy należy, bo przechowuje numer tej klasy (id_klasy), a nie np. jej nazwę powtórzoną w każdym wierszu. To jest właśnie sens relacji: dane nie duplikują się, tylko odwołują się do siebie przez klucze.

Ćwiczenie samodzielne

Narysuj (na kartce, w PowerPoincie albo w dowolnym edytorze diagramów) model E-R dla biblioteki szkolnej z trzema encjami: KSIĄŻKA, CZYTELNIK, WYPOŻYCZENIE. Określ, jaki typ relacji łączy czytelnika z książką (podpowiedź: potrzebna będzie tabela pośrednicząca, tak jak w przykładzie z kursami).

Zadania do pracy własnej

Zadanie 1. Rozpoznaj dane i encje w sklepie muzycznym

Uwaga przed importem: plik z bazą jest spakowany jako .gz. phpMyAdmin w XAMPP przyjmuje takie pliki bez rozpakowywania. Jeżeli po pobraniu nazwa wygląda np. sklep_muzyczny.sql_.gz i import nie przejdzie, zmień nazwę pliku na sklep_muzyczny.sql.gz, a potem wybierz go w zakładce Importuj.

Baza do pracy: Sklep muzyczny (.sql.gz). Zaimportuj spakowany plik SQL w XAMPP/phpMyAdmin, a następnie wykonaj zapytania.
1. Wypisz encje występujące w opisanym systemie.
2. Dla każdej encji zaproponuj co najmniej 5 atrybutów.
3. Wskaż klucze główne.
4. Wskaż klucze obce.
5. Określ relacje 1:1, 1:N i N:M.
6. Zaprojektuj tabelę pośredniczącą dla relacji N:M.
7. Zapisz strukturę tabel w SQL.
8. Wskaż dane, których nie powinno się powtarzać.
9. Narysuj prosty ERD w zeszycie lub narzędziu online.
10. Porównaj projekt z gotowym plikiem SQL i zaznacz różnice.
Zadanie 2. Szkoła jako model relacyjny

Uwaga przed importem: plik z bazą jest spakowany jako .gz. phpMyAdmin w XAMPP przyjmuje takie pliki bez rozpakowywania. Jeżeli po pobraniu nazwa wygląda np. sklep_muzyczny.sql_.gz i import nie przejdzie, zmień nazwę pliku na sklep_muzyczny.sql.gz, a potem wybierz go w zakładce Importuj.

Baza do pracy: Szkoła relacje (.sql.gz). Zaimportuj spakowany plik SQL w XAMPP/phpMyAdmin, a następnie wykonaj zapytania.
1. Wyświetl uczniów wraz z nazwą klasy.
2. Wyświetl uczniów wraz z numerem legitymacji.
3. Wyświetl uczniów i przedmioty, na które uczęszczają.
4. Policz uczniów w każdej klasie.
5. Policz ilu uczniów ma każdy przedmiot.
6. Wyświetl uczniów z klasy 1P i ich przedmioty.
7. Wyświetl klasy wraz z wychowawcą i liczbą uczniów.
8. Wyświetl uczniów, którzy mają więcej niż jeden przedmiot.
9. Wyświetl przedmioty, na które nie zapisano żadnego ucznia.
10. Wyświetl pełny raport: klasa, uczeń, legitymacja, przedmiot.
Zadanie 3. Mini audyt pojęć SQL

Uwaga przed importem: plik z bazą jest spakowany jako .gz. phpMyAdmin w XAMPP przyjmuje takie pliki bez rozpakowywania. Jeżeli po pobraniu nazwa wygląda np. sklep_muzyczny.sql_.gz i import nie przejdzie, zmień nazwę pliku na sklep_muzyczny.sql.gz, a potem wybierz go w zakładce Importuj.

Baza do pracy: Sklep muzyczny 2P (.sql.gz). Zaimportuj spakowany plik SQL w XAMPP/phpMyAdmin, a następnie wykonaj zapytania.
1. Znajdź w bazie przykład tabeli.
2. Znajdź przykład rekordu.
3. Znajdź przykład kolumny tekstowej.
4. Znajdź przykład klucza głównego.
5. Znajdź przykład klucza obcego.
6. Znajdź przykład relacji 1:N.
7. Znajdź przykład relacji N:M.
8. Znajdź zapytanie używające WHERE.
9. Znajdź zapytanie używające GROUP BY.
10. Znajdź zapytanie używające JOIN.

Typowe błędy

Uczniowie często mylą klucz główny z kluczem obcym — klucz główny identyfikuje wiersz WE WŁASNEJ tabeli, klucz obcy wskazuje na wiersz W INNEJ tabeli. Drugi typowy błąd to próba zrobienia relacji wiele-do-wielu bez tabeli pośredniczącej — to się po prostu nie da zrobić bezpośrednio w relacyjnej bazie danych. Trzeci błąd: pisanie SQL-a od razu, bez wcześniejszego zaprojektowania modelu na papierze — kończy się to przeprojektowywaniem tabel w połowie pracy.

Nawiązanie do egzaminu zawodowego

To jest dokładnie zakres kwalifikacji INF.03.4 "Projektowanie i administrowanie bazami danych" — na egzaminie pisemnym pojawiają się pytania o rozpoznawanie symboli na diagramach E/R i określanie typu relacji (1:1, 1:N, N:M) na podstawie opisu sytuacji, a w części praktycznej egzamin może wymagać zaprojektowania bazy danych na podstawie treningowego opisu (np. dla biura turystycznego).