VZM0uxPN

1-- per te shikuar si kemi bere lidhjen e FOREIGN KEY, shikoni ne fundin e dokumentit
2
3CREATE DATABASE IF NOT EXISTS `mikrofinance` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
4USE `mikrofinance`;
5
6-- krijojm tabelen banka
7CREATE TABLE IF NOT EXISTS `banka` (
8  `id` int(11) NOT NULL AUTO_INCREMENT,
9  `emri` varchar(100) NOT NULL,
10  `adresa` varchar(100) NOT NULL,
11  PRIMARY KEY (`id`)
12) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
13
14-- insertojm emrin dhe adresen e bankes
15INSERT INTO `banka` (`id`, `emri`, `adresa`) VALUES
16(1, 'Banka VFB', 'Prishtine, Kosove');
17
18-- krijom tabelen dega
19CREATE TABLE IF NOT EXISTS `dega` (
20  `id` int(11) NOT NULL AUTO_INCREMENT,
21  `bankaID` int(11) NOT NULL,
22  `emri` varchar(100) NOT NULL,
23  `qyteti` varchar(100) NOT NULL,
24  `adresa` varchar(200) NOT NULL,
25  PRIMARY KEY (`id`),
26  KEY `bankaID` (`bankaID`)
27) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
28
29-- insertojm ne tabelen dega duke ju referuar bankaID si FOREIGN KEY te tabeles Banka, emrin qytetin dhe adresen
30INSERT INTO `dega` (`id`, `bankaID`, `emri`, `qyteti`, `adresa`) VALUES
31(1, 1, 'Dega AAB', 'Fushe Kosove', 'Fushe Kosove nr 1');
32
33-- krijom tabelen  departament
34CREATE TABLE IF NOT EXISTS `departament` (
35  `id` int(11) NOT NULL AUTO_INCREMENT,
36  `emri` varchar(100) NOT NULL,
37  `phone` varchar(100) NOT NULL,
38  PRIMARY KEY (`id`)
39) ENGINE=InnoDB DEFAULT CHARSET=utf8;
40
41-- insertojm ne tabelen departament 2 rows
42INSERT INTO `departament` (`id`, `emri`, `phone`) VALUES
43(1, 'Financa', '+38349860001'),
44(2, 'Menaxhment', '+38349860001');
45
46-- krijojm tabelen klienti
47CREATE TABLE IF NOT EXISTS `klienti` (
48  `id` int(11) NOT NULL AUTO_INCREMENT,
49  `personID` int(11) NOT NULL,
50  `sherbimetID` int(11) DEFAULT NULL,
51  `data_antarsimit` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
52  PRIMARY KEY (`id`),
53  KEY `personID` (`personID`),
54  KEY `sherbimetID` (`sherbimetID`)
55) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
56
57-- insertojm ne tabelen dega duke ju referuar personID si FOREIGN KEY te tabeles person, sherbimetID si FOREIGN KEY te tabeles sherbimet, emrin qytetin dhe adresen
58INSERT INTO `klienti` (`id`, `personID`, `sherbimetID`, `data_antarsimit`) VALUES
59(2, 4, 1, '2021-01-21 21:56:19'),
60(3, 4, 1, '2021-01-21 21:56:21');
61
62-- krijojm tabelen person
63CREATE TABLE IF NOT EXISTS `person` (
64  `id` int(11) NOT NULL AUTO_INCREMENT,
65  `emer` varchar(100) NOT NULL,
66  `mbiemer` varchar(100) NOT NULL,
67  `tel` varchar(100) NOT NULL,
68  `email` varchar(50) NOT NULL,
69  `adresa` varchar(200) NOT NULL,
70  PRIMARY KEY (`id`)
71) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
72
73-- insertojm ne tablen personi
74INSERT INTO `person` (`id`, `emer`, `mbiemer`, `tel`, `email`, `adresa`) VALUES
75(1, 'Vegim', 'Rexhepi', '+38349001001', 'vegim.rexhepi@universitetiaab.com', 'Prishtine nr.12, Kosove'),
76(2, 'Fisnik', 'Gojani', '+38349001001', 'fisnik.gojani@universitetiaab.com', 'Prishtine nr.12, Kosove'),
77(3, 'Besant', 'Sahiti', '+38349001001', 'besant.sahiti@universitetiaab.com', 'Prishtine nr.12, Kosove'),
78(4, 'Klienti 1', 'Mbiemri 1', '+38349001001', 'klienti1@klienti.com', 'Prishtine, Kosove'),
79(5, 'Klienti 2', 'Mbiemri 2', '+38349001001', 'klienti2@klienti.com', 'Prishtine, Kosove');
80
81-- krijojm tabelen sherbimet
82CREATE TABLE IF NOT EXISTS `sherbimet` (
83  `id` int(11) NOT NULL AUTO_INCREMENT,
84  `emri` varchar(100) NOT NULL,
85  `pershkrimi` varchar(500) DEFAULT NULL,
86  `cmimi` double DEFAULT NULL,
87  `kredia` double DEFAULT NULL,
88  PRIMARY KEY (`id`)
89) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
90
91-- insertojm ne tabelen sherbimet
92INSERT INTO `sherbimet` (`id`, `emri`, `pershkrimi`, `cmimi`, `kredia`) VALUES
93(1, 'Sherbimi 1', 'Ofrojme kete sherbim ....', 10, NULL);
94
95-- krijojm tabelen stafi
96CREATE TABLE IF NOT EXISTS `stafi` (
97  `id` int(11) NOT NULL AUTO_INCREMENT,
98  `stafID` int(11) NOT NULL,
99  `departamentID` int(11) NOT NULL,
100  `paga` double NOT NULL,
101  PRIMARY KEY (`id`),
102  KEY `stafID` (`stafID`),
103  KEY `departamentID` (`departamentID`)
104) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
105
106-- insertojm ne tabelen dega duke ju referuar stafID si FOREIGN KEY te tabeles stafi, departamentID si FOREIGN KEY te tabeles departament, dhe pagen
107INSERT INTO `stafi` (`id`, `stafID`, `departamentID`, `paga`) VALUES
108(1, 1, 1, 1200),
109(2, 2, 1, 1200),
110(3, 3, 1, 1300);
111
112
113-- modifikojm tabelen dega duke shtuar si FOREIGN KEY bankaID si reference e tabeles banka.id
114ALTER TABLE `dega`
115  ADD CONSTRAINT `dega_ibfk_1` FOREIGN KEY (`bankaID`) REFERENCES `banka` (`id`);
116
117-- modifikojm tabelen klienti duke shtuar si FOREIGN KEY personID si reference e tabeles person.id dhe
118-- shtojm  si FOREIGN KEY sherbimetID si reference e tabeles sherbimet.id
119ALTER TABLE `klienti`
120  ADD CONSTRAINT `klienti_ibfk_1` FOREIGN KEY (`personID`) REFERENCES `person` (`id`),
121  ADD CONSTRAINT `klienti_ibfk_2` FOREIGN KEY (`sherbimetID`) REFERENCES `sherbimet` (`id`);
122
123-- modifikojm tabelen klienti duke shtuar si FOREIGN KEY stafID si reference e tabeles person.id dhe
124-- shtojm  si FOREIGN KEY departamentID si reference e tabeles departament.id
125ALTER TABLE `stafi`
126  ADD CONSTRAINT `stafi_ibfk_1` FOREIGN KEY (`stafID`) REFERENCES `person` (`id`),
127  ADD CONSTRAINT `stafi_ibfk_2` FOREIGN KEY (`departamentID`) REFERENCES `departament` (`id`);
128