GgsaJgYW

1-- -- -------------------------------------------------------------------------------
2-- -- Zadanie 1
3-- -- -------------------------------------------------------------------------------
4
5-- -- -------------------------------------------------------------------------------
6-- Section: setting sql_mode
7-- -- -------------------------------------------------------------------------------
8-- Key settings:
9-- ONLY_FULL_GROUP_BY = gwarantuje swiadome deklarowanie sekcji GROUP BY
10-- STRICT_ALL_TABLES = gwarantuje, że wstawicie odpowiednie typy/długości danych w odpowiednie miejsca
11-- 	DB automatycznie nie będzie konwertował typów ani nie ucinał długości znaków.
12-- 	Czyli nie bedzie mozliwe wstawienie stringa '10/24' do pola INT(10) ani stringa
13-- 	o dlugosci 100 znakow w pole VARCHAR(10). Przy wlaczonym STRICT_ALL_TABLES pojawi sie Error
14-- 	a nie Warning.
15-- SQL Modes: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
16-- -- -------------------------------------------------------------------------------
17
18SET sql_mode='ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
19
20-- -- -------------------------------------------------------------------------------
21-- Section: DROP DATABASE lub DROP Tables
22-- -- -------------------------------------------------------------------------------
23-- Tutaj usuwamy całą bazę przed jej zadeklarowaniem. To pozwoli Wam łatwo resetować
24-- bazę danych w trakcie testów.
25-- -- -------------------------------------------------------------------------------
26
27DROP DATABASE IF EXISTS jsk_db_mariusz_cyris;
28
29-- -- -------------------------------------------------------------------------------
30-- Section: CREATE DATABASE/SCHEMA
31-- -- -------------------------------------------------------------------------------
32CREATE DATABASE jsk_db_mariusz_cyris DEFAULT CHARACTER SET UTF8MB4 ;
33
34-- -- -------------------------------------------------------------------------------
35-- Section: USE
36-- Zamien 'jsk_db_jan_kowalski' na poprawna nazwe Twojej bazy danych
37-- Format nazwy DB: jsk_db_[imie]_[nazwisko]
38-- -- -------------------------------------------------------------------------------
39USE jsk_db_mariusz_cyris;
40	
41-- -- -------------------------------------------------------------------------------
42-- Section: CREATE
43-- -- -------------------------------------------------------------------------------
44-- Tutaj tworzymy nasze tabele bez kluczy obcych. Definiujemy tylko i wyłącznie tabele.
45-- W sekcji niżej należy pododawać relacje między tabelami. To daje większą przejrzystość.
46-- Pola, do ktorych dodamy klucze w sekcji 'ALTER TABLE', powinny znalezc sie juz w naszych DDL
47-- -- -------------------------------------------------------------------------------
48CREATE TABLE branch (
49	branch_id INT NOT NULL AUTO_INCREMENT,
50    branch_city VARCHAR(50) NOT NULL,
51    branch_street VARCHAR(50) NOT NULL,
52    branch_phone_number VARCHAR(15) NOT NULL,
53    branch_email VARCHAR(50) NOT NULL,
54    PRIMARY KEY(branch_id)
55);
56-- ENGINE = InnoDB
57-- DEFAULT CHARACTER SET = utf8mb4;
58
59CREATE TABLE employee (
60	employee_id INT NOT NULL AUTO_INCREMENT,
61    employee_name VARCHAR(30) NOT NULL,
62    employee_surname VARCHAR(30) NOT NULL,
63    employee_age INT(3) NOT NULL,
64    employee_occupation ENUM('Seller', 'Manager', 'Accountant') NOT NULL,
65    employee_email VARCHAR(50) NOT NULL,
66    branch_id INT NOT NULL,
67    PRIMARY KEY(employee_id),
68    CONSTRAINT fk_employee_branch FOREIGN KEY (branch_id) REFERENCES branch (branch_id)
69);
70-- ENGINE = InnoDB
71-- DEFAULT CHARACTER SET = utf8mb4;
72
73CREATE TABLE customer (
74	customer_id INT NOT NULL AUTO_INCREMENT,
75    customer_name VARCHAR(30) NOT NULL,
76    customer_surname VARCHAR(30) NOT NULL,
77    customer_date_of_birth date NOT NULL,
78    customer_phone_number VARCHAR(15) NOT NULL,
79    customer_card_number VARCHAR(20) NOT NULL,
80    customer_email VARCHAR(50) NOT NULL,
81    PRIMARY KEY(customer_id)
82);
83-- ENGINE = InnoDB
84-- DEFAULT CHARACTER SET = utf8mb4;
85
86CREATE TABLE car (
87	car_id INT NOT NULL AUTO_INCREMENT,
88    car_type ENUM('Passenger', 'Delivery', 'Truck') NOT NULL,
89    car_brand ENUM('Ford', 'Fiat', 'Opel', 'Citroen', 'Mercedes', 'Skoda') NOT NULL,
90    car_color VARCHAR(20) NOT NULL,
91    car_engine_capacity VARCHAR(7) NOT NULL,
92    car_mileage INT(15) NOT NULL,
93    -- employee_id INT NOT NULL,
94	PRIMARY KEY(car_id)
95);
96-- ENGINE = InnoDB
97-- DEFAULT CHARACTER SET = utf8mb4;
98
99CREATE TABLE rental (
100	rental_id INT NOT NULL AUTO_INCREMENT,
101    rental_time_from date NOT NULL,
102    rental_time_to date NOT NULL,
103    rental_price INT(20) NOT NULL,
104    rental_car_id INT NOT NULL,
105    rental_customer_id INT NOT NULL,
106    rental_pickup_branch_id INT NOT NULL,
107    rental_return_branch_id INT NOT NULL,
108    PRIMARY KEY(rental_id),
109    CONSTRAINT fk_rental_car FOREIGN KEY (rental_car_id) REFERENCES car (car_id),
110    CONSTRAINT fk_rental_customer FOREIGN KEY (rental_customer_id) REFERENCES customer (customer_id),
111    CONSTRAINT fk_rental_pickup FOREIGN KEY (rental_pickup_branch_id) REFERENCES branch (branch_id),
112    CONSTRAINT fk_rental_return FOREIGN KEY (rental_return_branch_id) REFERENCES branch (branch_id)
113);
114-- ENGINE = InnoDB
115-- DEFAULT CHARACTER SET = utf8mb4;
116
117CREATE TABLE car2employee (
118	car_employee_id INT NOT NULL AUTO_INCREMENT,
119    car_id INT(15) NOT NULL,
120    employee_id INT(15) NOT NULL,
121    PRIMARY KEY(car_employee_id),
122    CONSTRAINT fk_car2employee_car FOREIGN KEY (car_id) REFERENCES car (car_id),
123    CONSTRAINT fk_car2employee_employee FOREIGN KEY (employee_id) REFERENCES employee (employee_id)
124);	-- ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
125-- -- -------------------------------------------------------------------------------
126-- Section: ALTER TABLE
127-- -- -------------------------------------------------------------------------------
128-- Tutaj dodajemy klucze obce oraz ewentualne zmiany w tabelach.
129-- -- -------------------------------------------------------------------------------