Z biblioteki podręczników wypożyczanych na cały rok akademicki mogą korzystać studenci z miasteczka akademickiego i spoza miasteczka. Każdy student może wypożyczyć wiele książek. W miasteczku studenci mieszkają w kilkuosobowych pokojach.
Dane są trzy pliki: studenci.txt, meldunek.txt oraz wypozyczenia.txt, w których zapisano informacje o książkach aktualnie wypożyczonych przez studentów. Każdy plik ma wiersz nagłówkowy. Dane rozdzielono znakami tabulacji. Wszystkie dane tekstowe w plikach mają długość nieprzekraczającą 60 znaków.
Archiwum ZIP z plikami niezbędnymi do wykonania zadania można pobrać poniżej:
Zadanie zostało opracowane przez Centralną Komisję Egzaminacyjną i pojawiło się na egzaminie maturalnym z informatyki w maju 2016 r. (poziom rozszerzony, część II). Cały arkusz można znaleźć na stronie internetowej CKE.
W pliku studenci.txt zapisano dane studentów – informacje na temat każdego studenta znajdują się w osobnym wierszu. Imiona i nazwiska mogą się powtarzać. W każdym wierszu zapisano numer PESEL, nazwisko, imię.
Fragment pliku studenci.txt:
Linia 1. pesel nazwisko imie.
Linia 2. 92051048757 BAJOREK JAKUB.
Linia 3. 92051861424 SLOTARZ MARIANNA.
pesel nazwisko imie
92051048757 BAJOREK JAKUB
92051861424 SLOTARZ MARIANNA
Plik meldunek.txt zawiera przypisania studentów z miasteczka akademickiego do wynajętych pokoi. W każdym wierszu znajdują się: numer PESEL studenta oraz identyfikator pokoju.
Fragment pliku meldunek.txt:
Linia 1. pesel id podkreślnik pok.
Linia 2. 92051048757 8.
Linia 3. 92051861424 32.
pesel id_pok
92051048757 8
92051861424 32
Plik wypozyczenia.txt zawiera informacje o wypożyczonych podręcznikach. W każdym wierszu zapisano liczbę porządkową wypożyczenia, numer PESEL wypożyczającego oraz tytuł wypożyczonej książki.
Fragment pliku wypozyczenia.txt:
Linia 1. lp pesel tytul.
Linia 2. 1 92061083359 FIZYKA TECHNICZNA I.
Linia 3. 2 94103033254 PROGRAMOWANIE MIKROKONTROLEROW I.
lp pesel tytul
1 92061083359 FIZYKA TECHNICZNA I
2 94103033254 PROGRAMOWANIE MIKROKONTROLEROW I
Wykorzystując dane znajdujące się w plikach i dostępne narzędzia informatyczne, wykonaj polecenia przedstawione dalej. Odpowiedzi zapisz w kolejnych wierszach nowego pliku tekstowego. Odpowiedź do każdego polecenia poprzedź jego numerem.
Rozwiązanie zadania
Przedstawimy rozwiązanie wszystkich części zadania. Zanim przystąpimy do pracy, przygotujemy w programie Microsoft Access nowy plik bazy danych, noszący np. nazwę biblioteka.accdb.
Jeżeli używasz narzędzia LibreOffice Base, to przygotuj nowy plik biblioteka.odb.
Import danych z plików tekstowych i relacje
Najpierw przeniesiemy dane udostępnione w postaci plików tekstowych do tabel systemu bazodanowego. Sprawdzimy przy tym typy danych oraz ustanowimy odpowiednie klucze podstawoweklucz podstawowyklucze podstawowe, a także relacjerelacjarelacje (powiązania) pomiędzy atrybutami tabel:
R1VJ0mt4SLLGm
Film przedstawia sposób importu blików do bazy danych.
Film przedstawia sposób importu blików do bazy danych.
Film przedstawia sposób importu blików do bazy danych.
Ć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 na przycisk poniżej:
Importu tabeli Studenci należy dokonać poprzez otwarcie pliku tekstowego studenci.txt w narzędziu LibreOffice Calc, nie zmieniając domyślnych opcji importu pliku tekstowego. Upewnij się jedynie, iż zaznaczono w opcjach wyboru separatora pole przy tabulatorze:
Rjn1lF4K4PykM
Ilustracja przedstawiająca okno Importuj tekst – [studenci.txt]. W oknie dwie sekcje: Importuj oraz Opcje Separatora. W sekcji Importuj pola z właściwościami: Zestaw znaków – Unicode (UTF‑8), Język – Domyślny - Polski, Od wiersza – 1. W sekcji Opcje separatora spośród radialnych pól Stała szerokość oraz Rozdzielony wybrano drugą opcję. Poniżej pola wyboru z separatorami. Zaznaczono i wyróżniono czerwonym obramowaniem opcję Tabulatory.
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:
R1Zc5zYZNX05f
Zrzut ekranu narzędzia LibreOffice Calc. Z górnego menu wybrano opcję Edycja. Następnie z listy Zaznacz i Zaznacz obszar danych.
Pora skopiować zaznaczony obszar danych kombinacją klawiszy Ctrl + C, po czym przejść już do narzędzia LibreOffice Base i upewnić się, że w oknie Baza danych aktywną zakładką jest Tabele:
RvnuvRL6Hdeuy
Zrzut ekranu z programu LibreOffice Base. Po lewej stronie kolumna Baza danych, w której znajdują się ikony i nazwy: Tabele, Kwerendy, Formularze oraz Raporty. Zaznaczone są Tabele. Po prawej obszar Zadania, a w nim polecenia: Utwórz projekt tabeli…; Użyj kreatora, aby utworzyć tabelę…; Utwórz widok…
Teraz możemy już wkleić informacje ze schowka z użyciem kombinacji klawiszy Ctrl + V. Spowoduje to otwarcie kreatora Kopiuj tabelę. Poprawną konfigurację pokazaną poniżej zatwierdzamy przyciskiem Dalej, po czym przenosimy wszystkie skopiowane kolumny do nowo tworzonej tabeli:
R1S32HSWCWp8z
Ilustracja przedstawiająca dwa okna. Na górze okno Kopiuj tabelę. Na początku okna pole Nazwa tabeli z wartością Studenci. Poniżej sekcja Opcje, a w niej opcje: Definicje i dane, Definicja, Jako widok tabeli, Dołącz dane. Zaznaczono Definicje i dane. Dalej pola wyboru: Użyj pierwszego wiersza jako nazw kolumn oraz Utwórz nowe pole jako klucz główny. Zaznaczono pierwszą opcję. Dalej nieaktywne pole Nazwa. Na końcu tekst: Istniejące pola danych mogą być ustawione jako klucz główny na etapie formatowania typu kreatora (trzecia strona). Na dole okna przyciski: Pomoc, Wstecz, Dalej, Utwórz oraz Anuluj. Przycisk Dalej w czerwonej ramce.
Na dole okno Zastosuj kolumny. W sekcji Istniejące kolumny po lewej pusty obszar, a po prawej obszar z wierszami: pesel, nazwisko oraz imie. Pomiędzy obszarami przyciski z ikonami: strzałka w prawo, strzałka w prawo i pionowa linia, strzałka w lewo, pionowa linia i strzałka w lewo. Czerwonym obramowaniem wyróżniono drugi przycisk strzałka w prawo i pionowa linia. Na dole okna przyciski: Pomoc, Wstecz, Dalej, Utwórz oraz Anuluj.
W trzecim kroku kreatora upewniamy się co do typu wartości każdej kolumny – odpowiednio:
pesel – typ danych Tekst [ VARCHAR ], czyli podobnie jak w MS Access tę wartość możemy potraktować jako tekst - decydujemy się na taką opcję. Co prawda w LibreOffice Base występuje typ danych BigInt [ BIGINT ], który zmieści jedenastocyfrowe liczby, jednak nie uchroni nas to przed ewentualnym zerem z lewej strony mogącym wystąpić w liczbie - stąd decyzja o użyciu typu tekstowego,
nazwisko – typ danych Tekst [ VARCHAR ],
imie – typ danych Tekst [ VARCHAR ].
Nie należy jednak zapomnieć o ustanowieniu w tabeli klucza podstawowego – klikając prawym przyciskiem myszy na atrybucie pesel zaznaczamy opcję Klucz główny:
R1UN2Fl20ateh
Ilustracja przedstawiająca okno Formatowanie typu. Po lewej stronie obszar z wierszami: pesel, nazwisko oraz imie. Przy pesel, w czerwonym obramowaniu zaznaczona opcja Klucz główny. Po prawej stronie okna sekcja Informacje o kolumnach, w której znajdują się informacje takie jak nazwa pola – pesel, typ pola – Tekst [ VARCHAR ] oraz długość – 255. Poniżej sekcja Automatyczne rozpoznawanie typu, a w nim pole Wiersze (maks.): 10 z przyciskiem Automatyczne. Na dole okna przyciski: Pomoc, Wstecz, Dalej, Utwórz oraz Anuluj.
Jak widać, spowodowało to dodanie małej ikonki klucza przy wskazanym atrybucie. Możemy zakończyć import tabeli klikając na przycisk Utwórz.
Analogicznie postępujemy dla dwóch pozostałych tabel, importując dane używając 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 zgodnie z opisem kolumn w treści zadania.
Warto jeszcze zauważyć, iż tabela Meldunek nie posiada atrybutu, który mógłby stać się kluczem głównym – w związku z tym pozwalamy kreatorowi dodać własny klucz podstawowy, o nazwie np. id_meldunku:
R1XBqjzSM7ca9
Ilustracja przedstawiająca okno Kopiuj tabelę. Na początku okna pole Nazwa tabeli z wartością Meldunek. Poniżej sekcja Opcje, a w niej opcje: Definicje i dane, Definicja, Jako widok tabeli, Dołącz dane. Zaznaczono Definicje i dane. Dalej pola wyboru: Użyj pierwszego wiersza jako nazw kolumn oraz Utwórz nowe pole jako klucz główny. Zaznaczono obie opcje. Dalej wyróżnione czerwonym obramowaniem pole Nazwa z wpisaną zawartością: id_meldunku. Na końcu tekst: Istniejące pola danych mogą być ustawione jako klucz główny na etapie formatowania typu kreatora (trzecia strona). Na dole okna przyciski: Pomoc, Wstecz, Dalej, Utwórz oraz 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 następujące powiązania pomiędzy kluczami podstawowymi i obcymi:
RG5GcGEy1TG9y
Zrzut ekranu z programu LibreOffice Base przedstawiający relacje między tabelami: Meldunek, Studenci oraz Wypozyczenia. W tabeli Meldunek pola: id_meldunku, pesel, id_pok. W tabeli Studenci pola: pesel, nazwisko, imie. W tabeli Wypozyczalnia pola: lp, pesel, tytul. Tabele Meldunek oraz Studenci połączono na wysokości pól pesel w relacji n do 1. Tabele Studenci oraz Wypożyczalnia połączono na wysokości pól pesel w relacji 1 do n.
Zwróćmy uwagę, iż LibreOffice Base domyślnie nie pozwoli na stworzenie powiązania pomiędzy atrybutami pesel w tabelach Meldunek oraz Wypozyczenia – to dlatego, iż program wymaga istnienia przynajmniej jednego atrybutu unikalnego do zdefiniowania relacji.
Zamiast zmieniać ten stan rzeczy własnym, dodatkowym zapytaniem SQL o składni ALTER TABLE ... ADD UNIQUE ... (co może się wydawać trudne) poradzimy sobie w poleceniu piątym w inny sposób, relację tworząc z użyciem dodatkowej, pośredniczącej kwerendy.
Polecenie 1
Podaj imię i nazwisko osoby, która wypożyczyła najwięcej podręczników. Wypisz tytuły wszystkich książek przez nią wypożyczonych.
Aby utworzyć nową kwerendę w LibreOffice Base należy wybrać z sekcji Baza danych zakładkę o nazwie Kwerendy, po czym wybrać opcję o nazwie Utwórz projekt kwerendy:
RCu4ZRdmihBqX
Zrzut ekranu z programu LibreOffice Base. Po lewej kolumna Baza danych, a w niej typy baz danych. Wybrano Kwerendy. Po prawej sekcja Zadania, a w niej polecenia: Utwórz projekt kwerendy…; Użyj kreatora, aby utworzyć kwerendę… oraz Utwórz kwerendę SQL…. Wybrano pierwsze polecenie: Utwórz projekt kwerendy…
Projekt kwerendy 1a wyszukującej imię i nazwisko osoby, która wypożyczyła najwięcej podręczników – dodatkowo ograniczono liczbę zwróconych przez zapytanie rekordów do jednego (patrz czerwona ramka poniżej):
RzVo8Cdk6fcLc
Zrzut ekranu z programu LibreOffice Base przedstawiający relacje między tabelami: Studenci oraz Wypozyczenia. W tabeli Studenci pola: pesel, nazwisko, imie. W tabeli Wypozyczalnia pola: lp, pesel, tytul. Tabele Studenci oraz Wypozyczenia połączono na wysokości pól pesel. Na górze, w pasku z opcjami czerwonym obramowaniem wyróżniono opcję filtrowana, gdzie w dostępne pole wpisano 1. Na dole zrzutu ekranu tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. Dane wierszy uzupełniono w dwóch komórkach. Pole: pesel, lp. Alias: pusta komórka, liczba_wyp. Tabela: Studenci, Wypozyczalnia. Sortowanie: pusta komórka, malejąco. Widoczny: w obu komórkach zaznaczono haczyk. Funkcja: Grupuj, Liczba. Pozostałe wiersze puste.
Projekt kwerendy 1b wypisującej tytuły wszystkich książek wypożyczonych przez tę osobę:
RFIGoi6AXi7Z4
Zrzut ekranu z programu LibreOffice Base przedstawiający relacje między tabelami: Wypozyczenia oraz Studenci. W tabeli Wypozyczenia pola: lp, pesel, tytul. W tabeli Studenci pola: pesel, nazwisko, imie. Tabele te połączono na wysokości pól pesel. Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. Uzupełniono niektóre wiersze. Wiersz Pole: pesel, tytul, imie, nazwisko. Wiersz Tabela: Wypozyczenia, Wypozyczenia, Studenci, Studenci. Wiersz Widoczny: we wszystkich komórkach zaznaczono haczyk. Wiersz Kryterium: w pierwszej komórce ‘97021392858’.
Poprawna odpowiedź
Imię i nazwisko:
KRZYSZTOF LEWANDOWSKI
Tytuły wypożyczonych książek:
TEORIA GRAFOW
JEZYKI PROGRAMOWANIA II
METODY NUMERYCZNE II
FLASH I PHP
Schemat oceniania
2 punkty – za poprawne podanie imienia i nazwiska studenta oraz wszystkich tytułów książek,
1 punkt – za poprawne podanie imienia i nazwiska lub wszystkich tytułów książek,
0 punktów – za odpowiedź niepełną lub błędną albo za brak odpowiedzi.
Polecenie 2
Podaj średnią liczbę osób zameldowanych w jednym pokoju. Wynik zaokrąglij do 4 miejsc po przecinku.
Projekt kwerendy o nazwie 2a obliczającej liczbę osób zameldowanych w każdym z pokojów – zwróćmy uwagę na alias liczba_studentow nowego atrybutu powstałego wskutek wywołania funkcji agregującej:
R13up2NYyVeus
Zrzut ekranu z programu LibreOffice Base przedstawiający tabelę Meldunek i jej pola: id_meldunku, pesel, id_pok. Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. Uzupełniono niektóre wiersze. Wiersz Pole: id_pok, pesel. Wiersz Alias w drugiej komórce liczba_studentow. Wiersz Tabela: Meldunek, Meldunek. Wiersz Widoczny: w dwóch komórkach zaznaczono haczyk. Wiersz Funkcja: Grupuj, Liczba.
Teraz pora na projekt kwerendy 2b wypisującej średnią liczbę osób zameldowanych w jednym pokoju. Zamiast korzystać z tabel bazy danych, tym razem dodajemy na płótno projektu wyniki zapytania 2a. Nowy atrybut również otrzymał alias, tym razem o nazwie srednio_w_pokoju:
R1GXkDO7DFFkp
Zrzut ekranu z programu LibreOffice Base przedstawiający tabelę 2a i jej pola: id_pok, liczba_studentow. Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. W wierszach wpisano następujące wartości: Pole – liczba_studentow, Alias – średnio_w_pokoju, Tabela – 2a, Widoczny – zaznaczono haczyk, Funkcja – Średnia.
Aby wynik na pewno zaokrąglono do czterech miejsc po przecinku, wskazujemy w menu kontekstowym właśnie utworzonej kwerendy następujące opcje:
R15iLrlPo4icL
Zrzut ekranu z programu LibreOffice Base. Po lewej kolumna Baza danych, a w niej typy baz danych. Wybrano Kwerendy. Po prawej sekcja Zadania, a w niej polecenia: Utwórz projekt kwerendy…; Użyj kreatora, aby utworzyć kwerendę… oraz Utwórz kwerendę SQL…. Poniżej sekcja Kwerendy, w której wymieniono kwerendy. Zaznaczono kwerendę 2b. Nad kwerendą 2b lista opcji, spośród których wybrano Edycja w widoku SQL… .
Dopisujemy funkcję CAST(), która dokonuje rzutowania wartości w kolumnie na zmiennoprzecinkowy typ danych DOUBLE:
R1Eq4krF2cNQa
Zrzut ekranu z programu LibreOffice Base. W głównym oknie kod SQL: SELECT AVG( CAST( "liczba_studentow" AS DOUBLE ) ) AS "srednio_w_pokoju FROM "2a".
Teraz można już powrócić do wyników kwerendy 2b, kliknąć prawym przyciskiem na kolumnę srednio_w_pokoju wybierając opcję formatowania wyświetlanej wartości. Wpisujemy wyświetlanie czterech miejsc po przecinku, zgodnie z treścią polecenia:
RRc4E9aNW354r
Zrzut ekranu z programu LibreOffice Base. W głównym obszarze przy komórce średnio_w_pokoju znajduje się lista opcji, gdzie wybrano Formatowanie kolumn… . Poniżej okno Format pola. W nim wybrano zakładkę Format. W sekcji Kategoria lista kategorii, spośród których zaznaczono i wyróżniono czerwonym obramowaniem Naukowy. Obok sekcja Format z formatami kategorii Naukowy. Wybrano 1,2346E+04. Obok sekcja Język, gdzie wybrano Polski. Niżej sekcja Opcje. W sekcji opcje pola: Miejsca dziesiętne z wartością 4 oraz Zera wiodące z wartością 1. Pierwsze pole wyróżniono czerwonym obramowaniem. Obok pól, pola wyboru: Liczby ujemne na czerwono, Notacja inżynierska. Poniżej kolejna sekcja nazwana Kod formatu, gdzie w polu wpisano kod formatu: 0,0000E+00. Na dole okna przyciski: Pomoc, Resetuj, OK oraz Anuluj.
Warto zwrócić uwagę, iż wartość zostanie wyświetlona w zapisie naukowym.
Poprawna odpowiedź
Średnia liczba osób zameldowanych w jednym pokoju: 4,7101.
Schemat oceniania
2 punkty – za prawidłowy wynik, zaokrąglony zgodnie z treścią zadania,
1 punkt – za wynik bez prawidłowego zaokrąglenia (np. 4,7101449275 lub 4,71) albo za wynik obliczony dla 70 pokoi (4,6429),
0 punktów – za odpowiedź błędną albo za brak odpowiedzi.
Polecenie 3
Numer PESEL zawiera informację o płci osoby. Jeżeli przedostatnia cyfra numeru jest parzysta, to PESEL należy do kobiety, jeśli nieparzysta, to do mężczyzny. Podaj liczbę kobiet i liczbę mężczyzn wśród studentów.
Projekt pomocniczej kwerendy o nazwie 3a, dzięki której wyznaczymy dziesiątą cyfrę numeru PESEL. Do jej ekstrakcji używamy funkcji o nazwie SUBSTRING() – pierwszy parametr funkcji określa łańcuch, z którego wyjmujemy znaki, drugi argument to pozycja pierwszego wyjmowanego znaku, a trzecia wartość oznacza liczbę wyjmowanych znaków.
Warto także zwrócić uwagę na ustanowiony alias nowego atrybutu: cyfra_nr_10:
RWJEt41JpY0so
Zrzut ekranu z programu LibreOffice Base przedstawiający tabelę Studenci i jej pola: pesel, nazwisko, imie. Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. W poszczególnych wierszach wpisano następujące wartości: Pole – SUBSTRING ( [pesel]; 10; 1 ), Alias – cyfra_nr_10.
Analogicznie jak w rozwiązaniu z użyciem programu MS Access, korzystamy z obliczenia reszty z dzielenia dziesiątej cyfry przez dwa. Wartość 0 dla cyfr parzystych oznacza w zadaniu kobietę, zaś wartość 1 mężczyznę.
Dysponując zestawem danych o wartościach równych tylko 0 lub 1 w tym samym zapytaniu dokonujemy grupowania z wykorzystaniem funkcji agregującej do zliczenia liczby rekordów:
R8Yx0iG8KB3z4
Zrzut ekranu z programu LibreOffice Base przedstawiający tabelę 3a z polem cyfra_nr_10. Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. W poszczególnych wierszach w dwóch komórkach wpisano następujące wartości: Pole – MOD( [cyfra_nr_10], 2) – MOD( "cyfra_nr_10", 2), Funkcja – Grupuj – Liczba.
Warto także zwrócić na dwa poprawne rodzaje zapisu argumentu funkcji - może być on zapisany wewnątrz w cudzysłowie albo w nawiasach kwadratowych.
Poprawna odpowiedź
Kobiety = 138, mężczyźni = 192.
Schemat oceniania
2 punkty – za prawidłowe podanie dwóch wyników,
1 punkt – za prawidłowe podanie jednego wyniku,
1 punkt – w przypadku otrzymania wyników: kobiety = 153, mężczyźni = 177,
0 punktów – za odpowiedź błędną albo za brak odpowiedzi.
Polecenie 4
Podaj nazwiska i imiona studentów, którzy nie mieszkają w pokojach w miasteczku akademickim. Listę posortuj alfabetycznie według nazwisk.
W projekcie kwerendy dodajemy tabele Studenci oraz Meldunek, po czym klikamy prawym przyciskiem myszy na „nić” symbolizującą relację pomiędzy tymi tabelami. Następnie z menu kotekstowego wskazujemy opcję Edycja.
W kolejnym oknie z listy wyboru Typ wskazujemy taki rodzaj złączenia, dla którego otrzymamy w oknie komunikat: „Zawiera WSZYSTKIE rekordy z tabeli 'Studenci' i podzbiór rekordów z tabeli 'Meldunek' o równych wartościach w powiązanych polach”:
RNCEQ2CDF476D
Zrzut ekranu z programu LibreOffice Base. W widoku relacji dwie tabele: Studenci oraz Meldunek. Obok tabeli Studenci nad tabelą Meldunek wyświetlono menu, z którego wybrano opcję Edycja… . Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. W wierszu Pole wpisano nazwy pól: imie, nazwisko, pesel . W wierszu Tabele wpisano nazwy tabel: Studenci, Studenci, Meldunek. W wierszu Kryterium w trzeciej komórce wpisano: IS EMPTY. Po prawej stronie zrzutu ekranu okno Właściwości załącznika. W oknie sekcje: Złączone tabele, Opcje, Złączone pola. W sekcji Złączone tabele dwa nieaktywne pola z zawartością: Meldunek oraz Studenci. W sekcji Opcje pole Typ wyróżnione czerwonym obramowaniem, z wybraną opcją Złączenie prawostronne. Poniżej pole wyboru Naturalne. W sekcji Złączone pola tabela składająca się z dwóch kolumn: Meldunek z wierszem pesel oraz Studenci z wierszem pesel. Poniżej wyróżniony czerwonym obramowaniem tekst: Zawiera WSZYSTKIE rekordy z tabeli ‘Studenci’ i podzbiór rekordów z tabeli ‘Meldunek’ o równych wartościach w powiązanych polach. Dalej tekst: Zwróć uwagę, że niektóre bazy danych mogą nie obsługiwać tego typu połączenia. Na dole okna przyciski: Pomoc, OK oraz Anuluj.
Typ złączenia (lewostronne lub prawostronne) zależeć będzie w praktyce od tego, którą tabelę LibreOffice Base umieści po lewej stronie w oknie Właściwości złączenia.
W przykładzie powyżej tabela Meldunek znajduje się na pozycji lewej, stąd złączenie musiało być typu prawostronnego – szukamy przecież tych studentów, którzy uwzględnieni są w tabeli Meldunek, ale nie znajdziemy ich numeru PESEL w tabeli Meldunek.
Warto także zauważyć, iż znana z języka SQL oraz programu MS Access klauzula IS NULL w LibreOffice Base przyjmuje postać IS EMPTY. Sam edytor automatycznie poprawi nas zresztą w przypadku wpisania w polu Kryterium niewłaściwego wariantu klauzuli.
Poprawna odpowiedź
DYLAG JACEK
NAJDA PIOTR
PIETRASZEWSKI STEFAN
SIECZKOWSKI MACIEJ
ZALESKA JULIA
Schemat oceniania
3 punkty – za poprawną listę nazwisk i imion wszystkich oraz posortowanie wyników,
2 punkty – za poprawną listę nazwisk i imion, ale brak posortowania wyników,
0 punktów – za odpowiedź niepełną lub błędną albo za brak odpowiedzi.
Polecenie 5
Biblioteka planuje wprowadzenie zakazu wypożyczania kilku egzemplarzy tego samego podręcznika studentom mieszkającym w jednym pokoju. Gdy ta zasada będzie obowiązywać, w żadnym pokoju nie powtórzy się żaden tytuł podręcznika. Podaj, ile byłoby wypożyczonych podręczników, gdyby takie ograniczenie już obowiązywało.
Rozpoczynamy od kwerendy o nazwie 5a zawierającej podwójne grupowanie – nie tylko według tytułów książek, lecz także z uwzględnieniem numeru pokoju studenta, który ją wypożyczył.
Co ważne – ponieważ LibreOffice Base nie pozwolił stworzyć powiązania pomiędzy atrybutami pesel w widoku relacji, to ustanawiamy takie powiązanie samodzielnie na potrzeby kwerendy (patrz czerwona ramka):
R1TPbT0QSw58w
Zrzut ekranu z programu LibreOffice Base przedstawiający relacje między tabelami Wypozyczenia i Meldunek. W tabeli Wypozyczenia pola: lp, pesel, tytul. W tabeli Meldunek pola: id_meldunku, pesel, id_pok. Pola pesel zaznaczone na niebiesko i objęte czerwonym obramowaniem. Na wysokości tych pól połączenie między tabelami. Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. W wierszach w poszczególne komórki wpisano dane. Pole – tytul – id_pok – lp. Alias – w trzeciej komórce ile_wypozyczen. Tabela – Wypozyczenia – Meldunek – Wypozyczenia. Funkcja – Grupuj – Grupuj – Liczba. Kryterium – w trzeciej komórce > 1.
Oczywiście nas interesują tylko wypożyczenia nielegalne w świetle nowej zasady, a zatem takie, w których liczba książek o tym samym tytule przekracza wartość 1 w dowolnym pokoju.
Tworzymy zatem kolejną, pomocniczą kwerendę o nazwie 5b, która zsumuje łączną liczbę tylko wypożyczeń łamiących nową zasadę. A ponieważ jedna książka o danym tytule jest legalna w pokoju, to wartości otrzymane w rezultatach kwerendy 5a należy zmniejszyć o jeden:
RVS1gcn7iudbi
Zrzut ekranu z programu LibreOffice Base przedstawiający tabelę 5a z polami: tytul, id_pok, ile_wypozyczen. Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. W wiersze wpisano dane. Pole – "ile_wypozyczen", Alias – nielegalnych, Tabela – 5a, Funkcja – Suma.
Po wykonaniu powyższego zapytania okazuje się, iż aktualnie w bazie znajduje się 14 wypożyczeń łamiących wspomnianą zasadę. Ponieważ wszystkich wypożyczeń zgromadzonych w tabeli wypozyczenia jest łącznie 330, to wypożyczeń zgodnych z planowaną zasadą jest dokładnie 316.
Poprawna odpowiedź
Liczba wypożyczonych podręczników, gdyby ograniczenie już wprowadzono: 316.
Schemat oceniania
3 punkty – za podanie prawidłowego wyniku,
1 punkt – za wynik nieuwzględniający studentów spoza kampusu (311),
0 punktów – za odpowiedź niepełną lub błędną albo za brak odpowiedzi.
Słownik
klucz podstawowy
klucz podstawowy
zwany też kluczem głównym (ang. primary key) – jedno lub więcej pól (atrybutów), których wartość jednoznacznie identyfikuje każdy rekord w tabeli; taka cecha klucza nazywana jest unikatowością (unikalnością); klucz podstawowy służy do powiązania rekordów jednej tabeli z rekordami w innej tabeli
relacja
relacja
powiązanie logiczne występujące między dwoma tabelami, realizowane za pomocą klucza podstawowego i klucza obcego albo specjalnej tabeli (tabeli łączącej); w oryginalnej nomenklaturze E.F. Codda relacja oznaczała podzbiór iloczynu kartezjańskiego, czyli tabelę – współcześnie jednak używamy tego terminu raczej w odniesieniu do powiązań logicznych między tabelami, niż do samych tabel
![Ilustracja przedstawiająca okno Importuj tekst – [studenci.txt]. W oknie dwie sekcje: Importuj oraz Opcje Separatora. W sekcji Importuj pola z właściwościami: Zestaw znaków – Unicode (UTF‑8), Język – Domyślny - Polski, Od wiersza – 1. W sekcji Opcje separatora spośród radialnych pól Stała szerokość oraz Rozdzielony wybrano drugą opcję. Poniżej pola wyboru z separatorami. Zaznaczono i wyróżniono czerwonym obramowaniem opcję Tabulatory.](https://static.zpe.gov.pl/portal/f/res-minimized/Rjn1lF4K4PykM/1665414593/5XOMuTfHsrP8V56j8tbHE7Kbk2hvL5Lv.png)
![Ilustracja przedstawiająca okno Formatowanie typu. Po lewej stronie obszar z wierszami: pesel, nazwisko oraz imie. Przy pesel, w czerwonym obramowaniu zaznaczona opcja Klucz główny. Po prawej stronie okna sekcja Informacje o kolumnach, w której znajdują się informacje takie jak nazwa pola – pesel, typ pola – Tekst [ VARCHAR ] oraz długość – 255. Poniżej sekcja Automatyczne rozpoznawanie typu, a w nim pole Wiersze (maks.): 10 z przyciskiem Automatyczne. Na dole okna przyciski: Pomoc, Wstecz, Dalej, Utwórz oraz Anuluj.](https://static.zpe.gov.pl/portal/f/res-minimized/R1UN2Fl20ateh/1665414594/U9ix0DZ3cft9DGDa3WQjqlrOD6QDziwi.png)
![Zrzut ekranu z programu LibreOffice Base przedstawiający tabelę Studenci i jej pola: pesel, nazwisko, imie. Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. W poszczególnych wierszach wpisano następujące wartości: Pole – SUBSTRING ( [pesel]; 10; 1 ), Alias – cyfra_nr_10.](https://static.zpe.gov.pl/portal/f/res-minimized/RWJEt41JpY0so/1665414680/2MOHjnZaD4YmMgDy5jO8gXAfDDkwNSGQ.png)
![Zrzut ekranu z programu LibreOffice Base przedstawiający tabelę 3a z polem cyfra_nr_10. Poniżej tabela z właściwościami. Wiersze nazwano kolejno: Pole, Alias, Tabela, Sortowanie, Widoczny, Funkcja, Kryterium. W poszczególnych wierszach w dwóch komórkach wpisano następujące wartości: Pole – MOD( [cyfra_nr_10], 2) – MOD( "cyfra_nr_10", 2), Funkcja – Grupuj – Liczba.](https://static.zpe.gov.pl/portal/f/res-minimized/R8Yx0iG8KB3z4/1665414680/VR8aXDtbu1NdGPIa5mc9jw9r1nZnkh4q.png)
