3NsCShER

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 `Postverket` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
6USE `Postverket` ;
7
8-- -----------------------------------------------------
9-- Table `Postverket`.`land`
10-- -----------------------------------------------------
11CREATE  TABLE IF NOT EXISTS `Postverket`.`land` (
12  `landkoder` VARCHAR(4) NOT NULL ,
13  `Land` VARCHAR(45) NOT NULL ,
14  PRIMARY KEY (`landkoder`) )
15ENGINE = InnoDB;
16
17
18-- -----------------------------------------------------
19-- Table `Postverket`.`PostListe`
20-- -----------------------------------------------------
21CREATE  TABLE IF NOT EXISTS `Postverket`.`PostListe` (
22  `PostNR` VARCHAR(4) NOT NULL ,
23  `Poststed` VARCHAR(32) NOT NULL ,
24  `KommuneNR` VARCHAR(4) NOT NULL ,
25  `KommuneNavn` VARCHAR(30) NOT NULL ,
26  `Kode` CHAR NOT NULL ,
27  `land_landkoder` VARCHAR(4) NOT NULL ,
28  PRIMARY KEY (`PostNR`, `land_landkoder`) ,
29  INDEX `fk_PostListe_land1` (`land_landkoder` ASC) ,
30  CONSTRAINT `fk_PostListe_land1`
31    FOREIGN KEY (`land_landkoder` )
32    REFERENCES `Postverket`.`land` (`landkoder` )
33    ON DELETE NO ACTION
34    ON UPDATE NO ACTION)
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `Postverket`.`Kunder`
40-- -----------------------------------------------------
41CREATE  TABLE IF NOT EXISTS `Postverket`.`Kunder` (
42  `KundeNR` INT NOT NULL AUTO_INCREMENT ,
43  `Fornavn` VARCHAR(25) NOT NULL ,
44  `Etternavn` VARCHAR(30) NOT NULL ,
45  `Adresse` VARCHAR(32) NOT NULL ,
46  `Kjønn` TINYINT(1)  NOT NULL ,
47  `Mobil` VARCHAR(20) NULL ,
48  `Telefon` VARCHAR(20) NULL ,
49  `E-Mail` VARCHAR(45) NULL ,
50  `PostListe_PostNR` VARCHAR(4) NOT NULL ,
51  `land_landkoder` VARCHAR(4) NOT NULL ,
52  PRIMARY KEY (`KundeNR`, `PostListe_PostNR`, `land_landkoder`) ,
53  INDEX `fk_Kunder_PostListe` (`PostListe_PostNR` ASC) ,
54  INDEX `fk_Kunder_land1` (`land_landkoder` ASC) ,
55  CONSTRAINT `fk_Kunder_PostListe`
56    FOREIGN KEY (`PostListe_PostNR` )
57    REFERENCES `Postverket`.`PostListe` (`PostNR` )
58    ON DELETE NO ACTION
59    ON UPDATE NO ACTION,
60  CONSTRAINT `fk_Kunder_land1`
61    FOREIGN KEY (`land_landkoder` )
62    REFERENCES `Postverket`.`land` (`landkoder` )
63    ON DELETE NO ACTION
64    ON UPDATE NO ACTION)
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `Postverket`.`PC`
70-- -----------------------------------------------------
71CREATE  TABLE IF NOT EXISTS `Postverket`.`PC` (
72  `idPC` INT NOT NULL AUTO_INCREMENT ,
73  `Navn` VARCHAR(20) NULL ,
74  PRIMARY KEY (`idPC`) )
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `Postverket`.`Mapper`
80-- -----------------------------------------------------
81CREATE  TABLE IF NOT EXISTS `Postverket`.`Mapper` (
82  `idMapper` INT NOT NULL AUTO_INCREMENT ,
83  `Navn` VARCHAR(45) NOT NULL ,
84  `HD_Path` VARCHAR(45) NOT NULL ,
85  `Size` INT NOT NULL ,
86  `PC_idPC` INT NOT NULL ,
87  PRIMARY KEY (`idMapper`, `PC_idPC`) ,
88  INDEX `fk_Mapper_PC1` (`PC_idPC` ASC) ,
89  CONSTRAINT `fk_Mapper_PC1`
90    FOREIGN KEY (`PC_idPC` )
91    REFERENCES `Postverket`.`PC` (`idPC` )
92    ON DELETE NO ACTION
93    ON UPDATE NO ACTION)
94ENGINE = InnoDB;
95
96
97-- -----------------------------------------------------
98-- Table `Postverket`.`Mapper_has_Kunder`
99-- -----------------------------------------------------
100CREATE  TABLE IF NOT EXISTS `Postverket`.`Mapper_has_Kunder` (
101  `Mapper_idMapper` INT NOT NULL ,
102  `Kunder_KundeNR` INT NOT NULL ,
103  PRIMARY KEY (`Mapper_idMapper`, `Kunder_KundeNR`) ,
104  INDEX `fk_Mapper_has_Kunder_Kunder1` (`Kunder_KundeNR` ASC) ,
105  INDEX `fk_Mapper_has_Kunder_Mapper1` (`Mapper_idMapper` ASC) ,
106  CONSTRAINT `fk_Mapper_has_Kunder_Mapper1`
107    FOREIGN KEY (`Mapper_idMapper` )
108    REFERENCES `Postverket`.`Mapper` (`idMapper` )
109    ON DELETE NO ACTION
110    ON UPDATE NO ACTION,
111  CONSTRAINT `fk_Mapper_has_Kunder_Kunder1`
112    FOREIGN KEY (`Kunder_KundeNR` )
113    REFERENCES `Postverket`.`Kunder` (`KundeNR` )
114    ON DELETE NO ACTION
115    ON UPDATE NO ACTION)
116ENGINE = InnoDB;
117
118
119
120SET SQL_MODE=@OLD_SQL_MODE;
121SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
122SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;