Structure.sql

-- phpMyAdmin SQL Dump
-- version 4.0.10
-- http://www.phpmyadmin.net
--
-- Хост: 127.0.0.1:3306
-- Время создания: Авг 22 2014 г., 14:07
-- Версия сервера: 5.5.37-log
-- Версия PHP: 5.3.28

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!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 */;

--
-- База данных: `Sample2`
--

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

--
-- Структура таблицы `accounts`
--

CREATE TABLE IF NOT EXISTS `accounts` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Login` varchar(50) DEFAULT NULL,
  `Password` varchar(64) DEFAULT NULL,
  `EMail` varchar(255) DEFAULT NULL,
  `UserRoleID` int(11) NOT NULL,
  `ActivationCode` varchar(40) DEFAULT NULL,
  `isEnabled` tinyint(1) NOT NULL DEFAULT '1',
  `UserAgent` text,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Login` (`Login`),
  UNIQUE KEY `EMail` (`EMail`),
  KEY `UserRoleID` (`UserRoleID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


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

--
-- Структура таблицы `accounts`
--

CREATE TABLE IF NOT EXISTS `job_positions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `departments`
--

CREATE TABLE IF NOT EXISTS `departments` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) NOT NULL,
  `FacultyID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`,`FacultyID`),
  KEY `FacultyID` (`FacultyID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `disciplines`
--

CREATE TABLE IF NOT EXISTS `disciplines` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Grade` int(11) NOT NULL,
  `SubjectID` int(11) NOT NULL,
  `AuthorID` int(11) NOT NULL,
  `ExamType` enum('exam','credit') NOT NULL,
  `SemesterID` int(11) NOT NULL,
  `PracticeCount` int(11) NOT NULL DEFAULT '0',
  `LectionCount` int(11) NOT NULL DEFAULT '0',
  `FacultyID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `SubjectID` (`SubjectID`),
  KEY `TeacherID` (`AuthorID`),
  KEY `SemesterID` (`SemesterID`),
  KEY `FacultyID` (`FacultyID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `disciplines_groups`
--

CREATE TABLE IF NOT EXISTS `disciplines_groups` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DisciplineID` int(11) NOT NULL,
  `StudyGroupID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `DisciplineID_2` (`DisciplineID`,`StudyGroupID`),
  KEY `DisciplineID` (`DisciplineID`),
  KEY `StudyGroupID` (`StudyGroupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `disciplines_students`
--

CREATE TABLE IF NOT EXISTS `disciplines_students` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DisciplineID` int(11) NOT NULL,
  `StudentID` int(11) NOT NULL,
  `Type` enum('attach','detach') NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `DisciplineID_2` (`DisciplineID`,`StudentID`),
  KEY `DisciplineID` (`DisciplineID`),
  KEY `StudentID` (`StudentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `disciplines_teachers`
--

CREATE TABLE IF NOT EXISTS `disciplines_teachers` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DisciplineID` int(11) NOT NULL,
  `TeacherID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `DisciplineID_2` (`DisciplineID`,`TeacherID`),
  KEY `DisciplineID` (`DisciplineID`),
  KEY `AccountID` (`TeacherID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `faculties`
--

CREATE TABLE IF NOT EXISTS `faculties` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `Abbr` varchar(20) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `general_settings`
--

CREATE TABLE IF NOT EXISTS `general_settings` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Val` int(11) DEFAULT NULL,
  `ValS` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `logs`
--

CREATE TABLE IF NOT EXISTS `logs` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `RatingTableID` int(11) NOT NULL,
  `AccountID` int(11) NOT NULL,
  `OldValue` int(11) NOT NULL,
  `NewValue` int(11) NOT NULL,
  `Date` datetime NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `RatingTableID` (`RatingTableID`),
  KEY `AccountID` (`AccountID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `modules`
--

CREATE TABLE IF NOT EXISTS `modules` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `OrderNum` int(11) NOT NULL,
  `DisciplineID` int(11) NOT NULL,
  `isExam` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `OrderNum` (`OrderNum`,`DisciplineID`),
  KEY `DisciplineID` (`DisciplineID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `page_access`
--

CREATE TABLE IF NOT EXISTS `page_access` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Pagename` text NOT NULL,
  `Bitmask` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `rating_table`
--

CREATE TABLE IF NOT EXISTS `rating_table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `StudentID` int(11) NOT NULL,
  `TeacherID` int(11) NOT NULL,
  `SubmoduleID` int(11) NOT NULL,
  `Rate` int(11) NOT NULL,
  `Date` date NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `StudentID_2` (`StudentID`,`SubmoduleID`),
  KEY `StudentID` (`StudentID`),
  KEY `SubmoduleID` (`SubmoduleID`),
  KEY `TeacherID` (`TeacherID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `requests`
--

CREATE TABLE IF NOT EXISTS `requests` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `AccountID` int(11) DEFAULT NULL,
  `Information` text NOT NULL,
  `Date` date NOT NULL,
  `Status` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `AccountID` (`AccountID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `semesters`
--

CREATE TABLE IF NOT EXISTS `semesters` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Year` int(11) NOT NULL,
  `Num` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Year` (`Year`,`Num`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `specializations`
--

CREATE TABLE IF NOT EXISTS `specializations` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Abbr` varchar(20) NOT NULL,
  `FacultyID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`,`FacultyID`),
  KEY `FacultyID` (`FacultyID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `students`
--

CREATE TABLE IF NOT EXISTS `students` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `StudyGroupID` int(11) NOT NULL,
  `AccountID` int(11) NOT NULL,
  `LastName` varchar(30) NOT NULL,
  `FirstName` varchar(30) NOT NULL,
  `SecondName` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `StudyGroupID` (`StudyGroupID`),
  KEY `AccountID` (`AccountID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `study_groups`
--

CREATE TABLE IF NOT EXISTS `study_groups` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Grade` int(11) NOT NULL,
  `GroupNum` int(11) NOT NULL,
  `SpecializationID` int(11) NOT NULL,
  `Name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Grade` (`Grade`,`GroupNum`,`SpecializationID`),
  KEY `SpecializtionID` (`SpecializationID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `subjects`
--

CREATE TABLE IF NOT EXISTS `subjects` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Abbr` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `submodules`
--

CREATE TABLE IF NOT EXISTS `submodules` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ModuleID` int(11) NOT NULL,
  `MaxRate` int(11) NOT NULL,
  `OrderNum` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Description` varchar(200) DEFAULT NULL,
  `Type` enum('CurrentControl','LandmarkControl') NOT NULL DEFAULT 'CurrentControl',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ModuleID_2` (`ModuleID`,`OrderNum`),
  KEY `ModuleID` (`ModuleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `teachers`
--

CREATE TABLE IF NOT EXISTS `teachers` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `LastName` varchar(30) NOT NULL,
  `FirstName` varchar(30) NOT NULL,
  `SecondName` varchar(30) DEFAULT NULL,
  `JobPositionID` int(11) NOT NULL,
  `DepartmentID` int(11) NOT NULL,
  `AccountID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FacultyID` (`DepartmentID`),
  KEY `AccountID` (`AccountID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

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

--
-- Структура таблицы `user_roles`
--

CREATE TABLE IF NOT EXISTS `user_roles` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Type` enum('student','teacher') NOT NULL,
  `RoleName` varchar(30) NOT NULL,
  `Mark` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Дамп данных таблицы `user_roles`
--

INSERT INTO `user_roles` (`ID`, `Type`, `RoleName`, `Mark`) VALUES
(1, 'student', 'Студент', 1),
(2, 'teacher', 'Преподаватель', 2),
(3, 'teacher', 'Преподаватель-Администратор', 4);

--
-- Дамп данных таблицы `user_roles`
--

INSERT INTO `page_access` (`ID`, `Pagename`, `Bitmask`) VALUES
(1, 'common:index', 7),
(2, 'common:settings', 7),
(3, 'teacher:index', 6),
(4, 'teacher:settings', 6),
(5, 'teacher:map:create', 6),
(6, 'teacher:map:edit', 6),
(7, 'teacher:rating', 6),
(8, 'admin:common', 4),
(9, 'student:index', 1),
(10, 'student:settings', 1),
(11, 'student:subject', 1),
(12, 'teacher:map:discipline', 6),
(13, 'teacher:map:structure', 6),
(14, 'teacher:map:groups', 6),
(15, 'teacher:map:students', 6),
(16, 'teacher:map:teachers', 6);


INSERT INTO `general_settings` (`ID`, `Val`, `ValS`) VALUES
(1, 1, NULL),
(2, NULL, 'sdhrhsdrh');

--
-- Ограничения внешнего ключа сохраненных таблиц
--

--
-- Ограничения внешнего ключа таблицы `accounts`
--
ALTER TABLE `accounts`
  ADD CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`UserRoleID`) REFERENCES `user_roles` (`ID`);

--
-- Ограничения внешнего ключа таблицы `departments`
--
ALTER TABLE `departments`
  ADD CONSTRAINT `departments_ibfk_1` FOREIGN KEY (`FacultyID`) REFERENCES `faculties` (`ID`);

--
-- Ограничения внешнего ключа таблицы `disciplines`
--
ALTER TABLE `disciplines`
  ADD CONSTRAINT `disciplines_ibfk_2` FOREIGN KEY (`SubjectID`) REFERENCES `subjects` (`ID`),
  ADD CONSTRAINT `disciplines_ibfk_4` FOREIGN KEY (`SemesterID`) REFERENCES `semesters` (`ID`),
  ADD CONSTRAINT `disciplines_ibfk_5` FOREIGN KEY (`AuthorID`) REFERENCES `teachers` (`ID`),
  ADD CONSTRAINT `disciplines_ibfk_6` FOREIGN KEY (`FacultyID`) REFERENCES `faculties` (`ID`);

--
-- Ограничения внешнего ключа таблицы `disciplines_groups`
--
ALTER TABLE `disciplines_groups`
  ADD CONSTRAINT `disciplines_groups_ibfk_1` FOREIGN KEY (`DisciplineID`) REFERENCES `disciplines` (`ID`),
  ADD CONSTRAINT `disciplines_groups_ibfk_2` FOREIGN KEY (`StudyGroupID`) REFERENCES `study_groups` (`ID`);

--
-- Ограничения внешнего ключа таблицы `disciplines_students`
--
ALTER TABLE `disciplines_students`
  ADD CONSTRAINT `disciplines_students_ibfk_1` FOREIGN KEY (`DisciplineID`) REFERENCES `disciplines` (`ID`),
  ADD CONSTRAINT `disciplines_students_ibfk_2` FOREIGN KEY (`StudentID`) REFERENCES `students` (`ID`);

--
-- Ограничения внешнего ключа таблицы `disciplines_teachers`
--
ALTER TABLE `disciplines_teachers`
  ADD CONSTRAINT `disciplines_teachers_ibfk_1` FOREIGN KEY (`DisciplineID`) REFERENCES `disciplines` (`ID`),
  ADD CONSTRAINT `disciplines_teachers_ibfk_2` FOREIGN KEY (`TeacherID`) REFERENCES `teachers` (`ID`);

--
-- Ограничения внешнего ключа таблицы `logs`
--
ALTER TABLE `logs`
  ADD CONSTRAINT `logs_ibfk_1` FOREIGN KEY (`RatingTableID`) REFERENCES `rating_table` (`ID`),
  ADD CONSTRAINT `logs_ibfk_2` FOREIGN KEY (`AccountID`) REFERENCES `accounts` (`ID`);

--
-- Ограничения внешнего ключа таблицы `modules`
--
ALTER TABLE `modules`
  ADD CONSTRAINT `modules_ibfk_2` FOREIGN KEY (`DisciplineID`) REFERENCES `disciplines` (`ID`);

--
-- Ограничения внешнего ключа таблицы `rating_table`
--
ALTER TABLE `rating_table`
  ADD CONSTRAINT `rating_table_ibfk_1` FOREIGN KEY (`StudentID`) REFERENCES `students` (`ID`),
  ADD CONSTRAINT `rating_table_ibfk_3` FOREIGN KEY (`SubmoduleID`) REFERENCES `submodules` (`ID`),
  ADD CONSTRAINT `rating_table_ibfk_4` FOREIGN KEY (`TeacherID`) REFERENCES `teachers` (`ID`);

--
-- Ограничения внешнего ключа таблицы `requests`
--
ALTER TABLE `requests`
  ADD CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`AccountID`) REFERENCES `accounts` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL;

--
-- Ограничения внешнего ключа таблицы `specializations`
--
ALTER TABLE `specializations`
  ADD CONSTRAINT `specializations_ibfk_1` FOREIGN KEY (`FacultyID`) REFERENCES `faculties` (`ID`);

--
-- Ограничения внешнего ключа таблицы `students`
--
ALTER TABLE `students`
  ADD CONSTRAINT `students_ibfk_1` FOREIGN KEY (`StudyGroupID`) REFERENCES `study_groups` (`ID`),
  ADD CONSTRAINT `students_ibfk_2` FOREIGN KEY (`AccountID`) REFERENCES `accounts` (`ID`);

--
-- Ограничения внешнего ключа таблицы `study_groups`
--
ALTER TABLE `study_groups`
  ADD CONSTRAINT `study_groups_ibfk_4` FOREIGN KEY (`SpecializationID`) REFERENCES `specializations` (`ID`);

--
-- Ограничения внешнего ключа таблицы `submodules`
--
ALTER TABLE `submodules`
  ADD CONSTRAINT `submodules_ibfk_1` FOREIGN KEY (`ModuleID`) REFERENCES `modules` (`ID`);

--
-- Ограничения внешнего ключа таблицы `teachers`
--
ALTER TABLE `teachers`
  ADD CONSTRAINT `teachers_ibfk_2` FOREIGN KEY (`AccountID`) REFERENCES `accounts` (`ID`),
  ADD CONSTRAINT `teachers_ibfk_3` FOREIGN KEY (`DepartmentID`) REFERENCES `departments` (`ID`);

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