Bazy danych 1
Opracowano na podstawie:
Thomas Connolly, Carolyn Begg, Systemy baz danych, Wydawnictwo RM, 2004
1 Projektowanie bazy danych
Krok 3
Tworzenie i weryfikacja globalnego logicznego modelu danych [1]
(zgodnie z założeniami istnieje tylko jedna perspektywa - patrz dwa wykłady wcześniej)
Krok 3.1
Scal lokalne, logiczne modele danych w model globalny
Krok 3.2
Sprawdz
poprawność globalnego logicznego modelu danych
Krok 3.3
Sprawdz
możliwość przystosowania modelu do przewidywanych zmian
Krok 3.4
Omów globalny logiczny model danych z użytkownikami
Krok 4
Wyrażenie globalnego logicznego modelu danych w docelowym SZBD
Krok 4.1
Zaprojektuj relacje bazowe
Krok 4.2
Zaprojektuj reprezentację danych pochodnych (Zgodnie z założeniami dane pochodne nie istnieją)
Krok 4.3
Zaprojektuj więzy ogólne
Krok 5
Projektowanie reprezentacji fizycznej
Krok 5.1
Przeanalizuj transakcje
Krok 5.2
Ustal organizację plików
Krok 5.3
Wybierz indeksy
1
Krok 5.4
Oszacuj wymaganą pamięć dyskową
Krok 6
Projektowanie perspektyw użytkownika
Krok 7
Projektowanie mechanizmów bezpieczeństwa
Krok 8
Rozważenie potrzeby wprowadzenia kontrolowanej redundancji
Krok 8.1
A
ączenie relacji występujących w związkach wzajemnie jednoznacznych (1:1)
Krok 8.2
Redukowanie liczby złączeń poprzez powielanie atrybutów, które nie należą do kluczy w związkach typu
 jeden do wielu (1:*)
Krok 8.3
Redukowanie liczby złączeń poprzez powielanie atrybutów kluczy obcych w związkach typu  jeden do
wielu (1:*)
Krok 8.4
Redukowanie liczby złączeń poprzez powielanie atrybutów w związkach  wiele do wielu (*:*)
Krok 8.5
Wprowadzenie powtarzajÄ…cych siÄ™ grup
Krok 8.6
Scalanie tabel podglÄ…du z relacjami bazowymi
Krok 8.7
Tworzenie tabel skrótowych
Krok 9
Monitorowanie i dostrajanie systemu operacyjnego
2 Definicje
Model relacyjny[1] dostarcza tylko jednego sposobu reprezentowania danych: jest nim dwuwymiarowa
tabela, nazywana relacją. W nagłówku relacji są podawane atrybuty. Wiersze relacji, poza wierszem nagłówka,
zawierającym atrybuty relacji, są nazywane krotkami. W krotce każdy atrybut ma swój odpowiednik w postaci
składowej krotki. Nazwa relacji oraz jej zbiór atrybutów nazywają się schematem relacji. Schemat relacji
zapisujemy za pomocą jej nazwy, po której występuje lista atrybutów ujęta w nawiasy okrągłe, np.
Student(NrStudenta, Nazwisko, ImiÄ™, NrGrupy).
2
Zbiór schematów relacji projektu[1] jest określany schematem relacyjnym bazy danych lub po prostu
schematem bazy danych. Zakłada się, że z każdym atrybutem relacji jest powiązana dziedzina, czyli pewien
określony typ elementarny. Każda składowa każdej krotki w relacji ma wartość, która należy do dziedziny
odpowiedniej kolumny. W modelu relacyjnym każda składowa każdej relacji musi mieć określony typ atomowy,
tzn. jej typ musi należeć do typów elementarnych, np. musi być całkowity lub znakowy. Wartość atrybutu nie
może być ani rekordem, ani listą, ani tablicą, ani zbiorem, ani żadną inną strukturą, którą można podzielić
na mniejsze części. (!!!)
Odpowiedniki terminologiczne dla pojęć modelu relacyjnego [3]:
Nazwa formalna Pierwszy odpowiednik Drugi odpowiednik
Relacja Tablica Plik
Krotka Wiersz Rekord
Atrybut Kolumna Pole
3 Typy Oracle
Wybrane typy danych Oracle Opis
Zmiennej długości ciąg znaków o maksymalnej długości
VARCHAR2(size [BYTE  CHAR]) size bajtów lub znaków. Długość musi zawierać się pomię-
dzy 1 a 4000.
Zmiennej długości ciąg znaków Unicode o maksymalnej
NVARCHAR2(size)
długości size znaków (do 4000 bajtów).
Liczba o precyzji precision z zakresu od 1 do 38 i skali scale
NUMBER[(precision [, scale])]
z zakresu od -84 do 127.
Podtyp NUMBER o precyzji precision z zakresu od 1 do
FLOAT [(precision)]
126 binarnych cyfr.
LONG Typ znakowy zmiennej długości do 2GB lub231 -1
DATE Data
BINARY FLOAT 32 bitowa liczba zmiennoprzecinkowa
BINARY DOUBLE 64 bitowa liczba zmiennoprzecinkowa
TIMESTAMP [(fractional seconds)] Znacznik czasu
TIMESTAMP [(fractional seconds)]
Znacznik czasu ze strefÄ… czasowÄ…
WITH TIME ZONE
Znacznik czasu ze strefą czasową z poniższymi wyjątkami:
gdy jest przechowywany w bazie danych data jest znor-
TIMESTAMP [(fractional seconds)]
malizowana do strefy czasowej bazy danych lub gdy jest
WITH LOCAL TIME ZONE
pobierany z bazy danych data przedstawiana jest w strefie
czasowej danej sesji.
INTERVAL YEAR [(year precision)] TO Przechowuje okres czasu wyrażony w latach oraz miesią-
MONTH cach.
INTERVAL DAY [(day precision)] TO SE- Przechowuje okres czasu wyrażony w dniach, godzinach,
COND [(fractional seconds)] minutach i sekundach.
Dane binarne długości size. Maksymalna długość danych
RAW(size)
2000 bajtów.
LONG RAW Dane binarne o długości do 2GB.
ROWID Tekst reprezentujÄ…cy unikalny adres wiersza w tabeli.
Tekst reprezentujÄ…cy logiczny adres wiersza w tabeli zorga-
UROWID [(size)]
nizowanej przy pomocy indeksu.
Stałej długości ciąg znaków o maksymalnej długości si-
CHAR [(size [BYTE  CHAR])] ze bajtów lub znaków. Długość musi zawierać się pomię-
dzy 1 a 2000.
Stałej długości ciąg znaków o maksymalnej długości size
NCHAR[(size)]
bajtów.
3
Duży obiekt znakowy zawierający jednobajtowe lub wielo-
CLOB
bajtowe znaki.
Duży obiekt znakowy zawierający znaki zapisane w forma-
NCLOB
cie Unicode.
BLOB Duży obiekt binarny
Zawiera locator do dużego pliku binarnego przechowywa-
BFILE
nego poza bazÄ… danych.
4 Instrukcje SQL
Poniżej przedstawiona została podstawowa składnia wybranych instrukcji języka SQL. Pełna ich specyfi-
kacja dostępna jest w dokumentacji Oracle np. [8, 9].
4.1 Tworzenie tabel (instrukcja CREATE TABLE ...)
CREATE TABLE nazwa_tabeli (
nazwa_kolumny typ_danych [DEFAULT wyrażenie] [ograniczenie_kolumny]
{, nazwa_kolumny typ_danych [DEFAULT wyrażenie] [ograniczenie kolumny]}
[ograniczenia_tabeli]
);
CREATE TABLE grupa(
id NUMBER(6) CONSTRAINT grupa_pk PRIMARY KEY,
nazwa VARCHAR2(20));
CREATE TABLE student(
id NUMBER(6) CONSTRAINT student_pk PRIMARY KEY,
nazwisko VARCHAR2(20),
imie VARCHAR2(20),
srednia NUMBER (3,2),
id_grupa NUMBER(6) CONSTRAINT student_fk REFERENCES grupa(id));
Przykład 1: Przykład tworzenia tabel 1 {sql/test create1.sql}
CREATE TABLE grupa(
id NUMBER(6),
nazwa VARCHAR2(20),
CONSTRAINT grupa_pk PRIMARY KEY(id));
CREATE TABLE student(
id NUMBER(6) CONSTRAINT student_pk PRIMARY KEY,
nazwisko VARCHAR2(20),
imie VARCHAR2(20),
srednia NUMBER (3,2),
id_grupa NUMBER(6),
CONSTRAINT student_fk FOREIGN KEY (id_grupa) REFERENCES grupa(id));
Przykład 2: Przykład tworzenia tabel 2 {sql/test create2.sql}
4.2 Tworzenie tabeli na podstawie innej tabeli
(instrukcja CREATE TABLE ... AS ...)
CREATE TABLE nazwa_tabeli AS
INSTRUKCJA_SELECT;
4
CREATE TABLE prymus AS
SELECT * FROM student WHERE srednia > 4.5;
Przykład 3: Przykład tworzenia tabel 3 {sql/test create3.sql}
4.3 Ograniczenia
Ograniczenia [5]:
a) NOT NULL  uniemożliwia wprowadzenie do kolumny wartości pustej,
CREATE TABLE test_null(
pole_not_null VARCHAR2 (4) NOT NULL,
pole_null VARCHAR2 (4)
);
Przykład 4: Przykład wykorzystania ograniczenia not null {sql/test not null.sql}
b) UNIQUE KEY  wartość w kolumnie (lub grupie kolumn) będzie unikatowa w obrębie tabeli,
CREATE TABLE test_unique(
nazwisko VARCHAR2 (10),
imie VARCHAR2 (10),
UNIQUE (nazwisko, imie)
);
CREATE TABLE test_unique2(
nazwisko VARCHAR2 (10),
imie VARCHAR2 (10),
UNIQUE (nazwisko),
UNIQUE (imie)
);
Przykład 5: Przykład wykorzystania ograniczenia unique {sql/test unique.sql}
c) PRIMARY KEY  klucz główny:
" może być zdefiniowany tylko jeden dla danej tabeli,
" może składać się z jednej lub większej ilości kolumn,
" nie może zawierać wartości null,
" nie może się powtarzać.
Która z poniższych instrukcji jest poprawna?
CREATE TABLE test_primary_key(
nip PRIMARY KEY,
pesel PRIMARY KEY,
numer_indeksu PRIMARY KEY
);
Przykład 6: Przykład wykorzystania ograniczenia klucza głównego 1 {sql/test pk1.sql}
CREATE TABLE test_primary_key2(
nazwisko VARCHAR2 (10) ,
imie VARCHAR2 (10) ,
PRIMARY KEY (nazwisko, imie)
);
Przykład 7: Przykład wykorzystania ograniczenia klucza głównego 2 {sql/test pk2.sql}
5
d) FOREIGN KEY  klucz obcy,
Klucz obcy [7]  Jest to kolumna lub grupa kolumn występujących w definicji tabeli i związanych ograni-
czeniem integralnościowym z kolumnami klucza innej tabeli.
Klucz wskazywany (nadrzędny) [7]  Klucz unikatowy lub główny, na podstawie którego tworzony jest
klucz obcy w innej tabeli.
Tabela zależna (tabel-potomek) [7]  Tabela zawierająca klucz obcy oparty na kluczu innej tabeli.
Tabela wskazywana (tabela-rodzic) [7]  Tabela, na której oparty jest klucz obcy w innej tabeli (klucz
wskazywany tej tabeli wyznacza zbiór dopuszczalnych wartości da klucza obcego).
e) złożone testy integralności, CHECK,
CREATE TABLE czlowiek (
oczy VARCHAR2(10) DEFAULT 'NIEBIESKIE' CONSTRAINT oczy_check
CHECK (oczy IN ('NIEBIESKIE', 'PIWNE', 'ZIELONE')),
wzrost NUMBER(3,2) CONSTRAINT wzrost_check
CHECK (wzrost > 0 AND wzrost < 3),
plec VARCHAR2(1) CONSTRAINT plec_check
CHECK (plec = 'K' OR plec = 'M')
);
Przykład 8: Przykład wykorzystania ograniczenia CHECK {sql/test check.sql}
f) wyzwalacze.
4.4 Usuwanie tabel (Instrukcja DROP TABLE ...)
DROP TABLE nazwa_tabeli [CASCADE CONSTRAINTS];
Czym różnią się wyniki wykonania poniższych pary instrukcji?
--
DROP TABLE student;
DROP TABLE grupa;
--
DROP TABLE grupa;
DROP TABLE student;
--
DROP TABLE grupa cascade constraints;
DROP TABLE student;
4.5 Modyfikowanie tabel (ALTER TABLE)
Wybrane warianty instrukcji ALTER TABLE:
" ALTER TABLE nazwa tabeli ADD (nazwa kolumny nazwa typu [ograniczenia]);
" ALTER TABLE nazwa tabeli DROP (nazwa kolumny);
" ALTER TABLE nazwa tabeli MODIFY (nazwa kolumny nazwa typu);
" ALTER TABLE nazwa tabeli ADD ograniczenie (nazwa kolumny);
ALTER TABLE student
ADD (drugie_imie VARCHAR2(30)
CONSTRAINT student_drugie_imie_nn NOT NULL );
ALTER TABLE student DROP (drugie_imie);
ALTER TABLE student
MODIFY (nazwisko VARCHAR2(30)
6
CONSTRAINT student_nazwisko_nn NOT NULL );
ALTER TABLE student
ADD UNIQUE(nazwisko);
ALTER TABLE grupa ENABLE CONSTRAINT grupa_pk;
ALTER TABLE student DISABLE PRIMARY KEY;
Przykład 9: Przykład modyfikacji tabel {sql/test alter.sql}
Uwaga! Nie zawsze wszystkie modyfikacje są możliwe. Niektóre mogą być wykonywane tylko na pustych
tabelach.
4.6 Wprowadzanie danych (Instrukcja INSERT INTO ... VALUES...)
INSERT INTO nazwa_tabeli [(nazwa_kolumny{, nazwa kolumny})]
VALUES (wartości);
INSERT INTO grupa (id) VALUES (1);
INSERT INTO grupa VALUES (2, '201');
Przykład 10: Przykład wprowadzania danych {sql/test insert.sql}
Jeżeli nazwy kolumn nie zostaną podane zakłada się, że polecenie dotyczy wszystkich kolumn tabeli
w kolejności jaka została użyta w instrukcji CREATE TABLE.
4.7 Modyfikowanie danych (Instrukcja UPDATE ... SET ...)
UPDATE nazwa_tabeli
SET nazwa_kolumny = wartość{, nazwa_kolumny = wartość}
[WHERE warunek];
UPDATE student SET imie = 'Jan' WHERE imie = 'John';
UPDATE student SET imie = 'Jan';
Przykład 11: Przykład modyfikacji danych {sql/test update.sql}
Uwaga! Jeżeli warunek zostanie pominięty zostaną zmodyfikowane wszystkie wiersze w tabeli.
4.8 Usuwanie danych (Instrukcja DELETE FROM ... )
DELETE FROM nazwa_tabeli [WHERE warunek];
DELETE FROM student WHERE WHERE imie = 'Jan';
DELETE FROM student;
Przykład 12: Przykład modyfikacji danych {sql/test delete.sql}
Uwaga! Jeżeli warunek zostanie pominięty zostaną usunięte wszystkie wiersze w tabeli.
4.9 Wyświetlanie danych (Instrukcja SELECT ... FROM .. WHERE ...)
Składnia najprostszej instrukcji SELECT :
SELECT *
FROM nazwa_tabeli;
7
4.10 Test
Jaki wpływ na wyniki skryptu z przykładu 16 ma wykorzystanie jednego ze skryptów z przykładów: 13,
14 lub 15?
-- Definicje tabel 1
CREATE TABLE grupa(
id NUMBER(6) CONSTRAINT grupa_pk PRIMARY KEY,
nazwa VARCHAR2(20));
CREATE TABLE student(
id NUMBER(6) CONSTRAINT student_pk PRIMARY KEY,
nazwisko VARCHAR2(20),
id_grupa NUMBER(6) CONSTRAINT student_fk REFERENCES grupa(id));
Przykład 13: Test 1 {sql/test1.sql}
-- Definicje tabel 2
CREATE TABLE grupa(
id NUMBER(6) CONSTRAINT grupa_pk PRIMARY KEY,
nazwa VARCHAR2(20));
CREATE TABLE student(
id NUMBER(6) CONSTRAINT student_pk PRIMARY KEY,
nazwisko VARCHAR2(20),
id_grupa NUMBER(6) CONSTRAINT student_fk REFERENCES grupa(id)
ON DELETE CASCADE);
Przykład 14: Test 2 {sql/test2.sql}
-- Definicje tabel 3
CREATE TABLE grupa(
id NUMBER(6) CONSTRAINT grupa_pk PRIMARY KEY,
nazwa VARCHAR2(20));
CREATE TABLE student(
id NUMBER(6) CONSTRAINT student_pk PRIMARY KEY,
nazwisko VARCHAR2(20),
id_grupa NUMBER(6) CONSTRAINT student_fk REFERENCES grupa(id)
ON DELETE SET NULL);
Przykład 15: Test 3 {sql/test3.sql}
-- Wprowadzenie danych
INSERT INTO grupa (id, nazwa) VALUES(54, '101a');
INSERT INTO student (id, nazwisko, id_grupa) VALUES(38, 'Kowalski', 54);
INSERT INTO student (id, nazwisko, id_grupa) VALUES(39, 'Malinowski', 45);
-- Usunięcie danych
DELETE FROM grupa
WHERE nazwa = '101a';
-- Usunięcie tabel
DROP TABLE student;
DROP TABLE grupa;
Przykład 16: Test 4 {sql/test4.sql}
5 Przykładowa treść laboratorium
Proszę przygotować następujące skrypty na temat podany przez prowadzącego:
8
" skrypt tworzÄ…cy tabele,
" skrypt modyfikujÄ…cy tabele,
" skrypt usuwajÄ…cy tabele,
" skrypt wprowadzajÄ…cy dane do tabel,
" skrypt modyfikujÄ…cy dane w tabelach,
" skrypt wyświetlający dane z tabel,
" skrypt usuwajÄ…cy dane z tabel.
Uwaga! Oceniane będą tylko instrukcje, które zostaną wykonane bez błędów przez serwer Oracle.
Literatura
[1] Thomas Connolly, Carolyn Begg,  Systemy baz danych , Wydawnictwo RM, 2004
[2] Jeffrey D. Ullman, Jennifer Widom,  Podstawowy wykład z systemów baz danych , WNT, 2001
[3] Jeffrey D. Ullman, Jennifer Widom,  Systemy baz danych Pełny wykład , WNT, 2006
[4] Kevin Loney, Bob Bryla,  Oracle Database 10g Podręcznik administratora baz danych , Helion, 2008
[5] Scott Urman, Ron Hardman, Michael McLaughlin,  Oracle Database 10g Programowanie w języku
PL/SQL , Helion, 2008
[6] Ramez Elmasri, Shamkant B. Navathe,  Wprowadzenie do systemów baz danych , Helion, 2005
[7] Jose A. Ramalho,  Oracle 8i , Mikom, 2001
[8]  Oracle®Database SQL Language Reference 11g Release 1 (11.1) B28286-02 (b28286.pdf)
[9]  Oracle®Database SQL Language Quick Reference 11g Release 1 (11.1) B28285-02 (b28285.pdf)
9