3.3 Development of the Database for the Irrigation
System
The database was developed using Microsoft Access 2003 which
is a relational database management system (RDBMS). The model thus developed
under this software helped us to relate the various aspects of the system and
led to the development of a conceptual and physical model of data. Figure 3.2
shows the architecture of the GIS database
The procedure for the development of the database within MS
Access 2003 included:
· The review of existing data
· The identification of entities and their attributes
· The creation of tables, primary and foreign keys
· The definition of relationships
· Creation of data entry and retrieval forms
· Creation of queries
Irrigation Service
GIS DATABASE ON THE
IRRIGATION SYSTEM
Crop Water Needs Irrigation
Irrigation System
Scheduling Options
Figure 3.5: Architecture of the GIS database 3.3.1 Data
review
Data was reviewed to identify entities, attributes and
facilitate the classification and coding of data. At the start of the study,
most of the data for the PHP irrigation system had been entered into MS excel
2003 spreadsheets. These excel spreadsheets were then transformed into MS
access tables. This decision was made to avoid the re-entry of data as far as
possible. The excel files where then exported to obtain MS access tables.
However, the data on the final table required some cleaning. This was preferred
to a re-entry of the data into MS access because of the time needed to re-enter
data.
3.3.2 Entity and attribute identification
Entities were identified and attributes determined. The
objective was to create normalized, non-redundant table structures. Several
entities regarding to the irrigation system have been identified with respect
to their function in the system. Thus, we started from the pumping station up
to the crop in order to determine the various components which will constitute
entities for the irrigation system database.
3.3.3 Table and key creation
On the basis of the entities determined tables were created.
A primary key was established for each table. The primary keys were defined as
Autonumber? field types to facilitate data entry and to avoid doubles in the
data. Tables for the production plots, irrigation plots, climatic data, soil
data, pumping stations, pipes, sprinklers etc have been created. Four principal
methods exist in MS access for the creation of tables.
· Design Mode
This method was used most often as it enabled us to name
tables and organize the structure of each table. This method has been used for
tables which did not exist in other formats.
Field name
Primary key
Data type
Figure 3.6: Creation of table in design mode under MS
access
· Table assistant mode
Here, access proposes different models of pre-established
tables to choose from and create the desired table. The table was then
personalized in order for it to suit the conditions required.
· Import table mode
This method has been used for the design of most of the tables
that are being presented in this database. The rationale for this was the fact
that most of the data concerning the irrigation system has already been stored
either as texts or in MS excels spreadsheets. This made it easy for us to
import the tables to our database and then define the contents of each field to
suit the requirements of our database under the design mode by modifying the
specifications of the various fields involved.
· Attach table mode
This method has been used for files that need to remain in
their original database such as excel files on which calculations have been
done. Hence the link between these files and the attached table in MS access
remains and any modification on the file immediately leads to a modification in
the attached table. In the calculation of the crop water requirements, the
excel spreadsheet on which all the formulas were entered was exported to the
database.
| 
