Asiri's Blog: December 2014

The main purpose of this Environmental Monitoring System is to collect and store various environmental data gathered from sensors located around the city. Apart from that, database should assist in providing information for reporting and analysis of the environmental condition in the city.

The data entry will be done by an automated system. Database will be updated automatically with data sent wirelessly from the sensors. Sensors will send data with in10 minute time intervals to the database. Sensors will also store the readings for a day’s period to avoid the loss of data in case wireless transmission fails. The System is able to monitor the following environmental conditions atmospheric pressure, SO2, CO2 level in the atmosphere, rain fall, wind speed, wind direction, temperature and humidity. Front end of the database is a web interface which is used to monitor, maintain and analyse the database. To access the database users should sign in using the web interface. Access and privileges to the database differs from the each type of user. There are four user levels.

Administrative
Data Entry
Researcher
Guest

Each user levels access privileges are as follows.

AdministrativeLevel

Administrative Level includes full access and authority to the web interface and the database. They will be also in charge of maintaining the database. Administrative level users will be given the ability of viewing, present and the past data stored in the database. Administrators can create new users with specific privileges. Also they have the ability to add a new sensor to the system. Most importantly they have the authority to back up the database as a safety measure.

DataEntryLevel

This level provides authority to view the previously entered data within a week . Data Entry Level users have the ability to edit data entries(make a value null) in case if there is a malfunction in the sensor system it transmits an obliviously wrong set of values. Otherwise these wrong values will affect analysis done by the researches. Apart from that they can enter completely new data entries if the wireless transmission fails.

ResearchLevel

Research level is for the researchers who analyse the data. They can view all past and present data with in a customisable range of time. They are allowed to download data from the main server.

GuestLevel

Guest Level includes the general public. They have very limited access to the database. They are only allowed to view the data of present weather condition from the web interface. For an example they can view the present temperature, rainfall etc of certain area. But they will be entitled to view history of the environmental data within a week.

Database Design

Functional and nonfunctional requirements relating to the system are as follows.

Functional Requirements

The database should

Allow the automated system to enter the statistical data from sensors periodically.
Allow the system to update existing data.
Allow data entry operator to enter new data entries.
Allow authenticated users to access and view environmental data records according to
their privileges.

Non-functional Requirements

There is a wireless transmission system to get data from sensors. (WAN)
There is a single database (Mysql).
There is a well implemented security to each level of users in order to prevent data
corruption which will cause false analysis at the end.
Database have backup system to backup old data to make sure no data is lost in case of
a failure in the central database

Database was designed according to the principles of database designing and after careful considerations of the functional and nonfunctional requirements. Following are the list of major design decisions that has been taken and rationale for taking those dictions

To reduce the redundancy in database created a separate location table to store details about the locations and created foreign key constrained with other entities. Use of foreign keys help to keep data integrity which is used to aid the programmer to manipulate data in the correct way.
Created several views in order to hide data complexity and protect the data. For example when weather conditions are shown to the clients natural join of several tables are done through views excluding information which is not needed to the client.
Database updates rapidly in 24/7 from the readings of the sensors. Therefore database grows fast. Most of the time the users only need the most recent data(within a week).Doing quarrying on a large database reduces the performance. To overcome this, data which is older than 1 week are stored in separate table.
The database is designed in a way which allows to backup data in separate storage space. Above mentioned tables are used to backup data.
Decided to create several user levels with different privileges to access and view the database to increase the security.
Database is in the First Normal Form.
Decided to make location_id and sensor_id of 8 digits integers, so that the first two digits of the sensor_id will indicate which type of a sensor the and next two digits will indicate the year of the sensor which was installed and the last 4 digits will be the sensor number. In location_id, the first two digits will indicate the district number and the next three digits will indicate the sector and the last 3 is the location number.

ER/EER diagrams

Database schema for EMS

Implementation

Initial implementation process

After designing the ER diagrams and database schema for the EMS next step was to choose the suitable implementation methods to create the database and the User Interface. The first challenge is to choose the suitable database type for implement the database. From the choices available, MySQL was chosen for implementing the database. MySQL is good for web apps and it integrates nicely with PHP and other web page developing languages, and as its community server is free. Also MySQL is widely used in the society. Also it has portability, security, Scalability.

The next task was to choose the suitable DBMS application type. For that purpose it has been considered the following aspects

This DBMS will be accessed by multiple users at the same time.
Those multiple users will do several different tasks at the same time.
Those multiple users will not be located inside same premises.
The number of users is not fixed.
Different user types will have different level of privileges.
All the data should have to be synchronously updated.
Data have to be secured.
Database is updated simultaneously with the data sent from the sensors.
All the people can access this through the Internet.

So after considering the above facts the model that has been chosen was a web server with a single database back-end and web clients. All the data and the logic of the DBMS will be resides in the web server and client will be query the server with HTTP to accomplish their tasks. To accomplish the above we created a web site using HTML, PHP and JAVA Scripts. And integrate it with the MySQL database.

Overall implementation process

In the web server there will be a MySQL database resides with all the data. Web server will query the database with MySQL queries to store, retrieve find and edit the data. Data validation, arrangement will be done by the server logic.

All the logic, validations, authentications and entering data and retrieval of data will be govern by the web server. That will be the interface of the database for the outside. Server will run on PHP. Client users HTTP request to accomplish their tasks. On those request server will do queries to the database to achieve the requested tasks. When the server receives those request, it will analyse and process those before executing queries. That will guarantee those rules and regulation of the system.

Functionalities

General functionalities

Any user who visits the web application will be able to see the most recent weather condition in every location of the city.