source: trunk/de.ugoe.cs.swe.exercises/schema/exercises-mysql.sql @ 6

-- phpMyAdmin SQL Dump
-- version 3.2.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 19. Oktober 2009 um 10:53
-- Server Version: 5.0.51
-- PHP-Version: 5.2.6-1+lenny3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Datenbank: `xprakt1`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `exercise`
--

CREATE TABLE IF NOT EXISTS `exercise` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(256) NOT NULL,
  `complexity` tinyint(4) NOT NULL,
  `revisionnumber` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=288 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `exercisesheet`
--

CREATE TABLE IF NOT EXISTS `exercisesheet` (
  `id` int(11) NOT NULL auto_increment,
  `number` int(11) NOT NULL,
  `complexityvalue` tinyint(4) NOT NULL,
  `revisionnumber` int(11) NOT NULL,
  `examination` tinyint(1) NOT NULL default '0',
  `lectureevent` int(11) NOT NULL,
  `numOfExercises` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `lectureevent` (`lectureevent`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=149 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `exercisesheet_exercise`
--

CREATE TABLE IF NOT EXISTS `exercisesheet_exercise` (
  `exercisesheet` int(11) NOT NULL,
  `exercise` int(11) NOT NULL,
  `revisionnumber` int(11) NOT NULL,
  UNIQUE KEY `exercisesheet_2` (`exercisesheet`,`exercise`),
  KEY `exercise` (`exercise`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `keyword_exercise`
--

CREATE TABLE IF NOT EXISTS `keyword_exercise` (
  `id` int(11) NOT NULL auto_increment,
  `word` varchar(30) NOT NULL,
  `exercise` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `exercise` (`exercise`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=245 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `keyword_exercisesheet`
--

CREATE TABLE IF NOT EXISTS `keyword_exercisesheet` (
  `id` int(11) NOT NULL auto_increment,
  `word` varchar(30) NOT NULL,
  `exercisesheet` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `exercisesheet` (`exercisesheet`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=134 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `keyword_lecture`
--

CREATE TABLE IF NOT EXISTS `keyword_lecture` (
  `id` int(11) NOT NULL auto_increment,
  `word` varchar(30) NOT NULL,
  `lecture` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `lecture` (`lecture`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=229 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `lecture`
--

CREATE TABLE IF NOT EXISTS `lecture` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=94 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `lectureevent`
--

CREATE TABLE IF NOT EXISTS `lectureevent` (
  `id` int(11) NOT NULL auto_increment,
  `semester` int(11) NOT NULL,
  `ss_ws` varchar(2) NOT NULL,
  `lecture` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `lecture` (`lecture`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=162 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `lectureevent_lecturer`
--

CREATE TABLE IF NOT EXISTS `lectureevent_lecturer` (
  `lectureevent` int(11) NOT NULL,
  `lecturer` int(11) NOT NULL,
  UNIQUE KEY `lectureevent_2` (`lectureevent`,`lecturer`),
  KEY `lecturer` (`lecturer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `lecturer`
--

CREATE TABLE IF NOT EXISTS `lecturer` (
  `id` int(11) NOT NULL auto_increment,
  `lastname` varchar(30) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `title` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=111 ;

-- --------------------------------------------------------

--
-- Tabellenstruktur fÃŒr Tabelle `lecture_exercise`
--

CREATE TABLE IF NOT EXISTS `lecture_exercise` (
  `lecture` int(11) NOT NULL,
  `exercise` int(11) NOT NULL,
  UNIQUE KEY `lecture_2` (`lecture`,`exercise`),
  KEY `exercise` (`exercise`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Constraints der exportierten Tabellen
--

--
-- Constraints der Tabelle `exercisesheet`
--
ALTER TABLE `exercisesheet`
  ADD CONSTRAINT `exercisesheet_ibfk_1` FOREIGN KEY (`lectureevent`) REFERENCES `lectureevent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints der Tabelle `exercisesheet_exercise`
--
ALTER TABLE `exercisesheet_exercise`
  ADD CONSTRAINT `exercisesheet_exercise_ibfk_1` FOREIGN KEY (`exercisesheet`) REFERENCES `exercisesheet` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `exercisesheet_exercise_ibfk_2` FOREIGN KEY (`exercise`) REFERENCES `exercise` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints der Tabelle `keyword_exercise`
--
ALTER TABLE `keyword_exercise`
  ADD CONSTRAINT `keyword_exercise_ibfk_1` FOREIGN KEY (`exercise`) REFERENCES `exercise` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints der Tabelle `keyword_exercisesheet`
--
ALTER TABLE `keyword_exercisesheet`
  ADD CONSTRAINT `keyword_exercisesheet_ibfk_1` FOREIGN KEY (`exercisesheet`) REFERENCES `exercisesheet` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints der Tabelle `keyword_lecture`
--
ALTER TABLE `keyword_lecture`
  ADD CONSTRAINT `keyword_lecture_ibfk_1` FOREIGN KEY (`lecture`) REFERENCES `lecture` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints der Tabelle `lectureevent`
--
ALTER TABLE `lectureevent`
  ADD CONSTRAINT `lectureevent_ibfk_1` FOREIGN KEY (`lecture`) REFERENCES `lecture` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints der Tabelle `lectureevent_lecturer`
--
ALTER TABLE `lectureevent_lecturer`
  ADD CONSTRAINT `lectureevent_lecturer_ibfk_1` FOREIGN KEY (`lectureevent`) REFERENCES `lectureevent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `lectureevent_lecturer_ibfk_2` FOREIGN KEY (`lecturer`) REFERENCES `lecturer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints der Tabelle `lecture_exercise`
--
ALTER TABLE `lecture_exercise`
  ADD CONSTRAINT `lecture_exercise_ibfk_1` FOREIGN KEY (`lecture`) REFERENCES `lecture` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `lecture_exercise_ibfk_2` FOREIGN KEY (`exercise`) REFERENCES `exercise` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;