Ćwiczenia, wprowadzające w świat tworzenia relacyjnego modelu reprezentującego rzeczywistość, umożliwiają naukę tworzenia kwerend wybierających informacje oraz zapytań parametrycznych – przeprowadzimy je na przykładzie bazy danych dziennika szkolnego.

Pliki zawierające rekordy pochodzą z zadania maturalnego z informatyki rozszerzonej.

W archiwum znajdują się trzy pliki tekstowe:

• uczniowie.txt – z atrybutami opisującymi uczniów: IDucznia, nazwisko, imie, ulica, dom, IDklasy,

• oceny.txt – z danymi na temat uzyskanych ocen: IDucznia, Ocena, Data, IDprzedmiotu,

• przedmioty.txt – z danymi o realizowanych w szkole przedmiotach: IDprzedmiotu, NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.

Dane zgromadzone w plikach będą potrzebne do realizacji tego e‑materiału.

Import danych z plików tekstowych

Przed dokonaniem analizy modelu relacyjnego na przykładzie udostępnionej bazy dziennika szkolnego należy przenieść dane z plików tekstowych do tabel programu MS Access. Proces ten omówiono szczegółowo w filmie:

RbfcqSsZoxqVe

Film nawiązujący do treści materiału: Import danych z pliku tekstowego.

Film nawiązujący do treści materiału: Import danych z pliku tekstowego.

Film dostępny pod adresem /preview/resource/RbfcqSsZoxqVe

Film nawiązujący do treści materiału: Import danych z pliku tekstowego.

Oprócz przeniesienia rekordów do tabel dokonaliśmy także oznaczenia logicznych powiązań pomiędzy atrybutami obiektów. Ostatecznie struktura bazy danych prezentuje się następująco w widoku relacji:

Rn7QKdhmWdUgE

Zrzut ekranu przedstawia relacje w bazie danych.
W zakładce relacje znajdują się trzy tabele: Przedmioty, Oceny, Uczniowie.
W skład tabeli przedmioty wchodzi: IDprzedmiotu z kluczem obcym, NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
W skład tabeli Oceny wchodzi: IDoceny, IDucznia, Ocena, Data, IDprzedmiotu.
W skład tabeli Uczniowie wchodzi: IDucznia, nazwisko, imie, ulica, dom, IDklasy.
Relacje między tabelami to jeden do wielu dla: pole IDprzedmiotu z tabeli Przedmioty z polem IDprzedmiotu z tabeli Oceny.
Jeden do wielu dla: IDucznia z tabeli Uczniowie z IDucznia z tabeli Oceny.

Ćwiczenie 1

Przedstawmy także import danych z plików tekstowych oraz sposób definiowania relacji w programie LibreOffice Base – w przypadku użytkowania tego narzędzia, kliknij tutaj:

Pokaż rozwiązanie

Importu tabeli Uczniowie należy dokonać poprzez otwarcie pliku tekstowego uczniowie.txt w narzędziu LibreOffice Calc, nie zmieniając domyślnych opcji importu pliku tekstowego. Upewnij się jedynie, że w opcjach wyboru separatora zaznaczono pole przy średniku:

R1ISBsFhqiEs1

Zrzut ekranu przedstawia fragment okna dialogowego: Importuj tekst, pliku: uczniowie.txt.
W sekcji Importuj znajduje się pole Zestaw znaków z wybraną opcją: Unicode (UTF‑8).
Następnie w polu język wybrano: domyślny - Polski.
W polu od wiersza wybrano wartość: 1.
Poniżej znajduje się sekcja Opcje separatora.
Wybrana w niej jest opcja Rozdzielony.
Poniżej zakreślony czerwonym prostokątem został zaznaczony: średnik.

Po otwarciu pliku w arkuszu kalkulacyjnym z menu Edycja, wybieramy  opcję Zaznacz | Zaznacz obszar danych lub naciskamy kombinację klawiszy Ctrl + *. W ten sposób zaznaczymy wszystkie dane znajdujące się w arkuszu:

R1OzbIdqOWIUd

Zrzut ekranu przedstawia program LibreOffice Calc.
Po kliknięciu w opcję: edytuj pojawiła się lista rozwijana gdzie rozwinięto opcję: zaznacz. <br. Następnie z kolejnej listy wybrano opcję: Zaznacz obszar danych.

Następnie kopiujemy zaznaczony obszar danych za pomocą kombinacji klawiszy Ctrl + C, po czym przechodzimy już do narzędzia LibreOffice Base. Upewnijmy się przy tym, że w oknie Baza danych aktywną zakładką jest zakładka Tabele:

R1W99DXkRf3qL

Zrzut ekranu przedstawia bazę sklep‑pszczelarski w programie LibreOffice Base.
Pod Paskiem narzędzi znajdują się trzy sekcje: Baza danych, Zadania oraz kwerendy.
W sekcji baza danych wybrano opcję: Tabele.
W sekcji Zadania znajdują się trzy dostępne opcje: Utwórz projekt tabeli, Użyj kreatora, aby utworzyć tabelę, utwórz widok.

Teraz możemy wkleić informację ze schowka za pomocą kombinacji klawiszy Ctrl + V. Spowoduje to otwarcie kreatora Kopiuj tabelę. Poprawną konfigurację zatwierdzamy przyciskiem Dalej, po czym przenosimy wszystkie skopiowane kolumny do nowo tworzonej tabeli:

RiBbitm8HlrJo

Zrzut ekranu przedstawia okno dialogowe: kopiuj tabelę.
W polu nazwa tabeli wpisano: Uczniowie, w sekcji Opcje zaznaczono opcję: Definicje i dane oraz: Użyj pierwszego wiersza jako nazw kolumn.
Pola te zaznaczone są czerwonymi prostokątami.
Okno to zawiera przyciski pomoc, anuluj, dalej, utwórz, wstecz.
Poniżej znajduje się kolejne okno dialogowe: Zastosuj kolumny.
Znajduje się w nim pusta sekcja: Istniejące kolumny po lewej stronie oraz po prawej z danymi: IDucznia, nazwisko, imie, ulica, dom, IDklasy.
Pomiędzy sekcjami znajdują się przyciski gdzie czerwonym prostokątem zaznaczona jest strzałka z kreską po prawej stronie.
Okno to zawiera takie przyciski jak: pomoc, wstecz, dalej, utwórz, anuluj.

W trzecim kroku kreatora zawsze należy dobrać odpowiednie typy danych do atrybutów. Przykładowa konfiguracja:

• IDucznia – typ danych Integer [ INTEGER ], czyli wartość liczbowa całkowita dla klucza głównego,

• nazwisko – typ danych Tekst [ VARCHAR ],

• imie – typ danych Tekst [ VARCHAR ],

• ulica – typ danych Tekst [ VARCHAR ],

• dom – typ danych Tekst [ VARCHAR ],

• IDklasy – typ danych Tekst [ VARCHAR ], ponieważ mimo mylącej nazwy nie jest to klucz obcy, tylko tekstowa nazwa klasy, np. 1a, 1b itd.

Pamiętajmy, by koniecznie ustanowić w tabeli klucz podstawowy – klikając prawym przyciskiem myszy na atrybucie IDucznia, zaznaczamy opcję Klucz główny:

R1INipYNgcWyS

Zrzut ekranu przedstawia okno dialogowe: formatowanie typu.
IDucznia został wybrany jako klucz główny.
Poniżej znajdują się nagłówki: nazwisko, imie, ulica, dom, IDklasy.
Po prawej stronie widoczne jest pole: typ pola z wybraną wartością: Integer [INTEGER] .
W polu nazwa pola wpisano: IDucznia, w polu Długość: 10, w Wiersze (maks) 10.
Okno to zawiera takie przyciski jak: Pomoc, wstecz, dalej, utwórz, anuluj, automatyczne.

Jak widać, spowodowało to dodanie małej ikonki klucza przy wskazanym atrybucie. Teraz możemy już zakończyć import tabeli, klikając przycisk Utwórz.

Analogicznie postępujemy dla dwóch pozostałych tabel, importując dane przy użyciu narzędzia LibreOffice Calc jako pośrednika w procesie przenoszenia plików tekstowych do bazy danych. Pamiętajmy, aby zawsze w pierwszym kroku kreatora importu zaznaczona była opcja Definicje i dane.

Typy danych dobieramy odpowiednio do przechowywanej wartości – w razie wątpliwości kieruj się opisem kolumn przedstawionym w filmie dotyczącym MS Access. Zwróć szczególną uwagę na pole Data w tabeli Oceny, które nie powinno mieć tekstowego typu danych, tylko DATA [ DATE ] oraz na klucze obce IDucznia i IDprzedmiotu, które są liczbami całkowitymi Integer [ INTEGER ].

Warto jeszcze zauważyć, że tabela Oceny nie zawiera atrybutu, który mógłby stać się kluczem głównym – w związku z tym pozwalamy kreatorowi dodać własny klucz podstawowy, np. o nazwie IDoceny:

R17t41EVUiVeE

Zrzut ekranu przedstawia okno dialogowe: Kopiuj tabelę.
Poniżej w polu nazwa tabeli wpisano: Oceny.
W sekcji Opcje wybrano opcję: Definicje i dane, utwórz nowe pole jako klucz główny oraz użyj pierwszego wiersza jako nazwy kolumn.
W pole nazwa wpisano: IDoceny.
Okno to zawiera takie przyciski jak: pomoc, wstecz, dalej, utwórz, anuluj.

W celu stworzenia relacji pomiędzy zaimportowanymi tabelami z menu wybieramy Narzędzia | Relacje, po czym dodajemy wszystkie trzy tabele do projektu relacji.

Ustanawiamy analogiczne do MS Access powiązania pomiędzy kluczami podstawowymi i obcymi:

Riagbq1SGzEvL

Zrzut ekranu przedstawia relacje w bazie danych Szkoła.
W polu głównym znajdują się trzy tabele: Przedmioty, Oceny, Uczniowie.
W skład tabeli przedmioty wchodzi: IDprzedmiotu z kluczem obcym, NazwaPrzedmiotu, Nazwisko_naucz, Imie_naucz.
W skład tabeli Oceny wchodzi: IDoceny, IDucznia, Ocena, Data, IDprzedmiotu.
W skład tabeli Uczniowie wchodzi: IDucznia, nazwisko, imie, ulica, dom, IDklasy.
Relacje między tabelami to jeden do wielu dla: pole IDprzedmiotu z tabeli Przedmioty z polem IDprzedmiotu z tabeli Oceny.
Jeden do wielu dla: IDucznia z tabeli Uczniowie z IDucznia z tabeli Oceny.

Zanim przygotujemy naszą pierwszą kwerendę wybierającą, przyjrzyjmy się najpierw temu modelowi danych – na ile skutecznie i dokładnie odwzorowuje on codzienne życie szkolnej społeczności.

Analiza istniejącego modelu danych

Na początek przypomnijmy definicję modelu danych – jest to integralny zbiór zasad opisujących stan reprezentowanych obiektów, ich zachowania oraz istniejące powiązania logiczne pomiędzy fragmentami symulowanego świata.

Proces tworzenia modelu reprezentującego konkretną rzeczywistość co do zasady przebiega w trzech teoretycznych etapach:

• Projektowanie konceptualne – polega na wyodrębnieniu ze świata autonomicznych obiektów (instancji) posiadających własny zestaw atrybutów, który nazywamy klasą lub encją. W przypadku dziennika szkolnego obiektami są np. uczniowie, zaś atrybuty każdego takiego obiektu to m.in.: imię, nazwisko, adres zamieszkania.

• Projektowanie logiczne – do obiektów dających się wyróżnić w symulowanym świecie, które opisaliśmy w projektowaniu konceptualnym, dodajemy logiczne powiązania (relacje) występujące pomiędzy atrybutami. Dokonujemy tego przy użyciu specjalnych atrybutów, które nazywamy kluczami. W analizowanym tu modelu dziennika szkolnego przykładem logicznej relacji jest związek pomiędzy atrybutem obiektu uczeń o nazwie IDucznia (klucz podstawowyKlucz podstawowyklucz podstawowy) oraz atrybutem obiektu ocena o tej samej nazwie (klucz obcyKlucz obcyklucz obcy).

• Projektowanie fizyczne – poprzednie dwa etapy pracy nad modelem nie uwzględniają decyzji technicznych związanych z implementacją modelu w konkretnym DBMSDatabase Management SystemDBMS. A zatem trzeci, finałowy etap budowy symulacji polega na określeniu przez projektanta nazw tabel i kolumn, przypisaniu atrybutom obiektów właściwych typów danych oraz wszelkich działaniach konfiguracyjnych, specyficznych dla wybranego systemu bazodanowego.

Wprawne i skuteczne modelowanie rzeczywistości w systemach bazodanowych to trudne zadanie, wymagające praktycznego doświadczenia. Dlatego też naukę modelowania baz danych w początkowym etapie najlepiej rozpocząć od analizy już istniejących przykładowych struktur, zamiast od syntezy w pełni kompletnej symulacji. Przyjrzyjmy się zatem udostępnionemu w zadaniu modelowi dziennika szkolnego:

RLUMrHTOUh7aW

Film nawiązujący do treści materiału: Analiza struktury modelu relacyjnego.

Film nawiązujący do treści materiału: Analiza struktury modelu relacyjnego.

Film dostępny pod adresem /preview/resource/RLUMrHTOUh7aW

Film nawiązujący do treści materiału: Analiza struktury modelu relacyjnego.

Reasumując, w projektowaniu konceptualno‑logicznym dokonaliśmy analizy rzeczywistości szkolnej pod kątem istnienia:

• dających się wyróżnić obiektów (tworząc rekordy, czyli wiersze w tabelach),

• niezbędnych atrybutów tych obiektów (zestaw takich cech to klasa lub encja),

• występujących powiązań logicznych (relacji) pomiędzy atrybutami obiektów (stosując klucze podstawowe oraz klucze obce).

Wspomniane działania prowadzą jednak zawsze do ostatniego etapu pracy, który nazywamy projektowaniem logicznym – jest to ogół działań specyficznych dla używanego przez projektanta DBMSDatabase Management SystemDBMS.

W przypadku MS Access należało dokonać operacji importu danych ze szczególną dbałością o poprawne określenie typów danych dla kolumn – tak, aby symulacja mogła wiernie reprezentować opisywaną rzeczywistość.

Przeprowadzona przez nas analiza ujawniła także istniejące w modelu tzw. anomalie – czyli okoliczności, w których może dojść do utraty spójności (integralności) danych. W takim wypadku przechowywane informacje nie będą już zgodne ze stanem faktycznym występującym w symulowanym świecie.

Przykładem anomalii jest sytuacja wystąpienia redundancji w tabeli Uczniowie – informacja o nazwie klasy (atrybut IDklasy) powtarza się w wielu wierszach. W związku z tym dokonanie modyfikacji wartości tego atrybutu okazało się działaniem obarczonym ryzykiem wystąpienia anomalii.

W praktyce anomalie najłatwiej wykryć w analizie modelu, dokonując operacji: wstawiania, modyfikowania oraz usuwania danych. Natomiast ogół działań projektowych, prowadzących do zapobiegania utracie integralności danych, to tzw. normalizacja modelu.

Kwerendy wybierające i parametryczne

Pora na utworzenie pierwszych kwerend, czyli napisanych w języku SQLSQLjęzyku SQL zapytań skierowanych do bazy danych, które zwracają wartości atrybutów w rekordach spełniających zadane kryteria.

Oczywiście kwerendy w bazach danych nie służą jedynie do wyszukiwania informacji, lecz także wstawiają, modyfikują oraz usuwają dane. Ponadto, mogą mieć wpływ na elementy struktury modelu (konfiguracja tabel, kolumn, typów danych, kluczy podstawowych i obcych).

Swoją przygodę z bazami danych zdecydowana większość osób rozpoczyna jednak od poznania dwóch klasycznych rodzajów zapytań:

• kwerendy wybierające – zwracają informacje spełniające określone kryteria zdefiniowane w treści zapytania,

• kwerendy parametryczne – działają identycznie jak wybierające, z tą jednak różnicą, że poszukiwaną wartość kryterium można wprowadzić dopiero po uruchomieniu zapytania.

Słownik