DBMS Important Questions with Answers (MSBTE 22319)
1. What is DBMS? Explain advantages.
DBMS (Database Management System) is software that manages data in databases. It allows data storage,
retrieval, update, and management.
Advantages:
- Reduces data redundancy
- Ensures data consistency and integrity
- Provides data security
- Supports backup and recovery
- Allows multiple users access
- Provides easy data retrieval using queries
2. Explain 3 Levels of DBMS Architecture.
1. Internal Level - Physical storage of data
2. Conceptual Level - Overall logical structure of the database
3. External Level - View of data for users
This is called the three-schema architecture, providing data abstraction.
3. Define Normalization. Explain 1NF, 2NF, 3NF with example.
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
1NF: No repeating groups, each column holds atomic values
2NF: In 1NF + no partial dependency
3NF: In 2NF + no transitive dependency
Example:
1NF -> Student(Name, Subject1, Subject2)
2NF -> Student(ID, Name), Subject(ID, SubjectName)
3NF -> Remove indirect dependencies
4. Types of SQL Commands with Examples
1. DDL - CREATE, ALTER, DROP
Example: CREATE TABLE student(id INT, name VARCHAR(20));
2. DML - SELECT, INSERT, UPDATE, DELETE
Example: INSERT INTO student VALUES (1, 'Raj');
3. DCL - GRANT, REVOKE
4. TCL - COMMIT, ROLLBACK, SAVEPOINT
5. DDL and DML Statements with Examples
DDL:
- CREATE TABLE emp(id INT, name VARCHAR(20));
- ALTER TABLE emp ADD salary INT;
- DROP TABLE emp;
DML:
- INSERT INTO emp VALUES (1, 'Amit');
- UPDATE emp SET name='Ravi' WHERE id=1;
- DELETE FROM emp WHERE id=1;
6. Types of Joins in SQL with Example
1. Inner Join - SELECT * FROM A INNER JOIN B ON A.id = B.id;
2. Left Join - SELECT * FROM A LEFT JOIN B ON A.id = B.id;
3. Right Join - SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
4. Full Join - SELECT * FROM A FULL JOIN B ON A.id = B.id;
7. ER Diagram - Components and Explanation
ER Diagram shows how entities relate in a database.
Components:
- Entity: e.g., Student
- Attributes: name, age
- Primary Key: unique identifier
- Relationship: e.g., STUDIES
- Cardinality: 1:1, 1:N, N:M
8. PL/SQL Program: Even or Odd
DECLARE
num NUMBER := #
BEGIN
IF MOD(num, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Even Number');
ELSE
DBMS_OUTPUT.PUT_LINE('Odd Number');
END IF;
END;
Bonus Short Questions (2-3 Marks Each)
1. Define schema and instance.
Schema: Structure or design of the database.
Instance: Actual data present in the database at a given time.
2. What is primary key and foreign key?
Primary Key: Uniquely identifies each record.
Foreign Key: Refers to the primary key in another table.
3. Write any four characteristics of DBMS.
- Reduces redundancy
- Provides data security
- Ensures consistency
- Allows concurrent access
4. List any two DML and two DDL commands.
DML: INSERT, UPDATE
DDL: CREATE, ALTER
5. State any two advantages of PL/SQL.
- Reduces network traffic
- Supports procedural programming
6. Define entity and attribute.
Entity: Real-world object (e.g., Student)
Attribute: Property of an entity (e.g., Name, Age)
7. What is a data model? Name types.
A way to represent data structure.
Types: Hierarchical, Network, Relational, Object-Oriented
