Online-Student-Grading-System

It is a project of MUST course CS108-Advanced Database System, which is a simple database for grading students.

Development Environment


System	Windows 10 x64
Front-end Framework	ASP.NET 4.7.2
Back-end Language	C#
IDE	Visual Studio Community 2019 v16.7.5
Database Tool	SQL Server Management Studio (SSMS) v18.7.1

Install

Import the project
1. Create an empty ASP.NET web application (.NET framework).
1. Copy all files in OSGS_webpages into the root directory of the created project.
1. Include them in the project.
1. Click IIS Express to open the website.
Migrate the database
1. Add a connection string in Web.config.
```
<connectionStrings>
    <add name="OSGSConnectionString" connectionString="Data Source=DESKTOP-KV5M48K;Initial Catalog=OSGS;Integrated Security=True"
      providerName="System.Data.SqlClient" />
</connectionStrings>
```
1. Replace the Data Source value in each .cs file and Web.config file. Use Ctrl+H to replace all. The Data Source value should be replaced with your SSMS server name, and mine is DESKTOP-KV5M48K.
1. Open SQL Server Configuration Manager, and change log on as to Local System. After that, restart the MSSQLSERVER.
1. Open SQL Server Management Studio (SSMS) and connect.
2. Open a New Query and execute the following SQL.
```
EXEC sp_attach_db @dbname = 'OSGS',
@filename1 = 'yourDir\Online-Student-Grading-System\OSGS.mdf',    
@filename2 = 'yourDir\Online-Student-Grading-System\OSGSLog.ldf'
```
1. Test the database.
```
USE OSGS
SELECT * FROM Teacher
```
Log in OSGS
1. Click IIS Express to run, and enter the Teacher ID/Password: sllo_stu/123456.

Usage

Login page (Login.aspx)

The login page must be the initial page which has two different login types, which will handle the login event of both teachers and students. Users can log in with the correct ID and Password. The teachers' information is assigned by SSMS and students' information is assigned by the teacher.

Student account management page (StuOverview.aspx, this page is for the teacher)

As we all know, a teacher probably has more than one course. For example, Professor Lo has two courses CS101 and CS108. On this page, he can see all the students who take CS108 by choosing the DropDownList whose value is 'CS108'. By the way, the teacher can also Edit the information of students (including name, password, and email address) or Delete it if the student cancels the course.

Assignment management page (AssignmentManagement.aspx, this page is for the teacher)

Switching the RadioButtonList can enter this page. We can see all the assignments the teacher gives to each course, and also about assignment info (name, weight, total question number, deadline, submit state), student info, and grade info (grade and comment). Clicking the Select button to get more information.

Assignment marking page (AssignmentMarking.aspx, this page is for the teacher)

It is a page to mark students' assignments, and all the questions and answers will be displayed. The teacher can give comments and grades, the data will be updated on the previous page.

Assignment creating page (AddNewAssignment.aspx, this page is for the teacher)

To create a new assignment, the teacher can enter information about this assignment, and also choose to add or delete a question, which implements the variability of the question number.

Register page (RegisterNew.aspx, this page is for the teacher)

This page is used for course registration or new-student registration, the teacher can select the register type (Student Register for a new student, Course Register for a student who has not taken the course) and add information to the database.

Student assignment page (AssignmentOverview.aspx, this page is for students)

After logging in as a student, you will enter into this page. For each course you have taken, you can see each of the assignment info (weight, total question number, deadline, grade, submission state [Y for yes/N for no], teacher's comment) and the GPA will be computed and displayed automatically.

Student answer page (StudentAnswer.aspx, this page is for students)

On this page, students can see the details about their assignment answers or submit their answers. But if students have submitted the answer before, they will not be allowed to update their answers again. The teacher's comments can also be seen.

Highlights

To be more realistic, I design a dual-login system for both teacher and student, the user can switch the login type to enter the system. After entering the system, the user can click the logout button to back to the home page.
Allow the user to take more than one course and the system will display the information corresponding to the selected course.
The question number of each assignment is not fixed, teachers can add or delete questions if they want.
On StuOverview.aspx page, the teacher can reset students' login passwords directly by clicking the Edit button and then the data will be updated.
The bootstrap framework provides a user-friendly GUI which is supported on most devices.

Design

Class Diagram

It is drawn by NClass 2.04, we have 8 C# classes, corresponding to each front page shown before. For each class, I implement some event functions which can handle all the events (such as Clicking, DropdownList change, Text change, RadioButton changed etc.) taking place on the current page.

ER Diagram
- In the design phase, first I find the Student Entity and the Teacher Entity have the same attributes like name, password and email address, which reminds me to use the ISA structure to represent.
- And then, Course Entity records the course ID and the course name. It has both relationships with Student and Teacher, that's why stuid and tid are foreign keys of Course.
- For Assignment Entity, we set aid* as the primary key. This entity has lots of attributes but it still has a constraint with cid of Course. By the way, Assignment Entity will store some important messages like the assignment name, which course, how many questions does have, weight, and also the deadline.
- In Question Entity, it records question content and the index of questions, aid is a foreign key because I consider that many questions might be related to one assignment so that we can find the exact question if we know about the aid and the q_index.
- The Answer Entity has two foreign keys, one is qid and another is stuid because for the same question, we need to know which student has answered it. It is the reason why both foreign keys are necessarily required. Additionally, the content of the student's answers and the teacher's comments will be recorded, too.
- The last entity is Submit Entity, this entity stores the grade information, submission statement and teacher's comment about the whole assignment. Submit Entity has a relationship with Student and Assignment because it records all assignment information of each student.

Schema

SQL Code

Database Creation

 /* create database */
 CREATE DATABASE OSGS
 ON(
 	NAME='OSGS',
 	FILENAME='C:\OSGS.mdf',
 	SIZE=17MB,
 	FILEGROWTH=5MB
 )
 LOG ON
 (	
 	NAME='OSGSLog',
 	FILENAME='C:\OSGSLog.ldf',
 	SIZE=5MB,
 	FILEGROWTH=5MB
 )
 GO

Table Creation

 /* create table */
 CREATE TABLE Teacher(
 	tid varchar(25) NOT NULL PRIMARY KEY,
 	tName varchar(15) NOT NULL,
 	psw varchar(15) NOT NULL,
 	emailAddress varchar(30) NOT NULL
 )
 
 CREATE TABLE Student(
 	stuid varchar(25) NOT NULL PRIMARY KEY,
 	sName varchar(15) NOT NULL,
 	psw varchar(15) NOT NULL,
 	emailAddress varchar(30) NOT NULL
 )
 
 CREATE TABLE Course(
     indexNumber INT NOT NULL PRIMARY KEY,
 	cid varchar(10) NOT NULL,
 	cname varchar(20) NOT NULL,
 	stuid varchar(25) NOT NULL FOREIGN KEY REFERENCES Student(stuid),	
 	tid varchar(25) NOT NULL FOREIGN KEY REFERENCES Teacher(tid)
 )
 
 CREATE TABLE Assignment(
     aid INT NOT NULL PRIMARY KEY,
 	aname varchar(15) NOT NULL,
 	cid varchar(10) NOT NULL,
 	q_number INT NOT NULL DEFAULT 0,
 	[weight] INT NOT NULL,	
 	tid varchar(25) NOT NULL FOREIGN KEY REFERENCES Teacher(tid),
 	deadline varchar(20) NOT NULL,	
 )
 
 CREATE TABLE Question(
     qid INT NOT NULL PRIMARY KEY,
 	q_index INT NOT NULL,
 	aid INT NOT NULL FOREIGN KEY REFERENCES Assignment(aid),
 	content varchar(100) DEFAULT NULL
 )
 
 CREATE TABLE Answer(
     ansid INT NOT NULL PRIMARY KEY,
 	qid INT NOT NULL FOREIGN KEY REFERENCES Question(qid),
 	stuid varchar(25) NOT NULL FOREIGN KEY REFERENCES Student(stuid),
 	content varchar(100) DEFAULT NULL,
 	tcomment varchar(100) DEFAULT NULL
 )
 
 CREATE TABLE Submit(
     subid INT NOT NULL PRIMARY KEY,
 	aid INT NOT NULL FOREIGN KEY REFERENCES Assignment(aid),
 	stuid varchar(25) NOT NULL FOREIGN KEY REFERENCES Student(stuid),
 	grade real DEFAULT 0.0,
 	comment varchar(100) DEFAULT NULL,
 	stat varchar(1) NOT NULL DEFAULT 'N'
 )
 GO

Data Update

On StuOverview.aspx, the teacher can edit students' information:

 /* update table */
 UPDATE Student
 SET sName = @sName, psw=@psw, emailAddress=@emailAddress
 WHERE stuid = @stuid
 GO

Data Query

I use the query of Assignment Management.aspx as an example:
```
 SELECT A.aid, A.aname, S.stuid, S.sName, A.weight AS [weight(%)], 
 A.q_number, A.deadline, SU.stat, SU.grade, SU.comment
 FROM Assignment AS A
 JOIN Submit AS SU 
 ON SU.aid = A.aid 
 JOIN Course AS C
 ON C.cid=A.cid AND C.tid = A.tid AND C.cid = @cid AND 
 C.tid = @tid AND SU.stuid = C.stuid
 JOIN Student AS S
 ON S.stuid = C.stuid
```
We can see that all the columns totally involve 3 tables (Assignment, Submit and Course), so the first operation is definitely joining all three tables. And then, we need to check:
1. The current teacher exactly teaches this course (probably there are 2 teachers teaching CS108 together).
2. The students exactly take the course of this teacher.
3. The result belongs to currently selected course (if selected CS101, some of the students will miss it).
4. The submission is corresponding to those students.

Data Insertion

SQL Connecting

SqlConnection con = new SqlConnection("Data Source=DESKTOP
-KV5M48K;Initial Catalog=OSGS;Integrated Security=True");

con.Open();

Insertion Method

// insert, update and delete
public void sql_insert(string sql, SqlConnection con)
{
    SqlCommand cmd = new SqlCommand(sql, con);
    try
    {
        cmd.ExecuteNonQuery();               
    }
    catch
    {
        halt = true;
        Response.Write("<script type='text/javascript'>
        alert('Insert Fail!');</script>");
    }
        
}

Insert Question Table

sql_que = "INSERT INTO Question(qid, q_index, aid, content)
VALUES (" + qid + ", 1, " + aid + ", '" + Q_one.Text + "')";

Insert Assignment Table

sql_assign = "INSERT INTO Assignment(aid, aname, cid, q_number, 
[weight], tid, deadline) 
VALUES(" + aid + ", '" + aname.Text + "', '" + cid.Text + 
"', " + num.ToString() +", " + weight.Text +", '" + tid + 
"', '" + ddl.Text +"')";

Insert Answer Table

sql = "INSERT INTO Answer(ansid, qid, stuid, content, tcomment) 
VALUES(" + ansid.ToString() + ", " + qid_1 + ", '" + stuid 
+"', '" + ans.Text + "', '')";

Insert Submit Table

sql1 = "INSERT INTO Submit(subid, aid, stuid, grade, comment, 
stat) 
VALUES(" + subid.ToString() + ", " + aid + ", '" +
tmp[0].ToString() + "', 0.0, ' ', 'N')";

How to assign a new ID as the primary key (like new aid, qid, ansid, subid)

// get the largest ansid
sql = "SELECT TOP 1 ansid FROM Answer ORDER BY ansid DESC";
ansid = int.Parse(sql_command(sql, con));

ansid++; // a new ansid

In conclusion, all the parameters of previous insertion methods can be divided into 3 types: new ids (aid, qid, ansid, subid), passed by Textbox (has .Text), passed by URL (stuid or tid). They all can be connected as String Type into SQL.

Name		Name	Last commit message	Last commit date
Latest commit History 21 Commits
OSGS_webpages		OSGS_webpages
LICENSE		LICENSE
OSGS.mdf		OSGS.mdf
OSGSLog.ldf		OSGSLog.ldf
README.md		README.md
osgs.sql		osgs.sql

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

Online-Student-Grading-System

Table of Contents

Development Environment

Install

Usage

Highlights

Design

SQL Code

Maintainers

Contributing

License

About

Languages

License

MUST-SCSE-SE-2018/Online-Student-Grading-System

Folders and files

Latest commit

History

Repository files navigation

Online-Student-Grading-System

Table of Contents

Development Environment

Install

Usage

Highlights

Design

SQL Code

Maintainers

Contributing

License

About

Topics

Resources

License

Stars

Watchers

Forks

Languages