Microsoft Access - tabele i kwerendy

opr. Lech Banachowski, Krzysztof Matejewski

Relacyjne bazy danych

Wykład IV

Microsoft Access – tabele i kwerendy

opr. Lech Banachowski, Krzysztof Matejewski

Streszczenie wykładu IV

W wykładzie 4 przedstawiona jest realizacja zasad relacyjnego modelu danych

w konkretnym systemie baz danych – Microsoft Access. W ramach

jednego systemu projektuje się tabele – w

widoku projekt

oraz

wprowadza się i przetwarza dane – w widoku

arkusz danych

. Podobnie

rzecz ma się z perspektywami, nazywanymi w MS Access

kwerendami

Projektuje się je w widoku projekt (lub bezpośrednio w składni języka SQL),

a ogląda wyniki i wykonuje operacje na danych – w widoku arkusza danych.

opr. Lech Banachowski, Krzysztof Matejewski

Dlaczego MS Access ?

MS Access został wybrany do roli dydaktyzcznej w PJWSTK z uwagi na szereg

cech, wyróżniających go spośród innych Systemów Baz Danych.

• W łatwy, oparty o interfejs graficzny sposób buduje się w nim obiekty bazy

danych, oraz definiuje wszelkie między nimi zależności.

• Umożliwia korzystanie z niego nawet niezaawansowanym użytkownikom,

ale może też stać się bardzo mocnym narzędziem do budowy

zaawansowanej aplikacji użytkownika.

• Operuje bardzo dobrą implementacją języka SQL, bliską standardu (ANSI

92)

• Praktycznie nie wymaga administrowania RDB.
• Wyposażony jest w język VBA – niezwykle mocne narzędzie

programistyczne

Ma też oczywiste niedostatki, jak choćby bezpieczeństwo, ale w tym

przypadku może spełniać zupełnie dobrze rolę dydaktyczną i testową,

świetnie się też nadaje do tworzenia prototypów baz oraz aplikacji

klienckich współpracujących z innymi bazami danych.

opr. Lech Banachowski, Krzysztof Matejewski

Program MS Access

Program MS Access zawiera:
• Narzędzia służące do zakładanie relacyjnej bazy danych, składającej się z

tabel i perspektyw (kwerend);

• interfejs graficzny do obiektów bazy danych - tabel i perspektyw;
• interfejs graficzny dla aplikacji bazy danych (formularze, raporty, strony

WWW);

• środowisko do programowania aplikacji baz danych (makra, język VBA -

Visual Basic for Applications, język SQL).

Jak widać MS Access jest narzędziem uniwersalnym, pozwalającym realizować

zarówno funkcje serwera bazy danych (w ograniczonym zakresie), jak też

funkcje klienta. Zwłaszcza tworzenie tych ostatnich Access umożliwia w

sposób bardzo bogaty i elastyczny. Należy tu zwrócić uwagę na bardzo

poprawną (w dużym stopniu zgodną ze standardem) implementację języka

SQL, jak też ogromne możliwości tworzenia funkcjonalnych interfejsów

udostępniane przez język VBA (dialekt VB wersja 6.0).

Począwszy od wersji 2000, Access wyposażony jest w bardzo wydajny „silnik

bazy danych” o nazwie Jet 4 (wersja rozwojowa silnika Jet 3, w który

wyposażony był Access 97).

opr. Lech Banachowski, Krzysztof Matejewski

Okno bazy danych

Zasadniczym oknem dialogowym programu MS Access jest

okno bazy

danych

. W oknie tym są wyświetlane listy nazw obiektów bazy danych,

pogrupowane w klasy obiektów. Dla tabel jest możliwe:

• utworzenie nowej tabeli - przycisk

Nowy (New)

• przejście do widoku projektu istniejącej tabeli - przycisk

Projektuj

(Design)

• przejście do widoku arkusza danych istniejącej tabeli - przycisk

Otwórz

(Open)

• usunięcie istniejącej tabeli (ikonka reprezentująca przekreślenie)

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access

Tabele w Accessie stanowią:
• strukturę logiczną przechowywania danych w bazie danych;
• źródło danych dla innych obiektów, takich jak kwerendy (perspektywy),

formularze i raporty,

• element interfejsu użytkownika.
Zwłaszcza na tę ostatnią właściwość warto zwrócić uwagę. Access w sposób

bardzo łatwy udostępnia zawartość tabel – wyposażony jest w sprawny

interfejs prezentujący zawarte w tabelach dane. 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 oraz struktury samych tabel (!).

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access - widok projektu

widoku projektu

(

Design

View

) tabeli określamy schemat tabeli, czyli

definiujemy kolumny (pola), z których będą się składać wiersze (rekordy)

tej tabeli, typy danych (dziedziny) przewidziane dla poszczególnych pól,

oraz wszystkie pozostałe właściwości kolumn oraz całej tabeli.

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access - widok arkusza danych

Po zdefiniowaniu schematu tabeli wprowadzamy do niej dane i wyświetlamy je

w widoku

Arkusza Danych

(

Datasheet

). W widoku tym można

wykonywać podstawowe operacje na wierszach tabeli:

•

przeglądać wiersze (scroll),

•

wyszukiwać (filtrować) wiersze (filter),

•

dopisywać nowy wiersz (insert),

•

usuwać wybrany wiersz (delete),

•

aktualizować wartości w wybranym wierszu (update),

•

sortować wiersze (sort),

•

wyszukiwać danych o określonych wartościach (search).

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access - typy danych (Jet 4.0)

• Tekst

(Text) - napisy do 255 znaków (Unicode)

• Memo

(albo Text bez określenia długości) - długie teksty (do 2.14 GB, czyli

ok 1.07 * 10

znaków),

• Liczba

(Number) - bajt, liczba całkowita (Integer), liczba całkowita długa

(Long Integer), pojedyncza precyzja (Single), podwójna precyzja

(Double),

• Data/Godzina

(Data/Time) - np. "22.06.97" lub "22.06.97 12:12:34",

• Waluta

(Currency) - np. "200,25 zł",

• Autonumer

(Autonumber) - wartości liczbowe zwiększane automatycznie

dla każdego nowego rekordu lub generowane losowo; fizycznie są to

wartości typu Long Integer,

• Tak/Nie

(Yes/No) - wartości logiczne (boolean),

• Obiekt

OLE

(OLE Object) - obiekt jak np. grafika, dokument Worda,

arkusz Excela, obsługiwany przez inny program systemu Windows.

• Hiperłącze

(Hyperlink) - adres obiektu w sieci, na przykład pliku lub

strony WWW.

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access

zmiana schematu tabeli

Używając tego samego interfejsu ekranowego, co przy tworzeniu tabeli (widok

projektu tabeli), można dokonywać zmian schematu tabeli takich jak:

• Dodanie nowego pola.
• Usunięcie pola – odwołanie do tego pola trzeba usunąć samemu, także z

kwerend, formularzy i raportów. W przypadku pozostawienia 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 komunikat z żadaniem podania

brakującej wartości.

• Zmiana nazwy pola - po dokonaniu zmiany nazwy w widoku projektu,

trzeba ją zmienić samemu również w kwerendach, formularzach i raportach

(konsekwencje braku tych zmian j.w.).

• Zmiana typu danych z konwersją zapisanych danych, np. zwiększenie

rozmiaru, zmiana tekstu na typ Memo lub dowolnego 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że dokonać odpowiedniej

konwersji istnieją dwie możliwości – albo nie dokonywać zmiany typu

danych (rozwiązanie trywialne), albo wartości, których nie można

przekształcić, ustawić na Null.

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access - właściwości kolumny

Oprócz podania typu danych, oraz znaczenia (opisu), określa się dodatkowe

właściwości kolumny mające charakter

więzów spójności

• rozmiar pola (Field Size);

• reguła poprawności (Validation Rule) np. w tabeli

Studenci

Rekrutacja >= Date()

• wymagane (Required)- czy wartość musi być wprowadzona (czy NULL

jest dozwolone);

• zerowa długość (Allow Zero Legth) - czy wartość może być pustym

napisem (dla typu danych tekst i memo);

• format (Format) - format wyświetlania na ekranie (ale nie dla wszystkich

typów);

• miejsca dziesiętne (Decimal Places) - ile miejsc po kropce ma być

wyświetlane na ekranie (dla kolumn liczbowych);

• maska wprowadzania (Input Mask) - wyświetlanie znaków

formatujących przy wprowadzaniu wartości do pola;

• tytuł (Caption) - nazwa tego pola w widoku arkusza danych, formularzu

lub raporcie;

• wartość domyślna (Default Value) - wartość automatycznie wstawiana

do pola gdy użytkownik jej nie podał;

• komunikat o błędzie (Validation Text) - komunikat wypisywany na

ekranie gdy dane nie spełniają reguł poprawności;

• indeksowane (Indexed) - czy na polu ma być założony indeks.

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access - więzy spójności globalne dla

tabeli

Reguły poprawności dotyczące całej tabeli np. dla pól typu DateTime (tabela

Studenci

)

Rekrutacja < Obrona

. Dostęp z menu Widok (View) ->

Właściwości tabeli (Properties) lub przycisk Właściwości na pasku

narzędzi. Można zdefiniować własny Komunikat o błędzie (Validation

text), wyświetlany w postaci komunikatu ekranowego, w przypadku

naruszenia zadeklarowanych reguł. Identycznie działają reguły poprawności

wraz z komunikatami o błędach definiowane na poziomie pojedynczej

kolumny w tabeli.

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access - kreator odnośników

Odnośnik

(

LookUp

) określa listę wartości, bądź kolumnę innej tabeli

(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.

Pole z odnośnikiem jest wyświetlane w postaci listy rozwijanej.

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, zamiast

wyświetlać identyfikatory wykładowców, na liście rozwijanej pojawiają się

ich imiona i nazwiska.

Wartości wyświetlane w polu

Id_Wykladowcy

(w tabeli

Katedry

) pochodzą z

tabeli

Wykladowcy

. Sam identyfikator wykładowcy, będący kolumną

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).

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access

lista odnośnika

Pole

Kierownik katedry

jest typu odnośnik. Wyświetlane wartości (

Nazwisko

Imię

)

pochodzą z tabeli

Wykladowcy

. Wartością fizycznie zapisywaną w bazie danych

jest identyfikator wykładowcy (

Id_wykladowcy

). W polu

Kierownik

katedry

kolumna ta została ukryta (szerokość jej wyświetlania została ustawiona na 0

cm), natomiast wyświetlane są wartości imion i nazwisk kierowników katedr.

Kolumna

Id_wykladowcy

pełni natomiast rolę

Kolumny

Wiążącej

(

Bound

Column

) odnośnika.

opr. Lech Banachowski, Krzysztof Matejewski

Tabele w MS Access - indeksy

Indeks służy przyśpieszeniu wyszukiwania rekordów w oparciu o wartości w

jednym lub kilku polach. Access samodzielnie zakłada indeksy na kluczu

głównym tabeli (na wszystkich jego polach – jeden indeks). Dostęp do listy

indeksów uzyskujemy albo z menu Widok -> Indeksy (Indexes), albo przez

przycisk Indeksy z paska narzędzi.

opr. Lech Banachowski, Krzysztof Matejewski

Powiązania między tabelami

Tworząc schemat bazy danych należy zaplanować

powiązania

(

związki

określane w MS Access jako

relacje

) pomiędzy danymi zapisanymi w

tabelach. Ze względu na to, że przy projektowaniu bazy danych termin

relacja

ma znaczenie matematycznej abstrakcji tabeli, w treści wykładu

używamy terminów

związek

lub

powiązanie

Związki tworzymy w celu:
• określenia referencyjnych więzów spójności między tabelami,

sprawdzanych przez system.

• automatycznego utworzenia warunku złączenia w perspektywie

(kwerendzie) korzystającej z powiązanych tabel.

• synchronizacji wyświetlania powiązanych ze sobą danych w

formularzach np. dla formularza

Katedry

automatycznego wyświetlania,

w podformularzu lub formularzu powiązanym, danych wszystkich

wykładowców – pracowników danej katedry (podobnie w podraporcie).

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ć klucz

główny, lub musi być na nich określony jednoznaczny (unikatowy) indeks.

• Tabela, do której prowadzi odwołanie nazywa się

tabelą nadrzędną

. Tabela,

z której wychodzi odwołanie nazywa się

tabelą podrzędną

• Powiązane pola muszą mieć ten sam typ danych.

opr. Lech Banachowski, Krzysztof Matejewski

Powiązania między tabelami

Diagram powiązań (schemat bazy danych)

W menu

Narzędzia

występuje opcja (ikona na pasku ikon)

Relacje

(

Relationships

), po wybraniu której, w osobnym okienku zostaje

wyświetlony schemat bazy danych. Ramki reprezentują

tabele

. Linie między

tabelami reprezentują

powiązania

(

związki

) między tabelami.

opr. Lech Banachowski, Krzysztof Matejewski

Powiązania między tabelami

Diagram powiązań (schemat bazy danych)

Każde powiązanie między tabelami ma dwie strony. Strona

jeden

jest

reprezentowana graficznie symbolem

i znajduje się przy tabeli, do której

prowadzi odwołanie, czyli przy tabeli nadrzędnej. Strona

wiele

jest

reprezentowana graficznie symbolem matematycznej

"nieskończoności"

znajduje się przy tabeli, z której 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 między odpowiednimi obiektami – w obie strony (verb phrase, inverse

verb phrase). Oto pełne wyrażenie schematu przedstawionego powyżej:

• Każdy student jest przypisany do jednej i tylko jednej grupy.

• Do każdej grupy może być przypisanych wielu studentów.

opr. Lech Banachowski, Krzysztof Matejewski

Referencyjne więzy spójności - (więzy integralności)

Przy definiowaniu powiązania między tabelami należy wybrać opcję

Wymuszaj więzy integralności

(

Enforce Referential Integrity

). Jak

już to powiedzieliśmy uprzednio, tabela występująca w powiązaniu po

stronie jeden jest nazywana w Accessie

tabelą nadrzędną

, natomiast

tabela po stronie wiele

tabelą podrzędną

. Utworzenie wiezów integralności

pomiędzy tabelami daje następujące efekty:

• 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ą. Np.

gdy do tabeli

Studenci

wpisujemy numer grupy w rekordzie opisującym

studenta, grupa z tym numerem musi już występować w tabeli

Grupy

, albo

musimy wstawić wartość NULL.

• 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ąć

Grupy

, jeśli są do

niej przypisani studenci.

• W przypadku ustawienia opcji Kaskadowe usuwanie powiązanych

rekordów (Cascade Delete Related Records), przy usuwaniu rekordu z

tabeli nadrzędnej automatycznie są usuwane wszystkie powiązane rekordy

z tabeli podrzędnej.

• W przypadku ustawienia opcji Kaskadowa aktualizacja powiązanych

rekordów (Cascade Update Related Fields), przy zmianie klucza

głównego rekordu z tabeli nadrzędnej automatycznie są aktualizowane

klucze obce we wszystkich powiązanych rekordach z tabeli podrzędnej.

opr. Lech Banachowski, Krzysztof Matejewski

Kwerendy

Kwerenda

w MS Access jest to albo

• perspektywa

czyli

kwerenda wybierająca

, albo

• instrukcja operowania danymi

czyli

kwerenda funkcjonalna

- np. polecenie

tworzenie tabeli, aktualizacja zawartości tabeli (usuwanie wierszy,

aktualizacja danych, dopisywanie wierszy do tabeli).

Access oferuje następujące metody tworzenia kwerend:
• Przez użycie Konstruktora (siatki) kwerendy (postać graficzna),
• Przez napisanie Instrukcji SQL w trybie tekstowym,
• Przez procedury języka VBA.
Niektóre rodzaje kwerend nie są realizowane przez postać graficzną.
Metody 2 i 3 będą omówione na dalszych wykładach.

opr. Lech Banachowski, Krzysztof Matejewski

Konstruktor kwerend („siatka” kwerend)

Przy tworzeniu kwerendy wybieramy jej typ z menu

Kwerenda

(

Query

) –

domyślnie jest to

Kwerenda wybierająca

(

Select

Query

). W przypadku

wyboru innego niż domyślny - MS Access samoczynnie dopasowuje opcje

konstruktora kwerend, wyświetlając niezbedne wiersze siatki kwerendy.

opr. Lech Banachowski, Krzysztof Matejewski

Wynik działania kwerendy wybierającej

Kwerenda wybierająca (select query)

W wyniku wykonania kwerendy wybierającej otrzymuje się tak zwany

dynamiczny zestaw wynikó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.

opr. Lech Banachowski, Krzysztof Matejewski

Wynik działania kwerendy wybierającej

Kwerenda wybierająca w MS Access odpowiada instrukcji języka SQL

rozpoczynającej się od słowa kluczowego SELECT.

• 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 oknie Relacje (Relationships), który jest zawsze typem
domyślnym.

• Wynik wykonania kwerendy wybierającej nie jest zapisywany na stałe,

natomiast każda kwerenda (w sensie „przepisu realizacji zadania”) może
zostać nazwana i zapisana w bazie jako obiekt swojej klasy.

opr. Lech Banachowski, Krzysztof Matejewski

Kwerenda usuwająca (Delete query)

Kwerenda usuwająca (Delete Query)

Jej działanie

powoduje usunięcie wierszy, które spełniająją zadeklarowane

kryteria np.

Rekrutacja < 2000-07-01 AND IsNull( Obrona )

Kwerenda usuwająca w MS Access odpowiada instrukcji języka SQL

rozpoczynającej się od słowa kluczowego DELETE.

opr. Lech Banachowski, Krzysztof Matejewski

Kwerenda usuwająca (Delete query)

Uwaga:

Kwerenda usuwająca jest definiowana na

jednej tabeli!!!

Usunięcie danych z kilku tabel naraz można

osiągnąć poprzez wybór warunku kaskadowego
usuwania powiązanych rekordów w definicji
związku pomiędzy tabelami.

opr. Lech Banachowski, Krzysztof Matejewski

Kwerenda dołączająca (Append query)

Kwerenda dołączająca (Append Query)

Wynikiem uruchomienia kwerendy dołączającej jest dopisanie wiersza (lub

wierszy) do określonej tabeli (lub kwerendy), np. wstawienie do tabeli

Studenci

wiersza („Rafał","Kotliński", #2004-10-05#",115).

opr. Lech Banachowski, Krzysztof Matejewski

Kwerenda dołączająca (Append query)

• 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 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
struktury  wybieramy  pola,  których  wartości  mają  zostać  dopisane  do
wcześniej zdefiniowanej tabeli. Obie metody mogą być ze sobą łączone.

• Kwerenda dołączająca w MS Access odpowiada instrukcji języka SQL

rozpoczynającej się od słowa kluczowego INSERT INTO.

opr. Lech Banachowski, Krzysztof Matejewski

Kwerenda aktualizująca (Update query)

• Kwerenda aktualizująca

(

Update Query

) dokonuje zmian danych w wierszach

tabeli, np. wstawienie jako daty obrony wartości #2005-03-05# studenta o

numerze indeksu „S2121”. Aktualizacja danych dotyczy wszystkich rekordów

spełniających zadane kryteria.

• Kwerenda dołączająca w MS Access odpowiada instrukcji języka SQL

rozpoczynającej się od słowa kluczowego UPDATE

opr. Lech Banachowski, Krzysztof Matejewski

Typy złączania wierszy

W perspektywach, których definicja jest oparta na powiązaniu między

tabelami istotny jest

typ złączania

(

sprzężenia

), określający sposób

złączania wierszy z obu tabel. Istnieją trzy typy złączeń - pierwszy z nich

jest domyślny.

Uwzględnienie tylko rekordów, dla których połączone pola z obu tabel są

równe -

złączenie wewnętrzne (inner join)

. W powyższym przykładzie

łączone są grupy studenckie z przypisanymi do nich 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 zapisano żadnego studenta.

opr. Lech Banachowski, Krzysztof Matejewski

Typy złączania wierszy

Uwzględnienie wszystkich rekordów z tabeli nadrzędnej i tylko tych

rekordów z tabeli podrzędnej, dla których istnieje powiązany z nimi rekord

w tabeli nadrzędnej -

lewostronne złączenie zewnętrzne (lefy join)

. W

naszym przykładzie obejmuje również

Grupy

, w których nie ma studentów,

ale nie obejmuje

studentów

, którzy nie są zapisani do określonych

Grup

opr. Lech Banachowski, Krzysztof Matejewski

Typy złączania wierszy

Uwzględnienie wszystkich rekordów z tabeli podrzędnej i tylko tych rekordów

z tabeli nadrzędnej, dla których istnieje powiązany z nimi rekord w tabeli

podrzędnej -

prawostronne złączenie zewnętrzne (right join)

. Teraz

uwzględnia również

studentów

nie mających przydziału do

grupy

, ale już nie

obejmuje

grup

, w których nie ma

studentów

opr. Lech Banachowski, Krzysztof Matejews

Koniec wykładu IV

Do zobaczenia na wykładzie V

Document Outline