📘 od plików płsakich do systemów zarządzania bazami danych

Zanim zaczniemy korzystać z profesjonalnych systemów zarządzania bazami danych (DBMS), warto zrozumieć, dlaczego są one potrzebne. Na początku dane często przechowuje się w plikach tekstowych, np. CSV (Comma Separated Values). Pliki te można łatwo tworzyć i edytować, np. w Excelu, LibreOffice czy nawet w Notatniku.

Jednak takie „proste bazy plikowe” mają swoje ograniczenia:

• brak kontroli poprawności danych (np. brak numeru domu, kodu pocztowego lub miejscowości),
• trudność w wyszukiwaniu i filtrowaniu danych,
• brak powiązań między danymi (relacji),
• ryzyko duplikacji i błędów,
• problemy przy równoczesnym dostępie wielu użytkowników.

Relacyjne systemy baz danych, takie jak PostgreSQL czy SQLite, rozwiązują te problemy. Dane są tam przechowywane w tabelach o jasno określonej strukturze (kolumny, typy danych), a system dba o integralność i spójność informacji. Zanim jednak przejdziemy do PostgreSQL, przyjrzyjmy się, jak wygląda „prosta baza plikowa” i jak można ją przekształcić w prawdziwą bazę danych.

SQLite

Jak wspomniano na wykładzie, dane można gromadzić w różnego rodzaju bazach danych. Często jednak potrzebne jest nam szybkie i łatwe rozwiązanie, bez konieczności mozolnego budowania architektury klient-serwer. Chcemy bowiem przechowywać dane w prostym pliku i edytować je równie łatwo jak w przypadku dokumentu tekstowego, takiego jak w programie Word. W takich przypadkach najbardziej optymalne jest użycie właśnie SQLite. SQLite jest najczęściej wykorzystywanym na świecie systemem zarządzania bazą danych. Został zastosowany w iPhonach, iPadach, w urządzeniach z systemem operacyjnym Android i Windows Mobile. Znajdziesz go również w termostatach, a także w samochodowych systemach komputerowych. Jest też wykorzystywany w satelitach i w wielu innych nowoczesnych urządzeniach, w przypadku których konieczne jest przechowywanie danych i proste ich przeszukiwanie. Z SQLite korzysta w dużym stopniu zarówno system operacyjny Windows, jak i system samolotu Airbus A350 XWB. Jest on więc stosowany wszędzie tam, gdzie istotna jest łatwość korzystania z niego oraz niskie koszty stałe. Jest również doskonały do przygotowywania prototypów baz danych dla przedsiębiorstw. Jednak coś za coś — z uwagi na brak serwera zarządzającego dostępem do bazy danych SQLite nie może być jednocześnie wykorzystywany przez wielu użytkowników. Nie jest bowiem możliwe edytowanie tego samego pliku przez wiele osób w tym samym czasie. Ten system zarządzania bazą danych nadaje się natomiast świetnie do celów szkoleniowych.

sqlite studio

import sqlite3
import csv

# Połączenie z bazą (plik .db zostanie utworzony, jeśli nie istnieje)
conn = sqlite3.connect("klienci.db")
cur = conn.cursor()

# Utworzenie tabeli
cur.execute("""
CREATE TABLE IF NOT EXISTS klienci (
    dane_osobowe TEXT,
    adres TEXT,
    produkty INTEGER
);
""")

# Wczytanie danych z pliku CSV
with open("klienci_produkty_v2.csv", newline='', encoding='utf-8') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        cur.execute("""
            INSERT INTO klienci (dane_osobowe, adres, produkty)
            VALUES (?, ?, ?)
        """, (row['dane_osobowe'], row['adres'], row['produkty']))

# Zapisanie zmian i zamknięcie połączenia
conn.commit()
conn.close()

print("Dane zostały zaimportowane do bazy danych klienci.db")

Dane zostały zaimportowane do bazy danych klienci.db

import sqlite3
import pandas as pd


db_file = "klienci.db"

# Połączenie z bazą SQLite (plik zostanie utworzony jeśli nie istnieje)
conn = sqlite3.connect(db_file)
cur = conn.cursor()

# Pobranie 10 pierwszych rekordów do DataFrame
df = pd.read_sql_query("SELECT * FROM klienci LIMIT 10;", conn)

# Wyświetlenie DataFrame
df

	dane_osobowe	adres	produkty
0	Andrzej Wiśniewski	ul. Parkowa , Tychy	Telefon
1	Michał Szymański	ul. Długa ,	Klawiatura
2	Monika Grabowska	ul. Lipowa 44, Lublin	Monitor, Kamera internetowa, Mysz, Router
3	Paweł Nowak	ul. Orla 50, 30-400 Warszawa	Laptop, Słuchawki, Router, Drukarka
4	Michał Wieczorek	ul. Różana 23, Kraków	Mysz, Telefon
5	Andrzej Kubiak	ul. Słoneczna , 80-001	Tablet, Drukarka
6	Rafał Nowak	ul. Lipowa , Kraków	Monitor, Laptop, Mysz, Tablet
7	Monika Kubiak	ul. Długa ,	Mysz, Telefon
8	Agnieszka Wójcik	ul. Mickiewicza ,	Mysz
9	Tomasz Zając	ul. Szkolna , 43-100 Szczecin	Klawiatura

# Zamknięcie połączenia
conn.close()

1. Nowa baza “wyklikana”
2. Nowa baza z pliku
3. Prosty kod sql
4. Załadowanie tabel i danych z kodu pliku sql

DDL w SQLite

1. Tworzenie tabeli klienci

• Tworzymy tabelę, jeśli jeszcze nie istnieje

CREATE TABLE IF NOT EXISTS klienci (
    id_klienci INTEGER PRIMARY KEY AUTOINCREMENT,
    dane_osobowe TEXT,    -- pole z imieniem i nazwiskiem
    adres TEXT,           -- pole z pełnym adresem
    produkty TEXT         -- pole z produktami, w wersji nienormalizowanej
);

• INSERT przykładowego rekordu do demonstracji działania

INSERT INTO klienci (dane_osobowe, adres, produkty)
VALUES ('Jan Kowalski', 'ul. Lipowa 12, 00-001 Warszawa', 'Laptop, Telefon');

• Sprawdzenie zawartości tabeli

SELECT * FROM klienci;

2. Dodanie dodatkowej kolumny

• Dodajemy kolumnę “typ” (np. kategoria klienta: standard/premium)

ALTER TABLE klienci ADD COLUMN typ TEXT;

• Wstawiamy przykładowe wartości dla nowych rekordów

UPDATE klienci SET typ = 'standard' WHERE id = 1;

• Sprawdzamy aktualną tabelę

SELECT * FROM klienci;

3. Tworzenie indeksu

CREATE INDEX idx_klienci_nazwisko ON klienci(dane_osobowe);

4. Usuwanie kolumny (ograniczenia SQLite)

• Uwaga! - SQLite nie pozwala bezpośrednio usunąć kolumny

-- 1. Tworzymy nową tabelę bez kolumny "typ"
CREATE TABLE klienci_nowa AS
SELECT id, dane_osobowe, adres, produkty
FROM klienci;

-- 2. Usuwamy starą tabelę
DROP TABLE klienci;

-- 3. Zmieniamy nazwę nowej tabeli na starą
ALTER TABLE klienci_nowa RENAME TO klienci;

-- Sprawdzamy strukturę tabeli po usunięciu kolumny
PRAGMA table_info(klienci);

5. Zmiana nazwy tabeli

ALTER TABLE klienci RENAME TO klienci_stara;

6. Usunięcie tabeli

DROP TABLE IF EXISTS klienci;

Problemy nienormalizowanej tabeli:

• Kolumna produkty zawiera wiele wartości (1–4 produkty w jednej komórce).
• Kolumna adres może być duplikowana, jeśli wielu klientów mieszka pod tym samym adresem.
• Trudno wykonywać analizy typu „którzy klienci kupili ten sam produkt”.
• Ryzyko powielania danych i błędów aktualizacji.

Pierwsza Postać Normalna (1NF) – analiza tabeli klienci

Problemy tabeli:

1. Kolumna produkty zawiera wiele wartości → brak atomowości.
2. Brak klucza głównego.
3. Trudno wyszukiwać klientów po produktach lub adresach.
4. Imię i nazwisko, adres są w jednej kolumnie.

Krok 0: Dodanie klucza głównego

ALTER TABLE klienci ADD COLUMN id_klienci INTEGER PRIMARY KEY AUTOINCREMENT;

• Każdy rekord ma teraz unikalny identyfikator id.
• Można bezpiecznie rozdzielać produkty w kolejnych krokach.

Krok 1 – Pierwsza Postać Normalna (1NF)

Cel: rozdzielamy wielowartościowe kolumny (produkty) na osobne wiersze.

Tabela 1NF – przykładowe rekordy:

CREATE TABLE klienci_1nf (
    klient_id INTEGER,
    dane_osobowe TEXT,
    adres TEXT,
    produkt TEXT
);

-- Wstawienie przykładowych danych
INSERT INTO klienci_1nf (klient_id, dane_osobowe, adres, produkt) VALUES
(1, 'Jan Kowalski', 'ul. Lipowa 12, 00-001 Warszawa', 'Laptop'),
(1, 'Jan Kowalski', 'ul. Lipowa 12, 00-001 Warszawa', 'Telefon'),
(2, 'Anna Nowak', 'ul. Długa 7, Kraków', 'Monitor'),
(3, 'Piotr Wiśniewski', 'ul. Parkowa 3, Łódź', 'Tablet'),
(3, 'Piotr Wiśniewski', 'ul. Parkowa 3, Łódź', 'Mysz'),
(3, 'Piotr Wiśniewski', 'ul. Parkowa 3, Łódź', 'Klawiatura');

Efekt dydaktyczny:

• Każdy wiersz zawiera jedną wartość produktu.
• Możemy wyszukiwać klientów po produkcie:

SELECT dane_osobowe, adres FROM klienci_1nf WHERE produkt='Laptop';

Krok 2 – Druga Postać Normalna (2NF)

Cel: usuwamy redundancję zależności częściowych

– dane, które zależą tylko od klienta (adres, imię/nazwisko), przenosimy do osobnej tabeli klienci.

CREATE TABLE klienci_2nf (
    klient_id INTEGER PRIMARY KEY,
    dane_osobowe TEXT,
    adres TEXT
);

CREATE TABLE zakupy (
    klient_id INTEGER,
    produkt TEXT,
    FOREIGN KEY (klient_id) REFERENCES klienci_2nf(klient_id)
);

-- Wstawienie przykładowych danych
INSERT INTO klienci_2nf (klient_id, dane_osobowe, adres) VALUES
(1, 'Jan Kowalski', 'ul. Lipowa 12, 00-001 Warszawa'),
(2, 'Anna Nowak', 'ul. Długa 7, Kraków'),
(3, 'Piotr Wiśniewski', 'ul. Parkowa 3, Łódź');

INSERT INTO zakupy (klient_id, produkt) VALUES
(1, 'Laptop'),
(1, 'Telefon'),
(2, 'Monitor'),
(3, 'Tablet'),
(3, 'Mysz'),
(3, 'Klawiatura');

Efekt:

• Klient pojawia się tylko raz w tabeli klienci.
• Tabela zakupy przechowuje produkty, a klient_id łączy je z klientem.
• Redukcja redundancji i łatwiejsze zarządzanie danymi.

Krok 3 – Trzecia Postać Normalna (3NF)

Cel: pełna atomizacja danych – dzielimy adres na ulicę, numer, kod, miasto.

import sqlite3
import pandas as pd

# Wczytanie CSV
df = pd.read_csv('klienci_produkty_v2.csv')  # kolumny: dane_osobowe, adres, produkty

# Utworzenie połączenia z bazą SQLite
conn = sqlite3.connect('klienci_normalizacja.db')
cur = conn.cursor()

# ===================================================
# 1. Tworzymy tabele w 3NF
# ===================================================
cur.execute('''
CREATE TABLE IF NOT EXISTS adresy (
    adres_id INTEGER PRIMARY KEY AUTOINCREMENT,
    ulica TEXT,
    numer TEXT,
    kod TEXT,
    miasto TEXT
)
''')

cur.execute('''
CREATE TABLE IF NOT EXISTS klienci (
    klient_id INTEGER PRIMARY KEY AUTOINCREMENT,
    dane_osobowe TEXT,
    adres_id INTEGER,
    FOREIGN KEY (adres_id) REFERENCES adresy(adres_id)
)
''')

cur.execute('''
CREATE TABLE IF NOT EXISTS zakupy (
    klient_id INTEGER,
    produkt TEXT,
    FOREIGN KEY (klient_id) REFERENCES klienci(klient_id)
)
''')

conn.commit()

# ===================================================
# 2. Wypełnienie tabel
# ===================================================

# Pomocnicza funkcja do rozdzielania adresu
def rozdziel_adres(adres):
    try:
        # zakładamy format: "ul. Lipowa 12, 00-001 Warszawa"
        czesci = adres.split(',')
        ulica_numer = czesci[0].strip().rsplit(' ', 1)
        ulica = ulica_numer[0]
        numer = ulica_numer[1] if len(ulica_numer) > 1 else ''
        kod_miasto = czesci[1].strip().split(' ', 1) if len(czesci) > 1 else ['', '']
        kod = kod_miasto[0]
        miasto = kod_miasto[1] if len(kod_miasto) > 1 else ''
        return ulica, numer, kod, miasto
    except:
        return '', '', '', ''

# Dodanie adresów i przypisanie adres_id
adres_map = {}  # mapowanie pełnego adresu na adres_id
for index, row in df.iterrows():
    adres = row['adres']
    if adres not in adres_map:
        ulica, numer, kod, miasto = rozdziel_adres(adres)
        cur.execute('''
            INSERT INTO adresy (ulica, numer, kod, miasto)
            VALUES (?, ?, ?, ?)
        ''', (ulica, numer, kod, miasto))
        adres_id = cur.lastrowid
        adres_map[adres] = adres_id

    # Dodanie klienta
    cur.execute('''
        INSERT INTO klienci (dane_osobowe, adres_id)
        VALUES (?, ?)
    ''', (row['dane_osobowe'], adres_map[adres]))
    klient_id = cur.lastrowid

    # Rozdzielenie produktów i dodanie do zakupy
    produkty = [p.strip() for p in str(row['produkty']).split(',')]
    for produkt in produkty:
        if produkt:  # pomijamy puste
            cur.execute('''
                INSERT INTO zakupy (klient_id, produkt)
                VALUES (?, ?)
            ''', (klient_id, produkt))

conn.commit()
conn.close()

print("Import i normalizacja zakończone!")

Import i normalizacja zakończone!

Opis działania:

1. Adresy są rozdzielane na ulica, numer, kod, miasto → tabela adresy.
2. Klienci trafiają do tabeli klienci z adres_id.
3. Produkty są rozdzielane i dodawane do tabeli zakupy.
4. Każdy klient ma unikalny klient_id, więc jeśli kupił kilka produktów, w zakupy będzie kilka wierszy.

import sqlite3
import pandas as pd

conn = sqlite3.connect('klienci_normalizacja.db')

# Wyświetlenie 10 pierwszych klientów
df_klienci = pd.read_sql('SELECT * FROM klienci LIMIT 10', conn)
df_klienci.head(10)

	klient_id	dane_osobowe	adres_id
0	1	Andrzej Wiśniewski	1
1	2	Michał Szymański	2
2	3	Monika Grabowska	3
3	4	Paweł Nowak	4
4	5	Michał Wieczorek	5
5	6	Andrzej Kubiak	6
6	7	Rafał Nowak	7
7	8	Monika Kubiak	8
8	9	Agnieszka Wójcik	9
9	10	Tomasz Zając	10

# Wyświetlenie 10 pierwszych zakupów
df_zakupy = pd.read_sql('SELECT * FROM zakupy LIMIT 10', conn)
df_zakupy

	klient_id	produkt
0	1	Telefon
1	2	Klawiatura
2	3	Monitor
3	3	Kamera internetowa
4	3	Mysz
5	3	Router
6	4	Laptop
7	4	Słuchawki
8	4	Router
9	4	Drukarka

conn.close()

Normalizacja tabeli klienci – pełne podsumowanie

Tabela początkowa (przed normalizacją)

• Nazwa tabeli: klienci
• Liczba kolumn: 3 (dane_osobowe, adres, produkty)
• Typy danych: wszystkie TEXT (nienormalizowane)
• Problemy:
• Brak klucza głównego → brak jednoznacznej identyfikacji rekordu.
• Wielowartościowa kolumna produkty → złamanie zasad atomowości.
• Imię/nazwisko i adres w jednej kolumnie → brak pełnej atomowości.
• Trudności w analizie i wyszukiwaniu.

⸻

Proces normalizacji krok po kroku

Krok 0 – Dodanie klucza głównego

• Kolumna id jako PRIMARY KEY.
• Każdy rekord jednoznacznie identyfikowany.

Krok 1 – 1NF

• Rozdzielenie wielowartościowej kolumny produkty na osobne wiersze.
• Jeden wiersz = jeden klient + jeden produkt.
• Cel: każda kolumna jest atomowa.

Krok 2 – 2NF

• Rozdzielenie danych, które zależą tylko od klienta (adres, imię/nazwisko), do osobnej tabeli klienci.
• Produkty przeniesione do tabeli zakupy.
• Relacja: klient_id w zakupy → klient_id w klienci (1:N).

Krok 3 – 3NF

• Rozdzielenie adresu na atomowe kolumny: ulica, numer, kod, miasto → tabela adresy.
• W tabeli klienci pozostaje tylko adres_id jako klucz obcy.
• Relacje:
• klienci.adres_id → adresy.adres_id (1:1)
• zakupy.klient_id → klienci.klient_id (1:N)

import pandas as pd

# Wczytanie CSV
df = pd.read_csv('klienci_produkty_v2.csv')  # kolumny: dane_osobowe, adres, produkty

# Plik wynikowy SQL
sql_file = open('klienci_3nf.sql', 'w', encoding='utf-8')

# ===================================================
# 1. Tworzenie tabel
# ===================================================
sql_file.write("""
-- Tabele w 3NF
CREATE TABLE IF NOT EXISTS adresy (
    adres_id INTEGER PRIMARY KEY AUTOINCREMENT,
    ulica TEXT,
    numer TEXT,
    kod TEXT,
    miasto TEXT
);

CREATE TABLE IF NOT EXISTS klienci (
    klient_id INTEGER PRIMARY KEY AUTOINCREMENT,
    dane_osobowe TEXT,
    adres_id INTEGER,
    FOREIGN KEY (adres_id) REFERENCES adresy(adres_id)
);

CREATE TABLE IF NOT EXISTS zakupy (
    klient_id INTEGER,
    produkt TEXT,
    FOREIGN KEY (klient_id) REFERENCES klienci(klient_id)
);

""")

# ===================================================
# 2. Generowanie INSERT dla wszystkich danych
# ===================================================
adres_map = {}  # mapowanie pełnego adresu na adres_id
adres_id_counter = 1
klient_id_counter = 1

def rozdziel_adres(adres):
    try:
        czesci = adres.split(',')
        ulica_numer = czesci[0].strip().rsplit(' ', 1)
        ulica = ulica_numer[0]
        numer = ulica_numer[1] if len(ulica_numer) > 1 else ''
        kod_miasto = czesci[1].strip().split(' ', 1) if len(czesci) > 1 else ['', '']
        kod = kod_miasto[0]
        miasto = kod_miasto[1] if len(kod_miasto) > 1 else ''
        return ulica, numer, kod, miasto
    except:
        return '', '', '', ''

for index, row in df.iterrows():
    adres = row['adres']
    if adres not in adres_map:
        ulica, numer, kod, miasto = rozdziel_adres(adres)
        sql_file.write(f"INSERT INTO adresy (adres_id, ulica, numer, kod, miasto) VALUES ({adres_id_counter}, '{ulica.replace('\'','\'\'')}', '{numer}', '{kod}', '{miasto}');\n")
        adres_map[adres] = adres_id_counter
        adres_id_counter += 1

    # Dodanie klienta
    sql_file.write(f"INSERT INTO klienci (klient_id, dane_osobowe, adres_id) VALUES ({klient_id_counter}, '{row['dane_osobowe'].replace('\'','\'\'')}', {adres_map[adres]});\n")

    # Rozdzielenie produktów
    produkty = [p.strip() for p in str(row['produkty']).split(',')]
    for produkt in produkty:
        if produkt:
            sql_file.write(f"INSERT INTO zakupy (klient_id, produkt) VALUES ({klient_id_counter}, '{produkt.replace('\'','\'\'')}');\n")

    klient_id_counter += 1

sql_file.close()
print("Plik SQL 'klienci_3nf.sql' został wygenerowany!")

Plik SQL 'klienci_3nf.sql' został wygenerowany!