home_site

Lab03 - Relacje 1-N i N-M w RBD [ ver. BD1.2025.10.11.003 ]

Zawartość strony

Plan zajęć

Diagram ERD projektu KURS - referencje

  1. W ramach tego punktu zostanie przedstawiony system rejestracji uczestników kursu. System zawiera informację o uczestnikach, wykładowcach oraz kursach. System umożliwia zapis uczestnika na wybranym kursie oraz przypisanie wykładowcy do kursu.
  2. W ramach opracowanej bazy wyróżniamy następujące encje:
    • uczestnik (opisuje uczestnika kursu),
    • wykładowca (opisuje wykładowcę na kursie),
    • kurs (opisuje organizowane kursy),
    • kurs_opis ( opis organizowanych kursów),
    • uczest_kurs (opisuje relację pomiędzy uczestnikiem a kursem),
    • wykl_kurs (opisuje relację pomiędzy wykładowcą a kursem).
  3. Struktura tabel w bazie
    • Tabela UCZESTNIK
      id_uczestnikintPRIMARY KEYKlucz główny
      nazwiskovarchar(50)NOT NULL
      imievarchar(50)NOT NULL
      kod_pocztowyvarchar(50)
      miastovarchar(50)
      adresvarchar(50)
      emailvarchar(50)
    • Tabela WYKLADOWCA
      id_wykladowcaintPRIMARY KEYKlucz główny
      nazwiskovarchar(50)NOT NULL
      imievarchar(50)NOT NULL
      kod_pocztowyvarchar(50)
      miastovarchar(50)
      adresvarchar(50)
      emailvarchar(50)
    • Tabela KURS
      id_kursintPRIMARY KEYIdentyfikator kursu
      id_nazwaIntFOREIGN KEYKlucz obcy do tabeli KURS_OPIS
      id_grupaIntNOT NULLidentyfikator grupy
      terminDatedata kursu
    • Tabela KURS_OPIS
      id_nazwaintPRIMARY KEYKlucz główny
      opisvarcharNazwa kursu
    • Tabela UCZEST_KURS
      id_uczestintPK (FOREIGN KEY)Klucz obcy do tabeli UCZESTNIK
      id_kursintPK (FOREIGN KEY)Klucz obcy do tabeli KURS
    • Tabela WYKL_KURS
      id_wyklintPK (FOREIGN KEY)Klucz obcy do tabeli WYKLADOWCA
      id_kursintPK (FOREIGN KEY)Klucz obcy do tabeli KURS
  4. Na rys. 1.1 przedstawiono diagram ERD (Entity Relation Diagram) pomiędzy poszczególnymi encjami wyróżnionymi w projekcie.
    Lab3_ERD_Kurs
    Rys.1.1. Diagram ERD bazy danych KURS
    Relacje pomiędzy tabelami w projekcie.
    • Pomiędzy encjami KURS_OPIS i KURS występuje relacja jeden do wielu – nazwa kursu występuje w kilku edycjach kursu (kurs może być kilka razy wznawiany).
    • Relacja pomiędzy encjami KURS i UCZESTNIK jest typu wiele do wiele, uczestnik może uczestniczyć w kilku kursach oraz w kursie może uczestniczyć N uczestników. Realację pomiędzy encjami realizuje encja asocjacyjna UCZEST_KURS.
    • Relacja pomiędzy encjami KURS i WYKLADOWCA jest typu wiele do wiele, wykładowca może prowadzić kilka kursów oraz kurs może prowadzić kilku wykładowców. Realację pomiędzy encjami realizuje encja asocjacyjna WYKL_KURS.

Skrypty tworzące strukturę bazy i wprowadzające dane

Skrypt tworzący strukture tabel kurs_DDL.sql ( [listing dokumentu] [link do dokumentu] )

   ------------------------------------------------------------
--
-- ZAJECIA 3 - BD1
--
------------------------------------------------------------
-- Tworzenie schematu dla bazy danych KURS --
CREATE SCHEMA kurs ;
SET SEARCH_PATH TO kurs ;

-- Tworzenie tabel --
create table uczestnik ( id_uczestnik int, nazwisko varchar(30), imie varchar(30) ) ;
create table kurs ( id_kurs int, id_grupa int, id_nazwa int, termin varchar(30) ) ;
create table wykladowca ( id_wykladowca int, nazwisko varchar(30), imie varchar(30) ) ;
create table kurs_opis ( id_nazwa int, opis varchar(30) ) ;
create table uczest_kurs ( id_uczest int, id_kurs int ) ;
create table wykl_kurs ( id_wykl int, id_kurs int ) ;
--
-- modyfikacja tabel - dodanie klucza glownego --
alter table kurs add primary key (id_kurs) ;
alter table uczestnik add primary key (id_uczestnik) ;
alter table wykladowca add primary key (id_wykladowca) ;
alter table kurs_opis add primary key (id_nazwa) ;
alter table uczest_kurs add primary key (id_uczest, id_kurs) ;
alter table wykl_kurs add primary key (id_wykl, id_kurs) ;
--
-- modyfikacja tabel - dodanie refencji klucza obcego --
alter table uczest_kurs add foreign key (id_uczest) references uczestnik ( id_uczestnik) ;
alter table uczest_kurs add foreign key (id_kurs) references kurs ( id_kurs) ;
alter table wykl_kurs add foreign key (id_kurs) references kurs ( id_kurs) ;
alter table wykl_kurs add foreign key (id_wykl) references wykladowca ( id_wykladowca) ;
alter table kurs add foreign key (id_nazwa) references kurs_opis ( id_nazwa) ;

Skrypt wprowadzający dane kurs_DML.sql ( [listing dokumentu] [link do dokumentu] )

--
SET SEARCH_PATH TO kurs ;
-- wstawienie danych - tabela uczestnik
insert into uczestnik ( id_uczestnik, nazwisko, imie ) values 
( 1, 'Flisikowski', 'Jan'),
( 2, 'Olech', 'Andrzej'       ),
( 3, 'Płochocki', 'Piotr'    ),
( 4, 'Stachyra', 'Krzysztof' ),
( 5, 'Sztuka', 'Stanisław'   ),
( 6, 'Sosin', 'Tomasz'       ),
( 7, 'Głowala', 'Paweł'      ),
( 8, 'Straszewski', 'Józef'  ),
( 9, 'Dwojak', 'Marcin'      ),
(10, 'Kotulski', 'Marek'    ),
(11, 'Łaski', 'Michał'       ),
(12, 'Iwanowicz', 'Grzegorz' ),
(13, 'Barnaś', 'Jerzy'       ),
(14, 'Stachera', 'Tadeusz'   ),
(15, 'Gzik', 'Adam'          ),
(16, 'Całus', 'Łukasz'       ),
(17, 'Kołodziejek', 'Zbigniew'),
(18, 'Bukowiecki', 'Ryszard' ),
(19, 'Sielicki', 'Dariusz'   ),
(20, 'Radziszewski', 'Henryk'),
(21, 'Szcześniak', 'Mariusz' ),
(22, 'Nawara', 'Kazimierz'   ),
(23, 'Kęski', 'Wojciech'     ),
(24, 'Rafalski', 'Robert'    ),
(25, 'Hołownia', 'Mateusz'   ),
(26, 'Niedziałek', 'Marian'  ),
(27, 'Matuszczak', 'Rafał'   ),
(28, 'Wolf', 'Jacek'         ),
(29, 'Kolczyński', 'Janusz'  ),
(30, 'Chrobok', 'Mirosław'   )  ;
--
-- wstawienie danych - tabela kurs_opis
insert into kurs_opis ( id_nazwa, opis ) values
( 1, 'Język angielski, stopień 1'),
( 2, 'Język angielski, stopień 2'),
( 3, 'Język angielski, stopień 3'), 
( 4, 'Język angielski, stopień 4'),
( 5, 'Język angielski, stopień 5'),
( 6, 'Język niemiecki, stopień 1'),
( 7, 'Język niemiecki, stopień 2'),
( 8, 'Język niemiecki, stopień 3'),
( 9, 'Język niemiecki, stopień 4'),
(10, 'Język hiszpański, stopień 1'),
(11, 'Język hiszpański, stopień 2'),
(12, 'Język hiszpański, stopień 3') ;
--
-- wstawienie danych - tabela wykladowca
insert into wykladowca ( id_wykladowca, imie, nazwisko ) values 
( 1, 'Marcin','Szymczak'),
( 2, 'Joanna','Baranowska'),
( 3, 'Maciej','Szczepański'),
( 4, 'Czesław','Wróbel'),
( 5, 'Grażyna','Górska'),
( 6, 'Wanda','Krawczyk'),
( 7, 'Renata','Urbańska'),
( 8, 'Wiesława','Tomaszewska'),
( 9, 'Bożena','Baranowska'),
(10, 'Ewelina','Malinowska'),
(11, 'Anna','Krajewska'),
(12, 'Mieczysław','Zając'),
(13, 'Wiesław','Przybylski'),
(14, 'Dorota','Tomaszewska'),
(15, 'Jerzy','Wróblewski') ;
--
-- wstawienie danych - tabela kurs
insert into kurs ( id_kurs, id_grupa, id_nazwa, termin ) values
( 1, 1, 1, '1.01.2017-31.03.2017'),
( 2, 2, 1, '1.01.2017-31.03.2017'),
( 3, 1, 2, '1.04.2017-30.06.2017'),
( 4, 1, 3, '1.08.2017-10.10.2017'),
( 5, 1, 4, '1.11.2017-23.12.2017'),
( 6, 1, 6, '1.01.2017-31.03.2017'),
( 7, 2, 6, '1.01.2017-31.03.2017'),
( 8, 1, 7, '1.04.2017-30.06.2017'),
( 9, 1, 8, '1.07.2017-31.07.2017'),
(10, 1, 10, '1.02.2017-31.05.2017'),
(11, 1, 11, '1.09.2017-30.11.2017') ; 
--
-- wstawienie danych - tabela wykl_kurs - wykladowcy na kursach
insert into wykl_kurs ( id_kurs, id_wykl ) values
( 1, 1 ),
( 2, 2 ),
( 3, 1 ),
( 4, 1 ),
( 5, 3 ),
( 6, 4 ),
( 7, 5 ),
( 8, 4 ),
( 9, 4 ),
(10, 11 ),
(11, 11 ) ; 
--
-- wstawienie danych - tabela uczest_kurs - uczestnicy na kursach
insert into uczest_kurs ( id_kurs, id_uczest ) values
-- kurs 1 - angielski 1 gr 1
( 1, 1 ),
( 1, 3 ),
( 1, 5 ),
( 1, 7 ),
( 1, 8 ),
( 1, 10 ),
( 1, 11 ),
( 1, 12 ),
-- kurs 2 - angielski 1 gr 2
( 2, 2 ),
( 2, 16 ),
( 2, 17 ),
( 2, 18 ),
( 2, 20 ),
-- kurs 3 - angielski 2 gr 1
( 3, 1 ),
( 3, 2 ),
( 3, 3 ),
( 3, 5 ),
( 3, 7 ),
( 3, 17 ),
( 3, 18 ),
( 3, 20 ),
-- kurs 4 - angielski 3 gr 1
( 4, 1 ),
( 4, 2 ),
( 4, 3 ),
( 4, 5 ),
( 4, 21 ),
( 4, 22 ),
( 4, 25 ),
-- kurs 5 - angielski 4 gr 1
( 5, 1 ),
( 5, 2 ),
( 5, 3 ),
( 5, 5 ),
( 5, 21 ),
( 5, 22 ),
-- kurs 6 - niemiecki 1 gr 1
( 6, 8 ),
( 6, 9 ),
( 6, 13 ),
( 6, 15 ),
( 6, 19 ),
( 6, 24 ),
( 6, 27 ),
-- kurs 7 - niemiecki 1 gr 2
( 7, 11 ),
( 7, 17 ),
( 7, 18 ),
( 7, 23 ),
( 7, 25 ),
( 7, 28 ),
( 7, 30 ),
-- kurs 8 - niemiecki 2 gr 1
( 8, 8 ),
( 8, 9 ),
( 8, 13 ),
( 8, 15 ),
( 8, 19 ),
( 8, 24 ),
( 8, 27 ),
-- kurs 9 - niemiecki 3 gr 1
( 9, 8 ),
( 9, 9 ),
( 9, 13 ),
( 9, 24 ),
( 9, 27 ),
-- kurs 10 - hiszpanski 1 gr 1
(10, 6 ),
(10, 16 ),
(10, 18 ),
(10, 22 ),
(10, 24 ),
(10, 29 ),
(10, 30 ),
-- kurs 11 - hiszpanski 2 gr 1
(11, 6 ),
(11, 16 ),
(11, 18 ),
(11, 22 ),
(11, 24 ),
(11, 29 ),
(11, 30 ) ;

Operator algebry relacyjnej - iloczyn kartezjański

Iloczyn relacji ( iloczyn kartezjąnski ) - daje relację składającą się ze wszystkich możliwych krotek, będących kombinacjami dwóch krotek, po jednej z każdej wskazanej relacji. Iloczyn ten nazywamy też złączeniem krzyżowym.

SELECT * FROM Relacja1, Relacja2 ;

Jeżeli pomiędzy relacja istneje powiązanie 1-1, 1-N możemy wykorzytując klucz złączenia zrelizować na nim selekcję rekordów, które spełniają wymaganie zgodności klucza kandydującego i klucza obcego. Sprawa się komplikuje przy powiązaniu N-M, gdyż wówczas należy wykorzystać trzy tabele w złaczeniu i dwie pary kluczy.

SELECT * FROM Relacja1, Relacja2 WHERE Relacja1.KK = Relacja2.KO ;

Przykłady złączenia tabel w bazie KURS.

Złączenie relacji Kurs i Kurs_opis. Pomiędzy relacjami istnieje powiązanie 1-N na atrybutach Kurs_opis.id_kurs (KK) i Kurs.id_kurs_nazwa (FK).
SELECT Kurs.id_kurs, Kurs.id_grupa, Kurs_opis.opis
FROM Kurs, Kurs_opis
WHERE Kurs.id_nazwa = Kurs_opis.id_nazwa ;
Złączenie relacji Kurs i Uczestnik. Pomiędzy relacjami istnieje powiązanie N-M. Do realizacji powiązania wykorzystujemy relację uczest_kurs. Powiązanie relacji realizujemy wykorzystując zależności atrybutów. Relacje Uczestnik i uczest_kurs łączymy na atrybutach: Uczestnik.id_uczestnik i uczest_kurs.id_uczest, a relacje Kurs i uczest_kurs łączymy na atrybutach: Kurs.id_kurs i uczest_kurs.id_kurs. 
SELECT Uczestnik.nazwisko, Uczestnik.imie, Kurs.id_kurs
FROM Kurs, uczest_kurs, Uczestnik
WHERE Kurs.id_kurs = uczest_kurs.id_kurs and  
      Uczestnik.id_uczestnik = uczest_kurs.id_uczest ;
Ostatni przykład przedstawia otrzymanie relacji zawierającej imię, nazwisko i nazwę kursu na którym jest uczestnik. Wymaga to połączenia czterech relacji: Uczestnik, uczest_kurs, Kurs i Kurs_opis. Rozwiązanie jest kompilacją poprzednich dwóch przykładów. 
SELECT Uczestnik.nazwisko, Uczestnik.imie, Kurs_opis.opis, Kurs.id_kurs, Kurs.id_grupa
FROM Kurs, uczest_kurs, Uczestnik, Kurs_opis
WHERE Kurs.id_kurs = uczest_kurs.id_kurs and  
      Uczestnik.id_uczestnik = uczest_kurs.id_uczest and
      Kurs.id_nazwa = Kurs_opis.id_nazwa ;

Aliasy w poleceniach SQL.

W ramach polecenia SQL można wykorzystać aliasy dla nazw atrybutów i nazw relacji. Wykorzystanie aliasów umożliwia wprowadzenie dodatkowej warstwy abstrakcji (zmiana nazw atrybutów czy relacji w wynikowej relacji) lub nazwanie atrybutów, które są wyliczane, przetwarzane przy pomocy funkcji czy na koniec upraszczenie polecenia SQL. W przypadku atrybutów wykorzystujemy słowo kluczowe AS, natomiast w przypadku relacji alias umieszczamy bezpośrednio po nazwie relacji. Przykładowe wykorzystanie aliasów przedstawiono poniżej.

SELECT u.nazwisko, u.imie, ko.opis, k.id_kurs, k.id_grupa
FROM Kurs k, uczest_kurs uk, Uczestnik u, Kurs_opis ko
WHERE k.id_kurs = uk.id_kurs and  
      u.id_uczestnik = uk.id_uczest and
      k.id_nazwa = ko.id_nazwa ;