=======PROJEKT LOGICZNY=======

===== 1. Projektowanie tabel, kluczy, kluczy obcych, powiązań między tabelami, indeksów, etc. w oparciu o zdefiniowany diagram konceptualny ERD: =====

Zdefiniowano typy wyliczeniowe : 
  *typ_nadwozia(np. sedan)
  *skrzynia_biegow(automatyczna/sekwencyjna/manualna)
  *rodzaj_silnika(benzynowy/diesel)

Klucze obce są oznaczone czerwonym rombem na diagramie ERD w punkcie 2. Ich nazwa wskazuje do której tabeli się odnoszą.

Indeksy utworzyliśmy na wszystkich kluczach obcych, ze względu na to że są często używane w zapytaniach wybierających wszystkie dane z tabeli. Dodatkowo indeksowana jest kolumna login w encji Użytkownik, ze względu na wyszukiwanie po loginie przy logowaniu. Wszystkie indeksy są wypisane na diagramie ERD, u dołu odpowiednich encji.


===== 2. Diagram ERD w notacji Barkera/Oracle=====


{{:pl:dydaktyka:sbd:2012:projekty:przeglady2:erdbarker2.png?800}}

===== 3. Tablica krzyżowa =====


{{:pl:dydaktyka:sbd:2012:projekty:przeglady2:crosstable.jpg?800}}
  *T1 - czynnosc_serwisowa		
  *T2 - marka		
  *T3 - model		
  *T4 - plik		
  *T5 - rodzaj_czynnosci_serwisowej		
  *T6 - samochod		
  *T7 - uzytkownik		
  *T8 - wersja_samochodu		
  *T9 - zdarzenie		

===== 4. Projekt bazy w języku SQL=====
<code sql>

CREATE TYPE typ_nadwozia_enum AS ENUM ('hatchback', 'sedan', 'kombi');\\
CREATE TYPE skrzynia_biegow_enum AS ENUM('manualna','sekwencyjna','automatyczna');\\
CREATE TYPE rodzaj_silnika_enum AS ENUM('spalinowy','diesel');\\
 
CREATE SEQUENCE id_uzytkownika_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE UZYTKOWNIK(
	id INTEGER DEFAULT
    nextval('id_uzytkownika_seq') PRIMARY KEY,
	imie VARCHAR(20) NOT NULL ,
	nazwisko VARCHAR(30) NOT NULL,
	login VARCHAR(30) NOT NULL,
	password VARCHAR(30) NOT NULL,
	email VARCHAR(50) NOT NULL,
	CHECK (email::text ~* '[a-zA-Z]{1}[a-zA-Z0-9.]{0,}@[a-z0-9A-Z]{1,}(.[a-z0-9A-Z]{1,}){1,}$'::text)
);

CREATE INDEX UZYTKOWNIK_login ON UZYTKOWNIK(login);

CREATE TABLE MARKA(
	id INTEGER PRIMARY KEY,
	nazwa VARCHAR(20) NOT NULL 
);

CREATE TABLE MODEL(
	id INTEGER PRIMARY KEY,
	id_marki INTEGER NOT NULL REFERENCES MARKA(id),
	nazwa VARCHAR(20) NOT NULL 
);

CREATE INDEX MODEL_FKmarka ON MODEL(id_marki);

CREATE SEQUENCE id_wersji_samochodu_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE WERSJA_SAMOCHODU(
	id INTEGER DEFAULT
    nextval('id_wersji_samochodu_seq') PRIMARY KEY,
	id_modelu INTEGER NOT NULL REFERENCES MODEL(id),
	skrzynia_biegow skrzynia_biegow_enum,
	wersja VARCHAR(20) NOT NULL,
	start_produkcji SMALLINT NOT NULL,
	koniec_produkcji SMALLINT,
	rodzaj_silnika rodzaj_silnika_enum NOT NULL,
	pojemnosc SMALLINT NOT NULL,
	CHECK(pojemnosc>0),
	CHECK(start_produkcji>1900),
	CHECK(koniec_produkcji>1900),
	CHECK(start_produkcji<2013),
	CHECK(koniec_produkcji<2013),
	CHECK(koniec_produkcji>start_produkcji)
);

CREATE INDEX WERSJA_SAMOCHODU_FKmodel ON WERSJA_SAMOCHODU(id_modelu);

CREATE SEQUENCE id_samochodu_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE SAMOCHOD(
	id INTEGER DEFAULT
    nextval('id_samochodu_seq') PRIMARY KEY,
	id_uzytkownika INTEGER NOT NULL REFERENCES UZYTKOWNIK(id),
	id_wersji_samochodu INTEGER NOT NULL REFERENCES WERSJA_SAMOCHODU(id),
	typ_nadwozia typ_nadwozia_enum,
	VIN CHAR(20) NOT NULL,
	kolor VARCHAR(20),
	nr_rejestracyjny VARCHAR(7),
	liczba_drzwi SMALLINT,
	moc SMALLINT,
	CHECK(moc>0),
	CHECK(liczba_drzwi>0)
);

CREATE INDEX SAMOCHOD_FKuzytkownik ON SAMOCHOD(id_uzytkownika);
CREATE INDEX SAMOCHOD_FKwersja_samochodu ON SAMOCHOD(id_wersji_samochodu);


CREATE SEQUENCE id_rodzaju_czynnosci_serwisowej_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE RODZAJ_CZYNNOSCI_SERWISOWEJ(
	id INTEGER DEFAULT 
	nextval('id_rodzaju_czynnosci_serwisowej_seq') PRIMARY KEY,
	nazwa VARCHAR(20) NOT NULL,
	opis VARCHAR(200) 
);

CREATE SEQUENCE id_czynnosci_serwisowej_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE CZYNNOSC_SERWISOWA(
	id INTEGER DEFAULT 
	nextval('id_czynnosci_serwisowej_seq') PRIMARY KEY,
	id_wersji_samochodu INTEGER NOT NULL REFERENCES WERSJA_SAMOCHODU(id),
	id_rodzaju_czynnosci_serwisowej INTEGER NOT NULL REFERENCES RODZAJ_CZYNNOSCI_SERWISOWEJ(id),
	opis VARCHAR(200) NOT NULL,
	okres SMALLINT,
	przebieg INTEGER,
	komentarz VARCHAR(200),
	CHECK(przebieg>0) 
);

CREATE INDEX CZYNNOSC_SERWISOWA_FKwersja_samochodu ON CZYNNOSC_SERWISOWA(id_wersji_samochodu);
CREATE INDEX CZYNNOSC_SERWISOWA_FKrcs ON CZYNNOSC_SERWISOWA(id_rodzaju_czynnosci_serwisowej);

CREATE SEQUENCE id_zdarzenia_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE ZDARZENIE(
	id INTEGER DEFAULT
	nextval('id_zdarzenia_seq') PRIMARY KEY,
	id_samochodu INTEGER NOT NULL REFERENCES SAMOCHOD(id),
	id_czynnosci_serwisowej INTEGER NOT NULL REFERENCES CZYNNOSC_SERWISOWA(id),
	data TIMESTAMP NOT NULL,
	czas_trwania SMALLINT,
	przebieg INTEGER,
	komentarz VARCHAR(100) NOT NULL
);

CREATE INDEX ZDARZENIE_FKsamochod ON ZDARZENIE(id_samochodu);
CREATE INDEX ZDARZENIE_FKczynnosc_serwisowa ON ZDARZENIE(id_czynnosci_serwisowej);

CREATE SEQUENCE id_pliku_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;

CREATE TABLE PLIK(
	id INTEGER DEFAULT
    nextval('id_pliku_seq') PRIMARY KEY,
	id_zdarzenia INTEGER NOT NULL REFERENCES ZDARZENIE(id),
	nazwa VARCHAR(50) NOT NULL,
	obiekt BYTEA NOT NULL
);

CREATE INDEX PLIK_FKzdarzenie ON PLIK(id_zdarzenia);

</code>


===== 5. Słowniki danych =====

===rodzaj_czynnosci_serwisowej===
  *id 
  *nazwa =  *nazwa przeglądu/naprawy/wymiany * * łańcuch znaków długości 1-200*
  *opis =  *szczegóły opis czynności serwisowej * * łańcuch znaków długości 0-200*


===model===
  *id
  *id_marki = *klucz obcy do słownika marka * * liczba całkowita*
  *nazwa = *nazwa modelu samochodu * * łańcuch znaków długości 1-20*

===marka===
  *id
  *nazwa = *nazwa marki samochodu * * łańcuch znaków długości 1-20*

===model_samochodu===
  *id
  *id_modelu = *klucz obcy do słownika model * * liczba całkowita*
  *skrzynia_biegow = *rodzaj skrzyni biegów * *typ wyliczeniowy* 
  *wersja = *wersja samochodu * *łańcuch znaków długości 1-20*
  *start_produkcji = *rok w którym rozpoczęto produkcję samochodu * * liczba całkowita w zakresie 1901-2012*
  *koniec_produkcji = *rok w którym zakończono produkcję samochodu * *liczba całkowita w zakresie 1901-2012*
  *rodzaj_silnika = * rodzaj silnika * * typ wyliczeniowy*
  *pojemnosc = *pojemność silnika * * dodatnia liczba całkowita*


===== 6. Analiza zależności funkcyjnych i normalizacja tabel (dekompozycja do 3NF, BCNF, 4NF, 5NF) =====

1NF - ponieważ wszystkie atrybuty wszystkich tabel są elementarne, więc baza spełnia pierwszą postać normalną

2NF - każdy atrybut niekluczowy jest w pełni funkcyjnie zależny od klucza głównego dla każdej tabeli, co definiuje drugą postać normalną

3NF - każdy atrybut niekluczowy jest bezpośrednio zależny od klucza głównego dla każdej tabeli, dzięki czemu baza jest w trzeciej postaci normalnej




===== 8. Projektowanie operacji na danych =====

===Rejestracja użytkownika ===

INSERT INTO UZYTKOWNIK (id, imie, nazwisko, login, password, email) VALUES(DEFAULT, 'Wojciech', 'Wilk', 'wilk', 'haslo', 'wilk@gmail.com ');

===Logowanie użytkownika ===

SELECT PASSWORD FROM UZYTKOWNIK WHERE login = 'wilk';

===Dodawanie samochodu===

INSERT INTO SAMOCHOD(id, id_użytkownika, id_wersji_samochodu, typ_nadwozia, rok_produkcji, VIN, kolor, nr_rejestracyjny, liczba_drzwi, moc) VALUES(DEFAULT, 5, 4, 'hatchback', 2003, 'W0L0XCF0814000002', 'czarny', RNI-20MC, 5, 130);

===Edycja samochodu===

UPDATE SAMOCHOD SET id użytkownika = 3, id_modelu_samochodu = 2, typ_nadwozia = 'sedan', rok produkcji = 2000, VIN  = 'W0L0TGF6915216555', kolor = 'czerwony', nr rejestracyjny = 'KR 34AM',  liczba drzwi = 3, moc = 200 WHERE id = 5;

===Usuwanie samochodu===

DELETE FROM SAMOCHOD WHERE id = 3;

===Dodawanie czynności serwisowej===

INSERT INTO CZYNNOSC_SERWISOWA(id, id_wersji_samochodu, id_rodzaju_czynnosci_serwisowej, opis, okres, przebieg, komentarz) VALUES(DEFAULT, 2, 1, 'Wymiana klocków hamulcowych', null, 200000, 'Zalecane już przy 180000km');

===Edycja czynności serwisowej===

UPDATE CZYNNOSC_SERWISOWA SET id_wersji_samochodu = 3 , id_rodzaju_czynnosci_serwisowej = 2, opis = 'Przegląd', okres = 30, przebieg = null, komentarz = null WHERE id = 3;

===Usuwanie czynności serwisowej===

DELETE FROM CZYNNOSC_SERWISOWA WHERE id = 4;

===Dodawanie zdarzenia===

INSERT INTO ZDARZENIE( id, id_czynnosci_serwisowej, id_samochodu, data, czas, przebieg, komentarz) VALUES(DEFAULT, 2, 5, '2012-10-19 10:23:00', 5, 200000, 'Wymiana paska rozrządu');

===Edycja zdarzenia===

UPDATE ZDARZENIE SET id_czynnosci_serwisowej = 2 , id_samochodu = 5, data = '2012-11-11 12:33:00', czas = 3, przebieg = 10000, komentarz = 'Przegląd techniczny' WHERE id = 5;

===Usuwanie zdarzenia===

DELETE FROM ZDARZENIE WHERE id = 2;


----