Datenbank BK-GuT-Kalender

SET @OLD_CHARACTER_SET_CLIENT  = @@CHARACTER_SET_CLIENT,
    @OLD_CHARACTER_SET_RESULTS = @@CHARACTER_SET_RESULTS,
    @OLD_COLLATION_CONNECTION  = @@COLLATION_CONNECTION,
    @OLD_TIME_ZONE             = @@TIME_ZONE,
    TIME_ZONE                  = '+00:00',
    @OLD_UNIQUE_CHECKS         = @@UNIQUE_CHECKS,
    UNIQUE_CHECKS              = 0,
    @OLD_FOREIGN_KEY_CHECKS    = @@FOREIGN_KEY_CHECKS,
    FOREIGN_KEY_CHECKS         = 0,
    @OLD_SQL_MODE              = @@SQL_MODE,
    SQL_MODE                   = 'NO_AUTO_VALUE_ON_ZERO',
    @OLD_SQL_NOTES             = @@SQL_NOTES,
    SQL_NOTES                  = 0;
SET NAMES utf8;


-- Create Database

CREATE DATABASE IF NOT EXISTS `bk_gut_kalender` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
USE `bk_gut_kalender`;


-- Create Tables

SET @saved_cs_client     = @@character_set_client,
    character_set_client = utf8;

-- Drop Tables if you need to replace them

-- DROP TABLE IF EXISTS `schulweites_ereignis`;
-- DROP TABLE IF EXISTS `leistungsnachweis`;
-- DROP TABLE IF EXISTS `klasse`;
-- DROP TABLE IF EXISTS `lehrer`;
-- DROP TABLE IF EXISTS `fach`;
-- DROP TABLE IF EXISTS `typ`;

CREATE TABLE IF NOT EXISTS `schulweites_ereignis` (
    `id`           int(11)     NOT NULL AUTO_INCREMENT,
    `start`        date        NOT NULL,
    `ende`         date            NULL,
    `beschreibung` varchar(50) NOT NULL DEFAULT '',
    `frei`         bool        NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS `leistungsnachweis` (
    `id`     int(11) NOT NULL AUTO_INCREMENT,
    `tag`    date    NOT NULL,
    `klasse` int(11) NOT NULL,
    `lehrer` int(11) NOT NULL,
    `fach`   int(11) NOT NULL,
    `typ`    int(11) NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`klasse`) REFERENCES `klasse` (`id`),
    FOREIGN KEY (`lehrer`) REFERENCES `lehrer` (`id`),
    FOREIGN KEY (`fach`) REFERENCES `fach` (`id`),
    FOREIGN KEY (`typ`) REFERENCES `typ` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS `klasse` (
    `id`   int(11)     NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS `lehrer` (
    `id`   int(11)     NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS `fach` (
    `id`   int(11)     NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS `typ` (
    `id`   int(11)     NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

SET character_set_client = @saved_cs_client;


-- Populate Tables

LOCK TABLES `schulweites_ereignis` WRITE,
            `leistungsnachweis`    WRITE,
            `klasse`               WRITE,
            `lehrer`               WRITE,
            `fach`                 WRITE,
            `typ`                  WRITE;
ALTER TABLE `schulweites_ereignis` DISABLE KEYS;

INSERT INTO `typ` (`id`, `name`) VALUES
(1,'Test'),
(2,'Klausur')
ON DUPLICATE KEY UPDATE `id`=`id`;

ALTER TABLE `schulweites_ereignis` ENABLE KEYS;
ALTER TABLE `leistungsnachweis`    ENABLE KEYS;
ALTER TABLE `klasse`               ENABLE KEYS;
ALTER TABLE `lehrer`               ENABLE KEYS;
ALTER TABLE `fach`                 ENABLE KEYS;
ALTER TABLE `typ`                  ENABLE KEYS;
UNLOCK TABLES;


SET TIME_ZONE             = @OLD_TIME_ZONE,
    SQL_MODE              = @OLD_SQL_MODE,
    FOREIGN_KEY_CHECKS    = @OLD_FOREIGN_KEY_CHECKS,
    UNIQUE_CHECKS         = @OLD_UNIQUE_CHECKS,
    CHARACTER_SET_CLIENT  = @OLD_CHARACTER_SET_CLIENT,
    CHARACTER_SET_RESULTS = @OLD_CHARACTER_SET_RESULTS,
    COLLATION_CONNECTION  = @OLD_COLLATION_CONNECTION,
    SQL_NOTES             = @OLD_SQL_NOTES;