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

Ź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

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

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 miasta i Wojewó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 miasta i Powierzchnia 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

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

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 demograficzne i Powierzchnie 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 demograficzne i Powierzchnie miejskie. W polu ID_miasta w tabelach Dane demograficzne i Powierzchnie miejskie mogą być zapisywane wyłącznie wartości występujące w polu ID_miasta tabeli Miasta.

Ryo1ChFJaSOZF1

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 demograficzne i Powierzchnie 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

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

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

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

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

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

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

Ćwiczenie – Przykład Przewodnika

Strona internetowa projektu (Internet)

Analiza danych aplikacji (Bazy danych)