Assessment of Students in Databases by Audit Trails

Assessment of Students in Databases by Audit Trails

Aprajita Singh

TIMSCDR

Mumbai, India

Abstract Assessing students answers is a very time- consuming activity in an examination and that makes teachers reduce the time they can devote to other duties. Testing and grading database skills automatically can dramatically reduce teachers time taken in routine tasks. Data collected in computer logs can record every action that the student performs in a computer-based learning environment. These log actions can be utilized to provide insight into the students performance. Database management systems include an audit trail component which involves setting up auditing to record user activities. Audit trail records can be studied to find pattern and behaviors of students while they are appearing in a practical test for databases. This paper attempt was made to investigate whether the audit trail records generated by students in practical examination for databases could be related to the marks they secured in the exam. The collected data was statistically analyzed to find any correlation between the number of records in the audit trials generated per student and his grades secured. The grades secured by the student indicated significant dependence on the number of audit trails records.

Index Terms Students Assessment, Audit Trails, Databases, Correlation.

1. INTRODUCTION

   Assessment in educational institutions is part of a system of accreditation and it encourages student learning and is crucial to effective learning. Educational assessment refers to the whole process of learners testing their understandings and getting feedback or report card.

   Assessing students answers is a very time-consuming activity that makes teachers cut down the time they can devote to other duties. Sometimes they may even have to reduce the number of assignments given to their students due to lack of time. Assessment is very powerful in driving students' learning behaviors thus should be given ample importance.

   Computers can be used to help the teachers in their assessment task. By testing and grading database skills automatically can dramatically reduce teachers time taken in assessment tasks.

   Database management systems include an audit trail component; one of the most significant aspects of database security involves setting up auditing to record user activities. Auditing is the monitoring and recording of selected user database actions. Databases allow audit options to be precise or general. One can audit: Successful or unsuccessful statement executions, Statement executions once in each user session or once every time the statement is executed,

   Activities of all users or of a specified user .When auditing is enabled audit trail table records the audit output.

   Audit trails are meant both for maintaining security and for recovering lost transactions; but from audit trail records we can also study pattern and behaviors of students while they are appearing in a practical test for databases. More importantly, a system can be developed with the help of audit trails that will allow students to practice and get meaningful feedback at any time and pace they desire. Data collected in computer logs can record every action that the student performs in a computer-based learning environment.

   Testing and grading database skills automatically can dramatically reduce teachers time taken in routine tasks. Essentially it allows students to practice and get meaningful feedback instantly. Perhaps in terms of learning the automatic assessment systems are most powerful at formative assessment, i.e. assessments that supports learning while it happens.

   Increasingly, researchers have begun to utilize trace methodologies in order to examine the complex temporal patterns of self-regulated learning (SRL) [2]. The idea is that students' learning and problem-solving activities are evolving events and their relation can be better understood by observing grades and audit trails records. In fact s the most common type of data collected is computer log files, which can record every action that the student performs in a database environment.

   Educational assessment refers to the whole process of learners testing their understandings giving feedback or report to the learner. On the basis of the report, learners modify and develop those understandings. This report can be given in the form of responses to students' contributions in class, as well as written or oral statement on their work. Feedbacks obtained thus will also form the basis for a summative judgment and the generation of marks and grades.

   Assessment is very important in driving students' learning behaviors. Therefore assessment tasks must be designed in such a way that it encourages more demanding and challenging approach to learning, such as the capacity to analyze and synthesize.

   Attempt was made in the present study to investigate whether the audit trail records generated by students in practical examination for databases could be related to the marks they secured in the exam. The collected data was statistically analyzed by using correlation and result indicated significant dependence on the variables.

   The focus of this paper is on the analysis of students' grades and to investigate dependence of various parameters that are attributes in the audit trail records in the database, by a statistical analysis between students' activity traces. Our main focus here is how to provide practicing and assessing database competences in a way that is both pedagogically meaningful and scalable to large number of students.

2. TYPE PROBLEMS WITH TRADITIONAL ASSESSMENT

   Assessing student progress throughout the semester is a very time consuming activity that makes teachers cut down the time they can devote to other duties. In some cases, they may even have to reduce the number of assignments given to their students due to lack of time. Automated assessment systems for student database offer important benefits like instant feedback and consistency of the evaluation, and thus saving substantial time in the evaluation of the assignments. Some studies have also reported [3], [4] that immediate grades generate motivation and enthusiasm among students, although there are indications that this may differ as per various cultural [5].

   Thus we have done this experiment so that we can investigate whether the audit records can be used for assessing the students during their practical exam. So that we can later extend it to assessing the student throughout the semester as per the logs so that it may save time for the teacher. Traditional assessment methods have been an integral part of the lab, and many educators believe are necessary to determine if students comprehend basic content. Traditional assessment includes marking by the teacher by looking at the output the student gets and understanding the process through which he/she reached the output manually. Traditional assessment is an efficient and effective method of determining whether the students are retaining the content material but as the number of students appearing for the exam increase it becomes tedious for the examiner to do justice to all students and maintain the same mind set so as to grade the students as per their performance.

3. AUDIT TRAILS IN A DATABASE

   Database management systems includes an audit trail component which keeps a record showing who has accessed a computer system and what operations he or she has performed during a given period of time. Audit trails are used extensively for maitaining security and recovery in databases.

   To maintain security in database one may have to set up auditing to record user activities. Auditing is the monitoring and recording of selected user database actions. Auditing a database involves investigating suspicious activity, like if an unauthorized user is tampering with data from tables, the security administrator would have to decide to audit all connections to the database and all successful and unsuccessful updations of rows from all tables in the database. He may monitor and gather data about specific database activities. The database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect to the server.

   Databases allow audit options that may be focused or broad. One can audit: Successful or unsuccessful statement executions or both, statement executions once in each user session or once every time the statement is executed, activities of all users or of a specific user.

   When auditing is enabled, the audit output is recorded in an audit trail. This audit trial is usually stored in the database in a table called AUD$ under the SYS schema. It can also reside as XML files in the system. Finer grained Auditing feature provides granular control of what to audit based on a more detailed set of policies.

   Various types of Auditing can be set up [1]

   1. Statement auditing:

      Audit trails table would have information about several DDL statements on which table they are issued.

      This can be set for all or selected users. It may audit several types of user actions.

   2. Privilege auditing:

      Privilege auditing is more focused than statement auditing because it audits only the privilege one is tracking. This can be set for auditing a selected user or every user in the database.

   3. Schema objects auditing:

      The selective auditing of specific statements on a particular schema object can be set which is much focused which keep track of a specific statement on a specific schema object. This type of auditing always applies to all users of the database.

   4. Fine-grained auditing:

   Fine-grained auditing helps keep content based monitoring of data.

   Once auditing is enabled in the database, an audit record is generated when the statements executes .Each SQL statements inside PL/SQL program units are audited separately when program executes. Audit trail records are generated and inserted independent of what user transaction is. Therefore, even if a user's transaction is rolled back, the audit trail record for the same will exist. When the system starts an audit record is generated indicating the user name, terminal identifier, date and time of the event.

   Statement and privilege audit options in effect at the times a user connects to the database remains in effect during the session. A session does not get effected by statement or privilege audit options being set or changed. DDL statements auditing for a particular type of database structure or schema object, like AUDIT TABLE audits all CREATE and DROP TABLE statements but not a specific table. DML statements auditing for a particular type of database structure or schema object, but not a particularly named structure or schema object like AUDIT SELECT TABLE audits all SELECT … FROM TABLE/VIEW statements, regardless of the table or view.

   Statement auditing can be general or specific, auditing the activities of all database users or the activities of only a selected database user. Statements that refer to clusters, indexes, or synonyms are not audited directly. One can audit access to these schema objects indirectly by auditing the

   operations that affect the base table. Schema object audit options are always set for all users of the database.

   Fine-grained auditing helps content based monitoring of data access. Audit mechanism in the database will prevent users from by-passing the audit. You can monitor actions even if the audited statements do not complete successfully. You can audit an unsuccessful statement execution only if a valid SQL statement is issued but fails because of lack of proper authorization or because it references a nonexistent schema object.

   Most auditing options can be set to indicate how audit records should be generated if the audited statement is issued multiple times in a single user session. There is a distinction between the BY SESSION and BY ACCESS clauses of the AUDIT statement.

   In BY SESSION, for any type of audit that refers to schema object, statement, or privilege, inserts for each user and schema object only one audit record in the audit trail during the session. If user issues five SELECT statements against the table name and then disconnects from the database and another user connects to the database and issues three SELECT statements against a different table then disconnects from the database. The audit trail contains two audit records for the eight SELECT statements– one for each session that issued a SELECT statement. If the SELECT TABLE statement auditing option is set BY SESSION and a user connects to the database and issues five SELECT statements against a table and three SELECT statements against another table and then disconnects from the database.

   In this case, the audit trail contains two records–one for each schema object against which the user issued a SELECT statement in a session. If you use the BY SESSION clause when audit records are directed to the operating system audit trail, database generates and stores an audit record each time an access is made. Therefore in this case there is no difference between BY SESSION and BY ACCESS.

   But audit BY ACCESS inserts one audit record into the audit trail for each execution of an auditable operation within a cursor if the SELECT TABLE statement auditing option is set BY ACCESS.

   A user connects to the database and issues five SELECT statements against a table and then disconnects from the database. Another user connects to the database and issues three SELECT statements against another the table and then disconnects from the database. The single audit trail contains eight records for the eight SELECT statements.

   The AUDIT statement lets you specify either BY SESSION or BY ACCESS but several audit options can be set only BY ACCESS that may include all statement audit options that audit DDL statements and all privilege audit options that audit DDL statements. BY SESSION is used by default for rest of the audit options.

4. METHODOLOGY

   Experiment in this study has been carried out using the Oracle database. Auditing is a default feature of the Oracle server, it can be initialized and parameters that influence its behavior can be displayed using the SHOW PARAMETER audit command.

   Auditing is disabled by default, but it was enabled by setting the AUDIT_TRAIL static parameter values AUDIT_TRAIL = db or true. It enabled auditing with all audit records stored in the database audit trial table SYS.AUD$. [6] The system is directed to tab on the changes by alter system command as follows: ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE. System is restarted to make the effect of alteration. Students were given different logins each and were strictly asked to appear in the practical exam by login in by the given login and password. This would help us to keep track of audit trails generated by specific student. All commands run by the students are then captured by directing the system by Audit all by access command.

   The question set in this exam given to students are almost similar in the sense that they need to almost run similar DDL and DML commands though each student gets a different question set. The test tasks were organized into three blocks which have question set to execute create statement to create tables, insert five records to the tables, run DML statement to query the created tables covering different concepts . Marks were allocated to each of te three blocks which carries twenty five marks in total. Students happen to appear for the exam by logging in to the database server and latter showing the output to the examiner. The duration for the exam is one and half hour. Examiner then evaluates them and scores the students accordingly.

   A total of 70 students appeared in the exam which in all generated. 1691 records in the audit trail table SYS.AUD$ by students giving the exam. Only records for the two days were needed hence they were extracted by using the TIMESTAMP# and USERID attribute of the SYS.AUD$ table. The output was exported to excel and latter some records were deleted as were not having data for other variables to be considered. Seven records were deleted, which were considered as outliers as they had very extreme values. Data was cleaned and placed in excel for analysis. Students marks for the same subject taken twice a semester were also placed and viva marks were also studied to find if any correlation occurred between them and the number of audit trail records generated by each student.

   We had considered only one aspect or say parameter from the AUD$ table that is the total number of records generated by each student during the practical exam only in this study. Finally, the relationship between number of audit trail records per student and the final practical exam scores of the experimental group students was investigated using Pearsons correlation coefficient. A correlation is a relationship between two variables x and y. The data is represented by the ordered pairs (x,y) where x is the independent, or explanatory variable and y is the dependent, or response variable. A positive correlation coefficient indicates a positive relationship; a negative coefficient indicates an inverse relationship between variables. A correlation coefficient indicates the extent to which two variables are related.

   Karl Pearsons Coefficient of Correlation

   rxy= cov(x, y)/x y (1)

   The explanatory variable in this study is the (x) number of records generated by the student in the audit trail. This variable has a standard deviation, x of 14.94.

   Fig 1. No. of records versus written exam score

   The correlation between x and first written exam score was obtained. The standard deviation y for the first written exam score obtained was 3.48. Covariance was recorded as -30.06. The correlation coefficient considering the two variables was –

   0.570 which was moderately high and negative.

   Fig 2. No. of records versus viva score

   The correlation between x and viva marks was obtained. The standard deviation for the viva score y obtained was

   1.79. Covariance was recorded as -3.99 and correlation coefficient was -0.15 which was very low.

   Fig 3. No. of records versus practical exam score

   The explanatory variable in the third figure is the (x) number of records generated by the student and (y) response variable is the score he/she gets from the examiner in the practical exam.

   Standard deviation of x, x was 14.9 and y for y was 4.16 with covariance of -29.463. A negative correlation of -0.5 between the number of records generated by the student and the score he/she gets from the examiner was observed. In this experiment we observed a negative correlation. If increase of one results in a corresponding decrease of the other, then the correlation is said to be diverse or negative.

   Other variables like attendance records for each student and second written exam was also studied but no significant correlation was obtained from there.

5. RESULT

   Explanatory variable in this study is the (x) number of records generated by the student and (y) response variable is the score he/she gets from the examiner. If increase of one results in a corresponding decrease of the other, then the correlation is said to be diverse or negative. In this experiment we observed a negative correlation.

   A negative correlation of -0.5 between the number of records generated by the student and the score he/she gets from the examiner was observed. Standard deviation of x was 14.9 and for y was 4.16 with covariance of -29.463. The study showed moderated correlation between the score received thus we can conclude that these audit records can be a significant indicator of the students understanding of the concept and the score the student gets from the examiner by manual inspection. Students getting marks between 18 and 25 are the once who have generated less then 35 records. Students who have generated more than 40 records have on an average got fewer marks. The data is showing negative correlation of -0.5 between the number of records generated and the grades students have acquired in the practical examination. Thus we can device a system through which students can be scored based on the audit trail record in this kind of a setup.

   The audit trails were studied for only the no. of records generated per student but various other attributes like action# and statement could have been studied to give more precise information about the performance of the student.

   The study cannot be generalized but can be refined for this particular setup for now. Although one may argue that a sample size of 60 students is insufficient to be confident of this conclusion. In general, the study may be repeated many times which is as necessary to get a perfect score.

   We had considered total number of records generated by each student only during the practical exam in this study. More detailed study is possible by considering the action#, statement and returncode attribute in the audit trail.

6. CONCLUSION

   The study showed moderately high correlation between the score received thus we can conclude that these audit records can be studied as an indicator of the students understanding of the concept and the score the student gets from the examiner by manual inspection. Another limitation of the experiment is the lack of a formal sample size calculation, which may have influenced the findings. Therefore, findings from this study must be interpreted with caution, and future research is recommended with a larger sample to investigate the effect of automated assessment system .This evidence as well as the more objective evidence indicates that records in the audit trials generated by the students during the practical examination have dependence on the marks they score in written as well as practical examination.

7. FUTURE WORK

Auditing is the monitoring and recording of selected user database actions. Audit trails are useful both for maintaining security and for recovering lost transactions, from audit trail records we can study pattern and behaviors of students corresponding to student learning outcomes and can help students choices when they area learning a specific topic or trying out ways to learn a specific topic.

REFERENCES

1. docs.oracle.com/html/A96524_01/c25audit.htm

2. Aleven et al. 2006;Azevedo and Witherspoon 2009; 2009; Biswas et al. 2010; Hadwin et al. 2007; Jeong and Biswas 2008; Zimmerman 2008.

3. G. García-Mateos and J. L. Fernández-Alemán, A course on algorithms and data structures using on-line judging, in Proc. 14th Innov.Technol. Comput. Sci. Educ., 2009, pp. 4549.

4. F. J. Montoya-Dato, J. L. Fernández-Alemán, and G. García- Mateos, An experience on ada programming using on-line judging, in Proc. 14th Int. Conf. Rel. Softw. Tech., Ada Europe, 2009, pp. 7589.

5. P. Brown, Setting the stage for the culturally adaptive agent, in Proc. AAAI Fall Symp. Socially Intell. Agents, 1997, pp. 93 97.

6. http://oracle-base.com/articles/10g/auditing-10gr2.php