E/R

Definiowanie tabel zgodnie z projektem (mapowanie E/R → SQL)

Projektowanie relacyjne, automatyzacja, praktyka Dział V – INF.03

Teoria Od modelu E/R do SQL

Model E/R (Entity‑Relationship) – opisuje encje, atrybuty i relacje między nimi.
Mapowanie E/R na SQL – encja → tabela, atrybut → kolumna, relacja → klucz obcy lub tabela pośrednia.
Relacja 1:N – po stronie N dodajemy kolumnę z kluczem obcym.
Relacja N:M – tworzymy tabelę pośrednią z dwoma kluczami obcymi i złożonym kluczem głównym.

W INF.03 ważne jest, aby poprawny diagram E/R potrafić zamienić na kompletny skrypt SQL tworzący bazę z kluczami głównymi, obcymi i odpowiednimi typami danych.

Ćwiczenie 1 Encja 1:N → tabele

Projekt:

Encja: Klasa(id, nazwa)
Encja: Uczeń(id, imię, nazwisko)
Relacja: Klasa 1:N Uczeń (uczeń należy do jednej klasy, klasa ma wielu uczniów)

Zidentyfikuj klucze główne encji.
Dla relacji 1:N dodaj klucz obcy po stronie Uczeń.

CREATE TABLE klasy (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nazwa VARCHAR(12) UNIQUE NOT NULL
);

CREATE TABLE uczniowie (
  id INT PRIMARY KEY AUTO_INCREMENT,
  imie VARCHAR(30) NOT NULL,
  nazwisko VARCHAR(40) NOT NULL,
  klasa_id INT,
  FOREIGN KEY (klasa_id) REFERENCES klasy(id)
);

Ćwiczenie 2 Relacja N:M → tabela pośrednia

Projekt:

Encje: Uczeń, Przedmiot.
Relacja: Uczeń ma wiele przedmiotów, Przedmiot ma wielu uczniów (N:M).

Utwórz tabele dla encji Uczeń i Przedmiot.
Relację N:M zastąp tabelą pośrednią złożoną z dwóch kluczy obcych.

CREATE TABLE przedmioty (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nazwa VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE uczniowie_przedmioty (
  uczen_id INT,
  przedmiot_id INT,
  PRIMARY KEY (uczen_id, przedmiot_id),
  FOREIGN KEY (uczen_id) REFERENCES uczniowie(id),
  FOREIGN KEY (przedmiot_id) REFERENCES przedmioty(id)
);

Ćwiczenie 3 Atrybut encji → kolumna z typem

Projekt: Encja Uczeń ma atrybut PESEL (dokładnie 11 cyfr, unikalny).

Dobierz typ danych i długość kolumny.
Zastosuj ograniczenia NOT NULL i UNIQUE.

ALTER TABLE uczniowie
ADD pesel CHAR(11) NOT NULL UNIQUE;

Ćwiczenie 4 Batch: cała baza „Szkoła”

Zadanie: Przygotuj skrypt, który w jednym pliku tworzy bazę szkola z encjami: Klasa, Uczeń, Przedmiot, Ocena.

Dodaj DROP DATABASE IF EXISTS na początek.
Utwórz tabele i relacje zgodnie z diagramem E/R.

DROP DATABASE IF EXISTS szkola;
CREATE DATABASE szkola;
USE szkola;

CREATE TABLE klasy (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nazwa VARCHAR(12) UNIQUE NOT NULL
);

CREATE TABLE uczniowie (
  id INT PRIMARY KEY AUTO_INCREMENT,
  imie VARCHAR(30) NOT NULL,
  nazwisko VARCHAR(40) NOT NULL,
  klasa_id INT,
  FOREIGN KEY (klasa_id) REFERENCES klasy(id)
);

CREATE TABLE przedmioty (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nazwa VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE oceny (
  id INT PRIMARY KEY AUTO_INCREMENT,
  uczen_id INT,
  przedmiot_id INT,
  ocena TINYINT CHECK (ocena BETWEEN 2 AND 5),
  FOREIGN KEY (uczen_id) REFERENCES uczniowie(id),
  FOREIGN KEY (przedmiot_id) REFERENCES przedmioty(id)
);

Ćwiczenie 5 Modyfikacja schematu – ALTER TABLE

Zadanie:

Dodaj do tabeli przedmioty kolumnę typ (np. „humanistyczny”, „ścisły”).
Zmień typ kolumny nazwa w tabeli kierunki (np. z 50 na 100 znaków).

Użyj ALTER TABLE ADD COLUMN.
Użyj ALTER TABLE MODIFY/ALTER COLUMN.

ALTER TABLE przedmioty
ADD typ VARCHAR(30);

ALTER TABLE kierunki
MODIFY nazwa VARCHAR(100);

Projekt INF.03 „Biblioteka” – od E/R do SQL

Opis modelu:

Encja: Książka(id, tytul, autor).
Encja: Czytelnik(id, imie, nazwisko).
Encja: Wypożyczenie(id, data_wyp, data_zwrotu).
Relacje: Czytelnik wypożycza wiele książek; Książka może wystąpić w wielu wypożyczeniach (dwie relacje 1:N).

Narysuj diagram E/R (co najmniej 3 encje i relacje).
Zidentyfikuj klucze główne i obce.
Napisz skrypt SQL definiujący wszystkie tabele.

CREATE DATABASE biblioteka;
USE biblioteka;

CREATE TABLE ksiazki (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tytul VARCHAR(150) NOT NULL,
  autor VARCHAR(100) NOT NULL
);

CREATE TABLE czytelnicy (
  id INT PRIMARY KEY AUTO_INCREMENT,
  imie VARCHAR(40) NOT NULL,
  nazwisko VARCHAR(40) NOT NULL
);

CREATE TABLE wypozyczenia (
  id INT PRIMARY KEY AUTO_INCREMENT,
  ksiazka_id INT NOT NULL,
  czytelnik_id INT NOT NULL,
  data_wyp DATE NOT NULL,
  data_zwrotu DATE,
  FOREIGN KEY (ksiazka_id) REFERENCES ksiazki(id),
  FOREIGN KEY (czytelnik_id) REFERENCES czytelnicy(id)
);

Quiz Sprawdź wiedzę z projektowania!

Do czego służy model E/R w projektowaniu baz danych?
Jak zamienić relację N:M na strukturę SQL?
Czym jest klucz obcy (FOREIGN KEY) i czemu służy?
Jakie kryteria bierzesz pod uwagę, dobierając typy danych dla kolumn?
W jaki sposób zautomatyzować budowę całej bazy danych?

Do graficznego zaplanowania encji, atrybutów i relacji przed implementacją bazy.
Przez wprowadzenie tabeli pośredniej z kluczami obcymi do obu tabel i złożonym kluczem głównym.
To kolumna wskazująca na rekord w innej tabeli, wymuszająca spójność referencyjną.
Rodzaj danych, długość, konieczność unikalności, możliwość pustych wartości oraz wymagania wydajnościowe.
Tworząc skrypt/batch z poleceniami CREATE/ALTER w jednym pliku SQL i uruchamiając go na serwerze.

Checklista Sprawdź umiejętności

Analizuję diagram E/R i potrafię wskazać encje, atrybuty i relacje.
Mapuję relacje 1:N i N:M na poprawne tabele SQL z kluczami obcymi.
Tworzę skrypty SQL budujące kompletny schemat bazy.
Modyfikuję schemat przy użyciu ALTER TABLE, dbając o spójność danych.