IIS3 Major Project Year 11 IPT

Contents

1. Design

1.1 Problem Definition

1.2 Rationale

1.3 Input/Output Requirements

1.5 Web Interface Design

2. Develop

2.1 Elementary Sentences

2.2 Conceptual Schema

2.3 Relational Schema

2.4 Tables – SQL Code (PHPMyAdmin)

2.5 Web Interface Implementation (interface screenshots)

2.7 SQL Queries

achievements.php

chooselesson.php

createlessonproc.php

homework.php

leaderboard.php

lesson.php

lessonproc.php

lessonresults.php

loginproc.php

registerproc.php

results.php

sethomeworkproc.php

userindex.php

2.8 Testing

3. Evaluation

3.1 Product Evaluation

3.2 Process Evaluation

3.3 Peer Evaluation

1. Design

1.1 Problem Definition

Nowadays technology dominates our lives. The presence of Information Systems throughout each element of our society borders on the level of omnipresence - in information terminals, transaction points, online banking and in our pockets. The only other thing that is just as pervasive throughout our society is our inability to type properly when interacting with this technology.

The aim of this production (DotKey) is to provide a simple, fully customisable, programmable and intuitive typing tutor / assistant that will correct society’s ineptitude when it comes to typing. It teaches children and adults alike how to effectively use and manage their keyboards. It is in the form of a web application that allows users to be able to access it on any of their devices that use a physical keyboard. The application’s ability to be simple / intuitive to use, as well as highly powerful means that it is a useful tool for both educators, people new to computing, or people that just want to learn how to be more efficient with their keyboard.

1.2 Rationale

Efficiency is appreciated in all fields of work, and in the modern age, practically all fields of work require interaction with an Information System. Thus it logically follows that it is beneficial to learn to efficiently interact with these systems. DotKey aims to provide its users with the skills that they need in order to best use the most common tool for interaction with computers – a keyboard.

DotKey systematically solves typing incompetence by developing the user’s skills from the ground-up. It teaches basic typing techniques, and then gives users the ability to practice these techniques in order to further their typing ability. It tracks user’s progress within the database to give them incentive to better themselves, and to compete with their peers. Finally it gives the ability to administrate for other users, and to give them directives as to lessons they are required to complete.

1.3 Input/Output Requirements

Users

FirstName

Lastname

AccountName

BirthDate

Grade

School

Role(Supervisor,Subordinate)

Lesson

Name

Difficulty

Content

User-Lesson

UserID

LessonId

KPM

WPM

KeysPressed

TimeSpent

Errors

Score

Achievements

AchievementID

UserID

Name

Homework

UserID

LessonID

1.4 Aims & Objectives

This system aims to teach its users how to properly use their keyboards and maximise their typing speed and accuracy. It should do this in a way that prioritises an easy to use interface that makes learning simple and fun. The site will allow users to learn using prebuilt typing lessons, randomly generated lessons, and lessons assigned to them by a supervisor. It provides a simple customisable interface that makes it easy for users to get what they want out of it. It ranks users, classes and schools based on a variety of scores generated from their learning sessions in order to give students further initiative by introducing competition. Users who perform exceedingly well will receive achievements for their efforts

It will store the following data:

User account information (Name, age, password, etc.)
Supervisor account information (Name, age, password, etc.)
User account scores (KPM, WPM, Errors, etc.)
User achievements
Individual lesson scores (KPM, WPM, Errors, etc.)
Class scores (KPM, WPM, Errors, etc.)
School scores (KPM, WPM, Errors, etc.)
User’s homework tasks
User’s custom lessons

It will provide forms for inserting:

User account information (Name, age, password, etc.)
Supervisor account information (Name, age, password, etc.)
User’s homework tasks
User’s custom lessons

It will produce queries showing:

User account information (Name, age, password, etc.)
Supervisor account information (Name, age, password, etc.)
User account scores (KPM, WPM, Errors, etc.)
User achievements
Individual lesson scores (KPM, WPM, Errors, etc.)
Class scores (KPM, WPM, Errors, etc.)
School scores (KPM, WPM, Errors, etc.)
User’s homework tasks
Student leader boards
Class leader boards
School leader boards

1.5 Web Interface Design

index.php - The application's index, links to all other pages

userindex.php - The user's index, links to all their specific functional pages

chooselesson.php - The lesson chooser, users select the lesson they want to complete

lesson.php - The lesson page, users can type out their chosen lesson

lessonproc.php - The processor, updates the user's information in the database

lessonresults.php –Displays the results for the previously completed lesson

createlesson.php - The lesson creator, users provide their own custom lessons

creatlessonproc.php - The processor, verifies the user's lesson and inserts it into the database

learn.php - The learning page, users can learn typing skills through a series of video tutorials

results.php - The results page, users can view a variety of results ranging in scope from a specific lesson through to a school

settings.php - The settings page, allows users to customise aesthetics

homework.php - The homework page, users view their assigned homework

sethomework.php - The homework setting page, supervisors assign homework

sethomeworkproc.php - The processing page, set homework is processed

achievements.php - The achievements page, users can view their achievements

loginproc.php - The processing page, checks the login details and assigns session values

registerproc.php - The processing page, checks details and updates the database

2. Develop

2.1 Elementary Sentences

User with ID has FirstName with varchar

User with ID has Lastname with varchar

User with ID has AccountName with varchar

User with ID has BirthDate with date

User with ID has Password with varchar

User with ID has Grade with varchar

User with ID has School with varchar

User with ID has Role with ENUM(Student, Supervisor)

Lesson with ID has Name with varchar

Lesson with ID has Difficulty with integer

Lesson with ID has Content with varchar

(User-Lesson) has KPM with integer

(User-Lesson) has WPM with integer

(User-Lesson) has KeysPressed with integer

(User-Lesson) has TimeSpent with integer

(User-Lesson) has Errors with integer

(User-Lesson) has Score with integer

Achievement with ID has UserID with ID

Achievement with ID has Name with varchar

User with ID required to complete Lesson with ID

2.2 Conceptual Schema

Download here:

2.3 Relational Schema

Users(ID,FirstName,Lastname,AccountName,BirthDate,Grade,School,Role)

OpOp

Lesson(ID,Name,Difficulty,Content)

1-5

User-Lesson(UserID,LessonId,KPM,WPM,KeysPressed,TimeSpent,Errors,FinalScore,Completed)

UserAchievements(AchievementID,UserID)

Supervisors(SupervisorID, UserID)

Homework(UserID,LessonID)

2.4 Tables – SQL Code (PHPMyAdmin)

CREATE TABLE Users (

UserID int NOT NULL AUTO_INCREMENT,

FirstName varchar(15),

Lastname varchar(15),

AccountName varchar(15),

BirthDate datetime,

Password varchar(15),

Grade varchar(15),

School varchar(15),

Role ENUM('Student', 'Supervisor')

)

CREATE TABLE Lessons (

LessonID int NOT NULL,

Name varchar(15),

Difficulty int,

Content text

)

CREATE TABLE UserLesson (

UserID int,

LessonID int,

KPM int,

WPM int,

KeysPressed int,

TimeSpent int,

Errors int,

FinalScore int,

Completed boolean

)

Create TABLE UserAchievements (

AchievementID int,

UserID int,

Name varchar(15),

PRIMARY KEY (AchievementID,UserID)

)

Create TABLE Homework (

UserID int,

StudentID int,

PRIMARY KEY (UserID)

)

Create TABLE Supervisors (

SupervisorD int,

UserID int,

PRIMARY KEY (SupervisorID)

)

2.5 Web Interface Implementation (interface screenshots)

index.php

login.phploginproc.phpuserindex.phpchooselesson.phplesson.phplessonresults.phplearn.phpresults.phpresults.phpsettings.phpachievements.phpcreatelesson.phphomework.phpsethomework.phpsethomeworkproc.phpregister.phpregisterproc.php

leaderboard.php

2.6 Populated Tables

Homework

Lessons

UserLesson

Users

2.7 SQL Queries

achievements.php

Query: "SELECT * From UserAchievements WHERE UserID = $UserID"

Explanation: This query selects the Achievements that have been ‘Achieved’ by the user from the UserAchievements table

chooselesson.php

Query: “SELECT * From Lessons”

Explanation: This query lists all the available lessons that users can complete

createlessonproc.php

Query: "INSERT INTO Lessons VALUES (0,'$Name',$Difficulty,'$Content')"

Explanation: This query adds a user’s custom lesson into the lessons table so that it can be completed by other users

homework.php

Query: "SELECT * FROM Lessons, Homework WHERE Homework.UserId = $UserId AND Lessons.LessonID = Homework.LessonID"

Explanation: This query shows the user the homework that they are required to complete, the table join is necessary because it displays additional information about the lesson that is not stored in the homework table because of unnecessary duplication

leaderboard.php

Query:"SELECT AccountName as Name, ROUND(AVG(KPM),0) AS KPM,ROUND(AVG(WPM),0) AS WPM,ROUND(SUM(KeysPressed),0) AS KeysPressed,ROUND(SUM(TimeSpent),0) AS TimeSpent,ROUND(SUM(Errors),0) AS Errors,ROUND(SUM(Score),0) Score From UserLesson, Users WHERE Users.UserID = UserLesson.UserID GROUP BY AccountName ORDER BY Score Desc"

Explanation: This query displays an ordered list of the users based on their respective scores in the database. It is calculated from the sum of all the user’s lesson scores, hence the necessity for the table join and the group by statement. It is ordered by Score ‘Desc’ because this allows for an effective ranking of users based on their ability and dedication to the application

Query:"SELECT Grade as Name, ROUND(AVG(KPM),0) AS KPM,ROUND(AVG(WPM),0) AS WPM,ROUND(SUM(KeysPressed),0) AS KeysPressed,ROUND(SUM(TimeSpent),0) AS TimeSpent,ROUND(SUM(Errors),0) AS Errors,ROUND(SUM(Score),0) AS Score From UserLesson, Users WHERE Users.UserID = UserLesson.UserID GROUP BY Grade ORDER BY Score Desc"

Explanation: This query displays an ordered list of the grades based on their respective scores in the database. It is calculated from the sum of all the grade’s lesson scores, hence the necessity for the table join and the group by statement. It is ordered by Score ‘Desc’ because this allows for an effective ranking of gradess based on their ability and dedication to the application

Query:"SELECT School as Name, ROUND(AVG(KPM),0) AS KPM,ROUND(AVG(WPM),0) AS WPM,ROUND(SUM(KeysPressed),0) AS KeysPressed,ROUND(SUM(TimeSpent),0) AS TimeSpent,ROUND(SUM(Errors),0) AS Errors,ROUND(SUM(Score),0) AS Score From UserLesson, Users WHERE Users.UserID = UserLesson.UserID GROUP BY School ORDER BY Score Desc"

Explanation: This query displays an ordered list of the schools based on their respective scores in the database. It is calculated from the sum of all the school’s lesson scores, hence the necessity for the table join and the group by statement. It is ordered by Score ‘Desc’ because this allows for an effective ranking of schools based on their ability and dedication to the application

lesson.php

Query: “SELECT Content FROM Lessons WHERE LessonID = $LessonID”

Explaination: This query selects the text that is required to be typed by the user as part of the lesson

lessonproc.php

Query: "SELECT * FROM UserLesson WHERE UserID='$UserID' AND LessonID='$LessonID'"

Explanation: This query checks to see whether a record pertaining to a specific lesson and user exists, thus determining whether to update a record, or add to a table

Query: "INSERT INTO UserLesson VALUES ($UserID,$LessonID,$KPM,$WPM,$KeysPressed,$TimeSpent,$Errors,$Score)"

Explanation: This query adds the data collected from a lesson into the table

Query: "UPDATE UserLesson SET KPM=$KPM,WPM=$WPM,KeysPressed=$KeysPressed,TimeSpent=$TimeSpent,Errors=$Errors,Score=$Score WHERE UserID=$UserID AND LessonID=$LessonID"

Explanation: This query updates the data collected from a lesson that has already been completed by a user

Query: "DELETE FROM Homework WHERE UserID = $UserID AND LessonID = $LessonID"

Explanation: This query clears the homework of a user that pertains to a lesson that they have just completed, and thus no-longer required to complete

lessonresults.php

Query: “SELECT KPM,WPM,KeysPressed,TimeSpent,Errors,Score From UserLesson WHERE LessonID = $LessonID AND UserID = $UserID"”

Explanation: This query displays the results of a lesson that the user has just completed

loginproc.php

Query: "SELECT UserID From Users WHERE AccountName = '$AccountName' AND Password = '$Password'"

Explanation: This query checks whether the user can login or not based on whether they exist in the database and have supplied correct credentials

registerproc.php

Query: "SELECT AccountName FROM Users WHERE AccountName = '$AccountName'"

Explanation: This query determines whether an account name already exists in the database

Query: "INSERT INTO Users VALUES ('0','$FirstName','$LastName','$AccountName','$BirthDate','$Password','$Grade','$School','$Role')"

Explanation: This query inserts the user’s information into the database if they select the student option

Query: "INSERT INTO Users (UserID,FirstName,LastName,AccountName,BirthDate,Password,Role) VALUES ('0','$FirstName','$LastName','$AccountName','$BirthDate','$Password','$Role')"

Explanation: This query inserts the user’s information into the database if they do not select the student option

results.php

Query: "SELECT KPM,WPM,KeysPressed,TimeSpent,Errors,Score From UserLesson WHERE LessonID IN (SELECT LessonID FROM Lessons WHERE Name = '$Name') AND UserID = $UserID"

Explanation: This query gets aggragated information from the UserLesson table (contains information about a lesson) and compiles it using the rounded values of aggregate functions

Query: "SELECT ROUND(AVG(KPM),0) AS KPM,ROUND(AVG(WPM),0) AS WPM,ROUND(SUM(KeysPressed),0) AS KeysPressed,ROUND(SUM(TimeSpent),0) AS TimeSpent,ROUND(SUM(Errors),0) AS Errors,ROUND(SUM(Score),0) Score From UserLesson WHERE UserID = $UserID"

Explanation: This query gets aggragated information from the UserLesson table (contains information about a lesson) and compiles it using the rounded values of aggregate functions. A subquery is necessary because the information regarding the context specific to this query is not avaliable in the UserLesson table

Query: "SELECT ROUND(AVG(KPM),0) AS KPM,ROUND(AVG(WPM),0) AS WPM,ROUND(SUM(KeysPressed),0) AS KeysPressed,ROUND(SUM(TimeSpent),0) AS TimeSpent,ROUND(SUM(Errors),0) AS Errors,ROUND(SUM(Score),0) AS Score From UserLesson WHERE UserID IN (SELECT UserID FROM Users WHERE Grade = '$Name')"

sethomeworkproc.php

Query: "SELECT UserID FROM Users WHERE UserID = $UserID AND Role = 'Supervisor'"

Explanation: This query checks that the user is a supervisor

Query: "SELECT School FROM Users WHERE AccountName = '$UserName'"

Explanation: This query determines which school the user belongs to

Query: "SELECT School FROM Users WHERE UserID = $UserID"

Explanation: This query determines which school the teacher belongs to

Query: "INSERT INTO Homework VALUES ((SELECT UserID FROM Users WHERE Accountname = $Accountname), $LessonID)"

Explanation: This query adds the user’s homework to the Homework table

userindex.php

Query: "SELECT UserID FROM Users WHERE UserID = $UserID AND Role = 'Supervisor'"

Explanation: This query determines if the user is a supervisor

2.8 Testing

Form Processing Page / Test Data/Parameters (in order of the fields of the form)
Correct Input / Incorrect Input
createlesson.php / Test lesson, 1, This is my lesson / Test lesson, 100, This is my lesson
login.php / adamsmith,password / adamsmith,incorrect
register.php / John,smith,jsmith01,2001-01-01,pass,11,hillcrest,user / John, ,jsmith01, ,pass,11, ,user
sethomework.php / adamsmith.3 / jsmith01,3

Snapshot of the Output Page

createlesson.php

Correct Input:

Incorrect Input:

These outputs were correct.

Snapshot of the Output Page

Correct Input:

Incorrect Input:

These outputs were correct.