· 8 years ago · Feb 09, 2018, 12:24 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-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`Legesenter`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`Legesenter` (
21 `Name` VARCHAR(45) NOT NULL,
22 `Addresse` VARCHAR(45) NOT NULL,
23 `TlfNr` VARCHAR(12) NULL,
24 PRIMARY KEY (`Name`, `Addresse`))
25ENGINE = InnoDB;
26
27
28-- -----------------------------------------------------
29-- Table `mydb`.`kontor`
30-- -----------------------------------------------------
31CREATE TABLE IF NOT EXISTS `mydb`.`kontor` (
32 `KontorNr` INT NOT NULL,
33 `TelefonNr` VARCHAR(45) NULL,
34 `Legesenter_Name` VARCHAR(45) NOT NULL,
35 `Legesenter_Addresse` VARCHAR(45) NOT NULL,
36 PRIMARY KEY (`KontorNr`),
37 INDEX `fk_kontor_Legesenter1_idx` (`Legesenter_Name` ASC, `Legesenter_Addresse` ASC),
38 CONSTRAINT `fk_kontor_Legesenter1`
39 FOREIGN KEY (`Legesenter_Name` , `Legesenter_Addresse`)
40 REFERENCES `mydb`.`Legesenter` (`Name` , `Addresse`)
41 ON DELETE NO ACTION
42 ON UPDATE NO ACTION)
43ENGINE = InnoDB;
44
45
46-- -----------------------------------------------------
47-- Table `mydb`.`Leger`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `mydb`.`Leger` (
50 `legekode` VARCHAR(3) NOT NULL,
51 `ForNavn` VARCHAR(50) NOT NULL,
52 `EtterNavn` VARCHAR(45) NOT NULL,
53 `kontor_KontorNr` INT NOT NULL,
54 PRIMARY KEY (`legekode`, `kontor_KontorNr`),
55 INDEX `fk_Leger_kontor1_idx` (`kontor_KontorNr` ASC),
56 CONSTRAINT `fk_Leger_kontor1`
57 FOREIGN KEY (`kontor_KontorNr`)
58 REFERENCES `mydb`.`kontor` (`KontorNr`)
59 ON DELETE NO ACTION
60 ON UPDATE NO ACTION)
61ENGINE = InnoDB;
62
63
64-- -----------------------------------------------------
65-- Table `mydb`.`Pasienter`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `mydb`.`Pasienter` (
68 `Pnr` INT NOT NULL,
69 `Fornavn` VARCHAR(45) NULL,
70 `Etternavn` VARCHAR(45) NULL,
71 `PSted` VARCHAR(45) NULL,
72 `TlfNr` VARCHAR(12) NULL,
73 `Leger_legekode` VARCHAR(3) NOT NULL,
74 PRIMARY KEY (`Pnr`, `Leger_legekode`),
75 INDEX `fk_Pasienter_Leger1_idx` (`Leger_legekode` ASC),
76 CONSTRAINT `fk_Pasienter_Leger1`
77 FOREIGN KEY (`Leger_legekode`)
78 REFERENCES `mydb`.`Leger` (`legekode`)
79 ON DELETE NO ACTION
80 ON UPDATE NO ACTION)
81ENGINE = InnoDB;
82
83
84-- -----------------------------------------------------
85-- Table `mydb`.`Tid`
86-- -----------------------------------------------------
87CREATE TABLE IF NOT EXISTS `mydb`.`Tid` (
88 `DATE` DATE NOT NULL,
89 PRIMARY KEY (`DATE`))
90ENGINE = InnoDB;
91
92
93-- -----------------------------------------------------
94-- Table `mydb`.`LegeKonsultasjoner`
95-- -----------------------------------------------------
96CREATE TABLE IF NOT EXISTS `mydb`.`LegeKonsultasjoner` (
97 `Tid_DATE` DATE NOT NULL,
98 `Pasienter_Pnr` INT NOT NULL,
99 `Leger_legekode` VARCHAR(3) NOT NULL,
100 `Leger_kontor_KontorNr` INT NOT NULL,
101 INDEX `fk_LegeKonsultasjoner_Tid1_idx` (`Tid_DATE` ASC),
102 INDEX `fk_LegeKonsultasjoner_Pasienter1_idx` (`Pasienter_Pnr` ASC),
103 PRIMARY KEY (`Pasienter_Pnr`, `Tid_DATE`, `Leger_legekode`, `Leger_kontor_KontorNr`),
104 INDEX `fk_LegeKonsultasjoner_Leger1_idx` (`Leger_legekode` ASC, `Leger_kontor_KontorNr` ASC),
105 CONSTRAINT `fk_LegeKonsultasjoner_Tid1`
106 FOREIGN KEY (`Tid_DATE`)
107 REFERENCES `mydb`.`Tid` (`DATE`)
108 ON DELETE NO ACTION
109 ON UPDATE NO ACTION,
110 CONSTRAINT `fk_LegeKonsultasjoner_Pasienter1`
111 FOREIGN KEY (`Pasienter_Pnr`)
112 REFERENCES `mydb`.`Pasienter` (`Pnr`)
113 ON DELETE NO ACTION
114 ON UPDATE NO ACTION,
115 CONSTRAINT `fk_LegeKonsultasjoner_Leger1`
116 FOREIGN KEY (`Leger_legekode` , `Leger_kontor_KontorNr`)
117 REFERENCES `mydb`.`Leger` (`legekode` , `kontor_KontorNr`)
118 ON DELETE NO ACTION
119 ON UPDATE NO ACTION)
120ENGINE = InnoDB;
121
122
123SET SQL_MODE=@OLD_SQL_MODE;
124SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
125SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;