Podstawy Baz Danych Wykład 2024-2025 PDF

Podstawy Baz Danych Informatyka I stopień Informatyka - Data Science II stopień semestr zerowy Informatyka- Uczenie Maszynowe i Sztuczna Inteligencja semestr zerowy dr inż. Anna Zygmunt [email protected] D17, p. 2.19  Cy...

Podstawy Baz Danych Informatyka I stopień Informatyka - Data Science II stopień semestr zerowy Informatyka- Uczenie Maszynowe i Sztuczna Inteligencja semestr zerowy dr inż. Anna Zygmunt [email protected] D17, p. 2.19  Cykl bazodanowy » I stopień - inżynierskie – „Podstawy baz danych” – 3. semestr Anna Zygmunt – „Bazy danych” – 4. semestr – ścieżka „Wytwarzanie oprogramowania” Robert Marcjan » II stopień – magisterskie – Informatyka-Data Science „Bazy danych w Data Science” – 1. semestr - nowy – Robert Marcjan „Eksploracja danych” – 1. semestr – Anna Zygmunt » prace inżynierskie i magisterskie  Tryb prowadzenia zajęć » Wykład – zdalnie – MS Teams – wykład nagrywany i udostępniany przez prowadzącego – UPEL AGH Podstawy Baz Danych wykład 2024-2025 (PBD_2024-2025) hasło: PBD_2025 forum (informacje dla wszystkich uczestników), prezentacje z wykładów, materiały » Egzamin – D17 – forma test – do egzaminu można przystąpić pod warunkiem uzyskania zaliczenia z części laboratoryjnej  Tryb prowadzenia zajęć c.d. » Laboratoria – D17 » Prowadzący: dr R. Marcjan, dr Tomasz Pełech-Pilichowski, dr A. Zygmunt – każda grupa co do zasady prowadzona tak samo – każdy prowadzący ma swój zespół/kanał na MS Teams oraz kurs na UPEL AGH – zajęcia odbywają się zgodnie z harmonogramem w usos – obrazy na komputerach w lab, możliwość pracy na własnych laptopach – jedna nieobecność nieusprawiedliwiona jest dozwolona każda kolejna skutkuje obniżeniem oceny z laboratorium o 1 stopień więcej niż 3 nieobecności nieusprawiedliwione skutkują niezaliczeniem laboratorium w przypadku złego samopoczucia/przypadków losowych należy powiadomić prowadzącego mailem PRZED zajęciami L4 należy przynieść na pierwszych zajęciach po chorobie  Organizacja laboratoriów » Laboratoria - relacyjny model danych 1. kurs SQL 2. projektowanie struktury bazy danych według opisanych wymagań 3. implementacja struktury bazy danych uwzględnienie warunków integralnościowych warstwa dostępu do danych: – widoki, procedury, funkcje, triggery indeksy/uprawnienia DANE w każdej tabeli  Organizacja laboratoriów – kurs SQL » Microsoft SQL Server – zainstalowany na serwerze II – serwer dbmanage.lab.ii.agh.edu.pl login: student/hasło: student – konieczne wcześniejsze uruchomienie VPN (panel.agh.edu.pl) » Aplikacje klienckie – SQL Server Managament Studio (SSMS) – DataGrip (port 1433) – Azure Data Studio – Dbeaver – SQL NoteBook » Na stanowiskach uruchamiamy MS Teams – udostępniamy swoje pulpity/piszemy na czacie  Łączenie z SQL Server WI – autentykacja przez serwer BD  Organizacja laboratoriów – kurs SQL c.d. » Instalacja lokalna na swoich komputerach – SQL Express - darmowa https://www.microsoft.com/en-us/sql-server/sql- server-downloads – Ćwiczeniowe bazy danych – backupy i opisy w UPEL – sekcja „Przykładowe bazy danych” – „Restore database”  Łączenie z SQL Server lokalnym – autentykacja przez system operacyjny  Organizacja laboratoriów – kurs SQL c.d. » Kolokwium z SQL – początek grudnia przy komputerach, w laboratoriach, w ramach zajęć można mieć schematy baz danych kartkę ze sygnaturami funkcji brak dostępu do help’a  Organizacja laboratoriów – projektowanie i implementacja struktury bazy danych » Projekt schematu bazy danych dla zadanego problemu – implementacja zaprojektowanej bazy danych – definicja warunków integralnościowych – wypełnienie danymi testowymi – oprogramowanie warstwy dostępu do danych: widoki, procedury, funkcje, triggery – określenie uprawnień » Zadanie realizowane w zespołach 3-osobowych » W oparciu o MS SQL Server » System do zakładania własnych baz danych: https://dbmanage.lab.ii.agh.edu.pl/ » Początek listopada: – omawiany temat  Organizacja laboratoriów – projektowanie i implementacja struktury bazy danych c.d. »Projekt realizowany etapowo – zadania/minifora na UPEL: 1. opis funkcji systemu wraz z informacją, co jaki użytkownik może wykonywać w systemie dokument do 25.11 - 29.11 2. projekt i implementacja schematu bazy z warunkami integralnościowymi do 9.12-13.12 3. widoki do 20.12 4. procedury, funkcje do 12.01 (orientacyjnie) 5. triggery, indeksy, uprawnienia do19.01 (orientacyjnie) 6. końcowa wersja projektu do 25.01 (wygenerowana dokumentacja) »Schemat bazy danych musi być zatwierdzony! »Muszą być dane w każdej tabeli!  Punktacja za poszczególne elementy zadania Za zadanie można otrzymać 0-10 punktów: max 4 pkt za schemat bazy danych; max 1 pkt za warunki integralnościowe; max 4 pkt za procedury, triggery, widoki; max 1 pkt - inne (indeksy, uprawnienia,...). » Punkty uwzględniają terminowość (-0,5 pkt za 1 tydzień, -1 pkt > 1 tydzień) » Uzyskane punkty przeliczane są na ocenę wg skali AGH.  Schemat bazy danych (SSMS, Vertabelo, Draw.io) – implementacje SSMS  Wyliczanie oceny końcowej z laboratorium » Ocena końcowa z laboratorium obliczana jest ze wzoru: 0.5*kolokwium SQL + 0.5* zadanie – przy założeniu, że ocena z kolokwium SQL>=3.0 i zadanie >=3.0. » Ocena z części SQL może zostać podniesiona o 0.5 na podstawie aktywności – 5 plusów (zadania wskazane przez prowadzącego) » Ocena z zadania wystawiana na ostatnich zajęciach » Ocena 2.0 z SQL => konieczność poprawy – wtedy ocena jest średnią ocen z obydwu kolokwiów  Ocena końcowa z przedmiotu » Warunek konieczny: – uzyskanie pozytywnej oceny zarówno z laboratorium jak i egzaminu – obliczana jest średnia z ocen z laboratorium i wszystkich terminów egzaminu  Zgodnie z regulaminem studiów pierwszego i drugiego stopnia AGH... » „Student ma prawo do trzykrotnego przystąpienia do egzaminu w zaplanowanych terminach, w tym jeden raz w terminie podstawowym i dwa razy w terminie poprawkowym. Nieusprawiedliwiona nieobecność na egzaminie w danym terminie powoduje utratę tego terminu.” » „Egzaminator może uznać za usprawiedliwione nieprzystąpienie do egzaminu na wniosek studenta złożony najpóźniej w terminie 7 dni od ustalonego terminu egzaminu.” » „Egzamin poprawkowy w celu poprawy oceny pozytywnej nie jest dopuszczalny”  Zagadnienia omawiane na wykładzie » Wprowadzenie do baz danych, architektura SZBD » Relacyjny model danych » Język algebry relacji » Modelowanie rzeczywistości – model pojęciowy i zasady jego konstrukcji (model ER). » Zasady poprawnej konstrukcji schematów baz danych i proces normalizacji schematu. » Warunki integralności » Widoki, procedury, funkcje, triggery » Bezpieczeństwo danych, uwierzytelnianie, kontrola dostępu » Struktury przechowywania danych. » Rola indeksów i ich rodzaje » Przetwarzanie transakcyjne i zarządzania współbieżnością. » Rozproszone bazy danych i zarządzanie rozproszonymi transakcjami » Kierunki rozwoju systemów bazodanowych  Literatura »Abraham Silberschatz, Henry F. Korth, S. Sudarshan, "Database System Concepts", Seventh Edition, McGraw-Hill, 2019 https://www.db-book.com/db7/index.html »Ramez Elmasri, Shamkant B. Navathe, „Wprowadzenie do systemów baz danych.”, Wydanie 7, Helion, 2019,wersja nagielska online: http://iips.icci.edu.iq/images/exam/databases-ramaz.pdf »Wilfried Lemahieu, Seppe van den Broucke, Bart Baesens, "Principles of Database Management: The Practical Guide to Storing, Managing and Analyzing Big and Small Data", https://www.pdbmbook.com/, Cambridge University Press, 2018 »Jeffrey D. Ullman, Jennifer Widom "Podstawowy kurs systemów baz danych", Wydanie III, Wydawnictwo Helion, 2011. »Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer Widom, "Systemy baz danych. Kompletny podręcznik", Wydanie II, Wydawnictwo Helion, 2011.  Literatura c.d. » Michael J. Hernandez, „Projektowanie baz danych dla każdego. Przewodnik krok po kroku”, Wydawnictwo Helion, 2014. » Jeffrey D. Ullman, Jennifer Widom „Implementacja systemów baz danych”, seria Klasyka Informatyki, WNT, 2003. » C.J. Date, "Wprowadzenie do baz danych", WNT, Warszawa 2000. » Guy Harrison, "NoSQL, NewSQL i BigData. Bazy danych następnej generacji", Helion, 2019 » John L. Viescas, Douglas J. Steele, Ben G. Clothier, "Mistrzowski SQL. 61 technik pisania wydajnego kodu SQL", Helion, 2017 » Miguel Cebollero, Michael Coles, Jay Natarajan, "T-SQL dla zaawansowanych. Przewodnik programisty", Helion, 2016.  Źródła internetowe Tutoriale: – https://www.sqlpedia.pl/kurs-sql/ – https://www.sqlservertutorial.net/ – https://www.sqltutorial.org/ – https://www.geeksforgeeks.org/sql-tutorial/  Bazy danych - wprowadzenie » Baza danych – uporządkowany zbiór danych z pewnej dziedziny tematycznej » Podstawowa charakterystyka i cechy baz danych – Reprezentują wybrany fragment rzeczywistości – Są zorganizowane w ściśle określony sposób w strukturach odpowiadających przyjętemu modelowi – Stanowią logiczną spójną kolekcję danych – Przechowują informacje w sposób trwały – Często charakteryzują się bardzo dużym rozmiarem  Bazy danych wprowadzenie c.d. » Baza danych to zbiór powiązanych ze sobą danych » Baza danych jest zarządzana przez SZBD – System Zarządzania Bazami Danych » Baza danych wraz z SZBD tworzy Systemy Baz Danych » Z systemem bazy danych współpracują programy użytkowników, zwane aplikacjami – zadaniem tych programów jest przetwarzanie danych » Baza danych, SZBD, aplikacje wchodzą w skład Systemu Informatycznego Systemy baz danych  Bazy danych wprowadzenie c.d. » Każda baza danych jest komputerową reprezentacją jakiego fragmentu świata rzeczywistego » Konstrukcja bazy danych polega na zbudowaniu pewnego modelu tego świata, w którym elementy tego świata zostaną odzwierciedlone w bazie danych » Budowaniem takich modeli rzeczywistości zajmuje się dziedzina wiedzy nazywana modelowaniem » Ponieważ w bazach danych głównym obszarem modelowania są dane – model danych jest zestawem pojęć używanych do opisu danych, związków między nimi oraz operacji na danych  Model danych » Spójny zestaw pojęć używanych do opisu: danych, związków między danymi, operacji na danych » Modele danych wykorzystywane w bazach danych: – wykorzystywane na poziomie konceptualnym (pojęciowym): model związków encji (ER – model) UML – wykorzystywane w rzeczywistych systemach relacyjny obiektowy relacyjno-obiektowy dokumentowy grafowy klucz-wartość  Relacyjny model danych » Obecnie najpopularniejszy model używany w relacyjnych systemach baz danych » Zaproponowany przez E.F. Codd’a w latach 1970 » Podstawową strukturą danych jest relacja będąca podzbiorem iloczynu kartezjańskiego określonego na zbiorach wartości atrybutów – stąd nazwa: relacyjny model danych, relacyjne bazy danych » Relacyjny model danych – atrybuty, krotki, relacje – atrybuty, wiersze, tabele  SQL » SQL – Structured Query Language – Język komunikacji z relacyjnymi bazami danych – Nieproceduralny (deklaratywny) język typu strukturalnego przeznaczony do uzyskiwania dostępu i operowania danymi oraz definiowania bazy danych » SQL – DDL – Data Definiton Language CREATE, ALTER, DROP – DML – Data Manipulation Language SELECT, INSERT, UPDATE, DELETE – DCL – Data Control Language GRANT, REVOKE  Polecenie SELECT select - określa kolumny from - określa tabelę where - określa warunek (pozwala wybrać wiersze) SELECT [ALL | DISTINCT] FROM WHERE  Przykładowa baza danych » Baza Northwind – Baza danych firmy sprzedającej artykuły spożywcze – Informacje o towarach, dostawcach, klientach, zamówieniach klientów itp..  Baza danych Northwind cd.. » Podstawowe tabele: – Categories kategorie oferowanych produktów – Products informacja o oferowanych produktach (nazwy, dostawcy, ceny…) – Suppliers informacja o dostawcach (nazwy, adresy…) – Shippers informacja o spedytorach (firmach zajmujących się dostawą towarów) – Customers informacja o klientach – Employees informacja o pracownikach  Baza danych Northwind cd.. » Podstawowe tabele: – Orders zamówienia składane przez klientów – Order Details szczegóły zamówień (lista zamawianych produktów) – Territories terytoria/obszary/miasta – Region Regiony – EmployeeTerritories informacja o terytoriach/obszarach/miastach obsługiwanych przez poszczególnych pracowników – CustomerDemographics grupy klientów – CustomerCustomerDemo przyporządkowanie klientów do grup  Typy danych » Znaki, napisy, teksty » Liczby » Daty, godziny » Dane binarne (np. obraz, dźwięk itp.) https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact- sql?view=sql-server-ver16  Typy danych cd.. » Wartości liczbowe (numeryczne) bigint liczba całkowita od –2^63 do 2^63 - 1 int liczba całkowita od -2^31 do 2^31 - 1 smallint liczba całkowita od -2^15 do 2^15 - 1 tinyint liczba całkowita od 0 do 255 bit liczba całkowita o wartości 0 lub 1  Typy danych » Wartości liczbowe (numeryczne) decimal liczby dziesiętne o wartościach stałoprzecinkowych od –10^38 + 1 do 10^38 – 1 money wartości walutowe od –2^63 do 2^63 - 1 smallmoney wartości walutowe od –214 748,3648 do 214 748,3647 float wartości zmiennoprzecinkowe od –1,79E+308 do 1,79E+308 real wartości zmiennoprzecinkowe od –3,40E+38 do 3,40E+38  Typy danych » Data i czas datetime wartości daty i czasu od 1 stycznia 1753 do 31 grudnia 9999 roku smalldatetime wartości daty i czasu od 1 stycznia 1900 do 6 czerwca 2079 roku date tylko data time tylko czas  Typy danych » Znaki, napisy, teksty char napisy o stałej długości max. dł. 8000 znaków varchar napisy o zmiennej długości, max. dł. 8000 znaków text teksty, max. dł. 2^31-1 znaków Nchar kodowane w unicode napisy o stałej długości, max. dł. 4000 znaków Nvarchar kodowane w unicode dane o zmiennej długości, max. dł. 4000 znaków Ntext kodowane w unicode teksty, max. dł. 2^30-1 znaków  Typy danych » Dane binarne binary dane binarne o stałej długości, max. dł. wynosi 8000 bajtów Varbinary dane binarne o zmiennej długości, max. dł. wynosi 8000 bajtów image dane binarne o zmiennej długości, max. dł. wynosi 2^31-1 bajtów  Tabela Categories » Zawiera informacje o kategoriach produktów CategoryID int(4) identyfikator kategorii CategoryName nvarchar(15) nazwa kategorii Description ntext(16) opis Picture image(16) zdjęcie Tabela Categories - definicja  Tabela Products » Zawiera informacje o produktach, towarach ProductID int(4) identyfikator produktu ProductName nvarchar(40) nazwa produktu SupplierID int(4) identyfikator dostawcy CategoryID int(4) identyfikator kategorii produktu QuantityPerUnit nvarchar(20) informacja o ilosci w jednostce (np 20 sztuk w opakowaniu) UnitPrice money(8) cena jednostkowa UnitsInStock smallint(2) stan magazynu, ilość jednostek towaru w magazynie UnitsOnOrder smallint(2) ilość zamówiona, ilość zamówionych jednostek towaru ReorderLevel smallint(2) minimalna ilość w magazynie Discountinued bit(1) wycofany (tak/nie) Tabela Products - definicja  Tabela Suppliers » Zawiera informacje o dostawcach SupplierID int(4) identyfikator dostawcy CompanyName nvarchar(40) nazwa dostawcy (firmy będącej dostawcą) ContactName nvarchar nazwisko i imię reprezentanta ContactTitle nvarchar(30) stanowisko/tytuł reprezentanta Address nvarchar(60) adres dostawcy (firmy będącej dostawcą) City nvarchar(15) miasto Region nvarchar(15) region PostalCode nvarchar(10) kod pocztowy Country nvarchar(15) kraj Phone nvarchar(24) telefon Fax nvarchar(24) fax HomePage ntext(16) strona www firmy  Tabela Shippers » Zawiera informacje o spedytorach (firmach zajmujących się dostawą towarów) ShipperID int(4) identyfikator spedytora CompanyName nvarchar(40) nazwa firmy Phone nvarchar(24) telefon  Tabela Customers » Zawiera informacje o klientach CustomerID nchar(5) unikatowy identyfikator pięcioznakowy wzorowany na nazwie klienta CompanyName nvarchar(40) nazwa klienta (firmy będącej klientem) ContactName nvarchar(30) nazwisko i imię reprezentanta ContactTitle nvarchar(30) stanowisko/tytuł reprezentanta Address nvarchar(60) nazwa klienta (firmy będącej klientem) City nvarchar(15) miasto Region nvarchar(15) region PostalCode nvarchar(10) kod pocztowy Country nvarchar(10) kraj Phone nvarchar(24) nr telefonu Fax nvarchar(24) nr faxu  Tabela Orders » Zawiera informacje o zamówieniach OrderID int(4) identyfikator zamówienia CustomerID nchar(5) identyfikator klienta EmployeeID int(4) identyfikator pracownika (obsługującego, wystawiającego zamówienie) OrderDate datetime(8) data zamówienia RequiredDate datetime(8) wymagana data realizacji ShippedDate datetime(8) data wysyłki ShipVia int(4) identyfikator spedytora Freight money(8) opłata za przesyłkę (fracht) ShipName nvarchar(40) nazwa/nazwisko odbiorcy ShipAddres nvarchar(60) adres odbiorcy ShipCity nvarchar(15) miasto odbiorcy ShipRegion nvarchar(15) region odbiorcy ShipPostalCode nvarchar(10) kod pocztowy ShipCountry nvarchar(15) kraj odbiorcy  Tabela Order Details » Zawiera informacje o szczegółach zamówienia (pozycjach zamówienia) OrderID int(4) identyfikator zamówienia ProductID int(4) identyfikator zamawianego produktu UnitPrice money(8) cena jednostkowa Quantity smallint(2) ilość Discount real(4) rabat  Tabela Employees » Zawiera informacje o pracownikach EmployeeID int(4) identyfikator pracownika LastName nvarchar(20) nazwisko FirstName nvarchar(10) imie Title nvarchar(30) tytuł/stanowisko TitleOfCourtesy nvarchar(25) zwrot grzecznościowy (np. używany w korespondencji) BirthDate datetime(8) data urodzenia HireDate datetime(8) data zatrudnienia Notes ntext(16) uwagi dotyczące pracownika ReportsTo int(4) identyfikator przełożonego PhotoPath nvarchar(255) zdjęcie (adres strony www)  Tabela Employees cd.. » Zawiera informacje o pracownikach Address nvarchar(60) adres pracownika City nvarchar(15) miasto Region nvarchar(15) region PostalCode nvarchar(10) kod pocztowy Country nvarchar(15) kraj HomePhone nvarchar(24) telefon domowy Extension nvarchar(4) telefon wewnętrzny Photo image(16) zdjęcie  Tabela Region » Zawiera informacje o regionach RegionID int(4) identyfikator regionu RegionDescription nchar(50) opis regionu  Tabela Territories » Zawiera informacje o terytoriach/obszarach/miastach TerritoryID nvarchar(20) identyfikator obszaru TerritoryDescription nchar(50) opis, nazwa RegionID int(4) identyfikator regionu  Tabela EmployeeTerritories » Zawiera informacje o terytoriach/obszarach/miastach obsługiwanych przez poszczególnych pracowników EmplyeeID int(4) identyfikator pracownika TerritoryID nvarchar(20) identyfikator obszaru  Tabela CustomerDemographics » Zawiera informacje o grupach klientów CustomerTypeID int(4) identyfikator grupy CustomerDesc nvarchar(15) opis, charakterystyka grupy  Tabela CustomerCustomerDemo » Zawiera informacje o przyporządkowaniu klientów do grup CustomerID int(4) identyfikator klienta CustomerTypeID int(4) identyfikator grupy  Wprowadzenie » Zastosowania technologii bazodanowych » Kluczowe definicje » Podejście oparte o pliki vs bazodanowe » Elementy systemu bazodanowego » Zalety systemów bazodanowych  Zastosowania technologii baz danych 1. Przechowywanie i wyszukiwanie tradycyjnych danych numerycznych/alfanumerycznych 2. Aplikacje multimedialne 3. Aplikacje biometryczne 4. Aplikacje mobilne 5. Systemy Informacji Geograficznej (GIS) 6. Czujniki 7. Aplikacje Big Data 8. Aplikacje Internetu Rzeczy (IoT) 9. Aplikacje social media  Kluczowe definicje » Baza danych – kolekcja powiązanych elementów danych w ramach określonego procesu biznesowego lub podstawionego problemu – ma docelową grupę użytkowników i aplikacji » System Zarządzania Bazami Danych (DBMS) - pakiet oprogramowania używany do definiowania, tworzenia, używania i utrzymywania bazy danych – składa się z kilku modułów oprogramowania » Połączenie DBMS to system bazodanowy Podejście oparte o pliki vs bazodanowe Invoicing CRM GIS CustomerNr CustomerNr CustomerNr CustomerName CustomerName CustomerName VATcode Turnover ZipCode Duplicate data!  Podejście oparte o pliki vs bazodanowe Początki: aplikacje bazodanowe budowane bezpośrednio na systemach plików, co prowadziło do: » Przechowywanie zduplikowanych lub nadmiarowych informacji » Niebezpieczeństwo niespójnych danych » Silne powiązanie między danymi i aplikacjami » Trudności w zarządzaniu kontrolą współbieżności » Aplikacje trudne do zintegrowania Podejście oparte o pliki vs bazodanowe Invoicing CRM GIS DBMS Raw data Catalog  Podejście oparte o pliki vs bazodanowe » Podejście oparte o bazę danych – przewyższa podejście oparte o pliki pod względem wydajności, spójności i zarządzania – luźne połączenie między aplikacjami a danymi – zapewnione udogodnienia do odpytywania i pobierania danych  Podejście oparte o pliki vs bazodanowe O pliki O bazę danych (SQL) Procedure FindCustomer; SELECT * begin FROM Customer open file Customer.txt; WHERE Read(Customer) name = 'Bart' While not EOF(Customer) If Customer.name='Bart' then display(Customer); EndIf Read(Customer); EndWhile; End;  Elementy systemu bazodanowego Model bazy danych a instancje Modele danych Architektura trójwarstwowa Rola katalogu Użytkownicy bazy danych Języki bazodanowe  Model bazy danych a instancje » Model bazy danych dostarcza opisu danych w bazie danych na różnych poziomach szczegółowości i specyfikuje różne elementy danych, ich cechy i zależności, ograniczenia, szczegóły dotyczące przechowywania, itp – określony podczas projektowania bazy danych i nie oczekuje się, że będzie się zbyt często zmieniać – przechowywany w katalogu » Stan bazy danych reprezentuje dane w bazie danych w określonym momencie – zbiór instancji – zazwyczaj zmienia się na bieżąco  Model bazy danych a instancje » Model bazy danych Student (number, name, address, email) Course (number, name) Building (number, address)  Model bazy danych a instancje » Stan bazy danych  Model danych » Model bazy danych składa się z różnych modeli danych, z których każdy opisuje dane z różnych perspektyw – koncepcyjny – logiczny – wewnętrzny – zewnętrzny » Model danych zapewnia jasny i jednoznaczny opis elementów danych, ich relacji i różnych ograniczeń danych z określonej perspektywy  Model danych » Koncepcyjny model danych zapewnia wysokopoziomowy opis elementów danych wraz z ich charakterystykami i relacjami – instrument komunikacji pomiędzy architektem informacji a użytkownikiem biznesowym – powinien być niezależny od wdrożenia, przyjazny dla użytkownika i zbliżony do tego, jak użytkownik biznesowy postrzega dane – zwykle reprezentowany przez model ER (EER) lub modelu zorientowanego obiektowo (UML) » Logiczny model danych to translacja lub mapowanie konceptualnego modelu danych na określone środowisko implementacyjne – model hierarchiczny, CODASYL, relacyjny, obiektowy, rozszerzony relacyjny, XML lub NoSQL  Model danych » Logiczny model danych można zmapować do wewnętrznego modelu danych, który reprezentuje fizyczne szczegóły przechowywania danych – jasno opisuje, które dane są przechowywane, gdzie, w jakim formacie, jakie indeksy są dostarczane w celu przyspieszenia wyszukiwania itp. – ściśle związany z DBMS » Zewnętrzny model danych zawiera różne podzbiory elementów danych w modelu logicznym, zwane również widokami, dostosowane do potrzeb konkretnych aplikacji lub grup użytkowników Architektura trójwarstwowa dane fizyczne + logiczna niezależność danych Architektura trójwarstwowa  Katalog » Jądro DBMS » Zawiera definicje danych oraz metadane » Przechowuje definicje widoków, logiczne i wewnętrzne modele danych oraz synchronizuje te trzy modele danych, aby zapewnić ich spójność  Użytkownicy bazy danych » Architekt informacji projektuje koncepcyjny model danych – ściśle współdziała z użytkownikiem biznesowym » Projektant bazy danych przekłada koncepcyjny model danych na logiczny i wewnętrzny model danych » Administrator bazy danych (DBA) odpowiada za wdrożenie i monitorowanie bazy danych » Twórca aplikacji tworzy aplikacje bazodanowe w języku programowania (Java, Python…) » Użytkownik biznesowy uruchamia aplikacje, aby wykonać określone operacje na bazie danych  Języki bazodanowe » Data Definition Language (DDL) jest używany przez DBA do wyrażenia zewnętrznych, logicznych modeli danych bazy danych – definicje są przechowywane w katalogu » Data Manipulation Language (DML) służy do pobierania, wstawiania, usuwania i modyfikowania danych – Instrukcje DML mogą być osadzone w języku programowania lub wprowadzane interaktywnie za pomocą narzędzia front-end do wysyłania zapytań » Structured Query Language (SQL) oferuje zarówno instrukcje DDL, jak i DML dla relacyjnych systemów baz danych  Zalety systemów bazodanowych i zarządzania bazami danych » Niezależność danych » Modelowanie baz danych » Zarządzanie danymi ustrukturyzowanymi, częściowo ustrukturyzowanymi i nieustrukturyzowanymi » Zarządzanie nadmiarowością danych » Określanie zasad integralności » Kontrola współbieżności » Funkcje tworzenia kopii zapasowych i odzyskiwania » Bezpieczeństwo danych » Narzędzia wydajności  Niezależność danych » Niezależność danych oznacza, że zmiany w definicjach danych mają minimalny lub żaden wpływ na aplikacje » Fizyczna niezależność danych oznacza, że ani aplikacje, ani widoki ani logiczny model danych nie muszą być zmieniane, gdy wprowadzane są zmiany w specyfikacjach przechowywania danych w wewnętrznym modelu danych – DBMS powinien zapewniać interfejsy między logicznymi a wewnętrznymi modelami danych » Logiczna niezależność danych oznacza, że na aplikacje w minimalnym stopniu wpływają zmiany w koncepcyjnym lub logicznym modelu danych – widoki w zewnętrznym modelu danych będą działać jak tarcza ochronna – DBMS musi zapewniać interfejsy między warstwą koncepcyjną/logiczną a zewnętrzną  Modelowanie baz danych » Model danych to jawna reprezentacja elementów danych wraz z ich charakterystykami i zależnościami » Koncepcyjny model danych powinien zapewniać poprawne odwzorowanie wymagań dotyczących danych w procesie biznesowym i jest tworzony we współpracy z użytkownikiem biznesowym – przekładany na logiczny i wewnętrzny model danych » Ważne, aby założenia i niedociągnięcia modelu danych były jasno udokumentowane  Zarządzanie danymi ustrukturyzowanymi, częściowo ustrukturyzowanymi i nieustrukturyzowanymi » Dane ustrukturyzowane – można opisać zgodnie z formalnym logicznym modelem danych – możliwość wyrażania reguł integralności i egzekwowania poprawności danych – ułatwia również wyszukiwanie, przetwarzanie i analizę danych – np. identyfikator, nazwisko i adres studenta » Dane nieustrukturyzowane – brak drobnoziarnistych komponentów w pliku lub serii znaków, które mogą być interpretowane w znaczący sposób przez DBMS lub aplikację – Uwaga: ilość danych nieustrukturyzowanych przewyższa ilość danych ustrukturyzowanych  Zarządzanie danymi ustrukturyzowanymi, częściowo ustrukturyzowanymi i nieustrukturyzowanymi » Dane częściowo ustrukturyzowane – mają określoną strukturę - ale może być bardzo nieregularna lub bardzo niestabilna – Np. strony internetowe poszczególnych użytkowników na platformie mediów społecznościowych lub dokumenty życiorysów w bazie danych HR  Zarządzanie nadmiarowością danych » Powielanie danych może być pożądane w środowiskach rozproszonych w celu poprawy wydajności odzyskiwania danych » DBMS jest teraz odpowiedzialny za zarządzanie redundancją poprzez zapewnienie możliwości synchronizacji w celu zapewnienia spójności danych » W porównaniu z podejściem plikowym, DBMS gwarantuje poprawność danych bez ingerencji użytkownika  Określanie reguł integralności » Reguły syntaktyczne określają, w jaki sposób dane powinny być reprezentowane i przechowywane – Np. IDKlienta jest liczbą całkowitą; data urodzenia powinna być przechowywana w formacie miesiąc, dzień i rok » Reguły semantyczne skupiają się na poprawności semantycznej lub znaczeniu danych – Np. identyfikator klienta jest unikalny; saldo konta powinno być > 0; klient nie może zostać usunięty, jeśli ma oczekujące faktury » Reguły integralności są określone jako część koncepcyjnego/logicznego modelu danych i przechowywane w katalogu – bezpośrednio wymuszane przez DBMS zamiast aplikacji  Kontrola współbieżności » DBMS ma wbudowane udogodnienia do obsługi współbieżnego lub równoległego wykonywania programów bazodanowych » Kluczowym pojęciem jest transakcja bazy danych – sekwencja operacji odczytu/zapisu uważana za jednostkę atomową - albo wszystkie operacje są wykonywane, albo wcale » Operacje odczytu/zapisu mogą być wykonywane w tym samym czasie przez DBMS » DBMS powinien unikać niespójności  Kontrola współbieżności Problem utraconych aktualizacji Time T1 T2 balance t1 Begin transaction $100 t2 Begin transaction read(balance) $100 t3 read(balance) balance=balance+120 $100 t4 balance=balance-50 write(balance) $220 t5 write(balance) End transaction $50 29 t6 End transaction $50  Kontrola współbieżności » DBMS musi wspierać własność ACID (Atomicity, Consistency, Isolation, Durability) – Atomowość wymaga, aby transakcja została wykonana w całości lub wcale – Spójność zapewnia, że transakcja przenosi bazę danych z jednego spójnego stanu do drugiego – Izolacja zapewnia, że efekt równoczesnych transakcji powinien być taki sam, jak gdyby były wykonywane w izolacji – Trwałość zapewnia, że zmiany w bazie danych dokonane przez transakcję uznaną za udaną muszą być trwałe w każdych okolicznościach  Funkcje tworzenia kopii zapasowych i odzyskiwania » Mogą być wykorzystywane do radzenia sobie ze skutkami utraty danych spowodowanych błędami sprzętowymi lub sieciowymi lub błędami w systemie lub oprogramowaniu » Można wykonywać pełną lub przyrostową kopię zapasową » Możliwości odtwarzania pozwalają na przywrócenie danych do stanu poprzedniego po ich utracie lub uszkodzeniu  Bezpieczeństwo danych » Bezpieczeństwo danych egzekwowane przez DBMS » Niektórzy użytkownicy mają dostęp do odczytu, podczas gdy inni mają dostęp do zapisu danych (funkcja oparta na rolach) » Dostęp do danych może być zarządzany za pomocą loginów i haseł przypisanych do kont użytkowników » Każde konto ma własne reguły autoryzacji, które można przechowywać w katalogu  Aspekty wydajności DBMS » KPI (Key Performance Indicator) dla DBMS: – czas odpowiedzi oznaczający czas, jaki upłynął od wysłania żądania do bazy danych do jego pomyślnego zakończenia – przepustowość reprezentująca transakcje, które DBMS może przetwarzać w jednostce czasu – wykorzystanie przestrzeni w odniesieniu do przestrzeni wykorzystywanej przez DBMS do przechowywania zarówno surowych danych, jak i metadanych » DBMS są dostarczane z różnymi rodzajami narzędzi mających na celu poprawę tych KPI – Np. narzędzia do dystrybucji i optymalizacji przechowywania danych, dostrajania indeksów w celu szybszego wykonywania zapytań, dostrajania zapytań w celu poprawy wydajności aplikacji lub optymalizacji zarządzania buforami » Architektura DBMS » Kategoryzacja DBMS Architektura DBMS 2  Architektura DBMS » Menadżer połączeń i menadżer bezpieczeństwa (Connection and Security Manager) » Kompilator DDL (DDL Compiler) » Procesor zapytań (Query Procesor) » Menadżer przechowywania (Storage Manager) » Narzędzia DBMS (DBMS Utilities) » Interfejsy DBMS (DBMS Interfaces)  Menadżer połączeń i menadżer bezpieczeństwa » Menadżer połączeń zapewnia możliwość konfiguracji połączenia z bazą danych (lokalnie lub przez sieć) – weryfikuje dane logowania – połączenie z bazą danych może działać jako pojedynczy proces lub jako wątek w ramach procesu » Menadżer bezpieczeństwa weryfikuje, czy użytkownik ma odpowiednie uprawnienia – dostęp do odczytu vs dostęp do zapisu  Kompilator DDL » Kompiluje definicje danych określone w DDL » 3 DDL’e (wewnętrzny/logiczny/zewnętrzny model) » Kompilator DDL – najpierw analizuje definicje DDL i sprawdza ich poprawność składniową – następnie tłumaczy definicje danych na format wewnętrzny i w razie potrzeby generuje błędy – po udanej kompilacji rejestruje definicje danych w katalogu  Procesor zapytań » Asystuje w wykonywaniu zapytań do bazy danych, takich jak pobieranie, wstawianie, aktualizacja lub usuwanie danych » Kluczowe komponenty: – Kompilator DML (DML Compiler) – Parser zapytań (Query Parser) – Przepisywacz zapytań (Query Rewriter) – Optymalizator zapytań (Query Optimizer) – Wykonawca zapytań (Query Executor)  Kompilator DML » Kompilator DML kompiluje instrukcje DML » Proceduralny DML – DML w sposób jawny określa jak poruszać się po bazie danych – record-at-a-time DML (zorientowany na rekord) – brak procesora zapytań » Deklaratywny DML – DML określa jakie dane należy pobrać lub jakie zmiany należy wprowadzić – set-at-a-time DML (zorientowany na zbiór) – procesor zapytań  Języki imperatywne i deklaratywne StreamReader sr = new StreamReader("c:\\Nazwiska.txt"); string FirstName = null; while ((FirstName = sr.ReadLine()) != null) { Console.WriteLine(s); } sr.Dispose(); SELECT FirstName FROM Person.Person;  Kompilator DML try { import java.sql.*; System.out.println("Connecting to database"); public class JDBCExample1 { connection = DriverManager.getConnection(url, public static void main(String[] args) { username, password); System.out.println("MySQL Database connected!"); try { stmt = connection.createStatement(); System.out.println("Loading JDBC driver..."); ResultSet rs = stmt.executeQuery(query); Class.forName("com.mysql.jdbc.Driver"); while (rs.next()) { System.out.println("JDBC driver loaded!"); System.out.print(rs.getString(1)); System.out.print(" "); } catch (ClassNotFoundException e) { System.out.println(rs.getString(2)); throw new RuntimeException(e); } } stmt.close(); String url = "jdbc:mysql://localhost:3306/employeeschema";} catch (SQLException e) { System.out.println(e.toString()); String username = "root"; } finally { String password = "mypassword123"; System.out.println("Closing the connection."); String query = "select E.Name, D.DName" + if (connection != null) { "from employee E, department D" + try { connection.close(); "where E.DNR=D.DNR;"; } catch (SQLException ignore) {}}}} Connection connection = null; Statement stmt=null;  Kompilator DML » Problem niedopasowania impedancji – mapowanie między pojęciami OO (np. Java) a relacyjnymi (np. SQL) » Rozwiązania niedopasowania impedancji – język hosta i DBMS o porównywalnych strukturach danych (np. Java i OODBMS) – oprogramowanie pośredniczące do mapowania struktur danych z DBMS na język hosta i odwrotnie  Kompilator DML Java public class Employee { private int EmployeeID; private String Name; SQL private String Gender; private int DNR; CREATE TABLE Employee ( 'EmployeeID' INT NOT NULL, public int getEmployeeID() { 'Name' VARCHAR(45) NULL, return EmployeeID; } 'Gender' VARCHAR(45) NULL, public void setEmployeeID( int id ) { 'DNR' INT NULL) this.EmployeeID = id; } EmployeeID Name Gender DNR public String getName() { 100 Bart Baesens Male 2 return Name; 110 Wilfried Lemahieu Male 4 } public void setName( String name ) { 120 Seppe vanden Broucke Male 6 this.Name = name; … } …}  Kompilator DML » Kompilator DML rozpoczyna od wyodrębnienia instrukcji DML z języka hosta » Współpracuje następnie z parserem zapytań, przepisywaczem zapytań, optymalizatorem zapytań i wykonawcą zapytań w celu wykonania instrukcji DML » Błędy generowane i zgłaszane w razie potrzeby 1 2  Parser zapytań i przepisywanie zapytań » Parser zapytań analizuje zapytanie do wewnętrznego formatu reprezentacji » Parser zapytań sprawdza zapytanie pod kątem poprawności składniowej i semantycznej » Przepisywanie zapytań optymalizuje zapytanie niezależnie od aktualnego stanu bazy danych  Optymalizator zapytań » Optymalizuje zapytanie na podstawie aktualnego stanu bazy danych (na podstawie np. predefiniowanych indeksów) » Opracowuje różne plany wykonania zapytań i ocenia ich koszt pod kątem szacunkowych – liczby operacji we/wy – koszt przetwarzania procesora – czasu wykonywania » Szacunki oparte na informacjach katalogowych w połączeniu z wnioskami statystycznymi » Optymalizator zapytań jest kluczowym atutem DBMS  Wykonawca zapytań » Wynikiem optymalizacji zapytania jest ostateczny plan wykonania » Wykonawca zapytania zajmuje się faktycznym wykonaniem, wywołując menedżera pamięci w celu pobrania żądanych danych 1 5  Menadżer przechowywania » Menadżer przechowywania zarządza fizycznym dostępem do plików i nadzoruje prawidłowe i wydajne przechowywanie danych » Składa się z – menadżera transakcji – menadżera buforów – menadżera blokad – menadżera odzyskiwania  Menadżer transakcji » Nadzoruje realizację transakcji bazodanowych – transakcja bazodanowa to sekwencja operacji odczytu/zapisu uważana za jednostkę atomową » Menadżer transakcji tworzy harmonogram z przeplatanymi operacjami odczytu/zapisu » Menadżer transakcji gwarantuje właściwość ACID » COMMIT transakcji po pomyślnym wykonaniu i ROLLBACK po nieudanej realizacji  Menadżer buforów » Menadżer buforów zarządza pamięcią buforową DBMS – inteligentnie buforuje dane w buforze » Przykładowe strategie: – Lokalizacja danych: ostatnio pobrane dane prawdopodobnie zostaną ponownie pobrane – Prawo 20/80: 80% transakcji odczytuje lub zapisuje tylko 20% danych » Menadżer buforów musi przyjąć strategię inteligentnej wymiany w przypadku zapełnienia bufora » Menadżer bufora musi współpracować z menedżerem blokad  Menadżer blokad » Zapewnia kontrolę współbieżności, co zapewnia integralność danych przez cały czas » Dwa rodzaje blokad: blokady do odczytu i zapisu » Menadżer blokad odpowiedzialny za przypisywanie, zwalnianie i rejestrowanie blokad w katalogu » Menadżer blokad korzysta z protokołu blokowania, który opisuje zasady blokowania, oraz tabeli blokad z informacjami o blokadzie  Menadżer odzyskiwania » Nadzoruje poprawność wykonywania transakcji bazodanowych » Śledzi wszystkie operacje bazy danych w pliku dziennika » Zostanie wezwany do cofnięcia czynności przerwanych transakcji lub podczas odzyskiwania po awarii  Narzędzia DBMS » Narzędzia do ładowania » Narzędzia do reorganizacji » Narzędzia do monitorowania wydajności » Narzędzia do zarządzania użytkownikami » Narzędzia do tworzenia kopii zapasowych i odzyskiwania  Interfejsy DBMS » Interfejs webowy » Interfejs linii komend » Interfejs oparty na formularzach » Graficzny interfejs użytkownika » Interfejs w języku naturalnym » Interfejs administratora » Interfejs sieciowy » …  Kategoryzacja DBMS » Na podstawie: – modelu danych – stopnia równoczesnego dostępu – architektury – użycia  Kategoryzacja na podstawie modelu danych » Hierarchiczne DBMS – model danych podobny do drzewa – DML jest proceduralny i zorientowany na rekordy – brak procesora zapytań (model logiczny i wewnętrzny przeplatają się) – np. IMS (IBM) » Sieciowe DBMS – wykorzystują sieciowy model danych – CODASYL DBMS – DML jest proceduralny i zorientowany na rekordy – brak procesora zapytań (model logiczny i wewnętrzny przeplatają się) – CA-IDMS (Computer Associates)  Kategoryzacja na podstawie modelu danych » Relacyjne DBMS – wykorzystuje relacyjny model danych – obecnie najpopularniejszy – SQL (deklaratywny i zorientowany na zbiór) – procesor zapytań – ścisły rozdział między logicznym i wewnętrznym modelem danych – np. MySQL (open source, Oracle), Oracle DBMS (Oracle), DB2 (IBM), Microsoft SQL (Microsoft)  Kategoryzacja na podstawie modelu danych » Zorientowane obiektowo DBMSs (OODBMS) – oparte na obiektowym modelu danych – brak niedopasowania impedancji w połączeniu z obiektowym językiem hosta – np. db4o (open source, Versant), Caché (Intersystems) GemStone/S (GemTalk Systems) – sukcesy tylko na rynkach niszowych, ze względu na ich złożoność  Kategoryzacja na podstawie modelu danych » Obiektowo-relacyjny DBMSs (ORDBMSs) – określane również jako rozszerzone relacyjne DBMS (ERDBMSs) – używa modelu relacyjnego rozszerzonego o koncepcje obiektowe – DML jest SQL (deklaratywny i zorientowany na zbiór) – np. Oracle DBMS (Oracle), DB2 (IBM), Microsoft SQL (Microsoft) Kategoryzacja na podstawie modelu danych XML DBMS Bart – używa modelu XML do Baesens przechowywania danych – Natywne XML DBMS Naamsestraat (np. BaseX, eXist) 69 mapują strukturę drzewa dokumentu 3000 XML na fizyczną Leuven strukturę pamięci Belgium – DBMS z obsługą XML (np. Oracle, IBM DB2) to istniejące DBMS, Male które zostały rozszerzone o funkcje przechowywania danych XML 28  Kategoryzacja na podstawie modelu danych » NoSQL DBMS – ukierunkowane na przechowywanie dużych i nieustrukturyzowanych danych – można podzielić na magazyny klucz-wartość, bazy danych zorientowane kolumnowo i grafowe bazy danych – skupiają się na skalowalności i zdolności radzenia sobie z nieregularnymi lub wysoce niestabilnymi strukturami danych – np. Apache Hadoop, MongoDB, Neo4j  Trzy rewolucje związane z bazami danych » Pierwsza rewolucja zainicjowana pojawieniem się komputera elektronicznego po II wojnie » Druga rewolucja – wynik powstania relacyjnej bazy danych » Trzecia rewolucja spowodowana eksplozją alternatyw w postaci nierelacyjnych baz danych – odpowiedź na zapotrzebowanie nowoczesnych aplikacji wymagających globalnego zasięgu i ciągłej dostępności 3 1  Kategoryzacja na podstawie stopnia równoczesnego dostępu » Systemy jedno- i wieloużytkownikowe  Kategoryzacja na podstawie architektury » Scentralizowana architektura DBMS – dane są utrzymywane na centralnym serwerze » Architektura klient-serwer – aktywni klienci żądają usług od serwerów pasywnych – gruby serwer vs gruby klient » n-warstwowa architektura DBMS – klient z funkcjonalnością GUI, serwer aplikacji z aplikacjami, serwer bazy danych z DBMS i bazą danych oraz serwer WWW do dostępu przez WWW  Kategoryzacja na podstawie architektury » Chmurowa architektura DBMS – DBMS i baza danych są hostowane przez zewnętrznego dostawcę chmury – np. projekt Apache Cassandra i Google’s BigTable » Sfederowany DBMS – zapewnia jednolity interfejs do wielu źródeł danych – ukrywa szczegóły przechowywania danych aby ułatwić dostęp do danych  Kategoryzacja na podstawie architektury » in-memory DBMS (w pamięci) – przechowuje wszystkie dane w pamięci wewnętrznej zamiast wolniejszej pamięci zewnętrznej (np. dysk) – często wykorzystywane do zastosowań czasu rzeczywistego – np. HANA (SAP), SQLite (częściowo)  Kategoryzacja na podstawie użycia » Przetwarzanie transakcyjne w trybie on-line (On-line transaction processing - OLTP) – skupia się na zarządzaniu danymi operacyjnymi lub transakcyjnymi – serwer bazy danych musi być w stanie przetwarzać wiele prostych transakcji w jednostce czasu – DBMS musi mieć dobre wsparcie dla przetwarzania dużej liczby krótkich, prostych zapytań » Przetwarzanie analityczne w trybie on-line (On-line analytical processing - OLAP) – skupia się na wykorzystaniu danych operacyjnych do podejmowania decyzji taktycznych lub strategicznych – ograniczona liczba użytkowników formułuje złożone zapytania – DBMS powinien wspierać wydajne przetwarzanie złożonych zapytań, które często przychodzą w mniejszych ilościach  Kategoryzacja na podstawie użycia » Big Data & Analytics – bazy danych NoSQL – skupia się na bardziej elastycznych, a nawet pozbawionych schematów strukturach baz danych – przechowuje nieustrukturyzowane informacje, takie jak e- maile, dokumenty tekstowe, tweety na Twitterze, posty na Facebooku itp. » Multimedia – multimedialne DBMS zapewniają przechowywanie danych multimedialnych, takich jak tekst, obrazy, audio, wideo, gry 3D itp. – powinny również zapewniać narzędzia do zapytań opartych na treści  Kategoryzacja na podstawie użycia » Aplikacje przestrzenne – przestrzenne DBMS obsługują przechowywanie i odpytywanie danych przestrzennych (zarówno 2D, jak i 3D) – Geographical Information Systems (GIS) » Czujniki – dane z czujników, np. do śledzenia aktywności, dane telematyczne  Kategoryzacja na podstawie użycia » Mobilne – działają na smartfonach, tabletach lub innych urządzeniach mobilnych – powinny zawsze być online, mieć niewielką powierzchnię i być w stanie poradzić sobie z ograniczoną mocą przetwarzania, pamięcią masową i żywotnością baterii » Open source – kod DBMS typu open source jest publicznie dostępny i może być rozszerzany przez każdego – www.sourceforge.net – np. MySQL (Oracle)  Zagadnienia » Fazy projektowania bazy danych » Model związków encji (Entity Relationship - ER) » Rozszerzony model związków (Enhanced Entity Relationship - EER) » UML  Ad 1. Gromadzenie i analiza wymagań » Wywiady z końcowymi użytkownikami – aby zrozumieć i udokumentować ich wymagania danych – zwięźle zapisany zbiór wymagań użytkowników » Wymagania danych a wymagania funkcjonalne dla projektowanej aplikacji – określone operacje/transakcje – diagramy przepływu danych, sekwencji, scenariusze – inżynieria oprogramowania  Opis mini-świata – zarządzanie danymi pracowników, działów i projektów Cel: baza ma umożliwić zarzadzanie danymi pracowników, działów oraz realizowanych projektów »Firma jest podzielona na działy. »Każdy z działów ma unikatową nazwę, unikatowy numer oraz przydzielonego konkretnego pracownika, który tym działem kieruje. »W BD należy utrzymywać datę początkową, od której dany pracownik kieruje wskazanym działem. »Każdy dział może być rozproszony i znajdować się w wielu miejscach. »Dział kontroluje wiele projektów, z których każdy ma unikatową nazwę, unikatowy numer oraz jedno miejsce realizacji.  Opis mini-świata – zarządzanie danymi pracowników, działów i projektów c.d. » W bazie danych musi być przechowywane nazwisko i Pesel, adres, wysokość pensji, płeć i data urodzenia każdego pracownika firmy. » Pracownik musi być przypisany do jednego działu, ale może pracować nad wieloma projektami, które niekoniecznie muszą być kontrolowane przez ten sam dział. » W bazie danych będziemy rejestrować liczbę godzin, które pracownicy poświęcają poszczególnym projektom w ciągu tygodnia. » Dla każdego pracownika będziemy dodatkowo przechowywali informację o bezpośrednim zwierzchniku  Opis mini-świata – zarządzanie danymi pracowników, działów i projektów c.d. » Chcemy przechowywać (np. w celach ubezpieczeniowych) informacje o rodzinach poszczególnych pracowników. » Dla każdego członka rodziny pracownika firmy w bazie danych będą utrzymywane następujące elementy danych: imię, płeć, data urodzenia oraz stopień pokrewieństwa z pracownikiem.  Ad 2. Projektowanie koncepcyjne » Stworzenie schematu koncepcyjnego w oparciu o wysokopoziomowy, koncepcyjny model danych » Schemat koncepcyjny = zwięzły opis wymagań danych – szczegółowe opisy typów encji, związków oraz ograniczeń – wyrażane za pomocą elementów wysokopoziomowego modelu danych  Ad 3. Projektowanie logiczne (odwzorowanie modelu danych) » Implementacja w oparciu o jeden z SZBD » Wynik: schemat bazy danych zgodny z modelem implementacyjnym wykorzystywanym w danym SZBD » schemat koncepcyjny => przekształcany z wysokopoziomowego modelu danych w implementacyjny model danych  Ad 4. Projektowanie fizyczne » Definiuje się: – wewnętrzne struktury składowania – organizację plików – indeksy – ścieżki dostępu – fizyczne aspekty plików baz danych » Również projektowanie i implementowanie programów aplikacji bazy danych  Model związków encji (Entity Relationship - ER) » Typy encji » Typy atrybutów » Typy związków » Słabe typy encje » Związki ternarne (trójskładnikowe) » Przykłady modelu ER » Ograniczenia modelu ER Peter Chen “The Entity–Relationship Model: Toward A Unified View of Data”,1975, ACM Transactions on Database Systems. » uważany za jeden z najbardziej wpływowych artykułów dziedzinie oprogramowania komputerowego  Typy encji » Reprezentuje pojęcie biznesowe o jednoznacznym znaczeniu dla określonej grupy użytkowników – np. dostawca, student, produkt, pracownik… » Encja to jedno określone wystąpienie lub instancja typu encji – np. Deliwines, Best Wines i Ad Fundum to encje typu encji dostawca  Typy atrybutów » Typ atrybutu reprezentuje właściwość typu encji – np. nazwa i adres są typami atrybutu typu encji dostawca » Atrybut to instancja typu atrybutu ENTITY TYPE ATTRIBUTE TYPE SUPPLIER SUPNR SUPNAME SUPADDRESS SUPCITY SUPSTATUS 21 Deliwines 240, Avenue of the Americas New York 20 32 Best Wines 660, Market Street San Francisco 90 37 Ad Fundum 82, Wacker Drive Chicago 95 ENTITIES 52 Spirits & co. 928, Strip Las Vegas NULL 68 The Wine Depot 132, Montgomery Street San Francisco 10 69 Vinos del Mundo 4, Collins Avenue Miami 92 … ATTRIBUTES Typy atrybutów  Typy atrybutów » Dziedziny » Atrybuty klucza » Proste a złożone atrybuty » Jednowartościowe a wielowartościowe atrybuty » Atrybuty pochodne  Dziedziny » Dziedzina określa zbiór wartości, które mogą być przypisane do atrybutu każdej pojedynczej encji – np. województwo: małopolska, podkarpacie,… » Dziedzina może również zawierać wartości null – null: 1)wartość nieznana 2) wartość istnieje, ale nie jest dostępna 3) atrybut nie dotyczy tej krotki » Dziedziny nie są przestawiane w modelu ER  Atrybut klucza » Atrybut, którego wartości są różne dla każdej pojedynczej encji – np.: identyfikator dostawcy, numer produktu, numer ubezpieczenia » Atrybut klucza może być kombinacją atrybutów – np.: kombinacja numeru lotu i daty wylotu  Atrybuty proste a złożone » Proste (atomowe) atrybuty nie mogą być dalej dzielone na części – np. numer dostawcy, status dostawcy » Złożone atrybuty można rozłożyć na inne znaczące atrybuty – np. adres, nazwa  Jednowartościowe a wielowartościowe atrybuty » Jednowartościowy atrybut ma tylko jedną wartość dla określonej encji – np. numer produktu, nazwa produktu » Wielowartościowy - wiele wartości – np. adresy e-mailowe  Pochodny atrybut » Można wyprowadzić z innego typu atrybutu – np. wiek first name last name SUPNR name email Street Number ZIP address SUPPLIER City Country status date of birth age  Typy związków » Definicja » Stopień i role » Ograniczenia typu związku » Atrybuty związków  Definicja » Związek reprezentuje zależność między dwiema lub większą liczbą encji » Typ związku definiuje zbiór związków między instancjami jednej, dwóch lub większej liczby typów encji SUPNR supcity supstatus supname SUPPLIER supaddress sup- prod prod- SUPPLIES sup PRODNR prodtype PRODUCT prodname available_quantity  Stopień i role » Stopień typu związków odpowiada liczbie typów encji uczestniczących w typie związków – Unarny: stopień 1, binarny: stopień 2, ternarny: stopień 3 » Role typu związku wskazują różne kierunki, które można wykorzystać do jego interpretacji supcity SUPNR supstatus supname SUPPLIER supaddress sup- prod prod- SUPPLIES sup PRODNR prodtype PRODUCT prodname available_quantity  Stopień i role SUPERVISES Super Super- vises vised by SSN EMPLOYEE address ename BOOKING Tourist Hotel TNR HNR Travel Agency ANR  Współczynnik liczności (cardinality) » Każdy typ związku można scharakteryzować pod względem współczynnika liczności, który określa minimalną lub maksymalną liczbę wystąpień związku, w których może uczestniczyć pojedyncza encja » Minimalny współczynnik liczności może być 0 lub 1 – jeżeli 0: częściowy udział – jeżeli 1: całkowity udział lub zależność istnienia » Maksymalny współczynnik liczności: 1 lub N » Typy związków zazwyczaj charakteryzuje się maksymalnym współczynnikiem liczności – 4 opcje dla binarnych typów związków: 1:1, 1:N, N:1 i M:N.  Współczynnik liczności ENROLLED FOR STUDENT 0..N 1..M COURSE N:M ASSIGNED TO STUDENT 0..1 0..1 MASTER THESIS 1:1 MANAGED BY EMPLOYEE PROJECT 1:N 1..1 0..N  Atrybuty typów związków » Typy związków mogą również mieć atrybuty » Te atrybuty można przypisać do jednego z uczestniczących typów encji w przypadku typu relacji 1:1 lub 1:N hours PNR SSN 0..M pro- emp- 0..N ename emp pro PROJECT EMPLOYEE address WORKS ON pname pduration  Słabe typy encji » Silny typ encji posiada atrybut klucza » Słaby typ encji nie ma własnego atrybuta klucza – powiązane z typem encji właściciela, od którego pożycza atrybut, aby utworzyć atrybutu klucza HNR RNR 0..N 1..1 Hotel HNR Room Beds BELONGS TO Hname  Słaby typy encji » Słaby typ encji jest zawsze zależny od typu encji właściciela (nie odwrotnie) supcity supstatus SUPNR supname SUPPLIER supaddress 1..1 po-sup ON_ORDER sup-po 0..N PURCHASE ORDER PONR podate  Trójskładnikowe (ternarne) typy związków » Dostawcy mogą dostarczać produkty do projektów. Dostawca może dostarczyć konkretny produkt do wielu projektów. Produkt do konkretnego projektu może być dostarczany przez wielu dostawców. W ramach projektu określony dostawca może dostarczać wiele produktów. Model musi również zawierać ilość i termin dostarczenia określonego produktu do konkretnego projektu przez konkretnego dostawcę. Quantity Due date SUPPLY 0..N 0..N SUPPLIER PROJECT SUPNR 0..N PNR PRODUCT PRODNR  Ternarne typy związków SUPNR Utrata semantyki PNR SUPPLIES 0..N 0..N SUPPLIER PROJECT 0..N 0..N 0..N 0..N PRODUCT CAN SUPPLY USES PRODNR  Ternarne typy związków » Załóżmy, że mamy dwa projekty: projekt 1 używa ołówka i pióra, a projekt 2 używa pióra. Dostawca Peters dostarcza ołówek do projektu 1 i pióro do projektu 2, podczas gdy dostawca Johnson dostarcza pióro do projektu 1. » Z binarnych typów związków nie jest jasne, kto dostarcza pióro do projektu 1 Trójskładnikowe typy związków Trójskładnikowe typy związków Model ER - przykład Model ER - przykład  Ograniczenia modelu ER » Model ER przedstawia tymczasową migawkę i nie jest w stanie modelować ograniczeń czasowych – np.: projekt musi być przypisany do działu po miesiącu, pracownik nie może wrócić do działu, którego wcześniej był kierownikiem, po dwóch tygodniach musi zostać przydzielone zamówienie do dostawcy itp. » Model ER nie może zagwarantować spójności w wielu typach związków – np.: pracownik powinien pracować w dziale, którym zarządza, pracownicy powinni pracować nad projektami przypisanymi do działów, do których należą, dostawcy mogą być przydzielani tylko do zamówień zakupu produktów, które mogą dostarczyć  Ograniczenia modelu ER c.d. » Dziedziny nie są uwzględnione w modelu ER – np.: godziny powinny być dodatnie; prodtype musi być czerwony, biały lub niebieski, supstatus jest liczbą całkowitą od 0 do 100 » Funkcje nie są uwzględnione w modelu ER – np.: obliczyć średnią liczbę projektów, nad którymi pracuje pracownik; określić, który dostawca pobiera maksymalną cenę za produkt  Rozszerzony model EER (Enhanced Entity Relationship) » Specjalizacja/Generalizacja » Kategoryzacja » Agregacja » Przykłady modelu EER » Projektowanie modelu EER  Specjalizacja/Generalizacja » Specjalizacja odnosi się do procesu definiowania zbioru podklas typu encji – Przykład: superklasa ARTIST z podklasami SINGER i ACTOR » Proces specjalizacji definiuje relację „IS A” » Specjalizacja może następnie ustalić dodatkowe specyficzne typy atrybutów dla każdej podklasy – Przykład: piosenkarz może mieć typ atrybutu stylu muzycznego » Specjalizacja może również ustanowić dodatkowe specyficzne typy relacji dla każdej podklasy – Przykłady: aktor może występować w filmach, piosenkarz może być częścią zespołu » Podklasa dziedziczy wszystkie typy atrybutów i typy relacji ze swojej nadklasy  Specjalizacja/Generalizacja » Generalizacja (abstrakcja), to odwrotny proces specjalizacji – Specjalizacja odpowiada procesowi top-down udoskonalania koncepcji (conceptual refinment) – Generalizacja odpowiada procesowi bottom-up syntezy pojęciowej (conceptual synthesis) Specjalizacja/Generalizacja  Specjalizacja/Generalizacja » Ograniczenie rozłączności (disjointness constraint) określa, do jakich podklas może należeć encja nadklasy – Specjalizacja rozłączna to specjalizacja, w której jednostka może być członkiem co najwyżej jednej z podklas – Specjalizacja nakładająca się to specjalizacja, w której ta sama jednostka może należeć do więcej niż jednej podklasy » Ograniczenie kompletności (completeness constraint) wskazuje, czy wszystkie encje nadklasy powinny należeć do jednej z podklas, czy nie – Całkowita specjalizacja to specjalizacja, w której każda encja w superklasie musi być członkiem jakiejś podklasy – Częściowa specjalizacja pozwala encji należeć tylko do nadklasy i do żadnej z podklas  Specjalizacja/Generalizacja ANR ARTIST aname p o SINGER ACTOR music style  Specjalizacja/Generalizacja PNR PERSON pname t d PROFESSOR STUDENT  Specjalizacja/Generalizacja » W hierarchii specjalizacji każda podklasa może mieć tylko jedną nadklasę i dziedziczy typy atrybutów i typy relacji wszystkich poprzedzających ją nadklas aż do korzenia hierarchii  Specjalizacja/Generalizacja » W sieci specjalizacji podklasa może mieć wiele nadklas (wielokrotne dziedziczenie) VEHICLE p o MOTORCYCLE CAR BOAT TRIKE AMPHIBIAN  Kategoryzacja » Kategoria to podklasa, która ma kilka możliwych nadklas » Każda superklasa reprezentuje inny typ encji » Kategoria reprezentuje zbiór encji, który jest podzbiorem unii nadklas  Kategoryzacja » Dziedziczenie w przypadku kategoryzacji odpowiada encji dziedziczącej tylko atrybuty i relacje tej nadklasy, której jest członkiem (dziedziczenie selektywne) » Kategoryzacja może być całkowita lub częściowa – Całkowita: wszystkie encje nadklas należą do podklasy – Częściowa: nie wszystkie encje nadklas należą do podklasy Uwaga: całkowita kategoryzacja może być również reprezentowana jako specjalizacja/generalizacja  Agregacja » Typy encji, które są powiązane przez określony typ związku, można łączyć lub agregować w zagregowany typ encji wyższego poziomu » Agregacja jest szczególnie przydatna, gdy typ zagregowanej encji ma swoje własne typy atrybutów i/lub typy związków CNR PNR 1..M 0..N PROJECT CONSULTANT PARTICIPATION 1..M date 1..1 CONTRACT CONTNR Przykład  Projektowanie modelu EER 1. Zidentyfikuj typy encji 2. Zidentyfikuj typy związków i potwierdź ich stopień 3. Podaj współczynniki liczności i ograniczenia uczestnictwa (uczestnictwo całkowite lub częściowe) 4. Zidentyfikuj typy atrybutów i sprawdź, czy są one proste czy złożone, mają jedną lub wiele wartości, są pochodne czy nie 5. Połącz każdy typ atrybutu z typem encji lub typem związku 6. Wskaż typ(y) atrybutów klucza każdego typu encji 7. Zidentyfikuj słabe typy jednostek i ich klucze częściowe 8. Zastosuj abstrakcje, takie jak generalizacja/specjalizacja, kategoryzacja i agregacja 9. Określ cechy każdej abstrakcji, takie jak rozłączne lub nakładające się, całkowite lub częściowe  Geneza UML » Unified Modeling Language (UML) to język modelowania, który pomaga w specyfikacji, wizualizacji, konstrukcji i dokumentacji artefaktów systemu oprogramowania » Notacja graficzna wywodząca się z metodologii obiektowych » Możliwość modelowania wielu aspektów systemu, w tym jego struktury i zachowania » Zaakceptowany jako standard przez Object Management Group (OMG) w 1997 roku i zatwierdzony jako standard ISO w 2005 roku » UML oferuje różne diagramy, takie jak diagramy przypadków użycia, diagramy sekwencji, diagramy pakietów, diagramy wdrożeń itp. » Notacja "graficzna" wywodząca się z metodologii obiektowych » Diagramy klas mogą być używane jako uniwersalne narzędzie do modelowania danych  Obiektowość » Klasa jest definicją schematu dla zbioru obiektów – Podobne do typu encji w ER » I odwrotnie, obiekt jest instancją klasy – Podobne do encji w ER » Obiekt jest charakteryzowany zarówno poprzez zmienne, jak i metody – Zmienne odpowiadają typom atrybutów, a wartości zmiennych atrybutom w ER – Brak odpowiednika ER dla metod  Obiektowość » Przykładowa klasa Student – Obiekt: Bart, Wilfried, Seppe – Przykładowe zmienne: imię i nazwisko ucznia, płeć i data urodzenia – Przykładowe metody: calcAge, isBirthday, hasPassed(courseID) » Ukrywanie informacji (inaczej enkapsulacja) stwierdza, że do zmiennych obiektu można uzyskać dostęp tylko za pomocą metod pobierających (getter) lub ustawiających (seter) – metoda pobierająca służy do pobierania wartości zmiennej (getter) – metoda ustawiająca przypisuje mu wartość (setter)  Obiektowość » Dziedziczenie – Nadklasa może mieć jedną lub więcej podklas, które dziedziczą zarówno zmienne, jak i metody z nadklasy » Przeciążanie metod – Różne metody w tej samej klasie mogą mieć tę samą nazwę, ale różną liczbę lub typ argumentów wejściowych Przykład UML  Projekt i implementacja bazy danych 1. Model koncepcyjny – diagram ER 2. Model logiczny – model relacyjny (projekt bazy danych) – Mapowanie modelu koncepcyjnego ER na model relacyjny 3. Implementacja - SQL  Narzędzia » Oracle Data Modeler » Visual Paradigm » Vertabelo » SSMS » Draw.io » …  Plan » Model relacyjny » Algebra relacji » Normalizacja » Mapowanie modelu koncepcyjnego ER/EER na model relacyjny  Model relacyjny » Podstawowe pojęcia » Formalne definicje » Rodzaje kluczy » Organiczenia relacyjne (więzy integralności)  Podstawowe pojęcia » Model relacyjny po raz pierwszy sformalizowany przez Edgara F. Codda w 1970 roku – E.F. Codd, A relational model of data for large shared data banks. Communications of the ACM (1970) » Formalny model danych z solidnymi podstawami matematycznymi, oparty o teorię zbiorów i logikę predykatów pierwszego rzędu » Brak reprezentacji graficznej » Powszechnie stosowany do budowy zarówno logicznych, jak i wewnętrznych modeli danych Microsoft SQL Server, IBM DB2, Oracle  Podstawowe pojęcia » Baza danych jest reprezentowana jako zbiór relacji » Relację definiuje się jako zbiór krotek, z których każda reprezentuje podobną encję świata rzeczywistego » Krotka to uporządkowana lista wartości atrybutów, z których każda opisuje aspekt encji Podstawowe pojęcia  Podstawowe pojęcia Model ER Model relacyjny Typ encji Relacja Encja Krotka Typ atrybutu Nazwa kolumny Atrybut Komórka  Postawowe pojęcia Student (Studentnr, Name, HomePhone, Address) Professor (SSN, Name, HomePhone, OfficePhone, E- mail) Course (CourseNo, CourseName)  Formalne definicje » Dziedzina określa zakres dopuszczalnych wartości dla typu atrybutu – np.: dziedzina dla płci, dziedzina dla czasu » Każdy typ atrybutu jest zdefiniowany przy użyciu odpowiedniej dziedziny » Dziedzina może być używana wielokrotnie w relacji  Formalne definicje » Relacja R(A1, A2, A3,… An) może być formalnie zdefiniowana jako zbiór m krotek r = {t1, t2, t3,… tm} gdzie każda krotka t jest uporządkowaną listą n wartości t = odpowiadającą określonej encji – każda wartość vi jest elementem odpowiedniej dziedziny, dom(Ai), lub jest specjalną wartością NULL – Wartość NULL oznacza, że brakuje wartości, jest ona nieistotna lub nie dotyczy  Formalne definicje Student(100, Michael Johnson, 123 456 789, 532 Seventh Avenue) Professor(50, Bart Baesens, NULL, 876 543 210, [email protected]) Course(10, Principles of Database Management) 10  Formalne definicje » Relacja zasadniczo reprezentuje zbiór (bez porządkowania + bez duplikatów) » Ograniczenie dziedziny stanowi, że wartość każdego typu atrybutu A musi być niepodzielną i pojedynczą wartością z dziedziny dom(A) » np.: COURSE(coursenr, coursename, study points) (10, Principles of Database Management, 6) (10, {Principles of Database Management, Database Modeling}, 6)  BŁĄD 11  Formalne definicje » Relacja R stopnia n na dziedzinach dom(A1), dom(A2), dom(A3), … , dom(An) może być również alternatywnie zdefiniowana jako podzbiór iloczynu kartezjańskiego dziedzin, które definiują każdy z typów atrybutów Domain Product ID Domain Product Color Domain Product Category 001 Blue A 002 Red X B X 003 Black C … ProductID Product Color Product Category 001 Blue A 001 Blue B 001 Blue C 001 Red A 001 Red B 001 Red C … 12  Rodzaje kluczy » Nadklucze (superklucze) i klucze » Klucze kandydujące, klucze główne, klucze alternatywne » Klucze obce 13  Nadklucze i klucze » Nadklucz jest zdefiniowany jako podzbiór typów atrybutów relacji R z właściwością, że żadne dwie krotki w jakimkolwiek stanie relacji nie powinny mieć tej samej kombinacji wartości dla tych typów atrybutów » Nadklucz określa ograniczenie unikalności » Nadklucz może mieć nadmiarowe typy atrybutów » np.: (Studentnr, Name, HomePhone) 14  Nadklucze i klucze » Klucz K schematu relacji R jest nadkluczem R z dodatkową właściowością, że usunięcie dowolnego typu atrybutu z K pozostawia zestaw typów atrybutów, które nie są kluczem R » Klucz nie ma żadnych nadmiarowych typów atrybutów (minimalny nadklucz) » np.: Studentnr » Ograniczenie klucza stanowi, że każda relacja musi mieć co najmniej 1 klucz, który pozwala jednoznacznie zidentyfikować jej krotki 15  Klucze kandydujące, klucze główne i klucze alternatywne » Relacja może mieć więcej niż jeden klucz (klucze kandydujące) – PRODUCT: product numer i product name » Klucz główny (primary key) służy do identyfikowania krotek w relacji, ustalania połączeń z innymi relacjami oraz do celów przechowywania – Ograniczenie integralności encji: typy atrybutów, które tworzą klucz główny, powinny zawsze spełniać ograniczenie NOT NULL » Inne klucze kandydujące są wtedy nazywane kluczami alternatywnymi  Klucze obce » Zbiór typów atrybutów FK w relacji R1 jest kluczem obcym R1 jeżeli spełnione są dwa warunki (ograniczenie integralności referencyjnej - referential integrity constraint) – typy atrybutów w FK mają te same dziedziny, co typy atrybutów klucza głównego PK relacji R2 – wartość FK w krotce t1 w bieżącym stanie r1 albo występuje jako wartość PK dla pewnej krotki t2 w bieżącym stanie r2 albo jest NULL  Klucze obce SUPPLIER SUPNR SUPNAME SUPADDRESS SUPCITY SUPSTATUS … SUPPLIER 37 Ad Fundum 82, Wacker Drive Chicago 95 94 The Wine Crate 330, McKinney Avenue Dallas 75 1..1 … po-sup ON_ORDER sup-po 0..N PURCHASE_ORDER PONR PODATE SUPNR PURCHASE 1511 2015-03-24 37 ORDER 1512 2015-04-10 94 …  Klucze obce SUPPLIER SUPNR SUPNAME SUPADDRESS SUPCITY SUPSTATUS 21 Deliwines 240, Avenue of the Americas New York 20 SUPPLIER 32 Best Wines 660, Market Street San Francisco 90 … 0..M po-sup SUPPLIES sup-po PRODUCT 0..N PRODNR PRODNAME PRODTYPE AVAILABLE_QUANTITY Chateau Miraval, Cotes de PRODUCT 0119 rose 126 Provence Rose, 2015 0154 Chateau Haut Brion, 2008 red 111 … red 5  Klucze obce SUPPLIES SUPNR PRODNR PURCHASE_PRICE DELIV_PERIOD … 68 0327 56.99 4 … 21 0289 17.99 1 21 0327 56.00 6 21 0347 16.00 2 … 69 0347 18.00 4 84 0347 18.00 4 …  Ograniczenia relacyjne Ograniczenie dziedziny Wartość każdego typu atrybutu A musi być niepodzielną i pojedynczą wartością z dziedziny dom(A). Ograniczenie klucza Każda relacja ma klucz, który pozwala jednoznacznie zidentyfikować jej krotki. Ograniczenie Typy atrybutów, które tworzą klucz główny, powinny zawsze integralności encji spełniać ograniczenie NOT NULL. Klucz obcy FK ma tę samą dziedzinę, co typ(y) atrybutu PK Ograniczenie klucza głównego, do którego się odwołuje i występuje jako wartość PK lub NULL. integralności referencyjnej  Przykład modelu relacyjnego SUPPLIER(SUPNR, SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS) PRODUCT(PRODNR, PRODNAME, PRODTYPE, AVAILABLE QUANTITY) SUPPLIES(SUPNR, PRODNR, PURCHASE_PRICE, DELIV_PERIOD) PURCHASE_ORDER(PONR, PODATE, SUPNR) PO_LINE(PONR, PRODNR, QUANTITY) Przykład modelu relacyjnego Algebra relacji  Relacyjne języki zapytań » Model relacyjny – zbiór tabel do reprezentacji danych i zależności między nimi – opisuje dane na poziomie logicznym i widoku. » Języki zapytań – w jaki sposób użytkownicy określają żądania pobierania i aktualizowania danych » Proceduralne vs nieprocedurale, lub deklaratywne » “Czyste” języki: – Algebra relacji – Wnioskowane na krotkach (tuple relational calculus) – Wnioskowanie dziedzinowe (domain relational calculus) 2  Algebra relacji Język proceduralny składający się z zestawu operacji, które przyjmują jedną (unarne) lub dwie (binarne) relacje jako dane wejściowe, a ich wynikiem jest nowa relacja. Sześć podstawowych operatorów: 1. selekcja (select): σ 2. projekcja (project): ∏ 3. unia (union): ∪ 4. różnica zbiorów (set difference): – 5. produkt kartezjański (Cartesian product): x 6. przemianowanie (assignment): ρ Dodatkowe operatory: – przecięcie zbiorów (set intersection): ∩ – złączenie naturalne (natural join): – przypisanie (assignment): ← 3  Operacja selekcji » Operacja selekcji wybiera krotki spełniające dany predykat. » Notacja: σ p(r) » p zwane predykatem selekcji » Np : wybierz te krotki relacji instructor gdzie instruktor pracuje na wydziale “Physics”. – Zapytanie σ dept_name=“Physics” (instructor) – Wynik 4  Operacja selekcji c.d. » Dozwolone korzystanie z porównań =, ≠, >, ≥. 90,000(instructor) » Predykat selekcji może zawierać porównania między dwoma atrybutami. – Np znajdź wszystkie wydziały, których nazwa jest taka sama jak nazwa ich budynku: – σ dept_name=building (department) 5  Operacja projekcji/rzutowania » Operator jednoargumentowy, który zwraca relację będącą argumentem pomijając pewne atrybuty. » Notacja: ∏ A ,A ,A ….A (r) 1 2 3 k gdzie A1, A2 – nazwy atrybutów i r - nazwa relacji. » Wynik: relacja z k kolumnami uzyskana w wyniku usunięcia tych niewymienionych » Zduplikowane wiersze usunięte z wynikowej relacji, bo relacje są zbiorami 6  Operacja projekcji c.d. » Np : eliminacja atrybutu dept_name z instructor » Zapytanie: ∏ID, name, salary (instructor) » Wynik: 7  Złożenie operacji relacyjnych » Wynikiem operacji algebry relacji jest relacja i dlatego operacje algebry relacji można łączyć w wyrażenie algebry relacji. » Np. zapytanie – Znajdź nazwiska wszystkich instruktorów na wydziale fizyki. ∏name(σ dept_name =“Physics” (instructor)) » Zamiast podawać nazwę relacji jako argument operacji projekcji, można podać wyrażenie, które zwraca relację. 8  Operacja unii » Operator unii pozwala łączyć dwie relacje » Notacja: r ∪ s » Aby r ∪ s było poprawne. 1. r, s muszą mieć ten sam stopień (tę samą liczbę atrybutów) 2. Dziedziny atrybutów muszą być kompatybilne (np: druga kolumna r ma ten sam typ wartości co druga kolumna s) » Np.: znajdź wszystkie kursy prowadzone w semestrze jesiennym 2017 lub wiosennym 2018 lub w obydwu ∏course_id (σ semester=“Fall” Λ year=2017 (section)) ∪ ∏course_id (σ semester=“Spring” Λ year=2018 (section)) 9  Operacja unii c.d. » Wynik: ∏course_id (σ semester=“Fall” Λ year=2017 (section)) ∪ ∏course_id (σ semester=“Spring” Λ year=2018 (section)) 10  Operacja różnicy zbiorów » Pozwala znaleźć krotki, które występują jednej relacji ale nie w drugiej. » Notacja r – s » Rożnica zbiorów musi zachodzić między kompatybilnymi relacjami. – r i s muszą mieć ten sam stopień – dziedziny atrybutów r i s muszą być kompatybilne » Np : aby znaleźć wszystkie kursy prowadzone w semestrze jesiennym 2017 ale nie w wiosennym 2018 ∏course_id (σ semester=“Fall” Λ year=2017 (section)) − ∏course_id (σ semester=“Spring” Λ year=2018 (section)) 11  Operacja iloczyny kartezjańskiego » Operator iloczynu kartezjańskiego (oznaczony przez X) pozwala łączyć informację z dowolnych dwóch relacji. » Np: Iloczyn kartezjański relacji instructor i teaches: instructor X teaches » Krotka wynikowa tworzona z każdej możliwej pary krotek: jedna z relacji instructor i jedna z relacji teaches » Ponieważ ID instruktora występuje w obydwu relacjach, rozróżnia się te atrybuty dołączając do atrybutu nazwę relacji, z której pierwotnie pochodził atrybut. – instructor.ID – teaches.ID 12  Iloczyn kartezjański instructor teaches 13  Operacja przemianowania » Wyniki wyrażeń algebry relacji nie mają nazwy, do której można by się było odwoływać. Do tego służy operator przemianowania ρ » Wyrażenie: ρx (E) zwraca wynik wyrażenia E pod nazwą x » Jeżeli wyrażenie algebry relacji E ma liczność n, to wtedy : ρx(A1,A2,.. An) (E) zwraca wynik wyrażenia E pod nazwą X z atrybutami przemianowanymi na A1 , A2 , …., An. 14  Operacja przemianowania - przykład » Znajdź najwyższą pensję (1) Oblicz relację tymczasową składającą się z tych wynagrodzeń, które nie są największe: Πinstructor.salary(σinstructor.salary 90.000 (instructor)) » Dwa zapytania nie są identyczne; są jednak równoważne - dają taki sam wynik w dowolnej bazie danych. 32  Równoważne zapytania c.d. » Np.: Znajdź informacje o kursach prowadzonych przez instruktorów na wydziale fizyki » Zapytanie 1 σdept_name=“Physics” (instructor ⋈ instructor.ID = teaches.ID teaches) » Zapytanie 2 (σdept_name=“Physics” (instructor)) ⋈ instructor.ID = teaches.ID teaches » Dwa zapytania nie są identyczne; są jednak równoważne - dają taki sam wynik w dowolnej bazie danych. 33  Operatory rozszerzonej algebry relacji Dostarczają możliwości pisania zapytań, które nie mogą być wyrażone przy pomocy podstawowych operatorów algebry relacji Uogólniona projekcja Funkcje agregujące 34  Uogólniona projekcja Rozszerza operację projekcji zezwalając aby operatory arytmetyczne były użyte w liście projekcji. ∏F ,F ,...,F (E) 1 2 n E jest dowolnym wyrażeniem algebry relacji Każdy F1, F2, …, Fn jest wyrażeniem arytmetycznym ze stałymi i atrybutami ze schematu E. Dana relacja instructor(ID, name, dept_name, salary) gdzie salary jest roczną pensją, pokaż te same dane, ale z pensją miesięczną ∏ID, name, dept_name, salary/12 (instructor) 35  Funkcje i operacje agregujące Relacja r: A B C Funkcja agregująca bierze zbiór wartości i zwraca w wyniku α α 7 pojedynczą wartość: α β 7 avg, min, max, sum, count β β 3 sum(salary) (instructor) β β 10 (Kaligraficzne G) sum(c) (r) sum(c ) 27 36  Operacje agregujące - przykład Znajdź średnią pensję na każdym wydziale dept_name avg(salary) (instructor) avg_salar y 37  Operacje agregacji Operacja agregacji w algebrze relacji G1 ,G2 ,K,Gn F1 ( A1 ), F2 ( A2 ,K, Fn ( An ) (E) E jest dowolnym wyrażeniem algebry relacji – G1, G2 …, Gn lista atrybutów po której ma być grupowanie (może być pusta) – Każde Fi jest funkcją agregującą – Każde Ai jest nazwą atrybutu Uwaga: Czasem używane jest γ zamiast 38  Modyfikacje bazy danych Zawartość bazy danych może być zmieniana przy użyciu następujących operatorów : – Usuwanie o r ← r – E (r- relacja, E – wyrażenie algebry relacji) – Wstawianie o r← r ∪ E – Modyfikacja o r ← ∏ F ,F ,K,F , ( r ) 1 2 l Wszystkie te operacje mogą być wyrażone przy pomocy operatora przypisania 39  Algebra relacji wielozbiorów „Czysta” algebra relacji usuwa wszystkie duplikaty – np. po projekcji Algebra relacji wielozbiorów pozostawia duplikaty aby dopasować się do semantyki SQL – zostawianie duplikatów w SQL było początkowo zrobione w celach efektywnościowych, ale teraz jest jego cechą Algebra wielozbiorów definiowana następująco – selekcja: ma tyle duplikatów krotki co na wejściu, jeżeli krotka spełnia warunek selekcji – projekcja: jedna krotka na krotkę wejściową, nawet jeżeli jest duplikatem – iloczyn kartezjański: jeżeli jest m kopii t1 w r i n kopii t2 w s, to jest m x n kopii t1.t2 w r x s – pozostałe operacje podobnie definiowane o np. unia: m + n kopii, przecięcie: min(m, n) kopii różnica: max(0, m – n) kopii 40  SQL i algebra relacji select A1, A2,.. An from r1, r2, …, rm where P jest równoważne następującemu wyrażeniu w algebrze relacji wielozbiorów ∏ A1,.., An (σ P (r1 x r2 x.. x rm)) select A1, A2, sum(A3) from r1, r2, …, rm where P group by A1, A2 jest równoważne następującemu wyrażeniu w algebrze relacji wielozbiorów A1, A2 sum(A3) (∏ A1,.., An (σ P (r1 x r2 x.. x rm))) 41  Podstawowe kroki w przetwarzaniu zapytań 1. Parsowanie i translacja 2. Optymalizacja 3. Ewaluacja 42  Zapytanie i plan jego wykonania select salary from instructor where salary < 75000 może być przetłumaczone na jedno z wyrażeń algebry relacji: σsalaryY w relacji R jest zależnością przechodnią, jeśli istnieje zbiór atrybutów Z, który nie jest ani kluczem kandydującym, ani podzbiorem żadnego klucza R, i zachodzą zarówno X->Z, jak i Z->Y BCNF Trywialna zależność funkcyjna Zależność funkcjonalną X->Y nazywamy trywialną, jeśli Y jest podzbiorem X 4NF Wielowartościowa zależność Zależność X->>Y jest wielowartościowa wtedy i tylko wtedy, gdy każda wartość X dokładnie określa zbiór wartości Y, niezależnie od innych atrybutów 37  Mapowanie modelu koncepcyjnego ER w relacyjny » Mapowanie typów encji » Mapowanie typów związków » Mapowanie wielowartościowych typów atrybutów » Mapowanie słabych typów encji  Mapowanie typów encji EMPLOYEE(SSN, address, first name, last name) PROJECT(PNR, pname, pduration) SSN PNR address EMPLOYEE PROJECT ename pname first Last pduration name name  Mapowanie typów związków » Mapowanie binarnych typów związków 1:1 » Mapowanie binarnych typów związków 1:N » Mapowanie binarnych typów związków M:N » Mapowanie unarnych typów związków » Mapowanie n-arnych typów związków  Mapowanie binarnych typów związków 1:1 » Tworzymy dwie relacje: po jednej dla każdego typu encji uczestniczącej w typie związków » Połączenie można zrobić, umieszczając klucz obcy w jednej z relacji do klucza głównego drugiej » W przypadku istnienia zależności należy umieścić klucz obcy w istniejącej relacji zależnej i zadeklarować go jako NOT NULL » Typy atrybutów związku typu 1:1 można następnie dodać do relacji z kluczem obcym Mapowanie binarnych typów związków 1:1 SSN ename EMPLOYEE address 1..1 dep-