MGU UGP SEM 3 - DATABASE MANAGEMENT SYSTEMS,SECOND INTERNAL EXAMINATIONS, SEPTEMBER 2025

 

Section A (2 marks each)

1. Compare Primary Key and Unique Key

Feature	Primary Key	Unique Key
Uniqueness	Must be unique	Must be unique
Null Values	Not allowed	One NULL allowed
Number per Table	Only one	Multiple allowed
Purpose	Ensures entity integrity	Enforces uniqueness

 

2. DDL Commands in SQL

Command	Description	Example
CREATE	Create tables or database objects	CREATE TABLE Students(id INT, name VARCHAR(50));
ALTER	Modify existing table	ALTER TABLE Students ADD COLUMN age INT;
DROP	Delete table or object	DROP TABLE Students;
TRUNCATE	Remove all records	TRUNCATE TABLE Students;

 

3. Functional Dependency

• Attribute B is functionally dependent on A if A uniquely determines B.
• Example: id → name in Student(id, name) table.

 

4. Transaction in DBMS

• Definition: Sequence of operations as a single logical unit.
• Properties (ACID): Atomicity, Consistency, Isolation, Durability
• Example: Bank transfer: debit from one account and credit to another must succeed together.

 

5. NoSQL

• Non-relational database designed for unstructured/semi-structured data.
• Features: Schema-less, horizontally scalable, high performance
• Types: Document, Key-Value, Column-family, Graph

 

Section B (6 marks each)

6. Data Models

• Defines how data is stored, organized, and manipulated.
• Types:
• Hierarchical: Tree structure
• Network: Nodes and relationships
• Relational: Tables with rows and columns
• ER Model: Entities and relationships
• Purpose: Helps in database design, integrity, and queries.

 

7. Weak Entity

• Cannot be uniquely identified alone; depends on a strong entity.
• Characteristics:
• Has partial key
• Exists only with strong entity
• Connected via identifying relationship
• Example: Dependent depends on Employee (composite key: employee_id + dependent_name)

 

8. Join Operations in SQL

Join Type	Description	Example
Inner Join	Only matching rows	SELECT * FROM A INNER JOIN B ON A.id=B.id;
Left Join	All left table rows + matched right	SELECT * FROM A LEFT JOIN B ON A.id=B.id;
Right Join	All right table rows + matched left	SELECT * FROM A RIGHT JOIN B ON A.id=B.id;
Full Join	All rows from both tables	SELECT * FROM A FULL OUTER JOIN B ON A.id=B.id;
Cross Join	Cartesian product	SELECT * FROM A CROSS JOIN B;

 

9. Set Operations in SQL

Operation	Description	Example
UNION	Combine and remove duplicates	SELECT id FROM A UNION SELECT id FROM B;
UNION ALL	Combine including duplicates	SELECT id FROM A UNION ALL SELECT id FROM B;
INTERSECT	Common rows	SELECT id FROM A INTERSECT SELECT id FROM B;
EXCEPT / MINUS	Rows in first not in second	SELECT id FROM A EXCEPT SELECT id FROM B;

 

10. Armstrong’s Axioms
Rules to derive functional dependencies:

1. Reflexivity: If B ⊆ A, then A → B
2. Augmentation: If A → B, then AC → BC
3. Transitivity: If A → B and B → C, then A → C
4. Union: If A → B and A → C, then A → BC
5. Decomposition: If A → BC, then A → B and A → C
6. Pseudo Transitivity: If A → B and BC → D, then AC → D

 

11. ACID Properties

• Atomicity: All or nothing
• Consistency: Database remains consistent
• Isolation: Transactions do not interfere
• Durability: Changes persist after commit

 

12. CRUD in NoSQL

• C – Create: Insert documents
• R – Read: Retrieve documents
• U – Update: Modify existing documents
• D – Delete: Remove documents

 

Section C (15 marks each)

13. Relational Algebra-Explain with examples.

• Operations:
1. Selection (σ): σ(age>20)(Students)
2. Projection (π): π(name, age)(Students)
3. Union (∪) – Combine relations
4. Set Difference (-) – Rows in first but not in second
5. Cartesian Product (×) – All combinations
6. Join (⨝) – Combine related rows
• Example: Students ⨝ Enrollments → Student info + enrollment data

 

14. Normalization-Explain with examples

• 1NF: Atomic values, no repeating groups
• 2NF: 1NF + full dependency on primary key
• 3NF: 2NF + no transitive dependency
• BCNF: Stronger 3NF, all determinants are candidate keys

Example Table:

StudentID	Name	DeptName	DeptHead
1	Alice	CS	Dr. X

• DeptHead depends on DeptName → move DeptName & DeptHead to Department table.

 

15. Serializability & Recoverability-Explain Concepts in detail

• Serializability: Concurrent transactions produce same result as some serial order.
• Conflict and view serializability.
• Recoverability: Database restored after failure.
• Cascadeless & strict schedules prevent rollback conflicts.