I have a Windows program developed in Delphi that collects statistical information about the user's computer, such as idle time, the number of keys pressed, mouse movement, mouse clicks etc. At the moment the program saves this data to a text file, sample file with real data is attached.
I will need the program to be changed in a way that it sends the data over the Internet to a web server that saves the data to a DB.
In this project, I need you to design a MySQL database with one or more tables to store this kind of information. You can make slight changes to the data structure, e.g. add unique ID, user ID and something like that. For example, I recommend that we change the time format to something better and add a field for the date as well. We will also need to add an UserID field to support multiple users (collecting data from many users and storing them in same db) For any major changes, ask my permission first.
The data looks like this:
ProjectName=Business project one
Time is the time of the statistics event. Runtime means the time the program has been running (collecting data), in seconds. IdleTime means the time in seconds in which there has not been any computer usage (no keyboard inputs and no mouse movement or clicks). Keystrokes means the number of keyboard button presses. Backspaces means the number of times the backspace button of the keyboard has been pressed. MouseClicks means the number of times the mouse has been clicked and MouseMovement means the amount of length the mouse cursor has been moved in the screen, the unit is pixels. ProjectName means the name of the currently active project, i.e. what the user has been working on with the computer during this event.
The final delivery consists of the SQL files to create the database structure and SQL sample scripts that can be used to query the database the following information:
1) How long there was IdleTime in a given date?
2) How long there was IdleTime in a given date between 09:00 and 14:00?
3) What was the average keystrokes per minute rate in a given date?
4) What was the average keystrokes per minute rate in a given date between 09:00 and 14:00 to a project called "Business project one"?
5) How much work time (RunTime minus the IdleTime) was spent on each project on a given date?
6) How much work time (RunTime minus the IdleTime) was spent on a given project in the last 7 days?
7) How much work time (RunTime minus the IdleTime) was done in total in the last 30 days?
8) How many per cent of the RunTime was IdleTime on a given date between 10:00 and 16:00?
9) What project received the highest Keystrokes per minute average on a given date?