BAZY DANYCH PROJEKT

(1)

1

Tomasz Osmelak, 258441 Wrocław, 26.01.2014

Instytut Matematyczny

Wydział Matematyki I Informatyki Uniwersytet Wrocławski

BAZY DANYCH PROJEKT

Sprawdzający:

dr hab. Robert Stańczy

(2)

2

Spis treści:

1. Schemat bazy danych...3

2. Opis bazy danych. ...4

3. Skrypt generujący bazę danych. ...4

3.1. Tworzenie tabeli. ...4

3.2. Ustawienie kluczy głównych. ...6

3.3. Utworzenie kluczy obcych...6

3.4. Utworzenie więzów check. ...7

3.5. Utworzenie funkcji. ...7

3.6. Utworzenie widoków. ...8

3.7. Utworzenie wyzwalaczy...9

3.8. Wprowadzenie danych. ...10

4. Elementy interfejsu - OpenOffice Base. ...12

(3)

1. Schemat bazy danych.

3

(4)

4

2. Opis bazy danych.

Utworzona baza danych jest przykładowym projektem stworzonym na potrzeby prostej wypożyczalni samochodów.

Baza zawiera informacje zarówno o klientach (dwie tabele z podstawowymi danymi klientów i numerami telefonów) oraz o samochodach (trzy tabele) jakimi dysponuje wypożyczalnia. Zaprojektowano też dodatkową tabelę z usługami opcjonalnymi, które klient może dokupić do podstawowej usługi, jaką jest wynajem auta.

Całość łączy jedna tabela o nazwie "wynajem", łącząca identyfikator klienta z numerem rejestracyjnym (reprezentującym konkretny egzemplarz auta) oraz z numerem dokupionej usługi dodatkowej oraz podstawowymi danymi dotyczącymi wynajmu.

3. Skrypt generujący bazę danych.

3.1. Tworzenie tabeli.

W tej części zostają utworzone tabele (7 sztuk). Większość atrybutów przyjmuje się jako NOT NULL, z wyjątkiem kilku, które nie uznano jako niezbędne do funkcjonowania wypożyczalni.

Tam, gdzie to było możliwe, zastosowano typ SERIAL, autonumerujący krotki w tabelach. Tak zdefiniowane atrybuty oczywiście będą kluczami głównymi.

DROP TABLE samochody CASCADE;

CREATE TABLE samochody(

id_auta SERIAL NOT NULL, klasa TEXT NOT NULL, marka TEXT NOT NULL, model TEXT NOT NULL, rocznik INTEGER, kolor TEXT,

silnik VARCHAR NOT NULL

);

(5)

5

DROP TABLE cennik CASCADE;

CREATE TABLE cennik(

klasa TEXT NOT NULL, opis TEXT,

cena_doba INTEGER NOT NULL );

DROP TABLE samochody_egzemplarze CASCADE;

CREATE TABLE samochody_egzemplarze(

nr_rejestracyjny VARCHAR(9) NOT NULL, id_auta INTEGER NOT NULL

);

DROP TABLE pakiety_uslug CASCADE;

CREATE TABLE pakiety_uslug(

nr_uslugi SERIAL NOT NULL, nazwa TEXT NOT NULL, opis TEXT NOT NULL );

DROP TABLE klienci CASCADE;

CREATE TABLE klienci(

id_klienta SERIAL NOT NULL, nazwisko TEXT NOT NULL, imie TEXT,

adres VARCHAR NOT NULL, pesel VARCHAR(11) NOT NULL );

DROP TABLE telefony_klienci CASCADE;

CREATE TABLE telefony_klienci(

numer_telefonu VARCHAR NOT NULL, id_klienta INTEGER NOT NULL

);

DROP TABLE wynajem CASCADE;

CREATE TABLE wynajem(

id_wynajmu SERIAL NOT NULL, id_klienta INTEGER NOT NULL,

nr_rejestracyjny VARCHAR(9) NOT NULL, data_od VARCHAR(10) NOT NULL, data_do VARCHAR(10) NOT NULL, nr_uslugi INTEGER

);

(6)

6

3.2. Ustawienie kluczy głównych.

W tej części zmodyfikowano tabele, przypisując im klucze główne. Dodatkowo uwzględniono fakt niepowtarzalności numeru PESEL klientów.

ALTER TABLE samochody ADD PRIMARY KEY (id_auta);

ALTER TABLE cennik ADD PRIMARY KEY (klasa);

ALTER TABLE samochody_egzemplarze ADD PRIMARY KEY (nr_rejestracyjny);

ALTER TABLE pakiety_uslug ADD PRIMARY KEY (nr_uslugi);

ALTER TABLE klienci ADD PRIMARY KEY (id_klienta);

ALTER TABLE klienci ADD UNIQUE (pesel);

ALTER TABLE telefony_klienci ADD PRIMARY KEY (numer_telefonu);

ALTER TABLE wynajem ADD PRIMARY KEY (id_wynajmu);

3.3. Utworzenie kluczy obcych.

W tej części skryptu generuje się klucze obce, dbające o spójność bazy danych.

Tak ustawione klucze obce zapobiegają usunięciu krotek z tabel, których atrybuty odwołują się do innych tabel (przykładowo: nie ma możliwości usunięcia klasy "C" z tabeli "cennik", gdy w tabeli "samochody" występuje co najmniej jeden samochód z tej klasy).

ALTER TABLE samochody ADD FOREIGN KEY (klasa) REFERENCES cennik(klasa) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE samochody_egzemplarze ADD FOREIGN KEY (id_auta) REFERENCES samochody(id_auta)

ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE telefony_klienci ADD FOREIGN KEY (id_klienta) REFERENCES klienci(id_klienta) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE wynajem ADD FOREIGN KEY (id_klienta) REFERENCES klienci(id_klienta) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE wynajem ADD FOREIGN KEY (nr_rejestracyjny) REFERENCES samochody_egzemplarze(nr_rejestracyjny)

ON UPDATE CASCADE;

(7)

7 ALTER TABLE wynajem ADD FOREIGN KEY (nr_uslugi) REFERENCES pakiety_uslug(nr_uslugi)

ON UPDATE CASCADE;

3.4. Utworzenie więzów check.

W tym punkcie tworzy się więzy czek, które mają za zadanie chronić bazę przed wprowadzeniem błędnych danych. I tak, zakłada się 11-cyfrowy numer PESEL, określony typ daty wypożyczeń i zwrotów, sensowny rocznik samochodów, rodzaj silnika.

ALTER TABLE klienci ADD CHECK (pesel ~ '^[0-9]*$');

ALTER TABLE klienci ADD CHECK (length(pesel) IN (11));

ALTER TABLE samochody ADD CHECK (silnik IN ('benzyna', 'diesel'));

ALTER TABLE samochody ALTER COLUMN silnik SET DEFAULT 'benzyna';

ALTER TABLE samochody ADD CHECK (rocznik>1900 AND rocznik<2100);

ALTER TABLE wynajem ADD CHECK (data_od ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$');

ALTER TABLE wynajem ADD CHECK (data_do ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$');

ALTER TABLE wynajem ADD CHECK (data_od<=data_do);

3.5. Utworzenie funkcji.

Przykładowe funkcje zwracające informacje z bazy danych. Pierwsza pozwala na uzyskanie opisu usługi dodatkowej na podstawie jej nazwy, druga zwraca pełne dane klienta, podając jedynie jego numer w bazie, trzecia służy do dodawania nowych klientów, zaś czwarta napisana jest do tworzenia nowych umów najmu, wiążąc klientów z samochodami.

CREATE OR REPLACE FUNCTION opis_uslugi(n TEXT) RETURNS TEXT AS ' DECLARE

x RECORD;

BEGIN

SELECT INTO x * FROM pakiety_uslug WHERE nazwa=n;

RETURN x.opis;

END;

' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION klient(nr INTEGER) RETURNS RECORD AS ' DECLARE

x RECORD;

BEGIN

SELECT INTO x * FROM klienci AS k, telefony_klienci AS t

(8)

8 WHERE k.id_klienta=nr AND k.id_klienta=t.id_klienta;

RETURN x;

END;

CREATE OR REPLACE FUNCTION dodaj_klienta(na TEXT, im TEXT, ad VARCHAR, pe VARCHAR(11)) RETURNS INTEGER AS '

BEGIN

INSERT INTO klienci(nazwisko, imie, adres, pesel) VALUES(na, im, ad, pe);

RETURN 0;

END;

CREATE OR REPLACE FUNCTION wypozycz(kli INTEGER, rej VARCHAR(9), d_od VARCHAR(10), d_do VARCHAR(10), nr_us INTEGER) RETURNS INTEGER AS '

BEGIN

INSERT INTO wynajem(id_klienta, nr_rejestracyjny, data_od, data_do, nr_uslugi) VALUES(kli, rej, d_od, d_do, nr_us);

RETURN 0;

END;

3.6. Utworzenie widoków.

Stworzono trzy widoki, które pokazują kolejno wszystkie: wypożyczenia zarejestrowane w bazie danych, samochody dostępne w wypożyczalni, numery telefonów klientów zarejestrowanych w bazie danych.

DROP VIEW wypozyczenia_wszystkie CASCADE;

CREATE VIEW wypozyczenia_wszystkie AS(

SELECT k.nazwisko AS "Nazwisko", k.imie AS "Imie", s.marka AS "Marka", s.model AS "Model", pu.opis AS "Uslugi"

FROM klienci AS k, wynajem AS w, samochody_egzemplarze AS se, samochody AS s, pakiety_uslug AS pu

WHERE k.id_klienta=w.id_klienta AND w.nr_rejestracyjny=se.nr_rejestracyjny AND se.id_auta=s.id_auta AND w.nr_uslugi=pu.nr_uslugi

ORDER BY k.nazwisko );

DROP VIEW wszystkie_samochody CASCADE;

CREATE VIEW wszystkie_samochody AS(

SELECT se.nr_rejestracyjny AS "Numer Rejestracyjny", s.marka AS "Marka", s.model AS Model, s.rocznik AS "Rocznik", s.klasa AS "Klasa"

FROM samochody_egzemplarze AS se, samochody AS s WHERE se.id_auta=s.id_auta

ORDER BY se.nr_rejestracyjny );

(9)

9 DROP VIEW telefony_klientow CASCADE;

CREATE VIEW telefony_klientow AS(

SELECT k.nazwisko AS "Nazwisko", k.imie AS "Imie", t.numer_telefonu AS "Numer"

FROM klienci AS k, telefony_klienci AS t WHERE k.id_klienta=t.id_klienta ORDER BY k.nazwisko

);

3.7. Utworzenie wyzwalaczy.

Utworzono skrypt generujący wyzwalacze, które egzekwują zastawy panujące w wypożyczalni. Klienci muszą mieć minimum 21 lat (skrypt przygotowany na rok 2014), a samochody nie mogą być starsze niż z rocznika 2011.

CREATE OR REPLACE FUNCTION wiek_klienta() RETURNS TRIGGER AS ' BEGIN

IF substring(NEW.pesel from 1 for 2)>''93'' THEN RAISE EXCEPTION ''Wiek klienta: minumum 21 lat!'';

END IF;

RETURN NEW;

END;

DROP TRIGGER wiek_klienta_trigger ON klienci CASCADE;

CREATE TRIGGER wiek_klienta_trigger BEFORE INSERT OR UPDATE ON klienci FOR EACH ROW EXECUTE PROCEDURE wiek_klienta();

CREATE OR REPLACE FUNCTION rocznik_auta() RETURNS TRIGGER AS ' BEGIN

IF NEW.rocznik<2011 THEN

RAISE EXCEPTION ''Saochody z rocznika 2011 lub nowsze!'';

END IF;

RETURN NEW;

END;

DROP TRIGGER rocznik_auta_trigger ON samochody CASCADE;

CREATE TRIGGER rocznik_auta_trigger BEFORE INSERT OR UPDATE ON samochody FOR EACH ROW EXECUTE PROCEDURE rocznik_auta();

(10)

10

3.8. Wprowadzenie danych.

Uzupełnienie bazy danych o przykładowe krotki, ilustrujące jej działanie.

Wszystkie dane zostały wprowadzone poprawnie, z zachowaniem wymagań z więzów check, wyzwalaczy.

INSERT INTO cennik (klasa, opis, cena_doba) VALUES ('A', 'Klasa: miejskie auta.', 80);

INSERT INTO cennik (klasa, opis, cena_doba) VALUES ('B', 'Klasa: male auta.', 100);

INSERT INTO cennik (klasa, opis, cena_doba) VALUES ('C', 'Klasa: auta kompaktowe.', 120);

INSERT INTO cennik (klasa, opis, cena_doba) VALUES ('D', 'Klasa srednia: auta rodzinne.', 160);

INSERT INTO samochody (klasa, marka, model, rocznik, kolor, silnik) VALUES ('A', 'Fiat', 'Panda', 2013, 'zielony', 'benzyna');

INSERT INTO samochody (klasa, marka, model, rocznik, kolor, silnik) VALUES ('B', 'Suzuki', 'Swift', 2013, 'czarny', 'diesel');

INSERT INTO samochody (klasa, marka, model, rocznik, kolor, silnik) VALUES ('B', 'Suzuki', 'Swift', 2014, 'bialy', 'benzyna');

INSERT INTO samochody (klasa, marka, model, rocznik, kolor, silnik) VALUES ('C', 'Skoda', 'Octavia', 2011, 'niebieski', 'diesel');

INSERT INTO samochody (klasa, marka, model, rocznik, kolor, silnik) VALUES ('C', 'Skoda', 'Octavia', 2013, 'srebrny', 'diesel');

INSERT INTO samochody (klasa, marka, model, rocznik, kolor, silnik) VALUES ('C', 'Hyundai', 'i30', 2012, 'czerwony', 'benzyna');

INSERT INTO samochody (klasa, marka, model, rocznik, kolor, silnik) VALUES ('D', 'Ford', 'Mondeo', 2012, 'granatowy', 'benzyna');

INSERT INTO samochody (klasa, marka, model, rocznik, kolor, silnik) VALUES ('D', 'Peugeot', '508', 2011, 'czarny', 'diesel');

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('AAA 12345', 1);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('BBB 1234', 1);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('CCC DD223', 2);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('DDD 836AS', 3);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('GGG 836AS', 4);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('EE 121AX', 5);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('FFF 2942X', 5);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('HHH 23876', 6);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('JJ HG762', 7);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('KKK 32192', 8);

INSERT INTO samochody_egzemplarze (nr_rejestracyjny, id_auta) VALUES ('LL 12413', 8);

INSERT INTO pakiety_uslug (nazwa, opis) VALUES ('Podstawowy', 'Kamizelka odblaskowa, nawigacja GPS');

INSERT INTO pakiety_uslug (nazwa, opis) VALUES ('Premium', 'Kamizelka odblaskowa, nawigacja GPS, fotelik dzieciecy, bagaznik dachowy');

INSERT INTO pakiety_uslug (nazwa, opis) VALUES ('VIP', 'Kamizelka odblaskowa, nawigacja GPS, fotelik dzieciecy, bagaznik dachowy, odtwarzacz DVD, dodatkowe ubezpieczenie');

(11)

11 INSERT INTO klienci (nazwisko, imie, adres, pesel) VALUES ('Abacki', 'Adam', '11-111 Wroclaw,

Sudecka 12/13', '65020211231');

INSERT INTO klienci (nazwisko, imie, adres, pesel) VALUES ('Babacki', 'Bartosz', '12-123 Trzebnica, Wroclawska 15/53', '90112321245');

INSERT INTO klienci (nazwisko, imie, adres, pesel) VALUES ('Cabacka', 'Celina', '42-252 Legnica, Slaska 23', '66041927492');

INSERT INTO klienci (nazwisko, imie, adres, pesel) VALUES ('Dabacki', 'Dariusz', '34-242 Kobierzyce, Bardzka 77', '74091884628');

INSERT INTO klienci (nazwisko, imie, adres, pesel) VALUES ('Ebacka', 'Eliza', '32-543 Zgorzelec, Dunska 16', '88042737294');

INSERT INTO telefony_klienci (numer_telefonu, id_klienta) VALUES ('123456789', 1);

INSERT INTO wynajem (id_klienta, nr_rejestracyjny, data_od, data_do, nr_uslugi) VALUES (1, 'AAA 12345', '2014-01-02', '2014-02-03', 2);

INSERT INTO wynajem (id_klienta, nr_rejestracyjny, data_od, data_do, nr_uslugi) VALUES (1, 'CCC DD223', '2014-03-22', '2014-04-01', 1);

INSERT INTO wynajem (id_klienta, nr_rejestracyjny, data_od, data_do, nr_uslugi) VALUES (2, 'BBB 1234', '2014-02-03', '2014-02-05', 1);

INSERT INTO wynajem (id_klienta, nr_rejestracyjny, data_od, data_do, nr_uslugi) VALUES (3, 'BBB 1234', '2014-02-07', '2014-02-12', 2);

INSERT INTO wynajem (id_klienta, nr_rejestracyjny, data_od, data_do, nr_uslugi) VALUES (3, 'KKK 32192', '2014-03-21', '2014-03-27', 3);

INSERT INTO wynajem (id_klienta, nr_rejestracyjny, data_od, data_do, nr_uslugi) VALUES (4, 'HHH 23876', '2014-04-01', '2014-04-30', 2);

INSERT INTO wynajem (id_klienta, nr_rejestracyjny, data_od, data_do, nr_uslugi) VALUES (5, 'GGG 836AS', '2014-03-08', '2014-03-15', 3);

INSERT INTO wynajem (id_klienta, nr_rejestracyjny, data_od, data_do, nr_uslugi) VALUES (5, 'JJ HG762', '2014-04-05', '2014-04-25', 3);

(12)

4. Elementy interfejsu - OpenOffice Base.

Interfejs graficzny bazy danych został wykonany przy pomocy OpenOffice Base. P

danych, a tym samym dostęp do tabel, więzów, wyzwalaczy itp., oraz oczywiście do wprowadzonych danych.

Widok główny bazy danych:

OpenOffice Base.

Interfejs graficzny bazy danych został wykonany przy pomocy OpenOffice Base. Program uzyskał połączenie z lokalną bazą danych, a tym samym dostęp do tabel, więzów, wyzwalaczy itp., oraz oczywiście do wprowadzonych danych.

12

rogram uzyskał połączenie z lokalną bazą

danych, a tym samym dostęp do tabel, więzów, wyzwalaczy itp., oraz oczywiście do wprowadzonych danych.

(13)

W celu dodania nowego klienta, klikamy dwa razy na tabelę "klienci", uzyskując

Jak widać, pewne dane zostały już wprowadzone.

Aby dodać nowego klienta, wystarczy w pustym wierszu wypełnić pola w kolumnach "nazwisko", "adres", "pesel".

W celu dodania nowego klienta, klikamy dwa razy na tabelę "klienci", uzyskując okno:

Jak widać, pewne dane zostały już wprowadzone.

Aby dodać nowego klienta, wystarczy w pustym wierszu wypełnić pola w kolumnach "nazwisko", "adres", "pesel".

13

Aby dodać nowego klienta, wystarczy w pustym wierszu wypełnić pola w kolumnach "nazwisko", "adres", "pesel".

(14)

Próba wprowadzenia zbyt młodego klienta (rocznik 1995) kończy się takim komunikatem:

14

(15)

Zbyt krótki numer PESEL:

15

(16)

Brak nazwiska:

16

(17)

Poprawnie wprowadzony klient:

Warto zwrócić uwagę na fakt, że pole w kolumnie "id_klienta" zostało automatycznie uzupełnione, zgodnie z oczekiwaniami po typie SERIAL. Pan Frank Fabacki otrzymał numer

"zmarnowane" przy nieudanych próbach wprowadzenia nowego klienta.

Warto zwrócić uwagę na fakt, że pole w kolumnie "id_klienta" zostało automatycznie uzupełnione, zgodnie z oczekiwaniami po typie SERIAL. Pan Frank Fabacki otrzymał numer dopiero 10, ponieważ poprzednie numery zostały

"zmarnowane" przy nieudanych próbach wprowadzenia nowego klienta.

17

Warto zwrócić uwagę na fakt, że pole w kolumnie "id_klienta" zostało automatycznie uzupełnione, zgodnie z

dopiero 10, ponieważ poprzednie numery zostały

(18)

Spójrzmy teraz na tabelę "wynajem":

Jak widać, kilku zarejestrowanych klientów skorzystało lub skorzysta z usług naszej wypożyczalni.

18

Jak widać, kilku zarejestrowanych klientów skorzystało lub skorzysta z usług naszej wypożyczalni.

(19)

19

Spróbujmy teraz usunąć z bazy jednego z takich klientów, klikając prawym przyciskiem myszy na puste pole po lewej

stronie wiersza i wybierając "Usuń wiersze":

(20)

20

W ten sposób także usunęliśmy dane pana Dabackiego z tabeli "wynajem" (kasowanie kaskadowe).

(21)

21

Aby wynająć klientowi auto, dodajemy wiersz w tabeli "wynajem". Oczywiście, także tu klucze obce, więzy check oraz wyzwalacze spełniają swoją rolę.

Nieistniejący klient:

Zły format daty:

(22)

22

Nieistniejące auto:

Data zwrotu przed datą wynajmu:

(23)

23