BtStsJwy

1DROP DATABASE IF EXISTS banke;
2
3CREATE DATABASE banke;
4
5USE banke;
6
7CREATE TABLE mesto (id INT PRIMARY KEY AUTO_INCREMENT,
8					naziv VARCHAR(45) NOT NULL)
9					ENGINE=InnoDB;
10		
11CREATE TABLE korisnik (id INT PRIMARY KEY AUTO_INCREMENT,
12						kontakt1 VARCHAR(21) NOT NULL UNIQUE,
13						kontakt2 VARCHAR(21) UNIQUE DEFAULT NULL)
14						ENGINE=InnoDB;
15						
16CREATE TABLE fizicko_lice (id INT PRIMARY KEY,
17							jmbg VARCHAR(13) NOT NULL UNIQUE,
18							ime VARCHAR(45) NOT NULL,
19							prezime VARCHAR(45)NOT NULL,
20							mesto_id INT NOT NULL,
21							adresa VARCHAR(60) NOT NULL,
22							FOREIGN KEY(id) REFERENCES korisnik(id)
23								ON UPDATE CASCADE ON DELETE CASCADE,
24							FOREIGN KEY(mesto_id) REFERENCES mesto(id)
25								ON UPDATE CASCADE ON DELETE RESTRICT)
26							ENGINE=InnoDB;
27							
28CREATE TABLE pravno_lice (id INT PRIMARY KEY,
29							naziv VARCHAR(60) NOT NULL,
30							pib VARCHAR(9) NOT NULL UNIQUE,
31							email VARCHAR(50) UNIQUE DEFAULT NULL,
32							osoba_za_kontakt_id INT DEFAULT NULL,
33							FOREIGN KEY(id) REFERENCES korisnik(id)
34								ON UPDATE CASCADE ON DELETE CASCADE,
35							FOREIGN KEY(osoba_za_kontakt_id) REFERENCES fizicko_lice(id)
36								ON UPDATE CASCADE ON DELETE SET NULL)
37							ENGINE=InnoDB;
38							
39CREATE TABLE banka (id INT PRIMARY KEY,
40					FOREIGN KEY(id) REFERENCES pravno_lice(id) 
41						ON UPDATE CASCADE ON DELETE CASCADE)
42					ENGINE=InnoDB;
43					
44CREATE TABLE racun (br_racuna VARCHAR(60) PRIMARY KEY,
45					id_banke INT NOT NULL,
46					id_korisnika INT NOT NULL,
47					dinarski BOOLEAN NOT NULL DEFAULT TRUE,
48					trenutno_stanje DECIMAL(2) NOT NULL,
49					FOREIGN KEY(id_banke) REFERENCES banka(id)
50						ON UPDATE CASCADE ON DELETE CASCADE,
51					FOREIGN KEY(id_korisnika) REFERENCES korisnik(id)
52						ON UPDATE CASCADE ON DELETE RESTRICT
53					)
54					ENGINE=InnoDB;
55					
56CREATE TABLE transakcija (id INT PRIMARY KEY AUTO_INCREMENT,
57							sa_racuna VARCHAR(60) NOT NULL,
58							na_racun VARCHAR(60) NOT NULL,
59							iznos DECIMAL(2) NOT NULL,
60							vreme DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
61							FOREIGN KEY(sa_racuna) REFERENCES racun(br_racuna)
62								ON UPDATE CASCADE ON DELETE NO ACTION,
63							FOREIGN KEY(na_racun) REFERENCES racun(br_racuna)
64								ON UPDATE CASCADE ON DELETE NO ACTION,
65							CONSTRAINT razliciti_racuni CHECK (sa_racuna <> na_racun)
66							)
67							ENGINE=InnoDB;