When designing this project, I started by briefly documenting the required events.
After that, I broke up the events into the uses cases. Documenting the uses cases allowed me to analyze the requirements of the program and get an idea of how the user would interact with the browser and how the browser would interact with the server.
Since the program takes a lot of different input in different forms, I felt it was important to analyze the control requirements.
After documenting the controls, I started to think more about the flow of the program. I did a mock up of the portals for both the student and teacher users, and drew a simple flow diagram for the teacher's course editing option.
Most of my design was done just writing out information on note paper but the last somewhat formal design document I made up was a small database schema, outlining my table requirements.
As you can see in the database schema above, I created several tables in the database. Once I started working with the tables, I quickly realized that I wasn't happy about how I set up the CourseContent table. I feel it is somewhat restrictive.
The first table I created was the Users table. To do this, I simply used: CREATE TABLE Users (userName VARCHAR(20) NOT NULL, password VARCHAR(255) NOT NULL, userType VARCHAR(10) NOT NULL, PRIMARY KEY (userName)).
I, then, created the Courses table with: CREATE TABLE Courses (courseNumber VARCHAR(15) NOT NULL, courseName VARCHAR(100), courseDescription VARCHAR(200), courseAuthor VARCHAR(20), PRIMARY KEY (courseNumber), FOREIGN KEY (courseAuthor) REFERENCES Users(userName)).
Next, I created the CourseContent table. This one has many columns. CREATE TABLE CourseContent (courseNumber VARCHAR(15) NOT NULL, unitNumber VARCHAR(15) NOT NULL, contentType VARCHAR(15), sectionOne TEXT, imageOne VARCHAR(50), sectionTwo TEXT, imageTwo VARCHAR(50), sectionThree TEXT, imageThree VARCHAR(50), linkOne VARCHAR(30), linkTwo VARCHAR(30), linkThree VARCHAR(30), question1 VARCHAR(100), 1a VARCHAR(100), 1b VARCHAR(100), 1c VARCHAR(100), 1d VARCHAR(100), correct1 VARCHAR(1), question2 VARCHAR(100), 2a VARCHAR(100), 2b VARCHAR(100), 2c VARCHAR(100), 2d VARCHAR(100), correct2 VARCHAR(1), question3 VARCHAR(100), 3a VARCHAR(100), 3b VARCHAR(100), 3c VARCHAR(100), 3d VARCHAR(100), correct3 VARCHAR(1), question4 VARCHAR(100), 4a VARCHAR(100), 4b VARCHAR(100), 4c VARCHAR(100), 4d VARCHAR(100), correct4 VARCHAR(1), question5 VARCHAR(100), 5a VARCHAR(100), 5b VARCHAR(100), 5c VARCHAR(100), 5d VARCHAR(100), correct5 VARCHAR(1), question6 VARCHAR(100), 6a VARCHAR(100), 6b VARCHAR(100), 6c VARCHAR(100), 6d VARCHAR(100), correct6 VARCHAR(1), question7 VARCHAR(100), 7a VARCHAR(100), 7b VARCHAR(100), 7c VARCHAR(100), 7d VARCHAR(100), correct7 VARCHAR(1), question8 VARCHAR(100), 8a VARCHAR(100), 8b VARCHAR(100), 8c VARCHAR(100), 8d VARCHAR(100), correct8 VARCHAR(1), question9 VARCHAR(100), 9a VARCHAR(100), 9b VARCHAR(100), 9c VARCHAR(100), 9d VARCHAR(100), correct9 VARCHAR(1), question10 VARCHAR(100), 10a VARCHAR(100), 10b VARCHAR(100), 10c VARCHAR(100), 10d VARCHAR(100), correct10 VARCHAR(1), PRIMARY KEY (courseNumber, unitNumber, contentType), FOREIGN KEY (courseNumber) REFERENCES Courses (courseNumber)). I found that 50 characters was not enough for the image columns and that 100 characters wasn't enough for the questions or answer options. I changed those fields to 100 and 200 characters to work. Also, there are columns for links but you may notice that those are not used in the EML. I felt that the cross site scripting vulnerability presented by that was too large to warrant the use of links so they are never used in my program, though they could easily be added.
Next, I created a table for courses that the student is currently working on. CREATE TABLE InProgress (userName VARCHAR(20) NOT NULL, courseNumber VARCHAR(15) NOT NULL, unitNumber VARCHAR(15) NOT NULL, courseName VARCHAR(100), PRIMARY KEY (courseNumber, userName), FOREIGN KEY (userName) REFERENCES Users(userName), FOREIGN KEY (courseNumber) REFERENCES Courses(courseNumber), FOREIGN KEY (courseName) REFERENCES Courses(courseName)). Now that I look at this, I realize there is a flaw in my table design. Unit number should be included in the primary key so that a student can have several units in progress. Perhaps this is something that should be checked with the teacher users, however, as a teacher may only want a student to complete one unit at a time.
The last table I created was the Completed table. CREATE TABLE Completed (userName VARCHAR(20) NOT NULL, courseNumber VARCHAR(15) NOT NULL, unitNumber VARCHAR(15) NOT NULL, grade INT, feedback TEXT, courseName VARCHAR(100), PRIMARY KEY (userName, courseNumber, unitNumber), FOREIGN KEY (userName) REFERENCES Users (userName)). For this table, I originally had courseNumber and unitNumber as foreign keys; however, I want the information to remain in this table if a teacher removes the courses so I removed the foreign key constraints.
For the Educational Markup Language, I wanted to keep things as simple as possible. I wanted to create a basic structure that could easily be added to as the website evolved. If this was something that others would use, I would encourage feedback from the users to create content that would fit their needs. This would be my prototype to let them get a feel for how the program would work and how they could use it.
I divided the content type into lessons and quizzes. The lessons simply have 3 sections with the option of adding 3 images. Each image follows a text section.
The quizzes have more elements. My EML allows up to 10 questions. Each question has a question section, four option sections, then a section for the correct option.
Link to the EML instructionsMy biggest problem with this assignment was not remembering that variables start with a $. It is so simple and makes sense but most of the time when my page wouldn't load, that was the cause. Other than that, I did have to Google how to do document uploads and downloads. For that, https://www.w3schools.com/php/php_file_upload.asp had the information I used. Since the teacher users upload XML documents to add course content, I wanted them to be able to download a functional XML document so they could see the current course content when editing. If you test this function, you will see that it mostly works. There is some content written to the top of the document that I couldn't find the source of. Another feature that would be great for this program would be an XSD that verifies the teacher's XML on upload. I didn't attempt that due to time constraints. As with the Bookmarks program, I tried to keep security in mind. To that end, I used prepared statements with my queries and as mentioned above, removed the ability to add links to the site. Lastly, I have two logins set up for testing. User name: Shari with pw: shari is a teacher user and user name: Test with pw: test is a student user. I set up both accounts with some examples of what users might see.