Transcript WHERE expression - Dixie-Net

MADBAGS
Microsoft Access DataBase
Automated Grading System
What does MADBAGS grade?
• Table Structure
– Field Names
– Field Types
– Field Sizes
• Table Data
• Field attributes
–
–
–
–
Default values
Required fields
Validation rules
Validation text
• Indexes
• Primary Keys
• Relations
What does MADBAGS grade?
(continued)
• Queries
– SELECT Type
• Fields
• Table names plus
Join clauses
• Where criteria
• Total queries
– DELETE Type
• Table Names
• Where criteria
– Update Type
• Table name
• Col=expr List
• Where critera
What does MADBAGS not
grade?
• Forms
• Macros
• Reports
• Modules
• Data Access Pages
How does MADBAGS work?
• a Microsoft Access application
• Attributes are extracted
– from student Access databases
– into Microsoft Access tables:
_Tables
_Fields
_Indexes
_Queries
_Relations
Example Table: Item
_Fields Table
(Item table structure lines 1-6)
Example Access Query
_Queries Table
How does MADBAGS work?
(continued)
• After attributes are extracted from both
– instructor’s correct database
– a student’s database
• and placed into tables
• The resulting tables are compared
– missing attributes are detected
– unequal attributes are found
Recording Errors
Errors placed in “E R R O R S” table in student database
Screenshots
Grade Report
Class Errors Report
Grading Challenges
Renaming Tables
(After table names are recorded as incorrect)
• Misnamed tables are matched to correct
– By comparing field counts
• Of non-matched tables in correct DB
• With extraneous tables in student DB
– Table names are corrected in extracted tables
•
•
•
•
Fields
Indexes
Relations
Queries
Renaming Fields
(After being recorded as incorrect)
• Misnamed fields are matched to correct
– By finding minimum Levenshtein distance
• Of non-matched fields in correct DB
• With extraneous fields in student DB
– Levenshtein distance is the number of changes required to
convert one string into another string
LD(“tim”,”tom”) = LD(“tim”,”time”) = LD(“tim”,”ti”) = 1
– Field names are corrected in extracted tables
• Indexes
• Relations
• Queries
Grading Queries
• MADBAGS grades three query types
– SELECT
– DELETE
– UPDATE
Query Syntax
• SELECT [ DISTINCT | ALL ] col_expr1, col_expr1,
...
–
–
–
–
–
[
[
[
[
[
FROM from_clause ]
WHERE where_expression ]
GROUP BY expression1, expression2, .... ]
HAVING having_expression ]
ORDER BY column1, column2, .... ]
• UPDATE table_name
– SET col_name1 = expr1, col_name2 = expr2, ....
– [ WHERE expression ]
• DELETE FROM table_name
– [ WHERE expression ]
Grading Queries
• Query clauses are separated into 3 categories
– Lists
•
•
•
•
•
•
SELECT col_expressions
GROUP BY expressions
ORDER BY columns
UPDATE table name
UPDATE SET assignments
DELETE table name
– Boolean expressions
• WHERE clauses
• HAVING clause
– Unique clauses
• FROM clause
Grading Queries: List Clauses
• Tokenize Correct/Student lists
• Place into 2 dictionaries (Red-Black Trees)
• For each list element in Correct Dictionary
– If Found in Student Dictionary
• Delete from Student Dictionary
– Else
• Generate “Missing Item” error message
• For each remaining list element in Student Dictionary
– Generate “Extraneous Item” error message
Grading Queries: Boolean Expressions
• Convert Correct&Student boolean expressions to RPN
• For each relational expression (< > =) in Correct RPN
– If Found in Student RPN expression
• Replace relational expressions in both RPNs with same bool var
(e1,e2, …)
– Else
• Generate “Missing” error message
• Replace relational expression in Correct RPN with unique bool var
• For any remaining relational expressions in Student Dictionary
• Generate “Extraneous” error message
• Replace relational expression in Student RPN with unique bool var
Grading Queries: Boolean Expressions
(continued)
• While boolean (AND/OR) expressions exist
in Correct RPN expression
– Find any boolean expression with two bool var operands (innermost)
– If bool expression is found in the Student RPN expression
• Replace boolean expressions in both RPNs with same bool var
– Else
• Generate “Missing” error message with boolean operator
• Replace boolean expression in Correct RPN with unique bool var
• For each boolean operator in Student Dictionary
• Generate “Extraneous” error message with operator
How to use MADBAGS
• 100 point grading scale
• Enter number of points deducted for
– Incorrect field names, field type, field size
– Mistyped data
– Missing queries
• The amount is deducted for the following errors:
•Missing query fields
•Missing/incorrect query criteria
•Missing/incorrect joins
• The same amount is deducted for the following errors:
– Missing primary keys
– Missing indices
– Missing relations
– Missing/incorrect validation rules and/or validation text
Options: Only Grade Tables
– Only grades table structure and table data
– Useful with first class assignments
– Avoids grading more advanced features
(for example, queries, indexing, relations)
before being covered in class
– Avoids “error overload”; hides details that
might be more easily handled manually
Options: Deduct Extraneous
• Extra tables, fields, records, queries,
etc. are counted against the student
Error Analysis
– Helps discover possible plagiarism
– Considers a pair of students at a time
– How many errors do they have in common?
• More errors, more evidence of plagiarism
• Input is minimum # errors to be included in report
– What percent of a student A’s errors were also
made by student B?
Student Pairs Common Errors