Wymagania do projektu i realizacji baz danych 1

Wymagania do projektu i realizacji baz danych: 1

Projekt logiczny bazy danych: Przykładowy diagram: Biuro. Nr Oferuje Ma Personel. Nr Nadzoruje Nieruchomość Ogląda NieruchomośćNr Posiada Klient. Nr Wynajmuje Wynajęty Przez Właściciel prywatny Wynajęcie Właściciel. Nr Wynajęcie. Nr 2

Fragment słownika danych przedstawiający opis atrybutów: Nazwa zbioru encji Atrybuty Opis Typ danych i długość Wartości puste Wielowartościowy . . . Biuro biuro. Nr 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. 6 Nie Nie Tak Nie Nie . . . Personel personel. Nr imięNazwisko imię nazwisko stanowisko płeć data. Ur pensja biuro. Nr Jednoznacznie identyfikuje pracownika łańcuch, długość maks. 4 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. 10 łańcuch, długość 1 (M lub K) data liczba, Integer bez znaku łańcuch, długość maks. 4 Nie Nie Tak Tak Nie Nie nieruchomośćNr łańcuch, długość maks. 4 Nie miasto ulica kod typ pokoje czynsz wlasciciel. Nr biuro. Nr personel. Nr 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. 6 łańcuch, długość maks. 15 liczba, Integer bez znaku łańcuch, długość maks. 4 Nie Nie Nie Tak Tak 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 (biuro. Nr, ulica, miasto, kod pocztowy) Personel (pracownik. Nr, imię, nazwisko, stanowisko, płeć, data. Ur, pensja, biuro. Nr) Nieruchomość (nieruchomośćNr, ulica, miasto, kod pocztowy, typ, pokoje, czynsz, właściciel. Nr, pracownik. Nr, biuro. Nr) Klient (klient. Nr, imię, nazwisko, adres, telefon, preferencje, maks. Czynsz) Właściciel (właściciel. Nr, imię, nazwisko, adres, telefon) Wizyta (klient. Nr, nieruchomośćNr, data. Wizyty, uwagi) Rejestracja (klient. Nr, biuro. Nr, pracownik. Nr, data. Rejestracji) Wynajęcie (umowa. Nr, nieruchomośćNr, klient. Nr, czynsz, forma. Płatności, kaucja, zapłacona, od, do, okres) 7

Wyświetlanie zapisu tworzenia bazy: mysql> SHOW CREATE DATABASE biuro; +----------------------------+ | Database | Create Database | +----------------------------+ | biuro | CREATE DATABASE `biuro` /*!40100 DEFAULT CHARACTER SET cp 1250 COLLATE cp 1250_polish_ci */ | +----------------------------+ 1 row in set (0. 00 sec) Inny sposób prezentacji wyniku: mysql> SHOW CREATE DATABASE biuroG ************* 1. row ************* Database: biuro Create Database: CREATE DATABASE `biuro` /*!40100 DEFAULT CHARACTER SET cp 1250 COLLATE cp 1250_polish_ci */ 1 row in set (0. 01 sec) mysql> 9

Wprowadzanie poleceń z pliku : Plik z odpowiednimi poleceniami zapisujemy w katalogu bin naszego My. SQL'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: windowspulpitbiuro. sql 10

Przykładowa zawartość: CREATE DATABASE IF NOT EXISTS Biuro DEFAULT CHARACTER SET utf 8 DEFAULT COLLATE utf 8_polish_ci; CONNECT biuro; CREATE TABLE IF NOT EXISTs biuro ( biuro. Nr varchar(4) NOT NULL, ulica varchar(25) NOT NULL, miasto varchar(25) NOT NULL, kod varchar(6) NOT NULL, PRIMARY KEY (biuro. Nr) ) ENGINE=Inno. DB DEFAULT CHARSET=utf 8 COLLATE=utf 8_polish_ci; INSERT INTO biuro (biuro. Nr, ulica, miasto, kod) VALUES ('B 001', 'Piękna 46', 'Białystok', '15 -900'); INSERT INTO biuro VALUES ('B 002', '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

mysql> INSERT INTO biuro VALUES ('B 003', 'Mała 63', 'Białystok', '15 -900'), ('B 004', 'Miodowa 32', 'Grajewo', '19 -300'), ('B 005', 'Dobra 22', ‘Łomża', '18 -400'), ('B 006', 'Słoneczna 55', 'Białystok', '15 -900'), ('B 007', 'Akacjowa 16', 'Augustów', '16 -300'); Query OK, 1 row affected (0. 05 sec) mysql> SELECT * FROM biuro; +---------------+--------+ | biuro. Nr | ulica | miasto | kod | +---------------+--------+ | B 001 | Piękna 46 | Białystok | 15 -900 | | B 002 | Cicha 56 | Łomża | 18 -400 | | B 003 | Mała 63 | Białystok | 15 -900 | | B 004 | Miodowa 32 | Grajewo | 19 -300 | | B 005 | Dobra 22 | Łomża | 18 -400 | | B 006 | Słoneczna 55 | Białystok | 15 -900 | | B 007 | 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 ( personel. Nr 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, data. Ur date NOT NULL, pensja smallint(4) unsigned NOT NULL, biuro. Nr varchar(4) NOT NULL, PRIMARY KEY (personel. Nr), KEY biuro. Nr (biuro. Nr), CONSTRAINT biuro. Nr FOREIGN KEY (biuro. Nr) REFERENCES biuro (biuro. Nr) ON UPDATE CASCADE ON DELETE CASCADE -> ) ENGINE=Inno. DB DEFAULT CHARSET=utf 8 COLLATE=utf 8_polish_ci; Query OK, 0 rows affected (0. 08 sec) KEY `biuro. Nr` (`biuro. Nr`) – indeksy do kluczy obcych są tworzone w My. SQL automatycznie 15

Wprowadzanie danych z pliku w My. SQL: 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 ] [(pole 1, pole 2, . . . )] 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: My. SQL 5databiuropersonel. txt o zawartości: SA 8 SA 9 SB 20 SB 21 SB 22 SB 23 SB 30 SB 31 SB 32 SG 20 SG 21 SL 20 SL 21 SL 22 SL 30 SL 31 SL 32 Katarzyna Maria Sabina Daniel Małgorzata Anna Katarzyna Dawid Małgorzata Karolina Piotr 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 dyrektor asystent kierownik asystent dyrektor asystent K K K M M 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 B 007 B 003 B 006 B 004 B 002 B 005 19

zapiszemy: mysql>LOAD DATA INFILE ‘personel. txt' INTO TABLE personel; lub w innej lokalizacji: mysql>LOAD DATA INFILE 'C: /My. SQL 5/bin/personel. txt‘ personel; INTO TABLE W My. SQL jako znaku separacji w ścieżce dostępu używamy '/' lub '\' mysql>LOAD DATA INFILE 'C: \My. SQL 5\bin\ personel. txt' INTO TABLE personel; 20