Informatyka dla klas III

Rozwiązywanie zadań z bazą danych

Instrukcja:

Załaduj do katalogu /tmp podane pliki. Uruchom terminal i zaloguj się do serwera mysql. Wykonaj poszczególne punkty ćwiczenia. Wyniki skopiuj do notatnika leafpad wraz z poleceniem sql, za pomocą którego zostały uzyskane. Na początku pliku wpisz swoje imię i nazwisko. Gotowy plik z odpowiedziami prześlij na adres:

 

i-lo@eduinf.waw.pl

 

Przedszkolaki

W Bajtkowie trwa rekrutacja dzieci do przedszkoli prowadzona za pośrednictwem systemu komputerowego. Rodzice wybierają maksymalnie trzy przedszkola, a ich kolejność ustalają na liście preferencji. W plikach

dzieci.txt, preferencje.txt i przedszkola.txt

 

zebrano dane o dzieciach, ich listach preferencji oraz przedszkolach biorących udział w rekrutacji. W pliku dzieci.txt znajdują się 2443 wiersze z informacjami o dzieciach. W każdym wierszu znajdują się następujące dane rozdzielone średnikami: numer PESEL poprzedzony literą p (Pesel), nazwisko (Nazwisko), imię (Imie), płeć (Plec), wiek (Wiek).

Przykład:

p06281200672;Adrian;Kacper;chlopiec;6
p06300604359;Baranowski;Piotr;chlopiec;6
p06262204017;Bartoszewicz;Marcin;chlopiec;6
p06260111326;Charkiewicz;Sara;dziewczynka;6
 

Uwaga: każde dziecko ma inny numer PESEL; może być wiele dzieci o tym samym nazwisku i imieniu, ale wówczas będą one miały różne numery PESEL.

 

W pliku preferencje.txt znajdują się 4754 wiersze z informacjami o preferencjach w ubieganiu się o miejsce w przedszkolu. W każdym wierszu znajdują się następujące dane rozdzielone średnikami: numer PESEL dziecka poprzedzony literą p (Pesel), pozycja na liście preferencji danego dziecka (Numer_preferencji), identyfikator przedszkola (Id_przedszkola).

Przykład:

p07270202084;1;38
p08310501583;1;48
p09290609238;1;57

 

W pliku przedszkola.txt znajduje się 47 wierszy z informacjami o przedszkolach. W każdym wierszu znajdują się następujące dane rozdzielone średnikami: identyfikator przedszkola (Id_przedszkola), nazwa przedszkola (Nazwa_przedszkola), liczba miejsc w przedszkolu (Liczba_miejsc).

Przykład:

66;Niepubliczne Przedszkole Cogito;50
14;Niepubliczne Przedszkole Krasnal;30
 
Wykorzystując serwer mysql oraz dane zawarte w tych plikach, wykonaj poniższe polecenia.
  1. Utwórz uporządkowaną alfabetycznie według nazwisk listę sześcioletnich dziewczynek, których preferencje zawierają Przedszkole nr 75.
  2. Podaj imiona i nazwiska trzech pierwszych osób w kolejności alfabetycznej według nazwisk, które na liście preferencji mają po dokładnie trzy przedszkola.
  3. Podaj nazwę przedszkola, które znajduje się na liście preferencji wyłącznie na pierwszym miejscu (nikt nie wybrał go jako możliwość drugą albo dalszą), oraz liczbę osób, które je wybrały.
  4. Podaj trzy pierwsze wiersze zestawienia, uporządkowanego niemalejąco według średniej liczby chętnych dzieci, zawierającego dla każdego przedszkola następujące informacje: nazwę, liczbę miejsc, liczbę chętnych dzieci, średnią liczbę dzieci na jedno miejsce zaokrągloną do dwóch miejsc po przecinku.

Rozwiązanie częściowe

Z opisu zadania wynika, że w bazie danych będą dwie tabele o następującej strukturze kolumn:

 

Dzieci
Pesel Nazwisko Imie Plec Wiek
CHAR(12) VARCHAR(20) VARCHAR(20) VARCHAR(11) INT

 

Pole Pesel będzie kluczem podstawowym, ponieważ jest dla każdego dziecka inne.

 

Preferencje
Pesel Numer_preferencji Id_przedszkola
CHAR(12) INT VARCHAR(15)

 

W tej tabeli nie ma klucza podstawowego, ponieważ każde pole może się powtarzać.

 

Przedszkola
Id_przedszkola Nazwa_przedszkola Liczba_miejsc
INT VARCHAR(50) INT

 

Pole Id_przedszkola jest kluczem podstawowym.

 

Tabela Preferencje jest tabelą spajającą ze sobą tabele Dzieci oraz Przedszkola. Umożliwia ona uzyskanie tzw. relacji wiele do wielu. W tego typu relacji każdy rekord z tabeli Dzieci może być połączony z kilkoma rekordami z tabeli Przedszkola (ponieważ dane dziecko może wybrać do 3 przedszkoli w swoich preferencjach). Również każdy rekord z tabeli Przedszkola może się łączyć z wieloma rekordami z tabeli Dzieci (jeśli wiele dzieci wybrało dane przedszkole). Dlatego w bazie danych występują następujące relacje:

 

Preferencje.Pesel → Dzieci.Pesel

Preferencje.Id_przedszkola → Przedszkola.Id_przedszkola

Logujemy się na serwer mysql i tworzymy odpowiednie tabele:

 

CREATE TABLE Dzieci
(
    PESEL CHAR(12) PRIMARY KEY,
    Nazwisko VARCHAR(20) NOT NULL,
    Imie VARCHAR(20) NOT NULL,
    Plec VARCHAR(11) NOT NULL,
    Wiek INT NOT NULL
);

CREATE TABLE Preferencje
(
   Pesel CHAR(12) NOT NULL,
   Numer_preferencji INT NOT NULL,
   Id_przedszkola INT NOT NULL
);

CREATE TABLE Przedszkola
(
   Id_przedszkola INT PRIMARY KEY,
   Nazwa_przedszkola VARCHAR(50) NOT NULL,
   Liczba_miejsc INT NOT NULL
);

 

Pobieramy pliki dzieci.txt do katalogu /tmp.

Plik wczytujemy do tabeli w bazie danych

 

LOAD DATA INFILE '/tmp/dzieci.txt' INTO TABLE Dzieci FIELDS TERMINATED BY ';';

 

Sprawdź, czy dane zostały poprawnie odczytane:

 

SELECT * FROM Dzieci;

 

Rekordów ma być 2443.

 

Pobieramy pliki preferencje.txt do katalogu /tmp.

Plik wczytujemy do tabeli w bazie danych

 

LOAD DATA INFILE '/tmp/preferencje.txt' INTO TABLE Preferencje FIELDS TERMINATED BY ';';

 

Sprawdź, czy dane zostały poprawnie odczytane:

 

SELECT * FROM Preferencje;

 

Rekordów ma być 4754.

 

Pobieramy pliki przedszkola.txt do katalogu /tmp.

Plik wczytujemy do tabeli w bazie danych

 

LOAD DATA INFILE '/tmp/przedszkola.txt' INTO TABLE Przedszkola FIELDS TERMINATED BY ';';

 

Sprawdź, czy dane zostały poprawnie odczytane:

 

SELECT * FROM Przedszkola;

 

Rekordów ma być 47.

 

a) Utwórz uporządkowaną alfabetycznie według nazwisk listę sześcioletnich dziewczynek, których preferencje zawierają Przedszkole nr 75.

 

SELECT Nazwisko, Imie AS Imię
FROM Dzieci, Preferencje, Przedszkola
WHERE Plec='dziewczynka' AND Wiek=6 AND
Nazwa_przedszkola='Przedszkole nr 75' AND
Preferencje.Pesel=Dzieci.Pesel AND Preferencje.Id_przedszkola=Przedszkola.Id_przedszkola
ORDER BY Nazwisko ASC;

+---------------+---------+
| Nazwisko      | Imię    |
+---------------+---------+
| Czeszewska    | Paulina |
| Derengowska   | Joanna  |
| Jasinska      | Zuzanna |
| Studzieniecka | Marta   |
| Waluszko      | Zofia   |
+---------------+---------+
5 rows in set (0.00 sec)
 


   I Liceum Ogólnokształcące   
im. Kazimierza Brodzińskiego
w Tarnowie

©2024 mgr Jerzy Wałaszek

Dokument ten rozpowszechniany jest zgodnie z zasadami licencji
GNU Free Documentation License.

Pytania proszę przesyłać na adres email: i-lo@eduinf.waw.pl

W artykułach serwisu są używane cookies. Jeśli nie chcesz ich otrzymywać,
zablokuj je w swojej przeglądarce.
Informacje dodatkowe