Wstęp

Już wiesz

Aby zrealizować ten etap projektu przypomnijcie sobie informacje z gimnazjum dotyczące:

  • budowanie i formatowanie tabel

  • adresowanie względne, bezwzględne i mieszane

  • budowanie formuł

  • stosowanie funkcji w formułach (SUMA, ŚREDNIA, JEŻELI, MAX, MIN, LICZ JEŻELI, SUMA JEŻELI)

  • budowanie formuł zagnieżdżonych

  • formatowanie warunkowe

  • tworzenie i modyfikowanie wykresów

  • budowanie tabel i wykresów przestawnych

  • sortowanie

  • filtrowanie

Zrealizowany projekt aplikacji zawiera zbiór danych i zależności pomiędzy nimi. W tym rozdziale nauczycie się w jaki sposób gromadzić, wizualizować i przetwarzać zgromadzone dane za pomocą arkusza kalkulacyjnego oraz projektować proste relacyjne bazy danych.

Zebranie danych statystycznych projektu w arkuszu kalkulacyjnym

Należy się zastanowić, jakie dane związane z projektowaną aplikacją można wyszczególnić. Przykładowo, dla realizowanej gry komputerowej mogą to być rezultaty rozgrywki (np. punktacja i czas). W przypadku multimedialnego przewodnika geograficznego możemy zestawić dane statystyczne dotyczące konkretnego regionu, zaś dla symulatora lotów kosmicznych będą to, na przykład, trajektorie lotów.
Zebrane dane można następnie zapisać w arkuszu kalkulacyjnym, aby przeprowadzić ich statystyczne podsumowanie.

Graficzna prezentacja danych

Zrealizowana aplikacja wiąże ze sobą dużą liczbę danych. Graficzna prezentacja danych w postaci wykresów ułatwia ich zrozumienie i interpretację. W zależności od kontekstu danych, należy umiejętnie dobrać odpowiedni typ wykresu.

iLmtoVARjI_d5e182

Projektowanie relacyjnej bazy danych

Projekt przewodnika multimedialnego zawiera bardzo dużo informacji związanych z opisem ciekawych miejsc w postaci tekstu, danych statystycznych, zdjęć, filmów i dźwięku. Zapisanie tych informacji w postaci bazy danych w arkuszu kalkulacyjnym lub w bazie danych umożliwi ich uporządkowanie oraz szybkie wyszukiwanie potrzebnych danych, również z wykorzystaniem zapytań w języku SQL.
Realizacja tej części projektu będzie wymagała sięgnięcia do informacji z gimnazjum w następującym zakresie:

  • tworzenie bazy danych,

  • wprowadzanie i edycja rekordów z wykorzystaniem języka SQL,

  • wyszukiwanie informacji w bazie danych,

  • sortowanie danych według wybranych pól.

W bazie dane są przechowywane w tabelach w postaci rekordów, gdzie każdy rekord składa się z pól o ściśle określonym typie danych.
W tej części e‑podręcznika korzystając z umiejętności nabytych w gimnazjum, należy utworzyć bazę danych składającą się z jednej tabeli, a następnie przekształcić ją do modelu relacyjnego.
Baza danych wykorzystywana w projekcie przewodnika multimedialnego będzie zawierała dane statystyczne dotyczące wybranych miast w Polsce.
Materiałem źródłowym, z którego pobierane są informacje jest arkusz kalkulacyjny dostępny poniżej:

R2mnzFRYsbhjM1
Dane statystyczne GUS
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.

Dane statystyczne zostały pobrane ze stron Głównego Urzędu Statystycznego . Portal ten jest ogromnym źródłem wiedzy i informacji na temat ludności, gospodarki, regionów administracyjnych i innych dziedzin życia publicznego. Stanowi on ogromną bazę, z której można pobierać i wykorzystywać dane.
Można również utworzyć bazę danych zawierającą inne informacje.
Tworzona baza danych będzie zawierała tabelę o nazwie Dane statystyczne umożliwiającą przechowywanie informacji statystycznych wykorzystywanych w projekcie. Tabela ta może zawierać następujące pola

  • nazwa miasta,

  • województwo,

  • liczba mieszkańców,

  • liczba kobiet,

  • liczba mężczyzn,

  • grupa wiekowa,

  • powierzchnia miasta,

  • powierzchnia terenów zielonych,

  • data aktualizacji danych.

Wprowadzone w bazie danych nazwy pól nie mogą zawierać spacji, a ze względu na stosowane implementacje języka zapytań SQL, należy zrezygnować z polskich znaków diakrytycznych. Czyli zamiast nazwy pola Liczba mieszkańców, w bazie danych powinna być nazwa Liczba_mieszkancow, w której zamiast spacji będzie wstawiony znak podkreślenia i nie będą stosowane polskie znaki diakrytyczne.
Proces tworzenia bazy można prześledzić w przedstawionym pokazie slajdów

RP2PaLAcEkPJ11
Prezentacja o kreatorze baz danych
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.

Przy wprowadzeniu danych do tak przygotowanej tabeli, wprowadzając dane dla różnych grup wiekowych oraz aktualizując dane dotyczące liczby mieszkańców w danym roku, należy wprowadzić również wszystkie pozostałe dane do pozostałych pól tabeli.

R1AEV1ZPEcCig1
Nadmiarowość danych
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.

We wpisanych do tabeli rekordach powtarzają się informacje o nazwie miasta, województwie, powierzchni miasta, powierzchni terenów zielonych, a więc tak zbudowana tabela zawiera informacje nadmiarowe.
W relacyjnych bazach danych tworzy się nieco bardziej skomplikowaną strukturę zapisu danych, która składa się z wielu tabel wzajemnie ze sobą powiązanych. Tworzenie relacyjnej bazy danych wymaga wyodrębnienia pól, które utworzą nowe tabele, dzięki czemu ograniczona zostanie nadmiarowość zapisu danych.

iLmtoVARjI_d5e301

Relacyjna baza danych

Relacyjne bazy danych są obecnie najpowszechniej wykorzystywanym systemem baz danych. Baza danych w tym modelu zawiera wiele tabel, w których gromadzone są informacje. Tabele (nazywane inaczej relacjami) mają zawarte w swojej strukturze pól logiczne powiązania pomiędzy danymi znajdującymi się w różnych tabelach. Przykładem realizacji takiego powiązania może być baza danych Przewodnik multimedialny zawierająca informacje statystyczne o wybranych miastach w Polsce. W tabeli Dane statystyczne, w polach Nazwa miastaWojewództwo wpisane wartości wielokrotnie się powtarzają przy wprowadzaniu kolejnych rekordów. Ponadto w tabeli Dane statystyczne można wyodrębnić trzy grupy pól logicznie ze sobą powiązane. Pierwsza grupa dotyczy opisu miasta, czyli Nazwa miasta i jego położenie, czyli Województwo. Druga grupa to dane dotyczące ludności, czyli pola Liczba mieszkańców, Liczba kobiet, Liczba mężczyzn, Grupa wiekowa. Ostatnia grupa, to dane dotyczące powierzchni miejskich, czyli pola Powierzchnia miastaPowierzchnia terenów zielonych. Ponadto, dane statystyczne pobrane z Głównego Urzędu Statystycznego, podawane są na określony dzień, miesiąc i rok. Projektowana baza danych może gromadzić informacje z wielu lat, więc należy dodać pole Data aktualizacji danych w grupach dotyczących informacji demograficznych i powierzchni miejskich. Przedstawiony podział na tak określone grupy danych pozwala uniknąć powtarzania danych. Zamiast tabeli Dane statystyczne należy utworzyć trzy tabele i powiązać rekordy między tymi tabelami tak, aby można było otrzymać pełną informację.

ROAGRsWwh3oBo1
Tworzenie relacyjnych baz danych
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.

Nowe tabele będą miały następującą strukturę:
tabela Miasta zawierać będzie dwa pola

  • Nazwa miasta,

  • Województwo,

tabela Dane demograficzne będzie składała się z następujących pól:

  • Liczba mieszkańców,

  • Liczba kobiet,

  • Liczba mężczyzn,

  • Grupa wiekowa,

  • Data aktualizacji danych.

tabela Powierzchnie miejskie będzie miała pola:

  • Powierzchnia miasta,

  • Powierzchnia terenów zielonych,

  • Data aktualizacji danych.

Projektując bazę danych należy unikać zapisywania danych, które można obliczyć na podstawie danych zapisanych w tym samym rekordzie. W przypadku tabeli Dane demograficzne, liczbę mężczyzn można wyznaczyć z liczby mieszkańców i liczby kobiet, dlatego zapisywanie tej informacji jest zbędne.
Utworzone trzy tabele należy wzajemnie powiązać tak, aby jednoznacznie określić, które rekordy z tabeli Powierzchnie miejskie i Dane demograficzne są powiązane z określonym miastem w tabeli Miasta. Stosuje się do tego dodatkowe pola dodawane do wszystkich tabel. W tabeli Miasta dodajemy pole ID_miasta będące kluczem głównym tej tabeli, jednoznacznie identyfikującym każdy jej rekord. Cechą charakterystyczną klucza głównego jest jego unikalność w obrębie tabeli. W tabeli Dane demograficzne i Powierzchnie miejskie dodawane są pola o zwykle takiej samej nazwie, czyli ID_miasta, będące w tych tabelach kluczem obcym, umożliwiającym właściwe powiązanie rekordów między tabelami. Ostateczna postać struktury bazy danych Przewodnik multimedialny jest określona poprzez trzy tabele Miasta, Dane demograficzne oraz Powierzchnie miejskie, w których zastosowano powiązanie za pomocą pola ID_miasta. Struktura pól w tabelach, po dokonanych modyfikacjach będzie następująca:

RE1YXnz5pY2wX1
Ilustracja obsługi baz danych
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.

Tabela Miasta zawierać będzie trzy pola

  • ID_miasta (klucz główny),

  • Nazwa miasta,

  • Województwo.

Tabela Dane demograficzne składać się będzie z następujących pól

  • ID_miasta (klucz obcy),

  • Liczba mieszkańców,

  • liczba kobiet,

  • Grupa wiekowa,

  • Data aktualizacji danych.

Tabela Powierzchnie miejskie składać się będzie z następujących pól

  • ID_miasta (klucz obcy),

  • Powierzchnia miasta,

  • Powierzchnia terenów zielonych,

  • Data aktualizacji danych.

Zdefiniowany typ danych dla pola ID_miasta to typ numeryczny –najczęściej jest to liczba całkowita. Wartości w kolumnie pola klucza głównego muszą być niepowtarzalne (unikalne), jednoznacznie identyfikujące każdy rekord. Jedną z metod osiągnięcia niepowtarzalności jest autoinkrementacja, polegająca na nadawaniu kolejnym dodawanym do tabeli rekordom wartości kolejnych liczb całkowitych. Istnieją powszechnie stosowane dane, które mogą pełnić rolę klucza głównego. W przypadku danych osobowych jest to najczęściej numer PESEL, numer identyfikacji podatkowej - NIP, numer indeksu studenta lub legitymacji szkolnej.
W tabelach Dane demograficznePowierzchnie miejskie dodane zostało pole ID_miasta określane jako klucz obcy. Pole to służy do jednoznacznego powiązania rekordów z tabeli Miasta z rekordami w tabelach Dane demograficznePowierzchnie miejskie. W polu ID_miasta w tabelach Dane demograficznePowierzchnie miejskie mogą być zapisywane wyłącznie wartości występujące w polu ID_miasta tabeli Miasta.

Ryo1ChFJaSOZF1
Powiązania rekordów w bazach relacyjnych
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.
iLmtoVARjI_d5e452

Dodawanie rekordów

Sposób wprowadzania rekordów do tabeli omawiany był w e‑podręczniku dla gimnazjum. Rekordy można dodawać w trybie graficznym, przy wykorzystaniu widoku tabeli lub widoku formularza. Alternatywną metodą jest dodawanie rekordów z użyciem polecenia INSERT języka SQL.
Przy wprowadzaniu danych należy pamiętać o właściwym powiązaniu rekordów pomiędzy tabelą nadrzędną Miasta oraz tabelami podrzędnymi Dane demograficznePowierzchnie miejskie. Kolejne wprowadzone rekordy do tabeli Miasta mają wartość pola ID_miasta zwiększaną (inkrementowaną) o jeden. Ponadto, w tabeli Miasta wpisanych jest tylko sześć rekordów, natomiast w tabeli Dane statystyczne wpisanych rekordów było wielokrotnie więcej. Wartości występujące w polu ID_miasta są unikalne, nie możemy więc dodać nowego rekordu o już istniejącej w tym polu wartości. Po wprowadzeniu danych do tabeli Dane demograficzne, w polu klucza obcego ID_miasta będą występowały wyłącznie wartości odpowiadające wartościom z pola ID_miasta w tabeli Miasta.
Zrozumienie sposobu wprowadzania rekordów w bazie relacyjnej ułatwi pokaz slajdów zamieszczony poniżej

R15Yc8r9s3gj21
Dodawanie rekordów
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.
iLmtoVARjI_d5e493

Edycja rekordów

Już wiesz

Z podręcznika dla gimnazjum, przypomnijcie sobie rozdział „Operacje na bazie danych”,

Modyfikacja danych w tabelach bazy relacyjnej jest przeprowadzana z wykorzystaniem tych samych rozwiązań, które były omawiane w e‑podreczniku w gimnazjum. Podczas modyfikacji danych w tabelach bazy relacyjnej należy pamiętać o tym żeby nie modyfikować pola klucza głównego w tabeli Miasta bo może prowadzić do braku spójności relacji. Modyfikacja wartości klucza obcego w tabelach Dane demograficzne i Powierzchnie miejskie może powiązać rekordy z innym rekordem w tabeli nadrzędnej lub spowodować, że rekordy nie będą powiązane z żadnym rekordem w tabeli Miasta. Pokaz slajdów wstawiony poniżej ilustruje metody modyfikacji danych w widoku formularza oraz z wykorzystaniem zapytań SQL

RWAPTggkUNNBb1
Edycja rekorów
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.
iLmtoVARjI_d5e533

Wyszukiwanie danych

Wyszukiwanie informacji w relacyjnej bazie danych jest realizowane w nieco odmienny sposób niż w bazie zawierającej jedną tabelę. Dane są zapisywane w wielu tabelach powiązanych ze sobą za pomocą kluczy głównych i kluczy obcych. W zależności od tego, czy wyszukiwane informacje znajdują się w jednej tabeli, czy w wielu tabelach, stosuje się różną składnię zapytań w języku SQL.

Sposób konstruowania zapytań umożliwiający wyszukanie informacji w jednej tabeli był omawiany w gimnazjum w rozdziale dotyczącym baz danych i wyszukiwania informacji w bazie danych. Wyszukiwanie informacji z dwóch tabel wymaga wprowadzenia do zapytania, w klauzuli WHERE, kryteriów dotyczących klucza głównego i klucza obcego. Wprowadzone kryteria wyszukiwania muszą powiązać rekordy z tabeli podrzędnej z rekordem z tabeli nadrzędnej.

Oto przykładowe zadanie, które będzie polegało na wyszukaniu informacji o danych demograficznych i powierzchniach miejskich dla Krakowa. Nazwa miasta jest zapisana w tabeli Miasta. Dane demograficzne są zapisane w tabeli Dane demograficzne. Dane są powiązane miedzy tabelami poprzez pole ID_miasta, które w tabeli Miasta jest kluczem głównym, a w tabeli Dane demograficzne jest kluczem obcym. Wyszukanie w tabeli Miasta danych dla Krakowa można zrealizować za pomocą zapytania, które będzie miało w klauzuli WHERE kryterium wykorzystujące nazwę miasta lub identyfikator zapisany w polu ID_miasta. Zapytanie w języku SQL będzie wyglądało następująco:
SELECT Nazwa_miasta,
Wojewodztwo,
 ID_miasta
 FROM Miasta
 WHERE Nazwa_miasta = 'Kraków';
lub z wykorzystaniem pola ID_miasta
SELECT Nazwa_miasta,
 Wojewodztwo,
 ID_miasta
 FROM Miasta
 WHERE ID_miasta = 2;
Na zamieszczonym niżej pokazie slajdów przedstawiony został proces tworzenia zapytań w relacyjnej bazie danych

Ru4MpKxxRpdi81
Pola klucza głównego
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.

Zapytanie wyszukujące rekordy z tabeli Dane demograficzne musi wykorzystywać wartości zapisane w kluczu obcym tej tabeli ID_miasta i kod polecenia w języku SQL będzie następujący:

SELECT Liczba_mieszkancow,
 Liczba_kobiet,
 Grupa_wiekowa,
 Data_aktualizacji_danych,
 ID_miasta
 FROM Dane_demograficzne
 WHERE ID_miasta = 2;

RMKRbFz20r1MO1
Zapytania SQL
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.

Zapytanie wyszukujące rekordy z tabeli Powierzchnie miejskie musi wykorzystywać wartości zapisane w kluczu obcym tej tabeli czyli polu ID_miasta. Kod polecenia w języku SQL będzie następujący.

SELECT Powierzchnia_miasta,
 Powierzchnia_terenow_zielonych,
 Data_aktualizacji_danych,
 ID_miasta
 FROM Powierzchnie_miejskie
 WHERE ID_miasta = 2;

Realizacja przedstawionych zapytań SQL dla trzech tabel bazy Przewodnik multimedialny zwróci wyniki w postaci wyszukanych rekordów w trzech oddzielnych widokach. Tak jak zostało to przedstawione w poniższym pokazie slajdów.

RDabs4TGv3wMz1
Zapytania SQL
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.

Wykorzystanie tak uzyskanych danych nie jest wygodne, zmusza do ciągłej kontroli pól klucza ID_miasta w celu połączenia danych z różnych tabel. Znacznie lepszym rozwiązaniem jest otrzymanie wyniku zapytania SQL w jednym widoku. Język SQL oferuje możliwość konstruowania zapytań, w których można odwołać się do pól występujących w różnych tabelach. Składnia polecenia języka SQL musi uwzględniać nazwę tabeli przy wprowadzaniu w treści zapytania nazwy pola tej tabeli. Składnia instrukcji jest następująca:
SELECT table_name.column1, table_name.column2, ... table_name.columnN
FROM table_name
WHERE [condition]

W instrukcji występują już znane elementy określające nazwę tabeli oraz warunki wyszukiwania. Inny jest zapis nazwy pola, który poprzedzony jest nazwą tabeli i separatorem w postaci kropki. Ten sposób zapisu jest powszechnie wykorzystywaniu w adresach internetowych.
Postać zapytania umożliwiającego wybór Krakowa z tabeli miasta będzie miał następującą postać:
SELECT Miasta.Nazwa_miasta,
 Miasta.Wojewodztwo,
 Miasta.ID_miasta
 FROM Miasta
 WHERE Miasta.ID_miasta = 2;

W zapytaniu, które będzie pobierało dane z trzech tabel, należy uwzględnić nazwy tabel w klauzuli FROM. Trzeba wymienić nazwy wszystkich tabel, z których są wybierane pola, rozdzielając je przecinkiem. W warunku, który występuję w klauzuli WHERE, należy uwzględnić wszystkie nazwy pól występujące w poprzednich zapytaniach i połączyć je w jedno zdanie logiczne z operatorem AND.
Przykład zapytania SQL przedstawiony jest poniżej
SELECT Miasta.Nazwa_miasta,
 Miasta.Wojewodztwo,
 Miasta.ID_miasta,
 Dane_demograficzne.Liczba_mieszkancow,
 Dane_demograficzne.Liczba_kobiet,
 Dane_demograficzne.Grupa_wiekowa,
 Dane_demograficzne.Data_aktualizacji_danych,
 Dane_demograficzne.ID_miasta,
 Powierzchnie_miejskie.Powierzchnia_miasta,
 Powierzchnie_miejskie.Powierzchnia_terenow_zielonych,
 Powierzchnie_miejskie.Data_aktualizacji_danych,
 Powierzchnie_miejskie.ID_miasta
 FROM Miasta,
 Dane_demograficzne,
 Powierzchnie_miejskie
 WHERE Miasta.ID_miasta = 2
 AND
 Dane_demograficzne.ID_miasta = 2
 AND
 Powierzchnie_miejskie.ID_miasta = 2;
Wynikiem działania takiego zapytania jest widok zawierający scalone dane z trzech tabel. Proces konstruowania zapytania SQL został przedstawiony poniżej:

R1FKvIjqBSZqN1
Bazy danych PM‑10
Źródło: Maciej Kacperski <maciej.kacperski@p.lodz.pl>, licencja: CC BY 3.0.

W wyniku zapytania wyświetlane są również pola klucza głównego i pola kluczy obcych Miasta.ID_miasta, Dane_demograficzne.ID_miasta, Powierzchnie_miejskie.ID_miasta pola te w tym widoku nie są przydatne dla użytkownika, ponieważ informacja z trzech tabel została już scalona. Można usunąć te pola z klauzuli SELECT. Ostateczna postać zapytania będzie następująca:
SELECT Miasta.Nazwa_miasta,
 Miasta.Wojewodztwo,
 Miasta.ID_miasta,
 Dane_demograficzne.Liczba_mieszkancow,
 Dane_demograficzne.Liczba_kobiet,
 Dane_demograficzne.Grupa_wiekowa,
 Dane_demograficzne.Data_aktualizacji_danych,
 Dane_demograficzne.ID_miasta,
 Powierzchnie_miejskie.Powierzchnia_miasta,
 Powierzchnie_miejskie.Powierzchnia_terenow_zielonych,
 Powierzchnie_miejskie.Data_aktualizacji_danych,
 Powierzchnie_miejskie.ID_miasta
 FROM Miasta,
 Dane_demograficzne,
 Powierzchnie_miejskie
 WHERE Miasta.ID_miasta = 2
 AND
 Dane_demograficzne.ID_miasta = 2
 AND
 Powierzchnie_miejskie.ID_miasta = 2