Laboratorium 3.doc

Laboratorium 3

1.           Dostęp do bazy danych(powłoka psql)

2.           Literatura: PostgreSQL 8.0.3 Documentation (wersja angielska dla obecnie zainstalowanego Postgresa)
istnieje też tłumaczenie polskie trochę starszej wersji Postgresa: PostgreSQL 7.1.3 Tutorial

3. Definiowanie table
4.  

o             Od diagramu związków encji do tabel w bazie

W języku SQL tabele bazy danych tworzymy za pomocą polecenia CREATE TABLE. Jego argumentem jest lista par: nazwa atrybutu i jego typ. Nazwy atrybutów są "nagłówkami" w tabeli, w której zapisujemy dane. Atrybut można dodatkowo ustanowić kluczem głównym tabeli, można też zastrzec, że nie może on mieć niezdefiniowanej wartości (domyślnie może).

Zasada zamiany diagramu związków encji na zestaw definicji tabel jest następująca:

·         Każdemu zbiorowi encji diagramu (prostokątne pudełko) odpowiada jedna tabela o atrybutach równych wyróżnionym własnościom encji. Typy atrybutów zazwyczaj są jasne. Na ogół warto zadbać,by w tabeli był atrybut będący kluczem głównym (czasami może zajść potrzeba dodania takiego atrybutu).

·         Związek jednoznaczny na ogół wyrażany jest w postaci klucza obcego.

·         Związek wieloznaczny wyrażany jest w postaci odrębnej tabeli odwóch kluczach obcych wskazujących na obie związane encje. W takiej tabeli nie występuje pojedynczy atrybut będący kluczem głównym. Kluczem głównym jest para kluczy obcych.

·         W przypadku związku jedno-jednoznacznego należy zastanowić się, czy warto utrzymywać dwie odrębne tabele. Powodem do takiej decyzji może być chęć dopuszczenia, że poszczególne encje nie są związane z niczym, może być też silne przekonanie, że są to encje ważne i odrębne i nie chcemy mieszać ich w jednej tabeli.

Jeśli definicje tabel odpowiadających diagramowi zawarte są w pliku - powiedzmy o nazwie "tabele.sql", to plik ten należy przetworzyć w trybie "wsadowym". Oznacza to, że np. używając interfejsu znakowego psql, należy wywołać instrukcję "\i tabele.sql" (zakładamy, że jest on dostępny w bieżącym katalogu na naszym dysku).

o             Pierwszy przykład - "Faktury"

Jako pierwszy przykład, ilustrujący zamianę diagramu związków encji na definicje tabel, rozważmy problem reprezentacji w bazie faktur. W omówionym wcześniej diagramie mamy do czynienia z dwoma zbiorami encji - reprezentującymi odpowiednio: nagłówki oraz pozycje faktur. Pomiędzy tymi zbiorami zachodzi związek jednoznaczny "bycia pozycją faktury". Odzwierciedla on fakt, że każda faktura ma jeden i tylko jeden nagłówek (zawierający między innymi jej numer) oraz pewną liczbę pozycji. Każda pozycja (odpowiadająca faktowi nabycia danego towaru lub usługi) jest z kolei jednoznacznie przyporządkowana do konkretnego nagłówka.

Związek "bycia pozycją" reprezentujemy za pomocą klucza obcego w tabeli odpowiadającej zbiorowi encji "pozycje". Definicje stosownych tabel zawarte są w pliku F-tabele.sql.

SET client_encoding='utf-8';

-- W taki sposĂłb piszemy komentarz

-- a teraz definicje tabel

CREATE TABLE naglowek

(

    numer            int           PRIMARY KEY,

    imie             varchar(32)   not null,

    nazwisko        varchar(32)   not null,

    adres            varchar(100)  not null,

    data             date

);

CREATE TABLE pozycja

(

    numer            int   not null,

    nazwa            varchar(32)   not null,

    cena             int   not null,

    ilosc            int   not null,

    nr_fakt          int   REFERENCES naglowek(numer)

                           ON DELETE CASCADE

);

Naturalnym wydaje się założenie, że w momencie, gdy zechcemy usunąć z bazy nagłówek jakiejś faktury, to automatycznie usunięciu powinny ulec wszystkie pozycje na niej występujące. Temu właśnie celowi służy klauzula "ON DELETE CASCADE" skojarzona z atrybutem "nr_fakt" w tabeli "pozycje".

Aby wczytać definicje zawarte w pliku "F-tabele.sql" za pomocą narzędzia psql, należy wydać polecenie "\i F-tabele.sql". Oczywiście plik "F-tabele.sql" musi być dostępny na dysku w bieżącym katalogu.

o             Dane dla przykładu "Faktury"

Po zdefiniowaniu tabel należy wypełnić je treścią. W "prawdziwej" bazie danych będzie to oczywiście dokonywane stopniowo, w trakcie jej używania. Dla naszych celów, gdy bazy chcemy używać w celach testowych i dydaktycznych, nie będzie żadnych "prawdziwych" danych - trzeba je wprowadzić sztucznie w rozsądnej liczbie. Używamy w tym celu operacji INSERT. Dla naszego przykładu Faktur przykładowe dane zawarte są w pliku F-dane.sql.

SET client_encoding='utf-8';

INSERT INTO naglowek VALUES (1,'Jan','Kowalski','ul. Dudka 5, Sopot','2008-04-01');

INSERT INTO pozycja VALUES (1,'narty',120,1,1);

INSERT INTO pozycja VALUES (2,'czapka',23,1,1);

INSERT INTO pozycja VALUES (3,'rękawiczki',45,2,1);

INSERT INTO naglowek VALUES (2,'Grzegorz','Trek','ul Mały Dwór 12/3, Gdynia','2008-03-28');

INSERT INTO pozycja VALUES (1,'namiot',250,1,2);

INSERT INTO pozycja VALUES (2,'czapka',23,3,2);

INSERT INTO pozycja VALUES (3,'rękawiczki',45,2,2);

Tak jak poprzednio (w wypadku definicji tabel) plik ten należy załadować w trybie wsadowym. Np. używając narzędzia znakowego psql, należy wywołać instrukcję "\i F-dane.sql". Oczywiście, jak już mówiliśmy, plik ten musi być dostępny na naszym dysku.

Po załadowaniu danych do bazy wyświetl zawartość tabeli "pozycja". W  tym celu, używając programu psql wydaj polecenie:

SELECT * FROM pozycja;

W odpowiedzi otrzymasz listę wszystkich znajdujących się w bazie pozycji fakturowych. Spróbuj teraz usunąć któryś z nagłówków faktur – używając narzędzia psql wykonaj polecenie:

DELETE FROM naglowek WHERE nr_fakt=1;

Następnie sprawdź efekt wykonując ponownie polecenie

SELECT * FROM pozycja;

Czy widzisz efekt zastosowania w definicji tabeli "pozycja" klauzuli "ON DELETE CASCADE"?

o             Definicje tabel dla przykładu "Szkoła Wyższa"

Definicje tabel odpowiadające diagramowi związków encji opisującemu "Szkołę Wyższą" znajdują się w pliku SW-tabele.sql.

SET client_encoding='utf-8';

-- definicje tabel odpowiadajÄ…cych zbiorom encji nauczyciel i student

CREATE TABLE nauczyciel

(

    nr_leg           char(7)       PRIMARY KEY,

    imie             varchar(32)   not null,

    nazwisko         varchar(32)   not null

);

CREATE TABLE student

(

    nr_ind           char(7)       PRIMARY KEY,

    imie             varchar(32)   not null,

    nazwisko         varchar(32)   not null

);

-- Ĺ‚Ä…czymy encje przedmiot i termin

-- w diagramie był to związek jednojednoznaczny

-- pomiędzy dwoma zbiorami encji

CREATE TABLE przedmiot_termin

(

    kod              serial        PRIMARY KEY,

    rodzaj           varchar(20)   not null,

    nazwa            varchar(50)   not null,

    godziny          int           not null,

    -- nr legitymacji nauczyciela prowadzÄ…cego

    nr_leg           char(7)       not null, 

    -- teraz atrybuty terminu

    dzien_tyg        int         ,

    godzina          int         ,

    sala             varchar(5)  ,

    -- atrybuty encji termin muszÄ… być unikatowe w ramach tej tabeli

    CONSTRAINT       przedmiot_un UNIQUE(dzien_tyg,godzina,sala),

    -- klucz obcy - powiÄ…zanie przedmiotu z nauczycielem

    CONSTRAINT       przedmiot_fk FOREIGN KEY(nr_leg)

                        REFERENCES nauczyciel(nr_leg)

);

-- poniższa tabela jest realizacją związku między

-- zbiorami encji przedmioty i student

CREATE TABLE jest_sluchaczem

(

    nr_ind           char(7)     ,

    kod              int         ,

    CONSTRAINT       jest_sluchaczem_nr_ind_fk FOREIGN KEY(nr_ind)

                        REFERENCES student(nr_ind)

                        ON UPDATE CASCADE ON DELETE CASCADE,

    CONSTRAINT       jest_sluchaczem_kod_fk FOREIGN KEY(kod)

                        REFERENCES przedmiot_termin(kod)

                        ON UPDATE CASCADE ON DELETE CASCADE

);

Aby je wczytać za pomocą narzędzia psql należy wydać polecenie "\i SW-tabele.sql".

o             Dane dla przykładu "Szkoła Wyższa"

Przykładowe dane dla naszego przykładu Szkoły Wyższej zawarte są w pliku SW-dane.sql.

SET client_encoding='utf-8';

-- wypełnienie tabeli nauczyciel

INSERT INTO nauczyciel (nr_leg, imie, nazwisko)

       VALUES ('L 00001', 'Jan', 'Kowalski');

INSERT INTO nauczyciel (nr_leg, imie, nazwisko)

       VALUES ('L 00002', 'MichaĹ‚', 'Michalski');

INSERT INTO nauczyciel (nr_leg, imie, nazwisko)

       VALUES ('L 00003', 'Anna', 'Nowak');

INSERT INTO nauczyciel (nr_leg, imie, nazwisko)

       VALUES ('L 00004', 'Zuzanna', 'PrzepiĂłrkowska');

INSERT INTO nauczyciel (nr_leg, imie, nazwisko)

       VALUES ('L 00005', 'Eleonora', 'Plichta');

-- wypełnienie tabeli student

INSERT INTO student (nr_ind, imie, nazwisko)

       VALUES ('S 00001', 'Aleksander', 'Cyra');

INSERT INTO student (nr_ind, imie, nazwisko)

       VALUES ('S 00002', 'Hermenegilda', 'KociubiĹ„ska');

INSERT INTO student (nr_ind, imie, nazwisko)

       VALUES ('S 00003', 'Krzysztof', 'Kulikowski');

INSERT INTO student (nr_ind, imie, nazwisko)

       VALUES ('S 00004', 'Jerzy', 'Samp');

INSERT INTO student (nr_ind, imie, nazwisko)

       VALUES ('S 00005', 'BronisĹ‚aw', 'WstÄ™p');

-- wypełnienie tabeli przedmiot_termin

INSERT INTO przedmiot_termin (rodzaj, nazwa, godziny, nr_leg,

                              dzien_tyg, godzina, sala)

       VALUES ('wyklad', 'Matematyka', 2, 'L 00004', 1, 10, 'DB25');

INSERT INTO przedmiot_termin (rodzaj, nazwa, godziny, nr_leg,

                              dzien_tyg, godzina, sala)

       VALUES ('wyklad', 'Programowanie', 2, 'L 00002', 2, 8, 'UN109');

INSERT INTO przedmiot_termin (rodzaj, nazwa, godziny, nr_leg,

                              dzien_tyg, godzina, sala)

       VALUES ('laboratorium', 'Programowanie', 2, 'L 00001', 3, 12, 'DB37A');

INSERT INTO przedmiot_termin (rodzaj, nazwa, godziny, nr_leg,

                              dzien_tyg, godzina, sala)

       VALUES ('ćwiczenia', 'Matematyka', 2, 'L 00004', 2, 12, 'DB31');

INSERT INTO przedmiot_termin (rodzaj, nazwa, godziny, nr_leg,

                              dzien_tyg, godzina, sala)

       VALUES ('wykĹ‚ad', 'Filozofia', 2, 'L 00003', 4, 13, 'UN109');

-- wypełnienie tabeli jest_sluchaczem kodującej związek studenta i przedmiotu

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00001', 1);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00001', 4);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00001', 2);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00001', 3);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00002', 5);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00003', 3);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00003', 4);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00004', 1);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00004', 5);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00005', 3);

Plik ten należy załadować w trybie wsadowym. Np. używając terminala znakowego psql, należy wywołać instrukcję "\i SW-dane.sql".

W bazie danych dotyczącej szkoły wyższej z poprzedniego przykładu próbujemy wykonać komendy znajdujące się w pliku SW-fail.sql.

SET client_encoding='utf-8';

INSERT INTO przedmiot_termin (rodzaj, nazwa, godziny, nr_leg,

                              dzien_tyg, godzina, sala)

       VALUES ('wyklad', 'Socjologia', 2, 'L 00004', 1, 10, 'DB25');

INSERT INTO przedmiot_termin (rodzaj, nazwa, godziny, nr_leg,

                              dzien_tyg, godzina, sala)

       VALUES ('wyklad', 'Prawo', 2, 'L 00006', 5, 11, 'DB42');

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00001', 6);

INSERT INTO jest_sluchaczem (nr_ind, kod) VALUES ('S 00001', 4);

DROP TABLE student;

DROP TABLE nauczyciel;

DELETE FROM nauczyciel WHERE nr_leg='L 00001';

DROP TABLE jest_sluchaczem;

Wyjaśnij działanie poszczególnych komend, nawet gdy próba ich wykonania kończy się niepowodzeniem.

W przypadku błędów i konieczności rozpoczęcia pracy od nowa należy użyć instrukcji SQL postaci DROP TABLE nazwa, być może we wzmocnionej wersji DROP TABLE nazwa CASCADE. Usuwanie tabel można łatwo wykonać przy pomocy narzędzi graficznych: programu pgadmin3 i przeglądarki. W pliku SW-tabele_drop.sql znajdują się komendy usuwające poszczególne tabele z bazy. Zwróć uwagę na kolejność usuwania tabel. Czy kolejność ta może być dowolna?

SET client_encoding='utf-8';

-- usuwanie wszystkich tabel przykłady ze szkołą wyższą

DROP TABLE jest_sluchaczem;

DROP TABLE student;

DROP TABLE przedmiot_termin;

DROP TABLE nauczyciel;

-- UWAGA: kolejność usuwania tabel jest ważna. Dlaczego?

o             inne definicje tabel dla przykładu "Szkoła Wyższa"

W pliku SW2-tabele.sql znajduje się inna realizacja diagramu związków encji niż w punkcie: Dane dla przykładu "Szkoła Wyższa".

SET client_encoding='utf-8';

CREATE TABLE nauczyciel

(

    nr_leg           char(7)       not null,

    imie             varchar(32)   not null,

    nazwisko         varchar(32)   not null,

    CONSTRAINT       nauczyciel_pk PRIMARY KEY(nr_leg)

);

CREATE TABLE student

(

    nr_ind           char(7)       not null,

    imie             varchar(32)   not null,

    nazwisko         varchar(32)   not null,

    CONSTRAINT       student_pk PRIMARY KEY(nr_ind)

);

CREATE TABLE przedmiot

(

   kod              char(6)               ,

   rodzaj           varchar(20)   not null,

   nazwa            varchar(50)   not null,

   ile_godzin       int           not null,

   -- nr legitymacji nauczyciela prowadzacego

   nr_leg           char(7)       not null,

   CONSTRAINT       przedmiot_pk PRIMARY KEY(kod),

   -- klucz obcy - powiazanie przedmiotu z nauczycielem

   CONSTRAINT       przedmiot_fk FOREIGN KEY(nr_leg)

                       REFERENCES nauczyciel(nr_leg)...