• View
• Edit
• History
• Print

DatabaseMoodleEtc

Database Structure

We broke the database into four different tables to minimize the space that would be used while still allowing us to store all the information that we needed. The first table was the the Experiments table. This consisted of a an INT for the primary key for the table, a VARCHAR 128 for the title of the experiment and a VARCHAR 32 for the name of the person who owns the experiment. The second table, Sessions, contained an INT for the primary key, and INT for a foreign key linking it back to the Experiments table, a VARCHAR 32 for the name of the person running the session, a VARCHAR 128 to hold the plain text address of where the session was run and two FLOATs for the latitude and longitude. The third table, Fields, is made up of an INT for the primary key, and INT as a foreign key linking it back to the Experiments table, a VARCHAR 128 for the name of the field and an INT which corresponds to a list of field types that we maintain in the server. The last table, Data, consists of a BIGINT for a primary key since there will be significantly more entries in this table than any other, tow INTs used as foreign keys linking it to the Field and Sessions tables, a DOUBLE for the value and a LONG for the time the value was recorded at.

Experiments

Experiments are the top-level container in the iSENSE system. They are essentially templates, containing a title, owner, and a unique ID number. The title is how the users will identify different experiments, and could be something like "The effect of temperature on pressure". The owner of the experiment is the creator. Eventually, the owner will be able to modify and/or delete their experiments. Any user may create an experiment. Currently, there is no way for an owner to modify or delete an existing experiment.

Fields

Fields can be thought of as columns into which data are stored. Fields have a title, a numerical type identifier, an experiment ID number, and a unique ID number. The title is a description of what data the field contains, such as "Temperature (C)". The numerical type identifier defines what type of sensor is required for the field. The type can be 0 (temperature), 1 (humidity), or 2 (voltage). A more rebust system for type definition is needed, but for now, only three types are defined. The experiment ID number identifies which experiment the data field is in (a field may only belong to one experiment).

Sessions

A session is an instance of an experiment tied to a specific user. Each session has an experiment ID number, owner, title, location, and latitude/longitude. The experiment ID number specifies which experiment a session is an instance of. The owner of the session is the creator. Currently, there is no way for an owner to modify or delete his/her sessions. The title of the session gives the user a more specific explanation of what the session is measuring. For example, the session title for the example experiment above might be "The effect of temperature on pressure in Lowell, MA". The location is the street address at which the session was conducted. For example, the location might be "1 University Ave, Lowell, MA". The latitude and longitude are the GPS coordinates of the location string, geocoded using Google Maps. Currently, the latitude and longitude are not used, pending the implementation of a web manager which can geocode location strings.

Data

A datum is a single timestamped value, within a session, within a field, within an experiment. Each datum has a session ID number, field ID number, datum value, timestamp, and a unique ID number. The session ID number specifies which session each datum belongs to. The field ID number specifies which field inside that session the datum belongs in. The datum value is the value of the data point. The timestamp is the time (with millisecond precision) that data point was recorded, relative to the start of the UNIX epoch (January 1, 1970).

MySQL Accounts

The iSENSE database uses different MySQL user accounts, each with different privelages and responsibilities, to query and update the database. This is done primarily for security purposes. In other words, a database user that is only performing search operations should not have the ability to insert or delete database records.

Server Account

The server user, named isense-server, performs all major taks within the iSENSE system. These tasks include the creation of experiment, sessions, fields, and data points, along with other associated tasks. This user is trusted, meaning any input to queries must be cleansed by the iSENSE server prior to execution. Access is limited to the iSENSE database tables (i.e., the isense-server user may not access the Moodle database tables).

Search Account

The search user, named isense-search, performs searches of the database. This user only has SELECT access, and only to iSENSE database tables. This user is not trusted, meaning any input may be passed to it without damaging the system.

Moodle Account

The Moodle user, named isense-moodle, perform queries of the Moodle databse. The Moodle database is accessed only to authenticate users during the creation of a token. This user is trusted, but it is still only granted SELECT access, and only to the moodle.mdl_users table within the Moodle database.

DBMS

We use phpMyAdmin as out database management system (DBMS). It is a web-based MySQL administration tool written using PHP. It can be accessed at the following URL:

    http://isense.cs.uml.edu/phpmyadmin/

Attach: