Database Schema

CREATE TABLE ASSESSMENTS (

ID INT PRIMARY KEY NOT NULL,
TITLE TEXT NOT NULL,
DESCRIPTION TEXT,
NUM_QUESTIONS INT

);

TABLE ASSESSMENT_DATA (

AID INT NOT NULL,
QUESTION INT NOT NULL,
DIFFICULTY TEXT NOT NULL,
AUTHOR CHAR(20),
STATUS TEXT,
DOMAIN INT NOT NULL,
GROUPS INT NOT NULL,
COMPETENCY CHAR(2) NOT NULL,
DESCRIPTION TEXT,
FOREIGN KEY(AID) REFERENCES ASSESSMENTS(ID)

);

All question meta data is stored here with a reference to its pertaining assessment, ASSESSMENTS->ID.

CREATE TABLE CSC_PROFILES (

ID INT PRIMARY KEY NOT NULL,
CSC_EMAIL TEXT NOT NULL,
ACADEMIC_MAJOR TEXT,
HIGHEST_DEGREE CHAR(10),
CLASSROOMTEACHER BOOLEAN,
EXPERIENCE_CS_YEARS INT,
EXPERIENCE_PROJECT_YEARS INT,
CTCS_COLLAB INT,
EXPERIENCE_TEACHING_YEARS INT,
GENDER CHAR(10),
INCOMPLETE_PROFILE BOOLEAN,
FIRST_NAME TEXT,
LAST_NAME TEXT,
REGION INT,
STATE CHAR(15),
PLAN_CERTIFICATE CHAR(4),
TEACHING_LEVEL TEXT,
DOB CHAR(10),
FINAL_SUBMIT BOOLEAN,
SCHOOL_CAMPUS TEXT,
SCHOOLOFFERS_APCSA CHAR(3)

);

TABLE RESPONSES (

ASSESSMENT_ID INT NOT NULL,
EDX_QUESTION_ID TEXT NOT NULL,
USER_ID INT NOT NULL,
QUESTION_ID INT NOT NULL,
ATTEMPTS INT,
SEED INT,
DONE BOOLEAN,
ANSWER_CHOICE CHAR(15),
CORRECT BOOLEAN,
LAST_SUBMISSION DATETIME,
FOREIGN KEY(ASSESSMENT_ID) REFERENCES ASSESSMENTS(ID),
FOREIGN KEY(QUESTION_ID) REFERENCES DIAGNOSTIC_QUESTIONS(QUESTION),
FOREIGN KEY(USER_ID) REFERENCES PROFILES(ID)

);

TABLE GRADES (

ID INT NOT NULL,
ASSESSMENT_ID INT NOT NULL,
GRADE REAL NOT NULL,
FOREIGN KEY(ID) REFERENCES PROFILES(ID),
FOREIGN KEY(ASSESSMENT_ID) REFERENCES ASSESSMENTS(ID)

);

TABLE PROFILES (

ID INT PRIMARY KEY NOT NULL,
USERNAME TEXT NOT NULL,
NAME TEXT NOT NULL,
EMAIL TEXT NOT NULL,
LANGUAGE CHAR(50),
LOCATION TEXT,
YOB INT,
GENDER CHAR(10),
EDUCATION CHAR(10),
MAIL TEXT,
GOALS TEXT,
CITY CHAR(20),
COUNTRY CHAR(10)

);