Projektowanie baz danych – Ćwiczenia i rozwiązania

Ćwiczenie 1: Diagram ER – Projektowanie bazy danych

Polecenie: Zaprojektuj bazę danych sklepu internetowego. Uwzględnij encje: Klient, Produkt, Zamówienie, Kategoria. Określ atrybuty i relacje. Zastosuj 1NF, 2NF i 3NF.

Rozwiązanie:

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)

Diagram ER: (opisowy)
- Encje z atrybutami
- Klucze główne (PK) i obce (FK)
- Linie relacji z oznaczeniem 1:N, N:M
    

Ćwiczenie 2: Oprogramowanie CASE

Polecenie: Zainstaluj program CASE (np. dbdiagram.io, Vertabelo, MySQL Workbench), utwórz diagram ER z ćwiczenia 1, a następnie wygeneruj kod SQL.

Rozwiązanie:

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)
);
    

Ćwiczenie 3: Normalizacja tabel

Polecenie: Znormalizuj poniższą tabelę do 3NF:

Zamówienie(ID, ImięKlienta, NazwiskoKlienta, Produkt, Cena, Ilość, Data)
    

Rozwiązanie:

1NF:
Rozdzielenie powtarzających się wartości:
Zamówienie(ID_Zamówienia, Data, ImięKlienta, NazwiskoKlienta, Produkt, Cena, Ilość)

2NF:
Usunięcie zależności częściowych:
- 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:
Usunięcie zależności przechodnich – już spełnione w 2NF
    

Ćwiczenie 4: Rysowanie diagramów ER

Polecenie: Korzystając z programu CASE, narysuj diagram ER przedstawiający relacje: Nauczyciel uczy wiele Przedmiotów, Uczeń uczęszcza na wiele Przedmiotów. Przedmiot może mieć jednego nauczyciela.

Rozwiązanie:

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 UczeńPrzedmiot
    

Ćwiczenie 5: Tworzenie tabel i relacji w MySQL

Polecenie: Na podstawie diagramu ER z ćwiczenia 4 utwórz odpowiednie tabele w MySQL z kluczami głównymi i obcymi.

Rozwiązanie:

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)
);
    

Ćwiczenie 6: Dodawanie danych do zaprojektowanych tabel

Polecenie: Wprowadź przykładowe dane do tabel Nauczyciel, Przedmiot, Uczeń oraz UczeńPrzedmiot.

Rozwiązanie:

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);
    

Ćwiczenie 7: Wygenerowanie diagramu z kodu SQL

Polecenie: Skorzystaj z funkcji importu SQL w narzędziu CASE (np. MySQL Workbench) i wygeneruj diagram ER na podstawie struktury tabel z ćwiczenia 5.

Rozwiązanie:

1. Otwórz MySQL Workbench
2. Utwórz nowy schemat i wklej kod SQL z ćwiczenia 5
3. Kliknij "Database -> Reverse Engineer"
4. Wybierz bazę danych i zatwierdź kroki
5. Diagram zostanie automatycznie wygenerowany
    

Ćwiczenie 8: Tworzenie zapytania SQL z użyciem JOIN

Polecenie: Napisz zapytanie SQL, które wyświetli imię i nazwisko ucznia, nazwę przedmiotu, oraz imię i nazwisko nauczyciela prowadzącego dany przedmiot.

Rozwiązanie:

  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;