SSC Computer Database PPT Slides Download (LEC #24)

We will share SSC Computer Database PPT Slides Download, Database is among the most important and most tested topics in the SSC Computer Awareness syllabus. Questions on DBMS, SQL commands, types of keys, normalization, ACID properties, and popular database software appear in SSC CGL, SSC CHSL, RRB NTPC, and virtually every competitive exam that tests computer knowledge. This article is based on the LEC #24 PPT slides from the Complete Foundation Batch and covers every concept you need – from what a database is to SQL command categories, keys, relationships, normalization, and a full set of practice MCQs.

Lecture Details

Parameter	Details
Lecture Number	LEC #24
Topic	Database
Subject	Computer Awareness – SSC Foundation Batch
Total Slides	30
File Size	5 MB
Exam Relevance	SSC CGL, SSC CHSL, SSC MTS, SSC CPO, RRB NTPC, RRB Group D, UPSSSC PET

SSC Computer Database PPT Slides Download (LEC #24)

NOTE: IF YOU WANT TO DOWNLOAD COMPLETE SSC SERIES (PPT SLIDES) – JUST VISIT THIS REDIRECT PAGE

What Is a Database?

A database is an organized collection of structured data that is stored electronically and can be accessed, managed, and updated efficiently. Rather than storing data in separate, disconnected files, a database stores related data together in a structured format – making it easy to search, retrieve, and modify.

Think of a database as a digital filing cabinet where every drawer (table) holds organized records that can be instantly located. Banks, hospitals, railways, e-commerce websites, and government agencies all rely on databases to manage their data.

Aspect	Details
Definition	An organized, structured collection of data stored electronically for easy access and management
Purpose	Store, retrieve, update, and manage large volumes of data efficiently
Managed By	DBMS – Database Management System
Basic Unit of Storage	Table (in relational databases) – made up of rows and columns
Query Language	SQL – Structured Query Language
Invented By	Edgar F. Codd (IBM) – proposed the Relational Database Model in 1970
First Commercial RDBMS	Oracle (1979) – developed by Larry Ellison based on Codd’s model
Real-World Examples	Railway reservation (IRCTC), bank transactions, hospital patient records, e-commerce orders

Data vs Information vs Database – Key Distinctions

Term	Definition	Example
Data	Raw, unprocessed facts and figures without context or meaning	9876543210, Rahul, 72
Information	Processed, organized data that carries meaning and is useful for decision-making	Rahul’s phone number is 9876543210 and he scored 72 marks
Database	A structured collection of related data organized for efficient storage and retrieval	A student database storing name, roll number, marks, and contact details of all students
Metadata	Data about data – describes the structure, format, and properties of data stored in a database	Column names, data types, table names, constraints
Record (Row)	A single complete entry in a table – represents one entity or instance	One student’s complete details: name, roll number, class, marks
Field (Column)	A single attribute or characteristic of the entity stored in the table	The ‘Name’ column or the ‘Marks’ column in a student table

Database Management System (DBMS)

A Database Management System (DBMS) is software that acts as an interface between the user and the database. It allows users to create, read, update, and delete data – collectively known as CRUD operations – while also managing security, concurrency, and data integrity.

DBMS Feature	Description
Data Storage	Stores data in a structured format on disk – typically in tables, files, or objects
Data Retrieval	Allows users to query and retrieve specific data using SQL or other query languages
Data Security	Controls who can access, modify, or delete data using authentication and authorization
Data Integrity	Enforces rules (constraints) to ensure data accuracy and consistency
Concurrency Control	Manages simultaneous access by multiple users without data corruption
Backup and Recovery	Provides mechanisms to back up data and restore it after a failure
Data Independence	Separates the physical storage of data from how it is logically viewed by users
Reduced Redundancy	Minimizes duplicate data by organizing it in a normalized structure

Traditional File System vs DBMS

Parameter	Traditional File System	DBMS
Data Storage	Data stored in separate, unrelated files	Data stored centrally in structured tables
Data Redundancy	High – same data duplicated across many files	Low – normalization eliminates unnecessary duplication
Data Consistency	Poor – inconsistencies arise when files are updated separately	High – a single update reflects everywhere
Data Security	Minimal – basic file permissions only	Strong – role-based access control, encryption
Data Sharing	Difficult – files not easily shared across applications	Easy – multiple users and applications can access data simultaneously
Query Support	No built-in query language; manual searching required	Full SQL query support for complex searches and joins
Crash Recovery	Manual and unreliable	Automatic recovery using transaction logs and backups
Data Independence	None – changing file structure requires rewriting all programs that use it	Full – logical and physical data independence

Types of Database Models

A database model defines how data is logically stored, organized, and accessed. Different models suit different types of applications.

Model	Description	Structure	Examples
Hierarchical Model	Data organized in a tree-like parent-child structure; each child has exactly one parent	Tree (parent-child)	IBM IMS, Windows Registry
Network Model	Extension of hierarchical model; a child can have multiple parents – forms a graph structure	Graph	IDMS (Integrated Database Management System)
Relational Model	Data organized in tables (relations) with rows and columns; tables linked via keys; most widely used model today	Tables with keys	MySQL, PostgreSQL, Oracle, MS SQL Server, SQLite
Object-Oriented Model	Data stored as objects (like in OOP); combines database capabilities with OOP features	Objects and classes	ObjectDB, db4o
NoSQL Model	Flexible schema; stores data as documents, key-value pairs, graphs, or wide columns; ideal for big data and unstructured data	Document/Key-Value/Graph/Column	MongoDB, Redis, Cassandra, Neo4j, CouchDB
NewSQL Model	Combines the ACID guarantees of relational databases with the scalability of NoSQL	Distributed relational	Google Spanner, CockroachDB, VoltDB

Relational Database – Tables, Rows, and Columns

The relational model, proposed by Edgar F. Codd in 1970, is the most widely used database model in the world today. All data is stored in tables called relations. Each table has a defined structure and data is linked between tables using keys.

Term	Also Called	Definition	Example
Table	Relation	A two-dimensional structure made of rows and columns that stores data about one entity type	A ‘Students’ table storing student records
Row	Tuple / Record	One complete set of data for a single entity instance in a table	One student’s full details – name, ID, marks
Column	Attribute / Field	A single property or characteristic of the entity; all rows share the same columns	The ‘Name’ column, the ‘Marks’ column
Cell	–	The intersection of one row and one column – stores a single data value	The marks of student ID 101
Schema	–	The structure or blueprint of the database – defines tables, columns, data types, and constraints	The design of the entire student database
Instance	–	The actual data stored in the database at a specific point in time	All the student records present in the database today

Types of Keys in a Relational Database

Keys are one of the most heavily tested database concepts in SSC and RRB exams. A key is an attribute or set of attributes used to uniquely identify rows in a table or establish relationships between tables.

Key Type	Definition	Example
Primary Key	A column (or set of columns) that uniquely identifies each row in a table; cannot be NULL and must be unique	Student_ID in a Students table – no two students share the same ID
Foreign Key	A column in one table that references the Primary Key of another table; used to establish a link between two tables	Course_ID in a Students table referencing Course_ID in a Courses table
Candidate Key	Any column or set of columns that could qualify as a Primary Key – uniquely identifies each row with no NULLs	Both Student_ID and Email could be candidate keys if both are unique
Primary Key	The candidate key that the database designer selects as the official unique identifier for the table	Designer chose Student_ID as the Primary Key from the candidate keys
Alternate Key	Any candidate key that was NOT chosen as the primary key	Email becomes the alternate key if Student_ID is the Primary Key
Super Key	Any set of attributes that can uniquely identify a row; includes the primary key and all its supersets	{Student_ID}, {Student_ID, Name}, {Student_ID, Email} are all super keys
Composite Key	A primary key made up of two or more columns combined to uniquely identify a row	Order_ID + Product_ID together form the primary key in an Order_Items table
Surrogate Key	An artificial key (usually auto-incremented integer) added by the system when no natural key exists	An auto-generated ID column (1, 2, 3…) added to uniquely identify rows

SQL – Structured Query Language

SQL (Structured Query Language) is the standard language used to communicate with relational databases. It was developed by IBM (Donald Chamberlin and Raymond Boyce) in the 1970s based on Edgar Codd’s relational model. SQL became an ANSI standard in 1986 and an ISO standard in 1987.

Aspect	Details
Full Form	Structured Query Language
Developed By	Donald Chamberlin and Raymond Boyce at IBM (1970s)
Based On	Edgar F. Codd’s Relational Model (1970)
Standardized By	ANSI (1986) and ISO (1987)
Type of Language	4GL – Fourth Generation Language; declarative in style
Pronunciation	Officially ‘S-Q-L’ (ess-que-ell); also commonly pronounced ‘sequel’
Used In	All major relational databases – MySQL, Oracle, PostgreSQL, SQL Server, SQLite
Case Sensitivity	SQL keywords are not case-sensitive (SELECT = select), but data values may be

SQL Command Categories – DDL, DML, DCL, TCL, DQL

SQL commands are grouped into five categories based on their function. This categorization is one of the most tested topics in SSC Computer Awareness.

Category	Full Form	Purpose	Key Commands
DDL	Data Definition Language	Defines and modifies the structure (schema) of database objects like tables and indexes	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML	Data Manipulation Language	Inserts, updates, and deletes actual data within tables	INSERT, UPDATE, DELETE, MERGE
DQL	Data Query Language	Retrieves data from tables based on specified conditions	SELECT
DCL	Data Control Language	Controls access permissions – who can do what in the database	GRANT, REVOKE
TCL	Transaction Control Language	Manages database transactions – groups of operations treated as one unit	COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

DDL Commands – Detail

Command	Function	Example
CREATE	Creates a new table, database, view, index, or other object	CREATE TABLE Students (ID INT, Name VARCHAR(50));
ALTER	Modifies an existing table structure – adds, drops, or changes columns	ALTER TABLE Students ADD Email VARCHAR(100);
DROP	Permanently deletes a table, database, or other object along with all its data	DROP TABLE Students;
TRUNCATE	Removes all rows from a table but keeps the table structure intact	TRUNCATE TABLE Students;
RENAME	Renames an existing database object	RENAME TABLE Students TO Learners;

DML Commands – Detail

Command	Function	Example
INSERT	Adds new rows of data into a table	INSERT INTO Students VALUES (101, ‘Rahul’, 85);
UPDATE	Modifies existing data in one or more rows of a table	UPDATE Students SET Marks = 90 WHERE ID = 101;
DELETE	Removes specific rows from a table based on a condition	DELETE FROM Students WHERE ID = 101;

Key Difference – DROP vs TRUNCATE vs DELETE

Parameter	DROP	TRUNCATE	DELETE
What it removes	Entire table (structure + data)	All rows but keeps table structure	Specific rows based on a WHERE condition
Can be rolled back?	No – DDL command, auto-commits	No – DDL command, auto-commits	Yes – DML command, can be rolled back
Speed	Fast	Very Fast	Slower (row by row)
WHERE clause	Not applicable	Not applicable	Supported

Types of Relationships in a Database

Relationships define how data in one table is connected to data in another table. Understanding cardinality (the number of related records) is important for both exam questions and database design.

Relationship Type	Definition	Real-World Example	Implementation
One-to-One (1:1)	Each record in Table A is related to exactly one record in Table B, and vice versa	One person has one passport; one passport belongs to one person	Foreign key in either table with a UNIQUE constraint
One-to-Many (1:N)	One record in Table A is related to many records in Table B, but each record in Table B relates to only one record in Table A	One teacher teaches many students; each student has only one class teacher	Foreign key in the ‘many’ side table (most common relationship type)
Many-to-Many (M:N)	Many records in Table A relate to many records in Table B	Many students enroll in many courses; many courses have many students	Resolved using a junction/bridge table (e.g., Student_Course table with Student_ID and Course_ID)

Normalization – 1NF, 2NF, 3NF, and BCNF

Normalization is the process of organizing a database to reduce data redundancy and improve data integrity. It involves decomposing tables into smaller, well-structured tables without losing information. Each step is called a Normal Form (NF).

Normal Form	Full Name	Rule / Condition	Problem It Solves
1NF	First Normal Form	Every column must contain atomic (indivisible) values; no repeating groups or arrays in any column; each row must be unique	Eliminates multi-valued attributes and repeating groups
2NF	Second Normal Form	Must be in 1NF; every non-key column must depend on the entire primary key (no partial dependency) – applies only when primary key is composite	Eliminates partial dependencies on composite primary keys
3NF	Third Normal Form	Must be in 2NF; no non-key column should depend on another non-key column (no transitive dependency)	Eliminates transitive dependencies between non-key attributes
BCNF	Boyce-Codd Normal Form	A stricter version of 3NF; for every functional dependency A → B, A must be a super key	Resolves anomalies that 3NF may not fully address in certain edge cases

Exam tip: The most commonly tested normal forms in SSC are 1NF, 2NF, and 3NF. Remember: 1NF = atomic values; 2NF = no partial dependency; 3NF = no transitive dependency.

ACID Properties of Database Transactions

A transaction is a sequence of database operations (like a bank transfer) that must be executed as a single logical unit. ACID is the set of four properties that guarantee database transactions are processed reliably. ACID questions are frequently tested in SSC exams.

Property	Full Meaning	Definition	Real-World Example
A	Atomicity	A transaction is treated as a single unit – either all operations succeed completely, or none of them take effect at all	In a bank transfer of Rs. 5000 from Account A to B: both the debit from A and the credit to B must succeed. If the credit fails, the debit is also reversed.
C	Consistency	A transaction brings the database from one valid state to another valid state; all data integrity rules and constraints must be satisfied before and after the transaction	After the bank transfer, the total money across both accounts must remain the same as before the transaction.
I	Isolation	Transactions execute independently of each other; intermediate states of a transaction are invisible to other concurrent transactions	If two people transfer money simultaneously, each transaction proceeds as if it is the only one running, without interfering with the other.
D	Durability	Once a transaction is committed (completed successfully), its changes are permanently saved to the database – even if the system crashes immediately afterward	After the bank transfer is confirmed, a power outage should not reverse the transaction; the records are safely written to disk.

Entity-Relationship (ER) Diagram

An Entity-Relationship (ER) diagram is a visual blueprint of a database. It shows the entities (objects) in a system, their attributes, and how they relate to each other. ER diagrams are drawn before creating the actual database to plan its structure.

ER Diagram Component	Symbol Used	Definition	Example
Entity	Rectangle	A real-world object or concept about which data is stored	Student, Teacher, Course, Book
Attribute	Ellipse (Oval)	A property or characteristic of an entity	Student Name, Roll Number, Date of Birth
Key Attribute	Ellipse with underlined text	An attribute that uniquely identifies an entity – used to define the primary key	Roll Number (underlined in the ER diagram)
Multi-valued Attribute	Double Ellipse	An attribute that can hold more than one value for a single entity	Phone Numbers (a student may have multiple numbers)
Derived Attribute	Dashed Ellipse	An attribute whose value can be calculated from other attributes	Age (derived from Date of Birth)
Relationship	Diamond	Describes the association between two or more entities	Student ENROLLS IN Course
Weak Entity	Double Rectangle	An entity that cannot be uniquely identified by its own attributes alone; depends on a strong entity	Dependent (depends on Employee entity for identification)

Database Indexes

An index is a data structure that improves the speed of data retrieval operations on a table. It works like an index at the back of a book – instead of reading every page to find a topic, you go directly to the right page. Indexes speed up SELECT queries but slow down INSERT, UPDATE, and DELETE operations slightly because the index also needs to be updated.

Index Type	Description	Best Used For
Primary Index	Automatically created on the Primary Key column; ensures fast access to any row by its primary key	All tables – created automatically
Unique Index	Ensures all values in the indexed column are unique; prevents duplicate entries	Email columns, username columns, national ID numbers
Clustered Index	Determines the physical order in which rows are stored on disk; only one per table possible	Columns that are frequently used in range queries (e.g., dates, IDs)
Non-Clustered Index	A separate structure that points to the physical data rows; multiple allowed per table	Columns frequently used in WHERE clauses and JOIN conditions
Composite Index	An index built on two or more columns combined	Queries that filter on multiple columns simultaneously
Full-Text Index	Enables fast text search across large text columns	Search engines, blog platforms, document databases

Views, Stored Procedures, and Triggers

Object	Definition	Key Feature	Example Use Case
View	A virtual table created by a stored SELECT query; does not store data itself – it retrieves data from underlying tables each time it is queried	Simplifies complex queries; restricts access to specific columns or rows	A ‘PublicStudentView’ that shows only Name and Department, hiding sensitive data like marks or contact details
Stored Procedure	A pre-written, named, and saved SQL program stored in the database that can be executed on demand by name	Reduces repetitive SQL writing; improves performance through pre-compilation; supports parameters	A procedure called ‘GetStudentReport’ that automatically compiles a student’s complete academic record when called
Trigger	A set of SQL statements that automatically executes (fires) in response to a specific event (INSERT, UPDATE, or DELETE) on a table	Automatic – no manual execution needed; used to enforce business rules and maintain audit trails	A trigger that automatically logs the time and username whenever a salary record is updated in an HR database
Function (UDF)	A user-defined reusable block of SQL code that returns a single value or a table; must return a value	Reusable logic; can be embedded directly in SQL queries	A function called ‘CalculateTax’ that takes a salary as input and returns the calculated tax amount

Popular DBMS Software – Quick Reference

DBMS Software	Type	Developer	Key Use Case
MySQL	Relational (RDBMS)	Oracle Corporation (originally MySQL AB)	Web applications, open-source projects; used by Facebook, Twitter, YouTube
Oracle Database	Relational (RDBMS)	Oracle Corporation (Larry Ellison, 1979)	Enterprise applications, banking, ERP systems; first commercial RDBMS
Microsoft SQL Server	Relational (RDBMS)	Microsoft Corporation	Windows-based enterprise applications, .NET ecosystem
PostgreSQL	Relational (ORDBMS)	PostgreSQL Global Development Group (open source)	Advanced queries, geospatial data, open-source enterprise use
SQLite	Relational (RDBMS)	D. Richard Hipp (open source)	Mobile apps (Android, iOS), embedded systems, local storage
MongoDB	NoSQL (Document)	MongoDB Inc.	Big data, real-time web apps, content management systems
Redis	NoSQL (Key-Value)	Redis Labs (open source)	Caching, session management, real-time leaderboards
Cassandra	NoSQL (Wide Column)	Apache Software Foundation (originally Facebook)	High-write workloads, IoT data, time-series data at massive scale
Neo4j	NoSQL (Graph)	Neo4j Inc.	Social networks, recommendation engines, fraud detection
IBM Db2	Relational (RDBMS)	IBM Corporation	Mainframe systems, large enterprise data warehouses

SQL vs NoSQL Databases

Parameter	SQL (Relational)	NoSQL (Non-Relational)
Data Structure	Tables with rows and columns	Documents, key-value pairs, graphs, or wide columns
Schema	Fixed, predefined schema	Flexible or schema-less
Query Language	SQL – standardized and universal	Varies by database (no single standard)
Scalability	Vertical scaling (adding more power to one server)	Horizontal scaling (adding more servers – sharding)
ACID Compliance	Full ACID compliance	Often trades ACID for performance (BASE model)
Relationships	Handles complex joins and relationships natively	Relationships less natural; often handled in application code
Best For	Structured data with complex queries – banking, ERP, HR systems	Unstructured/semi-structured data at scale – social media, IoT, big data
Examples	MySQL, Oracle, PostgreSQL, SQL Server	MongoDB, Redis, Cassandra, Neo4j, CouchDB

Concurrency Control and Database Locks

When multiple users access and modify a database simultaneously, concurrency control mechanisms ensure that transactions do not interfere with each other and data integrity is maintained.

Concept	Definition
Concurrency	The ability of a database to allow multiple users or transactions to access and modify data at the same time
Locking	A mechanism that prevents two transactions from modifying the same data simultaneously; a transaction acquires a lock before modifying data
Shared Lock (Read Lock)	Multiple transactions can read the same data simultaneously; no transaction can write while a shared lock is active
Exclusive Lock (Write Lock)	Only one transaction can hold this lock; no other transaction can read or write the locked data until the lock is released
Deadlock	A situation where two or more transactions are waiting for each other to release locks, causing all of them to be stuck indefinitely
Deadlock Resolution	The DBMS detects the deadlock and aborts (rolls back) one of the transactions to break the cycle

Database Backup and Recovery

Type	Description	When Used
Full Backup	A complete copy of the entire database at a specific point in time	Weekly or monthly; baseline for all other backup types
Incremental Backup	Backs up only the data that has changed since the last backup (full or incremental)	Daily backups to minimize storage and backup time
Differential Backup	Backs up all data changed since the last full backup only	When faster recovery than incremental is needed
Transaction Log Backup	Backs up the transaction log – allows point-in-time recovery	Frequent backups (hourly) for critical systems
Recovery	The process of restoring the database to a consistent state after a failure using backups and transaction logs	After hardware failure, data corruption, or accidental deletion

Database Abbreviations – Quick Reference

Abbreviation	Full Form
DB	Database
DBMS	Database Management System
RDBMS	Relational Database Management System
SQL	Structured Query Language
NoSQL	Not Only SQL (non-relational databases)
DDL	Data Definition Language
DML	Data Manipulation Language
DQL	Data Query Language
DCL	Data Control Language
TCL	Transaction Control Language
ACID	Atomicity, Consistency, Isolation, Durability
ER	Entity-Relationship (as in ER Diagram)
1NF / 2NF / 3NF	First / Second / Third Normal Form
BCNF	Boyce-Codd Normal Form
PK	Primary Key
FK	Foreign Key
UDF	User-Defined Function
DBA	Database Administrator
CRUD	Create, Read, Update, Delete
OLTP	Online Transaction Processing
OLAP	Online Analytical Processing
ETL	Extract, Transform, Load
ANSI	American National Standards Institute
ISO	International Organization for Standardization
JVM	Java Virtual Machine (used in Java-based DB tools)
ORM	Object-Relational Mapping

Key Points to Remember Before Your Exam

• A database is an organized, structured collection of data managed by a DBMS (Database Management System).
• Edgar F. Codd (IBM) proposed the Relational Database Model in 1970 – he is the father of relational databases.
• Oracle Database (1979) was the first commercially available RDBMS, created by Larry Ellison.
• SQL (Structured Query Language) was developed by Donald Chamberlin and Raymond Boyce at IBM; became ANSI standard in 1986.
• SQL is a 4GL (Fourth Generation Language) – declarative in style (tells the database what to get, not how).
• The five SQL command categories: DDL (Define structure), DML (Manipulate data), DQL (Query/SELECT), DCL (Access control), TCL (Transaction control).
• DROP removes the entire table permanently; TRUNCATE removes all rows but keeps the structure; DELETE removes specific rows and can be rolled back.
• Primary Key: uniquely identifies each row; cannot be NULL. Foreign Key: links two tables together.
• Normalization levels: 1NF (atomic values), 2NF (no partial dependency), 3NF (no transitive dependency), BCNF (every determinant is a super key).
• ACID properties of transactions: Atomicity (all or nothing), Consistency (valid state before and after), Isolation (independent execution), Durability (permanent after commit).
• An ER Diagram uses rectangles (entities), ellipses (attributes), and diamonds (relationships) to design a database visually.
• Types of relationships: One-to-One (1:1), One-to-Many (1:N), Many-to-Many (M:N – resolved using a junction table).
• A View is a virtual table; it does not store data – it runs a stored query each time it is accessed.
• A Trigger fires automatically in response to INSERT, UPDATE, or DELETE events on a table.
• A Stored Procedure is a pre-saved SQL program that runs on demand and accepts parameters.
• SQL vs NoSQL: SQL uses fixed-schema tables and SQL queries; NoSQL uses flexible schemas and scales horizontally.
• Deadlock occurs when two transactions wait on each other forever; the DBMS resolves it by rolling back one transaction.
• OLTP (Online Transaction Processing) handles daily transactional operations; OLAP (Online Analytical Processing) handles complex analytical queries on historical data.
• Popular RDBMS: MySQL, Oracle, PostgreSQL, MS SQL Server, SQLite. Popular NoSQL: MongoDB, Redis, Cassandra, Neo4j.
• DBA stands for Database Administrator – the person responsible for managing, securing, and maintaining a database system.

READ ALSO: SSC Computer Class Programming Language PPT Slides (LEC #23)

FAQ:

What is the difference between DBMS and RDBMS?

A DBMS (Database Management System) is software that manages any organized collection of data. It may store data in files, hierarchies, networks, or tables. An RDBMS (Relational Database Management System) is a specific type of DBMS that stores data exclusively in tables (relations) and uses SQL. All RDBMS are DBMS, but not all DBMS are RDBMS. Examples of RDBMS: MySQL, Oracle, PostgreSQL, SQL Server. Examples of non-relational DBMS (NoSQL): MongoDB, Redis, Cassandra.

What is the difference between DDL, DML, and DCL in SQL?

DDL (Data Definition Language) deals with the structure of the database – commands like CREATE, ALTER, DROP, and TRUNCATE define and modify tables and schemas. DML (Data Manipulation Language) deals with the actual data inside tables – commands like INSERT, UPDATE, and DELETE add, change, or remove data rows. DCL (Data Control Language) deals with permissions and access – GRANT gives a user permission to perform actions, and REVOKE removes those permissions. A helpful way to remember: DDL = Structure, DML = Data, DCL = Access.

What are ACID properties and why are they important?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These four properties ensure that database transactions are processed reliably. Atomicity means a transaction either completes fully or not at all – no partial updates. Consistency means the database moves from one valid state to another, satisfying all constraints. Isolation means concurrent transactions do not interfere with each other. Durability means once a transaction is committed, its changes are permanent even if the system crashes. ACID properties are critical in systems like banking, railways, and healthcare where data accuracy and reliability are non-negotiable.

What is normalization and why is it important?

Normalization is the process of organizing database tables to minimize data redundancy and improve data integrity. It works by decomposing large, poorly designed tables into smaller, well-structured tables. The main normal forms are 1NF (every column must hold atomic, single values – no lists or repeating groups), 2NF (all non-key columns must depend on the entire primary key – no partial dependencies), and 3NF (no non-key column should depend on another non-key column – no transitive dependencies). BCNF is a stricter extension of 3NF. Normalization prevents update anomalies (changing data in one place leaves inconsistencies elsewhere) and insertion anomalies (unable to add data without adding unrelated data).

What is the difference between a Primary Key and a Foreign Key?

A Primary Key is a column or set of columns in a table that uniquely identifies every row in that table. It cannot contain NULL values and must be unique across all rows. Each table can have only one Primary Key. A Foreign Key is a column in one table that references (points to) the Primary Key of another table. It establishes a link between the two tables and enforces referential integrity – ensuring that a value in the Foreign Key column always corresponds to a valid value in the referenced Primary Key. For example, in a Students table, Student_ID is the Primary Key. In an Enrollment table, Student_ID appears as a Foreign Key referencing the Students table.

What is the difference between DROP, TRUNCATE, and DELETE?

DROP removes the entire table – its structure, data, indexes, and constraints are all permanently deleted. You cannot roll back a DROP. TRUNCATE removes all rows from a table but keeps the table structure (columns, constraints, indexes) intact. TRUNCATE is faster than DELETE because it does not log individual row deletions. It also cannot be rolled back (it is a DDL command). DELETE removes specific rows that match a WHERE condition. If no WHERE clause is given, it removes all rows like TRUNCATE, but much more slowly. DELETE is a DML command and can be rolled back within a transaction. Summary: DROP = kill the table; TRUNCATE = empty the table (keep structure, fast, irreversible); DELETE = remove specific rows (slow, reversible).

What is the difference between SQL and NoSQL databases?

SQL databases (relational) store data in structured tables with a fixed schema. They use SQL as the query language, support complex joins and relationships, and provide full ACID compliance. They scale vertically (by upgrading hardware). Examples: MySQL, Oracle, PostgreSQL, SQL Server. NoSQL databases (non-relational) store data in flexible formats – documents (MongoDB), key-value pairs (Redis), wide columns (Cassandra), or graphs (Neo4j). They have flexible or schema-less structures, typically trade ACID compliance for speed and horizontal scalability (adding more servers). They are ideal for big data, real-time apps, and unstructured data. The choice depends on the use case: structured transactional data suits SQL; large-scale, flexible, or unstructured data suits NoSQL.

What is an ER Diagram and what are its components?

An Entity-Relationship (ER) Diagram is a graphical representation of a database’s design – it shows entities, their attributes, and the relationships between them before the database is actually built. The main components are: Rectangle – represents an Entity (a real-world object like Student or Course). Ellipse/Oval – represents an Attribute (a property of the entity, like Name or Age). Underlined ellipse – represents a Key Attribute (uniquely identifies the entity, like Roll Number). Double ellipse – represents a Multi-valued Attribute (can hold multiple values, like Phone Numbers). Dashed ellipse – represents a Derived Attribute (calculated from other attributes, like Age from Date of Birth). Diamond – represents a Relationship between entities (like ENROLLS IN between Student and Course). Double rectangle – represents a Weak Entity (cannot be identified without a related strong entity).

Practice MCQs – Database

1. Who proposed the Relational Database Model and in which year?

A) Donald Knuth, 1968

B) Edgar F. Codd, 1970

C) Larry Ellison, 1979

D) Grace Hopper, 1965

Answer: B – Edgar F. Codd of IBM proposed the Relational Model in 1970.

2. SQL became an ANSI standard in which year?

A) 1970

B) 1979

C) 1986

D) 1992

Answer: C – SQL became an ANSI (American National Standards Institute) standard in 1986.

3. Which SQL command is used to remove all rows from a table but keep its structure intact?

A) DELETE

B) DROP

C) REMOVE

D) TRUNCATE

Answer: D – TRUNCATE removes all rows but preserves the table structure; it cannot be rolled back.

4. Which SQL command category does GRANT and REVOKE belong to?

A) DDL

B) DML

C) DCL

D) TCL

Answer: C – GRANT and REVOKE are DCL (Data Control Language) commands that manage access permissions.

5. In database keys, which key uniquely identifies each row in a table and cannot contain NULL values?

A) Foreign Key

B) Alternate Key

C) Super Key

D) Primary Key

Answer: D – A Primary Key uniquely identifies every row and cannot be NULL.

6. Which normal form requires that every non-key attribute must depend on the entire primary key (eliminating partial dependencies)?

A) 1NF

B) 2NF

C) 3NF

D) BCNF

Answer: B – 2NF (Second Normal Form) eliminates partial dependencies on composite primary keys.

7. ACID stands for:

A) Atomicity, Concurrency, Isolation, Durability

B) Atomicity, Consistency, Isolation, Durability

C) Availability, Consistency, Integrity, Durability

D) Atomicity, Consistency, Integrity, Distribution

Answer: B – ACID stands for Atomicity, Consistency, Isolation, Durability.

8. A Foreign Key in a database table is used to:

A) Uniquely identify rows in the same table

B) Create an index for faster search

C) Establish a link between two tables by referencing another table’s Primary Key

D) Define the schema of the table

Answer: C – A Foreign Key references the Primary Key of another table to create a relationship.

9. Which SQL command is used to retrieve data from a database?

A) FETCH

B) GET

C) SELECT

D) RETRIEVE

Answer: C – SELECT is the core DQL (Data Query Language) command used to retrieve data.

10. In an ER Diagram, which symbol represents an entity?

A) Ellipse

B) Diamond

C) Rectangle

D) Circle

Answer: C – Rectangles represent entities; ellipses represent attributes; diamonds represent relationships.

11. Which DBMS was the first commercially available relational database system?

A) MySQL

B) IBM Db2

C) Microsoft SQL Server

D) Oracle

Answer: D – Oracle (1979) was the first commercially available RDBMS, created by Larry Ellison.

12. A deadlock in a database occurs when:

A) A query takes too long to execute

B) Two transactions each wait for the other to release a lock, causing both to be stuck

C) The database runs out of storage space

D) A user tries to access a locked table directly

Answer: B – Deadlock is a circular wait condition where transactions block each other indefinitely.

13. Which type of NoSQL database is best suited for social network applications that model highly connected data?

A) Document database (MongoDB)

B) Key-Value database (Redis)

C) Graph database (Neo4j)

D) Wide Column database (Cassandra)

Answer: C – Graph databases like Neo4j are designed for highly connected data like social networks and recommendation engines.

14. Which of the following is NOT a DDL command?

A) CREATE

B) ALTER

C) INSERT

D) DROP

Answer: C – INSERT is a DML (Data Manipulation Language) command. CREATE, ALTER, and DROP are DDL.

15. OLTP stands for:

A) Online Transfer and Loading Protocol

B) Online Transaction Processing

C) Offline Transaction and Logging Platform

D) Online Table and Link Protocol

Answer: B – OLTP (Online Transaction Processing) handles day-to-day transactional database operations like order entry, banking, and reservations.