home_site

Lab02 - Bazy danych cw.02 [ lab:31.03.2023, ver. SPN.2023.03.28.004 ]

Zawartość strony

Bazy danych

Tematyka zajęć:

  1. Agregacja danych, grupowanie i filtrowanie wyników
  2. Relacje 1-1, 1-N i N-M. Złączenia w relacyjnej bazie danych. Baza KURS
  3. Przykładowa baza danych - baza BIBLIOTEKA

  1. Agregacja danych, grupowanie i filtrowanie wyników. Tabela UCZESTNIK.

    1. Baza danych Postgresql (DBaaS - ElephantSQL). Prezentacja funkcji agregujących na tabeli lab01.uczestnik w bazie danych Postgresql. 
      --==============================================================================
-- Cwiczenie 04 - jezyk DQL - funkcje agregujace
--==============================================================================
-- *****************************************************************************
-- Przygotowanie danych do cwiczenia 4 
DROP TABLE lab01.uczestnik;
CREATE TABLE lab01.uczestnik 
  ( id int PRIMARY KEY, imie varchar(30), nazwisko varchar(30), rok char(4) ) ;
ALTER TABLE lab01.uczestnik ADD ocena int;
ALTER TABLE lab01.uczestnik ADD oplata numeric(10,2);
INSERT INTO lab01.uczestnik ( id, imie, nazwisko, rok, ocena, oplata ) VALUES 
  ( 1, 'Adam', 'Abacki', '2001', 5, 100.00 ),
  ( 2, 'Bogdan', 'Babacki', '2001', 4, 120.00 ),
  ( 3, 'Marek', 'Cabacki', '2002', 4, 130.00 ),
  ( 4, 'Edward', 'Dadacki', '2002', 5, 100.00 ),
  ( 5, 'Zygmunt', 'Ebacki', '2002', 4, 100.00 ),
  ( 6, 'Anna', 'Mamacka', '2001', 4, 150.00 ),
  ( 7, 'Szymon', 'Zawacki', '2003', 5, 100.00 ),
  ( 8, 'Krystian', 'Nanacki', '2003', 4, 120.00 ),
  ( 9, 'Ewa', 'Cabacka', '2001', 5, 130.00 ),
  (10, 'Andrzej', 'Tadacki', '2002', 5, 100.00 ),
  (11, 'Zofia', 'Kowalska', '2002', 4, 140.00 ),
  (12, 'Maria', 'Mamacka', '2003', 5, 150.00 );
INSERT INTO lab01.uczestnik ( id, nazwisko, rok ) VALUES (13, 'Sosnowski', '2001') ;
-- **********************************************************************************
-- Suma wniesionych oplat SUM()
SELECT SUM(oplata) AS oplata FROM lab01.uczestnik ;
-- Liczba wszystkich uczestnikow COUNT()
SELECT COUNT(*) AS liczba_uczestnikow FROM lab01.uczestnik ;
-- Srednia wartosc oplaty dla wszystkich uczestnikow AVG()
SELECT AVG(oplata) AS srednia_oplat FROM lab01.uczestnik ;
-- Wartosc maksymalna i minimalna oplaty za kurs
SELECT MIN(oplata) AS min_oplata, MAX(oplata) AS max_oplata FROM lab01.uczestnik ; 
-- Liczba uczestnikow w poszczegolnych latach
SELECT rok, COUNT(*) FROM lab01.uczestnik GROUP BY rok;
-- Suma oplat w poszczegolnych latach
SELECT rok, SUM(oplata) AS oplata FROM lab01.uczestnik GROUP BY rok;
-- Lista kursow w poszczegolnych latach w ktorych liczba uczestnikow wieksza niz podana wartosc
SELECT rok, COUNT(*) FROM lab01.uczestnik GROUP BY rok HAVING COUNT(*) > 4;
    2. Zadania do ćwiczenia - agregacja danych, grupowanie i filtrowanie wyników.
      1. Liczba rekordów w tabeli Ksiazka 
          
SELECT COUNT(*) FROM lab01.ksiazka  ;
      2. Liczba rekordów w tabeli Ksiazka dla wybranego autora czy wydawnictwa. 
          
SELECT COUNT(*) FROM lab01.ksiazka WHERE ...  ;
      3. Maksymalna, minimalna i średnia wartość ceny ksiązki w tabeli. 
          
SELECT MIN(...), MAX(...), AVG(...) FROM lab01.ksiazka  ;
      4. Liczba książek w określonym przedziale cen. 
          
SELECT COUNT(*) FROM lab01.ksiazka WHERE ... ;
      5. Wydawnictwa dla których liczba książek w bazie jest większa niż 2. 
          
SELECT wydawnictwo FROM lab01.ksiazka GROUP BY wydawnictwo HAVING count(*) > 2  ;
      6. Liczba ksiązek dla każdego wydawnictwa. 
          
SELECT wydawnictwo, count(*) FROM lab01.ksiazka GROUP BY wydawnictwo ;

  2. Relacje 1-1, 1-N i N-M. Złączenia w relacyjnej bazie danych. Baza danych KURS

    1. W ramach tego punktu zostaną przedstawione relacje pomiędzy tabelami typu 1-N i N-M w ramach prostej bazy danych "KURS v.1.5" umożliwiającej zarządzanie uczestnikami na kursach. Uczestnik może uczestniczyć w kilku kursach. Występuje tu relacja wiele do wiele (N-M) - uczestnik może być na kilku kursach i w danym kursie uczetniczy kilku uczestników. Dodatkowo kursy należą do określonych kategorii kursów. W danej kategorii może być kilka kursów oraz kurs nie może należeć do kilku kategorii - jest to relacja jeden do wielu (1-N, jedna kategoria - kilka kursów).
    2. Na rys.1. przedstawiono diagram ERD (Entity Relation Diagram) pomiędzy poszczególnymi encjami ( tabelami ) przedstawionymi w projekcie.
      Lab2_ERD_Kurs
      Rys.1. Diagram ERD bazy danych Kurs v.1.5 (DBeaver)
      Relacje pomiędzy tabelami w projekcie.
      • Pomiędzy encjami KURS i KATEGORIA występuje relacja jeden do wielu – kategoria może posiadać kilka kursów, natomiast kurs może należeć tylko w do jednej kategorii.
      • Relacja pomiędzy encjami KURS i UCZESTNIK jest typu wiele do wielu, uczestnicy mogą być na kilku kursach i na danym kursie może być kilku uczestników. Pomiędzy encjami występuję encja asocjacyjna KURS-UCZESTNIK (na rysunku uczest-kurs).
    3. W ramach bazy danych KURS v.1.5 wyróżniamy następujące encje:
      • UCZESTNIK - opisuje użytkownika kursu,
      • KURS - opisuje kurs,
      • KATEGORIA - opisuje kategorie kursów do których przypisujemy kursy,
      • KURS-UCZESTNIK - encja asocjacyjna, łącząca tabele uczestnik i kurs.
    4. Struktura tabel w bazie.
      • Tabela UCZESTNIK
        id_uczestintPRIMARY KEYKlucz główny
        imievarchar(30)NOT NULL
        nazwiskovarchar(30)NOT NULL
      • Tabela KATEGORIA
        id_kategoriaintPRIMARY KEYKlucz główny
        nazwavarchar(30)
      • Tabela KURS
        id_kursintPRIMARY KEYKlucz główny
        nazwavarchar(30)NOT NULL
        rokchar(4)NOT NULL
        id_kategoriaintFOREIGN KEYKlucz obcy do tabeli kategoria
      • Tabela KURS-UCZESTNIK
        id_uczestintFOREIGN KEYKlucz obcy do tabeli uczestnik
        id_kursintFOREIGN KEYKlucz obcy do tabeli kurs
        ocenaint
        oplataNumeric(10,2)
    5. Tworzymy nowy schemat kurs w bazie danych, w którym umieścimy opisane wyżej tabele. Do utworzonej bazy danych wstawimy przykładowe dane przedstawione poniżej. Przykład zamyka kilka przykładowych zapytań do bazy. Zadanie zrealizujemy w bazie PostgreSQL w serwisie DBaaS ElephantSQL w narzędziu DBeaver. 
             
--==============================================================================
-- Cwiczenie 05 - baza danych KURS - DBaaS ElephantSQL
--==============================================================================
-- DDL - przygotowanie struktury tabel do bazy KURS 
--==============================================================================
--
-- Polecenie tworzące schemat "KURS"
CREATE SCHEMA  kurs ;
--
-- Polecenie tworzące tabelę "KURS.UCZESTNIK"
CREATE TABLE kurs.uczestnik 
   ( id_uczest int, imie varchar(30), nazwisko varchar(30), 
     CONSTRAINT PK_uczestnik PRIMARY KEY ( id_uczest ) ) ;
--	   
-- Polecenie tworzące tabelę "KURS.KATEGORIA"
CREATE TABLE kurs.kategoria
    ( id_kategoria int, nazwa varchar(30),
      CONSTRAINT PK_kategoria PRIMARY KEY ( id_kategoria ) ) ; 	 
--	 
-- Polecenie tworzące tabelę "KURS.KURS"     
CREATE TABLE kurs.kurs 
   ( id_kurs int, nazwa varchar(30), rok char(4), id_kategoria int,
     CONSTRAINT PK_kurs PRIMARY KEY ( id_kurs ),
-- Referencja w tabeli KURS.KURS do tabeli KURS.KATEGORIA
     CONSTRAINT FK_kurs_kategoria FOREIGN KEY(id_kategoria)
       REFERENCES kurs.kategoria (id_kategoria)       
      ) ;
--	  
-- Polecenie tworzące tabelę "KURS.KURS_UCZESTNIK"      
CREATE TABLE kurs.kurs_uczestnik
    ( id_uczest int, id_kurs int, ocena int, oplata numeric(10,2),
      CONSTRAINT PK_kurs_uczest PRIMARY KEY ( id_uczest, id_kurs ),
-- Referencja z tabeli KURS.KURS_UCZESTNIK do tabeli KURS.KURS
     CONSTRAINT FK_kurs FOREIGN KEY(id_kurs)
       REFERENCES kurs.kurs (id_kurs),  
-- Referencja z tabeli KURS.KURS_UCZESTNIK do tabeli KURS.UCZESTNIK
     CONSTRAINT FK_uczestnik FOREIGN KEY(id_uczest)
       REFERENCES kurs.uczestnik (id_uczest)                     
       ) ;   
--==============================================================================
-- DML - Przykładowe dane do bazy 
--==============================================================================  
--
INSERT INTO kurs.uczestnik ( id_uczest, imie, nazwisko ) VALUES 
  ( 1, 'Adam', 'Abacki' ),
  ( 2, 'Bogdan', 'Babacki' ),
  ( 3, 'Marek', 'Cabacki' ),
  ( 4, 'Edward', 'Dadacki' ),
  ( 5, 'Zygmunt', 'Ebacki' ),
  ( 6, 'Anna', 'Mamacka' ),
  ( 7, 'Szymon', 'Zawacki' ),
  ( 8, 'Krystian', 'Nanacki' ),
  ( 9, 'Ewa', 'Cabacka' ),
  (10, 'Andrzej', 'Tadacki' ),
  (11, 'Zofia', 'Kowalska' ),
  (12, 'Maria', 'Mamacka' ), 
  (13, 'Szymon', 'Sosnowski') ;
--  
INSERT INTO kurs.kategoria ( id_kategoria, nazwa ) VALUES
  ( 1, 'Informatyka' ),
  ( 2, 'Jezyki obce' ) ;
--  
INSERT INTO kurs.kurs ( id_kurs, nazwa, rok, id_kategoria ) VALUES
  ( 1, 'J.angielski', '2001', 2),
  ( 2, 'J.angielski', '2002', 2),
  ( 3, 'J.niemiecki', '2002', 2),
  ( 4, 'Bazy danych', '2001', 1),
  ( 5, 'Jezyk C', '2003', 1),
  ( 6, 'Jezyk Ada', '2002', 1) ;
--  
INSERT INTO kurs.kurs_uczestnik ( id_uczest, id_kurs, ocena, oplata ) VALUES 
  ( 1, 1, 4, 100.00 ),
  ( 2, 1, 5, 120.00 ),
  ( 3, 1, 3, 130.00 ),
  ( 4, 1, 3, 110.00 ),
  ( 5, 1, 4, 100.00 ),
  ( 6, 1, 5, 140.00 ),
  ( 7, 1, 5, 140.00 ),
  ( 8, 1, 4, 130.00 ),
  ( 1, 2, 4, 160.00 ),
  ( 2, 2, 4, 160.00 ),
  ( 9, 4, 4, 100.00 ),
  (10, 4, 4, 100.00 ),
  (11, 4, 4, 100.00 ),
  (12, 6, 4, 100.00 ),
  (13, 6, 5, 100.00 ),
  ( 3, 3, 4, 120.00 ),
  ( 4, 3, 4, 120.00 ),
  ( 5, 3, 3, 120.00 ),
  ( 6, 3, 4, 120.00 ),
  ( 7, 3, 4, 120.00 );
   
--==============================================================================
-- DQL - Przykładowe zapytania do bazy 
--==============================================================================
--
-- Lista kursow z przynaleznoscia do grupy ( trzy mozliwosci )
---------------------------------------------------------------
SELECT k.id_kurs, k.nazwa, k.rok, g.nazwa 
FROM kurs.kurs k, kurs.kategoria g
    WHERE k.id_kategoria = g.id_kategoria ; 
    
SELECT k.id_kurs, k.nazwa, k.rok, g.nazwa 
FROM kurs.kurs k JOIN kurs.kategoria g ON k.id_kategoria = g.id_kategoria ;

SELECT k.id_kurs, k.nazwa, k.rok, g.nazwa 
FROM kurs.kurs k JOIN kurs.kategoria g USING(id_kategoria) ;
--
-- Lista uczestnikow na poszczegolnych kursach
-----------------------------------------------
SELECT k.nazwa, k.rok, u.imie, u.nazwisko 
FROM kurs.kurs k JOIN kurs.kurs_uczestnik ku ON k.id_kurs = ku.id_kurs
                 JOIN kurs.uczestnik u ON ku.id_uczest = u.id_uczest
ORDER BY k.nazwa, u.nazwisko ; 

SELECT k.nazwa, k.rok, u.imie, u.nazwisko 
FROM kurs.kurs k JOIN kurs.kurs_uczestnik ku USING(id_kurs)
                 JOIN kurs.uczestnik u USING(id_uczest)
ORDER BY k.nazwa, u.nazwisko ; 
--
-- Liczba uczestnikow na poszczegolnych kursach
------------------------------------------------
SELECT k.nazwa, k.rok, COUNT(*), SUM(oplata), AVG(ocena) 
FROM kurs.kurs k JOIN kurs.kurs_uczestnik ku ON k.id_kurs = ku.id_kurs
                 JOIN kurs.uczestnik u ON ku.id_uczest = u.id_uczest
GROUP BY k.nazwa, k.rok ;

SELECT k.nazwa, k.rok, COUNT(*), SUM(oplata), AVG(ocena) 
FROM kurs.kurs k JOIN kurs.kurs_uczestnik ku USING(id_kurs)
                 JOIN kurs.uczestnik u USING(id_uczest)
GROUP BY k.nazwa, k.rok ;
--
-- Lista kursow w ktorych uczestniczyly poszczegolne osoby
-----------------------------------------------------------
SELECT u.nazwisko, u.imie, k.nazwa, k.rok
FROM kurs.uczestnik u JOIN kurs.kurs_uczestnik ku ON u.id_uczest = ku.id_uczest
                      JOIN kurs.kurs k ON ku.id_kurs = k.id_kurs
ORDER BY u.nazwisko, k.nazwa, k.rok ;

SELECT u.nazwisko, u.imie, k.nazwa, k.rok
FROM kurs.uczestnik u JOIN kurs.kurs_uczestnik ku USING(id_uczest)
                      JOIN kurs.kurs k USING(id_kurs)
ORDER BY u.nazwisko, k.nazwa, k.rok ;
    6. Realizacja operacji INSERT, UPDATE, DELETE oraz SELECT w bazie danych:
      • Dodać nowy kurs do bazy danych 
          
INSERT INTO kurs.kategoria ( ... ) VALUES ( ... ) ;
      • Dodać nowego uczestnika do bazy danych 
          
INSERT INTO kurs.uczestnik ( ... ) VALUES ( ... ) ;
      • Przypisać uczestnika do kursu INSERT INTO kurs.kurs_uczestnik ( ... ) VALUES ( ... ) ;
      • Usunać uczestnika z bazy danych ( Uwaga, kiedy można usunąć uczestnika z tabeli kurs.uczestnik ? ) 
          
DELETE FROM kurs.uczestnik WHERE id_uczestnik = ... ;
      • Usunać kurs z bazy danych ( Uwaga, kiedy można usunąć uczestnika z tabeli kurs.uczestnik ? ) 
          
DELETE FROM kurs.kurs WHERE id_kurs = ... ;
      • Przepisać uczestnika z jedngo do drugiego kursu 
          
UPDATE kurs.kurs_uczestnik SET id_kurs = ... WHERE id_uczestnik = ... AND id_kurs = ... ;
      • Lista wszystkich uczestników 
          
SELECT * FROM kurs.uczestnik ;
      • Lista uczestników ktorzy uczestniczyli w co najmniej w dwóch kursach 
          
SELECT nazwisko, imie FROM kurs.uczestnik u join kurs.kurs_uczestnik ku ON u.id_uczest = ku.id_uczest GROUP BY u.id_uczest HAVING count(*) >= 1 ;
      • Statystyka ocen w bazie danych (można też opłat) 
          
SELECT ocena, count(*) FROM kurs.kurs_uczestnik GROUP BY ocena ORDER BY ocena;
      • Lista osób, które uzyskały ocenę bdb z kursu 
          
SELECT imie, nazwisko FROM kurs.uczestnik u JOIN kurs.kurs_uczestnik ku ON u.id_uczest = ku.id_uczest WHERE ocena = 5 ORDER BY nazwisko;

  3. Przykładowa baza danych BIBLIOTEKA

    1. W ramach tego punktu zostanie przedstawiony przykład prezentujący prosty system biblioteczny. System zawiera informację o czytelnikach oraz książkach dostępnych w bibliotece. System umożliwia wypożyczenie książki czytelnikowi, śledzenie historii wypożyczeń przez czytelnika oraz historię wypożyczeń danego egzemplarza książki. Dodatkowo wprowadzono możliwość przypisania książek do odpowiednich grup tematycznych.
    2. W ramach opracowanej bazy danych wyróżniamy następujące encje:
      • CZYTELNIK - opisuje użytkownika biblioteki,
      • KSIAZKA - zawiera opis książki,
      • EGZEMPLARZ - opisuje poszczególne egzemplarze książek dostępnych w bibliotece,
      • DZIAL - encja słownikowa,
      • WYPOZYCZENIE - opisuje relację pomiędzy czytelnikiem, a konkretnym egzemplarzem książki.
    3. Na rys.2. przedstawiono diagram ERD (Entity Relation Diagram) pomiędzy poszczególnymi encjami wyróżnionymi w projekcie.
      Lab2_ERD_Biblioteka
      Rys.2. Diagram ERD bazy danych Biblioteka
      Relacje pomiędzy tabelami w projekcie.
      • Pomiędzy encjami CZYTELNIK i WYPOZYCZENIE występuje relacja jeden do wielu – czytelnik może wypożyczyć kilka książek (egzemplarzy), natomiast książka (egzemplarz) może być w danym momencie wypożyczona przez jednego czytelnika.
      • Relacja pomiędzy encjami EGZEMPLARZ i KSIAZKA jest typu jeden do wielu, poszczególne książki mogą mieć po kilka egzemplarzy w bibliotece.
      • Relacja pomiędzy encjami WYPOZYCZENIE i EGZEMPLARZ jest jeden do wielu. Poszczególne egzemplarze książek mogą być wypożyczone przez różnych czytelników, oczywiście w różnym czasie.
      • Encja DZIAL jest encją słownikową zawierającą nazwy działów, do których przypiszemy książkę. Pomiędzy encjami KSIAZKA i DZIAL występuje relacja jeden do wielu, do jednego działu należy N książek.
    4. Struktura tabel w bazie
      • Tabela CZYTELNIK
        id_czytelnikintPRIMARY KEYKlucz główny
        nazwiskovarchar(50)NOT NULL
        imievarchar(50)NOT NULL
        kod_pocztowyvarchar(50)
        miastovarchar(50)
        adresvarchar(50)
        emailvarchar(50)
      • Tabela KSIAZKA
        id_ksiazkaintPRIMARY KEYKlucz główny
        tytulvarchar(300)NOT NULL
        autorvarchar(300)NOT NULL
        ISBNvarchar(50)
        rok_wydaniachar(10)
        wydaniechar(20)
        id_dzialIntFOREIGN KEYKlucz obcy do tabeli opisującej dział
      • Tabela EGZEMPLARZ
        id_egzemplarzintPRIMARY KEYIdentyfikator egzemplarza w bibliotece
        id_ksiazkaIntFOREIGN KEYKlucz obcy do tabeli książka
        StatusIntNOT NULLParametr opisujący stan książki
      • Tabela WYPOZYCZENIE
        id_wypozyczenieintPRIMARY KEYKlucz główny
        id_czytelnikIntFOREIGN KEYKlucz obcy do tabeli czytelnik
        id_egzemplarzIntFOREIGN KEYKlucz obcy do tabeli egzemplarz
        data_wypozyczenieDate
        data_oddaneDate
        statusIntParametr opisujący stan wypożyczenia
      • Tabela DZIAL
        id_dzialintPRIMARY KEYKlucz główny
        nazwavarchar(50)NOT NULL
    5. W tabelach od 3.1 do 3.5 zostały przedstawione struktury tabel utworzone na podstawie opisanych wcześniej encji z szczegółowym opisem każdego atrybutu. Na uwagę zasługują atrybuty „status” występujące w tabelach WYPOZYCZENIE i EGZEMPLARZ. Atrybut „status” w tabeli WYPOZYCZENIE zawiera dwie wartości 1 i 2. Wartość 1 określa, że książka opisana tym rekordem jest wypożyczona określonemu w tym rekordzie czytelnikowi, natomiast wartość „2” oznacza, że książka została zwrócona do biblioteki. Natomiast atrybut „status” w tabeli EGZEMPLARZ posiada następujące wartości: 0 – egzemplarz w bibliotece (możliwość wypożyczenia), 1 – egzemplarz pożyczony. Możliwe są też dodatkowe wartości dla tego atrybutu, np. 2 – egzemplarz książki dostępny tylko w bibliotece czy 3 – książka zarezerwowana (możliwości te nie zostały wykorzystane w projekcie).
    6. Utworzenie tabel w bazie danych BIBLIOTEKA
      • Tworzenie struktury tabel w relacyjnej bazie danych PostgreSQL, serwis DBaaS ElephantSQL narzędziem DBeaver. 
        --==============================================================================
-- Cwiczenie 06 - baza danych BIBLIOTEKA - DBaaS ElephantSQL
--==============================================================================
-- DDL - przygotowanie struktur tabel do bazy BIBLIOTEKA 
--==============================================================================  
--
-- Utworzenie schematu BIBL
CREATE SCHEMA bibl ;
--
-- Tabela  DZIAL
CREATE TABLE bibl.dzial(
  id_dzial int, 
  nazwa varchar(40),
  CONSTRAINT PK_dzial PRIMARY KEY ( id_dzial )
 ) ;
--
-- Tabela KSIAZKA
CREATE TABLE bibl.ksiazka(
	id_ksiazka int,
	tytul varchar(300) NOT NULL,
	autor varchar(300),
	isbn varchar(50),
	rok_wydania varchar(10),
	wydanie varchar(10),
  id_dzial int NOT NULL,  
  CONSTRAINT PK_ksiazka PRIMARY KEY ( id_ksiazka ),
-- Referencja tabela KSIAZKA tabela DZIAL
  CONSTRAINT FK_ksiazka_dzial FOREIGN KEY(id_dzial)
    REFERENCES bibl.dzial (id_dzial)  
) ; 
--
-- Tabela EGZEMPLARZ
CREATE TABLE bibl.egzemplarz(
   id_egzemplarz int NOT NULL,
   id_ksiazka int,
   status int,
   CONSTRAINT PK_egzemplarz PRIMARY KEY ( id_egzemplarz ),
-- Referencja tabela EGZEMPLARZ tabela KSIAZKA
   CONSTRAINT FK_egzemplarz_ksiazka FOREIGN KEY(id_ksiazka)
     REFERENCES bibl.ksiazka (id_ksiazka)   
) ;
--
-- Tabela CZYTELNIK 
CREATE TABLE bibl.czytelnik(
	id_czytelnik  int,
	imie varchar(50) NOT NULL,
	nazwisko varchar(50) NOT NULL,
	kod_pocztowy varchar(50),
	miasto varchar(50),
	adres varchar(100),
	email varchar(100),
  CONSTRAINT  PK_czytelnik  PRIMARY KEY ( id_czytelnik )	  
) ;
--
-- Tabela WYPOZYCZENIE
CREATE TABLE bibl.wypozyczenie(
  id_wypozyczenie SERIAL,
	id_czytelnik int NOT NULL,
	id_egzemplarz int NOT NULL,
	data_pozyczenie date,
  data_oddania date,
  status int,  
  CONSTRAINT PK_wypozyczenie PRIMARY KEY ( id_wypozyczenie ),
-- Referencja tabela WYPOZYCZENIE tabela CZYTELNIK
  CONSTRAINT FK_wypozyczenie_czytelnik FOREIGN KEY(id_czytelnik)
    REFERENCES bibl.czytelnik (id_czytelnik), 
-- Referencja tabela WYPOZYCZENIE tabela EGZEMPLARZ
  CONSTRAINT FK_wypozyczenie_egzemplarz FOREIGN KEY(id_egzemplarz)
    REFERENCES bibl.egzemplarz (id_egzemplarz)       
)  ;
    7. Wprowadzenie przykładowych danych do bazy BIBLIOTEKA
      • Wprowadzenie danych do tabel: CZYTELNIK, DZIAL, KSIAZKA 
        --==============================================================================
-- DML - Przykładowe dane, tabele: BIBL.CZYTELNIK, BIBL.DZIAL, BIBL.KSIAZKA
--==============================================================================
--
-- Wprowadzamy dane do tabeli BIBL.CZYTELNIK
insert into bibl.czytelnik (id_czytelnik, imie, nazwisko,email) VALUES
 ( 1, 'Adam','Abacki','abacki@onet.pl'),
 ( 2, 'Ewa','Babacka','babacka@onet.pl'),
 ( 3, 'Edward','Cabacki','cabacki@onet.pl'),
 ( 4, 'Krystyna','Dadacka','dadacka@wp.pl'),
 ( 5, 'Miroslaw','Ebacki','ebacki@gmail.com'),
 ( 6, 'Dariusz','Kabacki','kabacki@onet.pl'),
 ( 7, 'Alicja','Lalacka','lalacka@wp.pl'),
 ( 8, 'Krzysztof','Mamacki','mamacki@gmail.com'),
 ( 9, 'Hanna','Adamczyk','adamczyk@onet.pl'),
 (10, 'Ilona','Cudna','cudna@wp.pl'),
 (11,'Joanna','Debska','debska@wp.pl'),
 (12,'Waldemar','Drygiel','drygiel@onet.pl'),
 (13,'Adam','Fronc','fronc@gmail.com'),
 (14,'Marian','Glowka','glowka@o2.pl'),
 (15,'Helena','Rokicka','rokicka@o2.pl'),
 (16,'Maria','Sabak','sabak@wp.pl'),
 (17,'Wladyslaw','Stanczyk','stanczyk@wp.pl'),
 (18,'Adam','Szczypiorek','szczypiorek@gmail.com'),
 (19,'Adam','Kowalczyk','kowalczyk@o2.pl'),
 (20,'Adam','Zawadzki','zawadzki@onet.pl'),
 (21,'Adam','Kedra','kedra@wp.pl'),
 (22,'Adam','Sobczuk','sobczuk@o2.pl'),
 (23,'Klaudia','Wiacek','wiacek@gmail.com'),
 (24,'Malwina','Wierzbicka','wierzbicka@onet.pl');
--
-- Wprowadzamy dane do tabeli BIBL.DZIAL
insert into bibl.dzial (id_dzial, nazwa) VALUES (1, 'literatura') ;
insert into bibl.dzial (id_dzial, nazwa) VALUES (2, 'informatyka') ;
--
-- Wprowadzamy dane do tabeli BIBL.KSIAZKA ( dzial literatura )
insert into bibl.ksiazka (id_ksiazka, tytul,autor,id_dzial) VALUES 
 ( 1,'Duma i uprzedzenie','Jane Austen',1),
 ( 2,'Wladca Pierscieni','JRR Tolkien',1),
 ( 3,'Jane Eyre','Charlotte Bronte',1),
 ( 4,'Seria o Harrym Potterze','JK Rowling',1),
 ( 5,'Wichrowe Wzgorza','Emily Bronte',1),
 ( 6,'Rok 1984','George Orwell',1),
 ( 7,'Mroczne materie','Philip Pullman',1),
 ( 8,'Wielkie nadzieje','Charles Dickens',1),
 ( 9,'Male kobietki','Louisa M Alcott',1),
 (10,'Tessa Urberville','Thomas Hardy',1),
 (11,'Paragraf 22','Joseph Heller',1),
 (12,'Rebeka','Daphne Du Maurier',1),
 (13,'Hobbit','JRR Tolkien',1),
 (14,'Birdsong','Sebastian Faulks',1),
 (15,'Buszujacy w zbozu','JD Salinger',1),
 (16,'Zona podroznika w czasie','Audrey Niffenegger',1),
 (17,'Miasteczko Middlemarch','George Eliot',1),
 (18,'Przeminelo z wiatrem','Margaret Mitchell',1),
 (19,'Wielki Gatsby','F Scott Fitzgerald',1),
 (20,'Samotnia','Charles Dickens',1),
 (21,'Wojna i pokoj','Leo Tolstoy',1),
 (22,'Autostopem przez Galaktyke','Douglas Adams',1),
 (23,'Znowu w Brideshead','Evelyn Waugh',1),
 (24,'Zbrodnia i kara','Fyodor Dostoyevsky',1),
 (25,'Grona gniewu','John Steinbeck',1),
 (26,'Alicja w Krainie Czarow','Lewis Carroll',1),
 (27,'O czym szumia wierzby','Kenneth Grahame',1);
--
-- Wprowadzamy dane do tabeli BIBL.KSIAZKA ( dzial informatyka )
insert into bibl.ksiazka (id_ksiazka,autor,tytul,rok_wydania,id_dzial) VALUES 
 (28,'Jeffrey D. Ullman, Jennifer Widom','Podstawowy wyklad z systemow baz danych','2000',2),
 (29,'C.J.Date','Wprowadzenie do systemow baz danych','2000',2),
 (30,'Jeffrey D. Ullman','Systemy baz danych','1982',2),
 (31,'Paul Beynon- Davies','Systemy baz danych','1998',2),
 (32,'Georg Lausen, Gottfried Vossen','Obiektowe bazy danych','2000',2),
 (33,'Rafe Coburn','SQL dla kazdego','2001',2),
 (34,'Martin Gruber','SQL','2000',2),
 (35,'Jerzy Roszkowski','Analiza i projektowanie strukturalne','2002',2),
 (36,'Steven Roman','Access Baza danych, Projektowanie i programowanie','2001',2),
 (37,'Rick Dobson','Programowanie Microsoft Access 2000','2000',2),
 (38,'Stephen Forte','Access 2000, Ksiega eksperta','2001',2),
 (39,'John Viescas','Podrecznik Microsoft Access 2000','2000',2),
 (40,'Richard Stones','Neil Matthew, Od podstaw bazy danych i PostgreSQL','2001',2),
 (41,'John C. Worsley, Joshua D. Drake','PosgreSQL, Praktyczny przewodnik','2002',2),
 (42,'Michal Grochala','Java aplikacje bazodanowe','2001',2),
 (43,'Luke Welling, Laura Thomson','PHP i MySQL, Tworzenie stron WWW','2002',2),
 (44,'Jim Buyens','Bazy danych w Internecie krok po kroku','2000',2),
 (45,'Mark Whitehorn, Bill Marklyn','Relacyjne bazy danych','2003',2),
 (46,'Lech Banachowski i inni','Bazy danych, Wyklady i cwiczenia','2003',2),
 (47,'Thomas Connolly, Carolyn Begg','Systemy baz danych','2004',2);
      • Wprowadzenie danych do tabeli EGZEMPLARZ, egzemparze ksiązek w bibliotece 
        --==============================================================================
-- DML - Przykładowe dane, tabela: BIBL.EGZEMPLARZ
--============================================================================== 
--
-- Wprowadzamy informacje o egzemplarzach ksiazek - tabela BIBL.EGZEMPLARZ 
insert into bibl.egzemplarz (id_egzemplarz,id_ksiazka,status)  VALUES 
 (1,1,0),
 (2,2,0),
 (3,3,0),
 (4,4,0),
 (5,5,0),
 (6,6,0),
 (7,7,0),
 (8,8,0),
 (9,9,0),
 (10,10,0),
 (11,11,0),
 (12,12,0),
 (13,13,0),
 (14,14,0),
 (15,15,0),
 (16,16,0),
 (17,17,0),
 (18,18,0),
 (19,19,0),
 (20,20,0),
 (21,21,0),
 (22,22,0),
 (23,23,0),
 (24,24,0),
 (25,25,0),
 (26,26,0),
 (27,27,0),
 (28,28,0),
 (29,29,0),
 (30,30,0),
 (31,31,0),
 (32,32,0),
 (33,33,0),
 (34,34,0),
 (35,35,0),
 (36,36,0),
 (37,37,0),
 (38,38,0),
 (39,39,0),
 (40,40,0),
 (41,41,0),
 (42,42,0),
 (43,43,0),
 (44,44,0),
 (45,45,0),
 (46,46,0),
 (47,47,0);
      • Utworzenie historii wypożyczeń w bazie BIBLIOTEKA 
        --=================================================================================
-- Przykładowe dane - język DML - wprowadzenie danych do tabeli BIBL.WYPOZYCZENIE
--                                modyfikacja tabeli BIBL.EGZEMPLARZ
--=================================================================================
--
-- Wprowadzamy informacje o ksiazkach wypozyczonych  
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 1,  1,'2012-10-10',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=1;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 1,  3,'2012-09-10',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=3;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 1,  4,'2013-01-04',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=4;                                                   
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 2,  2,'2012-10-07',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=2;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 2,  5,'2012-08-05',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=5;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 3,  6,'2012-07-11',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=6;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 3,  9,'2012-09-15',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=9;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 5, 10,'2012-10-13',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=10;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 5, 11,'2012-11-19',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=11;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 5, 12,'2012-12-08',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=12;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 8,  7,'2013-01-18',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=7;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 8, 14,'2012-07-10',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=14;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES ( 8, 15,'2012-09-20',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=15;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (11, 22,'2012-11-11',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=22;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (11, 24,'2012-10-01',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=24;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (11, 30,'2012-09-25',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=30;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (12, 32,'2012-10-14',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=32;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (18, 41,'2012-08-16',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=41;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (21, 28,'2012-10-16',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=28;
-- oddanie ksiazki ( wypozyczenie ponowne )
update bibl.wypozyczenie set (data_oddania,status) = ('2013-02-04',2) where id_egzemplarz=41 and id_czytelnik=18 and status=1;
update bibl.egzemplarz set status=0 where id_egzemplarz=41;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (19, 41,'2013-02-05',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=41;
-- oddanie ksiazki ( wypozyczenie ponowne )
update bibl.wypozyczenie set (data_oddania,status) = ('2012-10-04',2) where id_egzemplarz=30 and id_czytelnik=11 and status=1;
update bibl.egzemplarz set status=0 where id_egzemplarz=30;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (21, 30,'2012-10-05',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=30;
-- oddanie ksiazki ( wypozyczenie ponowne )
update bibl.wypozyczenie set (data_oddania,status) = ('2012-11-04',2) where id_egzemplarz=30 and id_czytelnik=21 and status=1;
update bibl.egzemplarz set status=0 where id_egzemplarz=30;
insert into bibl.wypozyczenie (id_czytelnik, id_egzemplarz, data_pozyczenie, status) VALUES (1, 30,'2012-11-05',1);
update bibl.egzemplarz set status=1 where id_egzemplarz=30;
    8. Przetwarzanie danych w bazie BIBLIOTEKA - raporty i zestawienia
      • Normalizacja bazy danych umożliwia poprawne przetwarzanie danych przez system zarządzania bazą danych, jednak informacja jest najczęściej rozmieszczona w wielu tabelach i nie jest czytelna dla użytkownika bazy danych. Tworzenie raportów i odpowiednich zestawień przez użytkownika bazy danych wymaga tworzenia odpowiednich połączeń pomiędzy tabelami. Powiązania te realizujemy wykorzystując polecenie SELECT i klauzulę JOIN.
      • Na początek utworzymy raport informujący o liczbie książek wypożyczonych przez poszczególnych czytelników – nazwisko, imię i liczba pożyczonych książek. Dodatkowo posortujemy dane alfabetycznie po nazwisku. Informacja o pożyczonych książkach znajduje się w tabeli BIBL.WYPOZYCZENIE w rekordach dla których atrybut status ma wartość 1. Pozostałe atrybuty rekordu zawierają identyfikator czytelnika (klucz obcy do tabeli BIBL.CZYTELNIK) oraz identyfikator egzemplarza książki (klucz obcy do tabeli BIBL.EGZEMPLARZ). W celu otrzymania raportu zawierającego imię i nazwisko musimy połączyć tabelę BIBL.WYPOZYCZENIE i BIBL.CZYTELNIK po atrybucie złączenia – id_czytelnik. Poniżej przedstawiono polecenie SQL realizujące przedstawioną funkcjonalność. 
        -- czytelnicy posiadający wypożyczone książki
SELECT  c.nazwisko, c.imie, COUNT(*) AS liczba_ksiazek 
FROM bibl.czytelnik c JOIN bibl.wypozyczenie w ON c.id_czytelnik = w.id_czytelnik 
WHERE  w.status=1
GROUP BY  c.id_czytelnik
ORDER BY  c.nazwisko;
        Lab2_Query01
        Rys.3.1. Czytelnicy posiadający wypożyczone książki
      • Kolejnym raportem, który utworzymy będzie zestawienie zawierające czytelników, którzy obecnie nie mają pożyczonych książek. Zestawienie to zostanie zrealizowane w oparciu o tabelę BIBL.CZYTELNIK. Poprzez polecenie selekcji z klauzulą NOT IN zostaną nie zostaną wybrane z tabeli BIBL.CZYTELNIK rekordy czytelników, którzy mają pożyczone książki. Informacja o czytelnikach posiadających wypożyczone książki znajduje się w tabeli BIBL.WYPOZYCZENIE – atrybut status równy 1. Polecenie tworzące odpowiedni widok przedstawiono poniżej. 
        -- czytelnicy nie posiadający wypożyczonych książek
SELECT  c.nazwisko, c.imie  FROM  bibl.czytelnik c 
WHERE  c.id_czytelnik NOT IN
 ( SELECT  DISTINCT w.id_czytelnik FROM  bibl.wypozyczenie w WHERE  w.status=1 )
ORDER BY c.nazwisko;
        Lab2_Query02
        Rys.3.2. Czytelnicy, którzy nie mają pożyczonych książek
      • Kolejne zestawienia będą prezentowały tytuły książek wypożyczonych i dostępnych w bibliotece. Informacja o wypożyczeniu książki jest dostępna poprzez atrybut status w tabeli BIBL.EGZEMPLARZ. Wartość atrybutu równa 0 – książka dostępna w bibliotece, natomiast 1 – książka pożyczona. Tabela BIBL.EGZEMPLARZ zawiera tylko informację identyfikującą egzemplarz i identyfikator książki. W celu uzyskania informacji zawierającej dodatkowo tytuł i autora pozycji należy połączyć tabelę BIBL.EGZEMPLARZ z tabelą BIBL.KSIAZKA po atrybucie złączenia id_ksiazka. Poniżej przedstawiono zapytania zwracające odpowiednio książki dostępne w bibliotece i pożyczone. 
        -- ksiazki wypozyczone
SELECT k.tytul, k.autor FROM bibl.egzemplarz e JOIN bibl.ksiazka k ON e.id_ksiazka=k.id_ksiazka
WHERE  e.status=1
ORDER BY  k.tytul ;
-- ksiazki w bibliotece
SELECT k.tytul, k.autor FROM bibl.egzemplarz e JOIN bibl.ksiazka k ON e.id_ksiazka=k.id_ksiazka 
WHERE  e.status=0
ORDER BY k.tytul ;
        Lab2_Query03
        Rys.3.3. Ksiażki pożyczone
      • Ostatnie zapytanie SQL przedstawia zestawienia statystyczne informujące o liczbie książek dostępnych w bibliotece, pożyczonych i liczbie egzemplarzy w poszczególnych kategoriach (działach). W ramach zapytania użyty został operator UNION realizujący sumę zbiorów ( w algebrez relacyjnej dodaje rekordy z tabel do siebie). 
        -- liczba ksiazek w bibliotece   
SELECT   'Ksiazki w bibliotece' as Opis, count(*) as "Liczba ksiazek"  FROM  bibl.egzemplarz WHERE  status=0 
UNION 
-- liczba ksiazek pożyczonych
SELECT   'Ksiazki pozyczone', count(*) FROM  bibl.egzemplarz WHERE  status=1 
UNION
-- liczba książek w poszczególnych kategoriach
SELECT  'Dzial '  || d.nazwa, count(*)  FROM  bibl.dzial d JOIN  bibl.ksiazka k ON d.id_dzial = k.id_dzial 
GROUP BY  d.nazwa;
        Lab2_Query04
        Rys.3.4. Zestawienie statystyczne

Zadania do realizacji

  1. Zadanie 1 - zrealizować przykłady przedstawione na zajęciach w ramach punktu C (Biblioteka).
  2. Zadanie 2 - przygotować zapytania do bazy "Biblioteka".
    • Lista książek pożyczonych przez czytelnika "Cabacki Edward". Lista zawiera pola:
      • tytuł książki
      • autor książki
      • dział
      Lista posortowana po tytule. 
        
SELECT tytul, autor, nazwa FROM 
  bibl.wypozyczenie w JOIN bibl.egzemplarz e ON w.id_egzemplarz = e.id_egzemplarz 
                      JOIN bibl.ksiazka k ON e.id_ksiazka = k.id_ksiazka
                      join bibl.dzial d ON d.id_dzial = k.id_dzial
					  JOIN bibl.czytelnik c ON c.id_czytelnik = w.id_czytelnik					  
WHERE c.nazwisko = 'Cabacki' AND imie='Edward' 
ORDER BY tytul ;
      Lab2_Zadanie01
      Rys.4.1. Realizacja zapytania 1
    • Lista książek zawierających w tytule tekst "SQL" (należy wykorzystać LIKE). Lista zawiera pola:
      • tytuł książki
      • autor ksiązki
      Lista posrtowana po tytule; 
        
SELECT tytul, autor FROM bibl.ksiazka 					  
WHERE tytul LIKE '%SQL%'  
ORDER BY tytul ;
      Lab2_Zadanie02
      Rys.4.2. Realizacja zapytania 2
    • Tytuł i data pięciu ostatnio pożyczonych książek (należy wykorzystać LIMIT).Lista zawiera pola:
      • tytuł ksiązki
      • autor książki
      • nazwisko czytelnika
      • data pożyczenia
      Posortowane po dacie wypożyczenia. 
        
SELECT tytul, autor, nazwisko, data_pozyczenie FROM 
  bibl.wypozyczenie w JOIN bibl.egzemplarz e ON w.id_egzemplarz = e.id_egzemplarz 
                      JOIN bibl.ksiazka k ON e.id_ksiazka = k.id_ksiazka
					  JOIN bibl.czytelnik c ON c.id_czytelnik = w.id_czytelnik					  
ORDER BY data_pozyczenie DESC LIMIT 5 ;
      Lab2_Zadanie03
      Rys.4.3. Realizacja zapytania 3
    • Lista czytelników którzy pożyczyli ksiązki zawierająca następującą informację:
      • nazwisko
      • imie
      • tytul ksiazki
      • data wypozyczenia
      • data oddania
      Lista posortowana po nazwisku czytelnika. 
        
SELECT nazwisko, imie, tytul, data_pozyczenie, data_oddania FROM 
  bibl.wypozyczenie w JOIN bibl.egzemplarz e ON w.id_egzemplarz = e.id_egzemplarz 
                      JOIN bibl.ksiazka k ON e.id_ksiazka = k.id_ksiazka
					  JOIN bibl.czytelnik c ON c.id_czytelnik = w.id_czytelnik					  
ORDER BY nazwisko ;
      Lab2_Zadanie04
      Rys.4.4. Realizacja zapytania 4