Liceum Klasa III 45 minut PP: II.3d | s. 344

Lekcja 9: Bazy danych - projekt, relacje, zapytania

Projektowanie baz danych, diagramy ER, zapytania SQL - powtorzenie i rozszerzenie

📋 Podstawa programowa: II.3d
AccessBaseSQLbazy danychkwerendypowtorzenierelacje
00:00
Wprowadzenie
5 min
00:05
Teoria
15 min
00:20
Cwiczenia
15 min
00:35
Podsumowanie
10 min
📚

Teoria

▼

Czym jest relacyjna baza danych?

Baza danych to zorganizowany zbior danych przechowywanych elektronicznie. Relacyjna baza danych organizuje dane w tablice (tabele) polaczone ze soba relacjami (powiazaniami). Jezyk SQL (Structured Query Language) sluzy do tworzenia, modyfikowania i odpytywania bazy danych.

Kluczowe pojecia:
Tabela - zbior danych w wierszach i kolumnach (jak arkusz kalkulacyjny)
Rekord - wiersz tabeli (np. dane jednego ucznia)
Pole - kolumna tabeli (np. imie, nazwisko, ocena)
Klucz glowny (PRIMARY KEY) - unikalne pole identyfikujace rekord
Klucz obcy (FOREIGN KEY) - pole laczace dwie tabele
Relacja - powiazanie miedzy tabelami

Diagram ER (Entity-Relationship)

Diagram ER to graficzna reprezentacja struktury bazy danych. Pokazuje encje (tabele), ich atrybuty (pola) i relacje miedzy nimi.

Przyklad: Baza danych szkolnych

[UCZNIOWIE]               [KLASY]
- id_ucznia (PK)    --->  - id_klasy (PK)
- imie                     - nazwa (np. "3a")
- nazwisko                 - wychowawca
- id_klasy (FK)
- data_urodzenia

[PRZEDMIOTY]              [OCENY]
- id_przedmiotu (PK)      - id_oceny (PK)
- nazwa                   - id_ucznia (FK)
- nauczyciel              - id_przedmiotu (FK)
                           - ocena
                           - data

Relacje:
KLASY (1) ---> (wiele) UCZNIOWIE  (jeden do wielu)
UCZNIOWIE (1) ---> (wiele) OCENY  (jeden do wielu)
PRZEDMIOTY (1) ---> (wiele) OCENY (jeden do wielu)

Tworzenie tabel w SQL

-- Tworzenie tabeli
CREATE TABLE uczniowie (
    id_ucznia INTEGER PRIMARY KEY AUTOINCREMENT,
    imie TEXT NOT NULL,
    nazwisko TEXT NOT NULL,
    id_klasy INTEGER,
    data_urodzenia DATE,
    FOREIGN KEY (id_klasy) REFERENCES klasy(id_klasy)
);

CREATE TABLE klasy (
    id_klasy INTEGER PRIMARY KEY AUTOINCREMENT,
    nazwa TEXT NOT NULL,
    wychowawca TEXT
);

CREATE TABLE przedmioty (
    id_przedmiotu INTEGER PRIMARY KEY AUTOINCREMENT,
    nazwa TEXT NOT NULL,
    nauczyciel TEXT
);

CREATE TABLE oceny (
    id_oceny INTEGER PRIMARY KEY AUTOINCREMENT,
    id_ucznia INTEGER,
    id_przedmiotu INTEGER,
    ocena INTEGER CHECK(ocena BETWEEN 1 AND 6),
    data DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (id_ucznia) REFERENCES uczniowie(id_ucznia),
    FOREIGN KEY (id_przedmiotu) REFERENCES przedmioty(id_przedmiotu)
);

-- Wstawianie danych
INSERT INTO klasy (nazwa, wychowawca) VALUES ('3a', 'Jan Kowalski');
INSERT INTO uczniowie (imie, nazwisko, id_klasy) VALUES ('Anna', 'Nowak', 1);
INSERT INTO oceny (id_ucznia, id_przedmiotu, ocena) VALUES (1, 1, 5);

Zapytania SQL - SELECT

-- Podstawowe zapytania
SELECT * FROM uczniowie;                    -- wszystko z tabeli
SELECT imie, nazwisko FROM uczniowie;       -- wybrane kolumny

-- Filtrowanie - WHERE
SELECT * FROM uczniowie WHERE id_klasy = 1;
SELECT * FROM oceny WHERE ocena >= 4;
SELECT * FROM uczniowie WHERE nazwisko LIKE 'K%';  -- zaczyna sie na K

-- Sortowanie - ORDER BY
SELECT * FROM uczniowie ORDER BY nazwisko ASC;
SELECT * FROM oceny ORDER BY ocena DESC;

-- Funkcje agregujace
SELECT COUNT(*) FROM uczniowie;                      -- ile uczniow
SELECT AVG(ocena) FROM oceny WHERE id_ucznia = 1;   -- srednia ucznia
SELECT MAX(ocena), MIN(ocena) FROM oceny;            -- najwyzsza i najnizsza

-- Grupowanie - GROUP BY
SELECT id_ucznia, AVG(ocena) as srednia
FROM oceny
GROUP BY id_ucznia
HAVING AVG(ocena) >= 4.0;  -- tylko uczniowie ze srednia >= 4.0

Laczenie tabel - JOIN

-- INNER JOIN - laczy rekordy z obu tabel
SELECT u.imie, u.nazwisko, o.ocena, p.nazwa as przedmiot
FROM uczniowie u
INNER JOIN oceny o ON u.id_ucznia = o.id_ucznia
INNER JOIN przedmioty p ON o.id_przedmiotu = p.id_przedmiotu
WHERE u.id_ucznia = 1;

-- Srednie ocen uczniow z nazwami klas
SELECT u.imie, u.nazwisko, k.nazwa as klasa, AVG(o.ocena) as srednia
FROM uczniowie u
JOIN klasy k ON u.id_klasy = k.id_klasy
JOIN oceny o ON u.id_ucznia = o.id_ucznia
GROUP BY u.id_ucznia
ORDER BY srednia DESC;
✏️

Zadania

▼
Latwe

Zadanie 1: Proste zapytania SQL

Korzystajac z bazy danych szkolnych (opisanej w teorii), napisz zapytania SQL: a) Wyswietl wszystkich uczniow z klasy 3a, b) Znajdz uczniow urodzonych po 2007 roku, c) Policz ile jest ocen celujacych (6), d) Wyswietl uczniow alfabetycznie po nazwisku.

Pokaz rozwiazanie 
-- a) Uczniowie z klasy 3a
SELECT u.imie, u.nazwisko
FROM uczniowie u
JOIN klasy k ON u.id_klasy = k.id_klasy
WHERE k.nazwa = '3a';

-- b) Uczniowie urodzeni po 2007
SELECT imie, nazwisko, data_urodzenia
FROM uczniowie
WHERE data_urodzenia > '2007-12-31';

-- c) Ile ocen celujacych
SELECT COUNT(*) as liczba_szostek
FROM oceny
WHERE ocena = 6;

-- d) Uczniowie alfabetycznie
SELECT imie, nazwisko
FROM uczniowie
ORDER BY nazwisko ASC, imie ASC;
Srednie

Zadanie 2: Zapytania z JOIN i GROUP BY

Napisz zapytania: a) Wyswietl imie, nazwisko i srednia ocen kazdego ucznia, b) Znajdz ucznia z najwyzsza srednia, c) Ile ocen z kazdego przedmiotu zostalo wystawionych, d) Wyswietl uczniow, ktorzy maja jakas ocene niedostateczna (1).

Pokaz rozwiazanie 
-- a) Srednia ocen kazdego ucznia
SELECT u.imie, u.nazwisko, ROUND(AVG(o.ocena), 2) as srednia
FROM uczniowie u
JOIN oceny o ON u.id_ucznia = o.id_ucznia
GROUP BY u.id_ucznia
ORDER BY srednia DESC;

-- b) Uczen z najwyzsza srednia
SELECT u.imie, u.nazwisko, ROUND(AVG(o.ocena), 2) as srednia
FROM uczniowie u
JOIN oceny o ON u.id_ucznia = o.id_ucznia
GROUP BY u.id_ucznia
ORDER BY srednia DESC
LIMIT 1;

-- c) Ile ocen z kazdego przedmiotu
SELECT p.nazwa, COUNT(*) as liczba_ocen
FROM oceny o
JOIN przedmioty p ON o.id_przedmiotu = p.id_przedmiotu
GROUP BY p.nazwa
ORDER BY liczba_ocen DESC;

-- d) Uczniowie z ocena niedostateczna
SELECT DISTINCT u.imie, u.nazwisko
FROM uczniowie u
JOIN oceny o ON u.id_ucznia = o.id_ucznia
WHERE o.ocena = 1;
Srednie

Zadanie 3: Projekt bazy danych - biblioteka

Zaprojektuj baze danych dla biblioteki szkolnej. Narysuj diagram ER i napisz polecenia CREATE TABLE. Tabele: ksiazki, autorzy, czytelnicy, wypozyczenia. Dodaj przykladowe dane (INSERT).

Pokaz rozwiazanie 
-- Diagram ER:
-- [AUTORZY] (1)---(wiele) [KSIAZKI] (1)---(wiele) [WYPOZYCZENIA] (wiele)---(1) [CZYTELNICY]

CREATE TABLE autorzy (
    id_autora INTEGER PRIMARY KEY AUTOINCREMENT,
    imie TEXT NOT NULL,
    nazwisko TEXT NOT NULL,
    kraj TEXT
);

CREATE TABLE ksiazki (
    id_ksiazki INTEGER PRIMARY KEY AUTOINCREMENT,
    tytul TEXT NOT NULL,
    id_autora INTEGER,
    rok_wydania INTEGER,
    gatunek TEXT,
    isbn TEXT UNIQUE,
    FOREIGN KEY (id_autora) REFERENCES autorzy(id_autora)
);

CREATE TABLE czytelnicy (
    id_czytelnika INTEGER PRIMARY KEY AUTOINCREMENT,
    imie TEXT NOT NULL,
    nazwisko TEXT NOT NULL,
    klasa TEXT,
    email TEXT
);

CREATE TABLE wypozyczenia (
    id_wypozyczenia INTEGER PRIMARY KEY AUTOINCREMENT,
    id_ksiazki INTEGER,
    id_czytelnika INTEGER,
    data_wypozyczenia DATE DEFAULT CURRENT_DATE,
    data_zwrotu DATE,
    FOREIGN KEY (id_ksiazki) REFERENCES ksiazki(id_ksiazki),
    FOREIGN KEY (id_czytelnika) REFERENCES czytelnicy(id_czytelnika)
);

-- Przykladowe dane
INSERT INTO autorzy VALUES (1, 'Adam', 'Mickiewicz', 'Polska');
INSERT INTO autorzy VALUES (2, 'Henryk', 'Sienkiewicz', 'Polska');
INSERT INTO ksiazki VALUES (1, 'Pan Tadeusz', 1, 1834, 'Epopeja', '978-83-01-00001-1');
INSERT INTO ksiazki VALUES (2, 'Quo Vadis', 2, 1896, 'Powiesc historyczna', '978-83-01-00002-8');
INSERT INTO czytelnicy VALUES (1, 'Anna', 'Nowak', '3a', 'anna@szkola.pl');
INSERT INTO wypozyczenia VALUES (1, 1, 1, '2024-09-01', NULL);
Trudne

Zadanie 4: Zaawansowane zapytania biblioteczne

Na bazie z Zadania 3 napisz: a) Kto aktualnie ma wypozyczona ksiazke (data_zwrotu IS NULL), b) Ktora ksiazka byla najczesciej wypozyczana, c) Kto przetrzymuje ksiazki (wypozyczenie > 30 dni), d) Ile ksiazek napisal kazdy autor.

Pokaz rozwiazanie 
-- a) Aktualnie wypozyczone
SELECT c.imie, c.nazwisko, k.tytul, w.data_wypozyczenia
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.data_zwrotu IS NULL;

-- b) Najczesciej wypozyczana ksiazka
SELECT k.tytul, COUNT(*) as ile_razy
FROM wypozyczenia w
JOIN ksiazki k ON w.id_ksiazki = k.id_ksiazki
GROUP BY k.id_ksiazki
ORDER BY ile_razy DESC
LIMIT 1;

-- c) Przetrzymujacy (> 30 dni, niezwrocone)
SELECT c.imie, c.nazwisko, k.tytul, w.data_wypozyczenia,
       julianday('now') - julianday(w.data_wypozyczenia) as dni
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.data_zwrotu IS NULL
  AND julianday('now') - julianday(w.data_wypozyczenia) > 30;

-- d) Liczba ksiazek kazdego autora
SELECT a.imie, a.nazwisko, COUNT(k.id_ksiazki) as liczba_ksiazek
FROM autorzy a
LEFT JOIN ksiazki k ON a.id_autora = k.id_autora
GROUP BY a.id_autora
ORDER BY liczba_ksiazek DESC;
🎥

Materialy wideo

▼
Bazy danych w szkole - wprowadzenie. Lekcja Informatyki z Microsoft Access. Kurs baz danych.
Zygmunt Pilarek o Edukacji
Access - importowanie tabel i tworzenie relacji
paulinapat96
🎧

Podcasty

▼
✔️

Quiz - sprawdz sie!

▼
📜

Podstawa programowa

▼
← Lekcja 8: Arkusz - matematyka Siatka godzinowa Lekcja 10: Strona WWW - projekt (1) →