Projektowanie baz danych

Teoria i podstawy

1Wstęp do ERD – kluczowe pojęcia

Cel: Poznanie pojęcia encji, atrybutu i związku.

Encja

Obiekt lub pojęcie z rzeczywistości, które przechowujemy w bazie – np. Klient, Produkt.

Atrybut

Właściwość encji – np. imię, cena, data_urodzenia.

Klucz główny (PK)

Unikalny identyfikator rekordu – np. id_klienta INT AUTO_INCREMENT.

Klucz obcy (FK)

Kolumna wskazująca na PK innej tabeli – tworzy relację między encjami.

Związek (relacja)

Powiązanie między encjami: 1:1, 1:N, M:N (wiele do wielu).

Diagram ERD

Graficzna reprezentacja encji, atrybutów i relacji w bazie danych.

✏️ Twoje notatki i odpowiedzi:

Metodologia

2Etapy projektowania bazy danych

Cel: Poznanie procesu projektowania bazy danych od wymagań do implementacji.

1
Analiza wymagań – rozmowa z klientem, zbieranie danych o systemie, ustalenie zakresu projektu.
2
Identyfikacja encji i atrybutów – wypisanie obiektów świata rzeczywistego i ich właściwości.
3
Określenie relacji – ustalenie, jak encje są ze sobą powiązane (1:1, 1:N, M:N).
4
Tworzenie diagramu ER – graficzne odwzorowanie struktury w narzędziu CASE lub na papierze.
5
Normalizacja (1NF → 2NF → 3NF) – eliminacja redundancji i anomalii danych.
6
Generowanie SQL i implementacja – tworzenie tabel w SZBD, np. MySQL przez XAMPP.

Polecenie: Zaprojektuj bazę danych dla wypożyczalni rowerów. Encje: Klient, Rower, Wypożyczenie.

✏️ Opisz etapy projektowania dla wypożyczalni rowerów:

Oprogramowanie CASE

3Diagram ER w narzędziu CASE

Cel: Praca z oprogramowaniem CASE – tworzenie diagramów i generowanie SQL.

Czym są narzędzia CASE?

CASE (Computer-Aided Software Engineering) to oprogramowanie wspomagające inżynierię oprogramowania. Umożliwia wizualne projektowanie struktury bazy, a następnie automatyczne generowanie kodu SQL.

Popularne narzędzia:

🔧 MySQL Workbench 🌐 draw.io (bezpłatny) 📐 SQL Developer Data Modeler 📊 dbdiagram.io 🗂 ERDplus

Polecenie: Stwórz diagram ER dla bazy danych biblioteki. Encje: Czytelnik, Książka, Wypożyczenie. Wygeneruj lub przepisz poniższy kod SQL.

SQL · DDL – Biblioteka

CREATE DATABASE biblioteka;
USE biblioteka;

CREATE TABLE czytelnicy (
    id_czytelnika  INT AUTO_INCREMENT PRIMARY KEY,
    imie           VARCHAR(80)  NOT NULL,
    nazwisko       VARCHAR(100) NOT NULL,
    email          VARCHAR(150) UNIQUE,
    data_zapisu    DATE         DEFAULT (CURRENT_DATE)
);

CREATE TABLE ksiazki (
    id_ksiazki     INT AUTO_INCREMENT PRIMARY KEY,
    tytul          VARCHAR(250) NOT NULL,
    autor          VARCHAR(150),
    isbn           VARCHAR(20)  UNIQUE,
    rok_wydania    YEAR,
    dostepna       TINYINT(1)   DEFAULT 1
);

CREATE TABLE wypozyczenia (
    id_wypozyczenia INT AUTO_INCREMENT PRIMARY KEY,
    id_czytelnika   INT NOT NULL,
    id_ksiazki      INT NOT NULL,
    data_od         DATE DEFAULT (CURRENT_DATE),
    data_do         DATE,
    zwrocona        TINYINT(1) DEFAULT 0,
    FOREIGN KEY (id_czytelnika) REFERENCES czytelnicy(id_czytelnika),
    FOREIGN KEY (id_ksiazki)    REFERENCES ksiazki(id_ksiazki)
);

💡

W MySQL Workbench: Database → Reverse Engineer – zaimportuj SQL i automatycznie uzyskasz diagram ERD gotowy do edycji.

✏️ Twoje notatki (opisz użyte narzędzie, co zrobiłeś krok po kroku):

Normalizacja 1NF → 3NF

4Pełny projekt + normalizacja – System rezerwacji noclegów

Cel: Stworzenie kompleksowego projektu zgodnego z 1NF, 2NF i 3NF.

Postacie normalne – w skrócie

1NF – każda kolumna przechowuje jedną wartość atomową, brak powtarzających się grup kolumn.
2NF – spełnia 1NF + każdy atrybut niebędący kluczem zależy od całego klucza głównego.
3NF – spełnia 2NF + brak przechodnich zależności funkcyjnych (atrybut → atrybut → klucz).

Postać	Reguła	Przykład problemu do usunięcia
1NF	Wartości atomowe, brak grup	Kolumna `telefony` = "123, 456" → dwie osobne kolumny lub tabela pomocnicza
2NF	Pełna zależność od PK	Klucz złożony: `id_rez + id_pokoju` → nazwa hotelu zależy tylko od id_pokoju, nie od całości
3NF	Brak zależności przechodnich	Kod pocztowy → miasto → województwo (usunąć, przenieść do osobnej tabeli)

Polecenie: Zaprojektuj i znormalizuj do 3NF bazę dla systemu rezerwacji noclegów. Encje: Klient, Rezerwacja, Pokój, Hotel.

SQL · DDL – Rezerwacje (3NF)

CREATE DATABASE rezerwacje;
USE rezerwacje;

-- 1. Hotele (przeniesione dane o hotelu – eliminacja 3NF)
CREATE TABLE hotele (
    id_hotelu    INT AUTO_INCREMENT PRIMARY KEY,
    nazwa        VARCHAR(200) NOT NULL,
    miasto       VARCHAR(100),
    adres        VARCHAR(250),
    gwiazdki     TINYINT
);

-- 2. Pokoje (zależy wyłącznie od id_pokoju – 2NF)
CREATE TABLE pokoje (
    id_pokoju    INT AUTO_INCREMENT PRIMARY KEY,
    id_hotelu    INT NOT NULL,
    numer_pokoju VARCHAR(10),
    typ          ENUM('jednoosobowy','dwuosobowy','apartament') DEFAULT 'jednoosobowy',
    cena_za_noc  DECIMAL(8,2),
    FOREIGN KEY (id_hotelu) REFERENCES hotele(id_hotelu)
);

-- 3. Klienci
CREATE TABLE klienci (
    id_klienta   INT AUTO_INCREMENT PRIMARY KEY,
    imie         VARCHAR(80)  NOT NULL,
    nazwisko     VARCHAR(100) NOT NULL,
    email        VARCHAR(150) UNIQUE,
    telefon      VARCHAR(20)
);

-- 4. Rezerwacje
CREATE TABLE rezerwacje (
    id_rezerwacji INT AUTO_INCREMENT PRIMARY KEY,
    id_klienta    INT  NOT NULL,
    id_pokoju     INT  NOT NULL,
    data_od       DATE NOT NULL,
    data_do       DATE NOT NULL,
    status        ENUM('oczekuje','potwierdzona','anulowana') DEFAULT 'oczekuje',
    FOREIGN KEY (id_klienta) REFERENCES klienci(id_klienta),
    FOREIGN KEY (id_pokoju)  REFERENCES pokoje(id_pokoju)
);

✏️ Opisz strategię projektowania, uzasadnij normalizację, dopisz kod SQL:

Praktyka – zapytania

5Zapytania SELECT na gotowej bazie – Biblioteka

Polecenie: Uruchom poniższe zapytania na bazie biblioteka. Sprawdź wyniki w phpMyAdmin.

SQL · SELECT + JOIN

-- Wstaw przykładowe dane
INSERT INTO czytelnicy (imie, nazwisko, email) VALUES
    ('Anna', 'Kowalska', 'anna@mail.pl'),
    ('Jan',  'Nowak',    'jan@mail.pl');

INSERT INTO ksiazki (tytul, autor, isbn, rok_wydania) VALUES
    ('Pan Tadeusz',   'Adam Mickiewicz', '978-83-0001', 1834),
    ('Lalka',         'Bolesław Prus',   '978-83-0002', 1890),
    ('Solaris',       'Stanisław Lem',   '978-83-0003', 1961);

INSERT INTO wypozyczenia (id_czytelnika, id_ksiazki, data_od, data_do) VALUES
    (1, 1, '2025-01-10', '2025-01-24'),
    (2, 3, '2025-01-15', NULL);

-- Lista aktywnych wypożyczeń z nazwiskiem czytelnika i tytułem
SELECT c.imie, c.nazwisko,
       k.tytul, k.autor,
       w.data_od, w.data_do
FROM wypozyczenia w
JOIN czytelnicy c ON w.id_czytelnika = c.id_czytelnika
JOIN ksiazki    k ON w.id_ksiazki    = k.id_ksiazki
WHERE w.zwrocona = 0;

-- Ile książek wypożyczył każdy czytelnik?
SELECT c.imie, c.nazwisko,
       COUNT(w.id_wypozyczenia) AS liczba_wypozyczen
FROM czytelnicy c
LEFT JOIN wypozyczenia w ON c.id_czytelnika = w.id_czytelnika
GROUP BY c.id_czytelnika
ORDER BY liczba_wypozyczen DESC;

✏️ Twoje obserwacje i wyniki: