1 # Creates Database for Assignment Trapper
3 # Use this command to run this script:
5 # mysql -u root -p < create_db.sql
7 CREATE DATABASE trapper;
10 CREATE TABLE filecom (
11 filecom_id int NOT NULL AUTO_INCREMENT,
12 file_id int NOT NULL, # file ID
13 line_no int NOT NULL, # line number in file
14 user_id int NOT NULL, # user who made the comment
15 txt varchar(128), # comment made about line
16 timeposted timestamp NOT NULL, # time comment was posted
17 PRIMARY KEY (filecom_id)
20 CREATE TABLE comments (
21 comment_id int NOT NULL AUTO_INCREMENT,
22 user_id int NOT NULL, # user ID - person who commented
23 sub_id int NOT NULL, # submission ID
24 fac_id int, # faculty identification id for faculty comments
25 role int NOT NULL, # 0 is prof, 1 is student
26 txt varchar(65536), # comment about this assignment
27 timeposted timestamp NOT NULL, # time comment was posted
28 PRIMARY KEY (comment_id)
31 CREATE TABLE sched_details (
32 detail_id int NOT NULL AUTO_INCREMENT,
33 sched_id int NOT NULL,
35 user_viewed int, # comments have been viewed by user
36 fac_viewed int, # comments have been viewed by faculty
37 help_me int, # students can ask for help on thier assignments
38 late int, # students who turn in work late have assignment permanently marked
39 timeposted timestamp NOT NULL, # time comment was posted
40 PRIMARY KEY (detail_id)
43 # alter table sched_details add column late int;
45 CREATE TABLE schedule (
46 sched_id int NOT NULL AUTO_INCREMENT,
47 class_id int NOT NULL, # class section number
48 assign_type int NOT NULL, # type of assignment
49 title varchar(256) NOT NULL, # title of assignment
50 chapter varchar(256) NOT NULL, # chapter number
51 section_id varchar(256) NOT NULL, # section number
52 ava_date DATETIME NOT NULL, # date for opening of assignment
53 due_date DATETIME NOT NULL, # due date for assignment
54 timeposted timestamp NOT NULL, # time posting
55 graded int NOT NULL, # 0 for no and 1 for yes
56 PRIMARY KEY (sched_id)
60 assign_type int NOT NULL,
61 type_name varchar(256),
62 PRIMARY KEY (assign_type)
65 INSERT INTO types values (0, "Final Exam");
66 INSERT INTO types values (1, "In-Class Practice Programs");
67 INSERT INTO types values (2, "Homework Programs");
68 INSERT INTO types values (3, "Chapter Test");
69 INSERT INTO types values (4, "Extra Credit");
72 class_id int NOT NULL,
73 class_name varchar(256) NOT NULL,
74 class_section varchar(256) NOT NULL,
75 class_location varchar(256),
76 class_instructor varchar(256) NOT NULL,
77 PRIMARY KEY (class_id)
80 CREATE TABLE enrollment (
81 enrollment_id int NOT NULL AUTO_INCREMENT,
82 class_id int NOT NULL,
84 PRIMARY KEY (enrollment_id)
89 sub_id int NOT NULL AUTO_INCREMENT, # address number - should be KEY to this Table
90 user_id int NOT NULL, # user ID
91 sched_id int NOT NULL, # schedule ID
92 time_post DATETIME NOT NULL, # time posting
98 file_id int NOT NULL AUTO_INCREMENT, # file number - should be KEY to this Table
99 sched_id int NOT NULL, # submission number
100 user_id int NOT NULL, # user ID who submitted file
101 file_1 text, # each file gets one column - not the best way but simple
102 file_name varchar(256), # original name of file
103 file_size int, # size of file in bytes
104 time_post DATETIME NOT NULL, # time file posted
105 PRIMARY KEY (file_id)
109 chat_id int NOT NULL AUTO_INCREMENT,
110 user_id int NOT NULL,
111 content varchar(256) NOT NULL,
112 chat_time timestamp NOT NULL,
117 user_id int NOT NULL AUTO_INCREMENT, #
118 email varchar(128) NOT NULL, #
119 password varchar(128) NOT NULL, #
120 name varchar(128) NOT NULL, # name of user
121 attempts int NOT NULL, # number of bad attempts to login
122 role int NOT NULL, # 0 is prof, 1 is student
123 first_login int NOT NULL, # 0 is false, 1 is true
124 last_click timestamp,
125 PRIMARY KEY (user_id)
128 # initial root account with default password
129 insert into users values ("", "steven.schronk@my.tccd.edu", "password", "Schronk, Steven", 0, 0, 1);