Lekcja 7 – Relacje w bazie danych
Po zaprojektowaniu różnych tabel dla poszczególnych tematów w bazie danych Microsoft Access (baza danych programu Microsoft Access: Kolekcja danych i obiektów (takich jak tabele, kwerendy i formularze) związanych z określonym tematem lub przeznaczonych do określonego celu. Danymi zarządza aparat bazy danych Microsoft Jet.), potrzebny jest sposób powiązania zawartych w nich informacji. Pierwszym krokiem w tym procesie jest zdefiniowanie relacji (relacja: Skojarzenie ustanowione między wspólnymi polami (kolumnami) w dwóch tabelach. Relacja może być typu jeden-do-jednego, jeden-do-wielu lub wiele-do-wielu.) między tabelami. Po jego wykonaniu można tworzyć kwerendy, formularze i raporty pozwalające wyświetlać za jednym razem informacje z różnych tabel. Na przykład poniższy formularz zawiera informacje z czterech tabel:
Tabela Klienci
Tabela Zamówienia
Tabela Produkty
Tabela Opisy zamówień
Nazwa klienta w polu Faktura dla jest pobierana z tabeli Klienci, identyfikator zamówienia i data zamówienia z tabeli Zamówienia, nazwa produktu z tabeli Produkty, a cena jednostkowa i ilość z tabeli Opisy zamówień. Te tabele są powiązane ze sobą wzajemnie w różny sposób w celu przekazywania z poszczególnych tabel do formularza.
Jak działają relacje
W poprzednim przykładzie pola w czterech tabelach muszą być uporządkowane tak, aby pokazywały informacje o tym samym zamówieniu. Taką koordynację uzyskuje się dzięki relacjom między tabelami. Relacja działa poprzez dopasowywanie danych w polach klucza— zwykle są to pola o tej samej nazwie w obu tabelach. W większości przypadków dopasowywane pola to klucz podstawowy (klucz podstawowy: Jedno lub większa liczba pól (kolumn), których wartości jednoznacznie identyfikują każdy rekord w tabeli. Klucz podstawowy nie dopuszcza wartości Null i zawsze musi mieć unikatowy indeks. Klucz podstawowy służy do wiązania tabeli z kluczami obcymi w innych tabelach.) z jednej tabeli, który dostarcza unikatowego identyfikatora dla każdego rekordu, oraz klucz obcy (klucz obcy: Jedno lub większa liczba pól tabeli (kolumn), które odwołują się do pola lub pól klucza podstawowego w innej tabeli. Klucz obcy wskazuje, w jaki sposób tabele są powiązane.) w drugiej tabeli. Na przykład rekordy pracowników można skojarzyć z rekordami podlegających im zamówień przez utworzenie relacji między polami IDPracownika.
Pole IDPracownika występuje w obydwu tabelach— jako klucz podstawowy...
... oraz jako klucz obcy.
Relacja jeden-do-wielu
Najbardziej powszechnym typem relacji jest relacja jeden-do-wielu. W relacji jeden-do-wielu rekord w tabeli A może mieć wiele dopasowanych do niego rekordów z tabeli B, ale rekord w tabeli B ma tylko jeden dopasowany rekord w tabeli A.
Jeden dostawca...
... może dostarczać kilka produktów...
... lecz każdy produkt ma tylko jednego dostawcę.
Relacja wiele-do-wiele
W relacji wiele-do-wielu rekord w tabeli A może mieć wiele pasujących rekordów w tabeli B, a rekord w tabeli B może mieć wiele pasujących rekordów w tabeli A. Realizacja tego typu relacji możliwa jest tylko przez zdefiniowanie trzeciej tabeli (nazywanej tabelą sprzęgającą), której klucz podstawowy (klucz podstawowy: Jedno lub większa liczba pól (kolumn), których wartości jednoznacznie identyfikują każdy rekord w tabeli. Klucz podstawowy nie dopuszcza wartości Null i zawsze musi mieć unikatowy indeks. Klucz podstawowy służy do wiązania tabeli z kluczami obcymi w innych tabelach.) składa się z dwóch pól— kluczy obcych (klucz obcy: Jedno lub większa liczba pól tabeli (kolumn), które odwołują się do pola lub pól klucza podstawowego w innej tabeli. Klucz obcy wskazuje, w jaki sposób tabele są powiązane.) z obydwu tabel, A oraz B. Relacja wiele-do-wielu to w rzeczywistości dwie relacje jeden-do-wielu z trzecią tabelą. Na przykład tabele Zamówienia i Produkty powiązane są relacją wiele-do-wielu zdefiniowaną przez utworzenie dwóch relacji jeden-do-wielu z tabelą Opisy zamówień. Jedno zamówienie może zawierać wiele produktów, a każdy produkt może występować w wielu zamówieniach.
Klucz podstawowy z tabeli Zamówienia
Klucz podstawowy z tabeli Produkty
Jedno zamówienie może zawierać wiele produktów...
... a każdy produkt może występować w wielu zamówieniach.
Relacja jeden-do-jednego
W relacji jeden-do-jednego każdy rekord w tabeli A może mieć tylko jeden pasujący rekord w tabeli B, a rekord w tabeli B może mieć tylko jeden pasujący rekord w tabeli A. Relacja tego typu jest rzadko spotykana, ponieważ większość informacji tak powiązanych znajdowałaby się w jednej tabeli. Relacji jeden-do-jednego można używać do podziału tabeli z wieloma polami, do odizolowania długiej tabeli ze względów bezpieczeństwa, albo do przechowania informacji odnoszących się tylko do podzbioru tabeli głównej. Na przykład, można by utworzyć tabelę do wyszukiwania pracowników uczestniczących w rozgrywkach piłkarskich. Dla każdego piłkarza w tabeli Piłkarze istnieje jeden pasujący rekord w tabeli Pracownicy.
Każdy piłkarz ma jeden pasujący rekord w tabeli Pracownicy.
Ten zestaw wartości to podzbiór oparty na polu IDPracownika i tabeli Pracownicy.
Definiowanie relacji— informacje
Rodzaj relacji tworzonych w programie Microsoft Access zależy od zdefiniowania pól powiązanych relacją.
· Relacja jeden-do-wielu jest tworzona, jeśli tylko jedno z powiązanych pól jest kluczem podstawowym (klucz podstawowy: Jedno lub większa liczba pól (kolumn), których wartości jednoznacznie identyfikują każdy rekord w tabeli. Klucz podstawowy nie dopuszcza wartości Null i zawsze musi mieć unikatowy indeks. Klucz podstawowy służy do wiązania tabeli z kluczami obcymi w innych tabelach.) lub ma indeks unikatowy (indeks unikatowy: Indeks definiowany przez ustawienie właściwości Indeksowany pola na Tak (Bez duplikatów). Indeks unikatowy nie zezwala na duplikowanie pozycji w polu indeksowanym. Pole ustawione jako klucz podstawowy jest automatycznie definiowane jako unikatowe.).
· Relacja jeden-do-jednego jest tworzona, jeśli oba powiązane pola są kluczami podstawowymi lub mają indeksy unikatowe.
· Relacja wiele-do-wielu jest w rzeczywistości połączeniem dwóch relacji jeden-do-wielu, za pomocą trzeciej tabeli, której klucz podstawowy składa się z dwóch pól— kluczy obcych (klucz obcy: Jedno lub większa liczba pól tabeli (kolumn), które odwołują się do pola lub pól klucza podstawowego w innej tabeli. Klucz obcy wskazuje, w jaki sposób tabele są powiązane.) z dwóch pozostałych tabel.
Można również utworzyć relację zwrotną do tej samej tabeli. Znajduje to zastosowanie przy tworzeniu odnośników do tej samej tabeli. Na przykład w tabeli Pracownicy można zdefiniować relację między polami IDPracownika a Szef, tak aby pole Szef mogło wyświetlić dane pracowników z pasującego pola IDPracownika.
Uwaga Po przeciągnięciu pola, które nie jest kluczem podstawowym i nie ma indeksu unikatowego, do innego pola, które również nie jest kluczem podstawowym i nie ma indeksu unikatowego, utworzona zostanie relacja nieokreślona. W kwerendach zawierających tabele z relacjami nieokreślonymi wyświetlana jest domyślna linia sprzężenia (sprzężenie: Skojarzenie między polem w jednej tabeli lub kwerendzie i polem o takim samym typie danych w innej tabeli lub kwerendzie. Sprzężenia informują program, jak dane są powiązane ze sobą. Rekordy, które nie pasują do siebie, mogą być uwzględniane lub wykluczane, w zależności od typu sprzężenia.) między tabelami, ale nie są wymuszane więzy integralności (integralność referencyjna: Reguły, których należy przestrzegać, aby zachować zdefiniowane relacje między tabelami podczas wprowadzania lub usuwania rekordów.) i nie ma gwarancji, że rekordy w tabelach są unikatowe.
Więzy integralności
Więzy integralności stanowią system reguł zapewniający poprawność związków między rekordami tabel powiązanych relacją i uniemożliwiający przypadkowe usunięcie lub zmianę powiązanych danych. Więzy integralności wymagają spełnienia poniższych warunków:
· Pole w tabeli podstawowej (tabela podstawowa: Strona „jeden” w parze tabel tworzących relację typu jeden-do-wielu. Tabela podstawowa powinna mieć klucz podstawowy, a rekordy muszą być unikatowe.) jest kluczem podstawowym (klucz podstawowy: Jedno lub większa liczba pól (kolumn), których wartości jednoznacznie identyfikują każdy rekord w tabeli. Klucz podstawowy nie dopuszcza wartości Null i zawsze musi mieć unikatowy indeks. Klucz podstawowy służy do wiązania tabeli z kluczami obcymi w innych tabelach.) lub ma indeks unikatowy (indeks unikatowy: Indeks definiowany przez ustawienie właściwości Indeksowany pola na Tak (Bez duplikatów). Indeks unikatowy nie zezwala na duplikowanie pozycji w polu indeksowanym. Pole ustawione jako klucz podstawowy jest automatycznie definiowane jako unikatowe.).
· Powiązane pola mają ten sam typ danych (typ danych: Cecha pola określająca, jakiego typu dane mogą być w nim przechowywane. Do typów danych należą: Boolean, Integer, Long, Currency, Single, Double, Date, String i Variant (domyślny).). Istnieją dwa wyjątki. Pole typu Autonumerowanie (typ danych Autonumerowanie: W bazie danych programu Microsoft Access jest to typ danych pola, który automatycznie przechowuje unikatowy numer każdego rekordu dodawanego do tabeli. Generowane są trzy rodzaje numerów: kolejne, losowe i identyfikatory replikacji.) może być powiązane z polem typu Liczba, którego właściwość FieldSize ma wartość Liczba całkowita długa, a pole typu Autonumerowanie, którego właściwość FieldSize ma wartość Identyfikator replikacji, może być powiązane z polem typu Liczba, którego właściwość FieldSize ma wartość Identyfikator replikacji.
· Obie tabele należą do jednej bazy danych programu Microsoft Access. Jeśli tabele są tabelami połączonymi (tabela połączona: Tabela, do której program Access może uzyskać dostęp, ale która jest przechowywana w pliku poza otwartą bazą danych. W tabeli dołączonej można dodawać, usuwać i edytować rekordy, ale nie można zmienić jej struktury.), muszą być tabelami programu Microsoft Access i, aby określić więzy integralności, trzeba otworzyć bazę danych, w której są one przechowywane. Więzów integralności nie można wymusić dla tabel połączonych z baz danych innego formatu.
Przy stosowaniu więzów integralności obowiązują następujące reguły:
· W polu klucza obcego (klucz obcy: Jedno lub większa liczba pól tabeli (kolumn), które odwołują się do pola lub pól klucza podstawowego w innej tabeli. Klucz obcy wskazuje, w jaki sposób tabele są powiązane.) w tabeli sprzężonej nie można wprowadzać wartości nieistniejących w polu klucza podstawowego tabeli podstawowej. Można jednak w polu klucza obcego wprowadzić wartość Null (Null: Wartość, którą można wprowadzać w polach lub używać w wyrażeniach i kwerendach, aby wskazać dane brakujące lub nieznane. W języku Visual Basic wartość Null wskazuje słowo kluczowe Null. Niektóre pola, na przykład pola klucza podstawowego, nie mogą zawierać wartości Null.) oznaczającą, że rekord nie jest powiązany. Na przykład: nie może istnieć faktura powiązana z nieistniejącym klientem, może natomiast istnieć faktura niepowiązana z żadnym klientem— to właśnie oznacza wartość Null w polu IDKlienta.
· W tabeli podstawowej nie można usunąć rekordu, jeśli istnieją powiązane z nim rekordy w tabeli sprzężonej. Na przykład: nie można usunąć rekordu pracownika z tabeli Pracownicy, jeśli istnieją faktury przypisane temu pracownikowi w tabeli Zamówienia.
· W tabeli podstawowej nie można zmienić wartości klucza podstawowego, jeśli istnieją powiązane z nim rekordy. Na przykład: nie można zmienić wartości IDPracownika w tabeli Pracownicy, jeśli istnieją faktury przypisane temu pracownikowi w tabeli Zamówienia.
Ćwiczenia praktyczne:
1. otwórz bazę egzamin.mdb, włącz w niej makra
2. z zakładki narzędzia bazy danych wybierz relacje – jest już utworzona jedna relacja 1 do wielu dla tabel aktor i wideo – edytuj ją i odznacz okienko wymuszania więzów integralności. Co zmieniło się w relacji? Przywróć ponownie więzy integralności w tej relacji
3. wprowadź do relacji pozostałe tabele (prawoklik na tle okna relacji/pokaż tabele/klikamy na interesującej nas tabeli i dodaj, na końcu zamknij)
4. jakie inne relacje 1 do wielu można utworzyć dla tych tabel – utwórz je, wymuszając więzy integralności. Weź pod uwagę, że np. jeden reżyser może nakręcić wiele filmów, jedna aktorka może zagrać w wielu filmach
5. utwórz relację miedzy tabelami aktor i kontakty wykorzystując pola IDaktora i IDkontaktu, spróbuj wymusić więzy integralności – jaka relacja została utworzona? co ona oznacza?
6. czy można utworzyć relację 1 do 1 dla tabel aktorka i reżyser korzystając z pól imię? czy można wymusić więzy integralności tej relacji? co to oznacza?
Włącz bazę dzienniczek ucznia – sprawdź jakie relacje są w niej utworzone? W jakim celu zostały one utworzone?
greysanatomy20