Projektowanie i wdrażanie bazy danych — SKLEP MOTOCYKLOWY

1. Definiowanie tabel zgodnie z projektem (mapowanie E/R → SQL)

Opis: Zaczynamy od modelu encja-związek (E/R). Każda encja staje się tabelą, a relacje są odwzorowane przez klucze obce. Poniżej pokazano kompletne przekształcenie prostego modelu sklepu motocyklowego.

ENCJE:
- Klient (id_klienta, imie, nazwisko, email, telefon, data_rejestracji)
- Producent (id_producenta, nazwa, kraj)
- Motocykl (id_moto, id_producenta, marka, model, pojemnosc, cena, rok)
- Zamowienie (id_zamowienia, id_klienta, data_zamowienia, status, wartosc)
- PozycjaZam (id_zam, id_zamowienia, id_moto, ilosc, cena_jednostkowa)

RELACJE:
- Klient 1:N Zamowienie
- Zamowienie 1:N PozycjaZam
- Motocykl 1:N PozycjaZam
- Producent 1:N Motocykl
  

-- Skrypt tworzenia struktury (fragment)
CREATE DATABASE IF NOT EXISTS SklepMotocyklowy;
USE SklepMotocyklowy;

CREATE TABLE Producent (
  id_producenta INT AUTO_INCREMENT PRIMARY KEY,
  nazwa VARCHAR(100) NOT NULL UNIQUE,
  kraj VARCHAR(50)
);

CREATE TABLE Klient (
  id_klienta INT AUTO_INCREMENT PRIMARY KEY,
  imie VARCHAR(40) NOT NULL,
  nazwisko VARCHAR(60) NOT NULL,
  email VARCHAR(100) UNIQUE,
  telefon VARCHAR(20),
  data_rejestracji DATE DEFAULT (CURRENT_DATE)
);

CREATE TABLE Motocykl (
  id_moto INT AUTO_INCREMENT PRIMARY KEY,
  id_producenta INT NOT NULL,
  marka VARCHAR(60) NOT NULL,
  model VARCHAR(60) NOT NULL,
  pojemnosc INT CHECK (pojemnosc > 0),
  cena DECIMAL(10,2) NOT NULL CHECK (cena >= 0),
  rok YEAR,
  FOREIGN KEY (id_producenta) REFERENCES Producent(id_producenta)
);

CREATE TABLE Zamowienie (
  id_zamowienia INT AUTO_INCREMENT PRIMARY KEY,
  id_klienta INT NOT NULL,
  data_zamowienia DATETIME DEFAULT CURRENT_TIMESTAMP,
  status ENUM('nowe','w_realizacji','zrealizowane','anulowane') DEFAULT 'nowe',
  wartosc DECIMAL(12,2) DEFAULT 0,
  FOREIGN KEY (id_klienta) REFERENCES Klient(id_klienta)
);

CREATE TABLE PozycjaZam (
  id_zam INT AUTO_INCREMENT PRIMARY KEY,
  id_zamowienia INT NOT NULL,
  id_moto INT NOT NULL,
  ilosc SMALLINT NOT NULL CHECK (ilosc > 0),
  cena_jednostkowa DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (id_zamowienia) REFERENCES Zamowienie(id_zamowienia),
  FOREIGN KEY (id_moto) REFERENCES Motocykl(id_moto)
);
  

2. Definiowanie odpowiednich typów danych zgodnie z projektem

Opis: Dobór typów danych wpływa na poprawność, szybkość zapytań i rozmiar pamięci. Poniżej zasady i przykłady dla najważniejszych pól w sklepie motocyklowym.

PRZYKŁADY DOPASOWAŃ:
- id (klucz) -> INT AUTO_INCREMENT (lub BIGINT jeśli dużo rekordów)
- nazwa, marka, model -> VARCHAR(50-150) (nie używaj TEXT jeśli niepotrzebne)
- opis -> TEXT (dla dłuższych treści)
- cena -> DECIMAL(10,2) (dokładność finansowa)
- pojemnosc -> SMALLINT / INT (wartości całkowite)
- data -> DATE / DATETIME / TIMESTAMP (wybierz zależnie od potrzeb)
- status -> ENUM (ograniczone wartości): np. ENUM('nowe','w_realizacji','zrealizowane')
- boolean -> TINYINT(1) lub BOOLEAN (np. dostępny)
  

-- przykładowe kolumny z dobranymi typami
CREATE TABLE Czesci (
  id_czesci INT AUTO_INCREMENT PRIMARY KEY,
  kod VARCHAR(30) NOT NULL UNIQUE,
  nazwa VARCHAR(120) NOT NULL,
  opis TEXT,
  cena DECIMAL(9,2) NOT NULL CHECK (cena >= 0),
  ilosc_magazyn INT DEFAULT 0 CHECK (ilosc_magazyn >= 0),
  data_dodania DATETIME DEFAULT CURRENT_TIMESTAMP
);
  

3. Wprowadzanie danych do bazy (INSERT, import z pliku)

Opis: Po zaprojektowaniu struktury wprowadzamy dane — ręcznie, za pomocą skryptów SQL lub przez import plików CSV. Poniżej pełne przykłady, walidacja i porady.

-- dodawanie producentów
INSERT INTO Producent (nazwa, kraj) VALUES
('Harley-Davidson', 'USA'),
('Yamaha', 'Japonia'),
('KTM', 'Austria');

-- dodawanie motocykli
INSERT INTO Motocykl (id_producenta, marka, model, pojemnosc, cena, rok)
VALUES
(1, 'Harley-Davidson', 'Street Bob', 1746, 58900.00, 2020),
(2, 'Yamaha', 'MT-09', 847, 35800.00, 2022),
(3, 'KTM', 'Duke 390', 373, 21000.00, 2023);
  

-- CSV: motocykle.csv (kolumny: id_producenta;marka;model;pojemnosc;cena;rok)
LOAD DATA INFILE '/var/lib/mysql-files/motocykle.csv'
INTO TABLE Motocykl
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id_producenta, marka, model, pojemnosc, cena, rok);
  

-- phpMyAdmin: zakładka Import
1. Wybierz plik CSV.
2. Ustaw Separator pól na ';' (lub inny).
3. Zaznacz 'Ignore first line' jeśli plik ma nagłówki.
4. Mapuj kolumny jeśli kolejność się różni.
5. Wybierz opcję 'Replace' lub 'Ignore' w razie konfliktów unikalności.
  

START TRANSACTION;
-- wstawienia hurtowe
INSERT INTO Klient (imie,nazwisko,email) VALUES ('Jan','Nowak','jan@example.com');
INSERT INTO Zamowienie (id_klienta, wartosc) VALUES (LAST_INSERT_ID(), 58900.00);
-- jeśli wszystko OK
COMMIT;
-- w przeciwnym wypadku
-- ROLLBACK;
  

4. Tworzenie skryptów automatyzujących proces tworzenia struktury

Opis: Skrypt SQL (batch) pozwala odtworzyć strukturę bazy w jednym kroku — użyteczne do środowisk testowych, CI/CD oraz migracji. Poniżej przykład kompletnego skryptu oraz dobre praktyki.

-- create_schema.sql
DROP DATABASE IF EXISTS SklepMotocyklowy;
CREATE DATABASE SklepMotocyklowy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE SklepMotocyklowy;

-- tworzenie tabel (Producent, Klient, Motocykl, Zamowienie, PozycjaZam)
-- (wstaw kod tworzący tabele, constraints, indeksy)
-- dodanie przykładowych danych (opcjonalne)
-- tworzenie widoków i procedur
  

-- indeksy pomocnicze
CREATE INDEX idx_marka ON Motocykl(marka);
CREATE INDEX idx_email ON Klient(email);

-- przykładowa procedura aktualizująca wartość zamówienia
DELIMITER //
CREATE PROCEDURE AktualizujWartoscZamowienia (IN pid INT)
BEGIN
  DECLARE total DECIMAL(12,2);
  SELECT SUM(ilosc * cena_jednostkowa) INTO total FROM PozycjaZam WHERE id_zamowienia = pid;
  UPDATE Zamowienie SET wartosc = IFNULL(total,0) WHERE id_zamowienia = pid;
END //
DELIMITER ;
  

5. Importowanie danych z pliku i eksportowanie danych

Opis: Import/eksport = przenoszenie danych między systemami, tworzenie kopii zapasowych, przygotowanie danych do analizy. Poniżej komplet metod i przykłady.

-- eksport całej bazy (konsola)
mysqldump -u root -p SklepMotocyklowy > SklepMotocyklowy_backup_2025-11-04.sql

-- eksport jednej tabeli
mysqldump -u root -p SklepMotocyklowy Motocykl > motocykle.sql
  

-- import (konsola)
mysql -u root -p SklepMotocyklowy < SklepMotocyklowy_backup_2025-11-04.sql
  

SELECT id_moto, marka, model, cena
FROM Motocykl
INTO OUTFILE '/var/lib/mysql-files/motocykle_export.csv'
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
  

-- 1) utwórz tabelę staging
CREATE TABLE Motocykle_staging (
  id_producenta INT,
  marka VARCHAR(60),
  model VARCHAR(60),
  pojemnosc INT,
  cena DECIMAL(10,2),
  rok YEAR
);

-- 2) załaduj CSV do staging
LOAD DATA INFILE '/var/lib/mysql-files/motocykle_new.csv'
INTO TABLE Motocykle_staging
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- 3) sprawdź błędy
SELECT * FROM Motocykle_staging WHERE cena IS NULL OR cena < 0 OR pojemnosc <= 0;

-- 4) przenieś poprawne rekordy do docelowej tabeli
INSERT INTO Motocykl (id_producenta, marka, model, pojemnosc, cena, rok)
SELECT id_producenta, marka, model, pojemnosc, cena, rok
FROM Motocykle_staging
WHERE cena >=0 AND pojemnosc > 0;
  

6. Przykłady raportów i zapytań kontrolnych (przydatne po wdrożeniu)

Krótko — przykłady zapytań, które warto mieć w arsenale administratora / analityka.

SELECT DATE(data_zamowienia) AS dzien, COUNT(*) AS liczba_zamowien, SUM(wartosc) AS przychod
FROM Zamowienie
WHERE data_zamowienia >= CURDATE() - INTERVAL 30 DAY
GROUP BY DATE(data_zamowienia)
ORDER BY DATE(data_zamowienia) DESC;
  

SELECT id_moto, marka, model, ilosc_magazyn
FROM Motocykl
WHERE ilosc_magazyn <= 5
ORDER BY ilosc_magazyn ASC;