Sop 07 lab

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

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

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

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

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

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

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

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