Moduł: Tworzenie i modyfikowanie baz danych

1. Tworzenie tabel

Cel: Uczeń potrafi utworzyć tabele w systemie zarządzania bazą danych (MS Access / MySQL).
Zakres:
• definiowanie nazw tabel i pól,
• określanie typów danych,
• ustawianie klucza głównego,
• tworzenie relacji między tabelami.

Przykładowe ćwiczenie:
Utwórz tabelę Klienci zawierającą pola: ID_Klienta, Imię, Nazwisko, Adres, Miasto, Kod_Pocztowy, Email.
Ustaw klucz główny dla ID_Klienta.

CREATE TABLE Klienci (
    ID_Klienta INT AUTO_INCREMENT PRIMARY KEY,
    Imie VARCHAR(50),
    Nazwisko VARCHAR(50),
    Adres VARCHAR(100),
    Miasto VARCHAR(50),
    Kod_Pocztowy VARCHAR(10),
    Email VARCHAR(100)
);

<?php
// Przykład: tworzenie tabeli Klienci w bazie biblioteka
$conn = new mysqli("localhost", "root", "haslo", "biblioteka");
if ($conn->connect_error) { die("Błąd połączenia: " . $conn->connect_error); }
$sql = "CREATE TABLE Klienci (
    ID_Klienta INT AUTO_INCREMENT PRIMARY KEY,
    Imie VARCHAR(50),
    Nazwisko VARCHAR(50),
    Adres VARCHAR(100),
    Miasto VARCHAR(50),
    Kod_Pocztowy VARCHAR(10),
    Email VARCHAR(100)
)";
if ($conn->query($sql) === TRUE) {
  echo "Tabela Klienci utworzona.";
} else {
  echo "Błąd: " . $conn->error;
}
$conn->close();
?>

Tabela Klienci została poprawnie utworzona w bazie danych.

2. Tworzenie formularzy do wprowadzania danych

Cel: Uczeń potrafi zaprojektować formularze do dodawania, edycji i usuwania rekordów.
Zakres:
• projektowanie formularzy,
• powiązanie z tabelami,
• dodawanie przycisków (Zapisz, Usuń, Nowy).

Przykładowe ćwiczenie:
Utwórz formularz do wprowadzania danych o klientach.

INSERT INTO Klienci (Imie, Nazwisko, Adres, Miasto, Kod_Pocztowy, Email)
VALUES ('Jan', 'Kowalski', 'ul. Przykładowa 1', 'Kraków', '30-001', 'jan@example.com');

<?php
// Formularz + zapis do tabeli Klienci w biblioteka
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $conn = new mysqli("localhost","root","haslo","biblioteka");
    if ($conn->connect_error) { die("Błąd: " . $conn->connect_error); }
    $imie = $conn->real_escape_string($_POST['imie']);
    $nazwisko = $conn->real_escape_string($_POST['nazwisko']);
    $adres = $conn->real_escape_string($_POST['adres']);
    $miasto = $conn->real_escape_string($_POST['miasto']);
    $kod = $conn->real_escape_string($_POST['kod']);
    $email = $conn->real_escape_string($_POST['email']);
    $sql = "INSERT INTO Klienci (Imie,Nazwisko,Adres,Miasto,Kod_Pocztowy,Email)
            VALUES ('$imie','$nazwisko','$adres','$miasto','$kod','$email')";
    $conn->query($sql);
    echo "Dane klienta zapisane.";
    $conn->close();
}
?>

<form method="POST">
Imię: <input type="text" name="imie"><br>
Nazwisko: <input type="text" name="nazwisko"><br>
Adres: <input type="text" name="adres"><br>
Miasto: <input type="text" name="miasto"><br>
Kod pocztowy: <input type="text" name="kod"><br>
Email: <input type="email" name="email"><br>
<button type="submit">Zapisz</button>
</form>

Formularz umożliwia dodawanie nowych rekordów do tabeli Klienci.

3. Rodzaje kwerend w SZBD

Cel: Poznanie różnych typów kwerend i ich zastosowania.
Zakres: kwerendy wybierające, aktualizujące, usuwające, dołączające, tworzące tabelę, parametryczne.

Przykładowe ćwiczenie: Utwórz kwerendę wybierającą klientów z miasta „Kraków”.

-- Wybierająca
SELECT * FROM Klienci WHERE Miasto = 'Kraków';

-- Aktualizująca
UPDATE Klienci SET Miasto = 'Kraków' WHERE Kod_Pocztowy = '30-001';

-- Usuwająca
DELETE FROM Klienci WHERE Email LIKE '%@spam.pl';

<?php
$conn = new mysqli("localhost","root","haslo","biblioteka");
if ($conn->connect_error) die("Błąd: ".$conn->connect_error);

// Przykład: selekcja klientów z Krakowa
$result = $conn->query("SELECT * FROM Klienci WHERE Miasto='Kraków'");
while($row = $result->fetch_assoc()){
  echo $row['Imie']." ".$row['Nazwisko']." — ".$row['Email']."<br>";
}

// Aktualizacja przykładowa
$conn->query("UPDATE Klienci SET Miasto='Kraków' WHERE Kod_Pocztowy='30-001'");

// Usuwanie przykładowe
$conn->query("DELETE FROM Klienci WHERE Email LIKE '%@spam.pl'");

$conn->close();
?>

Przykładowe kwerendy można wykonać z poziomu PHP lub narzędzia SQL.

4. Tworzenie zapytań do bazy danych

Cel: Tworzenie zapytań SQL (SELECT, INSERT, UPDATE, DELETE), warunki, sortowanie, agregaty i grupowanie.

Przykładowe ćwiczenie: Napisz zapytanie SQL wyświetlające średnią wartość zamówienia w tabeli Zamowienia.

SELECT AVG(Wartosc) AS SredniaZamowien FROM Zamowienia;

<?php
$conn = new mysqli("localhost","root","haslo","biblioteka");
$row = $conn->query("SELECT AVG(Wartosc) AS SredniaZamowien FROM Zamowienia")->fetch_assoc();
echo "Średnia wartość zamówienia: " . $row['SredniaZamowien'];
$conn->close();
?>

Zapytania analityczne (AVG, SUM, COUNT) pozwalają tworzyć podsumowania potrzebne w raportach.

5. Tworzenie i formatowanie raportów

Cel: Przygotowanie raportów z danych bazy: grupowanie, sumy częściowe, formatowanie.

Przykładowe ćwiczenie: Utwórz raport przedstawiający liczbę zamówień dla każdego klienta.

SELECT ID_Klienta, COUNT(*) AS LiczbaZamowien
FROM Zamowienia
GROUP BY ID_Klienta;

<?php
$conn = new mysqli("localhost","root","haslo","biblioteka");
$res = $conn->query("SELECT ID_Klienta, COUNT(*) AS LiczbaZamowien FROM Zamowienia GROUP BY ID_Klienta");
echo "<table class='effect-table'><tr><th>ID Klienta</th><th>Liczba zamówień</th></tr>";
while($r = $res->fetch_assoc()){
  echo "<tr><td>{$r['ID_Klienta']}</td><td>{$r['LiczbaZamowien']}</td></tr>";
}
echo "</table>";
$conn->close();
?>

Raport generowany dynamicznie z wykorzystaniem SQL + PHP.

6. Modyfikowanie struktury baz danych

Cel: Zmiana struktury istniejących tabel — dodawanie/usuwanie pól, zmiana typów, właściwości pól.

Przykładowe ćwiczenie: Dodaj pole Telefon do tabeli Klienci.

ALTER TABLE Klienci ADD Telefon VARCHAR(15);

<?php
$conn = new mysqli("localhost","root","haslo","biblioteka");
if ($conn->query("ALTER TABLE Klienci ADD Telefon VARCHAR(15)") === TRUE) {
  echo "Dodano pole Telefon.";
} else {
  echo "Błąd: " . $conn->error;
}
$conn->close();
?>

Po modyfikacji struktury warto wykonać kopię zapasową i przetestować aplikacje.

7. Analizowanie i modyfikacja struktury bazy danych

Cel: Identyfikacja problemów strukturalnych, normalizacja danych, usuwanie redundancji.

Przykładowe ćwiczenie: Sprawdź, czy baza spełnia 3NF i zaproponuj poprawki.

-- Przykładowa normalizacja: wyodrębnienie tabeli Miasta
CREATE TABLE Miasta (
  ID_Miasta INT AUTO_INCREMENT PRIMARY KEY,
  Nazwa VARCHAR(50)
);
ALTER TABLE Klienci ADD ID_Miasta INT;
-- Następnie uzupełnić ID_Miasta i usunąć pole Miasto z Klienci

<?php
// Prosty skrypt pomocniczy: listowanie powtórzeń wartości Miasto
$conn = new mysqli("localhost","root","haslo","biblioteka");
$res = $conn->query("SELECT Miasto, COUNT(*) AS Ile FROM Klienci GROUP BY Miasto HAVING Ile > 1");
while($r = $res->fetch_assoc()){
  echo $r['Miasto']." — ".$r['Ile']." rekordy<br>";
}
$conn->close();
?>

Analiza powtórzeń i zależności ułatwia decyzję o normalizacji.

8. Rozbudowa struktury bazy danych

Cel: Rozszerzenie bazy o nowe tabele, relacje i atrybuty (klucze obce).

Przykładowe ćwiczenie: Dodaj tabelę Faktury i połącz ją relacją z Zamowienia.

CREATE TABLE Faktury (
  ID_Faktury INT AUTO_INCREMENT PRIMARY KEY,
  Data DATE,
  Kwota DECIMAL(10,2),
  ID_Zamowienia INT,
  FOREIGN KEY (ID_Zamowienia) REFERENCES Zamowienia(ID_Zamowienia)
);

<?php
$conn = new mysqli("localhost","root","haslo","biblioteka");
$sql = "CREATE TABLE Faktury (
  ID_Faktury INT AUTO_INCREMENT PRIMARY KEY,
  Data DATE,
  Kwota DECIMAL(10,2),
  ID_Zamowienia INT,
  FOREIGN KEY (ID_Zamowienia) REFERENCES Zamowienia(ID_Zamowienia)
)";
if ($conn->query($sql) === TRUE) {
  echo "Tabela Faktury utworzona.";
} else {
  echo "Błąd: " . $conn->error;
}
$conn->close();
?>

Rozbudowa struktury powinna zachować integralność referencyjną (ON DELETE/UPDATE).

9. Usuwanie elementów struktury bazy danych

Cel: Bezpieczne usuwanie tabel, pól i relacji; tworzenie kopii zapasowych przed usunięciem.

Przykładowe ćwiczenie: Usuń tabelę TymczasoweZamowienia, zachowując integralność danych.

-- Najpierw sprawdzić zależności, potem:
DROP TABLE IF EXISTS TymczasoweZamowienia;

<?php
// Usuwanie tabeli po upewnieniu się, że nie ma zależności
$conn = new mysqli("localhost","root","haslo","biblioteka");
if ($conn->query("DROP TABLE IF EXISTS TymczasoweZamowienia") === TRUE) {
  echo "Tabela usunięta.";
} else {
  echo "Błąd: " . $conn->error;
}
$conn->close();
?>

Przed usunięciem wykonaj kopię zapasową (mysqldump) i sprawdź FK.

10. Modyfikowanie struktury bazy danych oraz danych

Cel: Wprowadzanie zmian zarówno w strukturze, jak i w danych; optymalizacja po zmianach.

Przykładowe ćwiczenie: Zmień typ pola Cena w tabeli Produkty na DECIMAL(10,2) i zaktualizuj ceny o 10%.

ALTER TABLE Produkty MODIFY Cena DECIMAL(10,2);
UPDATE Produkty SET Cena = ROUND(Cena * 1.10, 2);

<?php
$conn = new mysqli("localhost","root","haslo","biblioteka");
if ($conn->query("ALTER TABLE Produkty MODIFY Cena DECIMAL(10,2)") === TRUE) {
  echo "Typ pola Cena zmieniony.<br>";
} else {
  echo "Błąd: ".$conn->error."<br>";
}
if ($conn->query("UPDATE Produkty SET Cena = ROUND(Cena * 1.10, 2)") === TRUE) {
  echo "Ceny zaktualizowane o 10%.";
} else {
  echo "Błąd aktualizacji: ".$conn->error;
}
$conn->close();
?>

Po modyfikacjach uruchom EXPLAIN dla ciężkich zapytań i dodaj indeksy jeśli potrzeba.

Moduł: Tworzenie i modyfikowanie baz danych – INF.03