Klasa III rozwiązywanie zadań z bazą danych

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.

Utwórz uporządkowaną alfabetycznie według nazwisk listę sześcioletnich dziewczynek, których preferencje zawierają Przedszkole nr 75.
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.
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.
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;

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

Informatyka dla klas III

Rozwiązywanie zadań z bazą danych

Instrukcja:

Przedszkolaki

Rozwiązanie częściowe