8dXtGHRA

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 h17db337
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema h17db337
13-- -----------------------------------------------------
14-- CREATE SCHEMA IF NOT EXISTS `h17db337` DEFAULT CHARACTER SET utf8 ;
15USE `h17db337` ;
16
17-- -----------------------------------------------------
18-- Table `h17db337`.`Kontor`
19-- -----------------------------------------------------
20DROP TABLE IF EXISTS `h17db337`.`Kontor` ;
21
22CREATE TABLE IF NOT EXISTS `h17db337`.`Kontor` (
23  `KontorID` INT NOT NULL,
24  `KontorTlf` INT NOT NULL,
25  `Stedsnavn` VARCHAR(45) NOT NULL,
26  `KontorLeder` VARCHAR(45) NOT NULL,
27  PRIMARY KEY (`KontorID`))
28ENGINE = InnoDB;
29
30
31-- -----------------------------------------------------
32-- Table `h17db337`.`Ansatt`
33-- -----------------------------------------------------
34DROP TABLE IF EXISTS `h17db337`.`Ansatt` ;
35
36CREATE TABLE IF NOT EXISTS `h17db337`.`Ansatt` (
37  `AnsattID` INT NOT NULL,
38  `Fornavn` VARCHAR(45) NOT NULL,
39  `Etternavn` VARCHAR(45) NOT NULL,
40  `Fødselsdato` DATE NOT NULL,
41  `Stilling` VARCHAR(45) NOT NULL,
42  `Lønn` INT NULL,
43  `KontorID` INT NOT NULL,
44  PRIMARY KEY (`AnsattID`),
45  INDEX `fk_Ansatt_Kontor_idx` (`KontorID` ASC),
46  CONSTRAINT `fk_Ansatt_Kontor`
47    FOREIGN KEY (`KontorID`)
48    REFERENCES `h17db337`.`Kontor` (`KontorID`)
49    ON DELETE NO ACTION
50    ON UPDATE NO ACTION)
51ENGINE = InnoDB;
52
53
54-- -----------------------------------------------------
55-- Table `h17db337`.`Prosjekt`
56-- -----------------------------------------------------
57DROP TABLE IF EXISTS `h17db337`.`Prosjekt` ;
58
59CREATE TABLE IF NOT EXISTS `h17db337`.`Prosjekt` (
60  `ProsjektID` INT NOT NULL,
61  `Prosjektleder` VARCHAR(45) NOT NULL,
62  `Prosjektbeskrivelse` VARCHAR(2000) NULL,
63  `Stedsbeskrivelse` VARCHAR(2000) NULL,
64  PRIMARY KEY (`ProsjektID`))
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `h17db337`.`Kompetansekategori`
70-- -----------------------------------------------------
71DROP TABLE IF EXISTS `h17db337`.`Kompetansekategori` ;
72
73CREATE TABLE IF NOT EXISTS `h17db337`.`Kompetansekategori` (
74  `KompetanseID` INT NOT NULL,
75  `Kompetanseansvarlig` VARCHAR(45) NOT NULL,
76  `Kompetansekategorinavn` VARCHAR(45) NULL,
77  PRIMARY KEY (`KompetanseID`))
78ENGINE = InnoDB;
79
80
81-- -----------------------------------------------------
82-- Table `h17db337`.`Kompetanse`
83-- -----------------------------------------------------
84DROP TABLE IF EXISTS `h17db337`.`Kompetanse` ;
85
86CREATE TABLE IF NOT EXISTS `h17db337`.`Kompetanse` (
87  `Kompetansenavn` VARCHAR(45) NOT NULL,
88  `KompetansekategoriID` INT NOT NULL,
89  PRIMARY KEY (`Kompetansenavn`),
90  INDEX `fk_Kompetanse_Kompetansekategori1_idx` (`KompetansekategoriID` ASC),
91  CONSTRAINT `fk_Kompetanse_Kompetansekategori1`
92    FOREIGN KEY (`KompetansekategoriID`)
93    REFERENCES `h17db337`.`Kompetansekategori` (`KompetanseID`)
94    ON DELETE NO ACTION
95    ON UPDATE NO ACTION)
96ENGINE = InnoDB;
97
98
99-- -----------------------------------------------------
100-- Table `h17db337`.`AnsattKompetanse`
101-- -----------------------------------------------------
102DROP TABLE IF EXISTS `h17db337`.`AnsattKompetanse` ;
103
104CREATE TABLE IF NOT EXISTS `h17db337`.`AnsattKompetanse` (
105  `AnsattID` INT NOT NULL,
106  `Kompetansenavn` VARCHAR(45) NOT NULL,
107  `Startdato` DATE NOT NULL,
108  `Sluttdato` DATE NOT NULL,
109  PRIMARY KEY (`AnsattID`, `Kompetansenavn`),
110  INDEX `fk_Ansatt_has_Kompetanse_Kompetanse1_idx` (`Kompetansenavn` ASC),
111  INDEX `fk_Ansatt_has_Kompetanse_Ansatt1_idx` (`AnsattID` ASC),
112  CONSTRAINT `fk_Ansatt_has_Kompetanse_Ansatt1`
113    FOREIGN KEY (`AnsattID`)
114    REFERENCES `h17db337`.`Ansatt` (`AnsattID`)
115    ON DELETE NO ACTION
116    ON UPDATE NO ACTION,
117  CONSTRAINT `fk_Ansatt_has_Kompetanse_Kompetanse1`
118    FOREIGN KEY (`Kompetansenavn`)
119    REFERENCES `h17db337`.`Kompetanse` (`Kompetansenavn`)
120    ON DELETE NO ACTION
121    ON UPDATE NO ACTION)
122ENGINE = InnoDB;
123
124
125-- -----------------------------------------------------
126-- Table `h17db337`.`Prosjektdeltagelse`
127-- -----------------------------------------------------
128DROP TABLE IF EXISTS `h17db337`.`Prosjektdeltagelse` ;
129
130CREATE TABLE IF NOT EXISTS `h17db337`.`Prosjektdeltagelse` (
131  `AnsattID` INT NOT NULL,
132  `ProsjektID` INT NOT NULL,
133  `Prosjektdato` DATE NOT NULL,
134  PRIMARY KEY (`AnsattID`, `ProsjektID`),
135  INDEX `fk_Ansatt_has_Prosjekt_Prosjekt1_idx` (`ProsjektID` ASC),
136  INDEX `fk_Ansatt_has_Prosjekt_Ansatt1_idx` (`AnsattID` ASC),
137  UNIQUE INDEX `Prosjektdato_UNIQUE` (`Prosjektdato` ASC),
138  CONSTRAINT `fk_Ansatt_has_Prosjekt_Ansatt1`
139    FOREIGN KEY (`AnsattID`)
140    REFERENCES `h17db337`.`Ansatt` (`AnsattID`)
141    ON DELETE NO ACTION
142    ON UPDATE NO ACTION,
143  CONSTRAINT `fk_Ansatt_has_Prosjekt_Prosjekt1`
144    FOREIGN KEY (`ProsjektID`)
145    REFERENCES `h17db337`.`Prosjekt` (`ProsjektID`)
146    ON DELETE NO ACTION
147    ON UPDATE NO ACTION)
148ENGINE = InnoDB;
149
150
151SET SQL_MODE=@OLD_SQL_MODE;
152SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
153SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;