DBMS & SQL Fundamentals

What is a Database?

A database is an organized collection of structured data stored electronically. Think of it as a digital filing cabinet where information is arranged systematically for easy access, retrieval, and management.

Examples of databases in daily life:

• Bank records (account numbers, balances, transactions)
• Aadhaar database (biometric data of all Indians)
• PM-KISAN beneficiary list (farmer details, payment records)
• School records (student names, marks, attendance)

What is DBMS?

A DBMS (Database Management System) is software that manages databases — it allows users to create, read, update, and delete data while maintaining security and integrity.

Popular DBMS Software:

Types of Database Models

RDBMS (Relational DBMS) is the most widely used type. Data is stored in tables with defined relationships between them.

Database by Location

Relational Database Concepts

Tables, Records, and Fields

Example — Farmer Database:

Here: FarmerID, Name, Village, Crop, Area are fields. Each row is a record. The whole thing is a table.

erDiagram
FARMER ||--o{ CROP_RECORD : grows
FARMER {
int FarmerID PK
string Name
string Village
string AadhaarNo
}
CROP_RECORD {
int RecordID PK
int FarmerID FK
string Crop
float Area_Hectare
string Season
}
CROP_RECORD }o--|| CROP_MASTER : references
CROP_MASTER {
int CropID PK
string CropName
string CropType
float MSP
}

DBMS Architecture — Three Levels of Abstraction

flowchart TD
A["👤 External Level (View)<br/>What users see — customized per user/app"]
B["📐 Conceptual Level (Logical)<br/>Full structure — tables, relationships, constraints"]
C["💽 Internal Level (Physical)<br/>How data is stored on disk — files, indexes, blocks"]
A --> B --> C

A DBMS organizes data access through three levels, each providing a different view of the data:

This separation is called data abstraction — it hides complexity from users while giving full control to administrators.

Key DBMS Components

What is Metadata?

Metadata is “data about data” — it describes the structure, type, and rules of the actual data stored.

Keys in Databases

SQL — Structured Query Language

SQL (Structured Query Language) is the standard language for communicating with relational databases. It allows you to create, read, update, and delete data.

flowchart TD
SQL["SQL Commands"]
SQL --> DDL["DDL<br/>(Define Structure)"]
SQL --> DML["DML<br/>(Modify Data)"]
SQL --> DQL["DQL<br/>(Query Data)"]
SQL --> DCL["DCL<br/>(Permissions)"]
SQL --> TCL["TCL<br/>(Transactions)"]
DDL --> D1["CREATE / ALTER<br/>DROP / TRUNCATE"]
DML --> D2["INSERT / UPDATE<br/>DELETE"]
DQL --> D3["SELECT"]
DCL --> D4["GRANT / REVOKE"]
TCL --> D5["COMMIT / ROLLBACK"]

SQL Command Categories

Essential SQL Commands

CREATE TABLE — Creates a new table

CREATE TABLE Farmers (FarmerID INT PRIMARY KEY, Name VARCHAR(50), Crop VARCHAR(30));

INSERT INTO — Adds a new record

INSERT INTO Farmers VALUES (101, 'Ram Singh', 'Wheat');

SELECT — Retrieves data

SELECT * FROM Farmers;                    -- All records
SELECT Name, Crop FROM Farmers;           -- Specific columns
SELECT * FROM Farmers WHERE Crop='Wheat'; -- With condition

UPDATE — Modifies existing data

UPDATE Farmers SET Crop='Rice' WHERE FarmerID=101;

DELETE — Removes records

DELETE FROM Farmers WHERE FarmerID=103;

DROP TABLE — Deletes entire table

DROP TABLE Farmers;

Decision Support Systems (DSS)

A Decision Support System is a computer-based information system that helps managers and decision-makers analyze complex data and make better-informed decisions. Unlike simple databases that just store data, a DSS actively helps you interpret and act on that data.

Types of Decisions

Most real-world DSS tools focus on semi-structured decisions — where data helps but human expertise is still needed.

flowchart LR
A[("📊 Database<br/>(Raw Data)")] --> D["🧠 Model Base<br/>(Analysis & Forecasting)"]
B[("📚 Knowledge Base<br/>(Rules & Expertise)")] --> D
D --> E["🖥️ User Interface<br/>(Dashboard & Reports)"]
E --> F["👤 Decision Maker"]

Components of DSS

DSS Limitations

• Data quality — Poor or incomplete data leads to poor decisions (GIGO applies here too)
• Over-reliance — Users may blindly trust DSS output without applying judgment
• Complexity — Advanced models may be difficult for non-technical users to interpret
• Cost — Building and maintaining a DSS requires investment in software, data, and training
• Inappropriate analysis — DSS may run statistical tests on data even when they are not suitable

DSS in Agriculture

Key Takeaways

• Database is organized data; DBMS is software to manage it
• RDBMS (Relational) is most common — data in tables with relationships
• Three levels of abstraction: External (user view), Conceptual (logical), Internal (physical)
• Data Dictionary stores metadata (data about data) — table names, field types, constraints
• Primary Key uniquely identifies records; Foreign Key links tables
• SQL categories: DDL (structure), DML (data), DQL (queries), DCL (permissions)
• SELECT retrieves data, INSERT adds, UPDATE modifies, DELETE removes
• DSS helps with semi-structured decisions — components: Database + Model Base + User Interface
• DSS limitations: data quality, over-reliance, cost, inappropriate analysis

Summary Cheat Sheet