opr. Lech Banachowski, Krzysztof Matejewski
opr. Lech Banachowski, Krzysztof Matejewski
1
1
Relacyjne bazy danych
Relacyjne bazy danych
Wykład IV
Wykład IV
Microsoft Access – tabele i kwerendy
Microsoft Access – tabele i kwerendy
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
2
2
Streszczenie wykładu IV
Streszczenie wykładu IV
W wykładzie 4 przedstawiona jest realizacja zasad relacyjnego
W wykładzie 4 przedstawiona jest realizacja zasad relacyjnego
modelu danych w konkretnym systemie baz danych – Microsoft
modelu danych w konkretnym systemie baz danych – Microsoft
Access. W ramach jednego systemu projektuje się tabele – w
Access. W ramach jednego systemu projektuje się tabele – w
widoku projekt
widoku projekt
oraz wprowadza się i przetwarza dane – w
oraz wprowadza się i przetwarza dane – w
widoku
widoku arkusz danych
arkusz danych
. Podobnie rzecz ma się z perspektywami,
. Podobnie rzecz ma się z perspektywami,
nazywanymi w MS Access
nazywanymi w MS Access kwerendami
kwerendami
. Projektuje się je w
. Projektuje się je w
widoku projekt (lub bezpośrednio w składni języka SQL), a ogląda
widoku projekt (lub bezpośrednio w składni języka SQL), a ogląda
wyniki i wykonuje operacje na danych – w widoku arkusza danych.
wyniki i wykonuje operacje na danych – w widoku arkusza danych.
MS Access został wybrany do tej roli z uwagi na szereg cech,
MS Access został wybrany do tej roli z uwagi na szereg cech,
wyróżniających go spośród innych Systemów Baz Danych. Przede
wyróżniających go spośród innych Systemów Baz Danych. Przede
wszystkim w łatwy, oparty o interfejs graficzny sposób buduje się
wszystkim w łatwy, oparty o interfejs graficzny sposób buduje się
w nim obiekty bazy danych, oraz definiuje wszelkie między nimi
w nim obiekty bazy danych, oraz definiuje wszelkie między nimi
zależności. Po wtóre system pozwala korzystać z niego nawet
zależności. Po wtóre system pozwala korzystać z niego nawet
niezaawansowanym użytkownikom, ale może też stać się bardzo
niezaawansowanym użytkownikom, ale może też stać się bardzo
mocnym narzędziem do budowy zaawansowanej aplikacji
mocnym narzędziem do budowy zaawansowanej aplikacji
użytkownika.
użytkownika.
Ma też oczywiste niedostatki, jak choćby bezpieczeństwo, ale w tym
Ma też oczywiste niedostatki, jak choćby bezpieczeństwo, ale w tym
przypadku może spełniać zupełnie dobrze rolę dydaktyczną i
przypadku może spełniać zupełnie dobrze rolę dydaktyczną i
testową.
testową.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
3
3
Dlaczego MS Access ?
Dlaczego MS Access ?
MS Access został wybrany do tej roli z uwagi na szereg cech,
MS Access został wybrany do tej roli z uwagi na szereg cech,
wyróżniających go spośród innych Systemów Baz Danych.
wyróżniających go spośród innych Systemów Baz Danych.
W łatwy, oparty o interfejs graficzny sposób buduje się w nim
W łatwy, oparty o interfejs graficzny sposób buduje się w nim
obiekty bazy danych, oraz definiuje wszelkie między nimi
obiekty bazy danych, oraz definiuje wszelkie między nimi
zależności.
zależności.
Umożliwia korzystanie z niego nawet niezaawansowanym
Umożliwia korzystanie z niego nawet niezaawansowanym
użytkownikom, ale może też stać się bardzo mocnym narzędziem
użytkownikom, ale może też stać się bardzo mocnym narzędziem
do budowy zaawansowanej aplikacji użytkownika.
do budowy zaawansowanej aplikacji użytkownika.
Operuje bardzo dobrą implementacją języka SQL, bliską
Operuje bardzo dobrą implementacją języka SQL, bliską
standardu (ANSI 92)
standardu (ANSI 92)
Praktycznie nie wymaga administrowania RDB
Praktycznie nie wymaga administrowania RDB
Wyposażony jest w język VBA – niezwykle mocne narzędzie
Wyposażony jest w język VBA – niezwykle mocne narzędzie
programistyczne
programistyczne
Ma też oczywiste niedostatki, jak choćby bezpieczeństwo, ale w tym
Ma też oczywiste niedostatki, jak choćby bezpieczeństwo, ale w tym
przypadku może spełniać zupełnie dobrze rolę dydaktyczną i
przypadku może spełniać zupełnie dobrze rolę dydaktyczną i
testową, świetnie się też nadaje do tworzenia prototypów baz.
testową, świetnie się też nadaje do tworzenia prototypów baz.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
4
4
Program MS Access
Program MS Access
Program MS Access zawiera:
Program MS Access zawiera:
Narzędzia służące do zakładanie relacyjnej bazy danych,
Narzędzia służące do zakładanie relacyjnej bazy danych,
składającej się z tabel i perspektyw (kwerend);
składającej się z tabel i perspektyw (kwerend);
interfejs graficzny do obiektów bazy danych - tabel i perspektyw;
interfejs graficzny do obiektów bazy danych - tabel i perspektyw;
interfejs graficzny dla aplikacji bazy danych (formularze, raporty,
interfejs graficzny dla aplikacji bazy danych (formularze, raporty,
strony WWW);
strony WWW);
środowisko do programowania aplikacji baz danych (makra, język
środowisko do programowania aplikacji baz danych (makra, język
VBA - Visual Basic for Applications, język SQL).
VBA - Visual Basic for Applications, język SQL).
Jak widać MS Access jest narzędziem uniwersalnym, pozwalającym
Jak widać MS Access jest narzędziem uniwersalnym, pozwalającym
realizować
zarówno
funkcje
serwera
bazy
danych
(w
realizować
zarówno
funkcje
serwera
bazy
danych
(w
ograniczonym zakresie), jak też funkcje klienta. Zwłaszcza
ograniczonym zakresie), jak też funkcje klienta. Zwłaszcza
tworzenie tych ostatnich Access umożliwia w sposób bardzo
tworzenie tych ostatnich Access umożliwia w sposób bardzo
bogaty i elastyczny. Należy tu zwrócić uwagę na bardzo poprawną
bogaty i elastyczny. Należy tu zwrócić uwagę na bardzo poprawną
(w dużym stopniu zgodną ze standardem) implementację języka
(w dużym stopniu zgodną ze standardem) implementację języka
SQL, jak też ogromne możliwości tworzenia funkcjonalnych
SQL, jak też ogromne możliwości tworzenia funkcjonalnych
interfejsów udostępniane przez język VBA (dialekt VB w.6.0).
interfejsów udostępniane przez język VBA (dialekt VB w.6.0).
Począwszy od wersji 2000, Access wyposażony jest w bardzo
Począwszy od wersji 2000, Access wyposażony jest w bardzo
wydajny „silnik bazy danych” o nazwie Jet 4 (wersja rozwojowa
wydajny „silnik bazy danych” o nazwie Jet 4 (wersja rozwojowa
silnika Jet 3, w który wyposażony był Access 97).
silnika Jet 3, w który wyposażony był Access 97).
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
5
5
Okno bazy danych
Okno bazy danych
Zasadniczym oknem dialogowym programu MS Access jest
Zasadniczym oknem dialogowym programu MS Access jest okno bazy
okno bazy
danych
danych
. W oknie tym są wyświetlane listy nazw obiektów bazy danych,
. W oknie tym są wyświetlane listy nazw obiektów bazy danych,
pogrupowane w klasy obiektów. Dla tabel jest możliwe:
pogrupowane w klasy obiektów. Dla tabel jest możliwe:
utworzenie nowej tabeli - przycisk
utworzenie nowej tabeli - przycisk Nowy (New)
Nowy (New)
przejście do widoku projekt istniejącej tabeli - przycisk
przejście do widoku projekt istniejącej tabeli - przycisk Projektuj (Design)
Projektuj (Design)
przejście do widoku arkusz danych istniejącej tabeli - przycisk
przejście do widoku arkusz danych istniejącej tabeli - przycisk Otwórz
Otwórz
(Open)
(Open)
usunięcie istniejącej tabeli (ikonka reprezentująca przekreślenie)
usunięcie istniejącej tabeli (ikonka reprezentująca przekreślenie)
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
6
6
Tabele w MS Access
Tabele w MS Access
Tabele w Accessie stanowią:
Tabele w Accessie stanowią:
strukturę logiczną przechowywania danych w bazie danych;
strukturę logiczną przechowywania danych w bazie danych;
źródło danych dla innych obiektów, takich jak kwerendy
źródło danych dla innych obiektów, takich jak kwerendy
(perspektywy), formularze i raporty,
(perspektywy), formularze i raporty,
element interfejsu użytkownika.
element interfejsu użytkownika.
Zwłaszcza na tę ostatnią właściwość warto zwrócić uwagę. Access w
Zwłaszcza na tę ostatnią właściwość warto zwrócić uwagę. Access w
sposób bardzo łatwy udostępnia zawartość tabel – wyposażony
sposób bardzo łatwy udostępnia zawartość tabel – wyposażony
jest w sprawny interfejs prezentujący zawarte w tabelach dane.
jest w sprawny interfejs prezentujący zawarte w tabelach dane.
Ten łatwy dostęp, to mocna strona Access’a, ale zarazem
Ten łatwy dostęp, to mocna strona Access’a, ale zarazem
zagrożenie, wynikające z dużej łatwości dostępu do edycji danych
zagrożenie, wynikające z dużej łatwości dostępu do edycji danych
oraz struktury samych tabel (!).
oraz struktury samych tabel (!).
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
7
7
Tabele w MS Access
Tabele w MS Access
Widok projektu
Widok projektu
W
W Widoku
Widoku
projektu
projektu
(
(Design
Design
View
View
) tabeli określamy schemat tabeli, czyli
) tabeli określamy schemat tabeli, czyli
definiujemy kolumny (pola), z których będą się składać wiersze (rekordy)
definiujemy kolumny (pola), z których będą się składać wiersze (rekordy)
tej tabeli, typy danych (dziedziny) przewidziane dla poszczególnych pól,
tej tabeli, typy danych (dziedziny) przewidziane dla poszczególnych pól,
oraz wszystkie pozostałe właściwości kolumn oraz całej tabeli.
oraz wszystkie pozostałe właściwości kolumn oraz całej tabeli.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
8
8
Tabele w MS Access
Tabele w MS Access
Widok arkusza danych
Widok arkusza danych
Po zdefiniowaniu schematu tabeli wprowadzamy do niej dane i
Po zdefiniowaniu schematu tabeli wprowadzamy do niej dane i
wyświetlamy je w widoku
wyświetlamy je w widoku Arkusza
Arkusza
Danych
Danych
(
(Datasheet
Datasheet
). W widoku
). W widoku
tym można wykonywać podstawowe operacje na wierszach tabeli:
tym można wykonywać podstawowe operacje na wierszach tabeli:
przeglądać wiersze (scroll),
przeglądać wiersze (scroll),
wyszukiwać (filtrować) wiersze (filter),
wyszukiwać (filtrować) wiersze (filter),
dopisywać nowy wiersz (insert),
dopisywać nowy wiersz (insert),
usuwać wybrany wiersz (delete),
usuwać wybrany wiersz (delete),
aktualizować wartości w wybranym wierszu (update),
aktualizować wartości w wybranym wierszu (update),
sortować wiersze (sort),
sortować wiersze (sort),
wyszukiwać danych o określonych wartościach (search).
wyszukiwać danych o określonych wartościach (search).
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
9
9
Tabele w MS Access
Tabele w MS Access
Typy danych (Jet 4.0)
Typy danych (Jet 4.0)
Tekst
Tekst
(
(
Text
Text
)
)
- napisy do 255 znaków (Unicode)
- napisy do 255 znaków (Unicode)
Memo
Memo
(albo Text bez określenia długości) - długie teksty (do 2.14
(albo Text bez określenia długości) - długie teksty (do 2.14
GB, czyli ok 1.07 * 10
GB, czyli ok 1.07 * 10
9
9
znaków),
znaków),
Liczba
Liczba
(
(
Number
Number
) - bajt, liczba całkowita (
) - bajt, liczba całkowita (
Integer
Integer
), liczba
), liczba
całkowita długa (
całkowita długa (
Long
Long
Integer
Integer
), pojedyncza precyzja (
), pojedyncza precyzja (
Single
Single
),
),
podwójna precyzja (
podwójna precyzja (
Double
Double
),
),
Data/Godzina
Data/Godzina
(
(
Data/Time
Data/Time
) - np. "22.06.97" lub "22.06.97
) - np. "22.06.97" lub "22.06.97
12:12:34",
12:12:34",
Waluta
Waluta
(
(
Currency
Currency
) - np. "200,25 zł",
) - np. "200,25 zł",
Autonumer
Autonumer
(
(
Autonumber
Autonumber
) - wartości liczbowe zwiększane
) - wartości liczbowe zwiększane
automatycznie dla każdego nowego rekordu lub generowane
automatycznie dla każdego nowego rekordu lub generowane
losowo; wartości typu Long Integer,
losowo; wartości typu Long Integer,
Tak/Nie
Tak/Nie
(
(
Yes/No
Yes/No
) - wartości logiczne (boolean),
) - wartości logiczne (boolean),
Obiekt
Obiekt
OLE
OLE
(
(
OLE Object
OLE Object
) - obiekt jak np. grafika, dokument
) - obiekt jak np. grafika, dokument
Worda, arkusz Excela, obsługiwany przez inny program systemu
Worda, arkusz Excela, obsługiwany przez inny program systemu
Windows.
Windows.
Hiperłącze
Hiperłącze
(
(
Hyperlink
Hyperlink
)
)
- adres obiektu w sieci, na przykład
- adres obiektu w sieci, na przykład
pliku lub strony WWW.
pliku lub strony WWW.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
10
10
Tabele w MS Access
Tabele w MS Access
Zmiana schematu tabeli
Zmiana schematu tabeli
Używając tego samego interfejsu ekranowego, co przy tworzeniu tabeli
Używając tego samego interfejsu ekranowego, co przy tworzeniu tabeli
(widok projektu tabeli), można dokonywać zmian schematu tabeli
(widok projektu tabeli), można dokonywać zmian schematu tabeli
takich jak:
takich jak:
Dodanie nowego pola
Dodanie nowego pola
.
.
Usunięcie pola
Usunięcie pola
– odwołanie do tego pola trzeba usunąć samemu,
– odwołanie do tego pola trzeba usunąć samemu,
także z kwerend, formularzy i raportów. W przypadku pozostawienia
także z kwerend, formularzy i raportów. W przypadku pozostawienia
odwołania do usuniętej kolumny w któryś z wymienionych typów
odwołania do usuniętej kolumny w któryś z wymienionych typów
obiektów, podczas jego użycia wystąpi błąd, a MS Access wyświetli
obiektów, podczas jego użycia wystąpi błąd, a MS Access wyświetli
komunikat z żadaniem podania brakującej wartości.
komunikat z żadaniem podania brakującej wartości.
Zmiana nazwy pola
Zmiana nazwy pola
- po dokonaniu zmiany nazwy w widoku
- po dokonaniu zmiany nazwy w widoku
projektu, trzeba ją zmienić samemu również w kwerendach,
projektu, trzeba ją zmienić samemu również w kwerendach,
formularzach i raportach (konsekwencje braku tych zmian j.w.).
formularzach i raportach (konsekwencje braku tych zmian j.w.).
Zmiana typu danych
Zmiana typu danych
z konwersją zapisanych danych, np.
z konwersją zapisanych danych, np.
zwiększenie rozmiaru, zmiana tekstu na typ Memo lub dowolnego
zwiększenie rozmiaru, zmiana tekstu na typ Memo lub dowolnego
typu danych na typ Tekst. Po zmianie typu danych system sprawdza
typu danych na typ Tekst. Po zmianie typu danych system sprawdza
możliwość jej realizacji z uwagi na istniejące dane. Gdy system nie
możliwość jej realizacji z uwagi na istniejące dane. Gdy system nie
może dokonać odpowiedniej konwersji istnieją dwie możliwości –
może dokonać odpowiedniej konwersji istnieją dwie możliwości –
albo nie dokonywać zmiany typu danych (rozwiązanie trywialne),
albo nie dokonywać zmiany typu danych (rozwiązanie trywialne),
albo wartości, których nie można przekształcić, ustawić na Null.
albo wartości, których nie można przekształcić, ustawić na Null.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
11
11
Tabele w MS Access
Tabele w MS Access
Właściwości kolumny
Właściwości kolumny
Oprócz podania typu danych, oraz znaczenia (opisu), określa się dodatkowe
Oprócz podania typu danych, oraz znaczenia (opisu), określa się dodatkowe
właściwości kolumny mające charakter
właściwości kolumny mające charakter więzów spójności
więzów spójności
:
:
rozmiar pola
rozmiar pola
(
(
Field Size
Field Size
),
),
reguła poprawności
reguła poprawności
(
(
Validation Rule
Validation Rule
) np. w tabeli
) np. w tabeli Studenci
Studenci
Rekrutacja
Rekrutacja
>= Date()
>= Date()
wymagane
wymagane
(
(
Required
Required
)- czy wartość musi być wprowadzona (czy NULL
)- czy wartość musi być wprowadzona (czy NULL
jest dozwolone),
jest dozwolone),
zerowa długość
zerowa długość
(
(
Allow Zero Legth
Allow Zero Legth
) - czy wartość może być pustym
) - czy wartość może być pustym
napisem (dla typu danych tekst i memo),
napisem (dla typu danych tekst i memo),
bądź dostarczających
bądź dostarczających dodatkowych
dodatkowych
informacji
informacji
dla interfejsu użytkownika:
dla interfejsu użytkownika:
format
format
(
(
Format
Format
) - format wyświetlania na ekranie (ale nie dla wszystkich
) - format wyświetlania na ekranie (ale nie dla wszystkich
typów),
typów),
miejsca dziesiętne
miejsca dziesiętne
(
(
Decimal Places
Decimal Places
) - ile miejsc po kropce ma być
) - ile miejsc po kropce ma być
wyświetlane na ekranie (dla kolumn liczbowych),
wyświetlane na ekranie (dla kolumn liczbowych),
maska wprowadzania
maska wprowadzania
(
(
Input Mask
Input Mask
) - wyświetlanie znaków
) - wyświetlanie znaków
formatujących przy wprowadzaniu wartości do pola,
formatujących przy wprowadzaniu wartości do pola,
tytuł
tytuł
(
(
Caption
Caption
)
)
- nazwa tego pola w widoku arkusza danych, formularzu
- nazwa tego pola w widoku arkusza danych, formularzu
lub raporcie,
lub raporcie,
wartość domyślna
wartość domyślna
(
(
Default Value
Default Value
) - wartość automatycznie wstawiana
) - wartość automatycznie wstawiana
do pola gdy użytkownik jej nie podał,
do pola gdy użytkownik jej nie podał,
komunikat o błędzie
komunikat o błędzie
(
(
Validation Text
Validation Text
) - komunikat wypisywany na
) - komunikat wypisywany na
ekranie gdy dane nie spełniają reguł poprawności,
ekranie gdy dane nie spełniają reguł poprawności,
indeksowane
indeksowane
(
(
Indexed
Indexed
) - czy na polu ma być założony indeks.
) - czy na polu ma być założony indeks.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
12
12
Tabele w MS Access
Tabele w MS Access
Więzy spójności globalne dla tabeli
Więzy spójności globalne dla tabeli
Reguły poprawności dotyczące całej tabeli np. dla pól typu
Reguły poprawności dotyczące całej tabeli np. dla pól typu
DateTime
DateTime
(tabela
(tabela
Studenci
Studenci
)
) Rekrutacja < Obrona
Rekrutacja < Obrona
. Dostęp z menu
. Dostęp z menu
Widok (View)
Widok (View)
->
->
Właściwości
Właściwości
tabeli (Properties)
tabeli (Properties)
lub przycisk
lub przycisk
Właściwości
Właściwości
na pasku
na pasku
narzędzi. Można zdefiniować własny
narzędzi. Można zdefiniować własny
Komunikat o błędzie
Komunikat o błędzie
(
(
Validation
Validation
text
text
), wyświetlany w postaci komunikatu ekranowego, w przypadku
), wyświetlany w postaci komunikatu ekranowego, w przypadku
naruszenia zadeklarowanych reguł. Identycznie działają reguły poprawności
naruszenia zadeklarowanych reguł. Identycznie działają reguły poprawności
wraz z komunikatami o błędach definiowane na poziomie pojedynczej
wraz z komunikatami o błędach definiowane na poziomie pojedynczej
kolumny w tabeli.
kolumny w tabeli.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
13
13
Tabele w MS Access
Tabele w MS Access
Kreator odnośników
Kreator odnośników
Odnośnik
Odnośnik
(
(LookUp
LookUp
) określa listę wartości, bądź kolumnę innej tabeli
) określa listę wartości, bądź kolumnę innej tabeli
(kwerendy), z której pobiera się wartości dla danego pola, np. wartości
(kwerendy), z której pobiera się wartości dla danego pola, np. wartości
klucza obcego pochodzące z kolumny klucza głównego tabeli nadrzędnej.
klucza obcego pochodzące z kolumny klucza głównego tabeli nadrzędnej.
Pole z odnośnikiem jest wyświetlane w postaci listy rozwijanej.
Pole z odnośnikiem jest wyświetlane w postaci listy rozwijanej.
Istnieje możliwość wyświetlania w widoku arkusza danych tabeli nie samych
Istnieje możliwość wyświetlania w widoku arkusza danych tabeli nie samych
wartości odnośnika, ale wartości z nimi powiązanych. Na przykład,
wartości odnośnika, ale wartości z nimi powiązanych. Na przykład,
zamiast wyświetlać identyfikatory wykładowców, na liście rozwijanej
zamiast wyświetlać identyfikatory wykładowców, na liście rozwijanej
pojawiają się ich imiona i nazwiska.
pojawiają się ich imiona i nazwiska.
Wartości wyświetlane w polu
Wartości wyświetlane w polu Id_Wykladowcy
Id_Wykladowcy
(w tabeli
(w tabeli Katedry
Katedry
) pochodzą z
) pochodzą z
tabeli
tabeli Wykladowcy
Wykladowcy
. Sam identyfikator wykładowcy, będący kolumną
. Sam identyfikator wykładowcy, będący kolumną
związaną z polem, może nie być (i na ogół nie jest) wyświetlany (szerokość
związaną z polem, może nie być (i na ogół nie jest) wyświetlany (szerokość
wyświetlania odpowiadającej mu kolumny ustawiamy wówczas na 0 cm).
wyświetlania odpowiadającej mu kolumny ustawiamy wówczas na 0 cm).
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
14
14
Tabele w MS Access
Tabele w MS Access
Lista odnośnika
Lista odnośnika
Pole
Pole Kierownik katedry
Kierownik katedry
jest typu odnośnik. Wyświetlane wartości
jest typu odnośnik. Wyświetlane wartości
(
(Nazwisko
Nazwisko
i
i Imię
Imię
) pochodzą z tabeli
) pochodzą z tabeli Wykladowcy
Wykladowcy
. Wartością
. Wartością
fizycznie zapisywaną w bazie danych jest identyfikator wykładowcy
fizycznie zapisywaną w bazie danych jest identyfikator wykładowcy
(
(Id_wykladowcy
Id_wykladowcy
). W polu
). W polu Kierownik
Kierownik
katedry
katedry
kolumna ta została
kolumna ta została
ukryta (szerokość jej wyświetlania została ustawiona na 0 cm),
ukryta (szerokość jej wyświetlania została ustawiona na 0 cm),
natomiast wyświetlane są wartości imion i nazwisk kierowników
natomiast wyświetlane są wartości imion i nazwisk kierowników
katedr. Kolumna
katedr. Kolumna Id_wykladowcy
Id_wykladowcy
pełni natomiast rolę
pełni natomiast rolę Kolumny
Kolumny
Wiążącej
Wiążącej
(
(Bound
Bound
Column
Column
) odnośnika.
) odnośnika.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
15
15
Tabele w MS Access
Tabele w MS Access
Konstrukcja i działanie odnośnika
Konstrukcja i działanie odnośnika
To pole wiąże ze sobą
tabele, ale jego
wartości nie są
wyświetlane
Te pola są wyświetlane
w zadanych
szerokościach
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
16
16
Tabele w MS Access
Tabele w MS Access
Indeksy
Indeksy
Indeks służy przyśpieszeniu wyszukiwania rekordów w oparciu o
Indeks służy przyśpieszeniu wyszukiwania rekordów w oparciu o
wartości w jednym lub kilku polach. Access samodzielnie zakłada
wartości w jednym lub kilku polach. Access samodzielnie zakłada
indeksy na kluczu głównym tabeli (na wszystkich jego polach – jeden
indeksy na kluczu głównym tabeli (na wszystkich jego polach – jeden
indeks). Dostęp do listy indeksów uzyskujemy albo z menu
indeks). Dostęp do listy indeksów uzyskujemy albo z menu
Widok
Widok
->
->
Indeksy
Indeksy
(
(
Indexes
Indexes
), albo przez przycisk
), albo przez przycisk
Indeksy
Indeksy
z paska narzędzi.
z paska narzędzi.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
17
17
Powiązania między tabelami
Powiązania między tabelami
Tworząc schemat bazy danych należy zaplanować
Tworząc schemat bazy danych należy zaplanować powiązania
powiązania
(
(związki
związki
,
,
określane w MS Access jako
określane w MS Access jako relacje
relacje
) pomiędzy danymi zapisanymi w
) pomiędzy danymi zapisanymi w
tabelach. Ze względu na to, że przy projektowaniu bazy danych termin
tabelach. Ze względu na to, że przy projektowaniu bazy danych termin
relacja
relacja
ma znaczenie matematycznej abstrakcji tabeli, w treści wykładu
ma znaczenie matematycznej abstrakcji tabeli, w treści wykładu
używamy terminów
używamy terminów związek
związek
lub
lub powiązanie
powiązanie
. Związki tworzymy w celu:
. Związki tworzymy w celu:
określenia referencyjnych więzów spójności między tabelami
określenia referencyjnych więzów spójności między tabelami
,
,
sprawdzanych przez system.
sprawdzanych przez system.
automatycznego utworzenia warunku złączenia w perspektywie
automatycznego utworzenia warunku złączenia w perspektywie
(kwerendzie) korzystającej z powiązanych tabel.
(kwerendzie) korzystającej z powiązanych tabel.
synchronizacji wyświetlania powiązanych ze sobą danych w
synchronizacji wyświetlania powiązanych ze sobą danych w
formularzach
formularzach
np.
dla
formularza
np.
dla
formularza
Katedry
Katedry
automatycznego
automatycznego
wyświetlania, w podformularzu lub formularzu powiązanym, danych
wyświetlania, w podformularzu lub formularzu powiązanym, danych
wszystkich wykładowców – pracowników danej katedry (podobnie w
wszystkich wykładowców – pracowników danej katedry (podobnie w
podraporcie).
podraporcie).
Dla powiązanych pól muszą być spełnione następujące warunki:
Dla powiązanych pól muszą być spełnione następujące warunki:
Pole (pola) w tabeli, do której występuje odwołanie, muszą tworzyć
Pole (pola) w tabeli, do której występuje odwołanie, muszą tworzyć
klucz główny, lub musi być na nich określony jednoznaczny (unikatowy)
klucz główny, lub musi być na nich określony jednoznaczny (unikatowy)
indeks.
indeks.
Tabela, do której prowadzi odwołanie nazywa się
Tabela, do której prowadzi odwołanie nazywa się tabelą nadrzędną
tabelą nadrzędną
.
.
Tabela, z której wychodzi odwołanie nazywa się
Tabela, z której wychodzi odwołanie nazywa się tabelą podrzędną
tabelą podrzędną
.
.
Powiązane pola muszą mieć ten sam typ danych.
Powiązane pola muszą mieć ten sam typ danych.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
18
18
Powiązania między tabelami
Powiązania między tabelami
Diagram powiązań (schemat bazy danych)
Diagram powiązań (schemat bazy danych)
W menu
W menu Narzędzia
Narzędzia
występuje opcja (ikona na pasku ikon)
występuje opcja (ikona na pasku ikon) Relacje
Relacje
(
(Relationships
Relationships
), po wybraniu której, w osobnym okienku zostaje
), po wybraniu której, w osobnym okienku zostaje
wyświetlony schemat bazy danych.
wyświetlony schemat bazy danych.
Ramki
Ramki
reprezentują
reprezentują tabele
tabele
.
.
Linie
Linie
między tabelami reprezentują
między tabelami reprezentują powiązania
powiązania
(
(związki
związki
) między tabelami.
) między tabelami.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
19
19
Powiązania między tabelami
Powiązania między tabelami
Diagram powiązań (schemat bazy danych)
Diagram powiązań (schemat bazy danych)
Każde powiązanie między tabelami ma dwie strony. Strona
Każde powiązanie między tabelami ma dwie strony. Strona jeden
jeden
jest reprezentowana
jest reprezentowana
graficznie symbolem
graficznie symbolem 1
1
i znajduje się przy tabeli, do której prowadzi odwołanie,
i znajduje się przy tabeli, do której prowadzi odwołanie,
czyli przy tabeli nadrzędnej. Strona
czyli przy tabeli nadrzędnej. Strona wiele
wiele
jest reprezentowana graficznie
jest reprezentowana graficznie
symbolem matematycznej
symbolem matematycznej "nieskończoności"
"nieskończoności"
i znajduje się przy tabeli, z której
i znajduje się przy tabeli, z której
wychodzi odwołanie, czyli przy tabeli podrzędnej.
wychodzi odwołanie, czyli przy tabeli podrzędnej.
Każde powiązanie da się sformułować za pomocą dwóch zdań określających związek
Każde powiązanie da się sformułować za pomocą dwóch zdań określających związek
między odpowiednimi obiektami – w obie strony (verb phrase, inverse verb
między odpowiednimi obiektami – w obie strony (verb phrase, inverse verb
phrase). Oto pełne wyrażenie schematu przedstawionego powyżej:
phrase). Oto pełne wyrażenie schematu przedstawionego powyżej:
Każdy student jest przypisany do jednej i tylko jednej grupy.
Każdy student jest przypisany do jednej i tylko jednej grupy.
Do każdej grupy może być przypisanych wielu studentów.
Do każdej grupy może być przypisanych wielu studentów.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
20
20
Referencyjne więzy spójności
Referencyjne więzy spójności
(więzy integralności)
(więzy integralności)
Przy definiowaniu powiązania między tabelami należy wybrać opcję
Przy definiowaniu powiązania między tabelami należy wybrać opcję
Wymuszaj więzy integralności
Wymuszaj więzy integralności
(
(Enforce Referential Integrity
Enforce Referential Integrity
). Jak
). Jak
już to powiedzieliśmy uprzednio, tabela występująca w powiązaniu po
już to powiedzieliśmy uprzednio, tabela występująca w powiązaniu po
stronie jeden jest nazywana w Accessie
stronie jeden jest nazywana w Accessie tabelą nadrzędną
tabelą nadrzędną
, natomiast
, natomiast
tabela po stronie wiele
tabela po stronie wiele tabelą podrzędną
tabelą podrzędną
. Utworzenie wiezów
. Utworzenie wiezów
integralności pomiędzy tabelami daje następujące efekty:
integralności pomiędzy tabelami daje następujące efekty:
Gdy do tabeli podrzędnej jest wstawiany nowy rekord z określoną
Gdy do tabeli podrzędnej jest wstawiany nowy rekord z określoną
wartością klucza obcego, musi istnieć rekord w tabeli nadrzędnej z tą
wartością klucza obcego, musi istnieć rekord w tabeli nadrzędnej z tą
wartością. Np. gdy do tabeli
wartością. Np. gdy do tabeli Studenci
Studenci
wpisujemy numer grupy w
wpisujemy numer grupy w
rekordzie opisującym studenta, grupa z tym numerem musi już
rekordzie opisującym studenta, grupa z tym numerem musi już
występować w tabeli
występować w tabeli Grupy
Grupy
, albo musimy wstawić wartość NULL.
, albo musimy wstawić wartość NULL.
Z tabeli nadrzędnej nie można usunąć rekordu, jeśli w tabeli
Z tabeli nadrzędnej nie można usunąć rekordu, jeśli w tabeli
podrzędnej istnieją odpowiadające mu rekordy. Np. nie można usunąć
podrzędnej istnieją odpowiadające mu rekordy. Np. nie można usunąć
Grupy
Grupy
, jeśli są do niej przypisani studenci.
, jeśli są do niej przypisani studenci.
W przypadku ustawienia opcji
W przypadku ustawienia opcji
Kaskadowe usuwanie powiązanych
Kaskadowe usuwanie powiązanych
rekordów
rekordów
(
(
Cascade Delete Related Records
Cascade Delete Related Records
), przy usuwaniu
), przy usuwaniu
rekordu z tabeli nadrzędnej automatycznie są usuwane wszystkie
rekordu z tabeli nadrzędnej automatycznie są usuwane wszystkie
powiązane rekordy z tabeli podrzędnej.
powiązane rekordy z tabeli podrzędnej.
W przypadku ustawienia opcji
W przypadku ustawienia opcji
Kaskadowa aktualizacja powiązanych
Kaskadowa aktualizacja powiązanych
rekordów
rekordów
(
(
Cascade Update Related Fields
Cascade Update Related Fields
), przy zmianie klucza
), przy zmianie klucza
głównego rekordu z tabeli nadrzędnej automatycznie są aktualizowane
głównego rekordu z tabeli nadrzędnej automatycznie są aktualizowane
klucze obce we wszystkich powiązanych rekordach z tabeli podrzędnej.
klucze obce we wszystkich powiązanych rekordach z tabeli podrzędnej.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
21
21
Kwerendy
Kwerendy
Kwerenda
Kwerenda
w MS Access jest to albo
w MS Access jest to albo
perspektywa
perspektywa
czyli
czyli kwerenda wybierająca
kwerenda wybierająca
, albo
, albo
instrukcja operowania danymi
instrukcja operowania danymi
czyli
czyli kwerenda funkcjonalna
kwerenda funkcjonalna
- np.
- np.
polecenie tworzenie tabeli, aktualizacja zawartości tabeli
polecenie tworzenie tabeli, aktualizacja zawartości tabeli
(usuwanie wierszy, aktualizacja danych, dopisywanie wierszy do
(usuwanie wierszy, aktualizacja danych, dopisywanie wierszy do
tabeli).
tabeli).
Access oferuje następujące
Access oferuje następujące
metody tworzenia kwerend:
metody tworzenia kwerend:
Przez użycie
Przez użycie
Konstruktora
Konstruktora
(siatki) kwerendy (postać graficzna),
(siatki) kwerendy (postać graficzna),
Przez napisanie
Przez napisanie
Instrukcji
Instrukcji
SQL
SQL
w trybie tekstowym,
w trybie tekstowym,
Przez procedury
Przez procedury
języka
języka
VBA
VBA
.
.
Niektóre rodzaje kwerend nie są realizowane przez postać graficzną.
Niektóre rodzaje kwerend nie są realizowane przez postać graficzną.
Metody 2 i 3 będą omówione na dalszych wykładach.
Metody 2 i 3 będą omówione na dalszych wykładach.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
22
22
Kwerendy
Kwerendy
Konstruktor kwerend („siatka” kwerend)
Konstruktor kwerend („siatka” kwerend)
Przy tworzeniu kwerendy wybieramy jej typ z menu
Przy tworzeniu kwerendy wybieramy jej typ z menu Kwerenda
Kwerenda
(
(Query
Query
) –
) –
domyślnie jest to
domyślnie jest to Kwerenda
Kwerenda
wybierająca
wybierająca
(
(Select
Select
Query
Query
). W przypadku
). W przypadku
wyboru innego niż domyślny - MS Access samoczynnie dopasowuje
wyboru innego niż domyślny - MS Access samoczynnie dopasowuje
opcje konstruktora kwerend, wyświetlając niezbedne wiersze siatki
opcje konstruktora kwerend, wyświetlając niezbedne wiersze siatki
kwerendy.
kwerendy.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
23
23
Kwerendy
Kwerendy
Wynik działania kwerendy wybierającej
Wynik działania kwerendy wybierającej
Kwerenda wybierająca (select query)
Kwerenda wybierająca (select query)
W wyniku wykonania kwerendy wybierającej otrzymuje się tak zwany
W wyniku wykonania kwerendy wybierającej otrzymuje się tak zwany dynamiczny
dynamiczny
zestaw wyników
zestaw wyników
, który ma postać tabeli. Nie jest on zapamiętywany na stałe w
, który ma postać tabeli. Nie jest on zapamiętywany na stałe w
bazie danych, tylko zostaje wyświetlony w postaci arkusza danych.
bazie danych, tylko zostaje wyświetlony w postaci arkusza danych.
Kwerenda wybierająca w MS Access odpowiada instrukcji języka SQL
Kwerenda wybierająca w MS Access odpowiada instrukcji języka SQL
rozpoczynającej się od słowa kluczowego
rozpoczynającej się od słowa kluczowego
SELECT.
SELECT.
Typ złączania wierszy można ustalać dla każdej kwerendy wybierającej
Typ złączania wierszy można ustalać dla każdej kwerendy wybierającej
niezależnie od typu ustalonego przy definiowaniu powiązań między tabelami w
niezależnie od typu ustalonego przy definiowaniu powiązań między tabelami w
oknie Relacje (Relationships), który jest zawsze typem domyślnym.
oknie Relacje (Relationships), który jest zawsze typem domyślnym.
Wynik wykonania kwerendy wybierającej nie jest zapisywany na stałe,
Wynik wykonania kwerendy wybierającej nie jest zapisywany na stałe,
natomiast każda kwerenda (w sensie „przepisu realizacji zadania”) może
natomiast każda kwerenda (w sensie „przepisu realizacji zadania”) może
zostać nazwana i zapisana w bazie jako obiekt swojej klasy.
zostać nazwana i zapisana w bazie jako obiekt swojej klasy.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
24
24
Kwerendy
Kwerendy
Kwerenda usuwająca (Delete query)
Kwerenda usuwająca (Delete query)
Kwerenda usuwająca (Delete Query)
Kwerenda usuwająca (Delete Query)
Jej działanie
Jej działanie
powoduje usunięcie wierszy, które spełniająją zadeklarowane kryteria
powoduje usunięcie wierszy, które spełniająją zadeklarowane kryteria
np.
np.
Rekrutacja
Rekrutacja
< 2000-07-01 AND IsNull(
< 2000-07-01 AND IsNull(
Obrona
Obrona
)
)
Kwerenda usuwająca w MS Access odpowiada instrukcji języka SQL
Kwerenda usuwająca w MS Access odpowiada instrukcji języka SQL
rozpoczynającej się od słowa kluczowego
rozpoczynającej się od słowa kluczowego
DELETE.
DELETE.
Uwaga:
Uwaga:
Kwerenda usuwająca jest definiowana na jednej tabeli!!! Usunięcie
Kwerenda usuwająca jest definiowana na jednej tabeli!!! Usunięcie
danych z kilku tabel naraz można osiągnąć poprzez wybór warunku
danych z kilku tabel naraz można osiągnąć poprzez wybór warunku
kaskadowego usuwania powiązanych rekordów
kaskadowego usuwania powiązanych rekordów
w definicji związku
w definicji związku
pomiędzy tabelami.
pomiędzy tabelami.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
25
25
Kwerendy
Kwerendy
Kwerenda dołączająca (Append query)
Kwerenda dołączająca (Append query)
Kwerenda dołączająca (Append Query)
Kwerenda dołączająca (Append Query)
Wynikiem uruchomienia kwerendy dołączającej jest dopisanie wiersza (lub
Wynikiem uruchomienia kwerendy dołączającej jest dopisanie wiersza (lub
wierszy) do określonej tabeli (lub kwerendy), np. wstawienie do tabeli
wierszy) do określonej tabeli (lub kwerendy), np. wstawienie do tabeli
Studenci
Studenci
wiersza
wiersza
(„Rafał","Kotliński", #2004-10-05#",115
(„Rafał","Kotliński", #2004-10-05#",115
)
)
.
.
Tworząc kwerendę dołaczającą określamy tabelę (lub kwerendę!), do
Tworząc kwerendę dołaczającą określamy tabelę (lub kwerendę!), do
której będą dopisywane nowe wiersze. Źródłem danych dla pojedynczego
której będą dopisywane nowe wiersze. Źródłem danych dla pojedynczego
wiersza mogą być wartości wypisane wprost na siatce kwerend (powyższy
wiersza mogą być wartości wypisane wprost na siatce kwerend (powyższy
przykład). Może nim być też inna tabela lub kwerenda – wówczas z jej
przykład). Może nim być też inna tabela lub kwerenda – wówczas z jej
struktury wybieramy pola, których wartości mają zostać dopisane do
struktury wybieramy pola, których wartości mają zostać dopisane do
wcześniej zdefiniowanej tabeli. Obie metody mogą być ze sobą łączone.
wcześniej zdefiniowanej tabeli. Obie metody mogą być ze sobą łączone.
Kwerenda dołączająca w MS Access odpowiada instrukcji języka SQL
Kwerenda dołączająca w MS Access odpowiada instrukcji języka SQL
rozpoczynającej się od słowa kluczowego
rozpoczynającej się od słowa kluczowego
INSERT INTO.
INSERT INTO.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
26
26
Kwerendy
Kwerendy
Kwerenda aktualizująca (Update query)
Kwerenda aktualizująca (Update query)
Kwerenda aktualizująca
Kwerenda aktualizująca
(
(Update Query
Update Query
) dokonuje zmian danych w
) dokonuje zmian danych w
wierszach tabeli, np. wstawienie jako daty obrony wartości
wierszach tabeli, np. wstawienie jako daty obrony wartości
#2005-
#2005-
03-05#
03-05#
studenta o numerze indeksu „
studenta o numerze indeksu „
S2121
S2121
”. Aktualizacja danych
”. Aktualizacja danych
dotyczy wszystkich rekordów spełniających zadane kryteria.
dotyczy wszystkich rekordów spełniających zadane kryteria.
Kwerenda dołączająca w MS Access odpowiada instrukcji języka
Kwerenda dołączająca w MS Access odpowiada instrukcji języka
SQL rozpoczynającej się od słowa kluczowego
SQL rozpoczynającej się od słowa kluczowego
UPDATE
UPDATE
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
27
27
Typy złączania wierszy
Typy złączania wierszy
W perspektywach, których definicja jest oparta na powiązaniu między
W perspektywach, których definicja jest oparta na powiązaniu między
tabelami istotny jest
tabelami istotny jest typ złączania
typ złączania
(
(sprzężenia
sprzężenia
), określający sposób
), określający sposób
złączania wierszy z obu tabel. Istnieją trzy typy złączeń - pierwszy z
złączania wierszy z obu tabel. Istnieją trzy typy złączeń - pierwszy z
nich jest domyślny.
nich jest domyślny.
1.
1.
Uwzględnienie tylko rekordów, dla których połączone pola z obu tabel
Uwzględnienie tylko rekordów, dla których połączone pola z obu tabel
są równe -
są równe - złączenie wewnętrzne (inner join)
złączenie wewnętrzne (inner join)
. W powyższym
. W powyższym
przykładzie łączone są grupy studenckie z przypisanymi do nich
przykładzie łączone są grupy studenckie z przypisanymi do nich
studentami. Jeśli student nie został zapisany do żadnej grupy, nie jest
studentami. Jeśli student nie został zapisany do żadnej grupy, nie jest
uwzględniany. Nie uwzględniana jest również grupa, do której nie
uwzględniany. Nie uwzględniana jest również grupa, do której nie
zapisano żadnego studenta.
zapisano żadnego studenta.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
28
28
Typy złączania wierszy
Typy złączania wierszy
2.
2.
Uwzględnienie wszystkich rekordów z tabeli nadrzędnej i tylko
Uwzględnienie wszystkich rekordów z tabeli nadrzędnej i tylko
tych rekordów z tabeli podrzędnej, dla których istnieje powiązany
tych rekordów z tabeli podrzędnej, dla których istnieje powiązany
z nimi rekord w tabeli nadrzędnej -
z nimi rekord w tabeli nadrzędnej - lewostronne złączenie
lewostronne złączenie
zewnętrzne (lefy join)
zewnętrzne (lefy join)
. W naszym przykładzie obejmuje również
. W naszym przykładzie obejmuje również
Grupy
Grupy
, w których nie ma studentów, ale nie obejmuje
, w których nie ma studentów, ale nie obejmuje studentów
studentów
,
,
którzy nie są zapisani do określonych
którzy nie są zapisani do określonych Grup
Grup
.
.
opr. Lech Banachowski, Krzysztof Mate
opr. Lech Banachowski, Krzysztof Mate
jewski
jewski
29
29
Typy złączania wierszy
Typy złączania wierszy
3.
3.
Uwzględnienie wszystkich rekordów z tabeli podrzędnej i tylko
Uwzględnienie wszystkich rekordów z tabeli podrzędnej i tylko
tych rekordów z tabeli nadrzędnej, dla których istnieje powiązany
tych rekordów z tabeli nadrzędnej, dla których istnieje powiązany
z nimi rekord w tabeli podrzędnej -
z nimi rekord w tabeli podrzędnej - prawostronne złączenie
prawostronne złączenie
zewnętrzne (right join)
zewnętrzne (right join)
.
.
Teraz uwzględnia również
Teraz uwzględnia również studentów
studentów
nie mających przydziału do
nie mających przydziału do grupy
grupy
,
,
ale już nie obejmuje
ale już nie obejmuje grup
grup
, w
, w
których nie ma
których nie ma studentów
studentów
).
).
opr. Lech Banachowski, Krzysztof Matejews
opr. Lech Banachowski, Krzysztof Matejews
ki
ki
30
30
Koniec wykładu IV
Koniec wykładu IV
Do zobaczenia na wykładzie V
Do zobaczenia na wykładzie V