· 8 years ago · Dec 08, 2017, 06:40 PM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5CREATE SCHEMA IF NOT EXISTS `Nett` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6USE `Nett` ;
7
8-- -----------------------------------------------------
9-- Table `Nett`.`Nett_Tjenester`
10-- -----------------------------------------------------
11DROP TABLE IF EXISTS `Nett`.`Nett_Tjenester` ;
12
13CREATE TABLE IF NOT EXISTS `Nett`.`Nett_Tjenester` (
14 `idTjenester` INT NOT NULL AUTO_INCREMENT ,
15 `Tjenestetype` VARCHAR(30) NOT NULL ,
16 `Kommentar` VARCHAR(45) NULL ,
17 PRIMARY KEY (`idTjenester`) )
18ENGINE = InnoDB;
19
20
21-- -----------------------------------------------------
22-- Table `Nett`.`PC`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `Nett`.`PC` ;
25
26CREATE TABLE IF NOT EXISTS `Nett`.`PC` (
27 `idPC` INT NOT NULL AUTO_INCREMENT ,
28 `GW` VARCHAR(12) NOT NULL ,
29 `navn` VARCHAR(45) NOT NULL ,
30 `RAM` VARCHAR(45) NOT NULL ,
31 `HD SIze` VARCHAR(45) NOT NULL ,
32 `Nett_Tjenester_idTjenester` INT NOT NULL ,
33 `Adresse` VARCHAR(25) NOT NULL ,
34 PRIMARY KEY (`idPC`) ,
35 INDEX `fk_PC_Nett_Tjenester1` (`Nett_Tjenester_idTjenester` ASC) ,
36 CONSTRAINT `fk_PC_Nett_Tjenester1`
37 FOREIGN KEY (`Nett_Tjenester_idTjenester` )
38 REFERENCES `Nett`.`Nett_Tjenester` (`idTjenester` )
39 ON DELETE NO ACTION
40 ON UPDATE NO ACTION)
41ENGINE = InnoDB;
42
43
44-- -----------------------------------------------------
45-- Table `Nett`.`Switch`
46-- -----------------------------------------------------
47DROP TABLE IF EXISTS `Nett`.`Switch` ;
48
49CREATE TABLE IF NOT EXISTS `Nett`.`Switch` (
50 `idSwitch` INT NOT NULL AUTO_INCREMENT ,
51 `Porter` VARCHAR(128) NOT NULL ,
52 `Navn` VARCHAR(45) NOT NULL ,
53 PRIMARY KEY (`idSwitch`) )
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `Nett`.`Koblingstyper`
59-- -----------------------------------------------------
60DROP TABLE IF EXISTS `Nett`.`Koblingstyper` ;
61
62CREATE TABLE IF NOT EXISTS `Nett`.`Koblingstyper` (
63 `idKoblingstyper` INT NOT NULL AUTO_INCREMENT ,
64 `Betengelse` VARCHAR(20) NULL ,
65 PRIMARY KEY (`idKoblingstyper`) )
66ENGINE = InnoDB;
67
68
69-- -----------------------------------------------------
70-- Table `Nett`.`Kobling`
71-- -----------------------------------------------------
72DROP TABLE IF EXISTS `Nett`.`Kobling` ;
73
74CREATE TABLE IF NOT EXISTS `Nett`.`Kobling` (
75 `Kobling` INT NOT NULL AUTO_INCREMENT ,
76 `Navn` VARCHAR(45) NOT NULL ,
77 `Aktiv` TINYINT(1) NOT NULL ,
78 `PortNR` VARCHAR(45) NOT NULL ,
79 `Switch_idSwitch` INT NOT NULL ,
80 `Type` VARCHAR(3) NOT NULL ,
81 `Koblingstyper_idKoblingstyper` INT NOT NULL ,
82 PRIMARY KEY (`Kobling`) ,
83 INDEX `fk_Kobling_Switch1` (`Switch_idSwitch` ASC) ,
84 INDEX `fk_Kobling_Koblingstyper1` (`Koblingstyper_idKoblingstyper` ASC) ,
85 CONSTRAINT `fk_Kobling_Switch1`
86 FOREIGN KEY (`Switch_idSwitch` )
87 REFERENCES `Nett`.`Switch` (`idSwitch` )
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION,
90 CONSTRAINT `fk_Kobling_Koblingstyper1`
91 FOREIGN KEY (`Koblingstyper_idKoblingstyper` )
92 REFERENCES `Nett`.`Koblingstyper` (`idKoblingstyper` )
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION)
95ENGINE = InnoDB;
96
97
98-- -----------------------------------------------------
99-- Table `Nett`.`IP`
100-- -----------------------------------------------------
101DROP TABLE IF EXISTS `Nett`.`IP` ;
102
103CREATE TABLE IF NOT EXISTS `Nett`.`IP` (
104 `idIP` INT NOT NULL AUTO_INCREMENT ,
105 `Adresse` VARCHAR(20) NOT NULL ,
106 `Mask` VARCHAR(15) NOT NULL ,
107 `DFGW` VARCHAR(15) NULL ,
108 `Kommentar` VARCHAR(45) NULL ,
109 PRIMARY KEY (`idIP`) )
110ENGINE = InnoDB;
111
112
113-- -----------------------------------------------------
114-- Table `Nett`.`NIC`
115-- -----------------------------------------------------
116DROP TABLE IF EXISTS `Nett`.`NIC` ;
117
118CREATE TABLE IF NOT EXISTS `Nett`.`NIC` (
119 `MAC` INT NOT NULL AUTO_INCREMENT ,
120 `Hastighet` VARCHAR(45) NOT NULL ,
121 `KortType` VARCHAR(45) NULL ,
122 `PortNR` VARCHAR(45) NULL ,
123 `Kobling_Kobling` INT NOT NULL ,
124 `IP_idIP` INT NOT NULL ,
125 PRIMARY KEY (`MAC`) ,
126 INDEX `fk_NIC_Kobling1` (`Kobling_Kobling` ASC) ,
127 INDEX `fk_NIC_IP1` (`IP_idIP` ASC) ,
128 CONSTRAINT `fk_NIC_Kobling1`
129 FOREIGN KEY (`Kobling_Kobling` )
130 REFERENCES `Nett`.`Kobling` (`Kobling` )
131 ON DELETE NO ACTION
132 ON UPDATE NO ACTION,
133 CONSTRAINT `fk_NIC_IP1`
134 FOREIGN KEY (`IP_idIP` )
135 REFERENCES `Nett`.`IP` (`idIP` )
136 ON DELETE NO ACTION
137 ON UPDATE NO ACTION)
138ENGINE = InnoDB;
139
140
141-- -----------------------------------------------------
142-- Table `Nett`.`PC_has_NIC`
143-- -----------------------------------------------------
144DROP TABLE IF EXISTS `Nett`.`PC_has_NIC` ;
145
146CREATE TABLE IF NOT EXISTS `Nett`.`PC_has_NIC` (
147 `PC_idPC` INT NOT NULL ,
148 `NIC_MAC` INT NOT NULL ,
149 PRIMARY KEY (`PC_idPC`, `NIC_MAC`) ,
150 INDEX `fk_PC_has_NIC_NIC1` (`NIC_MAC` ASC) ,
151 INDEX `fk_PC_has_NIC_PC1` (`PC_idPC` ASC) ,
152 CONSTRAINT `fk_PC_has_NIC_PC1`
153 FOREIGN KEY (`PC_idPC` )
154 REFERENCES `Nett`.`PC` (`idPC` )
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION,
157 CONSTRAINT `fk_PC_has_NIC_NIC1`
158 FOREIGN KEY (`NIC_MAC` )
159 REFERENCES `Nett`.`NIC` (`MAC` )
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION)
162ENGINE = InnoDB;
163
164
165-- -----------------------------------------------------
166-- Table `Nett`.`Tjenesteliste`
167-- -----------------------------------------------------
168DROP TABLE IF EXISTS `Nett`.`Tjenesteliste` ;
169
170CREATE TABLE IF NOT EXISTS `Nett`.`Tjenesteliste` (
171 `idTjenesteliste` INT NOT NULL AUTO_INCREMENT ,
172 `PC_idPC` INT NOT NULL ,
173 PRIMARY KEY (`idTjenesteliste`) ,
174 INDEX `fk_Tjenesteliste_PC1` (`PC_idPC` ASC) ,
175 CONSTRAINT `fk_Tjenesteliste_PC1`
176 FOREIGN KEY (`PC_idPC` )
177 REFERENCES `Nett`.`PC` (`idPC` )
178 ON DELETE NO ACTION
179 ON UPDATE NO ACTION)
180ENGINE = InnoDB;
181
182
183
184SET SQL_MODE=@OLD_SQL_MODE;
185SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
186SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;