· 8 years ago · Apr 05, 2018, 01:12 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Table `Prosjekt`
13-- -----------------------------------------------------
14DROP TABLE IF EXISTS `Prosjekt` ;
15
16CREATE TABLE IF NOT EXISTS `Prosjekt` (
17 `ProsjektNr` INT NOT NULL,
18 `ProsjektNavn` VARCHAR(45) NULL,
19 `ClaimNr` INT NULL,
20 `Pstatus` VARCHAR(45) NULL,
21 PRIMARY KEY (`ProsjektNr`))
22ENGINE = InnoDB;
23
24
25-- -----------------------------------------------------
26-- Table `Adresse`
27-- -----------------------------------------------------
28DROP TABLE IF EXISTS `Adresse` ;
29
30CREATE TABLE IF NOT EXISTS `Adresse` (
31 `Postnummer` INT NOT NULL,
32 `Gatenavn` VARCHAR(45) NOT NULL,
33 `AdresseNr` INT NOT NULL,
34 `Bokstav` CHAR(1) NULL,
35 `Bynavn` VARCHAR(45) NULL,
36 `Land` VARCHAR(45) NULL,
37 PRIMARY KEY (`Postnummer`, `Gatenavn`, `AdresseNr`))
38ENGINE = InnoDB;
39
40
41-- -----------------------------------------------------
42-- Table `Klient`
43-- -----------------------------------------------------
44DROP TABLE IF EXISTS `Klient` ;
45
46CREATE TABLE IF NOT EXISTS `Klient` (
47 `KlientID` INT NOT NULL AUTO_INCREMENT,
48 `KlientNavn` VARCHAR(45) NULL,
49 `Telefon` INT NULL,
50 `Epost` VARCHAR(45) NULL,
51 `StartDato` DATE NULL,
52 `AntallBaater` INT NULL,
53 `Postnummer` INT NOT NULL,
54 `Gatenavn` VARCHAR(45) NOT NULL,
55 `AdresseNr` INT NOT NULL,
56 PRIMARY KEY (`KlientID`, `Postnummer`, `Gatenavn`, `AdresseNr`),
57 INDEX `fk_Klient_Adresse1_idx` (`Postnummer` ASC, `Gatenavn` ASC, `AdresseNr` ASC),
58 CONSTRAINT `fk_Klient_Adresse1`
59 FOREIGN KEY (`Postnummer` , `Gatenavn` , `AdresseNr`)
60 REFERENCES `Adresse` (`Postnummer` , `Gatenavn` , `AdresseNr`)
61 ON DELETE NO ACTION
62 ON UPDATE NO ACTION)
63ENGINE = InnoDB;
64
65
66-- -----------------------------------------------------
67-- Table `Baat`
68-- -----------------------------------------------------
69DROP TABLE IF EXISTS `Baat` ;
70
71CREATE TABLE IF NOT EXISTS `Baat` (
72 `IMO` INT NOT NULL,
73 `TypeBaat` VARCHAR(45) NULL,
74 `Navn` VARCHAR(45) NULL,
75 `ByggAar` INT NULL,
76 `Flagg` VARCHAR(45) NULL,
77 `SertifisertDato` DATE NULL,
78 `GyldigDato` DATE NULL,
79 `ProsjektNr` INT NOT NULL,
80 `KlientID` INT NOT NULL,
81 PRIMARY KEY (`IMO`, `ProsjektNr`, `KlientID`),
82 INDEX `fk_BÃ¥t_Prosjekt1_idx` (`ProsjektNr` ASC),
83 INDEX `fk_Baat_Klient1_idx` (`KlientID` ASC),
84 CONSTRAINT `fk_BÃ¥t_Prosjekt1`
85 FOREIGN KEY (`ProsjektNr`)
86 REFERENCES `Prosjekt` (`ProsjektNr`)
87 ON DELETE NO ACTION
88 ON UPDATE NO ACTION,
89 CONSTRAINT `fk_Baat_Klient1`
90 FOREIGN KEY (`KlientID`)
91 REFERENCES `Klient` (`KlientID`)
92 ON DELETE NO ACTION
93 ON UPDATE NO ACTION)
94ENGINE = InnoDB;
95
96
97-- -----------------------------------------------------
98-- Table `Ansatt`
99-- -----------------------------------------------------
100DROP TABLE IF EXISTS `Ansatt` ;
101
102CREATE TABLE IF NOT EXISTS `Ansatt` (
103 `Brukernavn` VARCHAR(45) NOT NULL,
104 `Passord` VARCHAR(45) NOT NULL,
105 `Fornavn` VARCHAR(45) NULL,
106 `Etternavn` VARCHAR(45) NULL,
107 `Yrkestatus` VARCHAR(45) NULL,
108 `TelefonNr` INT NULL,
109 `Epost` VARCHAR(45) NULL,
110 `Kurs` VARCHAR(45) NULL,
111 `Postnummer` INT NOT NULL,
112 `Gatenavn` VARCHAR(45) NOT NULL,
113 `AdresseNr` INT NOT NULL,
114 PRIMARY KEY (`Brukernavn`, `Postnummer`, `Gatenavn`, `AdresseNr`),
115 INDEX `fk_Ansatt_Adresse1_idx` (`Postnummer` ASC, `Gatenavn` ASC, `AdresseNr` ASC),
116 CONSTRAINT `fk_Ansatt_Adresse1`
117 FOREIGN KEY (`Postnummer` , `Gatenavn` , `AdresseNr`)
118 REFERENCES `Adresse` (`Postnummer` , `Gatenavn` , `AdresseNr`)
119 ON DELETE NO ACTION
120 ON UPDATE NO ACTION)
121ENGINE = InnoDB;
122
123
124-- -----------------------------------------------------
125-- Table `Arbeidsordre`
126-- -----------------------------------------------------
127DROP TABLE IF EXISTS `Arbeidsordre` ;
128
129CREATE TABLE IF NOT EXISTS `Arbeidsordre` (
130 `Ordrenr` INT NOT NULL AUTO_INCREMENT,
131 `PaabegyntDato` DATE NULL,
132 `UtfortDato` DATE NULL,
133 `Garanti` VARCHAR(45) NULL,
134 PRIMARY KEY (`Ordrenr`))
135ENGINE = InnoDB;
136
137
138-- -----------------------------------------------------
139-- Table `Service`
140-- -----------------------------------------------------
141DROP TABLE IF EXISTS `Service` ;
142
143CREATE TABLE IF NOT EXISTS `Service` (
144 `ServiceNr` INT NOT NULL AUTO_INCREMENT,
145 `TypeService` VARCHAR(45) NULL,
146 `Anlegg` VARCHAR(45) NULL,
147 `Kommentar` VARCHAR(255) NULL,
148 `SH` INT NULL,
149 `IMO` INT NOT NULL,
150 `ProsjektNr` INT NOT NULL,
151 `KlientID` INT NOT NULL,
152 `Postnummer` INT NOT NULL,
153 `Gatenavn` VARCHAR(45) NOT NULL,
154 `AdresseNr` INT NOT NULL,
155 `Ordrenr` INT NOT NULL,
156 `Brukernavn` VARCHAR(45) NOT NULL,
157 PRIMARY KEY (`ServiceNr`, `IMO`, `ProsjektNr`, `KlientID`, `Postnummer`, `Gatenavn`, `AdresseNr`, `Ordrenr`, `Brukernavn`),
158 INDEX `fk_Service_BÃ¥t1_idx` (`IMO` ASC),
159 INDEX `fk_Service_Prosjekt1_idx` (`ProsjektNr` ASC),
160 INDEX `fk_Service_Klient1_idx` (`KlientID` ASC),
161 INDEX `fk_Service_Adresse1_idx` (`Postnummer` ASC, `Gatenavn` ASC, `AdresseNr` ASC),
162 INDEX `fk_Service_Arbeidsordre1_idx` (`Ordrenr` ASC),
163 INDEX `fk_Service_Ansatt1_idx` (`Brukernavn` ASC),
164 CONSTRAINT `fk_Service_BÃ¥t1`
165 FOREIGN KEY (`IMO`)
166 REFERENCES `Baat` (`IMO`)
167 ON DELETE NO ACTION
168 ON UPDATE NO ACTION,
169 CONSTRAINT `fk_Service_Prosjekt1`
170 FOREIGN KEY (`ProsjektNr`)
171 REFERENCES `Prosjekt` (`ProsjektNr`)
172 ON DELETE NO ACTION
173 ON UPDATE NO ACTION,
174 CONSTRAINT `fk_Service_Klient1`
175 FOREIGN KEY (`KlientID`)
176 REFERENCES `Klient` (`KlientID`)
177 ON DELETE NO ACTION
178 ON UPDATE NO ACTION,
179 CONSTRAINT `fk_Service_Adresse1`
180 FOREIGN KEY (`Postnummer` , `Gatenavn` , `AdresseNr`)
181 REFERENCES `Adresse` (`Postnummer` , `Gatenavn` , `AdresseNr`)
182 ON DELETE NO ACTION
183 ON UPDATE NO ACTION,
184 CONSTRAINT `fk_Service_Arbeidsordre1`
185 FOREIGN KEY (`Ordrenr`)
186 REFERENCES `Arbeidsordre` (`Ordrenr`)
187 ON DELETE NO ACTION
188 ON UPDATE NO ACTION,
189 CONSTRAINT `fk_Service_Ansatt1`
190 FOREIGN KEY (`Brukernavn`)
191 REFERENCES `Ansatt` (`Brukernavn`)
192 ON DELETE NO ACTION
193 ON UPDATE NO ACTION)
194ENGINE = InnoDB;
195
196
197-- -----------------------------------------------------
198-- Table `Faktura`
199-- -----------------------------------------------------
200DROP TABLE IF EXISTS `Faktura` ;
201
202CREATE TABLE IF NOT EXISTS `Faktura` (
203 `FakturaNr` INT NOT NULL,
204 `Belop` DOUBLE NULL,
205 `Kommentar` VARCHAR(45) NULL,
206 `UtlopsDato` DATE NULL,
207 `FaktureringsDato` DATE NULL,
208 `ServiceNr` INT NOT NULL,
209 `KlientID` INT NOT NULL,
210 `Brukernavn` VARCHAR(45) NOT NULL,
211 PRIMARY KEY (`FakturaNr`, `ServiceNr`, `KlientID`, `Brukernavn`),
212 INDEX `fk_Faktura_Service1_idx` (`ServiceNr` ASC),
213 INDEX `fk_Faktura_Klient1_idx` (`KlientID` ASC),
214 INDEX `fk_Faktura_Ansatt1_idx` (`Brukernavn` ASC),
215 CONSTRAINT `fk_Faktura_Service1`
216 FOREIGN KEY (`ServiceNr`)
217 REFERENCES `Service` (`ServiceNr`)
218 ON DELETE NO ACTION
219 ON UPDATE NO ACTION,
220 CONSTRAINT `fk_Faktura_Klient1`
221 FOREIGN KEY (`KlientID`)
222 REFERENCES `Klient` (`KlientID`)
223 ON DELETE NO ACTION
224 ON UPDATE NO ACTION,
225 CONSTRAINT `fk_Faktura_Ansatt1`
226 FOREIGN KEY (`Brukernavn`)
227 REFERENCES `Ansatt` (`Brukernavn`)
228 ON DELETE NO ACTION
229 ON UPDATE NO ACTION)
230ENGINE = InnoDB;
231
232
233-- -----------------------------------------------------
234-- Table `Kalendar`
235-- -----------------------------------------------------
236DROP TABLE IF EXISTS `Kalendar` ;
237
238CREATE TABLE IF NOT EXISTS `Kalendar` (
239 `KalendarID` INT NOT NULL AUTO_INCREMENT,
240 `FraDato` DATE NULL,
241 `TilDato` DATE NULL,
242 `Kommentar` VARCHAR(45) NULL,
243 `Status` VARCHAR(45) NULL,
244 `Brukernavn` VARCHAR(45) NOT NULL,
245 PRIMARY KEY (`KalendarID`, `Brukernavn`),
246 INDEX `fk_Kalendar_Ansatt1_idx` (`Brukernavn` ASC),
247 CONSTRAINT `fk_Kalendar_Ansatt1`
248 FOREIGN KEY (`Brukernavn`)
249 REFERENCES `Ansatt` (`Brukernavn`)
250 ON DELETE NO ACTION
251 ON UPDATE NO ACTION)
252ENGINE = InnoDB;
253
254
255SET SQL_MODE=@OLD_SQL_MODE;
256SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
257SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;