Repository navigation
Expand file tree
/
Copy pathstudyflow.sql
More file actions
63 lines (57 loc) · 2.4 KB
/
Copy pathstudyflow.sql
File metadata and controls
63 lines (57 loc) · 2.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- StudyFlow Database Schema
-- Database: studyflow
CREATE DATABASE IF NOT EXISTS studyflow;
USE studyflow;
-- --------------------------------------------------------
-- Table structure for `tasks`
-- --------------------------------------------------------
DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks (
task_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
subject VARCHAR(100) NOT NULL,
priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
deadline DATETIME NOT NULL,
status ENUM('pending', 'completed') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- --------------------------------------------------------
-- Table structure for `study_sessions`
-- --------------------------------------------------------
DROP TABLE IF EXISTS study_sessions;
CREATE TABLE study_sessions (
session_id INT AUTO_INCREMENT PRIMARY KEY,
subject VARCHAR(100) NOT NULL,
duration INT NOT NULL COMMENT 'Duration in minutes',
completed_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- --------------------------------------------------------
-- Table structure for `resources`
-- --------------------------------------------------------
DROP TABLE IF EXISTS resources;
CREATE TABLE resources (
resource_id INT AUTO_INCREMENT PRIMARY KEY,
subject VARCHAR(100) NOT NULL,
topic VARCHAR(200) NOT NULL,
difficulty ENUM('Beginner', 'Intermediate', 'Advanced') DEFAULT 'Beginner',
description TEXT NOT NULL,
url VARCHAR(500) DEFAULT '#'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- --------------------------------------------------------
-- Table structure for `contact_messages`
-- --------------------------------------------------------
DROP TABLE IF EXISTS contact_messages;
CREATE TABLE contact_messages (
contact_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
subject VARCHAR(200) NOT NULL,
message TEXT NOT NULL,
submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Create Indexes for fast querying
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_priority ON tasks(priority);
CREATE INDEX idx_tasks_deadline ON tasks(deadline);
CREATE INDEX idx_sessions_date ON study_sessions(completed_date);
CREATE INDEX idx_resources_subject ON resources(subject);