To jest stara wersja strony!
PROJEKT LOGICZNY
4. Projekt bazy w języku 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 TABLE MODEL(
id INTEGER PRIMARY KEY,
nazwa VARCHAR(20) NOT NULL
);
CREATE TABLE MARKA(
id INTEGER PRIMARY KEY,
nazwa VARCHAR(20) NOT NULL
);
CREATE SEQUENCE id_modelu_samochodu_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE MODEL_SAMOCHODU(
id INTEGER DEFAULT
nextval('id_modelu_samochodu_seq') PRIMARY KEY,
id_marki INTEGER NOT NULL REFERENCES MARKA(id),
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 NOT NULL,
rodzaj_silnika rodzaj_silnika_enum NOT NULL,
pojemnosc SMALLINT NOT NULL,
CHECK(pojemnosc>0),
CHECK(start_produkcji>0),
CHECK(koniec_produkcji>0),
CHECK(koniec_produkcji>start_produkcji)
);
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_modelu_samochodu INTEGER NOT NULL REFERENCES MODEL_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 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,
opis VARCHAR(200) NOT NULL
);
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_modelu_samochodu INTEGER NOT NULL REFERENCES MODEL_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 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 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
);
5. Słowniki danych
rodzaj_czynnosci_serwisowej
model
marka
model_samochodu
id
id_marki = *klucz obcy do słownika marka * * liczba całkowita*
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';
Wyświetlenie wszystkich samochodów
SELECT id, id_użytkownika, id_modelu_samochodu, id_typu_nadwozia, rok_produkcji, VIN, kolor, nr_rejestracyjny, liczba_drzwi, moc FROM UZYTKOWNIK
Dodawanie samochodu
INSERT INTO SAMOCHOD(id, id_użytkownika, id_modelu_samochodu, id_typu_nadwozia, rok_produkcji, VIN, kolor, nr_rejestracyjny, liczba_drzwi, moc) VALUES(DEFAULT, 5, 4, 2, 2003, 'W0L0XCF0814000002', 'czarny', RNI-20MC, 5, 130);
Edycja samochodu
UPDATE SAMOCHOD SET id użytkownika = 3, id_modelu_samochodu = 2, id_typu_nadwozia = 1, 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;
Wyświetlenie wszystkich czynności serwisowych
SELECT id, id_modelu_samochodu, id_samochodu, id_rodzaju_czynnosci_serwisowej, opis, okres, przebieg, komentarz FROM CZYNNOSC_SERWISOWA
Dodawanie czynności serwisowej
INSERT INTO CZYNNOSC_SERWISOWA(id, id_modelu_samochodu, id_samochodu, id_rodzaju_czynnosci_serwisowej, opis, okres, przebieg, komentarz) VALUES(DEFAULT, 2, null, 1, 'Wymiana klocków hamulcowych', ######, 200000, 'Zalecane już przy 180000km');
Edycja czynności serwisowej
UPDATE CZYNNOSC_SERWISOWA SET id_modelu_samochodu = 3, id_samochodu = 2 , id_rodzaju_czynnosci_serwisowej = 2, opis = 'Przegląd', okres = #####, przebieg = null, komentarz = null WHERE id = 3;
Usuwanie czynności serwisowej
DELETE FROM CZYNNOSC_SERWISOWA WHERE id = 4;
Wyświetlenie wszystkich zdarzeń
SELECT id, id_rodzaju_zdarzenia, id_samochodu, data, czas, przebieg, komentarz FROM ZDARZENIE
Dodawanie zdarzenia
INSERT INTO ZDARZENIE( id, id_rodzaju_zdarzenia, 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_rodzaju_zdarzenia = 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;