Projektowanie bazy danych — od wymagań do schematu

Po co się tego uczymy?

Na egzaminie i w pracy rzadko dostajesz gotowy schemat bazy — zwykle dostajesz opis słowny ("zaprojektuj system rezerwacji hotelu") i musisz sam dojść do tabel i relacji. Ta lekcja porządkuje kroki, żeby robić to systematycznie, a nie na chybił trafił.

Teoria

Dobra baza danych nie powstaje przez pisanie CREATE TABLE "na wyczucie". Powstaje w kilku uporządkowanych krokach — dokładnie tak, jak buduje się dom według planu, a nie na oko.

Krok 1 — analiza wymagań. Zastanawiasz się: jakie informacje mam przechowywać? Jakie obiekty (rzeczy) istnieją w systemie? Przykład: w dzienniku elektronicznym są uczniowie, klasy, przedmioty i oceny.

Krok 2 — wskazanie encji i atrybutów. Dla każdego obiektu z kroku 1 tworzysz encję z listą jej cech. UCZEŃ: imię, nazwisko, data urodzenia. KLASA: nazwa, wychowawca.

Krok 3 — określenie relacji. Jak encje się łączą? Uczeń NALEŻY DO jednej klasy (1:N). Uczeń MA WIELE ocen Z WIELU przedmiotów (N:M, przez tabelę pośredniczącą).

Krok 4 — rysowanie diagramu E-R. Zbierasz wszystko z kroków 2-3 w jeden graficzny schemat (patrz lekcja o modelu ER).

Krok 5 — normalizacja. Sprawdzasz, czy żadne dane się nie powtarzają niepotrzebnie (patrz lekcja o normalizacji).

Krok 6 — implementacja w SQL. Dopiero TERAZ piszesz CREATE DATABASE i CREATE TABLE — reszta lekcji w tym dziale uczy Cię właśnie tego ostatniego kroku, ale bez kroków 1-5 łatwo zaprojektować bazę, którą trzeba będzie przebudować od zera.

Dobrze zaprojektowana baza: unika powtórzeń danych, jest logiczna i przejrzysta, umożliwia szybkie wyszukiwanie, łatwo się rozwija, gdy trzeba dodać coś nowego.

Przykład z życia

Klient zamawia u Ciebie system do zarządzania wypożyczalnią sprzętu narciarskiego. Dostajesz tylko opis słowny sytuacji — cała reszta (jakie tabele, jakie kolumny, jakie relacje) to Twoja praca projektowa, zanim w ogóle otworzysz edytor SQL.

Kod (SQL)

-- Przykład zastosowania kroków 1-6 dla "systemu wypożyczalni sprzętu":

-- Krok 1-3: encje SPRZET, KLIENT, WYPOZYCZENIE (relacja N:M między SPRZET i KLIENT)

-- Krok 6: implementacja


CREATE TABLE Sprzet (
    id_sprzetu INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100) NOT NULL,
    stan_techniczny VARCHAR(20) DEFAULT 'sprawny'
);

CREATE TABLE Klienci (
    id_klienta INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50) NOT NULL,
    nazwisko VARCHAR(50) NOT NULL
);

CREATE TABLE Wypozyczenia (
    id_wypozyczenia INT AUTO_INCREMENT PRIMARY KEY,
    id_sprzetu INT NOT NULL,
    id_klienta INT NOT NULL,
    data_wypozyczenia DATE NOT NULL,
    data_zwrotu DATE,
    FOREIGN KEY (id_sprzetu) REFERENCES Sprzet(id_sprzetu),
    FOREIGN KEY (id_klienta) REFERENCES Klienci(id_klienta)
);

Komentarz i wyjaśnienie kodu

Zanim powstał ten kod, przeszliśmy przez kroki 1-5 w głowie: rozpoznaliśmy trzy encje (Sprzęt, Klient, Wypożyczenie), zauważyliśmy że Wypożyczenie to w rzeczywistości tabela pośrednicząca realizująca relację N:M między sprzętem a klientami (jeden klient może wypożyczyć wiele przedmiotów w różnym czasie, jeden przedmiot może być wypożyczany przez wielu klientów, ale nie jednocześnie). Dopiero po tej analizie napisaliśmy CREATE TABLE.

Ćwiczenie samodzielne

Dla opisu "system zapisów na zajęcia dodatkowe w szkole" wykonaj kroki 1-4 (encje, atrybuty, relacje, opis diagramu) na kartce, zanim napiszesz jakikolwiek SQL.

Zadania do pracy własnej

Zadanie 1. Zaprojektuj wypożyczalnię samochodów

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: Baza egzaminacyjna restauracja/ogłoszenia (.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. Zaprojektuj sklep zoologiczny

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 zoologiczny (.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 3. Zaprojektuj aptekę

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: Apteka Zdrowie (.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.

Typowe błędy

Najczęstszy błąd to pominięcie kroków 1-5 i pisanie CREATE TABLE od razu "z głowy" — kończy się to przebudowywaniem struktury w połowie projektu. Drugi błąd to projektowanie zbyt ogólnych tabel (jedna wielka tabela "Dane" zamiast osobnych, logicznych encji). Trzeci: brak konsultacji opisu wymagań ze wszystkimi zdaniami w treści zadania — łatwo przeoczyć relację wspomnianą tylko raz w opisie.

Nawiązanie do egzaminu zawodowego

To realizacja efektu INF.03.4.2 w pełnym zakresie — projekt bazy danych na podstawie opisu sytuacji to typowy punkt wyjścia zadania praktycznego na egzaminie (np. baza biura turystycznego w przykładowym zadaniu z informatora CKE).