Revised

Question 1

What is DBMS?

Answer 1

DBMS (Database Management System) is software used to create, store, manage, and manipulate data efficiently.

Question 2

What is the main function of DBMS?

Answer 2

It acts as an interface between the user and the database.

Question 3

Give examples of DBMS software.

Answer 3

MySQL, Oracle, PostgreSQL, MS Access, MongoDB.

Question 4

List components of DBMS.

Answer 4

Hardware, Software, Data, Users, Procedures.

Question 5

What is the hardware component of DBMS?

Answer 5

Devices used for storage and data access.

Question 6

What is the software component of DBMS?

Answer 6

The DBMS program that manages the database.

Question 7

Who are the users of DBMS?

Answer 7

DBA, Designer, End-user.

Question 8

What are procedures in DBMS?

Answer 8

Rules and instructions on how to use and manage the database.

Question 9

List advantages of DBMS.

Answer 9

Reduces redundancy, ensures data integrity, provides security, supports backup and recovery, allows concurrent access, and enables data sharing.

Question 10

How does DBMS ensure data integrity?

Answer 10

By enforcing constraints and validation rules on data.

Question 11

What are the types of DBMS?

Answer 11

Hierarchical, Network, Relational, Object-Oriented.

Question 12

Describe Hierarchical DBMS.

Answer 12

Organizes data in a tree-like structure (e.g., IBM IMS).

Question 13

Describe Network DBMS.

Answer 13

Uses many-to-many relationships (e.g., IDMS).

Question 14

Describe Relational DBMS.

Answer 14

Stores data in tables of rows and columns (e.g., MySQL, Oracle).

Question 15

Describe Object-Oriented DBMS.

Answer 15

Stores data as objects (e.g., db4o).

Question 16

What is a primary key?

Answer 16

A field that uniquely identifies a record in a table.

Question 17

What is a foreign key?

Answer 17

A field that refers to the primary key in another table.

Question 18

What is a candidate key?

Answer 18

All possible attributes that can uniquely identify a record.

Question 19

What is an alternate key?

Answer 19

A candidate key not chosen as the primary key.

Question 20

What is a composite key?

Answer 20

A combination of multiple attributes used to uniquely identify a record.

Question 21

What is a super key?

Answer 21

Any set of attributes that can uniquely identify a record.

Question 22

What is normalization?

Answer 22

The process of organizing data to reduce redundancy and improve integrity.

Question 23

Explain 1NF.

Answer 23

Ensures atomic values; removes repeating groups.

Question 24

Explain 2NF.

Answer 24

Removes partial dependency; ensures each non-key attribute depends on the whole key.

Question 25

Explain 3NF.

Answer 25

Removes transitive dependency; non-key attributes depend only on primary key.

Question 26

Explain BCNF.

Answer 26

Every determinant must be a candidate key; stronger than 3NF.

Question 27

What are the types of SQL commands?

Answer 27

DDL, DML, DQL, DCL, TCL.

Question 28

What are DDL commands?

Answer 28

CREATE, ALTER, DROP, TRUNCATE – define database structure.

Question 29

What are DML commands?

Answer 29

INSERT, UPDATE, DELETE – manipulate data.

Question 30

What are DQL commands?

Answer 30

SELECT – used to query data.

Question 31

What are DCL commands?

Answer 31

GRANT, REVOKE – manage access control.

Question 32

What are TCL commands?

Answer 32

COMMIT, ROLLBACK, SAVEPOINT – manage transactions.

Question 33

What is an INNER JOIN?

Answer 33

Returns matching rows from both tables.

Question 34

What is a LEFT JOIN?

Answer 34

Returns all rows from left table and matched rows from right table.

Question 35

What is a RIGHT JOIN?

Answer 35

Returns all rows from right table and matched rows from left table.

Question 36

What is a FULL JOIN?

Answer 36

Returns all rows from both tables; unmatched rows have NULLs.

Question 37

What are constraints in DBMS?

Answer 37

Rules applied to columns to ensure data accuracy and integrity.

Question 38

List common constraints.

Answer 38

NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, FOREIGN KEY.

Question 39

Explain 1-Tier Architecture.

Answer 39

Database and application are on the same system.

Question 40

Explain 2-Tier Architecture.

Answer 40

Client communicates directly with database server.

Question 41

Explain 3-Tier Architecture.

Answer 41

Client interacts with an application server, which connects to the database server.

Question 42

Explain 3-level architecture of DBMS.

Answer 42

Internal (physical), Conceptual (logical), and External (user view) levels.

Question 43

What is a transaction?

Answer 43

A single logical unit of work like money transfer.

Question 44

What are ACID properties?

Answer 44

Atomicity, Consistency, Isolation, Durability.

Question 45

Define Atomicity.

Answer 45

Transaction is all or nothing.

Question 46

Define Consistency.

Answer 46

Database remains in a valid state before and after transaction.

Question 47

Define Isolation.

Answer 47

Transactions execute independently.

Question 48

Define Durability.

Answer 48

Once committed, changes are permanent.

Question 49

What is PL/SQL?

Answer 49

An Oracle extension of SQL that supports variables, loops, and exception handling.

Question 50

What is a trigger?

Answer 50

A stored procedure that executes automatically when an event occurs (e.g., INSERT, UPDATE).

Question 51

What is a cursor?

Answer 51

A database object used to retrieve and process data row by row.

Question 52

What is a view?

Answer 52

A virtual table created from query results.

Question 53

What is an ER model?

Answer 53

Represents data using entities, attributes, and relationships.

Question 54

List types of relationships in ER Model.

Answer 54

One-to-One, One-to-Many, Many-to-Many.

Question 55

What is functional dependency?

Answer 55

If attribute Y depends on X, written as X → Y.

Question 56

What is indexing in DBMS?

Answer 56

Technique to improve search/query performance.

Question 57

What is backup and recovery?

Answer 57

Process to restore data after a failure or loss.

Question 58

Difference between DBMS and File System?

Answer 58

DBMS reduces redundancy and provides security, while file system lacks these features.

Question 59

What are database anomalies?

Answer 59

Problems in database design like insertion, deletion, and update anomalies.

Question 60

Give an example of DBMS application.

Answer 60

School DB with tables: Students, Teachers, Courses linked via keys.

Question 61

What is data abstraction?

Answer 61

Hiding complexity of database details from users.

Question 62

What is data independence?

Answer 62

Ability to change schema at one level without affecting higher levels.

Question 63

What is data dictionary?

Answer 63

A repository containing metadata about database objects.

Question 64

What is a schema?

Answer 64

Logical design or structure of the database.

Question 65

What is a subquery?

Answer 65

A query nested inside another SQL query.

Question 66

What is a stored procedure?

Answer 66

Precompiled SQL statements stored in the database.

Question 67

What is concurrency control?

Answer 67

Technique to manage simultaneous data access by multiple users.

Question 68

What is deadlock?

Answer 68

A situation where two or more transactions wait for each other indefinitely.

Question 70

Front

Answer 70

Back

Question 71

PL/SQL

Answer 71

Oracle's procedural extension to SQL allowing variables, loops, and exception handling.

Question 72

PL/SQL Example

Answer 72

DECLARE v_name VARCHAR2(20); BEGIN SELECT name INTO v_name FROM Students WHERE ID = 1; DBMS_OUTPUT.PUT_LINE(v_name); END;

Question 73

Control Structure

Answer 73

Used to control program flow in PL/SQL like IF, LOOP, FOR, CASE.

Question 74

IF Example

Answer 74

IF marks >= 40 THEN DBMS_OUTPUT.PUT_LINE('Pass'); ELSE DBMS_OUTPUT.PUT_LINE('Fail'); END IF;

Question 75

Loop Example

Answer 75

FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE('Count: ' || i); END LOOP;

Question 76

Exception Handling

Answer 76

Handles runtime errors gracefully using EXCEPTION block.

Question 77

Exception Example

Answer 77

BEGIN SELECT name INTO v_name FROM Students WHERE ID = 100; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Record not found'); END;

Question 78

Cursor

Answer 78

Used to process query results row-by-row.

Question 79

Cursor Example

Answer 79

DECLARE CURSOR student_cur IS SELECT name FROM Students; OPEN student_cur; FETCH...; CLOSE student_cur;

Question 80

Procedure

Answer 80

A reusable PL/SQL block that performs a specific task.

Question 81

Procedure Example

Answer 81

CREATE OR REPLACE PROCEDURE add_student(p_id INT, p_name VARCHAR2) AS BEGIN INSERT INTO Students(ID, Name) VALUES(p_id, p_name); END;

Question 82

Clauses

Answer 82

WHERE, GROUP BY, ORDER BY, HAVING, DISTINCT, LIKE control how data is filtered, grouped, and sorted.

Question 83

WHERE Clause Example

Answer 83

SELECT * FROM Students WHERE Age > 18;

Question 84

GROUP BY Clause Example

Answer 84

SELECT Class, COUNT(*) FROM Students GROUP BY Class;

Question 85

ORDER BY Clause Example

Answer 85

SELECT * FROM Students ORDER BY Name ASC;

Question 86

HAVING Clause Example

Answer 86

SELECT Class, COUNT(*) FROM Students GROUP BY Class HAVING COUNT(*) > 10;

Question 87

DISTINCT Clause Example

Answer 87

SELECT DISTINCT Class FROM Students;

Question 88

LIKE Clause Example

Answer 88

SELECT * FROM Students WHERE Name LIKE 'A%';