06wV69sZ

1drop schema itinvertar;
2CREATE SCHEMA IF NOT EXISTS `itinvertar` DEFAULT CHARACTER SET latin1 ;
3USE `itinvertar` ;
4
5
6DROP TABLE IF EXISTS `itinvertar`.`oprema` ;
7
8CREATE TABLE IF NOT EXISTS `itinvertar`.`oprema` (
9  `invertarskiBR` INT(11) NOT NULL,
10  `datumpocetka` DATETIME NULL DEFAULT NULL,
11  `garancijaupotrebe` INT(11) NOT NULL,
12  `cena` INT(11) NOT NULL,
13  `tip` VARCHAR(15) NOT NULL,
14  `opis` VARCHAR(15) NOT NULL,
15  `Sifra` INT(11) NOT NULL,
16  PRIMARY KEY (`Sifra`))
17ENGINE = InnoDB
18DEFAULT CHARACTER SET = latin1;
19
20
21
22DROP TABLE IF EXISTS `itinvertar`.`installer` ;
23
24CREATE TABLE IF NOT EXISTS `itinvertar`.`installer` (
25  `SifI` INT(11) NOT NULL,
26  PRIMARY KEY (`SifI`))
27ENGINE = InnoDB
28DEFAULT CHARACTER SET = latin1;
29
30
31DROP TABLE IF EXISTS `itinvertar`.`firma` ;
32
33CREATE TABLE IF NOT EXISTS `itinvertar`.`firma` (
34  `Naziv` VARCHAR(20) NOT NULL,
35  `Adresa` VARCHAR(45) NOT NULL,
36  `oprema_Sifra` INT(11) NOT NULL,
37  `installer_SifI` INT(11) NOT NULL,
38  PRIMARY KEY (`installer_SifI`),
39  CONSTRAINT `fk_Firma_oprema1`
40    FOREIGN KEY (`oprema_Sifra`)
41    REFERENCES `itinvertar`.`oprema` (`Sifra`),
42  CONSTRAINT `fk_firma_installer1`
43    FOREIGN KEY (`installer_SifI`)
44    REFERENCES `itinvertar`.`installer` (`SifI`));
45
46
47DROP TABLE IF EXISTS `itinvertar`.`softver` ;
48
49CREATE TABLE IF NOT EXISTS `itinvertar`.`softver` (
50  `SifV` INT(11) NOT NULL,
51  `opis` VARCHAR(30) NOT NULL,
52  `verzija` VARCHAR(30) NOT NULL,
53  PRIMARY KEY (`SifV`));
54
55
56
57DROP TABLE IF EXISTS `itinvertar`.`jeinst` ;
58
59CREATE TABLE IF NOT EXISTS `itinvertar`.`jeinst` (
60  `oprema_Sifra` INT(11) NOT NULL,
61  `installer_SifI` INT(11) NOT NULL,
62  `softver_sifV` INT(11) NOT NULL,
63  CONSTRAINT `FK_softver`
64    FOREIGN KEY (`softver_sifV`)
65    REFERENCES `itinvertar`.`softver` (`SifV`),
66  CONSTRAINT `fk_oprema_has_installer_installer1`
67    FOREIGN KEY (`installer_SifI`)
68    REFERENCES `itinvertar`.`installer` (`SifI`),
69  CONSTRAINT `fk_oprema_has_installer_oprema1`
70    FOREIGN KEY (`oprema_Sifra`)
71    REFERENCES `itinvertar`.`oprema` (`Sifra`));
72
73
74
75DROP TABLE IF EXISTS `itinvertar`.`soba` ;
76
77CREATE TABLE IF NOT EXISTS `itinvertar`.`soba` (
78  `SifS` INT(11) NOT NULL,
79  `brsobe` INT(11) NOT NULL,
80  `sprat` INT(11) NOT NULL,
81  PRIMARY KEY (`SifS`));
82
83
84
85DROP TABLE IF EXISTS `itinvertar`.`radnik` ;
86
87CREATE TABLE IF NOT EXISTS `itinvertar`.`radnik` (
88  `Ime` VARCHAR(25) NOT NULL,
89  `Telefon` VARCHAR(13) NOT NULL,
90  `soba_SifS` INT(11) NOT NULL,
91  `installer_SifI` INT(11) NOT NULL,
92  PRIMARY KEY (`installer_SifI`),
93  CONSTRAINT `fk_Radnik_soba1`
94    FOREIGN KEY (`soba_SifS`)
95    REFERENCES `itinvertar`.`soba` (`SifS`),
96  CONSTRAINT `fk_radnik_installer1`
97    FOREIGN KEY (`installer_SifI`)
98    REFERENCES `itinvertar`.`installer` (`SifI`));
99
100
101
102DROP TABLE IF EXISTS `itinvertar`.`tip` ;
103
104CREATE TABLE IF NOT EXISTS `itinvertar`.`tip` (
105  `SifT` INT(11) NOT NULL,
106  `opis` VARCHAR(30) NULL DEFAULT NULL,
107  `sifra` INT(11) NOT NULL,
108  PRIMARY KEY (`SifT`),
109  CONSTRAINT `FK_tip`
110    FOREIGN KEY (`sifra`)
111    REFERENCES `itinvertar`.`oprema` (`Sifra`));
112
113
114DROP TABLE IF EXISTS `itinvertar`.`Je_rasporedio` ;
115
116CREATE TABLE IF NOT EXISTS `itinvertar`.`Je_rasporedio` (
117  `oprema_Sifra` INT(11) NOT NULL,
118  `soba_SifS` INT(11) NOT NULL,
119  CONSTRAINT `fk_oprema_has_soba_oprema1`
120    FOREIGN KEY (`oprema_Sifra`)
121    REFERENCES `itinvertar`.`oprema` (`Sifra`)
122    ON DELETE NO ACTION
123    ON UPDATE NO ACTION,
124  CONSTRAINT `fk_oprema_has_soba_soba1`
125    FOREIGN KEY (`soba_SifS`)
126    REFERENCES `itinvertar`.`soba` (`SifS`));