Database Management System (DBMS) is an important topic for Bihar STET Computer Science preparation. This topic covers database concepts, relational models, keys, SQL, normalization, transactions, concurrency control and database security.
DBMS – Introduction
A Database Management System (DBMS) is software used to create, store, organize, retrieve and manage data in a database.
Examples of DBMS include:
MySQL
PostgreSQL
Oracle Database
Microsoft SQL Server
SQLite
Advantages of DBMS
Reduces data redundancy
Improves data consistency
Provides data security
Supports data sharing
Provides backup and recovery
Supports concurrent access
Provides data independence
Database vs DBMS
A database is an organized collection of data, while a DBMS is software used to manage that data.
Types of Database Models
1. Hierarchical Model
Data is organized in a tree-like structure consisting of parent-child relationships.
2. Network Model
Data can have multiple relationships and is represented using a graph-like structure.
3. Relational Model
Data is stored in tables consisting of rows and columns.
The relational model is widely used in modern database systems.
4. Object-Oriented Model
Data is represented using objects, classes and their relationships.
Important DBMS Terminology
Table
A table is a collection of related data organized into rows and columns.
Row / Tuple
A row represents a single record in a relational table.
Column / Attribute
A column represents a property or characteristic of an entity.
Domain
A domain defines the set of permissible values for an attribute.
Schema
A database schema describes the logical structure of a database.
Instance
The actual data stored in a database at a particular point in time is called a database instance.
Keys in DBMS
Keys are used to identify records and establish relationships between tables.
Primary Key
A primary key uniquely identifies each record in a table.
Properties:
Must be unique
Cannot contain NULL values
Only one primary key constraint can be defined for a table, although it may contain multiple columns.
Candidate Key
A candidate key is an attribute or set of attributes that can uniquely identify a record and can potentially be selected as the primary key.
Alternate Key
Candidate keys that are not selected as the primary key are called alternate keys.
Foreign Key
A foreign key is an attribute or set of attributes that references a key in another table, commonly the primary key.
Composite Key
A key consisting of two or more attributes is called a composite key.
Super Key
A set of one or more attributes that can uniquely identify a tuple is called a super key.
ER Model
The Entity-Relationship (ER) model is used to represent the structure and relationships of data.
Entity
An entity is a real-world object about which information is stored.
Examples:
Student
Teacher
Course
Employee
Attribute
An attribute describes an entity.
For example, Student may have:
Student_ID
Name
Address
Course
Relationship
A relationship represents an association between entities.
Examples:
Student enrolls in Course
Employee works for Department
Cardinality
Cardinality describes the relationship between entities.
Common types are:
One-to-One (1:1)
One-to-Many (1:N)
Many-to-One (N:1)
Many-to-Many (M:N)
SQL – Structured Query Language
SQL is used to create, retrieve, modify and manage data in relational databases.
SQL commands are commonly divided into several categories.
DDL – Data Definition Language
DDL is used to define database structures.
Common commands:
CREATE
ALTER
DROP
TRUNCATE
Example:
CREATE TABLE Student (
id INT PRIMARY KEY,
name VARCHAR(100)
);
DML – Data Manipulation Language
DML is used to insert, update and delete data.
Common commands:
INSERT
UPDATE
DELETE
Example:
INSERT INTO Student (id, name)
VALUES (1, 'Rahul');
DQL – Data Query Language
The primary command associated with querying data is:
SELECT
Example:
SELECT * FROM Student;
DCL – Data Control Language
DCL is used to control database access.
Common commands:
GRANT
REVOKE
TCL – Transaction Control Language
TCL manages database transactions.
Common commands:
COMMIT
ROLLBACK
SAVEPOINT
SQL Constraints
Constraints are rules applied to columns to maintain data integrity.
Important constraints include:
PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
DEFAULT
NOT NULL
Prevents a column from storing NULL values.
UNIQUE
Ensures that values in a column are unique.
CHECK
Restricts values according to a specified condition.
DEFAULT
Provides a default value when no value is specified.
SQL Joins
Joins are used to retrieve related data from multiple tables.
INNER JOIN
Returns matching records from both tables.
LEFT JOIN
Returns all records from the left table and matching records from the right table.
RIGHT JOIN
Returns all records from the right table and matching records from the left table.
FULL OUTER JOIN
Returns matching and non-matching records from both tables.
CROSS JOIN
Produces the Cartesian product of two tables.
Normalization
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
First Normal Form – 1NF
A table is in 1NF when:
Each field contains atomic values.
Repeating groups are eliminated.
Second Normal Form – 2NF
A relation is in 2NF when:
It is in 1NF.
It has no partial dependency on a candidate key.
Third Normal Form – 3NF
A relation is in 3NF when:
It is in 2NF.
It has no transitive dependency of non-key attributes on a candidate key.
BCNF
Boyce-Codd Normal Form is a stronger version of 3NF.
A relation is in BCNF when every determinant is a candidate key.
Functional Dependency
A functional dependency describes a relationship between attributes.
If attribute A determines B, it is represented as:
A → B
Here, A is called the determinant and B is functionally dependent on A.
Transaction in DBMS
A transaction is a logical unit of database operations.
For example, transferring money from one bank account to another may involve multiple operations that should be treated as a single transaction.
ACID Properties
ACID stands for:
A – Atomicity
A transaction is completed completely or not performed at all.
C – Consistency
A transaction takes the database from one valid state to another valid state.
I – Isolation
Concurrent transactions should not improperly interfere with each other.
D – Durability
Once a transaction is committed, its changes should persist even after a system failure.
Concurrency Control
Concurrency control manages simultaneous transactions while maintaining database consistency.
Important concepts include:
Locks
Two-phase locking
Serializability
Timestamp ordering
Deadlock
Shared Lock
A shared lock generally allows multiple transactions to read a data item simultaneously.
Exclusive Lock
An exclusive lock is used when a transaction needs to modify a data item.
Database Security
Database security protects data against unauthorized access and misuse.
Important methods include:
Authentication
Authorization
Access control
Encryption
Backup
Auditing
Data Independence
Data independence means changes at one level of the database architecture do not necessarily require changes at higher levels.
Physical Data Independence
Changes to the physical storage structure do not affect the logical schema.
Logical Data Independence
Changes to the logical schema do not require changes to external views or application programs.
Three-Level Database Architecture
The three levels are:
External Level – User views
Conceptual Level – Overall logical database structure
Internal Level – Physical storage details
Bihar STET DBMS Important MCQs 2026
1. What does DBMS stand for?
A. Data Backup Management System
B. Database Management System
C. Database Machine System
D. Data Management Service
Answer: B. Database Management System
2. Which key uniquely identifies a record?
A. Foreign Key
B. Primary Key
C. Alternate Key
D. Composite Key
Answer: B. Primary Key
3. Which key establishes a relationship between tables?
A. Foreign Key
B. Super Key
C. Candidate Key
D. Alternate Key
Answer: A. Foreign Key
4. A row in a relational database is called:
A. Attribute
B. Domain
C. Tuple
D. Schema
Answer: C. Tuple
5. A column in a relational table is called:
A. Tuple
B. Attribute
C. Record
D. Entity
Answer: B. Attribute
6. Which SQL command is used to retrieve data?
A. INSERT
B. UPDATE
C. SELECT
D. DELETE
Answer: C. SELECT
7. Which of the following is a DDL command?
A. INSERT
B. UPDATE
C. CREATE
D. SELECT
Answer: C. CREATE
8. Which command removes a table structure from a database?
A. DELETE
B. DROP
C. UPDATE
D. SELECT
Answer: B. DROP
9. Which command removes all rows while retaining the table structure?
A. DROP
B. DELETE DATABASE
C. TRUNCATE
D. REMOVE
Answer: C. TRUNCATE
10. Which SQL command is used to modify existing records?
A. UPDATE
B. CREATE
C. ALTER
D. SELECT
Answer: A. UPDATE
11. Which command is used to give privileges to a database user?
A. REVOKE
B. GRANT
C. COMMIT
D. SAVEPOINT
Answer: B. GRANT
12. Which command removes privileges from a user?
A. DELETE
B. REMOVE
C. REVOKE
D. DROP
Answer: C. REVOKE
13. Which SQL command permanently saves a transaction?
A. SAVE
B. COMMIT
C. CHECKPOINT
D. STORE
Answer: B. COMMIT
14. Which command is used to undo changes made during a transaction?
A. ROLLBACK
B. DELETE
C. REVERSE
D. UNDO TABLE
Answer: A. ROLLBACK
15. Which normal form eliminates repeating groups and ensures atomic values?
A. 1NF
B. 2NF
C. 3NF
D. BCNF
Answer: A. 1NF
16. Which normal form removes partial dependency?
A. 1NF
B. 2NF
C. 3NF
D. BCNF
Answer: B. 2NF
17. Which normal form removes transitive dependency?
A. 1NF
B. 2NF
C. 3NF
D. 4NF
Answer: C. 3NF
18. Which is a stronger form of 3NF?
A. 1NF
B. 2NF
C. BCNF
D. 5NF
Answer: C. BCNF
19. Which of the following is NOT an ACID property?
A. Atomicity
B. Consistency
C. Isolation
D. Availability
Answer: D. Availability
20. In ACID, D stands for:
A. Dependency
B. Durability
C. Database
D. Distribution
Answer: B. Durability
21. Which join returns only matching records from both tables?
A. LEFT JOIN
B. RIGHT JOIN
C. INNER JOIN
D. CROSS JOIN
Answer: C. INNER JOIN
22. Which join produces the Cartesian product?
A. INNER JOIN
B. CROSS JOIN
C. LEFT JOIN
D. FULL JOIN
Answer: B. CROSS JOIN
23. Which constraint prevents NULL values?
A. UNIQUE
B. CHECK
C. NOT NULL
D. DEFAULT
Answer: C. NOT NULL
24. Which constraint ensures that values are unique?
A. UNIQUE
B. CHECK
C. DEFAULT
D. NULL
Answer: A. UNIQUE
25. Which SQL clause is used to filter rows?
A. ORDER BY
B. WHERE
C. GROUP BY
D. SELECT
Answer: B. WHERE
26. Which clause is used to sort query results?
A. SORT BY
B. ORDER BY
C. GROUP BY
D. ARRANGE BY
Answer: B. ORDER BY
27. Which clause is used to group rows having the same values?
A. GROUP BY
B. ORDER BY
C. WHERE
D. HAVING ONLY
Answer: A. GROUP BY
28. Which clause is generally used to filter grouped results?
A. WHERE
B. HAVING
C. ORDER BY
D. SELECT
Answer: B. HAVING
29. Which of the following represents a functional dependency?
A. A + B
B. A → B
C. A = B only
D. A × B
Answer: B. A → B
30. Which database level deals with physical storage?
A. External Level
B. Conceptual Level
C. Internal Level
D. View Level
Answer: C. Internal Level
Quick Revision – DBMS One-Liners
DBMS → Software for managing databases.
Table → Collection of rows and columns.
Tuple → Row/record.
Attribute → Column/property.
Primary Key → Uniquely identifies records.
Foreign Key → Establishes a reference between tables.
Candidate Key → Potential primary key.
Super Key → Attribute set that uniquely identifies a tuple.
DDL → CREATE, ALTER, DROP, TRUNCATE.
DML → INSERT, UPDATE, DELETE.
DQL → SELECT.
DCL → GRANT, REVOKE.
TCL → COMMIT, ROLLBACK, SAVEPOINT.
1NF → Atomic values.
2NF → No partial dependency.
3NF → No transitive dependency.
BCNF → Stronger form of 3NF.
ACID → Atomicity, Consistency, Isolation, Durability.
INNER JOIN → Matching rows from both tables.
WHERE → Filters rows.
HAVING → Filters groups.
ORDER BY → Sorts results.
GROUP BY → Groups rows.
COMMIT → Makes transaction changes permanent.
ROLLBACK → Reverts uncommitted transaction changes.
Bihar STET DBMS Preparation Tips
For Bihar STET Computer Science, give special attention to DBMS fundamentals, relational model, keys, ER model, SQL commands, joins, normalization, functional dependencies, transactions, ACID properties, concurrency control and database architecture.
Practice both conceptual MCQs and SQL-based questions because questions can test the difference between commands such as DELETE, DROP and TRUNCATE, as well as keys, normalization and transaction concepts.
Most Important DBMS Topics
DBMS Fundamentals
Database Models
Relational Model
Keys
ER Model
SQL
DDL, DML, DCL, DQL and TCL
SQL Constraints
SQL Joins
Functional Dependency
Normalization
1NF, 2NF, 3NF and BCNF
Transactions
ACID Properties
Concurrency Control
Serializability
Database Security
Data Independence
Three-Level Architecture
Database Recovery
