₹499 ₹999 · Full access — all mocks, practice sets & books · Unlock now
← Index: Computer Awareness — Complete Guide for Competitive ExamsChapter 8
Study Guide · Chapter 8

Databases, SQL & Data Management

Free study material · concepts, shortcuts & solved questions

✍️ Select any text to highlight or save it

Introduction: Organizing Information at Scale

Imagine a library with 10 million books. Without a catalog system, finding a specific book is impossible. A database is that catalog system for digital information—a highly organized, searchable repository of data.

Before databases, companies stored data in:

  • Spreadsheets (Excel) = Slow, unscalable, prone to errors when large
  • Flat files (text files) = No relationships between data, wasteful storage

A database solves these problems by organizing data into tables (like spreadsheets), with relationships between tables, and powerful search capabilities.

Section 1: What Is a Database?

A database is an organized collection of structured data stored, managed, and accessed through a Database Management System (DBMS).

Key Components

Tables (like spreadsheets)

  • Rows = Records (individual data entries, e.g., one customer)
  • Columns = Fields/Attributes (properties, e.g., customer name, email, phone)
  • Intersection of row+column = A single data value (e.g., "John" in Name column of Row 3)

Example: Customer Table

CustomerID Name Email Phone City
1 John Doe john@email.com 555-1234 Delhi
2 Jane Smith jane@email.com 555-5678 Mumbai
3 Bob Wilson bob@email.com 555-9999 Bangalore

Keys (identifiers)

  • Primary Key: Unique identifier for each record (CustomerID in above table)

    • No two rows can have the same primary key
    • Used to ensure data integrity

  • Foreign Key: Reference to primary key in another table

    • Creates relationship between tables
    • Example: Order table's CustomerID is a foreign key linking to Customer table

Relationships

  • One-to-Many: One customer → Multiple orders
  • Many-to-Many: Multiple students ↔ Multiple courses (through junction table)
  • One-to-One: Rare; one customer → one VIP status record

Exam Tip: Primary Key uniquely identifies records; Foreign Key links tables together.

Section 2: Relational vs. Non-Relational Databases

Relational Databases (SQL Databases)

Definition: Data organized into tables with relationships (foreign keys)

Characteristics:

  • Structured: Predefined schema (table structure must be defined before inserting data)
  • ACID Compliance: Guarantees data integrity
    • Atomicity: Transaction all-or-nothing (either completely succeeds or fails)
    • Consistency: Data remains valid before and after transaction
    • Isolation: Concurrent transactions don't interfere
    • Durability: Committed data survives system failures
  • Query Language: SQL (Structured Query Language)
  • Best for: Business data (customers, orders, inventory, employees)

Popular Relational Databases:

  • MySQL: Open-source, popular for web applications
  • PostgreSQL: Open-source, powerful, enterprise-grade
  • Oracle Database: Commercial, expensive, used by large enterprises
  • Microsoft SQL Server: Commercial, integrated with Windows/Azure
  • SQLite: Lightweight, embedded (used in mobile apps, browsers)

Exam Tip: MySQL is most popular for small-to-medium web apps; Oracle/SQL Server for enterprises.

Non-Relational Databases (NoSQL)

Definition: Flexible data storage without rigid table schema

Characteristics:

  • Unstructured: No predefined schema; add fields dynamically
  • Scalability: Distribute data across many servers (horizontal scaling)
  • Performance: Fast for certain queries (reading lots of records)
  • Flexibility: Handle varied data types (JSON documents, arrays, nested structures)

Trade-offs:

  • Sacrifice: ACID guarantees (eventual consistency instead)
  • Benefit: Massive scalability and flexibility

Popular NoSQL Databases:

  • MongoDB: Document-based (stores JSON-like documents)
  • Cassandra: Column-family (distributed, fault-tolerant)
  • Redis: Key-value store (in-memory, very fast)
  • DynamoDB: AWS managed service

Use Cases for NoSQL:

  • Social media feeds (unstructured posts, comments, reactions)
  • Real-time analytics (streaming data)
  • Content management (varied content types)
  • Mobile apps (offline-first, synced later)

Exam Tip: Relational = structured, reliable; NoSQL = flexible, scalable

Section 3: SQL (Structured Query Language)

SQL is the standard language for querying relational databases. It's declarative (you say what you want, not how to get it).

Basic SQL Operations

CREATE Creates a new table with structure:

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name VARCHAR(100),
  Email VARCHAR(100),
  Phone VARCHAR(20)
);

INSERT Adds new records:

INSERT INTO Customers (CustomerID, Name, Email, Phone)
VALUES (1, 'John Doe', 'john@email.com', '555-1234');

SELECT Retrieves data (most common operation):

SELECT Name, Email FROM Customers WHERE City='Delhi';
  • SELECT Name, Email = Which columns
  • FROM Customers = Which table
  • WHERE City='Delhi' = Filter condition

UPDATE Modifies existing records:

UPDATE Customers SET Phone='555-9999' WHERE CustomerID=1;

DELETE Removes records:

DELETE FROM Customers WHERE CustomerID=1;

SQL Operators & Keywords

WHERE Clauses:

  • WHERE Age > 18 = Greater than
  • WHERE Name LIKE 'J%' = Starts with J
  • WHERE City IN ('Delhi', 'Mumbai') = Match list
  • WHERE Email IS NULL = Missing value

Aggregate Functions:

  • COUNT() = Number of records
  • SUM() = Total of numeric column
  • AVG() = Average
  • MAX() = Highest value
  • MIN() = Lowest value

Example:

SELECT COUNT(*) FROM Customers WHERE City='Delhi';
-- Returns: 5 (5 customers in Delhi)

JOIN Combines data from multiple tables:

SELECT Customers.Name, Orders.OrderID
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
  • Returns: Customer names paired with their orders

ORDER BY Sorts results:

SELECT * FROM Customers ORDER BY Age DESC;
-- Descending = oldest first; ASC = youngest first (default)

GROUP BY Aggregates data by category:

SELECT City, COUNT(*) FROM Customers GROUP BY City;
-- Shows count of customers per city

Exam Tip: For competitive exams, focus on SELECT, INSERT, UPDATE, DELETE basics. Don't need complex joins or subqueries.

Section 4: Database Design & Normalization

Normalization: Eliminating Redundancy

Problem: Duplicated data wastes space and causes inconsistencies

  • If you update a customer's email in one place, you must update everywhere
  • Mistakes lead to data inconsistency

Solution: Normalization—organizing tables to minimize redundancy

Normal Forms (simplified):

First Normal Form (1NF):

  • Each cell contains only one value (not lists)
  • No repeating columns
  • Example: Customer has columns Name, Email, Phone (not Email1, Email2, Email3)

Second Normal Form (2NF):

  • Remove data that depends on only part of a composite key
  • Most well-designed databases satisfy this

Third Normal Form (3NF):

  • Remove data that depends on non-key columns
  • Example: Don't store CustomerCity if you already have CustomerID that relates to a Cities table

Exam Tip: For SSC exams, understanding redundancy and basic normalization (1NF) is sufficient.

Section 5: Database Security

Access Control

Principle of Least Privilege:

  • Users get minimum permissions needed
  • Example: Report-writing employees get READ-only access; admins get full access

Role-Based Access:

  • DBA (Database Administrator) = Full control
  • Analyst = READ-only
  • Developer = READ/WRITE for testing

Encryption

Data at Rest (stored on disk):

  • Encrypt database files using encryption keys
  • If someone steals hard drive, data is unreadable

Data in Transit (traveling over network):

  • Use HTTPS/TLS when accessing databases remotely
  • Prevents eavesdropping

Backup & Disaster Recovery

Regular Backups:

  • Daily/hourly backups of databases
  • Stored separately (different location/server)

Recovery Testing:

  • Periodically restore from backup to test it works
  • Many companies back up but never test restoration—disaster hits and backups are corrupted

RTO/RPO (Recovery metrics):

  • RTO (Recovery Time Objective): How long to restore service? (Goal: 2 hours)
  • RPO (Recovery Point Objective): How much data loss acceptable? (Goal: 1 hour of data max)

Disaster Recovery Site:

  • Geographically separate data center (earthquake in one city won't destroy both)

SQL Injection Attack (Common Threat)

Attack: Malicious SQL code injected through user input

Example (vulnerable code):

query = "SELECT * FROM Users WHERE Email='" + userInput + "'";
-- If userInput = "' OR '1'='1", the query becomes:
-- SELECT * FROM Users WHERE Email='' OR '1'='1';
-- This returns ALL users (1=1 is always true)!

Defense: Parameterized queries

-- Treated as data, not code
query = "SELECT * FROM Users WHERE Email=?";
query.bind(userInput);

Section 6: Big Data & Databases

The Scale Problem

Traditional Database Limits:

  • Single server can store ~10 TB
  • Queries on 1 billion rows are slow

Big Data Solutions:

  • Distributed Databases: Data split across many servers
  • MapReduce: Process data in parallel
  • Data Warehouses: Specialized for analytics (e.g., Amazon Redshift, Google BigQuery)
  • Data Lakes: Store raw data (structured and unstructured) for later analysis

Example: Netflix's database holds exabytes (1 million terabytes) of user watching data across thousands of servers globally.

Section 7: Database Market & Trends

Market Leaders (as of 2024):

  1. Oracle = Most widely used (dominant in enterprises)
  2. MySQL = Most popular open-source
  3. PostgreSQL = Gaining popularity (advanced features, free)
  4. MongoDB = Leading NoSQL (flexible, scalable)
  5. Microsoft SQL Server = Enterprise Windows shops

Emerging Trends:

  • Cloud Databases: MySQL, PostgreSQL hosted in cloud (AWS RDS, Google Cloud SQL)
  • Serverless Databases: Firebase, DynamoDB (automatic scaling, pay-per-use)
  • Time-Series Databases: InfluxDB, Prometheus (tracking metrics over time)
  • Graph Databases: Neo4j (storing relationships; popular for social networks)

Section 8: Data Backup & Business Continuity

Backup Strategies

Full Backup

  • Copy entire database
  • Slowest but simplest restoration

Incremental Backup

  • Back up only data changed since last backup
  • Faster backup; complex restoration (need full + all incrementals)

3-2-1 Backup Rule (Best Practice)

  • 3 copies of data (original + 2 backups)
  • 2 different storage types (disk + cloud)
  • 1 copy off-site (different geographic location)

Data Retention Policies

Why?

  • Legal compliance (financial records kept 7 years)
  • Storage cost (old data costs money to store)
  • Privacy (delete personal data when no longer needed—GDPR)

Example:

  • Keep 30 days of daily backups
  • Keep 12 months of monthly backups
  • Keep 5 years of annual backups

Exam Revision Checklist

Before exam, ensure you can:

  1. Define database and distinguish from spreadsheets
  2. Explain table structure: rows=records, columns=fields, primary key=unique identifier
  3. Distinguish relational (SQL) vs. non-relational (NoSQL) databases
  4. List 3 relational databases (MySQL, PostgreSQL, Oracle)
  5. List 2 NoSQL databases (MongoDB, Cassandra)
  6. Understand 5 SQL operations: CREATE, SELECT, INSERT, UPDATE, DELETE
  7. Explain foreign key and relationships
  8. Define normalization (eliminate redundancy)
  9. Understand SQL injection attack and parameterized queries
  10. Explain backup importance and 3-2-1 rule

MCQs (23 Questions)

1. A database is best defined as:

  • A) A spreadsheet file with formulas
  • B) An organized collection of structured data
  • C) A website for storing files
  • D) A text file with organized information

2. In a database, rows represent:

  • A) Fields or attributes
  • B) Individual records or entries
  • C) Tables in the database
  • D) Column headers

3. What is a primary key in a database?

  • A) A password to access the database
  • B) A unique identifier for each record in a table
  • C) The most important data column
  • D) A key used for encryption

4. A foreign key serves to:

  • A) Encrypt sensitive data
  • B) Create relationships between tables
  • C) Backup database
  • D) Delete old records

5. Which of the following is a relational database?

  • A) MongoDB
  • B) Redis
  • C) MySQL
  • D) Cassandra

6. SQL stands for:

  • A) Secure Query Language
  • B) System Query Language
  • C) Structured Query Language
  • D) Secure System Language

7. Which SQL operation retrieves data from a database?

  • A) INSERT
  • B) UPDATE
  • C) SELECT
  • D) DELETE

8. The INSERT statement is used to:

  • A) Modify existing records
  • B) Add new records to a table
  • C) Remove records from a table
  • D) Retrieve data from a table

9. The UPDATE statement modifies:

  • A) The table structure
  • B) Existing records in a table
  • C) The database name
  • D) The primary key

10. A WHERE clause in SQL is used to:

  • A) Define table structure
  • B) Filter records based on conditions
  • C) Delete entire tables
  • D) Create new databases

11. Which of the following is a NoSQL database?

  • A) MySQL
  • B) PostgreSQL
  • C) MongoDB
  • D) Oracle

12. NoSQL databases are preferred over relational for:

  • A) Structured financial data
  • B) Unstructured data and massive scalability
  • C) ACID compliance
  • D) Complex relationships

13. Normalization in databases is used to:

  • A) Increase database size
  • B) Make data unstructured
  • C) Eliminate data redundancy and inconsistency
  • D) Slow down queries

14. ACID compliance in databases ensures:

  • A) Automatic backup
  • B) Atomicity, Consistency, Isolation, Durability of transactions
  • C) Faster query execution
  • D) Lower storage costs

15. A JOIN operation in SQL:

  • A) Creates a new table
  • B) Combines data from multiple tables based on relationships
  • C) Deletes records from tables
  • D) Encrypts data

16. SQL injection is a security threat that:

  • A) Encrypts databases
  • B) Injects malicious SQL code through user input
  • C) Backs up databases automatically
  • D) Deletes database files

17. SQL injection can be prevented by using:

  • A) Firewalls only
  • B) Strong passwords
  • C) Parameterized queries
  • D) Antivirus software

18. A database administrator (DBA) is responsible for:

  • A) Writing web pages
  • B) Managing, backing up, and securing databases
  • C) Creating spreadsheets
  • D) Installing operating systems

19. The 3-2-1 backup rule recommends:

  • A) 3 backup servers
  • B) 3 copies of data, 2 storage types, 1 off-site
  • C) Daily, weekly, monthly backups only
  • D) Backing up every 3 hours, 2 times daily, 1 time yearly

20. Data at rest encryption protects:

  • A) Data being transmitted over network
  • B) Data stored on disk from unauthorized access
  • C) Passwords only
  • D) All network traffic

21. RTO (Recovery Time Objective) refers to:

  • A) How much data loss is acceptable
  • B) How long to restore service after a disaster
  • C) Time to back up database
  • D) Time to create new database

22. A composite primary key consists of:

  • A) Single column that uniquely identifies records
  • B) Multiple columns together that uniquely identify records
  • C) Backup key for security
  • D) Key used to encrypt data

23. When denormalization occurs in a database, it typically results in:

  • A) Faster queries but data redundancy
  • B) Slower queries but better security
  • C) Automatic backup
  • D) Complete loss of data

Answer Key: 1-B, 2-B, 3-B, 4-B, 5-C, 6-C, 7-C, 8-B, 9-B, 10-B, 11-C, 12-B, 13-C, 14-B, 15-B, 16-B, 17-C, 18-B, 19-B, 20-B, 21-B, 22-B, 23-A

Page 1 of 1
← Chapter 7TOC IndexChapter 9 →