2.8.2 Relational databases
A short definition of a RDBMS may be a DBMS in which data is
stored in the form of tables and the relationship among the data is also stored
in the form of tables (Codd, 1970). Data items are organized as a set of
formally-described tables from which data can be accessed or reassembled in
many different ways without having to reorganize the database tables.
The relational model is the most commonly used today. It is
used by mainframe, midrange and microcomputer systems. It uses two-dimensional
rows and columns to store data. The tables of records can be connected by
common key values. There are 3 relationship types:
One-to-one (1:1) - Each record in Table A
can have only one matching record in Table B and each record in Table B can be
related to only one record in Table A as shown in figure 2.2. This type of
relationship is not frequently used in database systems, but it can be very
useful way to link two tables together. However, the information related in
this way could be in one table. They may be used to divide a table with many
fields in order to isolate part
of a table for security reasons, or to store information that
applies only to a subset of the main table, or for efficient use of space. A
one-to-one relationship is created if both of the related fields are primary
keys or have unique indexes.
Table A Table B
1
1
irrigation plot
Id irrigation Plot
id plot
valve
id_irrigation_system
number of
sprinklers
plot name
Plot valve
Id plot valve
Longitude
latitude
Altitude
Figure 2.2: One-to-one relationship of
databases
This shows that, one irrigation plot could have one and only
one valve for the control of the flow of water in the plot.
One-to-many (1:M) - It is the most common
type of relationship and it is used to relate one record from the 'primary'
table with many records in the 'related' table. In a one-tomany relationship, a
record ('parent') in Table B can have many matching records ('children') in
Table A, but a record ('child') in Table A has only one matching record
('parent') in Table B as shown in Figure 2.3. This kind of relationship is
created if only one of the related fields is a primary key or has a unique
index.
Table A Table B
Production plot
n 1
Plot name Id_production sector Soil type
Crop variety Planting date Agronomic state Spatial arrangement
Irrigation plots
Total surface area slope
Name_station
Production sector
Id_production sector Surface area
Id_plantation
|
|
Figure 2.3: One-to-many relation of
databases
From Figure 2.3 it could be seen that, one production sector
could be related to many production plots, but one production plot belongs to
one and only one production sector.
Many-to-many (M:M) - is used to relate many
records in the Table A with many records in the Table B. A record ('parent') in
Table A can have many matching records ('children') in Table B, and a record
('child') in Table B can have many matching records ('parents') in Table A. By
breaking it into two one-to-many relationships and creating a new
(junction/link) table to stand between the two existing tables will enable
correct and appropriate relationship setting. A many-to-many relationship is
really two one-to-many relationships with a junction/link table.
A production plot for example could have many soil types and
this soil type could be found in many other production plots around the
plantation.
CHAPTER III
MATERIALS AND METHODS
| 
