CREATE DATABASE kabelteve;

USE kabelteve;

CREATE TABLE kabel 
(
    azon	INT NOT NULL,
    terazon	INT NOT NULL,
    ev	INT NOT NULL ,
    lakas	INT
);

INSERT INTO kabel (azon, terazon, ev, lakas) VALUES
	('1000', '100', '2003', '518760'),
	('1001', '101', '2003', '136177'),
	('1002', '102', '2003', '78141'),
	('1003', '103', '2003', '78929'),
	('1004', '104', '2003', '87339'),
	('1005', '105', '2003', '103431'),
	('1006', '106', '2003', '52348'),
	('1007', '107', '2003', '76344'),
	('1008', '108', '2003', '88528'),
	('1009', '109', '2003', '60324'),
	('1010', '110', '2003', '46604'),
	('1011', '111', '2003', '156141'),
	('1012', '112', '2003', '44279'),
	('1013', '113', '2003', '25660'),
	('1014', '114', '2003', '81255'),
	('1015', '115', '2003', '42215'),
	('1016', '116', '2003', '50170'),
	('1017', '117', '2003', '59347'),
	('1018', '118', '2003', '55332'),
	('1019', '119', '2003', '79192'),
	('1020', '100', '2004', '503617'),
	('1021', '101', '2004', '161915'),
	('1022', '102', '2004', '84605'),
	('1023', '103', '2004', '79844'),
	('1024', '104', '2004', '89978'),
	('1025', '105', '2004', '99542'),
	('1026', '106', '2004', '41352'),
	('1027', '107', '2004', '77208'),
	('1028', '108', '2004', '92066'),
	('1029', '109', '2004', '57071'),
	('1030', '110', '2004', '56553'),
	('1031', '111', '2004', '155952'),
	('1032', '112', '2004', '47433'),
	('1033', '113', '2004', '27147'),
	('1034', '114', '2004', '83999'),
	('1035', '115', '2004', '45051'),
	('1036', '116', '2004', '49635'),
	('1037', '117', '2004', '78875'),
	('1038', '118', '2004', '58009'),
	('1039', '119', '2004', '78173'),
	('1040', '100', '2005', '526230'),
	('1041', '101', '2005', '180456'),
	('1042', '102', '2005', '85483'),
	('1043', '103', '2005', '79133'),
	('1044', '104', '2005', '89026'),
	('1045', '105', '2005', '107681'),
	('1046', '106', '2005', '42729'),
	('1047', '107', '2005', '78857'),
	('1048', '108', '2005', '88030'),
	('1049', '109', '2005', '58119'),
	('1050', '110', '2005', '55604'),
	('1051', '111', '2005', '138772'),
	('1052', '112', '2005', '49464'),
	('1053', '113', '2005', '30288'),
	('1054', '114', '2005', '89401'),
	('1055', '115', '2005', '43319'),
	('1056', '116', '2005', '50089'),
	('1057', '117', '2005', '90041'),
	('1058', '118', '2005', '61414'),
	('1059', '119', '2005', '86430'),
	('1060', '100', '2006', '534624'),
	('1061', '101', '2006', '205935'),
	('1062', '102', '2006', '90859'),
	('1063', '103', '2006', '83123'),
	('1064', '104', '2006', '85385'),
	('1065', '105', '2006', '110889'),
	('1066', '106', '2006', '49786'),
	('1067', '107', '2006', '78409'),
	('1068', '108', '2006', '87804'),
	('1069', '109', '2006', '58553'),
	('1070', '110', '2006', '60800'),
	('1071', '111', '2006', '157054'),
	('1072', '112', '2006', '49774'),
	('1073', '113', '2006', '34374'),
	('1074', '114', '2006', '91405'),
	('1075', '115', '2006', '48584'),
	('1076', '116', '2006', '53133'),
	('1077', '117', '2006', '86642'),
	('1078', '118', '2006', '68062'),
	('1079', '119', '2006', '88074'),
	('1080', '100', '2007', '541400'),
	('1081', '101', '2007', '218163'),
	('1082', '102', '2007', '88181'),
	('1083', '103', '2007', '80921'),
	('1084', '104', '2007', '87925'),
	('1085', '105', '2007', '111198'),
	('1086', '106', '2007', '51552'),
	('1087', '107', '2007', '76226'),
	('1088', '108', '2007', '82621'),
	('1089', '109', '2007', '58833'),
	('1090', '110', '2007', '61894'),
	('1091', '111', '2007', '146085'),
	('1092', '112', '2007', '48724'),
	('1093', '113', '2007', '36175'),
	('1094', '114', '2007', '90728'),
	('1095', '115', '2007', '50770'),
	('1096', '116', '2007', '56592'),
	('1097', '117', '2007', '87085'),
	('1098', '118', '2007', '70194'),
	('1099', '119', '2007', '92306'),
	('1100', '100', '2008', '546265'),
	('1101', '101', '2008', '228448'),
	('1102', '102', '2008', '90843'),
	('1103', '103', '2008', '88011'),
	('1104', '104', '2008', '92412'),
	('1105', '105', '2008', '111981'),
	('1106', '106', '2008', '58904'),
	('1107', '107', '2008', '73462'),
	('1108', '108', '2008', '82280'),
	('1109', '109', '2008', '60730'),
	('1110', '110', '2008', '61462'),
	('1111', '111', '2008', '150844'),
	('1112', '112', '2008', '54156'),
	('1113', '113', '2008', '38942'),
	('1114', '114', '2008', '92332'),
	('1115', '115', '2008', '57874'),
	('1116', '116', '2008', '65179'),
	('1117', '117', '2008', '91235'),
	('1118', '118', '2008', '72153'),
	('1119', '119', '2008', '101068'),
	('1120', '100', '2009', '557189'),
	('1121', '101', '2009', '228281'),
	('1122', '102', '2009', '91637'),
	('1123', '103', '2009', '84773'),
	('1124', '104', '2009', '91749'),
	('1125', '105', '2009', '111967'),
	('1126', '106', '2009', '57689'),
	('1127', '107', '2009', '74570'),
	('1128', '108', '2009', '82899'),
	('1129', '109', '2009', '61888'),
	('1130', '110', '2009', '61511'),
	('1131', '111', '2009', '142904'),
	('1132', '112', '2009', '52605'),
	('1133', '113', '2009', '37971'),
	('1134', '114', '2009', '92377'),
	('1135', '115', '2009', '59385'),
	('1136', '116', '2009', '62403'),
	('1137', '117', '2009', '89892'),
	('1138', '118', '2009', '73619'),
	('1139', '119', '2009', '99924'),
	('1140', '100', '2010', '559467'),
	('1141', '101', '2010', '228182'),
	('1142', '102', '2010', '91072'),
	('1143', '103', '2010', '82286'),
	('1144', '104', '2010', '92122'),
	('1145', '105', '2010', '111185'),
	('1146', '106', '2010', '56679'),
	('1147', '107', '2010', '71746'),
	('1148', '108', '2010', '84815'),
	('1149', '109', '2010', '57443'),
	('1150', '110', '2010', '62501'),
	('1151', '111', '2010', '136225'),
	('1152', '112', '2010', '53805'),
	('1153', '113', '2010', '38252'),
	('1154', '114', '2010', '91600'),
	('1155', '115', '2010', '57936'),
	('1156', '116', '2010', '65618'),
	('1157', '117', '2010', '89468'),
	('1158', '118', '2010', '71514'),
	('1159', '119', '2010', '99412'),
	('1160', '100', '2011', '574928'),
	('1161', '101', '2011', '230322'),
	('1162', '102', '2011', '91922'),
	('1163', '103', '2011', '82568'),
	('1164', '104', '2011', '92739'),
	('1165', '105', '2011', '115157'),
	('1166', '106', '2011', '56744'),
	('1167', '107', '2011', '69889'),
	('1168', '108', '2011', '85732'),
	('1169', '109', '2011', '58990'),
	('1170', '110', '2011', '61613'),
	('1171', '111', '2011', '138036'),
	('1172', '112', '2011', '55126'),
	('1173', '113', '2011', '39383'),
	('1174', '114', '2011', '95373'),
	('1175', '115', '2011', '58173'),
	('1176', '116', '2011', '67664'),
	('1177', '117', '2011', '90647'),
	('1178', '118', '2011', '75264'),
	('1179', '119', '2011', '100527'),
	('1180', '100', '2012', '589880'),
	('1181', '101', '2012', '230623'),
	('1182', '102', '2012', '91644'),
	('1183', '103', '2012', '82744'),
	('1184', '104', '2012', '89690'),
	('1185', '105', '2012', '115897'),
	('1186', '106', '2012', '57784'),
	('1187', '107', '2012', '69816'),
	('1188', '108', '2012', '86032'),
	('1189', '109', '2012', '57775'),
	('1190', '110', '2012', '63929'),
	('1191', '111', '2012', '135743'),
	('1192', '112', '2012', '56419'),
	('1193', '113', '2012', '41864'),
	('1194', '114', '2012', '95251'),
	('1195', '115', '2012', '59377'),
	('1196', '116', '2012', '64678'),
	('1197', '117', '2012', '91597'),
	('1198', '118', '2012', '74247'),
	('1199', '119', '2012', '99761'),
	('1200', '100', '2013', '621193'),
	('1201', '101', '2013', '241352'),
	('1202', '102', '2013', '94767'),
	('1203', '103', '2013', '81681'),
	('1204', '104', '2013', '90877'),
	('1205', '105', '2013', '118468'),
	('1206', '106', '2013', '57175'),
	('1207', '107', '2013', '68441'),
	('1208', '108', '2013', '89650'),
	('1209', '109', '2013', '59519'),
	('1210', '110', '2013', '64137'),
	('1211', '111', '2013', '136079'),
	('1212', '112', '2013', '58118'),
	('1213', '113', '2013', '41618'),
	('1214', '114', '2013', '100820'),
	('1215', '115', '2013', '61754'),
	('1216', '116', '2013', '68299'),
	('1217', '117', '2013', '95330'),
	('1218', '118', '2013', '75998'),
	('1219', '119', '2013', '101907'),
	('1220', '100', '2014', '619124'),
	('1221', '101', '2014', '269793'),
	('1222', '102', '2014', '97126'),
	('1223', '103', '2014', '82352'),
	('1224', '104', '2014', '93094'),
	('1225', '105', '2014', '114807'),
	('1226', '106', '2014', '58853'),
	('1227', '107', '2014', '68472'),
	('1228', '108', '2014', '89992'),
	('1229', '109', '2014', '61224'),
	('1230', '110', '2014', '64581'),
	('1231', '111', '2014', '137428'),
	('1232', '112', '2014', '61251'),
	('1233', '113', '2014', '41349'),
	('1234', '114', '2014', '101730'),
	('1235', '115', '2014', '63683'),
	('1236', '116', '2014', '70971'),
	('1237', '117', '2014', '98121'),
	('1238', '118', '2014', '78565'),
	('1239', '119', '2014', '104145'),
	('1240', '100', '2015', '623993'),
	('1241', '101', '2015', '285148'),
	('1242', '102', '2015', '100648'),
	('1243', '103', '2015', '84016'),
	('1244', '104', '2015', '103100'),
	('1245', '105', '2015', '117095'),
	('1246', '106', '2015', '60909'),
	('1247', '107', '2015', '68911'),
	('1248', '108', '2015', '92332'),
	('1249', '109', '2015', '61324'),
	('1250', '110', '2015', '65262'),
	('1251', '111', '2015', '139650'),
	('1252', '112', '2015', '64099'),
	('1253', '113', '2015', '42502'),
	('1254', '114', '2015', '105746'),
	('1255', '115', '2015', '69166'),
	('1256', '116', '2015', '71421'),
	('1257', '117', '2015', '100448'),
	('1258', '118', '2015', '80898'),
	('1259', '119', '2015', '106640'),
	('1260', '100', '2016', '637192'),
	('1261', '101', '2016', '311452'),
	('1262', '102', '2016', '103669'),
	('1263', '103', '2016', '86821'),
	('1264', '104', '2016', '106913'),
	('1265', '105', '2016', '118315'),
	('1266', '106', '2016', '61096'),
	('1267', '107', '2016', '69630'),
	('1268', '108', '2016', '94243'),
	('1269', '109', '2016', '61109'),
	('1270', '110', '2016', '66005'),
	('1271', '111', '2016', '141679'),
	('1272', '112', '2016', '66637'),
	('1273', '113', '2016', '43113'),
	('1274', '114', '2016', '108889'),
	('1275', '115', '2016', '70923'),
	('1276', '116', '2016', '71831'),
	('1277', '117', '2016', '103679'),
	('1278', '118', '2016', '83194'),
	('1279', '119', '2016', '107335'),
	('1280', '100', '2017', '642774'),
	('1281', '101', '2017', '330274'),
	('1282', '102', '2017', '107699'),
	('1283', '103', '2017', '78248'),
	('1284', '104', '2017', '108451'),
	('1285', '105', '2017', '119311'),
	('1286', '106', '2017', '61533'),
	('1287', '107', '2017', '70359'),
	('1288', '108', '2017', '96553'),
	('1289', '109', '2017', '61712'),
	('1290', '110', '2017', '67214'),
	('1291', '111', '2017', '146325'),
	('1292', '112', '2017', '70227'),
	('1293', '113', '2017', '44249'),
	('1294', '114', '2017', '114418'),
	('1295', '115', '2017', '75795'),
	('1296', '116', '2017', '74367'),
	('1297', '117', '2017', '107913'),
	('1298', '118', '2017', '85422'),
	('1299', '119', '2017', '110502'),
	('1300', '100', '2018', '649077'),
	('1301', '101', '2018', '344049'),
	('1302', '102', '2018', '110020'),
	('1303', '103', '2018', '80265'),
	('1304', '104', '2018', '111852'),
	('1305', '105', '2018', '121062'),
	('1306', '106', '2018', '62540'),
	('1307', '107', '2018', '72229'),
	('1308', '108', '2018', '99716'),
	('1309', '109', '2018', '62497'),
	('1310', '110', '2018', '67219'),
	('1311', '111', '2018', '152249'),
	('1312', '112', '2018', '75179'),
	('1313', '113', '2018', '46184'),
	('1314', '114', '2018', '120807'),
	('1315', '115', '2018', '80889'),
	('1316', '116', '2018', '78538'),
	('1317', '117', '2018', '112473'),
	('1318', '118', '2018', '91591'),
	('1319', '119', '2018', '113914'),
	('1320', '100', '2019', '650419'),
	('1321', '101', '2019', '366982'),
	('1322', '102', '2019', '113767'),
	('1323', '103', '2019', '82593'),
	('1324', '104', '2019', '113660'),
	('1325', '105', '2019', '123631'),
	('1326', '106', '2019', '65540'),
	('1327', '107', '2019', '75432'),
	('1328', '108', '2019', '102324'),
	('1329', '109', '2019', '64094'),
	('1330', '110', '2019', '66635'),
	('1331', '111', '2019', '157934'),
	('1332', '112', '2019', '80867'),
	('1333', '113', '2019', '48571'),
	('1334', '114', '2019', '128741'),
	('1335', '115', '2019', '82498'),
	('1336', '116', '2019', '87062'),
	('1337', '117', '2019', '114511'),
	('1338', '118', '2019', '89721'),
	('1339', '119', '2019', '117131');

ALTER TABLE kabel
    ADD PRIMARY KEY (azon),
    ADD KEY terazon (terazon);

CREATE TABLE terulet
(
    azon INT NOT NULL ,
    nev	VARCHAR(64),
    regioazon INT NOT NULL
);

INSERT INTO terulet (azon, nev, regioazon) VALUES
	('100', 'Budapest', '1'),
	('101', 'Pest', '2'),
	('102', 'Fejér', '4'),
	('103', 'Komárom-Esztergom', '4'),
	('104', 'Veszprém', '4'),
	('105', 'Győr-Moson-Sopron', '5'),
	('106', 'Vas', '5'),
	('107', 'Zala', '5'),
	('108', 'Baranya', '6'),
	('109', 'Somogy', '6'),
	('110', 'Tolna', '6'),
	('111', 'Borsod-Abaúj-Zemplén', '7'),
	('112', 'Heves', '7'),
	('113', 'Nógrád', '7'),
	('114', 'Hajdú-Bihar', '8'),
	('115', 'Jász-Nagykun-Szolnok', '8'),
	('116', 'Szabolcs-Szatmár-Bereg', '8'),
	('117', 'Bács-Kiskun', '3'),
	('118', 'Békés', '3'),
	('119', 'Csongrád', '3');

ALTER TABLE terulet
    ADD PRIMARY KEY (azon),
    ADD KEY regioazon (regioazon);

CREATE TABLE regio
(
    azon INT NOT NULL,
    nev VARCHAR(64),
    nagyazon INT
);

INSERT INTO regio (azon, nev, nagyazon) VALUES
	('1', 'Budapest', '10'),
	('2', 'Pest', '10'),
	('3', 'Dél-Alföld', '30'),
	('4', 'Közép-Dunántúl', '20'),
	('5', 'Nyugat-Dunántúl', '20'),
	('6', 'Dél-Dunántúl', '20'),
	('7', 'Észak-Magyarország', '30'),
	('8', 'Észak-Alföld', '30');

ALTER TABLE regio
    ADD PRIMARY KEY (azon),
    ADD KEY nagyazon (nagyazon);

CREATE TABLE nagyregio 
(
    azon INT NOT NULL,
    nev	VARCHAR(64)
);

INSERT INTO nagyregio (azon, nev) VALUES
	('10', 'Közép-Magyarország'),
	('20', 'Dunántúl'),
	('30', 'Alföld és Észak');

ALTER TABLE nagyregio
    ADD PRIMARY KEY (azon);

ALTER TABLE regio
  ADD CONSTRAINT nagyazon FOREIGN KEY (nagyazon) REFERENCES nagyregio (azon);

ALTER TABLE terulet
  ADD CONSTRAINT regioazon FOREIGN KEY (regioazon) REFERENCES regio (azon);

ALTER TABLE kabel
  ADD CONSTRAINT terazon FOREIGN KEY (terazon) REFERENCES terulet (azon);


