Składniki diagramu ER

Diagram związków encji (ang. entity‑relationship – ER) to graficzny sposób przedstawienia modeli danych używanych w projektach informatycznych. Jego podstawowymi składnikami są:

• encjeencjaencje – rzeczywiste lub wyobrażone obiekty oznaczane prostokątami, w bazie danych są to tabele,

• atrybuty – cechy (właściwości) encji reprezentowane przez elipsy, w bazie danych to kolumny poszczególnych tabel,

• złączenia – związki między encjami, oznaczane rombami lub liniami ciągłymi lub przerywanymi, w bazie danych są to relacje.

Istnieją różne konwencje tworzenia diagramów ER. Przykładowe kształty i symbole graficzne możemy zobaczyć na zrzucie.

Polecenie 1

Przeanalizuj poniższy zrzut prezentujący przykładowe kształty i symbole graficzne stosowane w różnych konwencjach tworzenia diagramów ER. Zastanów się, co mogą oznaczać litery M i N.

RdZeFnstVYTbJ

Opis grafiki od góry. Po lewej stronie widoczny jest prostokąt z napisem Encja. Obok widoczny jest romb z napisem związek. Od lewego wierzchołka odchodzi odcinek podpisany jako M. Od prawego wierzchołka odchodzi odcinek podpisany jako N. Po prawej stronie widoczna jest elipsa z napisem atrybut. Poniżej jest prosty odcinek podpisany jako relacja obowiązkowa. Obok jest odcinek linią przerywana podpisany jako relacja opcjonalna. Niżej widoczny jest napis Notacja Martina. Crow's Foot. Information Engineering Style. Niżej, jedno pod drugim, widoczne są prostokąt z napisem encja, rąb z napisem związek i elipsa z napisem atrybut. Obok kształtów widoczne są. Odcinek podpisany jako jeden do jednego. Odcinek zakończony z prawej strony trójzębem podpisany jako jeden z wielu. Odcinek z obu stron zakończony trójzębem podpisany jako wiele do wielu. Obok widoczny jest odcinek, po jego lewej stronie, kawałek od początku zaznaczono pionową kreskę i podpisano jako obligatoryjność. Widoczny jest też odcinek, który po lewej stronie, kawałek od początku, zaznaczony ma okrąg. Podpisany jako opcjonalność. Niżej widoczny jest napis notacja Bachmana. Niżej widoczne, obok siebie, są prostokąt z napisem encja, rąb z napisem związek i elipsa z napisem atrybut. Niżej widoczny jest odcinek. Po lewej stronie zakończony jest wypełnionym okręgiem po prawej strzałką. Opisany jako jeden do jednego. Niżej widoczny jest odcinek. Po lewej stronie zakończony wypełnionym okręgiem, po prawej strzałką i wypełnionym okręgiem. Opisany jako jeden do jednego lub wielu. Niżej widoczny jest odcinek. Po lewej zakończony okręgiem, po prawej strzałką. Opisano jako zero lub więcej do jednego lub więcej.

Pokaż odpowiedź

Litery M oraz N reprezentują liczbę stron związku, w tym wypadku wiele‑do‑wielu. Inne możliwości to 1‑N (jeden‑do‑wielu) oraz 1‑1 (jeden‑do‑jednego).

Modelowanie bazy

Analizę diagramu ER dla potrzeb naszej restauracji zacząć możemy od dwóch encji, tzn. klientów i zamówień.

Rqv4ytLTD0goV

Schemat. Niebieski prostokąt Klienci. W dół odchodzą od niego trzy odcinki skierowane do trzech zielonych elips podpisanych od lewej jako Nazwisko, Imię, Identyfikator. Od prostokąta Klienci po prawej stronie odchodzi linia do czerwonego rombu z napisem Zamawia. Od niego po prawej stronie odchodzi odcinek zakończony okręgiem i trójzębem do niebieskiego prostokąta opisanego jako Zamówienia. Od tego prostokąta odchodzą dwie linie w dół do zielonych elips opisanych jako Identyfikator i Data realizacji.

Na zrzucie widzimy, że encja Klienci może być opisana przez trzy atrybuty: Imię, Nazwisko i Identyfikator. Z kolei właściwościami encji Zamówienia mogą być Data realizacji oraz Identyfikator. Związek Zamawia łączący obydwie encje to opcjonalne złączenie typu jeden‑do‑wielu, co oznacza, że jeden klient może złożyć wiele zamówień.

Biorąc pod uwagę ustalone wcześniej wymagania naszej restauracji, do diagramu dodamy kolejne encje z atrybutami, aby zilustrować możliwość dokonywania rezerwacji przez klientów:

• Stoliki, atrybuty: Numer i Liczba miejsc,

• Rezerwacje, atrybuty: Identyfikator i Termin,

• Rabaty, atrybuty: Identyfikator, Data przyznania, Data zakończenia.

Polecenie 2

Przeanalizuj poniższą grafikę i odpowiedz na pytanie: Jakie związki łączą dodane encje (Stoliki, Rezerwacje i Rabaty)?

RsUHdXxR84pkm

Schemat. Niebieski prostokąt opisany jako stoliki. Od niego odchodzą dwie linie do dwóch zielonych elips. Górna opisana jako numer, tekst podkreślony. Dolna opisana jako liczba miejsc. Od niebieskiego prostokąta odchodzi linia w dół. Przy niebieskim prostokącie zakończona jest trójzębem. Odchodzi do czerwonego rombu opisanego jako na stolik. Od rombu w dół odchodzi linia, przy końcu ma prostopadłą kreskę. Dochodzi do niebieskiego prostokąta opisanego jako rezerwacje. Od prostokąta odchodzą dwie linie w dół do dwóch zielonych elips. Od lewej identyfikator, tekst podkreślony, oraz termin. Od niebieskiego prostokąta rezerwacje odchodzi linia w lewo, zakończona przy prostokącie trójzębem z okręgiem. Dociera do czerwonego rombu złożona przez. Od rombu odchodzi linia w dół do niebieskiego prostokąta opisanego jako klienci. Od prostokąta odchodzą trzy linie w dół do trzech zielonych elips. Od lewej są to: nazwisko, imię i identyfikator, ostatnie słowo podkreślone. Od niebieskiego prostokąta klienci odchodzi linia w lewo do czerwonego rombu posiada. Od rombu odchodzi linia do góry, przy niebieskim prostokącie zakończona okręgiem i trójzębem. Niebieski prostokąt opisany jako rabaty. Od niego odchodzą trzy linie do góry, do trzech zielonych elips. Od lewej, data przyznania, data zakończenia i identyfikator. Ostatnie słowo podkreślone.

Pokaż podpowiedź

Wykorzystaj zamieszczoną wcześniej grafikę prezentującą różne konwencje graficznej realizacji diagramów ER.

Pokaż odpowiedź

Klienci mogą dokonywać wielu rezerwacji (opcjonalne złączenie jeden‑do‑wielu). Jedna rezerwacja musi dotyczyć co najmniej jednego z wielu stolików (obligatoryjne złączenie jeden‑do‑wielu). Klientom można przyznawać wiele rabatów (opcjonalne złączenie jeden‑do‑wielu).

Teraz zajmiemy się daniami oraz zamówieniami. Do diagramu dodamy następujące encje:

• Półprodukty, atrybuty: Identyfikator, Nazwa, Liczba i Liczba kilogramów,

• Dania, atrybuty: Identyfikator, Nazwa, Cena, Czy w menu,

• Zamówienia, atrybuty: Identyfikator, Data realizacji, Rabat.

Związek pomiędzy daniami i półproduktami polega na tym, że danie może składać się z wielu półproduktów, ale także jeden półprodukt może wystąpić w wielu daniach. Podobny związek łączy dania i zamówienia. Jedno zamówienie może obejmować wiele dań, z kolei to samo danie może wystąpić w wielu zamówieniach. W tego typu związkach mamy zatem do czynienia ze złączeniem N‑M, tj. wiele‑do‑wielu.

Polecenie 3

Przeanalizuj grafikę i odpowiedz na pytanie: W jaki sposób na diagramie zobrazowano złączenia wiele‑do‑wielu?

RJjSntgQVie3D

Schematy. Pierwszy schemat. Niebieski prostokąt stoliki. Od niego odchodzi linia w dół, zakończona przy prostokącie trójzębem. Dociera do czerwonego rombu na stolik. Od niego odchodzi linia w dół, zakończona linią prostopadłą, do niebieskiego prostokąta rezerwacje. Od niebieskiego prostokąta rezerwacje odchodzi linia w dół do zielonej elipsy termin. Schemat drugi. Niebieski prostokąt zamówienia. Od niego odchodzą trzy linie w dół do trzech zielonych elips. Od lewej. Słowo podkreślone identyfikator, data realizacji oraz rabat. Od niebieskiego prostokąta zamówienia odchodzi linia w prawo, zakończona przy prostokącie prostopadłą linią. Dociera do czerwonego rombu zamówienie. Od rombu odchodzi linia w prawo, zakończona przy niebieskim prostokącie trójzębem. Niebieski prostokąt składy zamówień. Od niego w prawo odchodzi linia do zielonej elipsy podkreślony identyfikator. Od prostokąta składy zamówień odchodzi linia do góry, zakończona przy prostokącie trójzębem i okręgiem. Dochodzi do czerwonego rombu danie. Od rombu odchodzi linia do góry do niebieskiego prostokąta dania. Od prostokąta odchodzą dwie linie w lewo do dwóch zielonych elips. Podkreślony identyfikator i czy w menu. Od prostokąta dania odchodzą dwie linie w prawo do dwóch zielonych elips. Nazwa i cena. Od prostokąta dania odchodzi do góry linia zakończona linią prostopadłą. Dociera do czerwonego rombu do dania. Od rombu odchodzi linia w prawo, zakończona przy niebieskim prostokącie trójzębem. Niebieski prostokąt składy dań. Od niego odchodzą dwie linie do góry do dwóch zielonych elips. Podkreślony identyfikator i liczba kilogramów. Od niebieskiego prostokąta składy dań odchodzi linia w prawo, zakończona przy prostokącie trójzębem i okręgiem. Dociera do czerwonego rombu składniki. Od niego odchodzi linia w dół do niebieskiego prostokąta półprodukty. Od prostokąta odchodzą cztery linie do zielonych elips. Nazwa, podkreślony identyfikator, liczba oraz liczba kilogramów.

Pokaż podpowiedź

Złączenia między encjami odpowiadają relacjom między tabelami w bazie danych. Zastanów się, w jaki sposób tworzymy relację wiele‑do‑wielu między dwoma tabelami.

Pokaż odpowiedź

Aby pokazać złączenie wiele‑do‑wielu w diagramie ER dodajemy dodatkową encję – łącznik między powiązanymi encjami. Encja Składy dań zawierająca atrybuty Identyfikator i Liczba kilogramów łączy encje Dania i Półprodukty za pomocą dwóch relacji 1‑N, przy czym danie musi zawierać przynajmniej jeden półprodukt, natomiast półprodukt nie musi należeć do jakiegokolwiek dania.

Podobnie Encja Składy zamówień, której właściwością jest Identyfikator, łączy encje Zamówienia i Dania, przy czym zamówienie musi zawierać przynajmniej jedno danie, natomiast danie może nie występować w zamówieniach.

Tabele i warunki integralności

Dotychczasowy model bazy zapisany jako diagram ER może wyglądać następująco:

Uwzględnia on etap normalizacji danych, tzn. wyodrębniono encje, np. Stoliki czy Rabaty, aby wyeliminować powtarzanie się danych. Na diagramie wskazano również klucze główne poszczególnych encji poprzez podkreślenie odpowiednich atrybutów. Klucze obce występują na diagramie jako związki łączące encje relacjami.

Na podstawie diagramu ER, czyli logicznego modelu bazy, możemy przygotować jej schemat w postaci tabel. Mógłby on wyglądać następująco:

Każda encja została przekształcona na osobną tabelę. Atrybuty encji stały się polami (kolumnami) tabel. Klucze główne oznaczone zostały skrótem PK (ang. primary key), klucze obce skrótem FK (ang. foreign key). Litera N wskazuje, że pole nie może zawierać wartości NULL (ang. not null). Relacje oznaczono tak jak na diagramie. Wreszcie każdemu polu przypisano odpowiedni typ danych:

• int – inaczej INTEGER, liczba całkowita,

• float – liczba zmiennoprzecinkowa pojedynczej precyzji, w nawiasach podano dokładność,

• decimal – liczba zmiennoprzecinkowa przechowywana w postaci znakowej, w nawiasach podano dokładność,

• varchar – inaczej TEXT, ciąg znaków o zmiennej długości podawanej w nawiasach,

• timestamp – data i czas, przechowywane w formacie RRRR‑MM‑DD GG:MM:SS.

Implementacja w języku SQL

Kolejnym krokiem będzie przygotowanie skryptu, w którym użyjemy instrukcji i klauzul języka SQL do zapisania i utworzenia tabel, pól, relacji oraz określenia warunków integralności.

Kolejność tworzenia tabel ma znaczenie ze względu na warunki integralności, na początku powinny być tworzone tabele, które nie zawierają kluczy obcych. Zaczniemy od grupy tabel związanych z klientami i rezerwacjami.

DROP TABLE IF EXISTS klienci;
CREATE TABLE klienci (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  imie TEXT NOT NULL,
  nazwisko TEXT NOT NULL
);

DROP TABLE IF EXISTS stoliki;
CREATE TABLE stoliki (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  l_miejsc INTEGER NOT NULL DEFAULT 0
);

DROP TABLE IF EXISTS rezerwacje;
CREATE TABLE rezerwacje (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  termin TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  id_stolika INTEGER,
  id_klienta INTEGER,
  FOREIGN KEY (id_stolika) REFERENCES stoliki (id) ON DELETE CASCADE,
  FOREIGN KEY (id_klienta) REFERENCES klienci (id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS rabaty;
CREATE TABLE rabaty (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  data_od TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  data_do TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  id_klienta INTEGER,
  FOREIGN KEY (id_klienta) REFERENCES klienci (id) ON DELETE CASCADE
);

Zadaniem klauzul DROP jest usunięcie podanej tabeli, jeżeli znajduje się już w bazie, co będzie przydatne podczas tworzenia interfejsu. Klauzule CREATE TABLE tworzą tabele o podanych polach. Nazwy pól ze względu na późniejsze ich wykorzystanie w kodzie aplikacji internetowej ujednolicamy i skracamy – np. wszystkie klucze główne nazywają się id, a klucze obce nazywamy wg schematu id_nazwa.

W większości przypadków nakładamy na pola warunki integralności:

• PRIMARY KEY – klucz główny, pole nie może zawierać wartości NULL i wartości nie mogą się powtarzać,

• AUTOINCREMENT – wartość pola (kolejne liczby naturalne) jest tworzona automatycznie przez bazę,

• FOREIGN KEY (klucz_obcy) REFERENCES tabela(klucz_glowny) – wskazanie klucza obcego, czyli utworzenie relacji między tabelami,

• ON DELETE CASCADE – jeżeli klucz główny w tabeli nadrzędnej zostanie usunięty, usunięte zostaną również wszystkie powiązane rekordy z tabeli zależnej,

• NOT NULL – jak wspominaliśmy, pole nie może przechowywać wartości NULL, tj. nie może być puste,

• DEFAULT – określenie wartości domyślnej wprowadzanej w polu przez bazę, jeżeli wartość ta nie zostanie podana,

• CURRENT_TMESTAMP – zwraca aktualną datę i czas w formacie znakowym RRRR‑MM‑DD GG:MM:SS, np.: 2023‑07‑18 14:16:52.

Po tych wyjaśnieniach możemy zaimplementować tabele dań i zamówień:

DROP TABLE IF EXISTS polprodukty;
CREATE TABLE polprodukty (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nazwa TEXT NOT NULL,
  l_kg FLOAT(5, 2)
);

DROP TABLE IF EXISTS dania;
CREATE TABLE dania (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nazwa TEXT NOT NULL,
  cena DECIMAL(5, 2) DEFAULT 0,
  menu BOOLEAN NOT NULL CHECK (menu IN (0, 1))
);

DROP TABLE IF EXISTS sklady_dan;
CREATE TABLE sklady_dan (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  l_kg FLOAT(5, 2),
  id_dania INTEGER,
  id_polproduktu INTEGER,
  FOREIGN KEY (id_dania) REFERENCES dania (id) ON DELETE CASCADE,
  FOREIGN KEY (id_polproduktu) REFERENCES polprodukty (id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS zamowienia;
CREATE TABLE zamowienia (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  id_klienta INTEGER NOT NULL,
  data TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  rabat DECIMAL(5, 2) DEFAULT 0,
  FOREIGN KEY (id_klienta) REFERENCES klienci (id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS sklady_zamowien;
CREATE TABLE sklady_zamowien (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  id_zamowienia INTEGER,
  id_dania INTEGER,
  liczba INTEGER DEFAULT 1,
  FOREIGN KEY (id_zamowienia) REFERENCES zamowienia (id) ON DELETE CASCADE,
  FOREIGN KEY (id_dania) REFERENCES zamowienia (id) ON DELETE CASCADE
);

W powyższym kodzie wyjaśnienia wymagają dwie rzeczy. Pierwsza to definicja pola, które przechowuje informacje o tym, czy danie jest dostępne w menu: menu BOOLEAN NOT NULL CHECK (menu IN (0, 1)). Jest to pole logiczne, które może przyjmować tylko dwie wartości podane w klauzuli CHECK IN, tzn. 0 – FAŁSZ lub 1 – PRAWDA.

Druga rzecz to dodatkowe pole o nazwie liczba w tablicy sklady_zamowien, które pozwoli na zapisanie liczby tych samych dań w danym zamówieniu.

Na koniec cały omówiony wyżej zestaw instrukcji SQL obejmiemy transakcją, tzn. na początku skryptu dodamy klauzulę BEGIN;, a na końcu COMMIT;. Dzięki temu zmiany w bazie danych, tj. utworzenie tabel, zostaną zatwierdzone tylko wtedy, kiedy wszystkie instrukcje zostaną poprawnie wykonane.

Przygotowany kod SQL zapiszmy na dysku w pliku restauracja.sql.

Utworzenie bazy i tabel

W następnym materiale zajmiemy się tworzeniem aplikacji internetowej, która pozwoli na utworzenie bazy i wygodne korzystanie z niej bez znajomości poleceń SQL. Jednak już teraz możemy sprawdzić poprawność utworzonego kodu za pomocą wiersza poleceń bazy SQLite lub programu SQLiteStudio. Narzędzia te omówione zostały na początku materiału Definiowanie schematu bazy danych w języku SQL, etap IIPkXbedRKMDefiniowanie schematu bazy danych w języku SQL, etap II.

W przypadku wiersza poleceń bazy SQLite w katalogu ze skryptem zawierającym omówione wyżej instrukcje SQL otwieramy terminal i wydajemy polecenie:

sqlite3 restauracja.db < restauracja.sql

Wynikiem działania będzie utworzenie bazy w pliku restauracja.db i wykonanie w niej instrukcji SQL zawartych w skrypcie restauracja.sql. Jeżeli skrypt zawierał będzie błędy składniowe, wyświetlony zostanie odpowiedni komunikat. Po utworzeniu bazy i tabel możemy ją otworzyć za pomocą wiersza poleceń i wykonywać w niej różne operacje, np.:

sqlite3 restauracja.db
sqlite> .table
sqlite> .schema klienci
sqlite> INSERT INTO klienci VALUES (NULL, 'Adam', 'Kowalski');
sqlite> SELECT * FROM klienci;
sqlite> .quit

Znaczenie kolejnych instrukcji:

• .table – pokazuje listę tabel,

• .schema [nazwa_tabeli] – pokaże kod SQL, który posłużył do utworzenia wszystkich tabel lub opcjonalnie podanej tabeli,

• .quit – wyjście z wiersza poleceń bazy,

• INSERT, SELECT – klauzule SQL, których używamy do dodawania i odczytywanie danych z tabel.

RseOVnslxwOaF

Zrzut ekranu przedstawia operacje na bazie przy użyciu wiersza poleceń. Tekst jest napisany w linijkach. lo1mint@atsbox:~/work$ sqlite3 restauracje.db < restauracja.sql. lo1mint@atsbox:~/work$ sqlite3 restauracje.db. SQLite version 3.37.2 2022‑01‑06 13:25:41. Enter ”.help” for usage hints. sqlite>.table. dania rabaty składy_zamowien. klienci rezerwacje stoliki. polprodukty składy_dan zamowienia. sqlite>. schema klienci. CREATE TABLE klienci (. id INTEGER PRIMARY KEY AUTOINCREMENT,. imie TEXT NOT NULL,. nazwisko TEXT NOT NULL. );. sqlite>.quit.

Drugie rozwiązanie polega na skorzystaniu z programu SQLiteStudio. Po jego uruchomieniu wybieramy polecenie Baza danych / Dodaj bazę danych i w polu plik wpisujemy albo wskazujemy ścieżkę oraz podajemy nazwę pliku bazy danych, np. restauracja.db. Po kliknięciu OK zostanie utworzona pusta baza danych w podanym pliku, a jej nazwa pojawi się w panelu Bazy danych po lewej stronie okna programu.

R1SDdS7Uze0vv

Zrzut ekranu przedstawia okno do otwierania lub tworzenia bazy danych w SQLiteStudio. Okno ma tytuł Baza danych. Środku okna znajduje się: Typ bazy danych – SQLite 3; Plik - /home/lo1mint/work/restauracja.db; Nazwa (na liście) – restauracja; Opcje – wybrana jest Trwała (trzymaj w konfiguracji). Na dole znajdują się trzy przyciski: Testuj połączenie, Cancel, OK.

Nazwę bazy w panelu dwa razy klikamy, aby się z nią połączyć, i otwieramy okno edytora SQL: Narzędzia / Otwórz edytor SQL. Następnie do pola Zapytanie wklejamy zawartość skryptu restauracja.sql lub wczytujemy ją po wybraniu polecenia Wczytaj SQL z pliku dostępnego pod ikoną katalogu na pasku narzędzi edytora. Wszystkie polecenia zaznaczamy np. naciskając CTRL+A i wykonujemy klikając przycisk Wykonaj zapytanie lub naciskając F9.

RkVxDNv569asU

Zrzut ekranu przedstawia wykonanie wielu instrukcji SQL w edytorze SQL programu SQLiteStudio. Na górze znajdują się opcje, ikonki. Po lewej stronie w kolumnie rozwinięty jest restauracja. W rozwinięciu znajduje się nierozwinięta Tabele (9). Na dole są Widoki. W centralnej części programu widoczny jest zaznaczony fragment kodu, który jest napisany w linijkach. 1 linijka – BEGIN;. 2 linijka – pusta. 3 linijka – DROP TABLE IF EXISTS klienci;. 4 linijka – CREATE TABLE klienci (. 5 linijka – id INTEGER PRIMARY KEY AUTOINCREMENT,. 6 linijka – imie TEXT NOT NULL,. 7 linijka – nazwisko TEXT NOT NULL. 8 linijka - );. 9 linijka – pusta. 10 linijka – DROP TABLE IF EXISTS stoliki;.

Utworzone tabele możemy przeglądać po wybraniu ich z listy w lewym panelu. Poniżej widać pola tabeli zamowienia:

Słownik