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 timeposted timestamp NOT NULL, # time comment was posted
39 PRIMARY KEY (detail_id)
42 CREATE TABLE schedule (
43 sched_id int NOT NULL AUTO_INCREMENT,
44 class_id int NOT NULL, # class section number
45 assign_type int NOT NULL, # type of assignment
46 title varchar(256) NOT NULL, # title of assignment
47 chapter varchar(256) NOT NULL, # chapter number
48 section_id varchar(256) NOT NULL, # section number
49 ava_date DATETIME NOT NULL, # date for opening of assignment
50 due_date DATETIME NOT NULL, # due date for assignment
51 timeposted timestamp NOT NULL, # time posting
52 graded int NOT NULL, # 0 for no and 1 for yes
53 PRIMARY KEY (sched_id)
57 assign_type int NOT NULL,
58 type_name varchar(256),
59 PRIMARY KEY (assign_type)
62 INSERT INTO types values (0, "Final Exam");
63 INSERT INTO types values (1, "In-Class Practice Programs");
64 INSERT INTO types values (2, "Homework Programs");
65 INSERT INTO types values (3, "Chapter Test");
66 INSERT INTO types values (4, "Extra Credit");
69 class_id int NOT NULL,
70 class_name varchar(256) NOT NULL,
71 class_section varchar(256) NOT NULL,
72 class_location varchar(256),
73 class_instructor varchar(256) NOT NULL,
74 PRIMARY KEY (class_id)
77 CREATE TABLE enrollment (
78 enrollment_id int NOT NULL AUTO_INCREMENT,
79 class_id int NOT NULL,
81 PRIMARY KEY (enrollment_id)
86 sub_id int NOT NULL AUTO_INCREMENT, # address number - should be KEY to this Table
87 user_id int NOT NULL, # user ID
88 sched_id int NOT NULL, # schedule ID
89 time_post DATETIME NOT NULL, # time posting
95 file_id int NOT NULL AUTO_INCREMENT, # file number - should be KEY to this Table
96 sched_id int NOT NULL, # submission number
97 user_id int NOT NULL, # user ID who submitted file
98 file_1 text, # each file gets one column - not the best way but simple
99 file_name varchar(256), # original name of file
100 file_size int, # size of file in bytes
101 time_post DATETIME NOT NULL, # time file posted
102 PRIMARY KEY (file_id)
106 user_id int NOT NULL AUTO_INCREMENT, #
107 email varchar(128) NOT NULL, #
108 password varchar(128) NOT NULL, #
109 name varchar(128) NOT NULL, # name of user
110 attempts int NOT NULL, # number of bad attempts to login
111 role int NOT NULL, # 0 is prof, 1 is student
112 first_login int NOT NULL, # 0 is false, 1 is true
113 PRIMARY KEY (user_id)
116 # initial root account with default password
117 insert into users values ("", "steven.schronk@my.tccd.edu", "password", "Schronk, Steven", 0, 0, 1);