- Course Code: 313302
- Semester: 3 – K Scheme
- Maximum Marks: 30
- Time: 1 Hour
🧾 Exam Pattern & Marks Distribution
The Database Management System (DBMS) exam follows the MSBTE Diploma pattern, designed to test both conceptual understanding and practical application of database principles.
📊 Exam Structure:
- Total Marks: 100
- Theory Paper: 70 Marks
- Internal Assessment / Term Work: 30 Marks
📝 Theory Paper Pattern:
| Section | Type of Questions | Marks | Remarks |
|---|---|---|---|
| Q1 | Objective / Multiple Choice / Fill in the Blanks | 8–10 | Based on basic concepts |
| Q2 | Short Answer Questions | 12–14 | From all units |
| Q3 | Attempt any 2 out of 3 (Descriptive Questions) | 16 | Moderate difficulty |
| Q4 | Long / Application-Based Questions | 16 | Includes SQL & normalization problems |
| Q5 | Case Study / ER Diagram / Query Writing | 14 | Real-world problem-solving |
Key Topics to Focus On
- Core Concepts: DBMS vs. File System, 3-Tier Architecture, Codd's Rules.
- Database Design: ER Diagrams, Normalization (1NF, 2NF, 3NF), and Keys (Primary, Foreign, Candidate).
- SQL Commands: DDL (`CREATE`, `ALTER`), DML (`INSERT`, `UPDATE`, `DELETE`), and DCL (`GRANT`, `REVOKE`).
- Advanced SQL: All types of `JOIN` operations are extremely important.
- PL/SQL: Basic block structure, Exception Handling, and simple programs like factorial or Fibonacci.
❌ Common Mistakes to Avoid
- Confusing JOIN Types: Mixing up `INNER JOIN` with `LEFT JOIN` or `RIGHT JOIN`. Always double-check which rows you need to include.
- Incorrect Normalization: Applying normalization forms in the wrong order or misunderstanding functional dependencies. Ensure you master 1NF, 2NF, and 3NF.
- Forgetting ER Diagram Notations: Using incorrect symbols for entities, attributes, or relationships. Practice standard notations (e.g., rectangles for entities, ovals for attributes).
- Mixing DDL and DML: Confusing commands like `ALTER` (DDL) with `UPDATE` (DML). Remember, DDL defines structure, while DML manipulates data.
- Ignoring Constraints: Forgetting to define `PRIMARY KEY`, `FOREIGN KEY`, or `NOT NULL` constraints, which are crucial for data integrity.
- PL/SQL Syntax Errors: Forgetting semicolons (`;`) after statements or using `=` for assignment instead of `:=`.
Q.1 Attempt any THREE of the following (3×2=6 Marks)
- Define Database and DBMS.
- List any two advantages of DBMS over file system.
- Define Primary Key and Foreign Key with examples.
- State any two Codd’s Rules.
- List any two Data Models.
Q.2 Attempt any THREE of the following (3×4=12 Marks)
- Explain 3-Tier Architecture of DBMS.
- Draw an ER diagram for a Library Management System.
- Write any four DDL commands with syntax.
- Explain different types of JOINs with example.
- What is Normalization? Explain 1NF and 2NF.
Q.3 Attempt any TWO of the following (2×6=12 Marks)
- Write a PL/SQL block to find factorial of a given number.
- Explain Exception Handling in PL/SQL with example.
- What are the different types of database backups? Explain any two.
Model Answers & Key Points
Answers for Q.1 (2 Marks Each)
- a) Database and DBMS: A Database is an organized collection of structured data. A DBMS is the software used to create, manage, and access that database (e.g., MySQL, Oracle).
- b) Advantages of DBMS: 1. Reduces data redundancy and inconsistency. 2. Improves data security and access control.
- c) Keys: A Primary Key uniquely identifies each record in a table (e.g., `StudentID` in a `Students` table). A Foreign Key links two tables by referring to the primary key of another table (e.g., `StudentID` in a `Grades` table).
- d) Codd’s Rules: 1. Information Rule: All data must be stored in a table format. 2. Guaranteed Access Rule: Every piece of data must be accessible by using the table name, primary key, and column name.
- e) Data Models: Relational Model, Hierarchical Model, Network Model, Entity-Relationship Model.
Key Points for Q.2 (4 Marks Each)
- a) 3-Tier Architecture: Explain the three tiers: 1. Presentation Tier (UI/Client), 2. Application Tier (Business Logic/Server), and 3. Data Tier (Database Server). Mention how this separation improves scalability and security.
- b) ER Diagram (Library): Key entities would be `Book`, `Member`, and `Librarian`. Attributes for `Book` could be `BookID`, `Title`, `Author`. Relationships would include `Member` *borrows* `Book`, which is a many-to-many relationship managed by a `Borrow` table.
- c) DDL Commands: 1. `CREATE`: Used to create tables/databases (`CREATE TABLE Students (...)`). 2. `ALTER`: Modifies the structure of a table (`ALTER TABLE Students ADD email VARCHAR(255)`). 3. `DROP`: Deletes a table (`DROP TABLE Students`). 4. `TRUNCATE`: Removes all records from a table (`TRUNCATE TABLE Students`).
- d) JOINs: Explain `INNER JOIN` (returns matching rows), `LEFT JOIN` (returns all rows from the left table), `RIGHT JOIN` (returns all rows from the right table), and `FULL OUTER JOIN` (returns all rows when there is a match in either table). Provide a simple SQL example for each.
- e) Normalization: The process of organizing columns and tables to minimize data redundancy. 1NF: Ensures all table cells hold a single value and each record is unique. 2NF: Must be in 1NF and all non-key attributes must be fully dependent on the primary key.
Hints for Q.3 (6 Marks Each)
- a) PL/SQL Factorial: Use a `DECLARE` block for variables (`num`, `fact`). Use a `FOR` loop from 1 to the given number. In the loop, calculate the factorial using `fact := fact * i;`. Use `DBMS_OUTPUT.PUT_LINE` to print the result.
- b) Exception Handling: Explain the `BEGIN...EXCEPTION...END` block. Use `WHEN exception_name THEN` to catch specific errors (e.g., `NO_DATA_FOUND`, `TOO_MANY_ROWS`). Show an example where a `SELECT...INTO` query might fail and how the `EXCEPTION` block handles it gracefully.
- c) Database Backups: Explain different types like Full Backup (copies the entire database), Differential Backup (copies data changed since the last full backup), and Incremental Backup (copies data changed since the last backup of any type). Explain any two in detail with their pros and cons.
✅ FREE VERSION ENDS HERE
For the full 70-mark paper with 15+ advanced questions and model answers, check out the Paid Version.
Get Full Paper Now