kRHKrZ6F

1/*
2 Navicat Premium Data Transfer
3
4 Source Server         : localhost
5 Source Server Type    : MariaDB
6 Source Server Version : 100110
7 Source Host           : localhost:3306
8 Source Schema         : ir2_courses
9
10 Target Server Type    : MariaDB
11 Target Server Version : 100110
12 File Encoding         : 65001
13
14 Date: 27/12/2018 14:07:36
15*/
16
17SET NAMES utf8mb4;
18SET FOREIGN_KEY_CHECKS = 0;
19
20-- ----------------------------
21-- Table structure for course
22-- ----------------------------
23DROP TABLE IF EXISTS `course`;
24CREATE TABLE `course`  (
25  `course_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
26  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
27  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
28  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
29  `teacher_id` int(10) UNSIGNED NOT NULL,
30  `programme_id` int(10) UNSIGNED NOT NULL,
31  `study_year` enum('1','2','3','4') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
32  PRIMARY KEY (`course_id`) USING BTREE,
33  INDEX `fk_course_teacher_id`(`teacher_id`) USING BTREE,
34  INDEX `fk_course_programme_id`(`programme_id`) USING BTREE,
35  CONSTRAINT `fk_course_programme_id` FOREIGN KEY (`programme_id`) REFERENCES `programme` (`programme_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
36  CONSTRAINT `fk_course_teacher_id` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`teacher_id`) ON DELETE RESTRICT ON UPDATE CASCADE
37) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;
38
39-- ----------------------------
40-- Table structure for faculty
41-- ----------------------------
42DROP TABLE IF EXISTS `faculty`;
43CREATE TABLE `faculty`  (
44  `faculty_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
45  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
46  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
47  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
48  PRIMARY KEY (`faculty_id`) USING BTREE,
49  UNIQUE INDEX `uq_faculty_name`(`name`) USING BTREE
50) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;
51
52-- ----------------------------
53-- Table structure for material
54-- ----------------------------
55DROP TABLE IF EXISTS `material`;
56CREATE TABLE `material`  (
57  `material_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
58  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
59  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
60  `course_id` int(10) UNSIGNED NOT NULL,
61  `category` enum('lecture','exercise','exam') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
62  `file_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
63  `size` decimal(10, 2) UNSIGNED NOT NULL,
64  `file_type` enum('word_document','excel_spreadsheet','pdf_document','powerpoint_presentation','7zip_archive') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
65  `uploaded_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
66  PRIMARY KEY (`material_id`) USING BTREE,
67  UNIQUE INDEX `uq_material_file_name`(`file_name`) USING BTREE,
68  INDEX `fk_material_course_id`(`course_id`) USING BTREE,
69  CONSTRAINT `fk_material_course_id` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE RESTRICT ON UPDATE CASCADE
70) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;
71
72-- ----------------------------
73-- Table structure for programme
74-- ----------------------------
75DROP TABLE IF EXISTS `programme`;
76CREATE TABLE `programme`  (
77  `programme_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
78  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
79  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
80  `faculty_id` int(10) UNSIGNED NOT NULL,
81  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
82  PRIMARY KEY (`programme_id`) USING BTREE,
83  INDEX `fk_programme_faculty_id`(`faculty_id`) USING BTREE,
84  UNIQUE INDEX `uq_programme_name`(`name`) USING BTREE,
85  CONSTRAINT `fk_programme_faculty_id` FOREIGN KEY (`faculty_id`) REFERENCES `faculty` (`faculty_id`) ON DELETE RESTRICT ON UPDATE CASCADE
86) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;
87
88-- ----------------------------
89-- Table structure for teacher
90-- ----------------------------
91DROP TABLE IF EXISTS `teacher`;
92CREATE TABLE `teacher`  (
93  `teacher_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
94  `username` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
95  `forename` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
96  `surname` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
97  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
98  `password_hash` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
99  `created_at` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
100  `is_active` tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
101  PRIMARY KEY (`teacher_id`) USING BTREE,
102  UNIQUE INDEX `uq_teacher_username`(`username`) USING BTREE,
103  UNIQUE INDEX `uq_teacher_email`(`email`) USING BTREE
104) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;
105
106SET FOREIGN_KEY_CHECKS = 1;