Bazy danych -wykład i ćwiczenia.

prowadzący: Michał Hanćkowiak

Literatura.

  1. Ullman, Widom, "Podstawowy wykład z systemów baz danych", WNT
  2. Cassel, Eddy, Price, "Access 2002/XP PL dla każdego", Helion
  3. opis języka SQL serwera InterBase (pdf)
  4. opis języka SQL serwera InterBase (html)
  5. opis języka SQL serwera Postgres 7.2.1
  6. PostgreSQL 7.2.1 Documentation

Tematyka wykładu.

  1. Projektowanie baz danych przy pomocy "diagramów związków encji" (ERD).
  2. Model relacyjny baz danych.
  3. Microsoft Access (w tym: język VBA, obiekty DAO, współpraca Access-u i serwerów b.d.).
  4. Serwery baz danych Postgres i InterBase.
  5. Dostęp do b.d. z internetu (serwer WWW + strony JSP).

................................................................................
................................................................................
 

Czym jest baza danych.

Czym "aplikacja z baza danych" różni się od zwykłej aplikacji ?

w aplikacjach z bazą danych przechowuje się bardzo duże ilości danych (nie mieszczące się w pamięci operacyjnej).

Dane --> informacje

baza danych przechowuje dane i pozwala je zamieniać na informacje

przykład 1:

tabela WizytyWSklepie - to są fakty ...
data godzina pogoda kwota_zakupu
1/10/2003 10:00 dobra 15zł
1/10/2003 10:15 b dobra 5zł
1/10/2003 11:20 zła 100zł
1/10/2003 15:00 zła 200zł
2/10/2003 14:35 dobra 6zł
.....      
.....      

to są informacje ...
pogoda kwota_zakupu
dobra 21zł
b dobra 5zł
zła 300zł

przykład 2:

w przychodni lekarskiej gromadzi się dane o pacjentach w postaci tabeli:

WizytyPacjentów (nazwisko i imię, pesel, data, kod schorzenia)

potem można zażądać utworzenia takiej tabelki:
kod schorzenia 1995 1996 1997 1998 1999 ... ... ...
C10 1000 1300            
N98 3023 4567            
N78 20 35            
...                


 

DBMS a "aplikacja z bazą danych"

Cechy jakie powinien mieć DBMS:

  1. Niezawodność zapisu danych  - odporność na awarie
  2. Integralność danych - przechowywane dane muszą spełniać pewne warunki (wymuszane przez DBMS)
  3. Sprawność zapytań - wydajne wyszukiwanie danych (tego wymaga "zamiana danych na informacje")
  4. Wygodne interfejsy - wygodne sposoby wprowadzania danych (formularze) i oglądania informacji (raporty), współpraca z innymi programami takimi jak arkusze kalkulacyjne i edytory tekstów, dostęp przez strony WWW itp ...
  5. Wielodostęp
  6. Zabezpieczenia dostępu
     

Składniki DBMS:

  1. Moduł zarządzania pamięcią - obsługuje dysk i bufory w pamięci operacyjnej, utrzymuje indeksy przyspieszające wyszukiwanie danych (przykład: wyszukiwanie w książce telefonicznej posort wg nazwisk)
  2. Procesor zapytań (=kwerend) - generuje odpowiedzi na zapytania (przykład zapytania: "znajdź wizyty w sklepie gdy pogoda była dobra i godzina > 12:00"); stara się generować odpowiedzi w możliwie krótkim czasie ! (optymalizacja zapytań); istnieją także zapytania modyfikujące/aktualizujące dane
  3. Moduł zarządzania transakcjami - transakcja to kilka modyfikacji danych które muszą być wykonywane jako całość (tzn albo wszystkie albo żadna); przykład: przelewy z konta na konto w banku



 

Baza danych jako element aplikacji klient/serwer (= aplikacji rozproszonej)

Aplikacja/ architektura 3-rzędowa oparta o WWW:

Aplikacja/ architektura 3-rzędowa oparta o obiekty rozproszone:




 

Projektowanie kontra implementacja.

Należy oddzielić projektowanie od implementacji (=tworzenie relacyjnej b.d.).
Podczas projektowania unika się szczegółów technicznych zaciemniających obraz.

(nieco wyprzedzając materiał ...)
Profesjonalny sposób tworzenia bazy danych jest następujący:

  1. poznajemy problem który chcemy rozwiązać
  2. projektujemy; tworzymy diagramy ERD
  3. zamieniamy ERD na tabele relacyjnej bazy danych
  4. ulepszamy te tabele (normalizacja)
  5. definiujemy tabele w DBMS-ie
  6. tworzymy kwerendy, formularze, raporty w DBMS-ie
  7. baza danych jest "gotowa" ...

Projektowanie przy pomocy diagramów związków encji (ERD).

ERD = Entity Relationship Diagram = diagram związków encji

Przykładowy ERD:

Elementy składowe ERD:

Przykłady ERD:


 

 

Model relacyjny baz danych.

Podstawowe pojęcia.

W relacyjnej bazie danych "dane" przechowuje się w tabelach.
data godzina pogoda kwota_zakupu
1/10/2003 10:00 dobra 15zł
1/10/2003 10:15 b dobra 5zł
1/10/2003 11:20 zła 100zł
1/10/2003 15:00 zła 200zł
2/10/2003 14:35 dobra 6zł
.....      
.....      

Przykład:

tabela Klienci :
nazwa adres id klienta
Jan Kowalski1 qqq1 1
Jan Kowalski2 qqq2 2
tutaj [id klienta] jest kluczem

tabela Zamowienia :
towar ilosc data id klienta
masło 5 1/10/2003 1
chleb 15 2/10/2003 2
wino 3 10/10/2003 1
tutaj kluczem może być [data] i [id klienta], przy założeniu że każdy klient może złożyć co najwyżej jedno zamówienie w każdym dniu; [id klienta] jest "kluczem" obcym

złączenie naturalne tabel Klienci i Zamówienia :
nazwa adres id klienta towar ilosc data
Jan Kowalski1 qqq1 1 masło 5 1/10/2003
Jan Kowalski1 qqq1 1 wino 3 10/10/2003
Jan Kowalski2 qqq2 2 chleb 15 2/10/2003

z powyższej tabeli mogę wybrać wiersze spełniające warunek : data >= #2/10/2003# (to jest selekcja)
mogę wybrać interesujące mnie pola : nazwa, towar, ilosc (to jest rzutowanie)
dostanę wtedy następującą tabelkę wynikową:
nazwa towar ilosc
Jan Kowalski1 wino 3
Jan Kowalski2 chleb 15

tabelka powyższa jest wynikiem "złożenia" operacji złączenia, selekcji i rzutowania;
innymi słowy jest wartością pewnego wyrażenia w algebrze relacji;
mianowicie takiego:
    ((Klienci*Zamowienia){data>=#2/10/2003#})[nazwa,towar,ilosc]

(koniec przykładu)


 

Kwerendy.

Kwerendy pozwalają "wyciągać" informacje z bazy danych (innymi słowy: zamieniać dane na informacje).

Kwerenda to wyrażenie algebry relacji zazwyczaj zapisane w języku QBE lub SQL.

QBE (= Query By Example), jest to "wizualny" sposób zapisu kwerend.
SQL (=Structured Query Language), jest to tekstowy sposób zapisu kwerend.
Języki SQL i QBE są równoważne.
Języki SQL/QBE wykraczają daleko poza możliwości algebry relacji ...

Przykład tej samej kwerendy zapisanej na różne sposoby:

Kwerenda zapisana oznaczeniami z algebry relacji:
((Klienci*Zamowienia){imie i nazwisko='Jan Kowalski1'})[imie i nazwisko, ilosc, data]

Kwerenda zapisana w języku QBE:

Kwerenda zapisana w języku SQL:
SELECT Klienci.[imie i nazwisko], Zamowienia.ilosc, Zamowienia.data
    FROM Klienci INNER JOIN Zamowienia ON Klienci.[id klienta] = Zamowienia.[id klienta]
    WHERE Klienci.[imie i nazwisko]="Jan Kowalski1";

Wynik działania kwerendy:

 

Inne proste przykłady SQL-a:

// zakładamy że dysponujemy tabelami Klienci i Zamówienia
// o następujących schematach formalnych:
//   	Klienci (nazwa, adres, id klienta)
//   	Zamówienia (towar, ilosc, data, id klienta)


np1:
select * from Klienci;
	// wyświetla wszystkie pola i wszystkie rekordy tabeli Klienci
np2:
select nazwa, [id klienta] from Klienci;
	// wyświetla tylko pola nazwa i [id klienta]
	// nawiasy [] ze względu na spacje w nazwie
	// (UWAGA: to jest specyficzne dla Access-u !)
		// rzutowanie

np3:
select nazwa, adres, towar 
  from Klienci inner join Zamowienia on Klienci.[id klienta]=Zamowienia.[id klienta]
  where data > #2/10/2003#
	// wyświetla nazwy klientów, ich adresy i towary 
	// zamówione po 2/10/2003;
		// rzutowanie + selekcja + złączenie (naturalne)

np4:
select nazwa, adres, towar 
  from Klienci, Zamowienia
  where (data > #2/10/2003#) and (Klienci.[id klienta] = Zamowienia.[id klienta])
	// alternatywne rozwiązanie ...


// w poniższych przykładach SQL wychodzi poza algebrę relacji !!!
np5:
update Zamowienia set ilosc=ilosc+1 where [id klienta]=2
	// polecenie modyfikuje tabele Zamówienia;
	// zwiększa "ilosc" o 1 dla wszystkich zamówień
	// pochodzących od klienta z [id klienta]=2

np6:
select towar, SUM(ilosc) as [Suma ilosci] 
  from Zamowienia 
  group by towar 
  order by SUM(ilosc)
	// dla każdego towaru oblicza łączną ilość zamówień;
	// grupujemy rekordy tabeli wg pola "towar",
	// 	i dla każdej grupy obliczamy sumę pól "ilosc";
	// kolumna pokazująca łączną ilość na nazwę "Suma ilosci";
	// wynik jest posortowany rosnąco wg wartości sum