Zadanie 101: Relacja Jeden do Wielu (1:N) w MySQL - Działy i Pracownicy

Wstęp

Relacja Jeden do Wielu (1:N) jest najczęściej spotykaną relacją w bazach danych. Przykład: Jeden dział w firmie może mieć wielu pracowników, ale jeden pracownik przypisany jest (zazwyczaj) do jednego działu. W tym zadaniu nauczysz się jak poprawnie zaimplementować tę relację, stosując klucze obce oraz jak wyciągać zintegrowane dane za pomocą złączeń i agregacji.

Część 1: Definiowanie Struktury (DDL)

Zadanie 1: Przygotowanie Bazy

Jeśli jeszcze nie masz, użyj bazy FirmaIT. Jeśli chcesz zacząć od zera, usuń starą i stwórz nową.

CREATE DATABASE IF NOT EXISTS FirmaIT;
USE FirmaIT;

Zadanie 2: Tabela Działy (Strona "Jeden")

Tabela nadrzędna, która przechowuje definicje działów.

CREATE TABLE Dzialy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(100) NOT NULL UNIQUE,
    lokalizacja VARCHAR(50)
);

Zadanie 3: Tabela Pracownicy (Strona "Wiele")

Tabela podrzędna, która będzie posiadać klucz obcy wskazujący na dział.

CREATE TABLE Pracownicy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50) NOT NULL,
    nazwisko VARCHAR(50) NOT NULL,
    pensja DECIMAL(10,2),
    dzial_id INT
);

Zadanie 4: Powiązanie Kluczem Obcym

Dodaj klucz obcy. Użyjemy ON DELETE RESTRICT, co oznacza, że nie można usunąć działu, dopóki są w nim przypisani pracownicy (ochrona danych).

ALTER TABLE Pracownicy ADD CONSTRAINT fk_dzial
FOREIGN KEY (dzial_id) REFERENCES Dzialy(id)
ON DELETE RESTRICT;

[!IMPORTANT] Commit 1: Definicja relacji 1:N.

Część 2: Zarządzanie Danymi (DML)

Zadanie 5: Dodawanie Działów

Wstaw trzy działy.

INSERT INTO Dzialy (nazwa, lokalizacja) VALUES 
('IT', 'Warszawa'),
('HR', 'Kraków'),
('Marketing', 'Wrocław');

Zadanie 6: Dodawanie Pracowników

Przypisz pracowników do odpowiednich działów (ID 1 = IT, ID 2 = HR).

INSERT INTO Pracownicy (imie, nazwisko, pensja, dzial_id) VALUES 
('Marek', 'Zieliński', 8000.00, 1),
('Ewa', 'Kowalska', 6500.00, 1),
('Adam', 'Nowak', 5500.00, 2),
('Karolina', 'Woźniak', 4800.00, 2);

Zadanie 7: Pracownik bez działu

Dodaj pracownika, który jeszcze nie został przypisany do żadnego działu.

INSERT INTO Pracownicy (imie, nazwisko, pensja, dzial_id) VALUES 
('Tomasz', 'Bąk', 4000.00, NULL);

Zadanie 8: Test Restrykcji (Błąd)

Spróbuj dodać pracownika do nieistniejącego działu (np. ID 99).

-- To zwróci błąd klucza obcego
INSERT INTO Pracownicy (imie, nazwisko, dzial_id) VALUES ('Jan', 'Testowy', 99);

Zadanie 9: Test Usuwania Działu (Błąd)

Spróbuj usunąć dział IT (ID 1), w którym są pracownicy.

-- Powinno zwrócić błąd "Cannot delete or update a parent row: a foreign key constraint fails"
DELETE FROM Dzialy WHERE id = 1;

[!IMPORTANT] Commit 2: Testy integralności relacji 1:N.

Część 3: Zapytania i Agregacja (DQL)

Zadanie 10: Pełna lista pracowników z nazwami działów (INNER JOIN)

SELECT p.imie, p.nazwisko, d.nazwa AS dzial
FROM Pracownicy p
INNER JOIN Dzialy d ON p.dzial_id = d.id;

Uwaga: W zapytaniu użyliśmy aliasu d.nazwa AS dzial.

Zadanie 11: Wszyscy pracownicy, nawet bez działu (LEFT JOIN)

SELECT p.nazwisko, d.nazwa 
FROM Pracownicy p 
LEFT JOIN Dzialy d ON p.dzial_id = d.id;

Zadanie 12: Wszystkie działy, nawet te puste (RIGHT JOIN)

Znajdź działy, które nie mają jeszcze pracowników (np. Marketing).

SELECT d.nazwa, p.nazwisko 
FROM Pracownicy p 
RIGHT JOIN Dzialy d ON p.dzial_id = d.id;

Zadanie 13: Liczba pracowników w każdym dziale (GROUP BY)

SELECT d.nazwa, COUNT(p.id) AS liczba_pracownikow
FROM Dzialy d
LEFT JOIN Pracownicy p ON d.id = p.dzial_id
GROUP BY d.nazwa;

Zadanie 14: Średnie zarobki na dział

SELECT d.nazwa, ROUND(AVG(p.pensja), 2) AS srednia_pensja
FROM Dzialy d
JOIN Pracownicy p ON d.id = p.dzial_id
GROUP BY d.nazwa
HAVING srednia_pensja > 5000;

[!IMPORTANT] Commit 3: Raporty i złączenia.

Część 4: Modyfikacje i Porządki

Zadanie 15: Przeniesienie pracownika

Przenieś Marka Zielińskiego (ID 1) z IT do Marketingu (ID 3).

UPDATE Pracownicy SET dzial_id = 3 WHERE id = 1;

Zadanie 16: Zmiana nazwy działu

SQL automatycznie "pilnuje" relacji po ID, więc zmiana nazwy jest bezpieczna.

UPDATE Dzialy SET nazwa = 'Public Relations' WHERE id = 3;

Zadanie 17: Rozwiązanie zależności i usunięcie działu

Aby usunąć dział HR (ID 2), musisz najpierw przenieść lub usunąć jego pracowników.

UPDATE Pracownicy SET dzial_id = 1 WHERE dzial_id = 2; -- Przenosimy do IT
DELETE FROM Dzialy WHERE id = 2;

Zadanie 18: Czyszczenie (Opcjonalne)

DROP TABLE Pracownicy;
DROP TABLE Dzialy;

[!IMPORTANT] Commit 4: Aktualizacje i zachowanie spójności.

Git Help

git add .
git commit -m "MySQL Zadanie 101: Relacja 1-N"