Miesięczne archiwum: październik 2017

Przykładowa baza danych – Biblioteka

W tym wpisie zaprezentuję Wam przykładową bazę danych biblioteki. Oczywiście jest to moja wizja bazy "Biblioteki", ale mam nadzieję, że się przyda.

Baza danych: BIBLIOTEKA
Środowisko: SQL SERVER


SCHEMAT

przykład bazy danych biblioteka database example library


TABELE

W moim projekcie zastosowałem 8 tabel:

1. Kategorie – zawiera kategorie książek
2. Książki – zawiera listę książek w bibliotece
3. Autorzy – zawiera listę autorów książek
4. Wydawnictwa  – zawiera listę wydawnictw które wydają książki
5. Czytelnicy – zawiera listę czytelników biblioteki
6. Pracownicy – zawiera listę pracowników biblioteki
7. Role – zawiera listę stanowisk pracowników w bibliotece
8. Wypożyczenia – zawiera wykaz wypożyczeń książek z biblioteki


TABELA – KATEGORIE

Ma służyc do przechowywania kategorii książek.

POLA
id_kategoria
– pole id_kategoria będzie kluczem głównym w tabeli
nazwa – nazwa kategorii

SKRYPT

CREATE TABLE Kategorie
    (
         id_kategoria INT PRIMARY KEY
        ,nazwa NVARCHAR(46)
    );


TABELA – AUTORZY

Służy do przechowywania informacji o autorach książek.

POLA
id_autor
– pole id_autor będzie kluczem głównym w tabeli
imie – wiadomo imię autora
nazwisko – tutaj także chyba nie wymaga komentarza

SKRYPT

CREATE TABLE Autorzy
    (
         id_autor INT  PRIMARY KEY
        ,imie NVARCHAR(15)
        ,nazwisko NVARCHAR(30)
    );


TABELA – WYDAWNICTWA

Służy do przechowywania informacji o wydawnictwach wydających książki.

POLA
id_wydawnictwo
– pole id_wydawnictwo będzie kluczem głównym w tabeli
nazwa – nazwa wydawnictwa
Można oczywiście rozszerzyć zakres danych w tej tabeli np. o: adres, miasto, kod-pocztowy, telefon, email, strona_www itp. Pozostawiam to do Waszej oceny.

SKRYPT

CREATE TABLE Wydawnictwa
    (
         id_wydawnictwo INT  PRIMARY KEY
        ,nazwa NVARCHAR(30)
    );


TABELA – KSIĄŻKI

To główna tabela bazy. Tutaj będziemy przechowywali inforamcje o książkach. W tabeli będziemy mieli także kilka kluczy obcych do tabel: Kategorie, Autorzy, Wydawnictwa.

POLA
id_ksiazka
– to klucz głowny naszej tabeli
isbn – numer ISBN to unikalny numer każdej książki
id_kategoria – numer id kategorii z tabeli Kategorie
tytul  – tytuł książki
opis – opis książki
id_autor – numer id autora z tabeli Autorzy
id_wydawnictwo  – numer id wydawnictwa z tabeli Wydawnictwa
rok_wydania – rok wydania książki
jako klucz główny można było uznać kolumnę ISBN bo to unikalny numer książki ale dla przejżystości postanowiłem dodać jednak kolumnę "id_ksiazka"

SKRYPT

CREATE TABLE Ksiazki
    (
         id_ksiazka INT PRIMARY KEY
        ,isbn NVARCHAR(13)
        ,id_kategoria INT CONSTRAINT FK_kategoria REFERENCES Kategorie(id_kategoria)
        ,tytul NVARCHAR(255)
        ,opis NVARCHAR(500)
        ,id_autor INT CONSTRAINT FK_autor REFERENCES Autorzy(id_autor)
        ,id_wydawnictwo INT CONSTRAINT FK_wydawnictwo REFERENCES Wydawnictwa(id_wydawnictwo)
        ,rok_wydania INT
    );   


TABELA – CZYTELNICY

Tabela czytelnicy będzie przechowywała informacje o naszych czytelnikach.

POLA
id_czytelnik
– klucz główny w tabeli czytelnicy
login – login czytelnika
haslo – hasło czytelnika
email – e-mail czytelnika
telefon – telefon czytelnika
data_urodzenia – data urodzenia czytelnika
Ktoś zapyta dlaczego nie będziemy przechowywali imienia i nazwiska czytelników. Można to zrobić. Jednak jeżeli chcielibyście stworzyć taką bazę w rzeczywistości i przechowywać w niej takie dane musicie wiedzieć, że musicie zgłosić ten zbiór do GIODO.

SKRYPT

CREATE TABLE Czytelnicy
    (
         id_czytelnik INT PRIMARY KEY
        ,login NVARCHAR(10)
        ,haslo NVARCHAR(20)
        ,email NVARCHAR(30)
        ,telefon NVARCHAR(20)
        ,data_urodzenia DATE
    );


TABELA – ROLE

Tutaj będziemy przechowywali informacje nt. stanowisk pracowników w bibliotece. W sumie jest to tabela opcjonalna, nie jest wymagana.

POLA
id_rola
– klucz głowny w tabeli
nazwa – nazwa stanowiska/roli w bibliotece
 

SKRYPT

CREATE TABLE Role
    (
         id_rola INT PRIMARY KEY
        ,nazwa NVARCHAR(25)
    );


TABELA – PRACOWNICY

Tutaj będziemy przechowywali informację pracowników naszej biblioteki.

POLA
id_pracownik
– klucz główny w tabeli
login – login pracownika
haslo – hasło pracownika
id_rola – numer id roli z tabeli "Role"
 

SKRYPT

CREATE TABLE Pracownicy
    (
         id_pracownik INT PRIMARY KEY
        ,login NVARCHAR(10)
        ,haslo NVARCHAR(20)
        ,id_rola INT CONSTRAINT FK_rola REFERENCES Role(id_rola)
    );   


TABELA – WYPOŻYCZENIA

To kolejna ważna tabela w naszej bazie. Tutaj będziemy przechowywali informacje nt. wypożyczen książek z naszej biblioteki.

POLA
id_wypozyczenie
– klucz głowny w naszej tabeli
id_czytelnik – numer id czytelnika z tabeli "Czytelnicy" który wypożyczył książkę
id_ksiazka – numer id wypożyczonej książki (numer id książki z tabeli książki)
data_wypozyczenia – data wypożyczenia książki
id_pracownik_wypozyczenie – numer id pracownika który wypożyczył książkę
data_oddania – data oddania książki przez czytelnika
id_pracownik_oddanie – numer id pracownika który przyjął książkę po wypożyczeniu
 

SKRYPT

CREATE TABLE Wypozyczenia
    (
         id_wypozyczenie BIGINT PRIMARY KEY
        ,id_czytelnik INT CONSTRAINT FK_czytelnik REFERENCES Czytelnicy(id_czytelnik)
        ,id_ksiazka INT CONSTRAINT FK_ksiazka REFERENCES Ksiazki(id_ksiazka)
        ,data_wypozyczenia DATE
        ,id_pracownik_wypozyczenie INT CONSTRAINT FK_pracownik_wypozyczenie REFERENCES Pracownicy(id_pracownik)
        ,data_oddania DATE
        ,id_pracownik_oddanie INT CONSTRAINT FK_pracownik_oddanie REFERENCES Pracownicy(id_pracownik)
    );


CAŁY SKRYPT TWORZĄCY STRUKTURĘ BAZY "BIBLIOTEKA" ZNAJDZIESZ >>>TUTAJ<<< A SKTYPT TWORZĄCY STRUKTURĘ BAZY + PRZYKŁADOWE DANE ZNAJDZIESZ >>>TUTAJ<<<

UWAGA

Musimy jednak pamiętać, że jeżeli połączymy wszystkie poszczególne części kodu w jeden skrypt tworzący bazę, to niektóre tabele musimy umieścić wcześniej w kodzie, dlaczego? Jeżeli w kodzie danej tabeli odwołujemy się (klucz obcy) do drugiej tabeli a jej jeszcze nie ma utworzonej, to kompilator zwróci nam błąd. Np. tabela 'Książki'. Jeżeli będziemy próbowali utworzyć tabelę 'Książki', w której mamy zdefiniowanych kilka kluczy obcych np. 'id_kategoria' , ' id_autor' , 'id_wydawnictwo', jeszcze przez utworzeniem tych tabel ('Kategorie' , 'Autorzy' , 'Wydawnictwa), to przy próbie jej utworzenia zobaczymy błąd:

Foreign key 'FK_kategoria' references invalid table 'Kategorie'.
Could not create constraint. See previous errors.


  SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka

PostgreSQL position – szukamy pozycji podłańcucha w łańcuchu znaków

Za pomocą funkcji POSITION możemy wyszukać pozycję (tam gdzie się rozpoczyna) szukanego łańcucha znaków (zdefiniowanego przez nas) w głównym łąńcuchu. Funkcja zwróci jego pozycję początkową.
Zwracany typ: INT

PostgreSQL POSITION() składnia

SUBSTRING( 'szukany_łańcuch' in 'łańcuch główny' )

Poniże gotowe zapytanie i efekt jego uruchomienia

SELECT
	position('kot' in 'Ala ma kota')

 

postgresql funkcja position szukamy podłańcucha w łańcuchu znaków

 


postgresqlpostgresql

PostgreSQL substring + wyrażenia regularne, wycięcie podłańcucha z łańcucha znaków

Za pomoc funkcji SUBSTRING() możemy "wyciąć" podłańcuch z łańcucha głównego który jest zgody z utworzonym przez nas wyrażeniem regularnym (wyrażenia regularne opiszę w całkiem nowym miejscu bo to jest temat na osobny wpis). Także w tym przypadku funkcja zwróci nam "wycięty" (pasujcy do wzorca) ciąg znaków.
Zwracany typ: TEXT

PostgreSQL SUBSTRING() składnia

SUBSTRING( 'łańcuch główny' from 'wzorzec_wyrażenia_regularnego' )

 

Poniże gotowe zapytanie i efekt jego uruchomienia

SELECT
	substring('Ala ma kota' from '..t')

W wyrażeniu regularnym definiowaliśmy, że szukamy ciągu znaków który będzie się składał z dwóch znaków (obojętnie jakich) i litery "t". Efekt uruchomienia poniżej.

 

postgresql funkcja substring wyrażenia regularne POSIX

Sprzewdźmy jeszcze czy dla funkcji substring() wielkość liter ma znaczenie. W tym celu nasz głowny łańcuch znaków przerobimy na różnej wielkości litery.

SELECT
	substring('AlA Ma KoTa' from '..t')

Uruchamiamy

postgresql funkcja substring wyrażenia regularne POSIX

Widzimy, że nie został zwrócony żaden wynik więc dla funkcji substring() wielkość liter ma znaczenie.


postgresqlpostgresqlpostgresql

PostgreSQL substring() wycięcie podłańcucha z łańcucha znaków

Funkcją SUBSTRING() możemy "wyciąć" jakiś podłańcuch znaków z naszego głównego łańcucha znaków. Funkcja ta posiada dwa parametry. W pierwszym wskazujemy od którego miejsca w łańcuchu głównym chcemy zacząć "ciąć" a w drugim ile znaków chcemy "wyciąć". Funkcja zwróci nam "wycięty" ciąg znaków.
Zwracany typ: TEXT

PostgreSQL SUBSTRING() składnia

SUBSTRING( 'łańcuch główny' from parametr_1 for parametr_2 )

parametr_1  – miejsce od którego (liczymy od lewej) chcemy zacząć "wycinać" nasz podłańcuch z łańcucha głównego
parametr_2  – ile znaków chcemy "wyciąć"

Poniże gotowe zapytanie i efekt jego uruchomienia

 

SELECT
	substring('AlA Ma KoTa')

 

postgresql funkcja substring wycinanie podłańcucha z łańcucha znaków

 


postgresqlpostgresqlpostgresql

PostgreSQL char_length() character_length() liczba znaków w łańcuchu

Kolejną funkcją którą poznamy to funkcja CHAR_LEHGTH(). Jej dłuższa nazwa to CHARACTER_LENGTH(). Za jej pomocą możeby zliczyć liczbę znaków w łańcuchu znaków. Poniżej przykład z wykorzystaniem tej funkcji.
Zwracany typ: INT

SELECT
	CHAR_LENGTH('Ala ma kota')

Poniżej zapytanie i efekt wykonania zapytania.

postgresql funkcja char_length character_length ilość znaków w łańcuchu

Efekt uruchomienia zapytania.

postgresql funkcja char_length character_length ilość znaków w łańcuchu


postgresqlpostgresqlpostgresql