CREATE TABLE Skusenost
(id_sk Varchar(10),
odjazdenych_rokov_od INT NOT NULL,
odjazdenych_rokov_do INT NOT NULL,
PRIMARY KEY (id_sk)
);
INSERT INTO Skusenost VALUES
('S1','1','4');
INSERT INTO Skusenost VALUES
('S2','5','7');
INSERT INTO Skusenost VALUES
('S3','8','10');
INSERT INTO Skusenost VALUES
('S4','11','13');
INSERT INTO Skusenost VALUES
('S5','14','17');
INSERT INTO Skusenost VALUES
('S6','18','20');
INSERT INTO Skusenost VALUES
('S7','21','24');
INSERT INTO Skusenost VALUES
('S8','27','30');
INSERT INTO Skusenost VALUES
('S9','31','33');
INSERT INTO Skusenost VALUES
('S10','34', '40');
CREATE TABLE Nehodovost_vodicov
(id_nehod Varchar(10),
pocet_nehod_od INT,
pocet_nehod_do INT,
Primary key (id_nehod)
);
INSERT INTO Nehodovost_vodicov VALUES
('N1','0','1');
INSERT INTO Nehodovost_vodicov VALUES
('N2','2','3');
INSERT INTO Nehodovost_vodicov VALUES
('N3','4','5');
INSERT INTO Nehodovost_vodicov VALUES
('N4','6','7');
INSERT INTO Nehodovost_vodicov VALUES
('N5','8','9');
INSERT INTO Nehodovost_vodicov VALUES
('N6','10','11');
CREATE TABLE Vodic
(id_vodica Varchar(10),
dat_nar DATE NOT NULL,
meno_vodica CHAR(10) NOT NULL,
priezvisko_vodica CHAR(15) NOT NULL,
ulica CHAR(12),
cislo INT,
mesto CHAR(12) NOT NULL,
psc INT NOT NULL,
telefon INT,
id_sk Varchar(10),
id_nehod Varchar(10),
PRIMARY KEY (id_vodica),
FOREIGN KEY (id_sk) REFERENCES Skusenost(id_sk),
FOREIGN KEY (id_nehod) REFERENCES Nehodovost_vodicov(id_nehod)
);
INSERT INTO Vodic VALUES
('V1', '1961.09.11','Ján','Modrý', 'Hejdukova', '21','Bratislava','77851','915312358','S7', 'N1');
INSERT INTO Vodic VALUES
('V2', '1968.05.21','Michal','Lisí','Trnavská cesta', '4','Stupava','77512','903541235', 'S6', 'N1');
INSERT INTO Vodic VALUES
('V3', '1983.01.20','Martin','Vylevý' ,'Matúšová', '3','Bratislava','77264','904987654',''S2', 'N1');
INSERT INTO Vodic VALUES
('V4', '1975.04.07','Peter','Csont','Planet', '8','Dunajska Streda','77652','908888585', 'S3', 'N1');
INSERT INTO Vodic VALUES
('V5', '1961.08.09','Oliver','Habur','Radiova', '9','Bratislava','77871','914557788', 'S5', 'N1');
INSERT INTO Vodic VALUES
('V6', '1976.06.05','Patrik','Derečík', 'Valašská', '15','Bratislava','77369','910493517', 'S5', 'N1');
INSERT INTO Vodic VALUES
('V7', '1969.05.20','Tomáš','Petrík','Búdková', '41','Bratislava','77978','907885566', 'S5', 'N1');
INSERT INTO Vodic VALUES
('V8', '1979.12.25','Marek','Dinka','Zochova', '85','Bratislava','77412','91012345', 'S1', 'N1');
INSERT INTO Vodic VALUES
('V9', '1966.11.29','Lukáš','Lomnický',,'Drobného', '32','Nitra','77012','908124578', 'S8', 'N2');
INSERT INTO Vodic VALUES
('V10', '1956.11.02','Peter','Tkáč','Konvalinková', '11','Levice','79011','907154676', 'S9', 'N2');
INSERT INTO Vodic VALUES
('V11', '1958.01.22','Ján','Hrubý','Mostná', '56','Trnava','77011','905134671', 'S8', 'N1');
INSERT INTO Vodic VALUES
('V12', '1956.08.04','Jozef','Petrák','Košická', '5','Dunajská Streda','88022','902134278', 'S9', 'N2');
INSERT INTO Vodic VALUES
('V13', '1961.09.09','Martin','Kotora','Kapská', '69','Senica','65011','915584676', 'S5', 'N1');
INSERT INTO Vodic VALUES
('V14', '1956.05.05','Ivan','Mačura','Miestna', '74','Myjava','55011','902556676', 'S6', 'N1');
INSERT INTO Vodic VALUES
('V15', '1964.05.02','Peter','Mokrý','Jarná', '23', 'Senec','79011','907154676', 'S7', 'N1');
CREATE TABLE Autobus
(id_autobus Varchar(10),
typ_autobusu VARCHAR(20),
spotreba_100km INT NOT NULL,
pocet_miest INT NOT NULL,
rok_vyroby INT NOT NULL,
PRIMARY KEY (id_autobus)
);
INSERT INTO Autobus VALUES
('A0001','ZHONG TONG - 6107H','22','43','2001');
INSERT INTO Autobus VALUES
('A0002','ZHONG TONG - 6108H','21','43','2002');
INSERT INTO Autobus VALUES
('A0003','ZHONG TONG – 6109H','21','43','2002');
INSERT INTO Autobus VALUES
('A0004','ZHONG TONG - 6110H','22','43','2003');
INSERT INTO Autobus VALUES
('A0005','ZHONG TONG – 6111H','22','43','2003');
INSERT INTO Autobus VALUES
('A0006','ZHONG TONG - 6112H','21','43','2004');
INSERT INTO Autobus VALUES
('B0001','DAEWOO – 6119','22','43','2002');
INSERT INTO Autobus VALUES
('B0002','DAEWOO – 6118','21','43','2004');
INSERT INTO Autobus VALUES
('B0003','DAEWOO – 6117','22','43','2004');
INSERT INTO Autobus VALUES
('B0004','DAEWOO – 6116','21','43','2005');
INSERT INTO Autobus VALUES
('B0005','DAEWOO – 6115','22','43','2005');
INSERT INTO Autobus VALUES
('C0001','MARZ - 5277-01','25','43','2005');
INSERT INTO Autobus VALUES
('D0001','MAN - CENTURY 61 SS','23','43','2006');
INSERT INTO Autobus VALUES
('E0001','MAN - LIONS REGIO','20','43','2008');
CREATE TABLE cestovna_linka
(id_linka Varchar(10),
nazov_linky VARCHAR(15),
miesto_odjazdu CHAR(20),
miesto_prijazdu CHAR(20),
cas_odjazdu TIME,
cas_prijazdu TIME,
vzdialenost_v_km INT NOT NULL,
cena_listka INT NOT NULL,
PRIMARY KEY (id_linka)
);
INSERT INTO cestovna_linka VALUES
('CS0001','BaSla1','Bratislava','Sladkovicovo','10:00','11:13','43 km','2.15');
INSERT INTO cestovna_linka VALUES
('CS0002', 'BaSla2','Bratislava','Sladkovicovo','11:30','12:43','43 km','2.15');
INSERT INTO cestovna_linka VALUES
('CS0003', 'BaSla3','Bratislava','Sladkovicovo','14:50','16:03','43 km','2.15');
INSERT INTO cestovna_linka VALUES
('CS0004', 'BaSla4','Bratislava','Sladkovicovo','16:15','17:28','43 km','2.15');
INSERT INTO cestovna_linka VALUES
('CS0005', 'SlaBa1', 'Sladkovicovo','Bratislava','9:20','10:33','43 km','2.15');
INSERT INTO cestovna_linka VALUES
('CS0006', 'SlaBa2', 'Sladkovicovo','Bratislava','11:35','12:48','43 km','2.15');
INSERT INTO cestovna_linka VALUES
('CS0007', 'SlaBa3', 'Sladkovicovo','Bratislava','16:20','17:33','43 km','2.15');
INSERT INTO cestovna_linka VALUES
('CS0008', 'SlaBa4', 'Sladkovicovo','Bratislava','17:45','18:58','43 km','2.15');
INSERT INTO cestovna_linka VALUES
('CS0009','BaDs1','Bratislava','Dunajska Streda','8:00','09:05','46 km','2.3');
INSERT INTO cestovna_linka VALUES
('CS0010','BaDs2','Bratislava','Dunajska Streda','11:00','12:05','46 km','2.3');
INSERT INTO cestovna_linka VALUES
('CS0011','BaDs3','Bratislava','Dunajska Streda','14:00','15:05','46 km','2.3');
INSERT INTO cestovna_linka VALUES
('CS0012','DsBa1','Dunajska Streda', 'Bratislava','7:30','08:35','46 km','2.3');
INSERT INTO cestovna_linka VALUES
('CS0013','DsBa2','Dunajska Streda', 'Bratislava','10:00','11:35','46 km','2.3');
INSERT INTO cestovna_linka VALUES
('CS0014','DsBa3','Dunajska Streda', 'Bratislava','15:30','16:35','46 km','2.3');
INSERT INTO cestovna_linka VALUES
('CS0015','BaSc1','Bratislava', 'Senec','08:30','09:10','27 km','1.35');
INSERT INTO cestovna_linka VALUES
('CS0016','BaSc2','Bratislava', 'Senec','10:20','11:00','27 km','2.3');
INSERT INTO cestovna_linka VALUES
('CS0017','ScBa1', 'Senec', 'Bratislava','15:30','16:35','27 km','2.3');
INSERT INTO cestovna_linka VALUES
('CS0018','ScBa2', 'Senec', 'Bratislava','15:30','16:35','27 km','2.3');
INSERT INTO cestovna_linka VALUES
('CS0019','BaSt1', 'Bratislava', 'Stupava','10:20','10:48','20 km','1');
INSERT INTO cestovna_linka VALUES
('CS0020','BaSt2', 'Bratislava', 'Stupava','12:30','12:58','20 km','1');
INSERT INTO cestovna_linka VALUES
('CS0021','StBa1', 'Stupava', 'Bratislava','10:40','11:08','20 km','1');
INSERT INTO cestovna_linka VALUES
('CS0022','StBa1', 'Stupava', 'Bratislava','11:10','11:38','20 km','1');
CREATE TABLE uskutocnenie_linky
(id_usli Varchar(10) NOT NULL,
dat_uskutocnenia DATE NOT NULL,
id_vodica Varchar(10),
id_autobus Varchar(10),
id_linka Varchar(10),
pocet_prepravenych INT,
spotreba_paliva INT NOT NULL,
PRIMARY KEY (id_usli)
FOREIGN KEY (id_vodica) REFERENCES Vodic(id_vodica)
FOREIGN KEY (id_autobus) REFERENCES Autobus(id_autobus)
FOREIGN KEY (id_linka) REFERENCES cestovna_linka(id_linka)
);
INSERT INTO uskutocnenie_linky VALUES
('UL1','2009.09.21','V1','A0001','CS0001','25','12');
INSERT INTO uskutocnenie_linky VALUES
('UL2','2009.09.21','V2','A0002','CS0002','32','13');
INSERT INTO uskutocnenie_linky VALUES
('UL3','2009.09.22','V3','A0003','CS0003','20','11');
INSERT INTO uskutocnenie_linky VALUES
('UL4','2009.09.22','V4','A0004','CS0004','11','11');
INSERT INTO uskutocnenie_linky VALUES
('UL5','2009.09.21','V5','A0005','CS0005','35','13');
INSERT INTO uskutocnenie_linky VALUES
('UL6','2009.09.25','V6','A0006','CS0006','18','10');
INSERT INTO uskutocnenie_linky VALUES
('UL7','2009.09.21','V7','B0001','CS0007','24','12');
INSERT INTO uskutocnenie_linky VALUES
('UL8','2009.09.25','V8','B0002','CS0008','30','13');
INSERT INTO uskutocnenie_linky VALUES
('UL9','2009.09.21','V9','B0003','CS0009','18','14');
INSERT INTO uskutocnenie_linky VALUES
('UL10','2009.09.22','V10','B0004','CS0010','28','14');
INSERT INTO uskutocnenie_linky VALUES
('UL11','2009.09.21','V11','B0005','CS0011','21','13');
INSERT INTO uskutocnenie_linky VALUES
('UL12','2009.09.22','V12','C0001','CS0012','40','16');
INSERT INTO uskutocnenie_linky VALUES
('UL13','2009.09.21','V13','D0001','CS0013','37','15');
INSERT INTO uskutocnenie_linky VALUES
('UL14','2009.09.22','V14','E0001','CS0014','42','17');
INSERT INTO uskutocnenie_linky VALUES
('UL15','2009.09.22','V15','A0001','CS0015','43','13');