Agricultural Statistics & Data Science · Statistical computing, data science and practice · also Database Management Systems, Data Engineering, Agricultural Informatics
Storing and serving agricultural data at scale: relational design and SQL, data warehouses and multidimensional models, big-data tools, APIs and open-data portals, sensor and IoT streams, FAIR data and data governance, and the information systems that hold trial, survey and market data.
In the exams: AICE-JRF Computer Application Unit 9 DBMS (heavy); GATE DA 'Database Management and Warehousing' (heavy); AIEEA-PG Group 07 MS Access databases (present)
Agricultural statistics now depends as much on data systems as on statistical methods. Crop-cutting experiments, farm surveys, variety trials, market prices, weather stations, soil sensors, drone and satellite imagery, livestock registers and extension records all generate data that must be stored, linked, checked, protected, shared and served to analysts and decision makers. A national crop-estimation programme that cannot reconcile district totals, a trial network whose results sit in hundreds of incompatible spreadsheets, or an advisory service that sends farmers outdated prices fails for reasons of data management, not statistics. This subject covers the database and information-system knowledge that agricultural statisticians and computer-application specialists need.
The manual covers the relational model, entity–relationship modelling and normalisation; SQL and procedural extensions; transactions, concurrency, indexing and recovery; distributed databases; data warehousing with star and snowflake schemas and OLAP; big-data tools for distributed storage and processing; APIs, open-data portals and data standards, including international agricultural statistics databases; sensor, Internet-of-Things and drone data pipelines; the FAIR principles, metadata and data governance; and agricultural information and decision-support systems. Throughout, examples come from agricultural data: plots and treatments, farms and households, prices and markets, weather and sensors.
Doctoral examinations in computer applications for agriculture test database management in depth, with questions on keys, normal forms, SQL queries, transactions, concurrency control, indexing and recovery; graduate entrance examinations in data science test database management and data warehousing; and master's admission examinations in statistical sciences include basic database concepts and desktop database use. Typical questions ask for the normal form of a relation, the result of a query, the effect of a transaction schedule, the type of OLAP operation, or the right design for a given agricultural data problem.
In the relational model, proposed by E. F. Codd in 1970, data are stored in relations (tables), each consisting of tuples (rows) with values for a fixed set of attributes (columns), each attribute drawing its values from a domain. A relation has no duplicate tuples and no meaningful row order. A superkey is any set of attributes that uniquely identifies a tuple; a candidate key is a minimal superkey; the primary key is the candidate key chosen to identify tuples; and a foreign key is an attribute in one relation that refers to the primary key of another. Two integrity rules apply: entity integrity (no part of a primary key may be null) and referential integrity (every foreign-key value must match an existing primary-key value or be null). In a trial database, a Plot relation might have primary key plot_id, with foreign keys trial_id referring to a Trial relation and variety_code referring to a Variety relation, so that no plot can refer to a variety that does not exist.
Relational algebra provides the operations on which query languages are built: selection (σ) picks rows meeting a condition; projection (π) picks columns; the Cartesian product combines every row of one relation with every row of another, so relations of 10 and 20 rows give 200; joins combine related rows, the natural join matching on common attributes; and set operations (union, intersection, difference) apply to relations with the same attributes. Division answers 'for all' queries, such as farms that grow every crop in a list.
Database design starts with a conceptual model, usually an entity–relationship (ER) diagram, introduced by Chen in 1976. Entities are things about which data are kept (farm, field, crop, season); attributes describe them (farm area, owner's name); and relationships link them (a farm has fields; a field is sown with a crop in a season). Relationships have cardinalities: one-to-one, one-to-many (one farm, many fields) and many-to-many (many farmers sell to many traders). In the relational schema, a one-to-many relationship is implemented by placing the primary key of the 'one' side as a foreign key in the 'many' side, and a many-to-many relationship requires a separate junction (associative) table whose primary key combines the keys of both entities, so a many-to-many relationship between two entities needs three tables. Weak entities, which cannot be identified without their owner, such as a plot within a trial numbered 1 to n, take part of their key from the owning entity. Attributes can be simple or composite, single- or multi-valued, and derived attributes, such as farm age from establishment year, are usually computed rather than stored.
Normalisation organises relations to remove redundancy and the update anomalies it causes. A functional dependency X → Y means that the value of X determines the value of Y. Anomalies arise when a relation mixes facts about different things: storing each farmer's village and district in every row of a crop-sales table means that a district change must be made in many rows (update anomaly), a new village cannot be recorded until a sale occurs (insertion anomaly), and deleting the last sale loses the village (deletion anomaly). First normal form (1NF) requires atomic values, with no repeating groups or lists in a cell, so a 'crops grown' cell containing 'rice, wheat' violates it. Second normal form (2NF) requires 1NF and that no non-key attribute depend on only part of a composite key: in a table keyed by (farm_id, crop) with an attribute farm_area, farm_area depends only on farm_id, a partial dependency. Third normal form (3NF) requires 2NF and no transitive dependency of a non-key attribute on the key through another non-key attribute, as when district depends on village, which depends on farm_id. Boyce–Codd normal form (BCNF) requires that every determinant be a candidate key. Higher forms (4NF, 5NF) address multi-valued and join dependencies. Normalised designs avoid anomalies; for analytical workloads, deliberate denormalisation is sometimes used for speed, as in data warehouses.
SQL (Structured Query Language) is the standard language for relational databases. Its statements fall into groups: data definition (CREATE, ALTER and DROP tables, views and indexes, with constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE and CHECK); data manipulation (SELECT, INSERT, UPDATE, DELETE); data control (GRANT and REVOKE privileges); and transaction control (COMMIT, ROLLBACK, SAVEPOINT). A query has the form SELECT columns FROM tables WHERE row-conditions GROUP BY grouping-columns HAVING group-conditions ORDER BY columns. Its logical order of evaluation differs from its written order: FROM (and joins), then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. This is why a condition on an aggregate, such as average yield greater than 3, must go in HAVING, not WHERE, and why a column alias defined in SELECT can be used in ORDER BY but not in WHERE. For example, SELECT variety, AVG(yield) AS mean_yield FROM plots WHERE trial_id = 12 GROUP BY variety HAVING COUNT(*) >= 4 ORDER BY mean_yield DESC lists the mean yield of each variety with at least four plots in trial 12, highest first.
Joins combine tables: an inner join returns only matching rows; a left outer join returns all rows of the left table, with NULLs where the right table has no match; right and full outer joins are defined similarly; and a cross join returns the Cartesian product. A self-join relates rows of the same table, such as plots to neighbouring plots. NULL represents a missing or inapplicable value, and SQL uses three-valued logic: any comparison with NULL, even NULL = NULL, is unknown rather than true, so rows with NULL are not returned by WHERE yield = NULL; the test must be WHERE yield IS NULL. Aggregates ignore NULLs, except that COUNT(*) counts all rows while COUNT(yield) counts non-null yields, a difference that matters when reporting the number of harvested plots. Subqueries nest one query inside another, in WHERE (with IN, EXISTS or comparison), in FROM (as a derived table) or in SELECT; correlated subqueries refer to the outer query and run once per outer row. Views are stored queries that act as virtual tables, useful for presenting consistent, restricted or pre-joined data to users. Indexes speed up searches and joins on the indexed columns at the cost of storage and slower inserts and updates.
Standard SQL is declarative: it says what result is wanted, not how to compute it. Procedural extensions, such as PL/SQL in one widely used commercial database system and similar languages in others, add variables, conditional statements, loops, error handling and modular code that runs inside the database. A PL/SQL block has DECLARE (variables and cursors), BEGIN (executable statements) and EXCEPTION (error handlers) sections, ending with END. Cursors let a program process the rows of a query one at a time. Stored procedures and functions encapsulate business logic, such as computing district crop estimates from plot data or validating survey records, so that every application uses the same rules; triggers run automatically before or after inserts, updates or deletes, for example to record an audit trail of who changed a price record and when, or to reject a yield above a plausibility threshold. Procedural code should be kept for logic that genuinely belongs in the database, because set-based SQL is usually far faster than row-by-row loops.
Modern SQL includes window functions, which compute values across sets of rows related to the current row without collapsing them into groups. For example, AVG(price) OVER (PARTITION BY market ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) gives a four-week moving average price for each market on every row; RANK() OVER (PARTITION BY trial ORDER BY yield DESC) ranks varieties within each trial; and LAG(price) OVER (PARTITION BY market ORDER BY week) retrieves the previous week's price, so that weekly changes can be computed in one query. Common table expressions (WITH clauses) name intermediate results and make long queries readable, and recursive forms traverse hierarchies such as administrative units nested in regions or pedigrees of breeding lines. These features let much routine summarisation be done inside the database, close to the data, before results are passed to statistical software.
A transaction is a sequence of database operations that forms one logical unit of work, such as recording a sale and updating the stock of a seed store. Transactions should satisfy the ACID properties: atomicity (all of its operations happen or none do), consistency (it takes the database from one valid state to another, respecting constraints), isolation (concurrent transactions do not see each other's intermediate states; the result is as if they ran in some serial order) and durability (once committed, its effects survive failures). COMMIT makes a transaction's changes permanent; ROLLBACK undoes them.
When transactions run concurrently, several anomalies can occur without control. A lost update happens when two transactions read the same value and both write, so one update overwrites the other: if two clerks each read a stock of 100 bags and each sells 10, the stock should be 80 but may be recorded as 90. A dirty read reads data written by a transaction that later rolls back; a non-repeatable read gets different values when reading the same row twice because another transaction changed it in between; and a phantom read finds new rows on re-running a query because another transaction inserted them. SQL defines isolation levels that allow progressively fewer anomalies: read uncommitted (dirty reads possible), read committed (no dirty reads), repeatable read (no non-repeatable reads) and serializable (no anomalies, equivalent to serial execution). Higher isolation costs concurrency. Locking protocols use shared locks for reading and exclusive locks for writing; two-phase locking (2PL), in which a transaction acquires all its locks before releasing any, guarantees conflict-serialisable schedules, and strict 2PL, which holds exclusive locks until commit, also prevents cascading aborts. Locking can cause deadlocks, which systems detect with waits-for graphs and resolve by aborting a victim transaction. Alternatives include timestamp ordering and multiversion concurrency control (MVCC), in which readers see a consistent snapshot without blocking writers.
Indexes are auxiliary structures that make lookups fast. The B+ tree, the standard index structure, is a balanced tree whose leaves hold keys in sorted order with pointers to records and links between leaves; searches, insertions and deletions take O(log n) time, and range queries (all prices between two dates) are efficient. Hash indexes give near-constant-time equality lookups but do not support range queries. A clustered index determines the physical order of the table's rows, so a table can have only one; non-clustered (secondary) indexes are separate structures, and there can be many. Recovery protects committed work against crashes. The write-ahead logging (WAL) rule requires that a log record describing a change be written to stable storage before the changed data page; after a failure, the system redoes the changes of committed transactions and undoes those of uncommitted ones, using the log. Checkpoints periodically record a consistent point so that recovery need not scan the whole log. Backups and log archives protect against media failure, and regular test restores show that backups actually work.
A distributed database stores data at several sites connected by a network while presenting them to users as one database. Agricultural information systems are often distributed by nature: district offices enter survey and price data, regional centres consolidate them, and a national server publishes results. Data can be fragmented horizontally, storing different rows at different sites (each district holds its own farms), or vertically, storing different columns (administrative details at one site, technical measurements at another), with the original relation reconstructible by union or join. Replication keeps copies of data at several sites, which improves availability and read performance and allows local operation when links fail, but requires keeping copies consistent. Distribution transparency hides location, fragmentation and replication from users, who query the database as if it were central. Distributed query processing chooses where to execute operations to minimise data transfer, for example by sending a selection to the site holding the data rather than shipping the whole table.
A transaction that updates data at several sites must commit everywhere or nowhere. The two-phase commit (2PC) protocol achieves this: in the prepare phase, the coordinator asks each participant whether it can commit, and each participant that votes yes guarantees it can do so; in the commit phase, if all voted yes, the coordinator tells them to commit, otherwise to abort. Its weakness is blocking: if the coordinator fails after participants have voted yes, they must wait. The CAP theorem, stated by Brewer and later proved, says that when a network partition occurs, a distributed system must choose between consistency (every read sees the latest write) and availability (every request receives a response); it cannot guarantee both along with partition tolerance. Systems that favour availability offer eventual consistency, in which replicas converge if updates stop, as in many large web-scale stores; systems that favour consistency may refuse requests during partitions.
So-called NoSQL databases relax relational structure or transactional guarantees to gain scalability or flexibility. Key–value stores map keys to opaque values and suit caching and session data; document stores hold self-describing documents such as JSON records, convenient for survey responses whose structure varies between questionnaire versions; wide-column stores organise data in column families across many machines and suit very large, sparse data such as sensor readings; and graph databases store nodes and relationships, suited to pedigrees, supply chains and knowledge graphs. Many such systems follow the BASE philosophy (basically available, soft state, eventually consistent) rather than ACID, though some now offer transactions. Relational databases remain the best default for structured, interrelated data needing integrity, such as trial and survey records; non-relational stores complement them for particular workloads.
Operational (online transaction processing, OLTP) databases support day-to-day recording, such as entering market prices or survey forms, with many small, concurrent transactions and highly normalised designs. Analytical (online analytical processing, OLAP) systems support summaries and trends across large volumes of historical data, such as production by crop, district and year over two decades, with few but complex read-only queries. A data warehouse, defined by Inmon as a subject-oriented, integrated, time-variant and non-volatile collection of data supporting decision making, separates analytics from operational systems. Data reach it through extract, transform and load (ETL) processes that extract data from sources, clean them, convert codes and units to common standards, integrate them, and load them on a schedule; transformation is where most of the effort and most of the data-quality work lie. Data marts are smaller warehouses for particular subjects or departments, and two classic design approaches are Inmon's top-down enterprise warehouse feeding marts and Kimball's bottom-up dimensional marts integrated through shared dimensions.
Warehouses are usually designed dimensionally. A fact table records measurements of a business process at a declared grain, such as one row per district, crop, season and year, with numeric measures (area harvested, production, yield) and foreign keys to dimension tables; dimension tables describe the context (time with year, season and month; geography with district, region and country; crop with crop group and variety type). In a star schema, each dimension is a single denormalised table joined directly to the fact table, which keeps queries simple and fast. In a snowflake schema, dimensions are normalised into several related tables (district → region → country), saving space and easing maintenance at the cost of more joins. A fact constellation (galaxy) shares dimensions among several fact tables, such as production and prices. Measures are additive (production can be summed over all dimensions), semi-additive (stock levels can be summed over regions but not over time) or non-additive (yields and prices must be recomputed from totals or averaged with weights, never summed). Because yield is production divided by area, the correct regional yield is total production divided by total area, not the average of district yields. Slowly changing dimensions handle attributes that change over time, such as districts that are split or renamed, by overwriting, adding new rows with validity dates, or adding columns.
OLAP tools present data as multidimensional cubes; a cube with 10 years, 30 districts and 8 crops has 2,400 cells. Roll-up aggregates to a coarser level (districts to regions, months to years); drill-down goes to finer detail; slice fixes one dimension at a single value (only the year 2020); dice selects a sub-cube with ranges on several dimensions (rice and wheat in three regions over five years); and pivot rotates the axes of the view. Aggregates may be precomputed to make queries fast. Agricultural statistics agencies use warehouses and cubes to publish dissemination databases, in which users select crop, area and period and view or download tables and charts.
Big data are commonly characterised by volume (amounts too large for a single machine), velocity (data arriving continuously and quickly, such as sensor streams), variety (structured tables, text, images, video, geospatial and sensor formats), veracity (uncertain quality) and value (the usefulness extracted). In agriculture, satellite archives, drone imagery, genomic sequences, machine telemetry from tractors and harvesters, weather model outputs and mobile-phone transaction records can all reach these scales. Not every large data set needs special tools: a few gigabytes of survey or trial data are handled comfortably by a relational database or a statistical package on one computer, and adopting distributed tools without need adds complexity.
Distributed file systems, such as the Hadoop Distributed File System (HDFS), store very large files by splitting them into large blocks (typically 128 MB) and spreading them over many commodity machines, with each block replicated, by default three times, so that the failure of a machine loses no data. Computation is moved to the data rather than data to the computation. The MapReduce programming model, popularised by Google and implemented in open-source frameworks, processes such data in parallel in two phases: the map phase applies a function to each input record and emits key–value pairs, and after a shuffle that groups pairs by key, the reduce phase combines the values for each key. Counting how often each pest name appears in millions of extension advisory records, for example, maps each record to (pest, 1) pairs and reduces by summing counts for each pest. MapReduce is fault-tolerant and scalable but writes intermediate results to disk, which makes iterative algorithms, such as fitting statistical models, slow.
In-memory cluster frameworks, such as Apache Spark, keep intermediate data in memory across a cluster and provide higher-level abstractions (resilient distributed datasets and data frames), SQL interfaces, machine-learning libraries and stream processing; they are much faster than disk-based MapReduce for iterative and interactive work. Columnar file formats, such as Parquet and ORC, store data column by column with compression, so that analytical queries reading a few columns of a wide table read far less data. Data lakes store raw data in its native formats at low cost for later processing, with the risk of becoming 'data swamps' without metadata and governance. Stream-processing systems analyse data as they arrive, for example flagging sensor readings that exceed thresholds within seconds. Cloud platforms provide these tools as managed services, and analysis-ready data cubes of satellite imagery let users run statistics over large areas without handling the raw files.
Much agricultural data is published openly. FAOSTAT, maintained by the Food and Agriculture Organization of the United Nations, provides country-level time series from 1961 on crop and livestock production, trade, food balances, land use, inputs, emissions and prices for over 200 countries and territories. Other international sources include the World Bank's development indicators, the AMIS market monitor for major grains, international crop and price monitoring systems, global soil and climate databases, and data repositories of international agricultural research centres. National statistical offices and ministries of agriculture publish census and survey results through open-data portals, many built on open-source portal software, with downloadable files and machine-readable interfaces. Users must read the metadata: definitions (production of paddy or milled rice, area sown or harvested), units, reference periods (calendar or agricultural year), estimation methods, revisions and flags marking official, estimated or imputed values.
An application programming interface (API) lets programs request data directly instead of downloading files by hand. Most data APIs follow the REST style: resources are identified by URLs, requested with HTTP GET, and returned in machine-readable formats, usually JSON or CSV, with query parameters specifying, for example, country, item, element and years. Using an API from R or Python makes data retrieval reproducible, since the script records exactly what was requested, and allows automatic updating of analyses. Practical aspects include authentication with API keys for some services, rate limits on the number of requests, pagination of large results, error handling for unavailable services, and versioning of the API, which may change. Responsible use caches results rather than repeatedly downloading the same data and records the date of retrieval, since published figures are revised.
Standards make data from different sources comparable and combinable. Statistical classifications include the Central Product Classification (CPC) and the Harmonized System (HS) for products and traded commodities, the International Standard Industrial Classification (ISIC), and FAO's commodity lists; ISO 3166 codes identify countries, and national codes identify administrative areas, which change over time and need concordance tables. The Statistical Data and Metadata eXchange (SDMX) standard, sponsored by international organisations including the FAO, IMF, OECD, World Bank and Eurostat, defines formats and structures for exchanging statistical data and metadata between agencies. Controlled vocabularies and ontologies, such as FAO's AGROVOC multilingual thesaurus and crop trait ontologies, standardise terms. Units should follow the International System of Units, with conversion factors documented for local units of land and produce, which vary widely between and within countries and are a major source of error in farm surveys.
Agricultural Internet-of-Things (IoT) systems connect sensors in fields, greenhouses, stores and animal houses to central systems. Sensors measure soil moisture and temperature, air temperature and humidity, rainfall, leaf wetness, water levels in canals, grain temperature in stores, animal activity, milk yield and many other quantities. Readings travel over communication technologies chosen for range, power and cost: low-power wide-area networks such as LoRaWAN and NB-IoT carry small messages over kilometres with years of battery life; cellular networks carry larger volumes; and short-range radio links connect devices to local gateways. Messaging protocols such as MQTT use a publish–subscribe model in which devices publish readings to topics on a broker and applications subscribe to the topics they need, which suits unreliable, low-bandwidth links. Edge computing processes data near the source, for example averaging readings or detecting alarms on a gateway, to reduce transmission and respond quickly.
Sensor data accumulate quickly. A station recording one value every 15 minutes produces 96 readings per day and 35,040 per year per variable; a network of 500 stations with ten variables produces about 175 million readings a year. Time-series databases, designed for timestamped data, store and query such data efficiently, with retention policies that keep raw readings for a period and aggregates for longer. Quality control is essential, because sensors drift, fail, get buried, flooded or chewed, and lose power or connection: pipelines check ranges and rates of change, flag stuck values and gaps, compare neighbouring sensors, and apply calibration corrections, recording quality flags alongside values rather than silently deleting or altering data. Timestamps should be stored in Coordinated Universal Time (UTC) with the time zone recorded, to avoid errors at daylight-saving changes and when combining networks. Metadata on sensor type, installation depth or height, calibration dates and maintenance are as important as the readings.
Drones (unmanned aerial vehicles) carrying RGB, multispectral or thermal cameras, or LiDAR, survey trial plots and fields at centimetre resolution. A typical pipeline plans flights with sufficient image overlap (often 70–80% forward and 60–70% side), places and surveys ground control points for accurate georeferencing, processes the images by structure-from-motion photogrammetry into orthomosaics and digital surface models, applies radiometric calibration using reflectance panels, and extracts plot-level traits such as canopy cover, plant height (from surface models) and vegetation indices such as the normalised difference vegetation index, NDVI = (NIR − Red)/(NIR + Red). Plot boundaries are digitised or generated from the trial layout so that each plot's values link to its treatment in the trial database. Each flight generates gigabytes of images, so storage, processing time and naming conventions must be planned, and flight conditions (sun angle, cloud, wind) recorded because they affect measurements. Aviation rules for drones differ between countries and must be followed.
A sensor or drone pipeline is complete only when its outputs reach analysts in a usable form. That means agreed variable names and units, summaries at the time steps and spatial units that analyses need (daily rainfall by station, plot-level canopy cover by trial), quality flags carried through every stage, and links to the experimental or survey database by stable identifiers such as plot or station codes. Automated reports showing data completeness, sensor failures and recent values let managers spot problems within days rather than at the end of a season, when lost data cannot be recovered.
The FAIR principles, published in 2016, state that research data should be findable (assigned globally unique, persistent identifiers such as DOIs, and described with rich metadata indexed in searchable resources), accessible (retrievable by their identifier using a standard, open protocol, with metadata remaining available even when data are restricted), interoperable (using formal, shared languages, vocabularies and standards so that they can be combined with other data) and reusable (with clear licences, detailed provenance and community standards). FAIR does not mean open: confidential farm or household data can be FAIR if their metadata are public and access is granted under defined conditions. Funders and journals increasingly require data management plans and deposit of data in recognised repositories, and agricultural research organisations have built repositories and dataverse-style systems for trial and survey data.
Metadata describe data at several levels: discovery metadata (title, authors, keywords, spatial and temporal coverage, identifiers), structural metadata (files, tables, variables, codes) and provenance (how data were collected and processed). Standards include Dublin Core for general discovery, the Data Documentation Initiative (DDI) for surveys, ISO 19115 for geographic data, and domain standards for trials, genotypes and phenotypes, such as minimum information standards for plant phenotyping experiments. Licences tell users what they may do: Creative Commons Attribution (CC BY) allows any reuse with credit, CC0 waives rights entirely, and more restrictive terms may forbid commercial use or redistribution. Data without a licence are legally ambiguous and often cannot be reused safely.
Data governance is the system of decision rights, policies and responsibilities for an organisation's data: who owns and stewards each data set, who may access it and for what purposes, what quality standards apply, how long data are kept, how privacy and security are protected, and how disputes are resolved. Data stewards are responsible for the quality and documentation of particular data sets. In agriculture, governance raises distinctive questions: who controls data generated on farms by machinery, sensors and digital advisory services; whether farmers can access, move and delete their data; how data from smallholders are shared with governments and companies; and how benefits are shared. Several countries and industry groups have codes of practice on agricultural data that address consent, transparency, portability and use. For data about Indigenous peoples and their lands, the CARE principles (collective benefit, authority to control, responsibility and ethics) complement FAIR by emphasising Indigenous data sovereignty, the right of peoples to govern data about themselves.
Information systems turn data into information that people use. Management information systems provide routine reports, such as monthly crop condition, procurement or input distribution by district. Decision-support systems (DSS) help users make semi-structured decisions by combining three components: a database, a model base (simulation, optimisation or statistical models) and a user interface, so that users can ask 'what if' questions. Expert systems encode specialists' knowledge as rules in a knowledge base and apply an inference engine to reach conclusions, either by forward chaining (from observed facts towards conclusions, as in diagnosing a pest from symptoms) or backward chaining (from a hypothesis back to the facts that would support it). Geographic information systems underpin many agricultural systems, linking data to locations. Farm management information systems record field operations, inputs, yields and finances for individual farms and increasingly draw data automatically from machinery and sensors.
Crop simulation models, such as those of the DSSAT and APSIM families, simulate crop growth from weather, soil, genotype and management, and are embedded in DSS for choosing sowing dates, varieties, irrigation and fertiliser rates, and for yield forecasting. Early-warning systems for food security, such as FAO's Global Information and Early Warning System and regional famine early-warning networks, combine satellite indicators of vegetation and rainfall, market prices, crop assessments and nutrition data to warn of crises. Pest and disease forecasting systems use weather data and epidemiological models to advise when to spray, such as blight forecasting for potatoes. Market information systems collect prices from markets and disseminate them by text message, voice services, radio and web, improving farmers' bargaining positions. Digital advisory services deliver weather-based and crop-stage advice to farmers' phones, often in local languages. Irrigation scheduling systems combine soil-moisture sensors, evapotranspiration estimates and crop coefficients.
Many agricultural information systems fail not technically but because they do not fit users' needs, timing, literacy, languages, devices or trust. Good design involves users from the start, delivers information at the time and scale decisions are made, states uncertainty in understandable ways, works with poor connectivity, and is sustainable after initial project funding ends. Evaluation should measure whether information changed decisions and outcomes, ideally with rigorous designs such as randomised or quasi-experimental impact evaluations, not only counts of messages sent or users registered. Data quality is fundamental: a DSS whose soil or weather inputs are wrong gives confidently wrong advice. For statisticians, these systems are both consumers of their estimates and sources of new data, and the integrity of the whole chain, from field measurement to the advice a farmer receives, depends on sound data systems.
A student uses this subject to design a small database for a trial or survey, with keys, relationships and normalised tables; to write SQL queries that summarise yields by treatment or prices by market; to understand why concurrent data entry needs transactions; to download FAOSTAT or national data through an API in a reproducible script; and to document data with metadata and a licence. In examinations, typical tasks are identifying keys and normal forms, predicting query results including NULL behaviour, recognising concurrency anomalies, choosing star or snowflake designs, naming OLAP operations and explaining FAIR principles.
A statistician or computer-application specialist five years into practice builds and maintains the data systems of a programme: databases for multi-location trials, survey data-capture and cleaning pipelines, price databases, sensor networks and drone-image workflows. Daily work includes writing SQL and ETL scripts, enforcing validation and referential integrity, managing backups and access rights, integrating data from different classifications and administrative boundaries, and serving data to analysts and dashboards. Typical decisions are how to model a new data source, how to handle changing district codes, and how to flag rather than delete suspect sensor values.
A senior professional with twenty-five years of experience leads the information systems of an agricultural statistics agency or research institute: setting data architecture and standards, choosing between relational, warehouse, cloud and big-data technologies, defining governance policies for access, privacy and sharing, negotiating data agreements with other agencies and companies, and ensuring that dissemination systems meet international standards. They balance ambitions for new technology against budgets, skills and the need for continuity of long statistical series. They also plan for the long life of statistical series, so that systems can change without breaking comparability.
A researcher with a PhD and long experience works on the frontier of agricultural data science: integrating heterogeneous data from surveys, sensors, satellites and genomes; designing scalable databases and analysis pipelines; developing decision-support systems with rigorous evaluation of their impact; and studying data governance, farmer data rights and Indigenous data sovereignty. Such researchers advise national and international bodies on data infrastructure and standards, supervise doctoral work in computer applications and examine candidates. They help agencies evaluate new data sources before they are adopted into official systems.
This manual covers database and information-system concepts at the level of doctoral computer-application and data-science entrance examinations. It does not teach any particular database product, cloud service or big-data platform in detail, does not cover database internals such as query optimisation algorithms in depth, and describes national data portals and agricultural data codes of practice only generically, since they differ by country and change. Specific technologies named are examples, not recommendations.
Original practice questions written to the syllabus and pattern of the exams named in each question's examTags. They are not past papers and do not reproduce any board's questions.
These notes are free to read here, with no account.
What the Laisha app adds:
Free for ten days, then a subscription. We would rather write that here than have you find it later.