Przykładowa baza danych MySQL, baza szkoły

piotr

W tym wpisie przyjrzymy się przykładowemu procesowi planowania bazy danych, na przykładzie bazy szkoły. Przejdziemy przez proces planowania i tworzenia tabel w bazie danych i uzupełnimy te tabele przykładowymi danymi. Powstaną tutaj też relacje, klucze główne w tabelach i klucze obce.

 

Planowanie struktury bazy danych

Nasza baza danych, będzie zawierała takie tabele jak Uczniowie, Nauczyciele, Klasy, Predmioty, Oceny, Obecnosci.

Przejdźmy więc do wstępnego układania poszczególnych tabel

Uczniowie – tabela zawiera podstawowe dane ucznia.

ID_ucznia (klucz główny)
Imie
Nazwisko
Data_urodzenia
Adres
ID_klasy (klucz obcy)

Tabela po zmianie, przyjmie więc strukturę:

Uczniowie:
ID_ucznia (klucz główny)
Imie
Nazwisko
Data_urodzenia
Ulica
Numer_domu
Numer_mieszkania
Kod_pocztowy
Miasto
Kraj
Pesel
ID_klasy (klucz obcy)

Nauczyciele:

ID_nauczyciela (klucz główny)
Imie
Nazwisko
Przedmiot

Klasy:

ID_klasy (klucz główny)
Nazwa_klasy
Rok_rozpoczecia
ID_wychowawcy (klucz obcy)

Przedmioty:

ID_przedmiotu (klucz główny)
Nazwa_przedmiotu

Oceny:

ID_oceny (klucz główny)
Wartosc
Data
ID_ucznia (klucz obcy)
ID_przedmiotu (klucz obcy)
ID_nauczyciela (klucz obcy)

Obecnosci:

ID_obecnosci (klucz główny)
Data
Obecnosc (np. obecny, nieobecny, spóźniony)
ID_ucznia (klucz obcy)
ID_przedmiotu (klucz obcy)

Relacje w tabelach

W powyższym przykładzie zostały już zawarte planowane relacje, jakie wystąpią w tabelach naszej bazy danych szkoły.

Przyjrzyjmy się im bliżej:

  1. Uczniowie do Klasy poprzez ID_klasy: Każdy uczeń należy do jednej klasy, ale w jednej klasie może być wielu uczniów.
  2. Klasy do Nauczyciele poprzez ID_wychowawcy: Każda klasa ma jednego wychowawcę, ale jeden nauczyciel może być wychowawcą dla wielu klas.
  3. Oceny do Uczniowie, Przedmioty, Nauczyciele poprzez odpowiednie klucze obce: Ocena jest przypisana do konkretnego ucznia, nauczyciela (który wystawił ocenę) oraz przedmiotu.
  4. Obecnosci do Uczniowie i Przedmioty poprzez odpowiednie klucze obce: Informacja o obecności jest przypisana do konkretnego ucznia oraz przedmiotu.

Typy danych nadane do poszczególnych encji

Uczniowie:

  • ID_ucznia: INT (klucz główny)
  • Imie: VARCHAR(50)
  • Nazwisko: VARCHAR(50)
  • Data_urodzenia: DATE
  • Ulica: VARCHAR(100)
  • Numer_domu: VARCHAR(10)
  • Numer_mieszkania: VARCHAR(10) (może być NULL, jeśli nie dotyczy)
  • Kod_pocztowy: VARCHAR(10)
  • Miasto: VARCHAR(50)
  • Kraj: VARCHAR(50)
  • Pesel: CHAR(11) (PESEL ma stałą długość 11 znaków)
  • ID_klasy: INT (klucz obcy)

Nauczyciele:

  • ID_nauczyciela: INT (klucz główny)
  • Imie: VARCHAR(50)
  • Nazwisko: VARCHAR(50)
  • Przedmiot: VARCHAR(50)

Klasy:

  • ID_klasy: INT (klucz główny)
  • Nazwa_klasy: VARCHAR(20)
  • Rok_rozpoczecia: YEAR
  • ID_wychowawcy: INT (klucz obcy)

Przedmioty:

  • ID_przedmiotu: INT (klucz główny)
  • Nazwa_przedmiotu: VARCHAR(50)

Oceny:

  • ID_oceny: INT (klucz główny)
  • Wartosc: DECIMAL(3,2) (np. 4.50 dla ocen w skali 1-5 z dwoma miejscami po przecinku)
  • Data: DATE
  • ID_ucznia: INT (klucz obcy)
  • ID_przedmiotu: INT (klucz obcy)
  • ID_nauczyciela: INT (klucz obcy)

Obecnosci:

  • ID_obecnosci: INT (klucz główny)
  • Data: DATE
  • Obecnosc: ENUM(’obecny’, 'nieobecny’, 'spóźniony’)
  • ID_ucznia: INT (klucz obcy)
  • ID_przedmiotu: INT (klucz obcy)

Przepisanie struktury bazy danych do postaci kodu MySQL

-- Tabela Uczniowie
CREATE TABLE Uczniowie (
ID_ucznia INT PRIMARY KEY AUTO_INCREMENT,
Imie VARCHAR(50),
Nazwisko VARCHAR(50),
Data_urodzenia DATE,
Ulica VARCHAR(100),
Numer_domu VARCHAR(10),
Numer_mieszkania VARCHAR(10) NULL,
Kod_pocztowy VARCHAR(10),
Miasto VARCHAR(50),
Kraj VARCHAR(50),
Pesel CHAR(11),
ID_klasy INT,
FOREIGN KEY (ID_klasy) REFERENCES Klasy(ID_klasy)
);

-- Tabela Nauczyciele
CREATE TABLE Nauczyciele (
ID_nauczyciela INT PRIMARY KEY AUTO_INCREMENT,
Imie VARCHAR(50),
Nazwisko VARCHAR(50),
Przedmiot VARCHAR(50)
);

-- Tabela Klasy
CREATE TABLE Klasy (
ID_klasy INT PRIMARY KEY AUTO_INCREMENT,
Nazwa_klasy VARCHAR(20),
Rok_rozpoczecia YEAR,
ID_wychowawcy INT,
FOREIGN KEY (ID_wychowawcy) REFERENCES Nauczyciele(ID_nauczyciela)
);

-- Tabela Przedmioty
CREATE TABLE Przedmioty (
ID_przedmiotu INT PRIMARY KEY AUTO_INCREMENT,
Nazwa_przedmiotu VARCHAR(50)
);

-- Tabela Oceny
CREATE TABLE Oceny (
ID_oceny INT PRIMARY KEY AUTO_INCREMENT,
Wartosc DECIMAL(3,2),
Data DATE,
ID_ucznia INT,
ID_przedmiotu INT,
ID_nauczyciela INT,
FOREIGN KEY (ID_ucznia) REFERENCES Uczniowie(ID_ucznia),
FOREIGN KEY (ID_przedmiotu) REFERENCES Przedmioty(ID_przedmiotu),
FOREIGN KEY (ID_nauczyciela) REFERENCES Nauczyciele(ID_nauczyciela)
);

-- Tabela Obecnosci
CREATE TABLE Obecnosci (
ID_obecnosci INT PRIMARY KEY AUTO_INCREMENT,
Data DATE,
Obecnosc ENUM('obecny', 'nieobecny', 'spóźniony'),
ID_ucznia INT,
ID_przedmiotu INT,
FOREIGN KEY (ID_ucznia) REFERENCES Uczniowie(ID_ucznia),
FOREIGN KEY (ID_przedmiotu) REFERENCES Przedmioty(ID_przedmiotu)
);

 

Dodawanie przykładowych danych do bazy danych

Przykładowe 25 rekordów, jakie dodamy do tabeli Uczniowie:

INSERT INTO Uczniowie (Imie, Nazwisko, Data_urodzenia, Ulica, Numer_domu, Numer_mieszkania, Kod_pocztowy, Miasto, Kraj, Pesel, ID_klasy) VALUES
('Anna', 'Kowalczyk', '2000-04-16', 'Słoneczna', '73', '10', '44-552', 'Poznań', 'Polska', '18244264028', 6),
('Katarzyna', 'Kowalski', '2000-05-21', 'Zielona', '37', '1', '65-274', 'Poznań', 'Polska', '14894248241', 1),
('Jan', 'Lewandowski', '2002-08-31', 'Zielona', '39', '2', '82-530', 'Warszawa', 'Polska', '23133549070', 3),
('Tomasz', 'Wiśniewski', '2005-11-06', 'Polna', '98', '1', '10-244', 'Kraków', 'Polska', '15513518780', 3),
('Piotr', 'Kowalski', '2000-10-26', 'Słoneczna', '54', '6', '38-462', 'Gdańsk', 'Polska', '36713150294', 7),
('Paweł', 'Wójcik', '2007-03-02', 'Kwiatowa', '95', '8', '27-397', 'Wrocław', 'Polska', '75631163522', 2),
('Piotr', 'Nowak', '2008-11-25', 'Kwiatowa', '48', '9', '58-769', 'Warszawa', 'Polska', '34231197757', 8),
('Marcin', 'Szymański', '2005-11-20', 'Zielona', '74', '3', '32-150', 'Wrocław', 'Polska', '60121827121', 10),
('Maria', 'Dąbrowski', '2001-03-02', 'Polna', '35', '10', '27-998', 'Poznań', 'Polska', '25198954282', 7),
('Jan', 'Lewandowski', '2004-04-09', 'Leśna', '77', '4', '76-878', 'Gdańsk', 'Polska', '45577511016', 8),
('Piotr', 'Szymański', '2008-12-19', 'Polna', '83', '4', '73-736', 'Warszawa', 'Polska', '66183232214', 10),
('Marcin', 'Zieliński', '2000-02-02', 'Leśna', '76', '3', '92-114', 'Kraków', 'Polska', '90678359834', 5),
('Anna', 'Nowak', '2001-10-08', 'Kwiatowa', '49', '2', '44-812', 'Wrocław', 'Polska', '87730653285', 9),
('Maria', 'Zieliński', '2000-05-21', 'Zielona', '59', '4', '81-921', 'Gdańsk', 'Polska', '06230404305', 4),
('Piotr', 'Kamiński', '2002-06-24', 'Leśna', '52', '3', '40-270', 'Poznań', 'Polska', '56213235482', 1);
('Jan', 'Nowak', '2008-12-28', 'Słoneczna', '16', '2', '42-417', 'Poznań', 'Polska', '28529141272', 5),
('Agnieszka', 'Szymański', '2004-06-10', 'Zielona', '88', '4', '10-609', 'Kraków', 'Polska', '62979646078', 8),
('Anna', 'Kamiński', '2003-06-23', 'Słoneczna', '92', '4', '13-791', 'Kraków', 'Polska', '85544230188', 5),
('Katarzyna', 'Dąbrowski', '2007-09-18', 'Kwiatowa', '53', '6', '39-175', 'Poznań', 'Polska', '40274951949', 8),
('Barbara', 'Kowalski', '2000-07-14', 'Kwiatowa', '39', '10', '97-766', 'Poznań', 'Polska', '32126649525', 2),
('Tomasz', 'Kowalczyk', '2001-12-26', 'Polna', '9', '2', '48-774', 'Gdańsk', 'Polska', '47344443410', 4),
('Paweł', 'Wójcik', '2007-07-29', 'Kwiatowa', '22', '6', '82-670', 'Gdańsk', 'Polska', '19135636356', 10),
('Katarzyna', 'Szymański', '2004-12-06', 'Kwiatowa', '87', '1', '83-947', 'Kraków', 'Polska', '74671969054', 2),
('Katarzyna', 'Zieliński', '2001-03-19', 'Zielona', '56', '5', '78-294', 'Poznań', 'Polska', '34732275331', 6),
('Barbara', 'Zieliński', '2002-05-12', 'Słoneczna', '60', '7', '65-146', 'Warszawa', 'Polska', '26287066810', 3);

Przykładowe rekordy, w tabeli Nauczyciele:

INSERT INTO Nauczyciele (Imie, Nazwisko, Przedmiot) VALUES
('Robert', 'Szczepańska', 'Matematyka'),
('Krzysztof', 'Skowron', 'Chemia'),
('Marta', 'Mazurek', 'Biologia'),
('Monika', 'Jaworski', 'W-F'),
('Jacek', 'Borowski', 'Historia'),
('Magdalena', 'Zatorski', 'Chemia'),
('Krzysztof', 'Borowski', 'Historia'),
('Marta', 'Skowron', 'Polski'),
('Monika', 'Skowron', 'Muzyka'),
('Michał', 'Małek', 'Biologia'),
('Krzysztof', 'Ostrowska', 'Polski'),
('Michał', 'Piechota', 'Polski'),
('Marta', 'Mazurek', 'Historia'),
('Robert', 'Ostrowska', 'Biologia'),
('Marta', 'Szczepańska', 'Historia');

Przykładowe dane w tabeli Klasy:

INSERT INTO Klasy (Nazwa_klasy, Rok_rozpoczecia, ID_wychowawcy) VALUES
('IIIc', 2019, 14),
('VIe', 2019, 4),
('IVd', 2020, 7),
('VIb', 2021, 2),
('Id', 2021, 11);

Zmiany w tabelach naszej bazy danych

W trakcie tworzenia przykładowych rekordów, zauważamy, że przydała by się zmiana. Otóż w tabeli przewidzieliśmy na wstępie ID_wychowawcy. Warto przemyśleć zmianę, polegającą na wprowadzeniu oddzielnie tabeli Wychowawcy.

Aktualny kod:

CREATE TABLE Klasy (
ID_klasy INT PRIMARY KEY AUTO_INCREMENT,
Nazwa_klasy VARCHAR(20),
Rok_rozpoczecia YEAR,
ID_wychowawcy INT,
FOREIGN KEY (ID_wychowawcy) REFERENCES Nauczyciele(ID_nauczyciela)
);

Kod wprowadzający zmiany do naszej bazy danych:

Tworzymy nową tabelę Wychowawcy

— Tabela Wychowawcy
CREATE TABLE Wychowawcy (
ID_wychowawcy INT PRIMARY KEY AUTO_INCREMENT,
ID_nauczyciela INT,
-- Możesz dodać dodatkowe pola specyficzne dla wychowawców
FOREIGN KEY (ID_nauczyciela) REFERENCES Nauczyciele(ID_nauczyciela)
);

Korzystamy tutaj z kwerendy Alter Table, dla tabeli klasy, gdzie usuwamy klucz obcy

— Aktualizacja tabeli Klasy
ALTER TABLE Klasy
DROP FOREIGN KEY ID_wychowawcy; -- Wymaga podania nazwy klucza obcego do usunięcia
ALTER TABLE Klasy
ADD FOREIGN KEY (ID_wychowawcy) REFERENCES Wychowawcy(ID_wychowawcy);