Systemy Baz Danych – laboratorium
WSTI
___________________________________________________________________________
Systemy Baz Danych – Laboratorium S1
laboratorium
załoŜenia
Tworzenie relacyjnego modelu bazy danych – koncepcja
NaleŜy zaprojektować model bazy danych wspomagający działanie Stowarzyszenia Miłośników Filmu (SMFBazaDanych).
Sekretariat stowarzyszenia prowadzi ewidencję swoich członków na terenia Polski, jak równieŜ ewidencję filmów, które mogą być wyświetlane na spotkaniach klubowych.
Spotkania te mogą odbywać się w róŜnych miastach, a daty tych spotkań nie zazębiają się, przez co członkowie stowarzyszenia mogą teoretycznie uczestniczyć w kaŜdym. Na kaŜdym spotkaniu odnotowywana jest frekwencja uczestników oraz wyświetlany film (tylko jeden).
Proponowany model relacyjnej bazy danych moŜe składać się z następujących tabel: 1. SzczegoloweDaneCzlonkow – dane osobowe członków stowarzyszenia, 2. Filmy – ewidencja filmów,
3. KategorieFilmow – słownik kategorii filmów,
4. Lokalizacja – adresy klubów, gdzie odbyły się spotkania, 5. Spotkanie – ewidencja spotkania klubowego,
6. Frekwencja – ewidencja uczestnictwa członków w spotkaniach.
Struktura poszczególnych tabel moŜe zawierać poniŜsze dane:
1. SzczegoloweDaneCzlonkow
IdentyfikatorCzlonka - klucz główny
Imie
Nazwisko
DataUrodzenia
Ulica
Miasto
Wojewodztwo
KodPocztowy
2. Filmy
IdentyfikatorFilmu – klucz główny
TytulFilmu
RokPremiery
StreszczenieFabuly
DostepnyNaDVD
Ocena
IdentyfikatorKategorii – klucz obcy z tablicy KategorieFilmow
3. KategorieFilmow
IdentyfikatorKategorii – klucz główny
Kategoria
___________________________________________________________________________
opr. Józef Woźniak
1
Systemy Baz Danych – laboratorium
WSTI
___________________________________________________________________________
4. Lokalizacja
IdentyfikatorLokalizacji – klucz główny
Ulica
Miasto
Województwo
KodPocztowy
Telefon
5. Spotkanie
DataSpotkania – klucz główny
IdentyfikatorLokalizacji – klucz obcy z tablicy Lokalizacja
IdentyfikatorFilmu – klucz obcy z tablicy Filmy
KosztyOrganizacji
6. Frekwencja
DataSpotkania – klucz główny oraz klucz obcy z tablicy Spotkanie IdentyfikatorCzlonka – klucz główny oraz klucz obcy z tablicy
SzczegoloweDaneCzlonkow
Klucz główny (Primary Key) – jedna lub kilka kolumn w tabeli, na podstawie których system zarządzania baza danych kontroluje dane podczas ich wprowadzania. W tabeli nie moŜna zapisać dwóch rekordów (wierszy) mających takie same wartości w kolumnach klucza głównego.
Przykładowo w tabeli Filmy nie moŜna wprowadzić dwóch filmów mających ten sam IdentyfikatorFilmu. Ale moŜna wprowadzić dwa filmy o tym samym tytule, lecz róŜniące się IdentyfikatoremFilmu. Klucz główny zapewnia, Ŝe w tabeli nie ma dwóch takich samych wierszy (zapewnia niepowtarzalność danych w tabeli).
Klucz obcy (Foreign Key) – zapewnia relację między dwoma tabelami. Dzięki niemu zapewniona jest integralność danych. Przykładowo w tabeli KategorieFilmow są trzy rekordy: (1, Przygodowy), (2, Thriller) i (3, Historyczny). Wprowadzając do tabeli Filmy film „Miś”, nie będzie moŜna w polu IdentyfikatorKategorii wprowadzić wartości 4, gdyŜ nie występuje ona w tabeli KategorieFilmow. Najpierw trzeba wprowadzić dane do KategorieFilmow w postaci (4, Komedie), a potem film do tabeli Filmy.
Graficzna prezentacja moŜe być przedstawiona tak, jak na rysunku poniŜej.
Struktura kaŜdej tabeli jest przedstawiona jako osobny prostokąt z wyspecyfikowanymi nazwami kolumn (pól). Wytłuszczona nazwa kolumny (kolumn) oznacza, Ŝe jest to klucz główny tej tabeli. Zwróć uwagę na tabelę Frekwencja, w której obie kolumny wchodzą w skład klucza głównego. Mówimy wówczas o złoŜonym kluczu głównym.
___________________________________________________________________________
opr. Józef Woźniak
2
Systemy Baz Danych – laboratorium
WSTI
___________________________________________________________________________
W przypadku tabeli Lokalizacja system zarządzania bazą danych nie dopuści do wprowadzenia dwóch rekordów (wierszy) o tej samej wartości pola IdentyfikatorLokalizacji.
Natomiast w przypadku tabeli Frekwencja (odnotowującej obecność konkretnego członka klubu na konkretnym spotkaniu) jest moŜliwy zapis wierszy (‘2006-09-25’,1) i (‘2006-09-25’, 2), gdyŜ to są róŜne wartości klucza głównego.
Na powyŜszym schemacie klucz obcy jest prezentowany przez literę F, a klucz główny przez literę P. Zwróć uwagę na to, Ŝe kluczem obcym w jednej tabeli jest klucz główny z drugiej.
Przykładowo klucz obcy tabeli Filmy (IdentyfikatorKategorii) jest kluczem głównym tabeli KategorieFilmow.
Implementacja modelu w języku Sybase SQL
PowyŜszy model bazy danych moŜna zaimplementować pisząc odpowiedni skrypt składający się ze zdań create table.
PoniŜej został zaprezentowany kompletny skrypt pozwalający załoŜyć wszystkie tabele modelu i relacje między nimi.
create table SzczegoloweDaneCzlonkow
(
IdentyfikatorCzlonka integer Primary Key,
Imie varchar(50) not null,
Nazwisko varchar(50) not null,
DataUrodzenia date,
Ulica varchar(100) not null,
Miasto varchar(75) not null,
Wojewodztwo varchar(75) not null,
KodPocztowy varchar(6) not null,
Email varchar(200),
DataPrzystapienia date not null
);
___________________________________________________________________________
opr. Józef Woźniak
3
Systemy Baz Danych – laboratorium
WSTI
___________________________________________________________________________
create table KategorieFilmow
(
IdentyfikatorKategorii integer Primary Key,
Kategoria varchar(100) not null
);
create table Filmy
(
IdentyfikatorFilmu integer,
TytulFilmu varchar(100) not null,
RokPremiery integer,
StreszczenieFabuly varchar(2000),
DostepnyNaDVD varchar(1) not null,
Ocena integer,
IdentyfikatorKategorii integer not null,
Primary Key (IdentyfikatorFilmu),
Foreign Key (IdentyfikatorKategorii)
References KategorieFilmow (IdentyfikatorKategorii)
);
create table Lokalizacja
(
IdentyfikatorLokalizacji integer Primary Key,
Ulica varchar(100) not null,
Miasto varchar(75) not null,
Wojewodztwo varchar(75) not null,
KodPocztowy varchar(6) not null,
Telefon varchar(20)
);
create table Spotkanie
(
DataSpotkania date,
IdentyfikatorLokalizacji integer not null,
IdentyfikatorFilmu integer not null,
KosztOrganizacji numeric not null,
Primary Key (DataSpotkania),
Foreign Key (IdentyfikatorLokalizacji)
References Lokalizacja (IdentyfikatorLokalizacji),
Foreign Key (IdentyfikatorFilmu)
References Filmy (IdentyfikatorFilmu)
);
create table Frekwencja
(
DataSpotkania date,
IdentyfikatorCzlonka integer,
Primary Key (DataSpotkania, IdentyfikatorCzlonka),
___________________________________________________________________________
opr. Józef Woźniak
4
Systemy Baz Danych – laboratorium
WSTI
___________________________________________________________________________
Foreign Key (DataSpotkania)
References Spotkanie (DataSpotkania),
Foreign Key (IdentyfikatorCzlonka)
References SzczegoloweDaneCzlonkow (IdentyfikatorCzlonka)
);
Uwagi:
1. NaleŜy zwrócić uwagę na dwa sposoby definiowania klucza głównego (Primary Key) np. definiowanie klucza głównego w tabelach Filmy i Lokalizacja.
2. Jedynym sposobem definiowania klucza złoŜonego jest definicja zaprezentowania na przykładzie tabeli Frekwencja.
3. Przy definiowaniu klucza obcego zastosowano w powyŜszym modelu zasadę tych samych nazw kolumn korespondujących ze sobą przy tworzeniu tego klucza.
Np. w tabelach KategorieFilmow i Filmy występują te same nazwy –
IdentyfikatorKategorii. To nie jest obligatoryjne. MoŜna tworząc tabele Filmy zmienić nazwę kolumny IdentyfikatorKategorii na RodzajFilmu (nie zmieniając konstrukcji tabeli KategorieFilmow). Wtedy odpowiedni fragment struktury tabeli Filmy będzie jak poniŜej:
create table Filmy
…………………………
RodzajFilmu integer,
…………………………
Foreign Key (RodzajFilmu)
References KategorieFilmow (IdentyfikatorKategorii)
…………………………
Modyfikacja modelu (struktur tabel)
Język SQL posiada odpowiednie konstrukcje zdaniowe do modyfikacji juŜ zaprojektowanego i zaimplementowanego modelu danych. Modyfikacje te mogą polegać na usuwaniu, zmianie nazwy, dodawaniu kolumn, zmianie typów danych istniejących kolumn, jak równieŜ
usuwaniu całych tabel.
PoniŜej przedstawione zostały na przykładach podstawowe zdania języka SQL umoŜliwiające modyfikacje modelu danych.
Usunięcie kolumny z tabeli
alter table Filmy
delete StreszczenieFabuly;
Dodanie kolumny do tabeli
alter table SzczegoloweDaneCzlonkow
add DataPrzystapienia date;
Modyfikacja istniejącej kolumny w tabeli (typu danych)
alter table SzczegoloweDaneCzlonkow
modify Email varchar(30);
___________________________________________________________________________
opr. Józef Woźniak
5
Systemy Baz Danych – laboratorium
WSTI
___________________________________________________________________________
Zmiana nazwy kolumny w tabeli
alter table Frekwencja
rename DataSpotkania to DS;
Usunięcie tabeli z bazy danych
drop table Frekwencja
Wprowadzanie danych do tabel
Język SQL nie jest najwygodniejszym narzędziem do wprowadzania danych do tabel z uwagi na to, Ŝe przy pomocy jednego zdania insert into moŜna wprowadzić tylko jeden rekord (wiersz).
Jest kilka sposobów uŜycia tego zdania:
1. wprowadzanie danych w kolejności zgodnej ze strukturą danych
insert into KategorieFilmow
(IdentyfikatorKategorii, Kategoria)
values
(1, ‘Thriller’);
2. uproszczony wariant sposobu pierwszego
insert into KategorieFilmow
values
(1, ‘Thriller’);
3. wprowadzanie danych w kolejności niezgodnej ze strukturą danych
insert into KategorieFilmow
(Kategoria , IdentyfikatorKategorii)
values
(‘Thriller’, 1);
4. wprowadzanie niepełnych danych
a).
insert into Lokalizacja
( IdentyfikatorLokalizacji, Ulica, Miasto, KodPocztowy)
values
( 11, 'ul. Conrada', 'Warszawa', '01-922');
b).
insert into Lokalizacja
values
( 11, 'ul. Conrada', 'Warszawa', ' ', '01-922', ' ');
W tym ostatnim przypadku wprowadzane są dane nie do wszystkich kolumn tabeli (porównaj ze strukturą tabeli Lokalizacja). W wariancie 4a wyspecyfikowane są wybrane kolumny tabeli (kolejność dowolna) i odpowiadające im wartości. W wariancie 4b wyspecyfikowane są ___________________________________________________________________________
opr. Józef Woźniak
6
Systemy Baz Danych – laboratorium
WSTI
___________________________________________________________________________
(domyślnie) wszystkie kolumny tabeli, ale we frazie values brak danych reprezentowany jest przez pusty parametr (‘ ‘).
Drugim zdaniem, przy pomocy którego moŜna wprowadzić dane do tabeli jest load into …
from …… Jest to efektywniejszy sposób załadowania tabeli większą liczbą wierszy. Dane te muszą być odpowiednio przygotowane w postaci pliku tekstowego, w którym jeden rekord jest zapisany w jednej linii, a poszczególne pola oddzielone są przecinkami.
Jest to często spotykany sposób wymiany danych między róŜnymi systemami bazodanowymi.
MoŜna na przykład dokonać konwersji tabeli excelowej lub accesowej do postaci pliku tekstowego (pliku ASCI):
1,'Krystyna','Trzmiel-Jasek','K',1956,26,62
2,'Anna','Dobkowska','K',1951,27,41
3,'Anna','Sawicka','K',1985,21,222
4,'Ewa','Misiaczek','K',1968,23,3
5,'Barbara','Jabłońska','K',1977,21,179
6,'Wanda','Tkaczyk','K',1959,25,217
7,'Anna','Górnicka','K',1968,23,105
8,'Maria','Teichert','K',1964,24,3
9,'Agnieszka','Stańczak','K',1974,22,3
10,'Aneta','Gołąbek','K',1984,21,42
11,'Anna','Chmielowiec','K',1975,22,196
12,'Justyna','Sidorska','K',1982,21,3
13,'Jolanta','Kaczmarczyk','K',1982,21,196
14,'Magdalena','Gliwińska','K',1984,21,1
15,'Anna','Kowalewska','K',1978,21,1
16,'Barbara','Kasprzak','K',1960,25,3
a następnie uŜyć zdania SQL:
load into table Pracownicy from ‘c:\biuro\pracownicy.txt’
Efektem będzie wgranie do tabeli Pracownicy zawartości pliku pracownicy.txt. NaleŜy tylko zadbać o odpowiedniość struktury tabeli Pracownicy ze strukturą wgrywanego pliku.
Zadanie do samodzielnego wykonania:
Zaprojektować prosty model bazy danych składający się z trzech tabel i odzwierciedlający ewidencję studentów uczelni.
Tabela Sudent – zawierająca dane studenta,
Tabela KierunkiStudiow – zawierająca nazwy kierunków (Informatyka, Psychologia,…..), Tabela RodzajStudiow – zawierająca nazwy sposobów studiowania (Dzienne, Wieczorowe, Zaoczne, Eksternistyczne).
Zdefiniować klucze główne oraz klucze obce definiujące relacje między tabelami: Relacje: Student studiuje na określonym kierunku,
Student studiuje określonym sposobem studiowania.
___________________________________________________________________________
opr. Józef Woźniak
7
Systemy Baz Danych – laboratorium
WSTI
___________________________________________________________________________
Na tak zbudowanym modelu przećwiczyć wprowadzanie danych do tabel, ich modyfikacje oraz usuwanie na róŜne sposoby (zdania Insert, Update, Delete).
Przećwiczyć skuteczność działania klucza obcego w celu utrzymania spójności bazy danych (np. poprzez wprowadzenie do ewidencji studenta, który studiuje na kierunku, którego brak w ewidencji kierunków).
Zmodyfikować model bazy danych poprzez wprowadzenie do jednej z tabel nowej kolumny (np. do tabeli KierunkiStudiow kolumnę określającą datę uruchomienia danego kierunku) i uzupełnić tę kolumnę danymi (zrobić to na dwa sposoby: wszystkie wiersze na raz oraz wybiórczo).
Sprawdzić czy jest moŜliwość zmiany struktury tabeli poprzez zmianę wielkości wybranej kolumny w przypadku, gdy jest ona wypełniona (np. kolumna Nazwisko w tabeli Student).
NaleŜy zwiększyć rozmiar kolumny np. do 100 i zmniejszyć np. do 5.
___________________________________________________________________________
opr. Józef Woźniak
8