Baza danych

Baza danych przygotowana w systemie bazodanowym SQLite3 zapisana jest w pliku pracownicy.db, który pobieramy i zapisujemy w wybranym katalogu. Do zarządzania bazą użyjemy programu SQLiteStudio, ale można do tego celu użyć również wiersza poleceń bazy SQLite3.

Na początku poznajmy schemat bazy.

Ćwiczenie 1

Po uruchomieniu programu SQLiteStudio wybierz polecenie Bazy danych | Dodaj bazę danych i wskaż położenie pliku pracownicy.db. Następnie połącz się z bazą, dwukrotnie klikając nazwę bazy w panelu Bazy danych.

Następnie kliknij dwa razy nazwę tabeli pracownicy, w zakładce Struktura zobaczysz nazwy i typy danych pól zawartych w tabeli. Po wybraniu zakładki Dane zobaczysz przechowywane w tabeli wartości. Jeżeli klikniesz zakładkę DDL, zobaczysz polecenie SQL, które zostało użyte do utworzenia danej tabeli.

Pokaż rozwiązanie

RZ7c1IlEUtAXo

Zrzut ekranu z programu LibreOffice Base. Przedstawia relacje między trzema tabelami. Tabela uczniowie zawiera pola id_ucz, nazwisko, imie, ulica, dom, id_klasy. Tabela oceny zawiera pola: id, id_ucz, icena, data, id_przedm. Tabela przedmioty zawiera id_przedm, nazwa_przedm, nazwisko_naucz, imie_naucz. Pola id_ucz tabel uczniowie oraz oceny połączono linią. Nad linią przy tabeli uczniowie jest cyfra 1, a przy tabeli oceny litera n. Pola id_przed tabel oceny oraz przedmioty połączono linią. Nad linią przy tabeli oceny jest litera n, a przy tabeli oceny cyfra 1.

Polecenie DDL tworzące tabelę pracownicy:

Linia 1. CREATE TABLE pracownicy otwórz nawias okrągły. Linia 2. id INTEGER PRIMARY KEY AUTOINCREMENT przecinek. Linia 3. imie TEXT przecinek. Linia 4. nazwisko TEXT przecinek. Linia 5. data DATE przecinek. Linia 6. ulica TEXT przecinek. Linia 7. kod TEXT przecinek. Linia 8. miasto TEXT. Linia 9. zamknij nawias okrągły średnik.

CREATE TABLE pracownicy (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  imie     TEXT,
  nazwisko TEXT,
  data     DATE,
  ulica    TEXT,
  kod      TEXT,
  miasto   TEXT
);

W tabeli pracownicy występują pola:

• id – INTEGER, klucz główny,

• imie, nazwisko, ulica, kod, miasto – TEXT,

• data – DATE, data urodzenia pracownika.

Polecenie DDL tworzące tabelę place:

Linia 1. CREATE TABLE place otwórz nawias okrągły. Linia 2. id podkreślnik pracownika INTEGER REFERENCES pracownicy otwórz nawias okrągły id zamknij nawias okrągły przecinek. Linia 3. id podkreślnik stanowiska INTEGER REFERENCES stanowiska otwórz nawias okrągły id zamknij nawias okrągły przecinek. Linia 4. data DATE przecinek. Linia 5. placa FLOAT przecinek. Linia 6. FOREIGN KEY otwórz nawias okrągły id podkreślnik pracownika zamknij nawias okrągły REFERENCES pracownicy otwórz nawias okrągły id zamknij nawias okrągły przecinek. Linia 7. FOREIGN KEY otwórz nawias okrągły id podkreślnik stanowiska zamknij nawias okrągły REFERENCES stanowiska otwórz nawias okrągły id zamknij nawias okrągły. Linia 8. zamknij nawias okrągły średnik.

CREATE TABLE place (
  id_pracownika INTEGER REFERENCES pracownicy (id),
  id_stanowiska INTEGER REFERENCES stanowiska (id),
  data          DATE,
  placa         FLOAT,
  FOREIGN KEY (id_pracownika) REFERENCES pracownicy (id),
  FOREIGN KEY (id_stanowiska) REFERENCES stanowiska (id)
);

W tabeli place występują pola:

• id_pracownika, id_stanowiska – INTEGER, klucze obce,

• data – DATE, data zatrudnienia pracownika,

• placa – DECIMAL.

Polecenie DDL tworzące tabelę stanowiska:

Linia 1. CREATE TABLE stanowiska otwórz nawias okrągły. Linia 2. id INTEGER PRIMARY KEY AUTOINCREMENT przecinek. Linia 3. stanowisko TEXT. Linia 4. zamknij nawias okrągły średnik.

CREATE TABLE stanowiska (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  stanowisko TEXT
);

W tabeli stanowiska występują pola:

• id – INTEGER, klucz główny,

• stanowisko – TEXT.

Polecenie DDL tworzące tabelę kontakty:

Linia 1. CREATE TABLE kontakty otwórz nawias okrągły. Linia 2. id INTEGER PRIMARY KEY AUTOINCREMENT przecinek. Linia 3. id podkreślnik pracownika INTEGER NOT NULL przecinek. Linia 4. telefon TEXT przecinek. Linia 5. email TEXT przecinek. Linia 6. FOREIGN KEY otwórz nawias okrągły id podkreślnik pracownika zamknij nawias okrągły REFERENCES pracownicy otwórz nawias okrągły id zamknij nawias okrągły. Linia 7. zamknij nawias okrągły średnik.

CREATE TABLE kontakty (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  id_pracownika INTEGER NOT NULL,
  telefon       TEXT,
  email         TEXT,
  FOREIGN KEY (id_pracownika) REFERENCES pracownicy (id)
);

W tabeli kontakty występują pola:

• id – INTEGER, klucz główny,

• id_pracownika – INTEGER, klucz obcy,

• telefon, email – TEXT.

Tabele pracownicy i kontakty łączy relacja jeden‑do‑wielu. Tabele pracownicy i stanowiska łączy relacja wiele‑do‑wielu definiowana przez relacje jeden‑do‑wielu z każdej z tych tabel do tabeli pośredniej place.

Zmiana schematu bazy

Baza zawiera dane na temat pracowników pewnej firmy. W tabeli pracownicy zawarte są informacje osobowe. Łatwo zauważyć, że część informacji dotycząca nazw miast i kodów pocztowych powtarza się. Odwołując się do zasad normalizacjinormalizacjanormalizacji, warto utworzyć nową tabelę słownikową z danymi miast i połączyć ją relacją z tabelą pracownicy.

W programie SQLiteStudio przywołujemy okno edytora SQL, wybierając polecenie Narzędzia | Otwórz edytor SQL. Wpisujemy i wykonujemy pierwszą instrukcję:

CREATE TABLE miasta (
    id_miasta INTEGER      PRIMARY KEY AUTOINCREMENT,
    nazwa     VARCHAR (30) NOT NULL,
    kod       CHAR (6)     DEFAULT ''
);

W wyniku kwerendy powstanie tabela miasta. Pierwsze pole będzie kluczem głównym. W drugim zapiszemy nazwy, w trzecim kody pocztowe miast.

Do nowej tabeli dodamy dane pobrane z tabeli pracownicy, przy użyciu podzapytania SELECT. Będą to niepowtarzające się nazwy miast i ich kody pocztowe.

INSERT INTO miasta (nazwa, kod)
SELECT DISTINCT miasto, kod FROM pracownicy;

SELECT * FROM miasta;

W tabeli pracownicy stworzymy nowe pole id_miasta, które posłuży do utworzenia relacji z tabelą miasta:

ALTER TABLE pracownicy ADD id_miasta INTEGER;

Używamy klauzuli ALTER, która pozwala na:

• zmianę nazwy tabeli,

• dodawanie, usuwanie lub zmienianie pól tabeli,

• dodawanie i usuwanie ograniczeńograniczeniaograniczeń nakładanych na pola tabeli.

Zamiast powtarzających się nazw i kodów pocztowych miast w tabeli pracownicy chcemy w każdym rekordzie zapisać identyfikator miasta. Użyjemy klauzuli UPDATE służącej do zmiany wartości przechowywanych w polach (kolumnach) bazy danych.

UPDATE pracownicy
SET id_miasta = (SELECT id_miasta FROM miasta WHERE nazwa = pracownicy.miasto);

Powyższy kod czytamy następująco: zaktualizuj tabelę pracownicy, zmień wartość w polu id_miasta na identyfikator miasta wybrany przez podzapytanie z tabeli miasta na podstawie dopasowania nazwy miasta z obu tabel. Zwróćmy uwagę, że zapytanie wykonuje się na wszystkich rekordach.

Ponieważ w tabeli pracownicy mamy już odniesienie do tabeli miasta, zbędne pola moglibyśmy usunąć, a na nowe pole id_miasta nałożyć ograniczenie:

ALTER TABLE pracownicy DROP COLUMN kod;
ALTER TABLE pracownicy DROP COLUMN miasto;
ALTER TABLE pracownicy
ADD FOREIGN KEY (id_miasta) REFERENCES miasta(id_miasta);

Jednak system bazodanowy SQLite3 nie wspiera powyższych operacji. Zamiast tego musimy utworzyć nową tabelę z odpowiednimi polami i zaimportować do niej dane z poprzedniej tabeli.

Zaczynamy od wykonania po kolei podanych trzech zapytań:

PRAGMA foreign_keys = 0;
CREATE TABLE pracownicy_tmp AS SELECT * FROM pracownicy;
DROP TABLE pracownicy;

Problem klucza obcego

Tabelę pracownicy utworzymy na nowo za pomocą poniższego kodu:

CREATE TABLE pracownicy (
id        INTEGER  PRIMARY KEY AUTOINCREMENT,
imie      TEXT,
nazwisko  TEXT,
data      DATE,
ulica     TEXT,
id_miasta INETEGER,
FOREIGN KEY (id_miasta) REFERENCES miasta (id_miasta)
);

Zwróćmy uwagę na definicję klucza obcego, którego nie mogliśmy dodać wcześniej ze względu na brak tej opcji w bazach SQLite3. W nowej tabeli nie tworzymy również niepotrzebnych już pól kod i miasto.

Dane w nowej tabeli umieścimy za pomocą poniższej klauzuli:

INSERT INTO pracownicy
SELECT id, imie, nazwisko, data, ulica, id_miasta
FROM pracownicy_tmp;

W zapytaniu wybierającym nie możemy użyć selektora wszystkich pól, czyli znaku znaku „*”, musimy podać we właściwej kolejności nazwy kolumn, ponieważ nie chcemy kopiować danych dotyczących miast.

Tabelę tymczasową pracownicy_tmp możemy już usunąć:

DROP TABLE pracownicy_tmp;

Na koniec należy przywrócić kontrolę ograniczeń kluczy obcych i sprawdzić poprawność wykonanych operacji:

PRAGMA foreign_keys = 1;

SELECT imie, nazwisko, miasta.nazwa
FROM pracownicy
INNER JOIN miasta USING (id_miasta);

RVPCuyApzRcWK

Zdjęcie programu SQLite Studio w głównym oknie zaprezentowany jest kod SELECT DISTINCT(miasto), kod FROM pracownicy; ALTER TABLE pracownicy ADD id_miasta INTEGER; UPDATE pracownicy SET id_miasta = (SELECT id_miasta FROM miasta WHERE nazwa = pracownicy.miasto); PRAGMA foreign_keys = 0; CREATE TABLE pracownicy_tmp AS SELECT * FROM pracownicy; DROP TABLE pracownicy; CREATE TABLE pracownicy ( id INTEGER PRIMARY KEY AUTOINCREMENT, imie TEXT, nazwisko TEXT, data DATE, ulica TEXT, id_miasta INETEGER, FOREIGN KEY (id_miasta) REFERENCES miasta (id_miasta) ); INSERT INTO pracownicy SELECT id, imie, nazwisko, data, ulica, id_miasta FROM pracownicy_tmp; PRAGMA foreign_keys = 1; SELECT imie, nazwisko, miasta.nazwa FROM pracownicy INNER JOIN miasta USING (id_miasta); Następnie umieszczona pod kodem jest tabela z kolumnami imie, nazwisko i nazwa, w każdej kolejno imiona, nazwiska oraz miasta.

Słownik