Skrypt tworzący bazę danych wraz z innymi elementami:
DROP DATABASE przeglady2;
CREATE DATABASE przeglady2 WITH encoding='UTF-8';
------------------------------------------------------------
CREATE TYPE typ_nadwozia_enum AS ENUM ('hatchback', 'sedan', 'kombi', 'coupe', 'minivan', 'limuzyna', 'van', 'kabriolet','pick-up');
CREATE TYPE skrzynia_biegow_enum AS ENUM('manualna','sekwencyjna','automatyczna');
CREATE TYPE rodzaj_silnika_enum AS ENUM('spalinowy','diesel');
CREATE SEQUENCE uzytkownik_id_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE UZYTKOWNIK(
id INTEGER DEFAULT
NEXTVAL('uzytkownik_id_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 SEQUENCE marka_id_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE MARKA(
id INTEGER DEFAULT
NEXTVAL('marka_id_seq') PRIMARY KEY,
nazwa VARCHAR(20) NOT NULL
);
CREATE SEQUENCE model_id_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE MODEL(
id INTEGER DEFAULT
NEXTVAL('model_id_seq') 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 wersja_samochodu_id_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE WERSJA_SAMOCHODU(
id INTEGER DEFAULT
NEXTVAL('wersja_samochodu_id_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 samochod_id_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE SAMOCHOD(
id INTEGER DEFAULT
NEXTVAL('samochod_id_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 rodzaj_czynnosci_serwisowej_id_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE RODZAJ_CZYNNOSCI_SERWISOWEJ(
id INTEGER DEFAULT
NEXTVAL('rodzaj_czynnosci_serwisowej_id_seq') PRIMARY KEY,
nazwa VARCHAR(20) NOT NULL,
opis VARCHAR(200)
);
CREATE SEQUENCE czynnosc_serwisowa_id_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE CZYNNOSC_SERWISOWA(
id INTEGER DEFAULT
NEXTVAL('czynnosc_serwisowa_id_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 zdarzenie_id_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE ZDARZENIE(
id INTEGER DEFAULT
NEXTVAL('zdarzenie_id_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 plik_id_seq
START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1;
CREATE TABLE PLIK(
id INTEGER DEFAULT
NEXTVAL('plik_id_seq') PRIMARY KEY,
id_zdarzenia INTEGER NOT NULL REFERENCES ZDARZENIE(id),
nazwa VARCHAR(50) NOT NULL,
obiekt VARCHAR(200) NOT NULL
);
CREATE INDEX PLIK_FKzdarzenie ON PLIK(id_zdarzenia);
Skrypt dodający do bazy dane tabel słownikowych oraz przykładowe dane użytkownika:
-- rodzaje czynności serwisowych
INSERT INTO RODZAJ_CZYNNOSCI_SERWISOWEJ(nazwa,opis)
VALUES
('inspekcja','opcjonalna wymiana'),
('wymiana','obowiązkowa wymiana'),
('regulacja','regulacja'),
('stan','przechowywanie informacji')
;
-- marki samochodów
INSERT INTO marka (nazwa)
VALUES
-- ('Honda'),
-- ('Suzuki'),
-- ('Chevrolet'),
-- ('Citroen'),
('Peugeot'),
-- ('Kia'),
-- ('Nissan'),
-- ('Hundai'),
-- ('Renault'),
('Fiat'),
-- ('Toyota'),
('Volkswagen')
-- ('Opel'),
-- ('Ford'),
-- ('Skoda'),
-- ('BMW');
;
-- modele samochodów
INSERT INTO model (nazwa,id_marki)
VALUES
--Peugeot
('307',1),
('207',1),
--Fiat
('500',2),
('126p',2),
('125',2),
--Volkswagen
('Scirocco',3),
('Touran',3)
;
-- wersja samochodu
INSERT INTO WERSJA_SAMOCHODU(
id_modelu,
skrzynia_biegow,
wersja,
start_produkcji,
koniec_produkcji,
rodzaj_silnika,
pojemnosc
)
VALUES
(1,'manualna','1.4 HDi (1398 cm³)',2001,2012,'diesel',1398),
(1,'manualna','2.0 HDi (1997 cm³)',2001,2012,'diesel',1997),
(2,'manualna','1.4 75 KM',2006,2012,'spalinowy',1400),
(2,'manualna','1.6 HDi 109 KM',2006,2012,'diesel',1600),
(3,'manualna','Pop 1.2 8V',2007,2012,'spalinowy',1242),
(5,'manualna','Saloon 1.6',1967,1972,'spalinowy',1600),
(5,'manualna','Special 1.6',1967,1972,'spalinowy',1600),
(4,'manualna','Standard (650E)',1972 ,2000,'spalinowy',652),
(4,'manualna','Bis',1972 ,2000,'spalinowy',652),
(6,'manualna','II GTX',1984 ,1992,'spalinowy',1500),
(6,'manualna','II BiMoto',1981 ,1992,'spalinowy',1600),
(7,'manualna','1.4 TSI - (140 KM)',2003 ,2012,'spalinowy',1400),
(7,'manualna','2.0 TDI - (170 KM)',2003 ,2012,'diesel',2000)
;
-- uzytkownik testowy
INSERT INTO uzytkownik (imie,nazwisko,login,password,email)
VALUES
('Imie_test','Nazwisko_test','test','test','test@agh.edu.pl');
-- czynność serwisowa
INSERT INTO CZYNNOSC_SERWISOWA(
id_wersji_samochodu,
id_rodzaju_czynnosci_serwisowej,
opis,
okres,
przebieg,
komentarz
)
VALUES
(4,2,'Olej i filtr oleju',12,15000,'Olej 10W40, koszt operacji ~100zł'),
(4,2,'Filtr paliwa',12,15000,'koszt operacji ~30zł'),
(4,4,'Przebieg',1,1000,'Stan licznika'),
(8,3,'Hamulec ręczny ',12,15000,''),
(8,2,'Olej i filtr oleju',12,15000,'Olej 10W40, koszt operacji ~100zł'),
(8,4,'Przebieg',1,1000,'Stan licznika')
;
-- samochody
INSERT INTO SAMOCHOD(
id_uzytkownika,
id_wersji_samochodu,
typ_nadwozia,
VIN,
kolor,
nr_rejestracyjny,
liczba_drzwi,
moc
)
VALUES
(1,4,'hatchback','5U43RW1N','granatowy','KR 4444',3,109),
(1,8,'hatchback','WN09118249','czerwony','KRA1234',3,25)
;
-- zdarzenia
INSERT INTO ZDARZENIE(
id_samochodu,
id_czynnosci_serwisowej,
DATA,
czas_trwania,
przebieg,
komentarz
)
VALUES
(1,1,'5/5/2012',1,86000,'Wymiana w zakładzie "Pan samochodzik"'),
(1,2,'29/11/2012',1,93000,'Wymiana w zakładzie "Pan samochodzik"'),
(1,3,'3/4/2012',1,85060,''),
(1,3,'1/12/2012',1,93456,''),
(2,4,'5/12/2012',1,162000,'Regulacja wykonana w własnym zakresie'),
(2,5,'29/11/2011',1,157000,'Wymiana w zakładzie "Pan samochodzik"'),
(2,6,'3/4/2012',1,160230,''),
(2,6,'1/12/2012',1,161540,'')
;