Moduł 7 – Projekt: Baza danych Wypożyczalnia Samochodów
Opis projektu
Baza danych Wypożyczalnia Samochodów modeluje działalność firmy wynajmującej pojazdy. Zawiera informacje o klientach, samochodach, pracownikach, oddziałach i transakcjach wypożyczenia.
Schemat bazy danych
MIEJSCA
NR_MIEJSCA (PK)
ULICA, NUMER, MIASTO, KOD, TELEFON, UWAGI
│
│ 1:M 1:M
├────────────────────────────────────────────────────────────┐
│ │
PRACOWNICY WYPOZYCZENIA
NR_PRACOWNIKA (PK) NR_WYPOZYCZENIA (PK)
IMIE, NAZWISKO, DATA_ZATR NR_KLIENTA ────> KLIENCI
DZIAL, STANOWISKO, PENSJA, DODATEK NR_SAMOCHODU ────> SAMOCHODY
NR_MIEJSCA (FK) ───────────────────────────────────> NR_PRACOW_WYP ────> PRACOWNICY
NR_PRACOW_ODD ────> PRACOWNICY
NR_MIEJSCA_WYP ────> MIEJSCA
NR_MIEJSCA_ODD ────> MIEJSCA
DATA_WYP, DATA_ODD
KAUCJA, CENA_JEDN
Tabele
KLIENCI
Przechowuje dane klientów wypożyczalni.
| Kolumna | Typ | Opis |
|---|---|---|
| NR_KLIENTA | CHAR(8) | Klucz główny |
| IMIE | VARCHAR(20) | Imię klienta |
| NAZWISKO | VARCHAR(20) | Nazwisko klienta |
| NR_KARTY_KREDYT | CHAR(20) | Numer karty (może być NULL) |
| FIRMA | VARCHAR(40) | Nazwa firmy (może być NULL) |
| ULICA | VARCHAR(24) | Ulica zamieszkania |
| NUMER | CHAR(8) | Numer domu/mieszkania |
| MIASTO | VARCHAR(24) | Miasto |
| KOD | CHAR(6) | Kod pocztowy |
| NIP | CHAR(12) | NIP firmy (może być NULL) |
| NR_TELEFONU | CHAR(16) | Numer telefonu |
SAMOCHODY
Flota samochodów dostępnych w wypożyczalni.
| Kolumna | Typ | Opis |
|---|---|---|
| NR_SAMOCHODU | CHAR(6) | Klucz główny |
| MARKA | VARCHAR(20) | Marka samochodu |
| TYP | VARCHAR(16) | Model/typ |
| ROK_PROD | DATE | Rok produkcji |
| KOLOR | VARCHAR(16) | Kolor |
| POJ_SILNIKA | SMALLINT | Pojemność silnika (cm³) |
| PRZEBIEG | INTEGER | Przebieg (km) |
Dostępne samochody w bazie:
| Marka | Modele |
|---|---|
| Fiat | Seicento, Tipo |
| Ford | Escort, Ka, Sierra |
| Mercedes | 190D, 230D |
| Opel | Corsa, Vectra |
| Polonez | Caro |
| Seat | Ibiza |
PRACOWNICY
Pracownicy obsługujący wypożyczenia.
| Kolumna | Typ | Opis |
|---|---|---|
| NR_PRACOWNIKA | CHAR(4) | Klucz główny |
| IMIE, NAZWISKO | VARCHAR(20) | Dane osobowe |
| DATA_ZATR | DATE | Data zatrudnienia |
| DZIAL | VARCHAR(20) | Dział: OBSLUGA KLIENTA, TECHNICZNY, KSIEGOWOSC |
| STANOWISKO | VARCHAR(20) | Stanowisko: SPRZEDAWCA, KIEROWNIK, MECHANIK, KASJER |
| PENSJA | DECIMAL(8,2) | Wynagrodzenie podstawowe |
| DODATEK | DECIMAL(8,2) | Dodatek do pensji (może być NULL) |
| NR_MIEJSCA | CHAR(6) | Oddział (FK → MIEJSCA) |
| NR_TELEFONU | CHAR(16) | Numer telefonu |
MIEJSCA
Oddziały/punkty wypożyczalni.
| Kolumna | Typ | Opis |
|---|---|---|
| NR_MIEJSCA | CHAR(6) | Klucz główny |
| ULICA, NUMER | VARCHAR/CHAR | Adres oddziału |
| MIASTO | VARCHAR(24) | Miasto: Warszawa, Wrocław, Kraków, Poznań |
| KOD | CHAR(6) | Kod pocztowy |
| TELEFON | CHAR(16) | Telefon oddziału |
| UWAGI | VARCHAR(40) | Dodatkowe uwagi |
WYPOZYCZENIA
Tabela faktów – każdy rekord to jedno wypożyczenie.
| Kolumna | Typ | Opis |
|---|---|---|
| NR_WYPOZYCZENIA | CHAR(8) | Klucz główny |
| NR_KLIENTA | CHAR(8) | FK → KLIENCI |
| NR_SAMOCHODU | CHAR(6) | FK → SAMOCHODY |
| NR_PRACOW_WYP | CHAR(4) | FK → PRACOWNICY (wydający auto) |
| NR_PRACOW_ODD | CHAR(4) | FK → PRACOWNICY (przyjmujący zwrot, może być NULL) |
| NR_MIEJSCA_WYP | CHAR(6) | FK → MIEJSCA (miejsce wydania) |
| NR_MIEJSCA_ODD | CHAR(6) | FK → MIEJSCA (miejsce oddania, może być NULL) |
| DATA_WYP | DATE | Data wypożyczenia |
| DATA_ODD | DATE | Data oddania (NULL = nie oddano) |
| KAUCJA | DECIMAL(8,2) | Kaucja (może być NULL) |
| CENA_JEDN | DECIMAL(8,2) | Cena jednostkowa za dobę |
Relacje w bazie
| Tabela nadrzędna | Tabela podrzędna | Kolumna łącząca | Typ relacji |
|---|---|---|---|
| KLIENCI | WYPOZYCZENIA | NR_KLIENTA | 1:M |
| SAMOCHODY | WYPOZYCZENIA | NR_SAMOCHODU | 1:M |
| PRACOWNICY | WYPOZYCZENIA | NR_PRACOW_WYP | 1:M |
| PRACOWNICY | WYPOZYCZENIA | NR_PRACOW_ODD | 1:M |
| MIEJSCA | WYPOZYCZENIA | NR_MIEJSCA_WYP | 1:M |
| MIEJSCA | WYPOZYCZENIA | NR_MIEJSCA_ODD | 1:M |
| MIEJSCA | PRACOWNICY | NR_MIEJSCA | 1:M |
Zadania projektowe
Poziom podstawowy
- Wyświetl wszystkich klientów prywatnych (bez firmy).
- Wyświetl samochody, które nie były nigdy wypożyczone.
- Wyświetl klientów, którzy aktualnie mają wypożyczony samochód (brak daty oddania).
Poziom średni
- Oblicz koszt każdego wypożyczenia:
(data_odd - data_wyp) * cena_jedn. - Który klient wydał łącznie najwięcej na wypożyczenia?
- Wyświetl ranking samochodów wg liczby wypożyczeń.
Poziom zaawansowany
- Wyświetl pracowników, którzy obsłużyli więcej niż 3 wypożyczenia.
- Znajdź klientów, którzy wypożyczali samochody w więcej niż jednym mieście.
- Wyświetl samochody, których łączny przebieg w wypożyczeniach jest nieznany (nie mamy dat oddania).
Wskazówki do zadań projektowych
-- 1. Klienci prywatni
SELECT IMIE, NAZWISKO FROM KLIENCI WHERE FIRMA IS NULL;
-- 2. Samochody nigdy nie wypożyczone (LEFT JOIN + IS NULL)
SELECT s.MARKA, s.TYP
FROM SAMOCHODY s
LEFT JOIN WYPOZYCZENIA w ON s.NR_SAMOCHODU = w.NR_SAMOCHODU
WHERE w.NR_WYPOZYCZENIA IS NULL;
-- 3. Aktualnie wypożyczone (brak daty oddania)
SELECT k.IMIE, k.NAZWISKO, s.MARKA, s.TYP, w.DATA_WYP
FROM KLIENCI k
JOIN WYPOZYCZENIA w ON k.NR_KLIENTA = w.NR_KLIENTA
JOIN SAMOCHODY s ON w.NR_SAMOCHODU = s.NR_SAMOCHODU
WHERE w.DATA_ODD IS NULL;
-- 4. Koszt wypożyczenia
SELECT NR_WYPOZYCZENIA,
DATEDIFF(DATA_ODD, DATA_WYP) AS DNI,
CENA_JEDN,
DATEDIFF(DATA_ODD, DATA_WYP) * CENA_JEDN AS KOSZT
FROM WYPOZYCZENIA
WHERE DATA_ODD IS NOT NULL;
-- 7. Pracownicy z > 3 wypożyczeniami
SELECT p.IMIE, p.NAZWISKO, COUNT(*) AS LICZBA
FROM PRACOWNICY p
JOIN WYPOZYCZENIA w ON p.NR_PRACOWNIKA = w.NR_PRACOW_WYP
GROUP BY p.NR_PRACOWNIKA, p.IMIE, p.NAZWISKO
HAVING COUNT(*) > 3;
Poprzedni moduł: Funkcje agregujące Wróć do: Spis treści