Online Scoring System

1
Online Scoring System Reference Table Updating
System for PE of NCU

• Presented by Chung-lien Tseng
• Date 10/28, 2004

2
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

3
Introduction

• It was a one-year project for MIS students of NCU
• Objectives
• Provide an online system (for equipment place
  management) for PE office
• Facilitate the process of scoring
• Target users
• Teachers of PE
• Students
• Administer of the system
• My job-Online Scoring System

4
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

5
Requirement Discovery

• Teachers have to calculate the average score and
  then fill in the official form
• Past grades cant be saved and checked
• Some test items of PE dont have real scores,
  such as triple jump, running
• The reference table of converting test grades
  into scores of percentage is difficult to be
  updated, and the data is old

6
Requirement Analysis

• To maintain the reference table and take the
  ability of current students into account
• Reference Table Updating System
• To facilitate the process of scoring and keep the
  past student grades on file
• Online Scoring System

7
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

8
Evaluation of Alternatives

• Use Internet Programming Language
• For Reference Updating System
• Internet Programming Language
• ASP
• ASP.NET
• For Online Scoring System
• Excel with VBA
• Internet Programming Language (ex ASP, ASP.NET,
  script languages)

9
Why Did I Choose Excel

• Most of PE teachers are normal users, so the
  system must be easy to use
• The scoring system needs lots of fields to be
  filled in grades, and some fields need to be
  frozen
• The scoring system needs a mechanism to transform
  grades of test into percentage scores
  automatically

10
Why Did I Choose Excel (Contd))
Excel with VBA ASP, Script languages
Easy to learn and use Almost all PE teachers can use Excel With good interface, itll be easy to use
Need many writable fields Excel interface is composed of table Insufficient knowledge to do that
Need to transform score automatically Can use Excels formula function to achieve Large volume in-time transformation takes lots of system resource
11
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

12
VBA Introduction

• VBA stands for Visual Basic Application
• Similar to VB
• Built in every AP of Microsoft Office (Word,
  Excel)
• Whats different with macro?
• A VBA project may contains several macros
• Macro a public sub-process with no parameter
• Macro can be created by either macro recorder
  or Visual Basic Editor

13
Pros and Cons of VBA

• Pros
• Facilitate the use of Microsoft Office
• Record (compile) a repetitive job as macro, so
  that we only have to load the macro every time we
  need to do that job
• Cons
• Macro may be a malicious program
• To avoid being attacked, we can set the security
  level to High or Medium
• Digital Signature

14
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

15
ER Model
teachclass
course
teacher (account)
teach
include
score
enroll
reftable
testitem
link
student
generate
contain
take
refmeta
test
16
Database Design

• Online Score System
• account
• teacherName, id, password
• teachclass
• teacherName, courseName
• course
• courseName, courseId, class
• student
• studentNo, studentName, studentGender,
  studentDept, studentGrade
• score
• studentNo, courseName, score
• testitem
• courseName, itemNo, item, itemPercent
• test
• courseName, studentNo, itemNo, testScore

17
Database Design (Contd)

• Reference Table Updating System
• testitem
• courseName, itemNo, item, itemPercent
• reftable
• item, result, gender, refScore
• refmeta
• item, gender, measure, refSize, refMean, refStd,
  refSizeStd2, refSizeMean2, refSizeMean

18
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

19
Reference Table Algorithm

• Based on past data
• Average grades of current student have most of
  percentage of new reference table
• Use the original algorithm of old system to
  generate new reference table

20
Reference Table Algorithm (Contd)

• Algorithm
• Input of algorithm mean( ), std (S) of all
  grades
• To calculate joint and S of old and new data,
  we need , , and
  for
• iI means the old reference table data

21
Reference Table Algorithm (Contd)

• Range of real score 40100 (40 is the lowest
  score of PE in NCU)
• Slope relation between test grades and scores
• gt0?triple jump lt0?running
• Set Upper Bound (UB) a range be deducting later
• UB 4 S (UB if UBgt )
• st is a decided parameter for every score range
• If the slopegt0 between test grades and scores,
  i.e. direction ratio, st decreases when score
  increases otherwise, st increases when score
  increases

22
Reference Table Algorithm (Contd)

• elseif igt85 and ilt89 then
• st0.1 std
• elseif igt88 and ilt92 then
• st0.095 std
• lseif igt91 and ilt95 then
• st0.09 std
• elseif igt94 and ilt98 then
• st0.085 std
• elseif igt97 and ilt101 then
• st0.08 std
• end if
• ubub-st
• score(i)mean-ub
• next

• Example
• slopegt0, iscore
• for i41 to 100
• if igt40 and ilt51 then
• st0.135 std
• elseif igt50 and ilt61 then
• st0.13 std
• elseif igt60 and ilt66 then
• st0.125 std
• elseif igt65 and ilt71 then
• st0.12 std
• elseif igt70 and ilt76 then
• st0.115 std
• elseif igt75 and ilt81then
• st0.11 std
• elseif igt80 and ilt86then
• st0.105 std

23
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

24
System Flowchart

• Online Scoring System

25
Problems

1. How to transform test grade into score
   immediately?
2. How to tell whether a test item needs the
   reference table?
3. How to renew the scores if relative reference
   table has been updates?
4. How to control or jump to a given field of
   column?
5. How to transmit data (ex teachers name, course
   name, etc.) from webpage to Excel?
6. Where to put the Excel program (at server or
   client)?

26
Solution 1

• How to transform test grade into score
  immediately?
• Save reference tables in database
• Load required reference tables at the beginning
  and when needed
• Use Excels built-in VLOOKUP() formula to
  convert test grade into score

27
Solution 2

• How to tell whether a test item needs the
  reference table?
• Use Combo Box to list all referenced test items
  when choosing test items
• If the listed item is selected
  ?link to relative reference table
• If the teacher types test item himself ? a
  normal test item

28
Solution 3

• How to renew the scores if relative reference
  table has been updates?
• Only record the original test grades when saving
  file
• Auto-transform into percentage scores on scoring
  (i.e. open Excel)
• Scores cant be renew immediately if administer
  updates the reference while someone is scoring

29
Solution 4

• How to control or jump to a given field of
  column?
• Naming some special fields and every column
• A column is named after item number or data type
  (stdno, gender) it is filled
• Use Excels Range(name) function to control
  whole column

30
Solution 5

• How to transmit data (ex teachers name, course
  name, etc.) from webpage to Excel?
• Record information needed into database while
  interface is changing from webpage to Excel
• Add a new table scoreLog with fields
  ipAddress, teacherName, courseName,
  status
• After opening Excel, running ipconfig command
  by Excel function to get IP address
• According the IP of the teacher, retrieve
  required information from database

31
Solution 6

• Where to put the Excel program (at server or
  client)?
• The Excel program is saved at server
• Easy to maintain
• Prevent Excel program from being destroyed
• More mobility
• Side effect
• Read only dialog shows when 2 teachers score
  at the same time

32
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

33
Reference Table Updating System

• Note This system is available only with 2 weeks
  after school opens.

34
Reference Table Updating System (Contd)
35
Reference Table Updating System (Contd)
36
Reference Table Updating System (Contd)
37
Online Scoring System
38
Online Scoring System (Contd)
39
Online Scoring System (Contd)
40
Online Scoring System (Contd)
41
Online Scoring System (Contd)
42
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

43
Limitation and Constraints

• System
• Online Scoring System can only be run in Excel
  2002 and above
• Insufficient security for student data and scores
• The VBA wont work if user doesnt agree it to
  run need trusted digital signature
• The transfer process between webpage and Excel is
  rough
• Data of reference table are not abundant enough

44
Limitation and Constraints (Contd)

• External Environment
• Cant integrate with system of Office of Academic
  Affairs
• Communication with end user is not enough
• Immature programming technique

45
Outline

• Introduction
• Requirement Analysis
• Evaluation of Alternatives
• VBA Introduction
• Database Design
• Algorithm of Reference Table
• Problems and Solutions
• System Structure and System Display
• Limitations and Constraints
• Conclusion

46
Conclusion

• It an unfinished project
• In the future
• Move all scoring system on the Internet
• Emphasize on the security of system and database
• Enhance system performance

47
Wait a minute

• its present time!!