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(256), # comment about this assignment
27 timeposted timestamp NOT NULL, # time comment was posted
28 PRIMARY KEY (comment_id)
31 CREATE TABLE schedule (
32 sched_id int NOT NULL AUTO_INCREMENT,
33 class_id int NOT NULL, # class section number
34 assign_type int NOT NULL, # type of assignment
35 title varchar(256) NOT NULL, # title of assignment
36 chapter varchar(256) NOT NULL, # chapter number
37 section_id varchar(256) NOT NULL, # section number
38 ava_date DATETIME NOT NULL, # date for opening of assignment
39 due_date DATETIME NOT NULL, # due date for assignment
40 timeposted timestamp NOT NULL, # time posting
41 PRIMARY KEY (sched_id)
45 assign_type int NOT NULL,
46 type_name varchar(256),
47 PRIMARY KEY (assign_type)
50 INSERT INTO types values (0, "Final Exam");
51 INSERT INTO types values (1, "In-Class Practice Programs");
52 INSERT INTO types values (2, "Homework Programs");
53 INSERT INTO types values (3, "Chapter Test");
54 INSERT INTO types values (4, "Extra Credit");
57 class_id int NOT NULL,
58 class_name varchar(256) NOT NULL,
59 class_section varchar(256) NOT NULL,
60 class_location varchar(256),
61 class_instructor varchar(256) NOT NULL,
62 PRIMARY KEY (class_id)
65 CREATE TABLE enrollment (
66 enrollment_id int NOT NULL AUTO_INCREMENT,
67 class_id int NOT NULL,
69 PRIMARY KEY (enrollment_id)
74 sub_id int NOT NULL AUTO_INCREMENT, # address number - should be KEY to this Table
75 user_id int NOT NULL, # user ID
76 sched_id int NOT NULL, # schedule ID
77 time_post DATETIME NOT NULL, # time posting
83 file_id int NOT NULL AUTO_INCREMENT, # file number - should be KEY to this Table
84 sched_id int NOT NULL, # submission number
85 user_id int NOT NULL, # user ID who submitted file
86 file_1 text, # each file gets one column - not the best way but simple
87 file_name varchar(256), # original name of file
88 file_size int, # size of file in bytes
89 time_post DATETIME NOT NULL, # time file posted
94 user_id int NOT NULL AUTO_INCREMENT, #
95 email varchar(128) NOT NULL, #
96 password varchar(128) NOT NULL, #
97 name varchar(128) NOT NULL, # name of user
98 attempts int NOT NULL, # number of bad attempts to login
99 role int NOT NULL, # 0 is prof, 1 is student
100 first_login int NOT NULL, # 0 is false, 1 is true
101 PRIMARY KEY (user_id)
104 # initial root account with default password
105 insert into users values ("", "steven.schronk@my.tccd.edu", "password", "Schronk, Steven", 0, 0, 1);