Wydrukuj Pobierz materiał do PDF Pobierz materiał do EPUB Pobierz materiał do MOBI Zaloguj się, aby skopiować i edytować materiał Zaloguj się, aby udostępnić materiał Zaloguj się, aby dodać całą stronę do teczki

Przed przystąpieniem do tego materiału, upewnij się, że posiadasz wiedzę 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.

  • podstawowa obsługa arkuszy kalkulacyjnych

Najpotrzebniejsze informacje znajdziesz w materiałach: Wprowadzenie do baz danychDuq1BycqWWprowadzenie do baz danych oraz Operacje na bazie danychDeLWXIQ5Operacje na bazie danych.

Większość aplikacji zawiera zbiory danych wraz z zależnościami pomiędzy nimi. W tym materiale dowiesz się w jaki sposób gromadzić, wizualizować i przetwarzać zgromadzone dane za pomocą arkusza kalkulacyjnego oraz projektować proste relacyjne bazy danych.

Przedstawione w tym materiale operacje na bazach danych wykonywane będą w programie SQLiteStudio (wersja SQLiteStudio 3.3.3) dostępnym do pobrania na stronie https://sqlitestudio.pl.

Zebranie danych statystycznych w arkuszu kalkulacyjnym

Przy projektowaniu każdej aplikacji należy się zastanowić, jakie dane związane z wybraną tematyką 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.

Projektowanie relacyjnej bazy danych

Dla przykładu, projekt przewodnika multimedialnego zawierać będzie 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 specjalnej aplikacji bazodanowej umożliwi ich uporządkowanie oraz szybkie wyszukiwanie potrzebnych danych, również z wykorzystaniem zapytań w języku SQL.

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. 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 w formacie .xlsx oraz .ods:

RtqTj5JXilh6Y

Jest tu przycisk „Pobierz załącznik”. Pobierze on dane statystyczne wybranych miast Polski z Głównego Urzędu Statystycznego w formacie .xlsx.

Dane statystyczne wybranych miast Polski z Głównego Urzędu Statystycznego.
Źródło: Maciej Kacperski, licencja: CC BY 3.0.
Plik XLSX o rozmiarze 27.38 KB w języku polskim
REh4bHhXrM9Rb

Jest tu przycisk „Pobierz załącznik”. Pobierze on dane statystyczne wybranych miast Polski z Głównego Urzędu Statystycznego w formacie .ods.

Dane statystyczne wybranych miast Polski z Głównego Urzędu Statystycznego.
Źródło: Maciej Kacperski, licencja: CC BY 3.0.
Plik ODS o rozmiarze 18.20 KB w języku polskim

Powyższe dane statystyczne z roku 2013 zostały pobrane ze stron Głównego Urzędu Statystycznego - https://stat.gov.pl. Portal ten jest ogromnym źródłem wiedzy i informacji na temat ludności, gospodarki, regionów administracyjnych i rozmaitych 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ć poniżej.

RsUOYmU8ZHiVG
Źródło: GroMar, 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.

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. Obie nadmiarowości zostały zaznaczone na poniższych zrzutach ekranu.

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.

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ązanych. 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ę.

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.

R5L8ZhLXjBLKl
Źródło: GroMar, licencja: CC BY 3.0.

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 można uznać za zbędne.

Utworzone trzy tabele należy wzajemnie powiązać tak, aby jednoznacznie określić, które rekordy z tabeli Powierzchnie miejskieDane 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 demograficznePowierzchnie 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 MiastaDane 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:

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.

R12TKCXpECgrc
Źródło: GroMar, licencja: CC BY 3.0.

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.

Dodawanie rekordów

Rekordy można dodawać w trybie graficznym, przy wykorzystaniu widoku siatki 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.
Sposobu wprowadzania rekordów w bazie relacyjnej został przedstawiony poniżej.

R1a6SrCjV958P
Źródło: GroMar, licencja: CC BY 3.0.

Edycja rekordów

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 demograficznePowierzchnie 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. Poniżej zaprezentowane zostały metody modyfikacji danych w widoku siatki oraz z wykorzystaniem zapytań SQL

R16rlfo31Oekv
Źródło: GroMar, licencja: CC BY 3.0.

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.

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.

Następujący przykład będzie polegał 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:

Linia 1. SELECT Nazwa podkreślnik miasta przecinek Wojewodztwo przecinek ID podkreślnik miasta. Linia 2. FROM Miasta. Linia 3. WHERE Nazwa podkreślnik miasta znak równości apostrof Kraków apostrof średnik.

lub z wykorzystaniem pola ID_miasta:

Linia 1. SELECT Nazwa podkreślnik miasta przecinek Wojewodztwo przecinek ID podkreślnik miasta. Linia 2. FROM Miasta. Linia 3. WHERE ID podkreślnik miasta znak równości 6 średnik.
R1EkaL5oYdTa9
Zrzut ekranu programu SQLiteStudio prezentujący zapytanie do bazy danych. 
Źródło: GroMar, 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:

Linia 1. SELECT Liczba podkreślnik mieszkancow przecinek. Linia 2. Liczba podkreślnik kobiet przecinek. Linia 3. Grupa podkreślnik wiekowa przecinek. Linia 4. Data podkreślnik aktualizacji podkreślnik danych przecinek. Linia 5. ID podkreślnik miasta. Linia 6. FROM Dane podkreślnik demograficzne. Linia 7. WHERE ID podkreślnik miasta znak równości 6 średnik.
R12xGy9khJlrd
Zrzut ekranu programu SQLiteStudio prezentujący zapytanie do bazy danych.
Źródło: GroMar, 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.

Linia 1. SELECT Powierzchnia podkreślnik miasta przecinek. Linia 2. Powierzchnia podkreślnik terenow podkreślnik zielonych przecinek. Linia 3. Data podkreślnik aktualizacji podkreślnik danych przecinek. Linia 4. ID podkreślnik miasta. Linia 5. FROM Powierzchnie podkreślnik miejskie. Linia 6. WHERE ID podkreślnik miasta znak równości 6 średnik.
RsYaCZjyoXcX4
Zrzut ekranu programu SQLiteStudio prezentujący zapytanie do bazy danych.
Źródło: GroMar, licencja: CC BY 3.0.

Realizacja przedstawionych zapytań SQL dla trzech tabel bazy Przewodnik multimedialny zwróci wyniki w postaci wyszukanych rekordów w trzech oddzielnych widokach.

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:

Linia 1. SELECT table podkreślnik name kropka column1 przecinek table podkreślnik name kropka column2 przecinek kropka kropka kropka table podkreślnik name kropka columnN. Linia 2. FROM table podkreślnik name. Linia 3. WHERE otwórz nawias kwadratowy condition zamknij nawias kwadratowy.

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 wykorzystywany w adresach internetowych.

Postać zapytania umożliwiającego wybór Krakowa z tabeli miasta będzie miał następującą postać:

Linia 1. SELECT Miasta kropka Nazwa podkreślnik miasta przecinek Miasta kropka Wojewodztwo przecinek Miasta kropka ID podkreślnik miasta. Linia 2. FROM Miasta. Linia 3. WHERE Miasta kropka ID podkreślnik miasta znak równości 6 średnik.

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

Linia 1. SELECT Miasta kropka Nazwa podkreślnik miasta przecinek. Linia 2. Miasta kropka Wojewodztwo przecinek. Linia 3. Miasta kropka ID podkreślnik miasta przecinek. Linia 4. Dane podkreślnik demograficzne kropka Liczba podkreślnik mieszkancow przecinek. Linia 5. Dane podkreślnik demograficzne kropka Liczba podkreślnik kobiet przecinek. Linia 6. Dane podkreślnik demograficzne kropka Grupa podkreślnik wiekowa przecinek. Linia 7. Dane podkreślnik demograficzne kropka Data podkreślnik aktualizacji podkreślnik danych przecinek. Linia 8. Dane podkreślnik demograficzne kropka ID podkreślnik miasta przecinek. Linia 9. Powierzchnie podkreślnik miejskie kropka Powierzchnia podkreślnik miasta przecinek. Linia 10. Powierzchnie podkreślnik miejskie kropka Powierzchnia podkreślnik terenow podkreślnik zielonych przecinek. Linia 11. Powierzchnie podkreślnik miejskie kropka Data podkreślnik aktualizacji podkreślnik danych przecinek. Linia 12. Powierzchnie podkreślnik miejskie kropka ID podkreślnik miasta. Linia 13. FROM Miasta przecinek Dane podkreślnik demograficzne przecinek Powierzchnie podkreślnik miejskie. Linia 14. WHERE Miasta kropka ID podkreślnik miasta znak równości 6 AND. Linia 15. Dane podkreślnik demograficzne kropka ID podkreślnik miasta znak równości 6 AND. Linia 16. Powierzchnie podkreślnik miejskie kropka ID podkreślnik miasta znak równości 6 średnik.

Wynikiem działania takiego zapytania jest widok zawierający scalone dane z trzech tabel.

R16NNcR9tq1bb
Zrzut ekranu programu SQLiteStudio prezentujący zapytanie do bazy danych.
Źródło: GroMar, 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. Wszystkie te trzy pola w tym widoku nie są przydatne dla użytkownika, ponieważ informacja z trzech tabel została już scalona. Można usunąć dwa niepotrzebne ID_miasta z klauzuli SELECT. Ostateczna postać zapytania będzie następująca:

Linia 1. SELECT DISTINCT Miasta kropka Nazwa podkreślnik miasta przecinek. Linia 2. Miasta kropka Wojewodztwo przecinek. Linia 3. Dane podkreślnik demograficzne kropka Liczba podkreślnik mieszkancow przecinek. Linia 4. Dane podkreślnik demograficzne kropka Liczba podkreślnik kobiet przecinek. Linia 5. Dane podkreślnik demograficzne kropka Grupa podkreślnik wiekowa przecinek. Linia 6. Dane podkreślnik demograficzne kropka Data podkreślnik aktualizacji podkreślnik danych przecinek. Linia 7. Powierzchnie podkreślnik miejskie kropka Powierzchnia podkreślnik miasta przecinek. Linia 8. Powierzchnie podkreślnik miejskie kropka Powierzchnia podkreślnik terenow podkreślnik zielonych przecinek. Linia 9. Powierzchnie podkreślnik miejskie kropka Data podkreślnik aktualizacji podkreślnik danych przecinek. Linia 10. Powierzchnie podkreślnik miejskie kropka ID podkreślnik miasta. Linia 11. FROM Miasta przecinek Dane podkreślnik demograficzne przecinek Powierzchnie podkreślnik miejskie. Linia 12. WHERE Miasta kropka ID podkreślnik miasta znak równości 6 AND. Linia 13. Dane podkreślnik demograficzne kropka ID podkreślnik miasta znak równości 6 AND. Linia 14. Powierzchnie podkreślnik miejskie kropka ID podkreślnik miasta znak równości 6 średnik.
RYtZa35P8K3Oq
Zrzut ekranu programu SQLiteStudio prezentujący zapytanie do bazy danych.
Źródło: GroMar, licencja: CC BY 3.0.

Wykorzystaj poniższy dzienniczek do zapisania swoich notatek lub przemyśleń.

RIOmij4lkmWdM
Źródło: GroMar, licencja: CC BY 3.0.