sieci komputerowe i bazy danych

KATEDRA MECHANIKI I ROBOTYKI STOSOWANEJ
WYDZIAŁ BUDOWY MASZYN I LOTNICTWA, POLITECHNIKA RZESZOWSKA
SIECI KOMPUTEROWE I BAZY DANYCH
Laboratorium DB2:
TEMAT:
Relacyjne bazy danych
Cz. I - III
Cel laboratorium
Celem laboratorium jest zapoznanie studentów z podstawami relacyjnych baz danych
Wstęp
Relacyjne bazy danych są naturalnym rozszerzeniem koncepcji tabeli. Relacyjne bazy danych
zapobiegają konieczności powielania tych samych danych w wielu tabelach. Aktualizacja tak
skonstruowanej bazy jest nieskuteczna i uciążliwa. Przykładem tabel, gdzie mielibyśmy do
czynienia z nadmiarem danych są tabele przechowujące dane o sprzedażowe. W tabeli transakcje
chcemy mieć dostęp do danych o kontrahencie, który brał w niej udział, jednak nie należy dodawać
do tabeli transakcje kolumn dotyczących danych teleadresowych klienta. Takie podejście prowadzi
do refundacji danych, co ma negatywny wpływ na proces ich aktualizacji.
Lepszym rozwiązaniem jest wydzielenie danych o klientach do osobnej tabeli i w tabeli transakcje
odwoływać się do odpowiednich krotek tabeli klienci. Takie podejście sprawia, że tylko w jednym
miejscu aktualizujemy dane adresowe kontrahentów. Przykład takiego podejścia pokazano na rys. 1
Rysunek 1: Przykład relacji. (Rys. stworzony dzięki
oprogramowaniu MySQL Workbanch)
Jak wskazano w [1], relacyjna baza danych opera się na kilku zasadach:
•
wszystkie dane w relacyjne bazie danych są opart o dwuwymiarowe tabele
•
po wprowadzeniu danych możliwe jest ich porównywanie (nawet gdy pochodzą z innych
tabel) czy wiązanie
•
wykonywanie wszystkich operacji oparte jest na logice.
By mówić o relacji, potrzebne są co najmniej dwie tabele, między którymi istnieje relacja (więź)
(patrz rys. 1). Istnieje kilka rodzajów relacji:
•
jeden-do-jednego – relacja ta występuje wtedy, gdy pojedynczemu rekordowi w jednej
tabeli odpowiada pojedynczy rekord w drugiej tabeli. W rzeczywistych zastosowaniach
relacji jeden-do-jeden jest bardzo rzadka.
•
relacja jeden-do-wielu – relacja ta zachodzi, gdy pojedynczemu rekordowi z jednej tabeli
odpowiada jeden lub więcej rekordów z drugiej tabeli.
Rysunek 2: Przykład relacji jeden-do-wielu. (Rys. stworzony dzięki oprogramowaniu
MySQL Workbanch)
•
relacja wiele-do-wielu – Gdy wielu rekordom z pierwszej tabeli odpowiada wiele rekordów
z drugiej tabeli. Taka relacja wymiaga normalizacji, która polega na dodaniu nowej tabeli.
Przykład znormalizowanej relacji wiele-do-wielu pokazano na rys. 3.
Rysunek 3: Przykład znormalizowanej relacji wiele-do-wielu. (Rys. stworzony dzięki
oprogramowaniu MySQL Workbanch)
Tworzenie relacji jeden-do-wielu
Na rys. 2 pokazano przykład relacji jeden-do-wielu pomiędzy tabelami książka i wydawnictwo. W
tej relacji wydawnictwo może wydać wiele książek. Patrząc od strony książki relacja jest
następująca: wiele książek może być wydanych przez jedno wydawnictwo.
W praktyce, relacja jeden-do-wielu jest tworzona poprzez dodanie dodatkowej kolumny w tabeli,
która w relacji występuje po stronie „wiele”. Kolumna ta ma własność klucza obcego (FK, foreign
key) tj. przetrzymuje wartość klucza głównego (PK, Primary key) odpowiedniego rekordu innej
tabeli.
Pierwszym krokiem prowadzącym do utworzenia relacji widocznej na rys. 2 jest wykonanie
zapytania SQL tworzącego tabelę Wydawnictwo
CREATE TABLE Wydawnictwo (
ID INT PRIMARY KEY,
Nazwa VARCHAR(45),
Adres VARCHAR(45)
);
Następnym krokiem tworzenia relacji jest utworzenie tabeli Ksiazka, w której zostanie dodana
dodatkowa kolumna, mająca własności klucza obcego. W tym przypadku będzie to kolumna o
nazwie W_ID, która będzie się odnosi się do numeru ID w tabeli Wydawnictwo. Dlatego typ
kolumny W_ID to INT, gdyż taki typ posiada kolumna ID (będąca PK) w tabeli Książka. Stąd
zapytanie SQL tworzące tabelę Ksiazka ma postać
CREATE TABLE Ksiazka (
ID INT PRIMARY KEY,
Tytul VARCHAR(45),
LiczbaStr tinyint,
W_ID INT
);
Ostatnim elementem tworzenia relacji jest dodanie do tabeli Ksiazka ograniczenia klucza obcego.
Schemat takiego zapytania ma postać
ALTER TABLE tabelaA
ADD CONSTRAINT nazwa_ograniczenia
FOREIGN KEY (kolumnaFK)
REFERENCES tabelaB(kolumnaPK)[ON DELETE CASCADE ON UPDATE CASCADE]
gdzie nazwa_ograniczenia to nazwa identyfikujące ograniczenia a tabelaA to nazwa tabeli która
zawiera kolumnę klucza obcego kolumnaFK. Występujące w zapytaniu tabelaB i kolumnaPK to
odpowiednio nazwa tabeli której wartości klucza głównego znajdą się w kolumnie kolumnaFK oraz
nazwa kolumny klucza głównego tabeli tworzącej relacje (tj. tabeli o nazwie tabelaB). Opcjonalne
słowa kluczowe ON DELETE CASCADE i ON UPDATE CASCADE zapewniają integralność
relacji.
W omawianym przypadku, zapytanie SQL dodające ograniczenia klucza obcego ma postać
ALTER TABLE ksiazka
ADD constraint ks_FK_w
FOREIGN KEY (W_ID)
REFERENCES wydawnictwo(ID) ON DELETE CASCADE ON UPDATE CASCADE
Alternatywą do dodawania referencji po utworzeniu obydwu tabel jest utworzenie referencji w
momencie tworzenia tabeli ksiazki
CREATE TABLE Ksiazka (
ID INT PRIMARY KEY,
Tytul VARCHAR(45),
LiczbaStr tinyint,
W_ID INT,
CONSTRAINT ks_FK_w FOREIGN KEY (W_ID) REFERENCES wydawnictwo(ID)
ON DELETE CASCADE ON UPDATE CASCADE
);
Tak utworzona relacja dba o to, by dodawane rekordy w tabeli Ksiazka odnosiły się do
odpowiedniego rekordy w tabeli wydawnictwa.
Usuwanie wierszy
Do usuwania wierszy w SQL służy polecenie DELETE, a schemat jego użycia ma postać
DELETE FROM nazwa_tabeli WHERE nazwa_kolumny=wartosc
Przykładem użycia polecenia DELETE jest usunięcie rekordu o ID równym 1 z tabeli książka
DELETE FROM ksiazki WHERE ID=1
Tworzenie relacji wiele-do-wielu
Relacja wiele-do-wielu nie jest możliwa do odwzorowania w relacyjnej bazie danych. Dlatego
każda relacja wiele-do-wielu podlega procedurze normalizacji. W jej wyniku relacja wiele-do-wielu
jest zamieniana na dwie relacje jeden-do-wielu (rys. 3). Takie działanie wymusza utworzenie
trzeciej tabeli, która będzie przetrzymywać dwa klucze obce. Jeden z nich odnosi się do klucza
głównego jednej, a drugi do klucza głównego drugiej tabeli będących w pierwotnej relacji.
Na rys. 3 pokazano znormalizowaną relacje wiele-do-wielu. Tabela Zapis zawiera dwa klucze obce.
Jeden z nich, przechowywany w kolumnie Student_ID przechowuje wartości kolumny ID z tabeli
Student, która jest jej kluczem głównym. Drugi klucz obcy w tabeli Zapisy tj. Przedmiot_ID,
przechowuje wartości klucza głównego tabeli Przedmiot, który zdefiniowano na kolumnie ID tejże
tabeli.
Po operacji normalizacji relacji wiele-do-wielu jej odwzorowywana w relacyjnej bazie danych jako
dwie relacje wiele-do-jednego. Stąd jej utworzenie jest tożsame z napisaniem dwóch ograniczeń
klucza obcego (CONSTRAINT FOREGINE KEY ). W przypadku modelu bazy danych,
pokazanym na rys. 3, ograniczenie klucza obcego występuje pomiędzy tabelami Zapisy i Student
oraz tabelami Zapisy i Przedmiot.
Wyszukiwanie informacji w relacyjnej bazie danych
Rozważmy fragment bazy danych Nordhwind pokazany na rys. 4
Rysunek 4: Fragment bazy danych Northwind z zaznaczonymi kluczami głównymi oraz obcymi
(Rys. stworzony dzięki oprogramowaniu MySQL Workbanch)
Rozważany fragment zawiera cztery tabele, z których pomiędzy tabelami products (produkty) i
suppliers (producenci) występuje relacja jeden-do-wielu a pomiędzy tabelami products i orders
(zamówienia) występuje relacja wiele-do-wielu normalizowana przez tabelę order details.
Rysunek 5: Zestawienie produktów z ich producentem
W celu zestawienia nazwy produktów (z tabeli products) z nazwą producenta (z tabeli suppliers), co
pokazano na rys. 5, należy wykonać następujące zapytanie SQL
select products.ProductName, suppliers.CompanyName from products,
suppliers where products.SupplierID=suppliers.SupplierID;
gdzie fragment products.SupplierID=suppliers.SupplierID wynika z istnienia
relacja zdefiniowanej pomiędzy kluczem obcym tj. w tabeli products a kluczem głównym
(kolumna) tabeli suppliers.
Uwaga: W celu jednoznacznej interpretacji pochodzenia kolumny należy używać notacji z
„kropką” tj. nazwaTabeli.kolumna
Złączenia
Zapoznaj się ze słowem kluczowym join (języka SQL) i jego modyfikacjami korzystając z zasobów
internetowych, np.:
•
http://www.w3schools.com/sql/sql_join.asp
•
http://www.sql-kursy.pl/ms-sql-kurs-join-union-except-2.html
•
http://www.sql-join.com/
Zadania do wykonania z użyciem join znajdują się w części III
Zadania do wykonania
CZĘŚĆ I
Uwaga 1.1: Dane do zadań zależą od grupy. Patrz tabela 1.
Uwaga 1.2: W celu połączenia z bazą użyj aplikacji Database browser oraz połącz się z bazą
MySQL działającą w sieci lokalnej.
Dane dostępu do bazy
Host: 192.168.11.252
Port: 3306
Użytkownik: studX
Hasło: studX
Baza: studX lub employees
gdzie X to cyfra podana przez prowadzącego
Zadania
2.1. Zamodeluj relacje jeden-do-wielu którą podano w tab. 1.Napisz zapytania do bazy
danych tworzącą dwie tabele oraz relacje (jeden do wielu) pomiędzy nimi. Wprowadź
kilka rekordów do każdej z tabeli, zaczynając od tej, która w relacji jest po stronie
„jeden”.
2.2. Zaprojektuj dwie tabele które łączy relacja wiele-do-wielu i dokonaj jej normalizacji.
Nazwy tabel podano w tab.1. Wprowadź kilka rekordów do każdej z tabel tej relacji.
Dane dostępu do bazy
Host: 192.168.11.252
Port: 3306
Użytkownik: studX
Hasło: studX
Baza: studX lub employees
Zad 1.1
I,IV,
II,V
Klient, Zamówienie
Logowanie, Użytkownik Przedmiot, student
Zad 1.2
EgzemplarzKsiazki,
Czytelnik
Tab 1. Dane do zadań z cz. I
Samochod, Klient
III,VI
Maszyna,Element
CZĘŚĆ II
Uwaga 2.1. W celu połączenia z bazą Northwind [3] użyj aplikacji Database browser oraz połącz
się z bazą MySQL działającą w sieci lokalnej.
Dane dostępu do bazy
Host: 192.168.11.252
Port: 3306
Użytkownik: studX
Hasło: studX
Baza: Nordhwind
gdzie X to cyfra podana przez prowadzącego
Uwaga 2.1. Do tabeli order details odwołujemy się w SQL za pomocą `. tj. `order details`, np.
`order details`.OrderID
Zadania
2.1. Zapoznaj się z modelem bazy danych Northwind pokazanym w pliku
Northwind_Model.pdf utworzonym za pomocą programu MySQL Workbanch.
2.2. Policz ile produktów dostarcza poszczególny producent. W odpowiedzi wyświetl
nazwę producent oraz ilość dostarczanych produktów.
2.3.
Jaki jest średni zapas produktów należących do poszczególnych kategorii
2.4.
Ile zamówień złożył dany klient (wyświetl: ID klient, nazwę klienta oraz liczbę
zamówień )
2.5.
Napisz własne zapytanie do bazy danych
CZĘŚĆ III
Uwaga 3.1. W celu połączenia z bazą Northwind [3] użyj aplikacji Database browser oraz połącz
się z bazą MySQL działającą w sieci lokalnej.
Dane dostępu do bazy
Host: 192.168.11.252
Port: 3306
Użytkownik: studX
Hasło: studX
Baza: Nordhwind
gdzie X to cyfra podana przez prowadzącego
Zadania
3.1.
Opisz w kilku zdaniach jak działa słowo kluczowe join
3.2. Dokonaj złączenia tabel customers oraz orders korzystając z różnych wariantów
złączenia. Wyświetl kolumnę CompanyName z tabeli Customers oraz EmployeeID i
OrderDate z tabeli orders
3.3.
Zinterpretuj różnicę w ilości zwróconych wierszy dla różnych wariantów złączenia
3.4.
Wykonaj zapytania 2.2,2.3 i 2.4 korzystając ze złączeń
3.5. Dodatkowe: Zapytanie złożone: podaj listę produktów które znalazły się w
zamówieniu, dla którego wartość kolumny Freight tabeli orders jest największa.
(Uwaga. Największej wartości nie można wpisać jawnie. Ma zostać wyznaczona w
zapytaniu)
Sprawozdanie
Sprawozdanie powinno zawierać polecenie języka SQL realizujące dane zadanie oraz wynik jego
działania
Bibliografia
[1] Giergiel J, Giergiel M, K. Kurc, Sieci komputerowe i bazy danych, OWPRz 2010
[2] MySQL Documentation (http://dev.mysql.com/doc)
[3] Baza danych Northwind na różne bazy danych: https://code.google.com/p/northwindextended