1
Instytut Automatyki Przemysłowej
Zakład Automatyki
1. Kurs/ nazwa projektu: Relacyjne bazy danych.
2. Cel projektu:
Zdobycie umiejętności projektowania prostych systemów baz danych realizowanych w oparciu o model
relacyjny poprzez opracowanie aplikacji z relacyjną bazą danych wykorzystującą właściwości języka SQL.
3. Wprowadzenie do tematyki projektu
Podstawy języka SQL (Structured Query Language)
• służy do manipulowania danymi umieszczonymi w relacyjnych bazach danych. Jest językiem uniwersalnym,
dzięki czemu praca na różnych systemach baz danych sprowadza się do wydawania tych samych lub
podobnych komend tzw. zapytań SQL. Język SQL został zaimplementowany w większości relacyjnych
systemów baz danych takich jak: DB2, Oracle, InterBase, MySQL, MS SQL
Relacyjny system baz danych przechowuje wszystkie dane w tabelach. Każda tabela zawiera dane na konkretny
temat, np. dane o klientach, pracownikach, towarach itp.
System bazy danych zarządza tymi informacjami, pozwala m.in. na szybsze ich wyszukiwanie i zorganizowanie.
Za każdym razem, kiedy potrzebujesz informacji z bazy danych, musisz "zapytać" system bazy danych w
zrozumiałym dla niego języku. Tym językiem jest SQL.
Składnię języka SQL można podzielić na
trzy części:
• język definiowania struktur danych
- DDL (Data Definition Language)
- jest wykorzystywany do wszelkiego
rodzaju operacji na tabelach, takich
jak: tworzenie, modyfikacja oraz
usuwanie,
•
język do wybierania i
manipulowania danymi
- DML (Data Manipulation
Language)
- służy do manipulowania danymi
umieszczonymi w tabelach, pozwala
na wstawienie danych, ich prezentację,
modyfikowanie oraz usuwanie,
•
język do zapewnienia
bezpieczeństwa dostępu do danych
- DCL (Data Control Language)
- jest używany głównie przez
administratorów systemu baz danych
do nadawania odpowiednich
uprawnień do korzystania z zasobów
bazy danych.
Aby połączyć się z serwerem baz danych:
• oprogramowanie dostarczane łącznie z pakietem MySQL
• panel administracyjny do baz danych – phpMyAdmin
• częstszym sposobem korzystania z bazy danych jest połączenie
wywoływane z wnętrza skryptu - język skryptowy
(umieszczanego na serwerach WWW), który posiada wbudowaną
obsługą baz danych, np. PHP
• dedykowany program tzw. klient, np. MySQL-Front
Połączenie z bazą MySQL można uzyskać z poziomu skryptów PHP i Perl
oraz kompilowanych CGI. W każdym przypadku w celu połączenia się z
serwerem baz danych należy podać jego nazwę (adres domenowy lub
adres IP), nazwę użytkownika oraz hasło.
Funkcja języka PHP, nawiązująca połączenie z serwerem MySQL, wygląda
następująco:
$db = mysql_connect ("adres", "użytkownik", "hasło");
Po prawidłowym podłączeniu do serwera MySQL należy wybrać bazę, na
której będziesz pracować:
mysql_select_db ("baza");
Po poprawnym połączeniu się z bazą danych możesz przystąpić do
wydawania poleceń języka SQL. Funkcję PHP wysyłającą zapytanie SQL
do serwera wywołuje się następująco:
mysql_query ("zapytanie_SQL");
Po zakończonej pracy z bazą danych należy użyć funkcji:
mysql_close ($db);
2
PROJEKTOWANIE RELACYJNYCH BAZ DANYCH
Celem procesu projektowania bazy danych jest stworzenie poprawnego i spełniającego wymagania użytkowników
schematu bazy danych. Ponieważ cały proces jest dosyć skomplikowany, w przypadku rozbudowanych baz danych dzieli
się go na kilka etapów:
1. Przygotowanie diagramu związków E/R
2. Normalizacja projektu
3. Implementacja zasad wymuszających integralność danych.
W pierwszej kolejności należy określić schemat (strukturę) bazy danych i dopiero wtedy, dysponując gotowym
schematem, wybrać te informacje, które będą przechowywane w bazie danych. Tworząc schemat, należy kierować się ogólną
regułą, na podstawie której:
o
W otaczającym nas świecie można wyróżnić mniej lub bardziej trwałe, ale będące logicznymi całościami obiekty
różnych typów.
o
Obiekty poszczególnych typów mogą zostać określone za pomocą właściwych , im cech (atrybutów, metod i
zdarzeń).
Wybór typów obiektów oraz określenie, które informacje powinny być przechowywane w bazie, jest podstawą diagramu
związku E/R (Encja/Relacja).
Model relacyjnych baz danych
Termin relacyjna baza danych oznacza bazę zbudowaną z relacji. Podstawowy obiekt takiej bazy danych, tabela, jest
konkretną reprezentacją relacji — technicznego pojęcia matematyki.
Relacyjny model baz danych stworzony został przez E.F. Codda w 1970 roku i przedstawiony w pracy „Relacyjny model
danych dla dużych banków danych". Nie używa się tam pojęć tabela, kolumna i wiersz, lecz relacja, atrybut i krotka.
Każda tabela składa się z pewnej liczby wierszy i kolumn. Na przecięciu wiersza z kolumną znajduje się pole. W modelu relacyjnym
przyjmuje się, że:
• kolejność wierszy i kolumn w tabelach jest nieistotna,
• wiersze zawierające takie same dane są identyczne.
Natomiast w tabeli przedstawiającej konkretny przypadek relacji identyczne dane (wartości pól) będą przechowywane w różnych
wierszach. Pole zawiera najmniejszą niepodzielną wartość, czyli taką część informacji, która nie może być dalej dzielona ze względu
na spójność logiczną. („pierwsza postać anormalna).
Podstawowe zasady implementacji modelu relacyjnych baz danych można podzielić na trzy grupy:
1. Zasady dotyczące struktury danych.
2. Zasady dotyczące przetwarzania danych
3. Zasady dotyczące integralności danych.
Zasady dotyczące struktury danych
W modelu relacyjnych baz danych informacja o poszczególnych obiektach zapisana jest w tabelach. Jest to model
abstrakcyjny, zawsze obsługiwany w ten sam sposób i niezwią-zany ze sposobem przechowywania danych. W ten sposób
możliwe jest oddzielenie danych od aplikacji klienckiej (interfejsu użytkownika) i platformy sprzętowej.
Serwer bazodanowy poprzez system przechowujący dane (ang. Storage engine) zarządza rozmieszczeniem danych w plikach
znajdujących się na dysku bądź w pamięci podręcznej oraz metodami dostępu do tych danych.
Cechą charakterystyczną modelu jest wymóg przechowywania w bazie danych tylko konkretnych wartości. W
relacyjnych bazach danych nie możemy posługiwać się wskaźnikami do danych.
Twórca relacyjnego modelu baz danych, E.F. Codd, przedstawił zbiór 12 postulatów, które powinny zostać
uwzględnione przez projektantów systemów zarządzania relacyjnymi bazami danych. Zamieszczone niżej postulaty
dotyczą struktury danych, a ich znajomość może okazać się przydatna podczas projektowania baz danych:
• Postulat informacyjny. Na poziomie logicznym dane reprezentowane są wyłącznie za pomocą tabel wartości.
• Postulat dostępu. Do każdej pojedynczej danej jest dostęp za pomocą nazwy tabel, kolumn i wartości kluczy głównych.
• Postulat fizycznej niezależności danych. Zmiany w sposobie przechowywania danych i dostępu do nich nie wpływają na
aplikację kliencką.
• Postulat logicznej niezależność danych. Zmiany w tabelach, zachowujące informację i dopuszczalne semantycznie, nie mają
wpływu na aplikację kliencką.
• Postulat niezależności dystrybucyjnej. System i jego język umożliwiają dostęp do danych zapisanych w różnych miejscach,
np. na wielu komputerach w sieci.
3
• Postulat zabezpieczania przed operacjami na niższym poziomie abstrakcji.
o
Jeśli system zarządzania bazą danych umożliwia bezpośrednie operacje na niższych poziomach abstrakcji, nie mogą
one naruszać reguł relacyjnego modelu baz danych, w szczególności nie mogą pomijać ograniczeń określonych
przez więzy spójności.
Zasady dotyczące przetwarzania danych
Dane przechowywane w bazie danych nie są niezmienne. Wręcz przeciwnie aby baza danych była przydatna,
przechowywane w niej informacje muszą odpowiadać stanowi faktycznemu, a więc muszą być cały czas aktualizowane.
Operacje modyfikowania danych nie mogą jednak naruszać struktury danych. Wynika z tego, że przekształcenie
danych dokonywane bądź to w celu ich modyfikacji, bądź pobrania danych, musi przebiegać z zachowaniem
wewnętrznej logiki (struktury) powiązań istniejących pomiędzy danymi.
Zasad związanych z przetwarzaniem danych bezpośrednio dotyczą trzy kolejne postulaty Codda:
Postulat pełnego języka danych. W systemie zaimplementowany jest pełny język, który obejmuje
• definiowanie danych.
• definiowanie perspektyw (widoków lub kwerend, które nie przechowują danych, ale pobierają porządkują dane
zapisane w tabelach, dzięki czemu pełnią funkcje „okularów", przez które możemy „spoglądać" na dane).
• definiowanie więzów spójności (ograniczeń uniemożliwiających wprowadzenie niepoprawnych danych).
• modyfikowanie danych.
• nadawanie uprawnień użytkownikom.
• implementację transakcyjnego modelu przetwarzania danych.
Postulat modyfikowania bazy danych przez perspektywy. System umożliwia modyfikowanie danych poprzez perspektywy, o ile
modyfikacja taka jest semantycznie sensowna.
Postulat modyfikowania danych na wysokim poziomie abstrakcji. System umożliwia modyfikowanie danych za pomocą operacji,
których argumentami są tabele i perspektywy.
Zasady dotyczące integralności danych
Zasady dotyczące integralności danych zapewniają zachowanie logicznej spójności przechowywanych w bazie danych
informacji. Podstawową zasadą tego typu jest wymóg jednoznacznego identyfikowania wszystkich atrybutów dowolnego
obiektu na podstawie jednego (lub kilku) jego atrybutów (klucz główny).
W teorii relacyjnych baz danych występuje pojęcie zależności funkcyjnej, za pomocą którego opisuje się wspomnianą zasadę.
Atrybuty zależą funkcyjnie od pewnej grupy atrybutów, jeżeli każdemu zespołowi wartości z pierwszej grupy
przyporządkowany jest jeden i tylko jeden zespół wartości z drugiej grupy. Zależność funkcyjną zapisuje się za pomocą znaku
strzałki, która wskazuje, że jeden zespół wartości zależy funkcyjnie od drugiego.
Dodatkowym mechanizmem wymuszającym integralność danych przechowywanych w bazie są zawężenia (ang. Constrainłs).
Jeżeli do definicji określonej kolumny tabeli dodamy zawężenie, to ilekroć będą modyfikowane lub dodawane nowe wartości tego
atrybutu, serwer bazodanowy sprawdzi, czy wprowadzane lub modyfikowane wartości są zgodne z określonym zawężeniem. W
przypadku, gdy nowe wartości nie będą zgodne z zadanym zawężeniem, serwer zwróci komunikat o błędzie i zatrzyma
wykonywanie takiego polecenia.
Wyróżniamy 5 rodzajów zawężeń:
-
NOT NULL
-
UNIQUE
-
DEFAULT
-
PRIMARY KEY (klucz główny)
-
REFERENCES (klucz zewnętrzny, czyli powiązanie relacji z inną relacją)
Zawężenie REFERENCES (klucz zewnętrzny)
Poprzez zawężenie REFERENCES możemy zdefiniować klucz zewnętrzny, czyli powiązanie relacji z inną relacją. W wyniku nadania
tego zawężenia na dany atrybut ograniczymy listę dopuszczalnych dla niego wartości do wartości przechowywanych w
odpowiadającej jej kolumnie w powiązanej relacji.
Aby SQL Server mógł utworzyć klucz zewnętrzny, musi wcześniej zostać utworzona tabela, do której klucz będzie się odwoływał.
Dodatkowo w tabeli tej musi być utworzona odpowiednia kolumna (lub kolumny), dla której zdefiniowano taki sam typ danych
jak dla kolumny z nałożonym warunkiem REFERENCES. Na kolumnę taką musi być także nałożone jedno z dwóch zawężeń:
PRIMARY KEY lub UNIQUE.
Jeżeli pewnej kolumnie nałożymy zawężenie REFERENCES, powinniśmy pamiętać, że:
• W tabeli z kluczem zewnętrznym nie można wstawić wiersza o wartościach klucza zewnętrznego, które nie mają odpowiedników
4
w powiązanej tabeli.
• W tabeli z kluczem zewnętrznym nie można bezpośrednio zmodyfikować wiersza klucza zewnętrznego, nadając mu wartości,
które nie mają odpowiedników w powiązanej tabeli, o ile podczas tworzenia zawężenia nie została podana dyrektywa {ON
UPDATE / CASCADE SET NULL}.
• Z powiązanej tabeli nie można bezpośrednio usunąć wiersza, do którego odwołują się wartości klucza zewnętrznego innej tabeli.
Można zażądać usuwania wraz z wierszem wszystkich wierszy w tabeli z kluczem zewnętrznym, do których ten wiersz się
odwołuje. W tym celu przy klauzuli definiującej klucz zewnętrzny należy umieścić dyrektywę ON DELETE CASCADE. Aby
podczas usuwania wiersza w powiązanych tabelach zamienić wartość klucza zewnętrznego na wartość nieokreśloną, należy
posłużyć się dyrektywą ON DELETE SET NULL.
Ostatnie 3 z 12 postulatów Codda związane są z.zasadami dotyczącymi integralności danych. Są to:
• Postulat wartości NULL. W systemie jest dostępna specjalna wartość reprezentująca wartość nieokreśloną,
brakującą lub nieznaną. Jest to wartość różna od wszelkich konkretnych wartości, w szczególności od ciągu
pustego ("") i zera(0).
• Postulat słownika danych. Informacje o obiektach bazy danych tworzących jej schemat (metainformacje) są
zgrupowane w tabelach i dostępne w taki sam sposób jak każde inne dane.
• Niezależność więzów spójności. Więzy spójności są definiowane w języku bazy danych i nie muszą być wyrażane w
aplikacji.
NORMALIZACJA BAZ DANYCH
Proces projektowania bazy danych tak, aby utworzyć zbiór tabel o odpowiedniej strukturze jest nazywany
normalizacją
lub sprowadzaniem do postaci normalnej. Normalizacja polega na kolejnych podziałach tabel na
mniejsze tabele, które są z kolei łączone w zapytaniach. Normalizacja leży na pograniczu teorii i praktyki.
Postacie normalne:
1. Pierwsza postać normalna
1NF
(ang. normal form) – postać najsłabsza; wymaga jedynie, aby dziedziny
atrybutów były elementarne (nierozkładalne, atomowe), czyli np. liczby całkowite, daty, łańcuchy, a nie np. listy liczb
lub zbiory dat. Rekordy w 1NF są stałej długości.
2. Druga postać normalna
2NF
– jeżeli każdy atrybut Y, który nie jest kluczem zależy funkcyjnie od klucza (a nie
od podzbioru atrybutów stanowiących klucz) – po wyznaczeniu wszystkich zależności funkcyjnych.
3. Trzecia postać normalna
3NF
– gdy nie istnieją żadne zależności przechodnie (nietrywialne).
4. Postać normalna Boyce-Codda (najmocniejsza) – zależności funkcyjne muszą mieć następującą
postać: jeżeli A X →i atrybut A nie jest zawarty w X, to X jest kluczem lub zawiera klucz.
5. Czwarta postać normalna 4NF – jeżeli zawsze wtedy kiedy zbiór atrybutów X określa
wartościowo Y, to zachodzi jeden z następujących warunków: Y jest puste lub zawiera się w X,
suma zbiorów X i Y jest pełnym zbiorem atrybutów lub, wreszcie, X zawiera klucz.
6. Piąta postać normalna 5NF – jeżeli nie istnieje rozkład odwracalny na zbiór mniejszych tabel.
Cel normalizacji:
1. uniknięcie redundancji (tj. powtarzania się pól z identycznymi wartościami w różnych tabelach);
2. wyeliminowanie niewygodnych relacji wieloznacznych;
3. uniknięcie anomalii przy aktualizacji: modyfikacji, wstawianiu i usuwaniu;
4. uniknięcie niespójności.
Koszt
:
Mnożenie liczby tabel – wydłużenie czasu dostępu
do danych.
Poszukiwanie kompromisu (dwa
współzawodniczące cele):
Zapobieganie anomaliom oraz zapewnienie
rozsądnego czasu dostępu do danych.
Schemat nieznormalizowany (UNF)
5
4. Opis przebiegu projektu
Celem projektu jest poznanie właściwości języka SQL oraz zagadnień dotyczących podstaw projektowania
i zarządzania relacyjnymi bazami danych SQL, m.in.: budowanie bazy (np. instrukcje create), operacje na rekordach
(np. instrukcje insert, delete, update, truncate, drop, alter), pobieranie danych z bazy danych (np. instrukcja select),
wykonywanie obliczeń (operatory arytmetyczne i funkcje), użycie klauzuli order by, where i between, łączenie tabel,
uprawnienia użytkowników bazy danych.
Zaprojektować relacyjną bazę danych SQL służącą do gromadzenia i przetwarzania informacji. Każda grupa
studentów otrzyma do wykonania jeden temat projektu z wykorzystaniem systemu relacyjnej bazy danych
(przykładowe zadania gromadzenia i przetwarzania informacji o produktach/usługach/ i/lub pracownikach/klientach
w różnych kategoriach: proces technologiczny, podsystem bankowy, rezerwacja biletów, wypożyczalnia itd). Baza
powinna mieć możliwość wykonywania kilku podstawowych analiz statystycznych (np. dochodowości i trendów w
zapotrzebowaniu produktów/ usług i/lub przebiegu wartości wybranych wielkości fizycznych) ilustrujących
wykorzystanie zaawansowanych instrukcji SQL.
Warunkiem zaliczenia projektu jest pozytywne wykonanie przynajmniej dwóch pierwszych etapów (etap I oraz etap
II) w nieprzekraczalnych (określonych przez prowadzącego projekt) terminach:
Etap I oraz II - podstawowe wymagania dotyczące projektu bazy danych :
•
I etap. Prezentacja przyjętego schematu bazy danych (przed kodowaniem w SQL): przynajmniej 4 poprawnie
zaprojektowane tabele wraz z opisem przeprowadzonej normalizacji oraz ze wskazaniem relacji pomiędzy
tabelami (kilka stron A4)
•
II etap. Prezentacja funkcjonalności zaprojektowanej bazy (kod SQL pogrupowany odpowiednio w komendy
DDL, DML oraz DCL) z wykorzystaniem serwera relacyjnej bazy SQL (np. MySQL, MS SQL) oraz (do
wyboru) a) rozkazów linii komend lub narzędzi dostarczanych wraz z bazą (np. phpMyadmin) lub b)
dodatkowego oprogramowania klienta bazy (np. MySQL-Front)
Etap III - dodatkowe (nieobowiązkowe) wymagania dotyczące projektu bazy danych:
•
internetowa baza danych, czyli internetowy interfejs do projektu wykonanego w etapach I i II. na platformie
Windows i/lub Linux: np. Apache / IIS + PHP + MySQL+ HTML. Celem tego etapu jest poznanie sposobu
korzystania z bazy danych poprzez połączenie wywoływane z wnętrza skryptu witryny internetowej: np.
poprzez PHP - język skryptowy (umieszczany na serwerach WWW) z wbudowaną obsługę wybranego
serwera baz danych (np. MySQL)
•
konfiguracja bezpiecznego połączenia SSL z serwerem bazy danych z wykorzystaniem certyfikatów X.509
PKI (Infrastruktura Klucza Publicznego): po dostarczeniu pliku żądania certyfikatu (z serwera WWW) zespół
otrzyma certyfikat serwera X.509 od PKI Academus
5. Wymagania dotyczące sprawozdania
Sprawozdanie z wykonania projektu powinno zawierać:
•
pisemne streszczenie projektu (max. do 5 stron A4) oraz prezentację w formacie HTML /PowerPoint
•
opis przyjętego schematu bazy danych: modelowanie danych, zależności funkcyjne, modelowanie relacji,
ograniczenia dla kolumn, wartości domyślne, utworzone indeksy, więzy integralności oraz klucze główne
•
opis normalizacji bazy danych (problem redundancji i anomalii);
•
cztery pliki ze skryptami SQL tworzące i zarządzające wszystkimi obiektami bazy danych, odpowiednio:
DDL, DML, DCL oraz plik z przynajmniej 5 zapytaniami SQL (pobierających dane) do bazy danych
ilustrujących właściwości języka SQL:
- tworzenia i usuwania baz danych, tabel, kluczy;
- wprowadzania, uaktualniania, usuwania oraz wyszukiwania danych;
- ustawiania praw dostępu do danych oraz administracji bazą danych
6. Literatura
• WellingL, Thomson L: PHP i MySQL. Tworzenie stron WWW. Helion 2005
• M. Szeliga: Transact-SQL, Helion, 2003
• R. Coburn: SQL dla każdego. Helion, 2001
Opracował: dr inż. Mirosław Będzak