Wymagania do projektu i realizacji baz danych

Wymagania do projektu i
realizacji baz danych:
1
Projekt logiczny bazy danych:
Przykładowy diagram:
Biuro
BiuroNr
Ma
Personel
PersonelNr
Nadzoruje
Oferuje
Nieruchomość
Ogląda
NieruchomośćNr
Klient
KlientNr
Posiada
Wynajmuje
Wynajęty Przez
Właściciel prywatny
Wynajęcie
WłaścicielNr
WynajęcieNr
2
Fragment słownika danych przedstawiający opis atrybutów:
Nazwa zbioru
encji
Atrybuty
Typ danych i długość
Opis
Wartości
puste
Wielowartościowy
...
Biuro
biuroNr
miasto
ulica
kod
Jednoznacznie identyfikuje biuro
Nazwa miasta
Nazwa ulicy z numerem domu
Kod pocztowy lokalizacji
łańcuch, długość maks. 4
łańcuch, długość maks. 15
łańcuch, długość maks. 15
łańcuch, długość maks. 6
Nie
Nie
Nie
Tak
Nie
Nie
Nie
Nie
...
Personel
personelNr
imięNazwisko
imię
nazwisko
stanowisko
płeć
dataUr
pensja
biuroNr
Jednoznacznie identyfikuje pracownika
łańcuch, długość maks. 4
Nie
Nie
...
Imię pracownika
Nazwisko pracownika
Nazwa zajmowanego stanowiska
Płeć pracownika
Data urodzenia pracownika
Zarobki pracownika
Powiązanie z biurem w którym
pracownik pracuje
łańcuch, długość maks. 15
łańcuch, długość maks. 15
łańcuch, długość maks. 10
łańcuch, długość 1 (M lub K)
data
liczba, Integer bez znaku
łańcuch, długość maks. 4
Nie
Nie
Nie
Tak
Tak
Tak
Tak
Nie
Nie
Nie
Nie
Nie
Nie
Nie
nieruchomośćNr
łańcuch, długość maks. 4
Nie
Nie
miasto
ulica
kod
typ
pokoje
czynsz
wlascicielNr
biuroNr
personelNr
Jednoznacznie identyfikuje
nieruchomość do wynajęcia
Nazwa miasta
Nazwa ulicy z numerem domu
Kod pocztowy lokalizacji
Typ nieruchomości
Ilość pokoi w nieruchomości
Wysokość czynszu za wynajęcie
Powiązanie z właścicielem
Powiązanie z biurem
Powiązanie z pracownikiem
łańcuch, długość maks. 15
łańcuch, długość maks. 15
łańcuch, długość maks. 6
łańcuch, długość maks. 15
liczba, Integer bez znaku
liczba, Integer bez znaku
łańcuch, długość maks. 4
łańcuch, długość maks. 4
łańcuch, długość maks. 4
Nie
Nie
Nie
Nie
Nie
Tak
Tak
Tak
Tak
Nie
Nie
Nie
Nie
Nie
Nie
Nie
Nie
Nie 3
...
...
...
...
...
Nieruchomość
...
...
...
Klucze relacji:
Klucz główny - to jedna lub więcej kolumn tabeli, w których
wartości jednoznacznie identyfikują każdy wiersz w tabeli.
Każda relacja musi mieć klucz główny. Dzięki temu możemy
zapewnić, aby wiersze nie powtarzały się w relacji.
Klucz kandydujący to kolumna lub zbiór kolumn, które mogą
występować jako jednoznaczny identyfikator wierszy w tabeli. W
każdej relacji może istnieć wiele kluczy kandydujących.
Klucz główny jest wybierany ze zbioru kluczy kandydujących.
Klucz obcy - jest kolumną lub grupą kolumn tabeli, która czerpie
swoje wartości z tej samej dziedziny co klucz główny tabeli
powiązanej z nią w bazie danych.
4
Więzy integralności:
1. wymagana obecność danych (NOT NULL);
2. więzy dziedzin atrybutów (dopuszczalny zbiór wartości
atrybutu, dopuszczalny zakres długości i format atrybutu);
3. integralność encji: każda tabela musi posiadać klucz
główny, a wartości klucza głównego muszą być w ramach
tabeli unikalne i nie równe NULL;
4. integralność referencyjna: każda wartość klucza obcego
może być albo równa jakiejś wartości klucza głównego
występującej w tabeli powiązanej, lub (ewentualnie) NULL;
5. więzy ogólne (dodatkowe warunki poprawności danych
określone przez użytkowników lub administratorów bazy
danych).
5
Integralność referencyjna pociąga za sobą konieczność
określenia reguły postępowania w wypadku usuwania wiersza z
tabeli powiązanej, co mogłoby unieważnić niektóre wartości
kluczy obcych w tabelach do niej się odnoszących:
1. Ograniczone usuwanie (Restricted). Podejście ostrożne – nie
dopuszcza do usuwania rekordu nadrzędnego, jeśli istnieją
rekordy podrzędne.
2. Kaskadowe usuwanie (Cascades). Podejście ufne – przy
usuwaniu rekordu nadrzędnego usuwa także rekordy
podrzędne.
3. Izolowane usuwanie (Isolated). Podejście wyważone – usuwa
jedynie rekord nadrzędny, nieważne wartości kluczy obcych
ulegają zastąpieniu przez NULL.
6
Schemat relacyjnej bazy danych
(Biuro_nieruchomości):
Relacyjna baza danych składa się z pewnej liczby znormalizowanych
relacji, np.:
Biuro
(biuroNr, ulica, miasto, kod pocztowy)
Personel
(pracownikNr, imię, nazwisko, stanowisko, płeć,
dataUr, pensja, biuroNr)
Nieruchomość (nieruchomośćNr, ulica, miasto, kod pocztowy, typ,
pokoje, czynsz, właścicielNr, pracownikNr, biuroNr)
Klient
(klientNr, imię, nazwisko, adres, telefon, preferencje,
maksCzynsz)
Właściciel
(właścicielNr, imię, nazwisko, adres, telefon)
Wizyta
(klientNr, nieruchomośćNr, dataWizyty, uwagi)
Rejestracja
(klientNr, biuroNr, pracownikNr, dataRejestracji)
Wynajęcie
(umowaNr, nieruchomośćNr, klientNr, czynsz,
formaPłatności, kaucja, zapłacona, od, do, okres)
7
mysql> CREATE DATABASE IF NOT EXISTS biuro DEFAULT
CHARACTER SET cp1250 DEFAULT COLLATE cp1250_polish_ci;
Query OK, 1 row affected (0.02 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| biuro
|
| test
|
+--------------------+
4 rows in set (0.00 sec)
mysql>
8
Wyświetlanie zapisu tworzenia bazy:
mysql> SHOW CREATE DATABASE biuro;
+----------+---------------------------------------------+
| Database | Create Database
|
+----------+---------------------------------------------+
| biuro
| CREATE DATABASE `biuro` /*!40100 DEFAULT
CHARACTER SET cp1250 COLLATE cp1250_polish_ci */ |
+----------+---------------------------------------------+
1 row in set (0.00 sec)
Inny sposób prezentacji wyniku:
mysql> SHOW CREATE DATABASE biuro\G
************************* 1. row *************************
Database: biuro
Create Database: CREATE DATABASE `biuro` /*!40100 DEFAULT
CHARACTER SET cp1250 COLLATE cp1250_polish_ci */
1 row in set (0.01 sec)
mysql>
9
Wprowadzanie poleceń z pliku :
Plik z odpowiednimi poleceniami zapisujemy w katalogu bin
naszego MySQL'a np. pod nazwą biuro.sql.
Uruchamiamy komendą:
SOURCE biuro.sql
Jeżeli plik zapisaliśmy w innym miejscu niż katalog bin np. na
pulpicie komenda wtedy wygląda np. tak:
SOURCE c:\windows\pulpit\biuro.sql
10
Przykładowa zawartość:
CREATE DATABASE IF NOT EXISTS Biuro DEFAULT CHARACTER
SET utf8 DEFAULT COLLATE utf8_polish_ci;
CONNECT biuro;
CREATE TABLE IF NOT EXISTs biuro (
biuroNr varchar(4) NOT NULL,
ulica varchar(25) NOT NULL,
miasto varchar(25) NOT NULL,
kod varchar(6) NOT NULL,
PRIMARY KEY (biuroNr)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
INSERT INTO biuro (biuroNr,ulica,miasto,kod) VALUES
('B001','Piękna 46','Białystok','15-900');
INSERT INTO biuro VALUES ('B002','Cicha 56',‘Łomża',
'18-400');
11
mysql> source biuro.sql
Query OK, 1 row affected (0.02 sec)
Connection id:
1
Current database: biuro
Query OK, 0 rows affected (0.06 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.02 sec)
mysql>
12
Wyświetlenie zawartości tabeli:
mysql> SELECT * FROM biuro;
+---------+-----------+-----------+--------+
| biuroNr | ulica
| miasto
| kod
|
+---------+-----------+-----------+--------+
| B001
| Piękna 46 | Białystok | 15-900 |
| B002
| Cicha 56 | Łomża
| 18-400 |
+---------+-----------+-----------+--------+
2 rows in set (0.00 sec)
13
mysql> INSERT INTO biuro VALUES
('B003','Mała 63','Białystok','15-900'),
('B004','Miodowa 32','Grajewo','19-300'),
('B005','Dobra 22',‘Łomża','18-400'),
('B006','Słoneczna 55','Białystok','15-900'),
('B007','Akacjowa 16','Augustów','16-300');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM biuro;
+---------+---------------+------------+--------+
| biuroNr | ulica
| miasto
| kod
|
+---------+---------------+------------+--------+
| B001
| Piękna 46
| Białystok | 15-900 |
| B002
| Cicha 56
| Łomża
| 18-400 |
| B003
| Mała 63
| Białystok | 15-900 |
| B004
| Miodowa 32
| Grajewo
| 19-300 |
| B005
| Dobra 22
| Łomża
| 18-400 |
| B006
| Słoneczna 55 | Białystok | 15-900 |
| B007
| Akacjowa 16
| Augustów
| 16-300 |
+---------+---------------+------------+--------+
7 rows in set (0.00 sec)
14
Tworzymy drugą powiązaną kluczem obcym tabelę:
mysql>
->
->
->
->
->
->
->
->
->
->
->
CREATE TABLE IF NOT EXISTS personel (
personelNr varchar(4) NOT NULL,
imie varchar(25) NOT NULL,
nazwisko varchar(25) NOT NULL,
stanowisko varchar(25) NOT NULL,
plec enum('K','M') NOT NULL,
dataUr date NOT NULL,
pensja smallint(4) unsigned NOT NULL,
biuroNr varchar(4) NOT NULL,
PRIMARY KEY (personelNr),
KEY biuroNr (biuroNr),
CONSTRAINT biuroNr FOREIGN KEY (biuroNr) REFERENCES
biuro (biuroNr) ON UPDATE CASCADE ON DELETE CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
COLLATE=utf8_polish_ci;
Query OK, 0 rows affected (0.08 sec)
KEY `biuroNr` (`biuroNr`) – indeksy do kluczy obcych
są tworzone w MySQL automatycznie
15
Wprowadzanie danych z pliku w MySQL:
LOAD DATA INFILE 'plik' [ REPLACE | IGNORE ]
INTO TABLE nazwa_tabeli
[CHARACTER SET charset_name]
[ FIELDS [ TERMINATED BY '\t']
[ [OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\']]
[ LINES [STARTING BY '']
[TERMINATED BY '\n']]
[ IGNORE number LINES ] [(pole1, pole2,...)]
Domyślnie separatorem pól jest znak tabulacji (\t), a każdy
rekord zakończony jest znakiem nowej linii (\n).
16
STARTING BY – od jakich znaków zaczyna się linia w naszym
pliku (ignorujemy niektóre znaki na początku linii);
IGNORE number LINES – ignorujemy kilka początkowych
linii np. nagłówka;
Opcje REPLACE i IGNORE dotyczą sposobu potraktowania
rekordów z pliku, których wprowadzenie spowodowałoby
duplikację wartości kluczy UNIQUE (w tym klucza
głównego).
Przy opcji REPLACE nowo wczytany rekord zastępuje wiersze o
kolidujących wartościach kluczy, przy IGNORE - rekord
taki zostanie zignorowany (pominięty).
17
W wypadku, gdy nie podano żadnej z tych opcji, kolizja wartości
klucza wywoła błąd i spowoduje przerwanie wczytywania
danych.
Podanie nazw pól służy przyporządkowaniu kolejnych pól
rekordów kolumnom tabeli.
Zazwyczaj można pominąć wszystkie opcje dotyczące
separatorów pól i rekordów, jeżeli plik wejściowy został
właściwie przygotowany.
18
Np. dla pliku zlokalizowanego C:\MySQL5\data\biuro\personel.txt o
zawartości:
SA8
SA9
SB20
SB21
SB22
SB23
SB30
SB31
SB32
SG20
SG21
SL20
SL21
SL22
SL30
SL31
SL32
Katarzyna
Maria
Sabina
Daniel
Małgorzata
Anna
Katarzyna
Dawid
Małgorzata
Karolina
Piotr
Paweł
Paweł
Monika
Jan
Julia
Michał
Morawska
Hojna
Bober
Frankowski
Kowalska
Biały
Michalska
Piotrowski
Plichta
Mucha
Cybulski
Nowak
Kowalski
Munk
Wiśniewski
Lisicka
Brzęczyk
kierownik
asystent
dyrektor
kierownik
asystent
asystent
dyrektor
asystent
asystent
dyrektor
asystent
kierownik
asystent
asystent
dyrektor
asystent
asystent
K
K
K
M
K
K
K
M
K
K
M
M
M
K
M
K
M
1971-5-6
1970-2-19
1940-6-3
1958-3-24
1972-3-15
1960-11-10
1960-11-17
1975-3-22
1971-10-3
1953-3-3
1974-12-6
1962-2-2
1969-5-5
1977-7-26
1945-10-1
1965-7-13
1959-3-15
1700
900
2400
1800
1000
1200
2500
1100
1200
2200
1300
1500
1000
1100
3000
900
1000
B007
B007
B003
B003
B003
B003
B006
B006
B006
B004
B004
B002
B002
B002
B005
B005
B005
19
zapiszemy:
mysql>LOAD DATA INFILE ‘personel.txt' INTO TABLE
personel;
lub w innej lokalizacji:
mysql>LOAD DATA INFILE
'C:/MySQL5/bin/personel.txt‘
personel;
INTO TABLE
W MySQL jako znaku separacji w ścieżce dostępu używamy '/' lub
'\\'
mysql>LOAD DATA INFILE 'C:\\MySQL5\\bin\\
personel.txt' INTO TABLE personel;
20