Z ERD do SQL

Mamy model zrobiony w SQL:

Jak otrzymać z niego skrypt w SQL?

Zajmiemy się drugim sposobem. Nawet jeśli używamy generatora, warto znać zasady.

Pierwszy krok jest prosty. Dla każdej encji tworzymy tabelę o tej samej nazwie poleceniem CREATE TABLE. Atrybuty encji stają się kolumnami tabeli. Jeśli nie określiliśmy typów atrybutów, to teraz je dodajemy --- to już wymaga zastanowienia (i znajomości dostępnych typów w SQL :-).

create table Gatunki (
  nazwa varchar(20),
  ochrona char(3),
  kontynent varchar(10),
  przysmak varchar(10)
);

create table Opieka (
  imie varchar(10),
  nazwisko varchar(20),
  miasto varchar(15),
  adres varchar(25)
);

create table Zwierzaki (
  imie varchar(12),
  plec char(1),
  wiek integer,
  waga integer
);

Teraz próbujemy określić klucze. Czasami jest to łatwe, na przykład dla gatunków kluczem jest nazwa. Czasem jednak prostego klucza nie ma, a kluczy trójkolumnowych jakoś nie lubimy.

Wtedy pora na klucz sztuczny. Zwykle nazywa się go id lub jakoś podobnie. Jest to dodatkowa kolumna, często nadajemy jej typ SERIAL.

Taki klucz będzie potrzebny dla zwierzaków i opieki (peselem się brzydzimy, bo jest wątpliwy prawnie, choć to oczywiście dobry klucz).

create table Gatunki (
  nazwa varchar(20) primary key,
  ochrona char(3),
  kontynent varchar(10),
  przysmak varchar(10)
);

create table Opieka (
  id integer primary key,
  imie varchar(10),
  nazwisko varchar(20),
  miasto varchar(15),
  adres varchar(25)
);

create table Zwierzaki (
  id serial primary key,
  imie varchar(12),
  plec char(1),
  wiek integer,
  waga integer
);

Teraz możemy dodać inne warunki na poprawność

create table Gatunki (
  nazwa varchar(20) primary key,
  ochrona char(3) check (ochrona in ('tak','nie')),
  kontynent varchar(10),
  przysmak varchar(10)
);

create table Opieka (
  id integer primary key,
  imie varchar(10) NOT NULL,
  nazwisko varchar(20) NOT NULL,
  miasto varchar(15),
  adres varchar(25)
);

create table Zwierzaki (
  id serial primary key,
  imie varchar(12) NOT NULL,
  plec char(1) CHECK (plec in ('m','z')),
  wiek integer CHECK (wiek > 0),
  waga integer CHECK (waga > 0),
);

Pora zająć się związkami. Najpopularniejsze to związki 1-n. Repezentujemy je dodatkowymi kolumnami po stronie n, używając przy tym warunku klucza obcego

create table Gatunki (
  nazwa varchar(20) primary key,
  ochrona char(3) check (ochrona in ('tak','nie')),
  kontynent varchar(10),
  przysmak varchar(10)
);

create table Opieka (
  id integer primary key,
  imie varchar(10) NOT NULL,
  nazwisko varchar(20) NOT NULL,
  miasto varchar(15),
  adres varchar(25)
);

create table Zwierzaki (
  id serial primary key,
  gatunek varchar(20) references Gatunki,
  imie varchar(12) NOT NULL,
  id_opiek integer references Opieka,
  plec char(1) check (plec in ('m','z')),
  wiek integer CHECK (wiek > 0),
  waga integer CHECK (waga > 0)
);

Co robić z innymi związkami? Związki 1-1 traktujemy tak, jak związki 1-n, możemy je nawet repezentować po obu stronach.

Ze związkami m-n jest gorzej. Nie da się ich bezpośrednio reprezentować w SQL. Każdy taki związek musi być osobną tabelą, zawierającą wyłącznie klucze obce do tabel encji, które łączył. Klucz jest złożony -- wszystkie kolumny, klucza sztucznego nie warto robić.

Inny sposób to eliminacja związku m-n już na diagramie ERD. Rozbijamy go, zastępując encją pośredniczącą i dwoma związkami 1-n.