====== Projekt logiczny ======

===== Doprecyzowanie struktury bazy =====

==== Schemat ====

{{:pl:dydaktyka:ztb:2011:projekty:osk:schemat_ztb.png|}}

==== Kod SQL ====
<code>
CREATE TABLE address (
                id integer NOT NULL,
                street VARCHAR(64),
                house_number VARCHAR(10) NOT NULL,
                flat_number VARCHAR(10),
                town VARCHAR(64) NOT NULL,
                postal_code VARCHAR(6) NOT NULL,
                post_office varchar(64) not null
        );

ALTER TABLE address ADD CONSTRAINT address_pk PRIMARY KEY (id);

CREATE TABLE users (
                id integer NOT NULL,
                username VARCHAR(32) not null,
                pass_sha256 char(64) not null,
                pass_salt char(64) not null,
                code_sha256 char(64),
                email varchar(128) not null,
                phone varchar(16),
                mobile varchar(16),
                status integer not null,
                first_name VARCHAR(64),
                last_name VARCHAR(64),
                pesel char(11),
                birth_date DATE,
                nationality VARCHAR(64),
                home_address integer not null,
                mailing_address integer,
                register_date timestamp,
                last_login_date timestamp
        );

ALTER TABLE users ADD CONSTRAINT users_pk PRIMARY KEY (id);

ALTER TABLE users ADD CONSTRAINT users_home_address_fk FOREIGN KEY (home_address)
        REFERENCES address (id);

ALTER TABLE users ADD CONSTRAINT users_mailing_address_fk FOREIGN KEY (mailing_address)
        REFERENCES address (id);
       
CREATE TABLE "payments_status" (
        "id" integer NOT NULL,
        "description" varchar(200) NOT NULL
);

ALTER TABLE "payments_status" ADD CONSTRAINT "payments_status_pk" PRIMARY KEY ("id");

CREATE TABLE "students" (
        "user_id" integer NOT NULL
);

ALTER TABLE "students" ADD CONSTRAINT "students_pk" PRIMARY KEY ("user_id");

ALTER TABLE "students" ADD CONSTRAINT "students_user_id_fk" FOREIGN KEY ("user_id")
        REFERENCES "users" ("id");
       
CREATE TABLE "payments" (
        "id" integer NOT NULL,
        "amount" numeric(9,2) NOT NULL,
        "date" date NOT NULL,
        "student_id" integer NOT NULL,
        "accepted_by" integer NOT NULL,
        "status" integer NOT NULL,
        "due_date" date,
        "description" integer
);

ALTER TABLE "payments" ADD CONSTRAINT "payments_pk" PRIMARY KEY ("id");

ALTER TABLE "payments" ADD CONSTRAINT "payments_student_id_fk" FOREIGN KEY ("student_id")
        REFERENCES "student" ("user_id");
        
ALTER TABLE "payments" ADD CONSTRAINT "payments_status_fk" FOREIGN KEY ("status")
        REFERENCES "payments_status" ("id");
       
CREATE TABLE "categories" (
        "category_code" varchar(4) NOT NULL,
        "category_name" varchar NOT NULL
);

ALTER TABLE "categories" ADD CONSTRAINT "categories_pk" PRIMARY KEY ("category_code");

CREATE TABLE "student_categories" (
        "student_id" integer NOT NULL,
        "category_code" varchar(4) NOT NULL
);

ALTER TABLE "student_categories" ADD CONSTRAINT "student_categories_pk" PRIMARY KEY ("student_id","category_code");

ALTER TABLE "student_categories" ADD CONSTRAINT "student_categories_student_id_fk" FOREIGN KEY ("student_id")
        REFERENCES "students" ("user_id");
        
ALTER TABLE "student_categories" ADD CONSTRAINT "student_categories_category_code_fk" FOREIGN KEY ("category_code")
        REFERENCES "categories" ("category_code");
       
CREATE TABLE "courses" (
        "id" integer NOT NULL,
        "start_date" date NOT NULL,
        "type" varchar(3) NOT NULL,
        "practical_training" integer NOT NULL,
        "theoretical_training" integer NOT NULL
);
        
ALTER TABLE "courses" ADD CONSTRAINT "courses_pk" PRIMARY KEY ("id");        

CREATE TABLE "cars" (
        "id" integer NOT NULL,
        "registration_number" varchar(7) NOT NULL,
        "brand" varchar(50) NOT NULL,
        "model" varchar(50) NOT NULL,
        "colour" varchar(50) NOT NULL,
        "year" integer NOT NULL,
        "examination_date" date,
        "insurance_date" date,
        "category" varchar(4),
        "disctance" integer
);
        
ALTER TABLE "cars" ADD CONSTRAINT "cars_pk" PRIMARY KEY ("id");

ALTER TABLE "cars" ADD CONSTRAINT "cars_category_fk" FOREIGN KEY ("category")
        REFERENCES "categories" ("category_code");
       
      
CREATE TABLE "student_courses" (
        "course_id" integer NOT NULL,
        "student_id" integer NOT NULL,
        "medical_certificate" bool,
        "instructor" integer,
        "certificate_number" varchar(10),
        "end_date" date
);
                
ALTER TABLE "student_courses" ADD CONSTRAINT "student_courses_pk" PRIMARY KEY ("course_id","student_id");

ALTER TABLE "student_courses" ADD CONSTRAINT "student_courses_course_id_fk" FOREIGN KEY ("course_id")
        REFERENCES "courses" ("id");
        
ALTER TABLE "student_courses" ADD CONSTRAINT "student_courses_student_id_fk" FOREIGN KEY ("student_id")
        REFERENCES "students" ("user_id");      

ALTER TABLE "student_courses" ADD CONSTRAINT "student_courses_instructor_fk" FOREIGN KEY ("instructor")
        REFERENCES "instructors" ("user_id");     
       
CREATE TABLE "internal_exams" (
        "id" integer NOT NULL,
        "student_id" integer  NOT NULL,
        "course_id" integer  NOT NULL,
        "type" char(1) NOT NULL,
        "result" bool NOT NULL DEFAULT False,
        "date" date NOT NULL,
        "instructor" integer  NOT NULL,
        "errors" varchar(500)
);

        
ALTER TABLE "internal_exams" ADD CONSTRAINT "internal_exams_pk" PRIMARY KEY("id","student_id","course_id");

ALTER TABLE "internal_exams" ADD CONSTRAINT "internal_exams_student_course_id_fk" FOREIGN KEY ("student_id", "course_id")
    REFERENCES "student_courses"("student_id", "course_id");    

ALTER TABLE "internal_exams" ADD CONSTRAINT "internal_exams_instructor_fk" FOREIGN KEY ("instructor")
        REFERENCES "instructors" ("user_id");    
       
      CREATE TABLE "status" (
        "id" integer NOT NULL,
        "name" varchar(100)

);

ALTER TABLE "status" ADD CONSTRAINT "status_pk" PRIMARY KEY("id");

CREATE TABLE "driving_lessons" (
        "id" integer NOT NULL,
        "student_id" integer NOT NULL,
        "instructor_id" integer NOT NULL,
        "start_time" timestamp NOT NULL,
        "end_time" timestamp NOT NULL,
        "course_id" integer NOT NULL,
        "status" integer NOT NULL,
        "distance" integer,
        "car_id" integer NOT NULL
);

ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_pk" PRIMARY KEY("id");

ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_student_id_fk" FOREIGN KEY ("student_id")
        REFERENCES "students" ("user_id");

ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_instructor_id_fk" FOREIGN KEY ("instructor_id")
        REFERENCES "instructors" ("user_id");

ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_course_id_fk" FOREIGN KEY ("course_id")
        REFERENCES "courses" ("id");
        
ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_status_fk" FOREIGN KEY ("status")
        REFERENCES "status" ("id");
        
ALTER TABLE "driving_lessons" ADD CONSTRAINT "driving_lessons_car_id_fk" FOREIGN KEY ("car_id")
        REFERENCES "cars" ("id");  
       
CREATE TABLE "theoretical_lessons" (
        "id" integer NOT NULL,
        "course_id" integer,
        "instuctor_id" integer,
        "subject" varchar(200),
        "date" timestamp NOT NULL,
        "hours" integer NOT NULL,
        "class" varchar(5)
);

ALTER TABLE "theoretical_lessons" ADD CONSTRAINT "theoretical_lessons_pk" PRIMARY KEY("id");

ALTER TABLE "theoretical_lessons" ADD CONSTRAINT "theoretical_lessons_course_id_fk" FOREIGN KEY ("course_id")
        REFERENCES "courses" ("id");

ALTER TABLE "theoretical_lessons" ADD CONSTRAINT "theoretical_lessons_instructor_id_fk" FOREIGN KEY ("instuctor_id")
        REFERENCES "instructors" ("user_id");      
       
      

CREATE TABLE "comments" (
	"id" integer NOT NULL,
	"place_id" integer,
	"comment" varchar(1024) NOT NULL,
	"author" integer,
	"date" date,
	"grade" integer
);

ALTER TABLE comments ADD CONSTRAINT comments_pk PRIMARY KEY (id);

CREATE TABLE "interesting_places" (
	"id" integer NOT NULL,
	"lattitude" float8 NOT NULL,
	"longitude" float8 NOT NULL,
	"type_id" integer,
	"description" varchar(512),
	"accepted" bool NOT NULL,
	"city" varchar(32) NOT NULL,
	"author" varchar(32) NOT NULL,
	"date" date NOT NULL
);

ALTER TABLE interesting_places ADD CONSTRAINT interesting_places_pk PRIMARY KEY (id);

CREATE TABLE "types" (
	"id" integer NOT NULL,
	"name" varchar(256) NOT NULL,
	"description" varchar(1024),
	PRIMARY KEY("id")
);

ALTER TABLE "types" ADD CONSTRAINT types_pk PRIMARY KEY (id);

ALTER TABLE "comments" ADD CONSTRAINT "comments_users_fk" FOREIGN KEY ("author") REFERENCES "users"("id")

ALTER TABLE "comments" ADD CONSTRAINT "comments_interesting_places_fk" FOREIGN KEY ("place_id") REFERENCES "interesting_places"("id") on delete cascade

ALTER TABLE "interesting_places" ADD CONSTRAINT "interesting_places_types_fk" FOREIGN KEY ("type_id") REFERENCES "types"("id") on delete cascade

ALTER TABLE "interesting_places" ADD CONSTRAINT "interesting_places_users_fk" FOREIGN KEY ("author") REFERENCES "users"("id") on delete cascade
  
</code>

===== Słowniki danych =====
  - address - tabela przechowująca adresy stałe i korespondencyjne użytkowników
      * id - id adresu, klucz główny, INTEGER, NOT NULL
      * street - nazwa ulicy - VARCHAR(64)
      * house_number - nr domu, VARCHAR(10) NOT NULL
      * flat_number - nr mieszkania, VARCHAR(10)
      * town - nazwa miasta, VARCHAR(64) NOT NULL
      * postal_code - kod pocztowy, VARCHAR(6) NOT NULL
      * post_office - nazwa poczty, VARCHAR(64), NOT NULL             
  - cars - tabela przechowująca dane o pojazdach szkoleniowych
      * id - id pojazdu, klucz główny, INTEGER, NOT NULL
      * registration_number - nr rejestracyjny, VARCHAR(7) NOT NULL,
      * brand - marka pojazdu, VARCHAR(50) NOT NULL
      * model - model pojazdu, VARCHAR(50) NOT NULL
      * colour - kolor pojazdu, VARCHAR(50) NOT NULL
      * year - rok produkcji, INTEGER, NOT NULL
      * examination_date - data następnego badania technicznego, DATE
      * insurance_date - data ważności ubezpieczenia OC, DATE
      * category - klucz obcy do tabeli CATEGORIES (category_code) kategoria nauki jazdy, dla której pojazd jest przeznaczony, VARCHAR(4)
      * distance - przebieg, INTEGER
  - categories - tabela przechowująca kategorie praw jazdy
      * category_code - klucz główny, kod kategorii prawa jazdy np. B+E, VARCHAR(4), NOT NULL
      * category_name - nazwa kategorii, VARCHAR(20), NOT NULL
  - courses - tabela przechowująca informacje o kursach
      * id - klucz główny, id kursu, INTEGER, NOT NULL
      * start_date - data rozpoczęcia kursu, DATE, NOT NULL
      * type - typ kursu (podstawowy - P, dodatkowy - D itp), VARCHAR(3), NOT NULL
      * practical_training - liczba godzin szkolenia praktycznego, INTEGER, NOT NULL
      * theoretical_training - liczba godzin szkolenia teoretycznego, INTEGER, NOT NULL
  - driving_lessons - tabela przechowująca informacje o jazdach kursanta (zajęcia praktyczne)
      * id - klucz główny, nr zajęcia praktycznego (jazdy), INTEGER, NOT NULL 
      * student_id - nr id studenta, klucz obcy do tabeli STUDENTS (user_id), INTEGER, NOT NULL
      * instructor_id - nr id instruktora, klucz obcy do tabeli INSTRUCTORS (user_id), INTEGER, NOT NULL
      * start_time - data i godzina rozpoczęcia, TIMESTAMP
      * end_time - data i godzina zakończenia, TIMESTAMP
      * course_id - kurs, którego dotyczą zajęcia, klucz obcy do tabeli COURSES (ID), INTEGER, NOT NULL
      * status - status zajęć, klucz obcy do tabeli STATUS (ID), INTEGER, NOT NULL
      * distance - ilość przebytych km, INTEGER
      * car_id - nr id pojazdu do nauki jazdy, klucz obcy do tabeli CARS (ID), INTEGER, NOT NULL
  - employees - tabela przechowująca informacje o pracownikach nie będących instruktorami
      * id - nr id pracownika, klucz główny, NOT NULL, INTEGER
      * nip - nr NIP pracownika, VARCHAR(12), NOT NULL
      * account_number - nr konta bankowego, CHAR(26)
  - instructors tabela przechowująca informacje o instruktorach
      * user_id - nr id instruktora, klucz główny, INTEGER, NOT NULL
      * account_number - nr konta bankowego, CHAR(26)
  - internal_exams - tabela przechowująca informacje o egzaminach wewnętrznych
      * id - nr id egzaminu wewnętrznego, klucz główny, NOT NULL, INTEGER
      * student_id - id studenta, klucz obcy złożony ("student_id", "course_id") do tabeli STUDENTS_COURSES (STUDENT_ID, COURSE_ID), INTEGER, NOT NULL
      * course_id - id kursu, klucz obcy złożony ("student_id", "course_id") do tabeli STUDENTS_COURSES (STUDENT_ID, COURSE_ID), INTEGER, NOT NULL
      * type - typ egzaminu (pisemny, praktyczny), CHAR(1), NOT NULL
      * result - wynik, BOOL, NOT NULL
      * date - data, DATE, NOT NULL
      * instructor - nr id instruktora przeprowadzającego egzamin, klucz obcy do tabeli INSTRUCTORS (USER_ID), NOT NULL, INTEGER
      * errors - opis błędów, VARCHAR(500)
  - payments - tabela przechowująca informacje o płatnościach
      * id - nr id płatności, klucz główny, INTEGER, NOT NULL
      * amount - kwota, NUMERIC(9,2), NOT NULL
      * date - data płatności, NOT NULL
      * student_id - id studenta, klucz obcy do tabeli STUDENTS (USER_ID), INTEGER, NOT NULL
      * accepted_by - nr id osoby akceptującej płatność, INTEGER, NOT NULL
      * status - nr id statusu płatności, klucz obcy do tabeli PAYMENTS_STATUS (ID), INTEGER, NOT NULL
      * due_date - data ważności, DATE
      * description - opis, VARCHAR(200) 
  - payments_status - tabela przechowująca statusy płatności
      * id - nr id statusu płatności, klucz główny, INTEGER, NOT NULL
      * description - opis statusu płatności, VARCHAR(200), NOT NULL
  - status - statusy zajęć praktycznych (zarezerwowane, odbyte itp.)
      * id - nr id statusu, klucz główny, INTEGER, NOT NULL
      * name - nazwa statusu, VARCHAR(100), NOT NULL
  - student_categories - tabela odwzorowująca relację wiele do wielu (student-kategoria)
      * student_id - nr id studenta, klucz główny, klucz obcy do tabeli STUDENTS (USER_ID), INTEGER, NOT NULL
      * category_code - kod kategorii, klucz główny, klucz obcy do tabeli CATEGORIES (CATEGORY_CODE), VARCHAR(4), NOT NULL
  - student_courses - tabela odwzorowująca relację wiele do wielu (student-kurs) i przechowująca informacje dotyczące konkrestnego studenta i kursu
      * course_id - nr id kursu, klucz główny, INTEGER, NOT NULL
      * student_id - nr id studenta, klucz główny, INTEGER, NOT NULL
      * medical_certificate - określenie posiadania zaświadczenia lekarskiego, BOOL
      * instructor - nr id instruktora, klucz obcy do tabeli INSTRUCTORS (USER_ID), INTEGER
      * certificate_number - nr zaświadczenia lekarskiego, VARCHAR(10)
      * end_date - data zakończenia, DATE
  - students - tabela przechowująca studentów
      * user_id - nr id studenta, klucz główny, klucz obcy do tabeli USERS (ID), INTEGER, NOT NULL
  - theoretical_lessons - tabela z danymi na temat zajęć teoretycznych
      * id - nr id zajęć teoretycznych, klucz główny, INTEGER, NOT NULL
      * course_id - id kursu, klucz obcy do tabeli COUSRES (ID), INTEGER, NOT NULL
      * instructor_id - id instruktora, klucz obcy do tabeli INSTRUCTORS (USER_ID)
      * subject - tematyka, VARCHAR(200)
      * date - data, TIMESTAMP, NOT NULL
      * hours - czas trwania w godz., INTEGER, NOT NULL
      * class - sala, VARCHAR(5)
  - users - tabela przechowująca dane o użytkownikach aplikacji
      * id - klucz główny, INTEGER, NOT NULL
      * username - nazwa użytkownika, username VARCHAR(32) NOT NULL
      * pass_sha256 - zaszyfrowane hasło, char(64), NOT NULL
      * pass_salt - sól, CHAR(64), NOT NULL
      * code_sha256 - CHAR(64)
      * email - adres e-mail użytkownika, VARCHAR(128), NOT NULL
      * phone - nr telefonu, VARCHAR(16)
      * mobile - nr telefonu komórkowego, VARCHAR(16)
      * status - status użytkownika, odniesienie do tabeli status (ID), INTEGER, NOT NULL
      * first_name - imię, VARCHAR(64)
      * last_name - nazwisko, VARCHAR(64)
      * pesel - nr pesel, CHAR(11)
      * birth_date - data urodzenia, DATE
      * nationality - narodowość, VARCHAR(64)
      * home_address - odniesienie do tabeli adress (pole ID), INTEGER, NOT NULL
      * mailing_address - odniesienie do tabeli adress (pole ID), INTEGER
      * register_date - data rejestracji, TIMESTAMP      
      * last_login_date - data ostatniego logowania, TIMESTAMP
  - comments - tabela przechowujaca komentarze użytkowników dotyczące miejsc
      * id - nr id komentarza, klucz główny, INTEGER, NOT NULL
      * place_id - id miejsca, klucz obcy do tabeli INTERESTING_PLACES (ID), INTEGER, NOT NULL
      * comment - treść komentarza, VARCHAR(1024), NOT NULL
      * author - autro komentarza (jego id), klucz obcy do tabeli USERS (ID), INTEGER
      * date - data, TIMESTAMP
      * grade - ocena miejsca, INTEGER
  - interesting_places - tabela przechowujaca dane dotyczące ciekawych miejsc
      * id - nr id miejsca, klucz główny, INTEGER, NOT NULL
      * lattitude - szerokość geograficzna, FLOAT, NOT NULL
      * longitude - długość geograficzna, FLOAT, NOT NULL
      * type_id - id typu miejsca, klucz obcy do tabeli TYPES (ID), INTEGER`
      * description - opis miejsca, VARCHAR(512)
      * accepted - zmienna określająca, czy miejsce zostało poddane moderacji i zaakceptowane, BOOL, NOT NULL
      * city - miasto, w którym jest dane miejsce, VARCHAR(32), NOT NULL
      * author - użytkownik, który dodał miejsce, INTEGER, NOT NULL
      * date - data dodania, DATE, NOT NULL
  - types - tabela przechowujaca typy ciekawych miejsc
      * id - nr id typu, klucz główny, INTEGER, NOT NULL
      * name - nazwa typu, VARCHAR(256), NOT NULL
      * longitude - długość geograficzna, FLOAT, NOT NULL
      * description - opis typu, VARCHAR(1024)

===== Analiza zależności funkcyjnych i normalizacja tabel =====
//**Pierwsza postać normalna - 1NF**//

Wszystkie atrybuty spełniają warunek atomiczności, czyli pierwsza postać normalna jest zachowana.

**//
Druga postać normalna - 2NF//**

Baza jest w pierwszej postaci normalnej i każdy atrybut niekluczowy relacji jest w pełni funkcjonalnie zależny od wszystkich kluczy niezależnych.

//**Trzecia postać normalna - 3NF**//

Baza jest w drugiej postaci normalnej oraz brak jest w niej przechodnich zależności funkcjonalnych. Spełnia więc warunki 3NF.

===== Projektowanie operacji na danych =====

Poniżej przedstawiono przykładowe instrukcje SQL realizujące funkcjonalności systemu.

** 1. Rejestracja kursanta: **
<code> 
insert into address (id, street_number, street, house_number, city, postcode, phone, mobile, email) values(1, '12b', 'Akacjowa', '39C', 'Kraków', '30-031', null, '503323332', 'mail@gmail.com');
insert into users (id, username, pass_sha256, pass_salt, code_sha256, status, first_name, last_name, pesel, birth_date, nationality, home_address, mailing_address) values(1, 'login', 'fdf9136b1e83481e68f9d94deba79539dd4aee8df5e6d5cb8e38466d24b9d89e', 'LIkQyqduKEkKsTCaZAqIFN4gwQtorxzKyXbi24GaBsLUOr6NgDwSGWFozhpS2aLo', '40d6ab39842f7fa95a7c71a528d0885a7ee9b9534d0bd3c2c380fc098d55b25f', 1, 'imie', 'nazwisko', '82031209214', '1982-03-12', 'polskie', 1, null);
insert into students (id) values(1);
</code>

** 2. Dodawanie kategorii: **

<code>
insert into types(id, name, description) values (1, 'Ronda', 'Ronda, na których kursanci najczęściej popełniają błędy');
</code>

** 2. Dodawanie miejsca (przez kursanta): **
<code>
insert into interesting_places(id, latitude, longitude, type_id, description, accepted, city, author, date) values (421, 50.05, 19.95, 1, 'Rondo grzegórzeckie - tutaj najczęściej...', false, 'Kraków', 1, current_date);
</code>

** 3. Akceptacja miejsca przez moderatora: **

<code>
update interesting_places set accepted = 'true' where  id = 423;
</code>


** 4. Edycja danych miejsca (współrzędne i opis): **

<code>
update interesting_places set latitude = 20.33, longitude = 55.64, description = 'nowy, lepszy opis' where id = 423;
</code>

** 5. Usuwanie miejsca: **

<code>
delete from interesting_places where id = 4224;
</code>

** 6. Dodanie komentarza na temat miejsca: **

<code>
insert into comments(id, place_id, comment, author, date, grade) values (23, 523, 'Tutaj oblałem egzamin', 4, current_date, 5);
</code>

** 7. Usuwanie miejsca: **

<code>
delete from interesting_places where id = 423; -- relacje są typu on delete cascade, komentarze do miejsca również się usuną
</code>

** 8. Usuwanie kategorii: **

<code>
delete from types where id = 123; -- relacje są typu on delete cascade, więc wszystkie miejsca kategorii również się usuną (wraz z komentarzami)
</code>

** 9. Wyszukiwanie miejsc w pewnym obszarze: **

<code>
select * from interesting_places where latitude between 50.05 and 52.10 and longitute beetwen 19.95 and 23.10
</code>