Projektowanie baz danych – Sklep i dziennik szkolny

Część ASklep internetowy

ERD + normalizacja + CASE

1Diagram ER – projekt sklepu internetowego

Cel: Zaprojektowanie modelu sklepu internetowego (encje, atrybuty, relacje) z zachowaniem 1NF, 2NF i 3NF.

Polecenie: Zaprojektuj bazę danych sklepu internetowego. Uwzględnij encje: Klient, Produkt, Zamówienie, Kategoria oraz tabelę pośredniczącą dla pozycji zamówienia.

Model logiczny – Sklep

Encje:
- Klient (ID_Klienta, Imię, Nazwisko, Email)
- Produkt (ID_Produktu, Nazwa, Cena, ID_Kategorii)
- Zamówienie (ID_Zamówienia, Data, ID_Klienta)
- Kategoria (ID_Kategorii, Nazwa)
- SzczegółyZamówienia (ID_Zamówienia, ID_Produktu, Ilość)

Relacje:
- Klient ma wiele Zamówień (1:N)
- Zamówienie zawiera wiele Produktów (N:M) – przez SzczegółyZamówienia
- Produkt należy do jednej Kategorii (N:1)

✏️ Twoje notatki (dopisz atrybuty, typy danych, klucze):

Część ASklep internetowy

Oprogramowanie CASE

2Model sklepu w narzędziu CASE + SQL

Cel: Odwzorowanie modelu sklepu w narzędziu CASE i wygenerowanie kodu SQL.

Polecenie: Utwórz diagram ER dla sklepu internetowego w narzędziu CASE (np. dbdiagram.io, Vertabelo, MySQL Workbench), a następnie wygeneruj kod SQL dla encji i relacji.

SQL · DDL – Sklep

CREATE TABLE Klient (
  ID_Klienta   INT PRIMARY KEY,
  Imię         VARCHAR(50),
  Nazwisko     VARCHAR(50),
  Email        VARCHAR(100)
);

CREATE TABLE Kategoria (
  ID_Kategorii INT PRIMARY KEY,
  Nazwa        VARCHAR(50)
);

CREATE TABLE Produkt (
  ID_Produktu   INT PRIMARY KEY,
  Nazwa         VARCHAR(100),
  Cena          DECIMAL(10,2),
  ID_Kategorii  INT,
  FOREIGN KEY (ID_Kategorii) REFERENCES Kategoria(ID_Kategorii)
);

CREATE TABLE Zamówienie (
  ID_Zamówienia INT PRIMARY KEY,
  Data          DATE,
  ID_Klienta    INT,
  FOREIGN KEY (ID_Klienta) REFERENCES Klient(ID_Klienta)
);

CREATE TABLE SzczegółyZamówienia (
  ID_Zamówienia INT,
  ID_Produktu   INT,
  Ilość         INT,
  PRIMARY KEY (ID_Zamówienia, ID_Produktu),
  FOREIGN KEY (ID_Zamówienia) REFERENCES Zamówienie(ID_Zamówienia),
  FOREIGN KEY (ID_Produktu)   REFERENCES Produkt(ID_Produktu)
);

💡

W Workbench możesz najpierw narysować diagram, a potem użyć funkcji Forward Engineer, aby wygenerować DDL dla MySQL.

✏️ Opisz pracę w narzędziu CASE (kroki, problemy, wnioski):

Część ASklep internetowy

Normalizacja do 3NF

3Normalizacja tabel – przykład sklepu

Cel: Przećwiczenie 1NF, 2NF i 3NF na uproszczonej tabeli zamówień.

Polecenie: Znormalizuj tabelę Zamówienie(ID, ImięKlienta, NazwiskoKlienta, Produkt, Cena, Ilość, Data) do 3NF.

Opis normalizacji – Sklep

1NF:
Zamówienie(ID_Zamówienia, Data, ImięKlienta, NazwiskoKlienta, Produkt, Cena, Ilość)

2NF:
- Klient(ID_Klienta, Imię, Nazwisko)
- Zamówienie(ID_Zamówienia, Data, ID_Klienta)
- Produkt(ID_Produktu, Nazwa, Cena)
- SzczegółyZamówienia(ID_Zamówienia, ID_Produktu, Ilość)

3NF:
W tym modelu atrybuty niekluczowe zależą wyłącznie od swoich kluczy głównych – warunki 3NF są spełnione.

✏️ Dopisz własne przykłady anomalii przed i po normalizacji:

Część BDziennik szkolny

Model ERD

4Diagram ER – dziennik szkolny

Cel: Zaprojektowanie modelu dziennika szkolnego z relacją N:M między uczniami a przedmiotami.

Polecenie: Zaprojektuj model dziennika szkolnego: Nauczyciel uczy wiele Przedmiotów, Uczeń uczęszcza na wiele Przedmiotów, każdy Przedmiot ma jednego nauczyciela.

Model logiczny – Dziennik

Encje:
- Nauczyciel(ID, Imię, Nazwisko)
- Przedmiot(ID, Nazwa, ID_Nauczyciela)
- Uczeń(ID, Imię, Nazwisko)
- UczeńPrzedmiot(ID_Ucznia, ID_Przedmiotu)

Relacje:
- Nauczyciel → Przedmiot (1:N)
- Uczeń → Przedmiot (N:M) przez tabelę UczeńPrzedmiot

✏️ Uzupełnij model (np. klasy, grupy, rok szkolny, oceny):

Część BDziennik szkolny

Implementacja w MySQL

5Tworzenie tabel i relacji w MySQL

Cel: Utworzenie fizycznej struktury dziennika w MySQL na podstawie modelu ER.

Polecenie: Na podstawie modelu z ćwiczenia 4 utwórz tabele z kluczami głównymi i obcymi.

SQL · DDL – Dziennik

CREATE TABLE Nauczyciel (
  ID       INT PRIMARY KEY,
  Imię     VARCHAR(50),
  Nazwisko VARCHAR(50)
);

CREATE TABLE Przedmiot (
  ID              INT PRIMARY KEY,
  Nazwa           VARCHAR(100),
  ID_Nauczyciela  INT,
  FOREIGN KEY (ID_Nauczyciela) REFERENCES Nauczyciel(ID)
);

CREATE TABLE Uczeń (
  ID       INT PRIMARY KEY,
  Imię     VARCHAR(50),
  Nazwisko VARCHAR(50)
);

CREATE TABLE UczeńPrzedmiot (
  ID_Ucznia      INT,
  ID_Przedmiotu  INT,
  PRIMARY KEY (ID_Ucznia, ID_Przedmiotu),
  FOREIGN KEY (ID_Ucznia)     REFERENCES Uczeń(ID),
  FOREIGN KEY (ID_Przedmiotu) REFERENCES Przedmiot(ID)
);

✏️ Zanotuj różnice między modelem a implementacją (typy, długości, ograniczenia):

Część BDziennik szkolny

Testowanie na danych

6Dodawanie danych do tabel dziennika

Cel: Sprawdzenie poprawności relacji na przykładzie prostych danych.

Polecenie: Dodaj przykładowe rekordy do wszystkich tabel dziennika.

SQL · INSERT – Dziennik

INSERT INTO Nauczyciel VALUES (1, 'Anna', 'Nowak');
INSERT INTO Przedmiot   VALUES (1, 'Matematyka', 1);
INSERT INTO Uczeń       VALUES (1, 'Jan', 'Kowalski');
INSERT INTO UczeńPrzedmiot VALUES (1, 1);

✏️ Dodaj więcej danych (kilku uczniów, kilka przedmiotów, kilku nauczycieli):

Część BDziennik szkolny

Reverse engineering

7Wygenerowanie diagramu z kodu SQL

Cel: Przejście z gotowego kodu SQL z dziennika do diagramu ER w narzędziu CASE.

Polecenie: Zaimportuj tabelę dziennika do programu CASE i wygeneruj diagram ERD (reverse engineering).

Procedura – Reverse engineering

1. Otwórz MySQL Workbench.
2. Utwórz schemat bazy i wykonaj skrypt z ćwiczenia 5.
3. W menu wybierz: Database → Reverse Engineer.
4. Wskaż swoją bazę i przejdź kreator.
5. Workbench wygeneruje diagram ERD na podstawie tabel i relacji.

✏️ Opisz, jak wygląda wygenerowany diagram (co ewentualnie poprawiłeś ręcznie):

Część BDziennik szkolny

Zapytania z JOIN

8Zapytanie SQL z JOIN – raport z dziennika

Cel: Utworzenie zapytania łączącego kilka tabel dziennika.

Polecenie: Napisz zapytanie, które pokaże imię i nazwisko ucznia, nazwę przedmiotu oraz imię i nazwisko nauczyciela prowadzącego.

SQL · SELECT + JOIN – Dziennik

SELECT 
  Uczeń.Imię          AS Imię_Ucznia,
  Uczeń.Nazwisko      AS Nazwisko_Ucznia,
  Przedmiot.Nazwa     AS Przedmiot,
  Nauczyciel.Imię     AS Imię_Nauczyciela,
  Nauczyciel.Nazwisko AS Nazwisko_Nauczyciela
FROM Uczeń
JOIN UczeńPrzedmiot
  ON Uczeń.ID = UczeńPrzedmiot.ID_Ucznia
JOIN Przedmiot
  ON UczeńPrzedmiot.ID_Przedmiotu = Przedmiot.ID
JOIN Nauczyciel
  ON Przedmiot.ID_Nauczyciela = Nauczyciel.ID;

✏️ Zmodyfikuj zapytanie (filtry, sortowanie, aliasy):