· 8 years ago · Feb 04, 2018, 03:32 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_KontorNr1` INT NOT NULL,
54 PRIMARY KEY (`ForNavn`, `EtterNavn`),
55 INDEX `fk_Leger_kontor1_idx` (`kontor_KontorNr1` ASC),
56 CONSTRAINT `fk_Leger_kontor1`
57 FOREIGN KEY (`kontor_KontorNr1`)
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_ForNavn` VARCHAR(50) NOT NULL,
74 `Leger_EtterNavn` VARCHAR(45) NOT NULL,
75 PRIMARY KEY (`Pnr`),
76 INDEX `fk_Pasienter_Leger1_idx` (`Leger_ForNavn` ASC, `Leger_EtterNavn` ASC),
77 CONSTRAINT `fk_Pasienter_Leger1`
78 FOREIGN KEY (`Leger_ForNavn` , `Leger_EtterNavn`)
79 REFERENCES `mydb`.`Leger` (`ForNavn` , `EtterNavn`)
80 ON DELETE NO ACTION
81 ON UPDATE NO ACTION)
82ENGINE = InnoDB;
83
84
85-- -----------------------------------------------------
86-- Table `mydb`.`Tid`
87-- -----------------------------------------------------
88CREATE TABLE IF NOT EXISTS `mydb`.`Tid` (
89 `DATE` DATE NOT NULL,
90 PRIMARY KEY (`DATE`))
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `mydb`.`LegeKonsultasjoner`
96-- -----------------------------------------------------
97CREATE TABLE IF NOT EXISTS `mydb`.`LegeKonsultasjoner` (
98 `Leger_ForNavn` VARCHAR(50) NOT NULL,
99 `Leger_EtterNavn` VARCHAR(45) NOT NULL,
100 `Tid_DATE` DATE NOT NULL,
101 `Pasienter_Pnr` INT NOT NULL,
102 INDEX `fk_LegeKonsultasjoner_Leger1_idx` (`Leger_ForNavn` ASC, `Leger_EtterNavn` ASC),
103 INDEX `fk_LegeKonsultasjoner_Tid1_idx` (`Tid_DATE` ASC),
104 INDEX `fk_LegeKonsultasjoner_Pasienter1_idx` (`Pasienter_Pnr` ASC),
105 CONSTRAINT `fk_LegeKonsultasjoner_Leger1`
106 FOREIGN KEY (`Leger_ForNavn` , `Leger_EtterNavn`)
107 REFERENCES `mydb`.`Leger` (`ForNavn` , `EtterNavn`)
108 ON DELETE NO ACTION
109 ON UPDATE NO ACTION,
110 CONSTRAINT `fk_LegeKonsultasjoner_Tid1`
111 FOREIGN KEY (`Tid_DATE`)
112 REFERENCES `mydb`.`Tid` (`DATE`)
113 ON DELETE NO ACTION
114 ON UPDATE NO ACTION,
115 CONSTRAINT `fk_LegeKonsultasjoner_Pasienter1`
116 FOREIGN KEY (`Pasienter_Pnr`)
117 REFERENCES `mydb`.`Pasienter` (`Pnr`)
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;