1

Relacyjne SystemyRelacyjne Systemy

Baz DanychBaz Danych

© Dr inż. Zbigniew Rudnicki

Bazy danych� jedna z głównych dziedzin zastosowań komputerów

od początku ich istnienia,

np.:

- katalogi książek,

- wykazy produktów,

- rejestry sprzedaży,

- inwentarze magazynowanych materiałów i części,

- kartoteki personalne pracowników,

- wykazy klientów

itp.

2

(c) Z. Rudnicki 3

System Bazy Danych (Bank Danych) to:

1. Baza Danych oraz

2. SZBD (System Zarządzania Bazą Danych)ang.RDBMS - Relation Data Base Management System

1. Baza Danych

= DANE związane z konkretnym tematem,

gromadzone i przetwarzane według określonych

reguł

2. SZBD = System Zarządzania Bazą Danych

= oprogramowanie do obsługi bazy danych

(gromadzenia, aktualizacji, wyszukiwania, ...)

(c) Z. Rudnicki 4

Relacyjna baza danych

• jest zbiorem dwuwymiarowych tabel

powiązanych wzajemnymi relacjami

3

(c) Z. Rudnicki 5

Struktura Bazy Danych• Dane - przechowywane są w TABELACH (relacjach, plikach)

Każda tabela dotyczy obiektów (lub zdarzeń) jednego rodzaju

i składa się z:

– rekordów (wierszy,

krotek) dotyczących

poszczególnych obiektów

pól (kolumn, atrybutów)

- czyli pozycji

w wierszu

odpowiadających

własnościom obiektu

(c) Z. Rudnicki 6

Struktura tabeli danych• Rekord (wiersz tabeli) gromadzi informacje o pojedynczym

obiekcie (na przykład: książce) lub zdarzeniu (np.wypożyczeniu)

• Pola rekordu zawierają informacje o poszczególnych atrybutach

obiektu (np.: Autor, Tytuł, Rok_Wydania)

każde pole posiada takie parametry (właściwości) jak:

– NAZWA

– TYP:

• liczbowy

• tekstowy

• i inne

– DŁUGOŚĆ a dla typu liczbowego także format (liczbę

miejsc przed i po przecinku lub kropce dziesiętnej

– . . . może też mieć inne

4

(c) Z. Rudnicki 7

Narzędzia do baz danych

• Pojedyncze tabele mogą być obsługiwane

bezpośrednio w arkuszu kalkulacyjnym

• Bazy danych złożone z wielu tabel powiązanych

relacjami wymagają wyspecjalizowanych aplikacji

• Lokalne aplikacje bazo-danowe mogą być tworzone

przy pomocy programu Access z pakietu Ms Office

a także (w ograniczonym zakresie) w Excel’a

• Sieciowe bazy danych tworzone są najczęściej przy

pomocy kilku języków np.: HTML+ PHP +SQL

(c) Z. Rudnicki 8

Proste środki do baz danych w Excel’u:

Pojedyncze tabele można nawet tworzyć w arkuszu

kalkulacyjnym. Excel dostarcza m.in.:

• Podpowiedzi przy wpisywaniu tych samych słów

• Sortowanie

• Autofiltr

• Formularz

• Wyszukiwanie

• Funkcje Excela dla baz danych

• Zaawansowane możliwości przy zastosowaniu

języka VBA - Visual Basic for Applications

5

(c) Z. Rudnicki 9

Excel - Autofiltr

(c) Z. Rudnicki 10

Excel - Dane-Formularz

6

(c) Z. Rudnicki 11

Projektowanie aplikacji bazodanowej

- projektowanie tabel i ich normalizacja (rozkład na prostsze tabele),

- tworzenie tabel z uwzględnieniem:

- określania nazw, typów i długości pól,

- wymagań i ograniczeń co do zakresu wartości

- określenia wartości domyślnej,

- wyboru klucza podstawowego,

- wyboru sposobów uporządkowania;

- określenie i utworzenie odpowiednich typów relacji między tabelami;

- opracowanie zapytań czyli tzw. kwerend (do selekcji, projekcji, ...) ;

- opracowanie transakcji (kwerend funkcjonalnych);

- opracowanie formularzy – jako interfejsów do operowania na danych,

- opracowanie postaci różnorodnych wydruków zwanych raportami.

(c) Z. Rudnicki 12

Ms Access- “Widok Projekt” - pozwala tworzyć i modyfikować strukturę obiektów

- “Widok Arkusz danych” – pozwala korzystać z utworzonych obiektów –

wprowadzać, wybierać i modyfikować dane.

7

(c) Z. Rudnicki 13

Normalizacja tabel

W praktyce chodzi m.in o to aby:

- w żadnej z tabel informacje nie powtarzały się,

- każda tabela dotyczyła tylko jednego tematu –

a więc gromadziła informacje o określonej grupie:

obiektów, zdarzeń, cech, stanów, ról i t.d.,

- elementy tabeli były niepodzielne (atomowe).

(c) Z. Rudnicki 14

Jednoznaczna identyfikacja

oraz spójność (integralność) danych

Dla każdej tabeli konieczne jest istnienie klucza

głównego (podstawowego) czyli pola lub zestawu

pól stanowiącego jednoznaczny identyfikator

obiektów (n.p.: PESEL, nr książki w bibliotece)

W Accesie kluczem głównym często może być pole

typu AUTONUMER (takie są pola identyfikatorów

w pokazanych tabelach)

Identyfikator nie może odsyłać do nie istniejących

danych. Gdyby tak było to byłaby to:

utrata spójności (integralności) danych

8

(c) Z. Rudnicki 15

Tabela nie może zawierać dwu (lub więcej) identycznych

rekordów.

Każda informacja powinna być zapisana TYLKO W JEDNYM

MIEJSCU

Dublowanie informacji jest nie tylko nieoszczędne ale wprowadza

ryzyko niezgodności danych.

Tabela taka jak niżej jest więc zastępowana kilkoma prostymi tabelami

powiązanymi RELACJAMI

Unikalność i zgodność danych:

(c) Z. Rudnicki 16

Tabele powiązane relacjamipoprzednia wirtualna (sztuczna) tabela

w rzeczywistości wykorzystuje dane z trzech tabel

9

(c) Z. Rudnicki 17

RelacjeMożliwe są trzy typy relacji:

• Relacja “jeden do wielu” - najczęściej stosowana

Jeden rekord tabeli podrzędnej może być wykorzystywany w

wielu rekordach tabeli nadrzędnej Na przykład: ten sam kolor

może posiadać wiele samochodów w tabeli Auta;

• Relacja “jeden do jednego” – polega na powiązaniu kluczy

podstawowych dwu tabel dotyczących tych samych podmiotów

na przykład tabeli Osoby z tabelą Pracownicy.

• Relacja “wiele do wielu” – w rzeczywistości składa się z dwu

relacji “jeden do wielu” i wymaga istnienia tabeli pośredniczącej

zwanej tabelą łącza, w której klucz podstawowy składa się z

dwóch pól - kluczy obcych z dwu tabel wiązanych tą relacją.

(c) Z. Rudnicki 18

Tworzenie relacji „jeden do wielu” -

przy pomocy kreatora odnośników

• Najpierw tworzymy tabele podrzędne np. „Kolory”

• Następnie tworzymy tabelę nadrzędną np. „Auta”

• Tworząc pole „kolor” w tabeli „Auta” wybieramy jako jego

typ (z listy typów danych) “kreator odnośników”

• wybieramy domyślną opcję: “... kolumna odnośnika ma

pobierać wartości z tabeli lub kwerendy” oraz wybieramy

tabelę „Kolory” a w niej dwa pola: “Id_koloru” oraz “Kolor”

• w następnych oknach pozostawiamy zaznaczoną opcję "ukryj

kolumnę klucz" oraz nazwę Kolor jako etykietę i kończymy

przyciskiem"Utwórz"

10

(c) Z. Rudnicki 19

Przykład relacji „jeden do wielu”a) w OpenOffice.org Base,

b) w Ms Access

(c) Z. Rudnicki 20

Tworzenie relacji w oknie „Relacje”

11

(c) Z. Rudnicki 21

Relacje przykładowej bazy po włączeniu

opcji “Wymuszaj więzy integralności”

(c) Z. Rudnicki 22

Ważniejsze operacje

• Indeksowanie - umożliwia przeglądanie danych według

określonych porządków

• Sortowanie - porządkuje fizycznie rekordy w/g zadanych kryteriów

• Selekcja wybieranie wierszy tabel według zadanych warunków

zwanych zapytaniami lub kwerendami (ang. query)

• Projekcja - wybieranie kolumn (pól) tabeli (też wg kwerend)

• Transakcje (kwerendy funkcjonalne) - dołączanie, usuwanie,

aktualizacja,

• Definiowanie formularzy do wprowadzania danych

• Definiowanie raportów - wydruki (wykazy, podsumowania,

nalepki)

12

(c) Z. Rudnicki 23

Kwerenda - widok „projekt”

(c) Z. Rudnicki 24

Kwerenda - widok „arkusz danych”

13

(c) Z. Rudnicki 25

Kwerenda - widok SQL

Access automatycznie generuje polecenia w języku SQL:

SELECT DISTINCTROW Pracownicy.Id_dz,Osoby.Nazwisko, Osoby.Imie, Osoby.Data_ur

FROM Osoby INNER JOIN Pracownicy ON Osoby.Id_os =

Pracownicy.Id_os

WHERE (((Pracownicy.Id_dz)=1));

(c) Z. Rudnicki 26

Kwerendy obliczające

14

(c) Z. Rudnicki 27

Kwerendy funkcjonalne

- usuwająca - usuwa grupę rekordów z jednej lub kilku

tabel;

- aktualizująca – pozwala zmienić wartości grupy

rekordów tabeli lub kilku tabel, na przykład dokonać

zmian cen produktów czy podwyżek płac;

- dołączająca – pozwala dopisać nowe rekordy,

kopiując je z innych tabel, lub dołączyć dane z pól

wybranych na podstawie określonych kryteriów.

- tworząca nową tabelę z całości lub części danych

znajdujących się w innych tabelach, przykładowo

może to być kopia zapasowa.

(c) Z. Rudnicki 28

Tworzenie formularza na podstawie

kwerendy

15

(c) Z. Rudnicki 29

Sposoby prezentowania informacjiprzy wprowadzaniu, poprawianiu i korzystaniu z danych

• Tabela na ekranie - rzeczywista tabela lub wybór

rekordów (selekcje) i pól (projekcje)

z jednej lub wielu tabel bazy danych

• Formularz - przedstawia w jeden rekord (wiersz)

w postaci pojedynczej karty

• Raport - wydruk dowolnego wykazu (listy)

według podanych kryteriów wyboru pól i

rekordów

(c) Z. Rudnicki 30

Niektóre narzędzia i systemy baz danych

• dBase (proceduralny język programowania + dialogowe centrum) -

już raczej historyczny lecz jego pliki akceptowane przez nowsze

systemy.

- Clipper, Fox Pro, dBase for Windows i in. - oparte na dBase

• SQL - Structured Query Language (nieproceduralny język zapytań)

używany m.in w:

- Ms Access, MySQL, Oracle, Progress, Informix i in.

• Uniwersalne języki programowania:

C++, Pascal, Delphi, Visual Basic i innne

16

(c) Z. Rudnicki 31

SQL

(ang. Structured Query Language)

czyli

„strukturalny język zapytań”

(c) Z. Rudnicki 32

SQL - charakterystyka ogólna

• SQL (ang. Structured Query Language) to „strukturalny język

zapytań” używany do tworzenia, modyfikowania i wykorzystywania

baz danych.

• SQL jest językiem deklaratywnym, nieproceduralnym to znaczy, że

podajemy co chcemy uzyskać a nie w jaki sposób i przy pomocy

jakich operacji

• Opracowany w IBM w latach siedemdziesiątych XX w.

• Jest standardem w komunikacji systemów klienckich z serwerami

relacyjnych baz danych.

• Pierwszą firmą, która włączyła SQL do swojego produktu

komercyjnego, był Oracle.

17

(c) Z. Rudnicki 33

Systemy bazodanowe używające SQL

# Caché

# DATAllegro

# DB2

# Firebird

# First SQL

# HSQL

# Ingres

# Informix

# InterBase SQL

# MaxDB znana jako SAP DB

# Microsoft Access

# Microsoft Jet

# Microsoft SQL Server

# Mimer SQL

# MySQL

# mSQL

# Neteeza

# OpenLink Virtuoso

# Oracle

# Oracle Rdb

# PostgreSQL

# SQL/DS

# SQLite

# Sybase

# Teradata

(c) Z. Rudnicki 34

Standardy SQL

W 1986 roku SQL stał się oficjalnym standardem, wspieranym

przez Międzynarodową Organizację Normalizacyjną (ISO) i

jej członka, Amerykański Narodowy Instytut Normalizacji

(ANSI).

Potrzeba określenia standardu dla aplikacji współpracujących z

wieloma różnymi bazami danych - doprowadziła do

opracowania SQL92, obowiązującego w produktach

komercyjnych do dziś.

W roku 2003 przedstawiono SQL:2003 – nowy standard

języka SQL opublikowany w Sigmod Record Vol.33 Nr.1

Marzec 2004. Jest to głównie poprawione SQL:1999 z

wyjątkiem części SQL/XML oraz kilku dodatkowych

właściwości.

18

(c) Z. Rudnicki 35

Funkcje silnika (back-end)

i interfejsy (front-end)

Serwer określa się często takimi nazwami jak "back-end", "engine", czy też

"motor/silnik bazy danych". Przechowuje on dane oraz zapewnia ich

pobieranie i aktualizacje w odpowiedzi na pobierane instrukcje w SQL-u.

• Uzupełnieniem serwera jest "front end” - interfejs upraszczający komunikację

z serwerem i pozwalający wykorzystać pobrane dane

• Może on mieć: mechanizmy generowania i obsługi formularzy, raportów,

języki czwartej generacji (4GL), graficzne języki zapytań, narzędzia

konstrukcyjne dla użytkownika, oprogramowanie do prezentacji

multimedialnych, systemy tworzenia hipertekstu, CAD/CAM, arkusze

kalkulacyjne, ...

• Wykorzystują one język SQL.

(c) Z. Rudnicki 36

Składnia SQL

Zapytania można zaliczać do trzech głównych podzbiorów:

• SQL DDL (ang. Data Definition Language, czyli Język Definicji

Danych).

• SQL DML (ang. Data Manipulation Language, czyli Język

Manipulacji Danymi),

• SQL DCL (ang. Data Control Language, czyli Język Kontroli nad

Danymi).

Instrukcje SQL tradycyjnie zapisywane są wielkimi literami, (jednak nie

jest to wymóg). Każde zapytanie w SQL-u musi kończyć się znakiem

średnikiem [;].

19

(c) Z. Rudnicki 37

DDL - Data Definition Language

Komendy grupy DDL operują na strukturach danych

- można np. dodawać, zmieniać i kasować tabele lub bazy.

Najważniejsze polecenia tej grupy to:

• CREATE (np. CREATE TABLE, CREATE DATABASE, ...) - utworzenie

struktury (bazy, tabeli, indeksu, itp.),

• DROP (np. DROP TABLE, DROP DATABASE, ...) - całkowite usunięcie

struktury,

• ALTER (np. ALTER TABLE ADD COLUMN ...) - zmiana struktury

(dodanie kolumny do tabeli, zmiana typu danych w kolumnie tabeli).

(c) Z. Rudnicki 38

DCL - Data Control Language

DCL ma zastosowanie do nadawania uprawnień do obiektów

bazodanowych. Najważeniejsze polecenie w tej grupie to:

• GRANT

np.:

GRANT ALL PRIVILEGES ON EMPLOYEE TO PIOTR

WITH GRANT OPTION

- przyznanie wszystkich praw do tabeli EMP

użytkownikowi PIOTR z opcją pozwalającą PIOTR'owi

nadawać prawa do tego obiektu (tabela EMP).

20

(c) Z. Rudnicki 39

DML - Data Manipulation Language

Podzbiór DML służy do operacji na danych - do ich

umieszczania w bazie, kasowania, przeglądania,

zmiany. Najważniejsze polecenia z tego zbioru to:

• SELECT - pobranie z bazy danych,

• INSERT - umieszczenie danych w bazie,

• UPDATE - zmiana danych,

• DELETE - usunięcie danych z bazy.

(c) Z. Rudnicki 40

Tworzenie i usuwanie tabeli:

CREATE i DELETE

CREATE TABLE pracownicy (imie varchar(255), nazwisko

varchar(255), pensja float, staz int);

Tworzy tabelę "pracownicy" zawierającą tekstowe (varchar -

zmiennej długości pole tekstowe) pola "imię" i "nazwisko", o

maksymalnej długości 255 znaków, zapisaną za pomocą liczby

rzeczywistej (float od ang. floating point) pensję oraz zapisany

za pomocą liczby całkowitej (int od ang. integer) staż.

DROP TABLE pracownicy;

Usuwa z bazy całkowicie tabelę "pracownicy".

21

(c) Z. Rudnicki 41

Instrukcja SELECT (wybierz)

Przykładowe zapytanie:

SELECT * FROM pracownicy WHERE pensja >

2000 ORDER BY staz DESC;

Wyświetla z tabeli pracownicy (FROM pracownicy)

wszystkie kolumny (*) dotyczące tych pracowników,

których pensja jest większa niż 2000 (WHERE pensja

> 2000) i sortuje wynik malejąco według stażu pracy

(ORDER BY staz DESC).

(c) Z. Rudnicki 42

Instrukcja INSERT (wstaw)

INSERT INTO pracownicy (imie,

nazwisko, pensja, staz) VALUES ('Jan',

'Kowalski', 5500, 1);

Dodaje do tabeli pracownicy (INTO

pracownicy) wiersz (rekord) zawierający

dane pojedynczego pracownika.

22

(c) Z. Rudnicki 43

Aktualizacja i usuwanie danych:

UPDATE i DELETE

UPDATE pracownicy SET pensja = pensja * 1.1 WHERE

staz > 2;

Podnosi o 10% (SET pensja = pensja * 1.1) pensję pracownikom,

których staż jest większy niż 2 (np. lata).

DELETE FROM pracownicy WHERE imie = 'Jan' AND

nazwisko = 'Kowalski';

Usuwa z tabeli "pracownicy" wiersz (rekord) dotyczący

pracownika o imieniu "Jan" i nazwisku "Kowalski".

(c) Z. Rudnicki 44

Modyfikacja struktury tabeli

ALTER TABLE pracownicy ADD COLUMN

dzial varchar(255);

Dodaje do struktury tabeli "pracownicy" kolumnę

"dzial" (dział), jako pole tekstowe o długości max.

255 znaków.