Bazy danych Zagadnienia • • • • • • • • • Podstawy relacyjnych baz danych SQL Języki manipulowania w relacjach Aspekty aktywne baz danych Aspekty systemowe baz danych Projektowanie baz danych Zależności funkcyjne i postacie normalne Transakcje bazodanowe Autoryzacja w bazach danych Literatura • J.D. Ullman, J.Widom, Podstawowy wykład z systemów baz danych, Wydawnictwo Naukowo-Techniczne, Warszawa, 2000. • T. Pankowski, Podstawy baz danych, Wydawnictwo Naukowe PWN, Warszawa, 1992. • D. Mendrala, M. Szeliga, Praktyczny Kurs SQL • R. J. Muller, Bazy danych – Język UML w modelowaniu danych • Jeffrey D. Ullman - Principles of database systems, 1982 Bazy danych Informacje podstawowe Czym jest baza danych? • Zbiór danych istniejących przez długi czas, posiadać odpowiednią strukturę (schemat bazy danych) , można na danych wykonywać pewne operacje, reprezentuje pewien fragment rzeczywistości, jest projektowana, tworzona i utrzymywana z punktu widzenia pewnych zastosowań. Zachowane są więzy integralności. System zarządzania bazą danych • Zbiór programów umożliwiających tworzenie i utrzymywanie bazy danych • Cechy: – – – – – Umożliwia dodanie nowej bazy danych i określenia jej schematu Umożliwia zadawanie zapytań Zapewnia możliwość przechowywania dużej ilości danych Jednoczesny dostęp do danych przez wielu użytkowników Umożliwia kontrolę dostępu do poszczególnych danych bazy Architektura systemu zarządzania bazą danych • Moduł zarządzania pamięcią składa się z modułu zarządzania plikami i modułu zarządzania buforami: – modułu zarządzania plikami - przechowuje dane o miejscu zapisania plików na dysku i na polecenie modułu zarządzania buforami przesyła zawartość bloku danych lub bloków – moduł zarządzania buforami – przydziela strony pamięci operacyjnej do odpowiednich bloków • Moduł zarządzania zapytaniami przetwarza zapytanie języka SQL na ciąg poleceń żądających dostarczenia określonych danych . Optymalizacja zapytania… odpowiedni plan, itd. Indeksy… • Moduł zarządzania transakcjami Algebra relacji i SQL Struktury danych • Relacyjna baza danych jest zbiorem relacji. • Schemat relacji R(A1, A2, … , An) składa się z nazwy relacji R oraz listy atrybutów A1, A2, … , An. • Każdy atrybut Ai schematu relacji R posiada dziedzinę oznaczoną jako dom(Ai). • Dziedzina definiuje zbiór wartości jakie dany atrybut może przyjmować. Z atrybutem związany jest więc typ danych. Struktury danych (2) • Relacją r o schemacie R(A1, A2, … , An) oznaczoną r(R) nazywamy zbiór k krotek postaci: r = {t1,t2,…, tk}. • Pojedyncza krotka t jest uporządkowaną listą n wartości: t=< v1,v2,…, vn > przy czym vi jest wartością należącą do dom(Ai) lub wartością pustą (NULL). • i-ta wartość krotki t odpowiadająca wartości atrybutu Ai będzie oznaczona przez t.Ai. Struktury danych (3) • Relacja r(R) jest matematyczną relacją stopnia n zdefiniowaną na zbiorze domen: dom(A1), dom(A2), … , dom(An) będącą podzbiorem iloczynu kartezjańskiego domen definiujących R: r(R) dom(A1) x dom(A2)x … x dom(An) Charakterystyka relacji • Każdy atrybut relacji ma unikalną nazwę • Porządek atrybutów w relacji nie jest istotny • Porządek krotek w relacji nie jest istotny • Wartości atrybutów są atomowe (elementarne) • Relacja nie zawiera rekordów powtarzających się Typy atrybutów Przykładowe typy atrybutów: • Char(n) – pole tekstowe o stałej długości (jeśli podamy mniej znaków, to pozostałe będą spacjami wypełnione) • Varchar(n) – pole tekstowe nie dłuższe niż n znaków ( nie ma spacji) • Int, Integer – liczby całkowite • Numeric – liczby stałopozycyjne np. Numeric(10,2), 2 miejsca po przecinku • Float (8 bytes), Real(4 bytes) – liczby zmiennopozycyjne • BLOB – pola przechowujące liczby binarne SQL, Algebra relacji • SQL (Structured Query Language) – język zapytań dla relacyjnych baz danych; • Język nieproceduralny – użytkownik wyszczególnia operacje co ma być zrobione, a nie w jaki sposób. • SQL posiada optymalizator, który wybiera optymalną ścieżkę wykonywania zapytania. • SQL ma kilka dialektów. Są nieznaczne różnice pomiędzy różnymi systemami baz danych. Udostępnienie relacji Udostępnienie relacji (tabeli) o nazwie R: Algebra relacji (AR): R SQL: Select * from R Przykład: select * from Pracownik Suma Definicja: Operandy R, S – tabele jednakowego typu U, wynik: tabela typu U Suma mnogościowa (union): R S = { t | t R t S} Student Pracownik Przykład: select Imie, Nazwisko, NrIndeksu, Rok FROM Student union select Imie, Nazwisko, NrPrac, RokPracy FROM Pracownik Wynik Przekrój Definicja: Operandy R, S – tabele jednakowego typu U, wynik: tabela typu U Przekrój (intersect): R S = { t | t R t S} Student Pracownik Przykład: select Imie, Nazwisko FROM Student intersect select Imie, Nazwisko FROM Pracownik Wynik Różnica Definicja: Operandy R, S – tabele jednakowego typu U, wynik: tabela typu U Różnica (except): R - S = { t | t R t S} Student Pracownik Przykład: select Imie, Nazwisko FROM Student except select Imie, Nazwisko FROM Pracownik Wynik Selekcja • Wypisuje tylko takie krotki, które spełniają warunek. R1 := C (R2) , C – oznacza warunek Przykład: Sells Wynik AR: Tawerna := bar=“Tawerna”(Sells) SQL: SELECT * FROM Sells WHERE bar = ‘Tawerna’ Selekcja (2) SQL: SELECT * FROM Sells WHERE bar = ‘Tawerna’ Operatory logiczne: =, <>, >=,<, <=, NOT, LIKE Działanie operatora LIKE: AR: T1 := bar like“T%” (Sells) SQL: SELECT * FROM Sells WHERE bar like ‘T%’ Definicja warunku Definicja warunku E: U – zbiór atrybutów, V – zbiór stałych (liczb, tekstów), A, A' U, c V, {=, !=, <, <=, >, >=, like, ... } – operator porównania, Warunkiem nad U nazywamy wyrażenie logiczne E : E ::= A c | A A' | (E ) | not E | E or E | E and E Krotka r typu U spełnia warunek E, E (r ) = TRUE, jeśli wyrażenie powstałe z E przez podstawienie za każde wystąpienie atrybutu A wartości r.A jest prawdziwe. Projekcja Wypisuje wszystkie krotki, ale ogranicza atrybuty do wyspecyfikowanej listy. R1 := L (R2), L – oznacza listę atrybutów należącą do schematu R2. Przykład: Sells Wynik AR: Prices := beer,price(Sells): SQL: SELECT beer,price FROM Sells Projekcja - formalnie Krotka: r = [A:a, B:b, C:c] typu U={A,B,C} X = {A,C} U Projekcja r na zbiór X: X(r) = {A,C}[A:a, B:b, C:c] = [A:a, C:c] • Definicja: Niech r będzie krotką typu U i niech X U. Krotkę t typu X nazywamy projekcją r na X, jeśli t i r mają jednakowe wartości na wszystkich atrybutach ze zbioru X, tzn: t = X(r) A( A X t.A = r.A). X – operator projekcji, t.A – wartość pola A dla krotki t. Rozszerzona projekcja • Rozszerzona projekcja umożliwiająca tworzenie nowych atrybutów za pomocą wyrażeń arytmetycznych oraz duplikowania istniejących. Przykład: Sells AR: Prices := Wynik Beer+Bar ->B, Bar, Bar (Sells) SQL: SELECT Bar + Beer as B, Bar, Bar FROM Sells Iloczyn kartezjański • R3 := R1 X R2 Łączy każdą krotkę relacji R1 z każdą krotką R2. Przykład: Sells Bar AR: Cartesian := Sells X Bar SQL: SELECT * FROM Sells, Bar T- SQL: SELECT * FROM Sells CROSS JOIN Bar Wynik Iloczyn kartezjański - formalnie Iloczyn kartezjański krotek: [A:a, B:b]N × M[A:a’, C:c] = [N.A:a, N.B:b, M.A:a’, M.C:c] Uwaga: W iloczynie kartezjańskim występują wszystkie kolumny z obydwu operandów. Prefiksowanie nazwami N i M jest potrzebne dla zapewnienia jednoznaczności nazw pól (kolumn). Gdy atrybuty w krotkach są różne, to prefiksowanie nie jest konieczne. Złączenie warunkowe • R3 := R1 ►◄ C R2 Na iloczyn kartezjański R1 X R2 nakładany jest warunek C. Złączenie jest nazwane Theta Join. Przykład: Sells Bar Wynik AR: ThetaJoin := Sells ►◄ Sells.Bar = Bar.Bar Bar SQL: SELECT * FROM Sells INNER JOIN Bar ON Bar.Bar = Sells.Bar Złączenie naturalne • R3 := R1 ►◄ R2 Łączy relacje R1 i R2 po wspólnym atrybucie. Wspólny atrybut wypisuje tylko raz. Przykład: Sells Bar Wynik AR: Natural := Sells ►◄ Bar SQL: SELECT * FROM Sells NATURAL JOIN Bar SQL: SELECT * FROM Sells, Bar WHERE Sells.Bar = Bar.Bar Złączenie naturalne - formalnie Definicja: Niech r i s będą krotkami typów odpowiednio X i Y. Krotka t typu X Y jest złączeniem naturalnym r i s, t = r ►◄ s, jeśli π X(t) = r i π Y(t) = s. Krotki naturalnie złączalne: [A:a, B:b, C:c, D:d] [A:a, B:b, D:d, E:e, F:f] = [A:a, B:b, C:c, D:d, E:e, F:f] Krotki niezłączalne: [A:a, B:b, C:c, D:d], [A:a, B:b’, D:d, E:e, F:f] Przemianowanie • R1 := ρ R1(A1,…,An)(R2) Tworzy relację R1 z przemianowanymi atrybutami relacji R2. Przykład: Bar Wynik AR: R:= ρ R(name, addr) (Bar) SQL: SELECT Bar as Name , Address as addr FROM Bar AS R Schematy operatorów algebry relacji • Suma, różnica, przekrój: schematy argumentów operacji są takie same jak schemat wyniku. • Selekcja: schemat argumentu jest taki sam jak schemat wyniku. • Projekcja: lista atrybutów projekcji mówi o schemacie wynikowym. Schematy operatorów algebry relacji (2) • Iloczyn kartezjański: schemat wynikowy składa się z atrybutów należących do obydwu relacji. Żeby odróżnić atrybuty o tej samej nazwie pochodzące z różnych relacji, należy podać nazwę relacji, np.. R.A • Złączenie warunkowe: tak samo jak iloczyn kartezjański. • Złączenie naturalne: suma atrybutów dwóch relacji. • Przemianowanie: operator określa schemat. Usuwanie duplikatów • R1 := δ (R2). R1 składa się z jednej kopii każdej krotki, która w R2 występuje wiele razy. Przykład: AR: BarDist:= δ( Bar (Sells)) SQL: select distinct Bar FROM Sells Sortowanie • R1 := L (R2) R1 jest listą krotek z R2 posortowaną najpierw po pierwszym atrybucie z listy L, potem po drugim, itd.. Przykład: Sells AR: Ordered : = Wynik Price Sells SQL: select * FROM Sells ORDER BY Price Agregacje • • Operacje agregacji nie są operacjami algebry relacji. Na podstawie wszystkich wartości atrybutów obliczają jedną wartość. Przykład: Sells SUM(Price) = 12,50 COUNT(Price) = 4 MAX(Price) = 3,50 AVG(Price) = 3,125 SQL: select avg(Price) FROM Sells Grupowanie • R1 := L (R2) , L jest listą atrybutów, które są: • • • pojedynczymi atrybutami; AGG(A), gdzie AGG jest funkcją agregującą na atrybucie A; R1 := L (R2) – grupuje R2 według wszystkich atrybutów grupujących w L, czyli formułuje jedną grupę dla każdej różnej listy wartości tych atrybutów – dla każdej grupy oblicza wartość funkcji agregującej Grupowanie (2) Przykład 1: Sells Wynik AR: Group := Bar, AVG(Price) -> AvgPrice (Sells) SQL: select Bar, AVG(Price) As AvgPrice FROM Sells GROUP BY Bar Przykład 2: Sells Wynik AR: Group := Bar,Beer, AVG(Price) -> AvgPrice (Sells) SQL: select Bar,Beer, AVG(Price) As AvgPrice FROM Sells GROUP BY Bar,Beer Grupowanie (3) • Wyniki grupowanie można ograniczyć selekcją Przykład: Sells Wynik AR: Group := AVG(Price)>3 Bar,Beer, AVG(Price) -> AvgPrice (Sells)) SQL: select Bar,Beer, AVG(Price) As AvgPrice FROM Sells GROUP BY Bar,Beer having AVG(Price)> 3 Łączenie zewnętrzne • obie kolejne instrukcje zwracają wszystkie rekordy X i tylko te rekordy Y, w których <warunek> ma wartość True: SELECT * FROM X LEFT OUTER JOIN Y ON <warunek> SELECT * FROM Y RIGHT OUTER JOIN X ON <warunek> Łączenie zewnętrzne (2) • Pełne łączenie zewnętrzne zwraca wszystkie rekordy obu zestawów, łącząc ze sobą tylko te rekordy, w których warunek jest spełniony (daje wartość True). SQL Server umożliwia pełne łączenia zewnętrzne za pomocą warunku FULL OUTER JOIN: SELECT * FROM X FULL OUTER JOIN Y ON <warunek> Złączenie zewnętrzne Przykład: SELECT * FROM Sells RIGHT OUTER JOIN Bar ON Bar.Bar = Sells.Bar SELECT * FROM Bar LEFT OUTER JOIN Sells ON Bar.Bar = Sells.Bar SELECT * FROM Bar FULL OUTER JOIN Sells ON Bar.Bar = Sells.Bar Sells Bar Wynik Operatory mnogościowe (różnica) select Imie, Nazwisko FROM Student except select Imie, Nazwisko FROM Pracownik select s.Imie, s.Nazwisko from Student as s left outer join Pracownik as p on s.imie = p.imie and s.nazwisko = p.nazwisko where p.imie is null Operatory mnogościowe (przekrój) select Imie, Nazwisko FROM Student intersect select Imie, Nazwisko FROM Pracownik select s.Imie, s.Nazwisko from Student as s left outer join Pracownik as p on s.imie = p.imie and s.nazwisko = p.nazwisko where p.imie is not null Instrukcja SELECT SELECT <ListaPól> FROM <ListaZestawówRekordów> <RodzajŁączenia> JOIN <Warunek> WHERE <KryteriaWyboru> GROUP BY <ListaPólGrupujących> HAVING <KryteriaWyboru> ORDER BY <ListaPólPorządkujących> Instrukcja WHERE select * FROM Pracownik WHERE Nazwisko < Imie select * FROM Pracownik WHERE Wydzial IN('PPT', 'IZ') select * FROM Pracownik WHERE Wydzial NOT IN('PPT', 'IZ') select * FROM Pracownik WHERE Wydzial IN ('PPT') OR Wydzial IN ('IZ') select * FROM Pracownik WHERE NrPrac BETWEEN 3300 AND 3600 select * FROM Pracownik WHERE NrPrac IS NULL Podzapytania Q: Podaj wszystkich pracowników, którzy pracują na wydziale, który ma lokalizację we Wrocławiu. Pracownik SELECT * FROM Pracownik WHERE Wydzial IN (SELECT Wydzial FROM Lokalizacja WHERE Lokalizacja = 'Wroclaw') Lokalizacja Wynik Podzapytania Q: Podaj wszystkie lokalizacje wydziału, na którym pracuje Zaorski. Pracownik SELECT Lokalizacja FROM Lokalizacja WHERE Wydzial IN (SELECT Wydzial FROM Pracownik WHERE Nazwisko = 'Zaorski') Lokalizacja Wynik Podzapytania Q: Podaj pracowników, których zarobki są mniejsze niż średnia zarobków pracowników. Pracownik SELECT * FROM Pracownik WHERE Zarobki < (SELECT AVG(Zarobki) FROM Pracownik ) Lokalizacja Wynik Podzapytania • Podzapytania mogą być bardziej zagnieżdżone, tzn. w warunku może być następny warunek, itd. • W podzapytaniu lewa strona predykatu wymaga zgodnego wyniku z podzapytaniem po prawej stronie. • Przykład: – Liczba > (podzapytanie zwracające pojedynczą liczbę) – Napis = (podzapytanie zwracające pojedynczy napis) • Nie może być: – Liczba > (Lista liczb) Podzapytania • Składnia z użyciem IN: – liczba IN (podzapytanie zwracające listę liczb) – text IN (podzapytanie zwracające listę testów) • Nie może być: – Liczba > (Lista liczb) • Może być: – Liczba > ANY (Lista liczb) – Liczba > ALL (Lista liczb) Podzapytania Q: Podaj pracowników, których zarobki są większe lub równe niż zarobek przynajmniej jednego pracownika. Pracownik SELECT * FROM Pracownik WHERE Zarobki > ANY(SELECT (Zarobki) FROM Pracownik ) Lokalizacja Wynik Podzapytania Q: Podaj pracowników, których zarobki są większe niż od zarobku wszystkich pracowników. Pracownik SELECT * FROM Pracownik WHERE Zarobki >= ALL(SELECT (Zarobki) FROM Pracownik ) Lokalizacja Wynik Podzapytania • W przypadku zapytania ze zwykłym podzapytaniem, podzapytanie wykonywane jest jako pierwsze, a wyniki tymczasowe są przechowywane w pamięci SZRBD. • Przykład: – WHERE Zarob < (SELECT AVG(ZAROB)….) • Wynik podzapytania jest sprawdzany z każdym wierszem tabeli. Podzapytania skolerowane Q: Którzy ludzie zarabiają mniej niż wynosi średnia pensja na tym wydziale, na którym dany pracownik pracuje. Pracownik SELECT * FROM Pracownik AS p WHERE Zarobki < (SELECT AVG(Zarobki) FROM Pracownik AS s WHERE p.Wydzial = s.Wydzial) Lokalizacja Wynik Podzapytania skolerowane Q: Podaj takie lokalizacje, dla których istnieją pracownicy w nich pracujący . Pracownik SELECT * FROM Lokalizacja Lokalizacja Wynik WHERE EXISTS (SELECT * FROM Pracownik WHERE Lokalizacja.Wydzial = Pracownik.Wydzial) Wyrażenie EXISTS(podzapytanie) jest prawdziwe tylko wtedy, kiedy podzapytanie zwróci co najmniej jeden wiersz Podzapytania skolerowane Q: Podaj takie lokalizacje, dla których nie istnieją pracownicy w nich pracujący . Pracownik SELECT * FROM Lokalizacja Lokalizacja Wynik WHERE NOT EXISTS (SELECT * FROM Pracownik WHERE Lokalizacja.Wydzial = Pracownik.Wydzial) Wyrażenie NOT EXISTS(podzapytanie) jest prawdziwe tylko wtedy, kiedy podzapytanie nie zwróci żadnego wiersza. Podzapytania skolerowane • W przypadku zapytania ze zwykłym podzapytaniem, podzapytanie wykonywane jest jako pierwsze, a wyniki tymczasowe są przechowywane w pamięci SZRBD. • W przypadku zapytań skolerowanych wartość z głównego zapytania jest przekazywana do podzapytania, aby mogło być ono wykonane. Wstawianie wierszy Pracownik Wynik insert into Pracownik (Imie, Nazwisko, NrPrac, RokPracy, Wydzial, Zarobki) values ('Jadwiga', 'Mrozowska',23000, 8,'PPT', 1200) Wstawianie wierszy Można opuścić jakiś atrybut: np. RokPracy, Wydział. Jeśli dla tego atrybutu jest zdefiniowana wartość domyślna, wówczas wpisywana jest wartość domyślna. Jeśli nie ma domyślnej wartości to wstawiany jest NULL o ile dla atrybutu można wstawić NULL . Pracownik Wynik insert into Pracownik (Imie, Nazwisko, NrPrac, RokPracy) values ('Jadwiga', 'Hajnicz',23000,4) Wstawianie wierszy Można opuścić wszystkie atrybuty tabeli. Wtedy należy jednak podać wszystkie wartości. Pracownik Wynik insert into Pracownik values ('Jadwiga', 'Jazwa',23000, 8,'PPT', 1200) Wstawianie wielu wierszy Można wstawić wiele wierszy do tabeli na podstawie danych z innej tabeli. Pracownik Wynik insert into Pracownik2 (Imie, Nazwisko, NrPrac, Wydzial) select Imie, Nazwisko, NrPrac, Wydzial from Pracownik Usuwanie danych z tabeli Usuwanie wszystkich danych z tabeli delete from Pracownik2 Usuwanie niektórych krotek delete from Pracownik where Imie = 'Jadwiga' Pracownik Wynik Usuwanie danych z tabeli Przy usuwaniu można korzystać z podzapytań. Q: Usuń wszystkich pracowników, którzy pracują na wydziale, który ma lokalizację we Wrocławiu delete FROM Pracownik2 WHERE Wydzial IN (SELECT Wydzial FROM Lokalizacja WHERE Lokalizacja = 'Wroclaw') Pracownik2 Wynik Modyfikowanie wierszy Pracownik update Pracownik set Wydzial = 'IZ', NrPrac = 22000 WHERE Nazwisko = 'Zaorski' Wynik Modyfikowanie wierszy Pracownik2 Pracownik UPDATE Pracownik2 SET Pracownik2.Wydzial = Pracownik.Wydzial FROM Pracownik2 INNER JOIN Pracownik ON Pracownik2.NrPrac = Pracownik.NrPrac Pracownik2 To tyle….