Relacyjne bazy danych - laboratorium 4

Wprowadzenie

Na dzisiejszych laboratiorach zaprezentowane zostanie wprowadznie do języka SQL wraz z instrukcją postawienia nowej bazy danych wykorzystując narzędzie do konteneryzacji, jakim jest Docker. DBMS użytym podczas zajęć będzie PostgreSQL.

Przygotowanie środowiska

Uruchom Docker Desktop oraz otwórz wiersz poleceń/terminal oraz użyj poniższej komendy:

docker run --name taxi-postgres -e POSTGRES_PASSWORD=lele -d -p 5432:5432 postgres

Powyższa komenda pobiera najnowszy obraz PostgrsSQL z DockerHub, uruchamia go jako kontener oraz ustawia takie parametry jak:

1. --name taxi-progres - nazwa kontenera Dockerowego
2. POSTGRES_PASSWORD=lele - hasło do bazy danych
3. -p 5432:5432 - ustawia port na zewnątrz kontenera

I tylko tyle, mamy już chodzący DBMS na naszym komputerze. Teraz, wejdźmy do kontenera i zalogujmy się do bazy.

Aby wejść do środowiska postawionego przez nas DBMS użyj w wierszu poleceń poniższej komendy:

docker exec -it taxi-postgres  /bin/sh

Poprawne użycie komendy docker exec powinno wyglądać tak:

Będąc w kontenerze, zalogujmy się do bazy danych za pomocą podstawowego konta postgresowego:

psql -h localhost postgres postgres

Cała operacja powinna wyglądać następująco:

SQL

Język SQL (Structured Query Language), jest standardowym językiem służącym do tworzenia, modyfikowania, umieszczania oraz pobierania danych z baz danych. Składnia języka jest tworzona na wzór języka naturalnego. Dzisiaj poznamy kilka podstawowych zapytań:

Tworzenie bazy danych

CREATE DATABASE

CREATE DATABASE jest to zapytania służące do stworzenia nowej bazy danych w DBMS. Podany poniżej kod, jest przykładem stworzenia bazy danych nazwanej “foodb”

CREATE DATABASE foodb;

Tworzenie scheme

CREATE SCHEMA

Schema służy do organizacji/grupowania tabel, widoków, typów danych, relacji w bazie danych. Domyślnie w PSQL wszystkie nowo tworzone tabele będą znajdowały się w scheme public.

Przykład stworzenia własnej scheme o nazwie data:

CREATE SCHEMA data;

Tworzenie tabeli

CREATE TABLE

Podstawową komendą do stworzenia nowej tabeli jest CREATE TABLE. W jej składni definiujemy nazwę tabeli oraz kolumny wraz z ich typem danych.

Przykład stworzenia nowej tabeli przechowywującej dane adresowe:

CREATE TABLE address (
  id serial PRIMARY KEY,
  street_name varchar(255),
  building_number varchar(10),
  flat_number varchar(10),
  city varchar(25),
  zip_code varchar(25),
  country varchar(25)
);

Aby stworzyć tabelę w danej schemie np. data, należy wpisać scheme jako prefix nazwy tabeli który jest oddzielony od samej nazwy kropką.

Przykład:

CREATE TABLE data.address (
  id serial PRIMARY KEY,
  street_name varchar(255),
  building_number varchar(10),
  flat_number varchar(10),
  city varchar(25),
  zip_code varchar(25),
  country varchar(25)
);

Dodawanie danych

INSERT

Komenda INSERT służy do dodawania danych do tabeli.

Przykład dodania pojedynczego rekordu do tabeli adresowej:

INSERT INTO 
  address (street_name, building_number, flat_number, city, zip_code, country)
VALUES
  ('Grunwaldzka', '2a', '3', 'Gdansk', '80-000', 'Poland');

Przykład dodania kilku rekordów na raz:

INSERT INTO 
  address (street_name, building_number, flat_number, city, zip_code, country)
VALUES
  ('Grunwaldzka', '2a', '3', 'Gdansk', '80-000', 'Poland'),
  ('Norwida', '19', '1c', 'Gdynia', '81-000', 'Poland'),
  ('Koszykowa', '86', null, 'Warszawa', '02-008', 'Warszawa');

Wysyłanie zapytań o dane

SELECT

SELECT to podstawowa komenda służąca do wysyłania zapytań o dane. Domyślnie deklarujemy w niej zapytanie o wybrane kolumny z danej tabeli.

Przykład dla tabeli adresowej:

SELECT street_name, city FROM address;

Aby pobrać z kolei wszystkie dane ze wszystkich kolumn, zamiast definiowania w zapytaniu po kolei wszystkich nazw kolumn, używany jest znak gwiazdki *.

SELECT * FROM address;

Konwencja

1. Przyjeło się że słowa kluczowe w SQL pisane są UPPER_CASEM
2. Podstawowym stylem nazewnictwa w PostgreSQL jest tzw. snake_case
3. Nazwy tabel, kolumn itd. pownny zawsze być pisane w języku angielskim
4. PostgreSQL przetrzymuje nazwy w tzw. lowercase oraz jest case-sensitive. Oznacza to że musimy być konsekwętni w nazywaniu struktur bazo danowych.
5. Zapytania w PSQL kończymy średnikiem: ;

Zadania

Dla każdego zadania przygotuj skrypty SQL. Skrypty umieśc w folderze migrations. Dla każdej tabeli przygotuj osobny plik ze skryptem i nazwij go V1__lab04.sql. Gotowe skrypty umieść w swoim repozytorium na Githubie.

Zadanie 1

Stwórz pustą bazę danych o nazwie taxi. Zweryfikuj stworzenie bazy danych przy pomocy komendy \l. Pamiętaj by skrypt do tworzenia bazy umieścić w pliku V1__lab04.sql. Po stworzeniu bazy połącz się do niej używając komendy \c taxi. Wykonane zadanie powinno prezentować się następująco:

Zadanie 2

Przygotuj skrypty tworzące tabele według powyższego diagramu ERD oraz zgodnie z konwencją nazewniczną PSQL. Pomiń dodawanie kluczy obcych. Użyjcie dokumentacji PostgresSQL, aby dobrać prawidłowe typy kolumn.

Zadanie 3

Wypełnij tabele danymi. Przygotuj skrypty, które dodadzą do każdej tabeli po 5 wierszy.

Zadanie 4

Dla każdej tabeli napisz zapytanie Select, dzięki któremu przetestujesz czy tabele zostały prawidłowo zapełnione danymi.