Relacyjne bazy danych

RELACYJNE BAZY DANYCH - ĆWICZENIA
Utworzymy bazę danych - Dziennik lekcyjny
Tworzenie tabel
Utwórz nową tabelę. Wybierz obiekt Tabele, kliknij przycisk Nowy, a następnie z listy wybierz Widok
Projektu.
1. Utwórz tabelę Ocena, w której zapiszesz informacje o ocenach uczniów z różnych przedmiotów.
Zapisz tabelę. W tej tabeli nie tworzymy żadnego Klucza podstawowego.
Wypełnij tabelę danymi (Przełącz się na Widok arkusza danych):
2. W podobny sposób utwórz następną tabelę. Kluczem podstawowym ma być komórka
ID ucznia. Aby ustawić klucz podstawowy kliknij prawym klawiszem myszki na komórkę i wybierz
Klucz podstawowy. Po utworzeniu tabeli zapisz ją pod nazwą Uczeń.
Wypełnij tabelę danymi (Przełącz się na Widok arkusza danych):
3.
Utwórz
tabelę
Nauczyciel.
ID nauczyciela. Zapisz tabelę.
Kluczem
podstawowym
ma
być
komórka
być
komórka
Wypełnij tabelę danymi (Przełącz się na Widok arkusza danych):
4.
Utwórz
ID ucznia.
tabelę
Szafka.
Kluczem
podstawowym
ma
Wypełnij tabelę danymi (Przełącz się na Widok arkusza danych):
5. Utwórz tabelę
ID przedmiotu.
Lista
przedmiotów.
Kluczem
podstawowym
ma
być
komórka
Wypełnij tabelę danymi (Przełącz się na Widok arkusza danych):
Tworzenie relacji między tabelami
Wybierz opcję Relacje z menu Narzędzia. Wyświetli się okno Pokaż tabelę.
Wybieraj z listy po kolei wszystkie tabele i za każdym razem naciśnij przycisk Dodaj.
Po dodaniu tabel kliknij lewym przyciskiem myszki na polu ID ucznia tabeli Uczeń i przeciągnij na
pole ID ucznia w tabeli Ocena. Pojawi się okno dialogowe Edytowanie relacji. Zaznacz opcje
Wymuszaj więzy integralności i Kaskadowo aktualizuj pola pokrewne. Upewnij się, że typ relacji
to jeden do wielu ( - 1).
W podobny sposób połącz pozostałe tabele.
Zauważ, że tabele Uczeń i Szafka połączone są relacją jeden do jednego (1 - 1).
Upewnij się, że relacje zostały utworzone jak na powyższym rysunku. Zapisz zmiany
i zamknij Relacje.
Język SQL
Selekcja pionowa (projekcja)
Ćwiczenie 1
Na podstawie tabeli uczeń chcielibyśmy utworzyć listę wszystkich uczniów danej klasy, z której
wydrukowalibyśmy zaproszenia na nasze urodziny, dane o imionach rodziców byłyby nam zupełnie
niepotrzebne.
Aby utworzyć tego typu zestawienia i raporty należy z dostępnych obiektów bazy danych wybrać
obiekt Kwerendy, kliknąć przycisk Nowy (w rezultacie wywołamy kreatora kwerend), następnie
z listy dostępnych możliwości wybierz Widok Projekt. Z listy okna dialogowego wybierz tabelę
Uczniowie. Następnie przełącz się z Widok Projekt na Widok SQL.
Po przejściu do tworzenia nowych kwerend w Widoku SQL, wpisz poniższą instrukcję:
SELECT *
FROM Uczeń;
Przełącz się na Widok arkusza danych i zobacz efekt.
Przełącz się na Widok SQL i zmodyfikuj kwerendę aby ograniczyć liczbę kolumn do dwóch:
SELECT Imię, Nazwisko
FROM Uczeń;
Przełącz się na Widok arkusza danych i zobacz efekt
Zapisz naszą nową kwerendę w bazie danych pod nazwą Imiona.
Zmodyfikujmy naszą listę tak, aby lista uczniów była uszeregowana alfabetycznie wg. nazwisk
uczniów. W tym celu (w Widoku SQL naszej kwerendy) dodajmy do instrukcji SELECT dodatkowy
element:
SELECT Imię, Nazwisko
FROM Uczeń
order by Nazwisko;
Przełącz się na Widok arkusza danych i zobacz efekt.
Selekcja pozioma (selekcja)
Ćwiczenie 1
Chcemy z bazy danych wydobyć informacje kontaktowe do Jana Bandury. Musimy znaleźć w tabeli
Uczeń wiersz w którym zapisane są jego dane.
Utwórz nową kwerendę na bazie tabeli Uczeń i w Widoku SQL wpisz następujące polecenie:
SELECT *
FROM Uczeń
WHERE [ID ucznia]='JBA';
Przełącz się na Widok arkusza danych i zobacz efekt (Nazwę
kolumny ID ucznia umieściliśmy w nawiasie
kwadratowym, ponieważ zawiera ona spację).
Powiedzmy, że interesuje nas wyłącznie numer telefonu. Połączmy selekcję pionową z poziomą
w następującym poleceniu:
SELECT Imię, Nazwisko, [telefon kontaktowy]
FROM Uczeń
WHERE [ID ucznia]='JBA';
Przełącz się na Widok arkusza danych i zobacz efekt
Ćwiczenie 2
Dyrektor chciałby wiedzieć, ile ocen dopuszczających wystawiliśmy na koniec semestru w klasie 2a.
Zadanie sprowadza się do wybrania z tabeli Ocena tych wierszy, które w kolumnie Ocena
przechowują wartość 2, w kolumnie ID przedmiotu wartość "MAT", a w kolumnie zdobyta za
wartość semestr. Rozwiązaniem zadania jest instrukcje:
SELECT Ocena
FROM Ocena
WHERE Ocena=2 AND [ID przedmiotu]='MAT' AND [Zdobyta za]='semestr';
Przełącz się na Widok arkusza danych i zobacz efekt. Zapisz kwerendę jako dopuszczające.
Ćwiczenie 3
Tym razem chcielibyśmy sprawdzić, które szafki były okradzione w ciągu ostatnich
3 miesięcy. Zadanie sprowadza się do wybrania z tabeli Szatnia tych wierszy dla których data
ostatniej kradzieży, przechowywana w kolumnie Data kradzieży należy do zakresu <data dzisiejsza,
data dzisiejsza - 91>.
Rozwiązaniem zadania jest poniższe zapytanie skierowane do SZBD:
SELECT [Data kradzieży], [Nr szafki], Uwagi
FROM Szafka
WHERE (Date()-[Data kradzieży])<91;
Przełącz się na Widok arkusza danych i zobacz efekt. Zapisz kwerendę jako kradzieże.
Dodawanie, modyfikowanie i usuwanie wierszy
Ćwiczenie 1
Przyjmijmy, że dyrektor zatrudnił nową nauczycielkę. Fakt ten powinien zostać uwzględniony
w naszej bazie. Do dopisywania danych do istniejącej tabeli służy instrukcja INSERT. W bazie Dziennik
informacje opisujące każdego nauczyciela przechowywane są w tabeli Nauczyciel. Polecenie
dopisania nowej nauczycielki wygląda następująco:
INSERT INTO Nauczyciel ( [ID nauczyciela], Imię, Nazwisko, Adres, [Wychowawca klasy] )
VALUES ('SLE', 'Samanta', 'Lepsza', 'K-ce, 44-112 ul. Portowa 13c', '4d');
Przełącz się na Widok arkusza danych i zobacz efekt. Zapisz kwerendę jako dodaj nauczyciela.
Zmodyfikuj kwerendę dodaj nauczyciela tak aby dane dotyczące nauczyciela można było wpisywać
po uruchomieniu kwerendy:
INSERT INTO Nauczyciel ( [ID nauczyciela], Imię, Nazwisko, Adres, [Wychowawca klasy],
[telefon kontaktowy] )
VALUES ([ID], [Imię], [Nazwisko ], [Adres], [Wychowawca], [Telefon]);
Przełącz się na Widok arkusza danych i zobacz efekt. Zapisz kwerendę jako dodaj nauczyciela.
Ćwiczenie 2
W szafce Ewy Starskiej ponownie dokonano kradzieży. Zadanie polega na aktualizacji daty kradzieży
w tabeli Szafka.
UPDATE Szafka
SET [Data kradzieży] = date ()
WHERE [ID ucznia]='EST';
Przełącz się na Widok arkusza danych i zobacz efekt. Zapisz kwerendę jako nowa kradzież.
Ćwiczenie 3
Zdecydowaliśmy się podnieść semestralną ocenę z matematyki wszystkim uczniom o 1. Poniżej
przedstawiamy polecenie realizujące to zadanie:
UPDATE Ocena
SET Ocena=Ocena+1
WHERE [Zdobyta za]='Semestr' AND [ID przedmiotu]='MAT';
Przełącz się na Widok arkusza danych i zobacz efekt. Zapisz kwerendę jako podnieś ocenę.
Ćwiczenie 4
Chcemy z listy przedmiotów usunąć przedmiot Filozofia wykorzystamy do tego poniższą instrukcję:
DELETE *
FROM [Lista przedmiotów]
WHERE [Id przedmiotu]='FILO';
W rezultacie z tabeli zostaną usunięte wszystkie wiersze spełniające podany warunek.
Przełącz się na Widok arkusza danych i zobacz efekt. Zapisz kwerendę jako skasuj przedmiot.
Łączenie tabel
Ćwiczenie 1
Chcemy poznać numery szafek należących do poszczególnych uczniów, musimy wykorzystać
informację z tabeli Uczeń (takie jak imię i nazwisko), oraz informację z tabeli Szatnia. Zadanie te
realizuje poniższa komenda:
SELECT Uczeń.Imię, Uczeń.Nazwisko, Szafka.[Nr szafki]
FROM Uczeń INNER JOIN Szafka ON Uczeń.[ID ucznia] = Szafka.[ID ucznia]
Ćwiczenie 2
Kolejnym zadaniem będzie wyświetlenie informacji o ocenach z matematyki wraz z imieniem i
nazwiskiem ucznia.
SELECT Uczeń.Imię, Uczeń.Nazwisko, Ocena.Ocena
FROM Uczeń INNER JOIN Ocena ON Uczeń.[ID ucznia] = Ocena.[ID ucznia]
WHERE Ocena.[ID przedmiotu]='MAT'
ORDER BY Uczeń.Nazwisko;
Przełącz się na Widok arkusza danych i zobacz efekt. Zapisz kwerendę jako oceny z matematyki.
Operatory teoriomnogościowe i proste funkcje
Ćwiczenie 1
Mamy za zadanie wyświetlić ilość wszystkich osób w szkole (uczniów i nauczycieli). Do zsumowania
tabel Nauczyciel i Uczeń posłużymy się operatorem UNION
SELECT Imię,Nazwisko
FROM Nauczyciel
UNION
SELECT Imię, Nazwisko
FROM Uczeń
Sprawdź efekt zapytania. Zapisz kwerendę pod nazwą wszyscy.
Ćwiczenie 2
Wróćmy do pytania dyrektora o liczbę ocen dopuszczających wystawionych na koniec semestru z
matematyki.
SELECT COUNT([Ocena])
FROM Ocena
WHERE [Ocena]=2 And [ID przedmiotu]='MAT' And [Zdobyta za]='semestr';
Sprawdź efekt zapytania. Zapisz kwerendę pod nazwą dopuszczające wersja 2.
Ćwiczenie 3
Mamy za zadanie przygotować zestawienie zawierające średnią ocen klasy z każdego przedmiotu.
Do obliczenia średniej użyj funkcji AVG . Parametrem dla funkcji będzie lista wszystkich ocen
pogrupowana według nazw przedmiotów.
SELECT Avg(Ocena) AS Średnia, [Nazwa przedmiotu]
FROM [Lista przedmiotów] INNER JOIN Ocena ON [Lista przedmiotów].[ID przedmiotu] =
Ocena.[ID przedmiotu]
GROUP BY [Nazwa przedmiotu];
Sprawdź efekt zapytania. Zapisz kwerendę pod nazwą średnia ocen.
Formularze
Ćwiczenie 1 – tworzenie formularza
Utwórz formularz na podstawie tabeli Uczeń w tym celu w bazie danych wybierz obiekt Formularze
-> Nowy -> Kreator formularzy. Wybierz tabelę Uczeń. Wybierz wszystkie dostępne pola. Wybierz
układ kolumnowy. Wybierz odpowiadający Ci styl i naciśnij Zakończ.
W podobny sposób wykonaj kolejny formularz na podstawie tabeli Nauczyciel.
Ćwiczenie 2 – tworzenie formularza w widoku projektu
Po wykonaniu powyższych formularzy utwórz formularz w Widoku projektu i zapisz go pod nazwą
Start.
Ćwiczenie 3 – korzystanie z przybornika (dodaj etykietę i przyciski otwierające formularze).
Z przybornika wybierz formatkę Etykieta i podpisz DZIENNIK LEKCYJNY.
Z Przybornika wybierz Przycisk polecenia po przeniesieniu przycisku na formularz pojawi się
Kreator przycisków poleceń. Wybierz Operacje na formularzach -> Otwórz formularz -> Uczeń
-> Dalej -> Wybierz opcję, że na przycisku ma być tekst -> Wpisz nazwę dla przycisku Uczeń ->
Naciśnij Zakończ. Następnie na formularzu opisz przycisk jako Dane uczniów W podobny sposób
dodaj przycisk dla formularza Nauczyciel i opisz przycisk jako Dane nauczycieli.
Widok formularza Start.
Ćwiczenie 4 – dodaj przycisk otwierający kwerendę
Otwórz formularz Nauczyciel. Powiększ obszar formularza a następnie z przybornika dodaj przycisk
polecenia. Wybierz Różne -> Uruchom kwerendę. Wybierz kwerendę dodaj nauczyciela. Wybierz,
że na przycisku ma być tekst. Opisz przycisk jako Dodaj nauczyciela.
Ćwiczenie 4 – Samodzielnie utwórz przycisk zamykający formularz Start.
Raporty
Utwórz
raport
zawierający
zestawienie
średnich
ocen
klasy
z
każdego
przedmiotu.
W tym celu w bazie danych wybierz obiekt Raporty -> Nowy -> Kreator raportów. Wybierz
kwerendę średnia ocen dodaj pola Średnia i Nazwa przedmiotu i naciśnij Dalej.
Nie dodawaj poziomów drukowania, kliknij Dalej. Posortuj rekordy wg średniej. Wybierz układ
kolumnowy - > Dalej. Wybierz odpowiadający Ci Styl -> Dalej. Tytuł raportu pozostaw Średnia
ocen. Zakończ i zobacz efekty swojej pracy.