Ćwiczenia przeprowadzimy na przykładzie bazy danych dziennika szkolnego. Baza danych pochodzi z zadania maturalnego.

Zadanie zostało opracowane przez Centralną Komisję Egzaminacyjną i pojawiło się na egzaminie maturalnym z informatyki w maju 2010 r. (poziom rozszerzony, cz. II). Cały arkusz można znaleźć na stronie internetowej CKE.

Niezbędne do zrealizowania kwerend pliki bazodanowe (w zależności od użytkowanego DBMSDBMSDBMS):

  • Szkola.accdb dla pakietu MS Access,

  • Szkola.odb dla oprogramowania LibreOffice Base.

można pobrać tutaj:

R1Ij83eg9I5Ba

Plik ZIP zawierający przykładową bazę danych.

Plik ZIP o rozmiarze 1.16 MB w języku polskim

Sposób importu rekordów z plików tekstowych, udostępnionych w zadaniu maturalnym, do bazy danych w wybranym formacie oraz dokładny opis modelu danych wraz z jego ograniczeniami przedstawiono w e‑materiale:

W bazie danych powinny znajdować się zaimportowane tabele:

  • Uczniowie – z atrybutami opisującymi uczniów szkoły: IDucznia, nazwisko, imie, ulica, dom, IDklasy;

  • Oceny – z danymi na temat uzyskanych ocen: IDoceny, IDucznia, Ocena, Data, IDprzedmiotu;

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

Ważne!

Warto pamiętać o analizie modelu danych, której dokonaliśmy w e‑materiale:

Wskazaliśmy wówczas wiele niedoskonałości modelu danych, który zastosowano w podanym zadaniu maturalnym. Rozważmy np. często występującą sytuację, gdy w szkole zatrudnieni zostaliby dwaj nauczyciele, prowadzący ten sam przedmiot. Wówczas tabela Przedmioty traci spójność, gdyż aktualnie pozwala przypisać tylko jednego nauczyciela do jednego przedmiotu. To pokazuje, jak błędy popełnione w fazie projektowania logicznego modelu (brak wydzielenia nauczycieli do osobnej tabeli) utrudniają lub nawet uniemożliwiają późniejszą pracę z danymi.

Oprócz tabel baza danych musi posiadać zdefiniowane powiązania logiczne (relacje), występujące pomiędzy kluczami podstawowymi i obcymi, co również omówiono szczegółowo w I etapie tej serii e‑materiałów.

Zadanie 1

Znajdź wszystkich uczniów klasy 2c, których nazwisko rozpoczyna się na literę B. Wyniki kwerendy, złożone kolejno z: IDklasy, nazwiska i imienia ucznia, posortuj alfabetycznie wg znalezionych nazwisk.

Aby znaleźć nazwiska na literę B, trzeba posłużyć się operatorem służącym do wyszukiwania zadanej frazy (wzorca) w wartości tekstowej pola. W tym celu posłużymy się operatorem LIKE, który oznacza: podobne, takie jak.

Poszukiwany wzorzec może znajdować się zarówno na początku tekstu, jak i wewnątrz lub na końcu łańcucha znaków. Poznajmy teraz podstawowe zapisy stosowane w poszukiwaniu zgodności ze wzorcem:

Zapis wzorca

Zastosowanie

Przykład użycia

Dopasowanie

Brak zgodności

*

dowolny ciąg znaków rozpoczynający się od wybranego znaku

a*

abc, aaBC, a12

bac, 1a2, 12a

ciąg znaków zawierający daną frazę

*ab*

aba, 1aBc, 1ab

a1b, ba1

dowolny ciąg znaków kończący się na wybranym znaku

*a

baa, a2A

aBC, ba1

?

dokładnie jeden znak

a?b

aab, a1b, aCb

accb, ac1b

#

jedna cyfra

a#b

a1b, a2b

a33b, acb, aCb

[*]

znak specjalny

a[*]b

a*b

acb, a1b

[a‑z]

zakres znaków

[a‑z]

k, m

1, $

[!a‑z]

spoza zakresu znaków

[!a‑z]

5, &

c, r

[0‑9]

cyfra

[0‑9]

5, 9

g, #

[!0‑9]

nie cyfra

[!0‑9]

f, %

4, 6

W przypadku naszych poszukiwań nazwisk rozpoczynających się na literę B, powinniśmy posłużyć się wzorcem: B*.

Ciekawostka

W języku SQL, który służy do komunikacji z bazami danych, zamiast znaku *, zastosujemy wewnątrz klauzuli LIKE operator %, natomiast zamiast znaku ?, zapiszemy znak podkreślenia: _

W kwerendzie z naszego zadania pozostało jeszcze zdefiniowanie kryterium dla pola IDklasy – w poleceniu chodzi przecież tylko o uczniów klasy 2c. Zobaczmy przykładowe rozwiązania.

Przykładowe rozwiązanie w MS Access

Zwróćmy uwagę na zastosowanie nowej klauzuli Like (czerwona ramka), a także na uporządkowanie wyników kwerendy w kolejności rosnącej według zwróconych nazwisk:

RWDLMHETbITuC
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Przykładowe rozwiązanie w LibreOffice Base

W zależności od używanego programu stosujemy nieco inne wartości kryterium dla pola nazwisko:

  • MS Access: Like "B*" – tylko pierwsza litera klauzuli Like zapisana jest wielką literą, wartość wzorca tekstowego definiujemy w cudzysłowie.

Ciekawostka

Operator LIKE, domyślnie zdefiniowany w języku SQL, nie rozróżnia wielkości liter podczas poszukiwania wystąpień wzorca w tekście. A zatem nazwiska rozpoczynające się na literkę B można wyszukać także w taki sposób: LIKE "b*".

Natomiast w ogólnym przypadku, w zależności od użytkowanego systemu DBMSDBMSDBMS, ustawiony dla kolumny sposób kodowania znaków moźe wpłynać na uwzględnianie wielkości znaków, podczas poszukiwań wzorca tekstowego. W SQL do tego celu służy specjalna klauzula COLLATE.

  • LibreOffice Base: LIKE 'B*' – całą klauzulę LIKE zapisujemy wielkimi literami, a wzorzec tekstowy definiujemy w apostrofach.

Ważne!

Warto wiedzieć, że bez względu na to, jakiej wielkości liter użyjemy do zapisania klauzuli Like, zarówno Microsoft Access, jak i LibreOffice Base, zwrócą poprawne wyniki.

R1KwMsHSomrCs
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Poprawne wyniki kwerendy

Rekordy uporządkowano alfabetycznie według nazwisk:

Rl5EPWmnqFG6J
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.
  • Zwróconych rekordów: 4

Wersja parametryczna zapytania w MS Access

Załóżmy, że chcemy wyszukać uczniów, których nazwisko rozpoczyna się na literę B, ale uczęszczających do klasy o nazwie wprowadzonej z klawiatury:

RNJEaPXNT8jGB1
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Wersja parametryczna zapytania w LibreOffice Base

Zamiast używać nawiasów kwadratowych [ ], znanych z MS Access, zastosowano tu zapis z operatorem dwukropka i nazwą atrybutu o wartości wprowadzanej z klawiatury:

Rnc73Km2eiW26
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Wnioski płynące z zadania

  • Operator LIKE umożliwia wyszukiwanie zadanego wzorca w tekstowych wartościach atrybutów.

  • Podstawowymi operatorami towarzyszącymi użyciu LIKE są przede wszystkim zapisy: znak *, zastępujący dowolną liczbę znaków oraz symbol ?, oznaczający dokładnie jeden znak. Pozostałe użyteczne operatory przedstawione zostały w tabeli.

  • W języku SQL znak * wewnątrz klauzuli LIKE zastąpimy symbolem %, zamiast ? zapiszemy: _ (znak podkreślenia).

  • Tekstową wartość wzorca zapiszemy w MS Access w cudzysłowie, natomiast w LibreOffice Base w apostrofach.

  • Pakiet MS Access preferuje zapis: Like, zaś w LibreOffice Base operator zapisuje się wielkimi literami: LIKE.

Zadanie 2

Utwórz kwerendę, która zwróci łączną liczbę ocen 5, umieszczonych w bazie danych we wszystkich klasach z przedmiotu chemia.

W zapytaniu tym wyznaczymy liczbę rekordów zwróconych przez kwerendę wyszukującą jedynie wskazane w treści zadania oceny.

Zarówno pakiet MS Access, jak i LibreOffice Base oferują możliwość wyznaczenia kilku elementarnych właściwości matematycznych (średnia, suma, liczba zwróconych rekordów, wartość minimalna lub maksymalna), dzięki zastosowaniu tzw. funkcji agregujących dane:

Funkcja agregująca

Zastosowanie

Policz/Liczba

wyznaczenie liczby rekordów zwracanych przez kwerendę

Suma

sumowanie wartości wybranego atrybutu we wszystkich zwróconych rekordach

Średnia

obliczanie średniej wartości wybranego atrybutu ze wszystkich wartości w zwróconych rekordach

Maksimum

znalezienie największej wartości atrybutu we wszystkich zwróconych rekordach

Minimum

znalezienie najmniejszej wartości atrybutu we wszystkich zwróconych rekordach

Odchylenie standardowe

obliczenie szerokości rozproszenia wartości od wartości średniej we wszystkich zwróconych rekordach

Wariancja

obliczenie statystycznej wariancji wszystkich wartości w zwróconych rekordach

Wspomniane w tabeli funkcje znajdują największe zastosowanie podczas procesu tzw. grupowania danych – więcej informacji na ten temat znajdziesz w e‑materiale: Kwerendy modyfikujące, etap IVPxhH71mMLKwerendy modyfikujące, etap IV.

Nawet bez użycia grupowania funkcje agregujące mogą nam posłużyć do znalezienia odpowiedzi na wiele pytań, pod warunkiem zastosowania kwerendy pomocniczej. Algorytm złożony z dwóch kroków przedstawia się następująco:

  1. Kwerenda pomocnicza wyjmuje z całego zbioru tylko te rekordy, które spełniają wszystkie kryteria.

  2. Wyniki zwrócone przez kwerendę pomocniczą stanowią zbiór danych, na rzecz których wywołamy funkcję agregującą.

Przedstawmy zatem wykorzystanie kwerendy pomocniczej do wyznaczenia liczby wszystkich ocen 5 z przedmiotu chemia, zapisanych w bazie danych.

Przykładowe rozwiązanie w MS Access

Rozpoczynamy od wyjęcia z bazy danych tylko ocen 5 z przedmiotu chemia – to jest właśnie nasza kwerenda pomocnicza:

ROfMGaNtoYS5X
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.
Ważne!

Zwróćmy uwagę, że użycie operatora LIKE tym razem okazuje się zbędne, gdyż dysponujemy wartością kryterium – szukamy pełnej nazwy przedmiotu: "chemia", a nie jedynie fragmentu dłuższego łańcucha.

Następnie, w widoku projektu nowego zapytania przechodzimy do zakładki Kwerendy, po czym dodajemy do projektu wyniki kwerendy pomocniczej:

RVYSHKUW0ZUcL1
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Dostęp do użycia funkcji agregujących w projekcie kwerendy uzyskamy w MS Access, w zakładce Projektowanie:

R188lxT0yD0Cd1
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Wciśnięcie wskazanego przycisku spowoduje pojawienie się w projekcie kwerendy wiersza Suma wraz z listą wyboru funkcji możliwych do użycia w zapytaniu. W pakiecie LibreOffice Base zawsze możemy wskazać funkcję agregującą, dzięki stale obecnemu wierszowi o nazwie Funkcja.

Przykładowe rozwiązanie w LibreOffice Base

Kwerenda pomocnicza – wyszukujemy tylko oceny 5 z przedmiotu chemia:

R1aH5MI3nIpnY
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Wyznaczamy liczbę ocen 5 z wykorzystaniem funkcji Liczba, która jest odpowiednikiem funkcji Policz, znanej z MS Access:

RF1qveLVpp2XG
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.
Ciekawostka

Odpowiednikiem funkcji Policz, znanej z programu MS Access oraz funkcji Liczba z pakietu LibreOffice Base jest – w języku SQL – funkcja o nazwie: COUNT.

Zwróćmy uwagę, że w projekcie zapytania – zamiast tabel – również użyto wyników zwróconych przez kwerendę pomocniczą.

Poprawne wyniki kwerendy

Zwrócona przez kwerendę pomocniczą liczba ocen 5 z przedmiotu chemia to łącznie 245 rekordów.

RQDGT4LuZoT1T
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Wnioski płynące z zadania

  • Dzięki użyciu funkcji agregujących dane możliwe staje się wyznaczenie wartości statystycznej atrybutu, np. średniej.

  • Możemy skorzystać z funkcji wyznaczających: liczbę zwróconych przez zapytanie rekordów, średnią, sumę, odchylenie standardowe lub wariancję oraz najmniejszą, bądź największą wartość danego atrybutu.

  • Funkcja agregująca zliczająca liczbę zwróconych przez kwerendę pomocniczą rekordów nosi nazwę: PoliczMS Access, Liczba lub Ilosc (w zależności od wersji) w LibreOffice Base oraz COUNT, w języku SQL.

  • W programie MS Access użycie funkcji w projekcie kwerendy jest możliwe po wciśnięciu przycisku Suma, w zakładce Projektowanie.

Zadanie 3

Wyznacz średnią wszystkich zapisanych w bazie danych ocen, które wystawiła nauczycielka Alina Bednarek uczniom klasy 1e. Wynik zaokrąglij do czterech miejsc po przecinku.

W kryteriach projektu kwerendy pomocniczej użyjemy imienia i nazwiska nauczycielki. Ponadto w wynikach uwzględnimy wartość wystawionej oceny, jak również kryterium nazwy klasy, gdyż interesują nas tylko oceny wystawione uczniom klasy 1e.

Przykładowe rozwiązanie w MS Access

RpnNtPadOBYDk
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Dysponując określonym zestawem ocen, spełniającym wszystkie kryteria, pora wyznaczyć wartość średnią oceny.

Podobnie jak w poprzednim przykładzie, w widoku projektu wynikowej kwerendy używamy nie tabel, lecz wyników kwerendy pomocniczej:

RFLvqmw855ZLM1
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Jak widać: na liście wyboru w wierszu Suma wskazano funkcję Średnia. Przypomnijmy także, że wiersz Suma staje się widoczny dopiero po kliknięciu przycisku na wstążce Projektowanie:

RFYfSI6gvEOJI1
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Ostatnim zadaniem do zrealizowania w poleceniu jest zaokrąglenie wartości średniej do czterech miejsc po przecinku. W tym celu w widoku projektu – po kliknięciu pola Ocena –włączamy dla tego atrybutu Arkusz właściwości:

R1Nwi9NF6Svcn1
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Dzięki temu w dodatkowym panelu uzyskujemy dostęp do atrybutów Format oraz Miejsca dziesiętne:

RVUS0PULIuP5w
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Co ważne – określenie jedynie liczby miejsc dziesiętnych nie spowoduje zaokrąglenia wartości w widoku arkusza danych! Potrzebne jest dodatkowo określenie formatu liczby.

W naszym przykładzie możemy zdecydować się na format stałoprzecinkowy lub standardowy.

Poprawne wyniki kwerendy

Widok arkusza danych potwierdza, że wartość średniej rzeczywiście została zaokrąglona do czterech miejsc dziesiętnych:

Ra2R4Nnszgw0l
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Przykładowe rozwiązanie w LibreOffice Base

Rozpoczynamy od stworzenia kwerendy pomocniczej, która uwzględnia wszystkie kryteria z polecenia oraz zwraca wartość samej wystawionej oceny:

R1J5FVpTYTIbv
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Następnie w projekcie wynikowej kwerendy, zamiast tabel bazy danych, używamy wyników zapytania pomocniczego:

R1I8R74wwSBSD1
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Dodatkowo, aby można było wartość średnią zaokrąglić, używamy funkcji CAST, której zadaniem jest tzw. rzutowanie (konwersja, zamiana) typu zmiennej.

Decydujemy się na typ DOUBLE, który obsługuje liczby zmiennoprzecinkowe. Słówko AS to klauzula oznaczająca: traktuj jako.

A zatem kompletny zapis:

Linia 1. CAST otwórz nawias okrągły cudzysłów Ocena cudzysłów AS DOUBLE zamknij nawias okrągły.

przeczytamy w następujący sposób:

  • Wartość pola Ocena traktuj jako liczbę zmiennoprzecinkową typu DOUBLE.

To jednak nie zagwarantuje nam zaokrąglenia wartości do czterech miejsc po przecinku. W pakiecie LibreOffice Base dokonamy tego nie w widoku projektu, lecz w widoku danych, czyli w wynikach kwerendy.

Klikamy prawym przyciskiem myszy na kolumnie z uzyskaną średnią, wybierając opcję Formatowanie kolumn:

R143InExDn5pN
Źródło: Contentplus.pl sp. z o.o., licencja: CC BY-SA 3.0.

Przedstawiona konfiguracja zapewni nam zaokrąglenie wartości średniej do czterech miejsc po przecinku.

Wyniki kwerendy będą oczywiście identyczne jak te uzyskane w MS Access.

Wnioski płynące z zadania

  • Systemy bazodanowe wymagają określenia odpowiedniego sposobu formatowania wartości pola stworzonego w wyniku działania funkcji agregującej – jest to szczególnie istotne w przypadku liczb zmiennoprzecinkowych.

  • Zaokrąglenie wartości zmiennoprzecinkowej w MS Access jest możliwe dzięki arkuszowi właściwości pola. Nie można jednak zapomnieć o wskazaniu formatu wartości pola – sama liczba miejsc po przecinku nie wystarczy do skutecznego zaokrąglenia.

  • Pakiet LibreOffice Base używa funkcji CAST do rzutowania (konwersji) typu pola stworzonego w wyniku działania funkcji agregującej. Zaokrąglenie tej wartości zmiennoprzecinkowej staje się możliwe dopiero w widoku danych.

Słownik

DBMS
DBMS

(ang. Database Management System) lub w wersji polskiej: SZBD (System Zarządzania Bazą Danych) – oprogramowanie, które obsługuje wszystkie działania administracyjne związane z bazami danych i odpowiada za realizację podstawowych funkcji: zarządzanie użytkownikami, ochronę integralności danych, zapewnienie możliwości wykonywania na zgromadzonych informacjach kwerend, dbanie o bezpieczeństwo zbioru informacji; przykładowe systemy DBMS to m.in.: MySQL, MariaDB, FireBird, PostgreSQL, Microsoft SQL Server, SQLite, Oracle Database, Microsoft Access, LibreOffice Base

kwerenda
kwerenda

(ang. query – zapytanie); zapisane z użyciem języka SQL polecenie skierowane do systemu bazodanowego; jego celem może być: pobranie, dodanie, modyfikacja lub usunięcie danych, a także zmiana sposobu ich przechowywania w systemie