Database Management (4331603) - Summer 2025 Solution

Question 1(a) [3 marks]

Define the following terms. 1) Metadata 2) Schema 3) Data dictionary.

Answer:

Table:

• Metadata: Information describing data characteristics and properties
• Schema: Blueprint defining database structure and constraints
• Data Dictionary: Catalog of all database objects and their attributes

Mnemonic: "MSD - My System Dictionary"

Question 1(b) [4 marks]

Write down advantages of Database Management system.

Answer:

Table:

• Reduced Redundancy: Eliminates duplicate data storage
• Centralized Control: Single point of data management
• Data Sharing: Multiple applications can use same data
• Backup Recovery: Automatic data protection mechanisms

Mnemonic: "DISC-RCDB - Database Is Super Cool"

Question 1(c) [7 marks]

Explain Responsibilities of DBA.

Answer:

Table:

• Database Installation: Setup and configure DBMS software
• Data Migration: Transfer data between systems safely
• Documentation: Maintain database schemas and procedures
• Monitoring: Track system performance and resource usage
• Troubleshooting: Resolve database issues and errors

Mnemonic: "DSPBU-DMT - DBA Solves Problems By Understanding Database Management Tasks"

Question 1(c OR) [7 marks]

What is data abstraction? Explain three level ANSI SPARC architecture in detail.

Answer:

Data Abstraction: Hiding complex database implementation details from users while providing simplified interfaces.

Table:

• External Level: Multiple user views hiding complexity
• Conceptual Level: Complete database schema without storage details
• Internal Level: Physical file organization and indexing
• Data Independence: Changes at one level don't affect others

Mnemonic: "ECI - Every Computer Implements"

Question 2(a) [3 marks]

Differentiate Schema vs Instance

Answer:

Table:

• Schema: Describes database organization and constraints
• Instance: Snapshot of database content at particular moment
• Relationship: Schema defines structure, instance contains data

Mnemonic: "SI - Structure vs Information"

Question 2(b) [4 marks]

Explain Specialization with example.

Answer:

Specialization: Process of creating subclasses from superclass based on specific characteristics.

• Top-Down Approach: From general entity to specific entities
• Inheritance: Subclasses inherit superclass attributes
• Disjoint: Manager and Developer are separate categories
• Example: Employee specialized into Manager and Developer

Mnemonic: "STID - Specialization Takes Inheritance Down"

Question 2(c) [7 marks]

What is ER diagram? Explain different symbols used in E-R diagram with example.

Answer:

ER Diagram: Graphical representation showing entities, attributes, and relationships in database design.

Table:

• Entity Sets: Collection of similar entities with same attributes
• Weak Entity: Depends on strong entity for identification
• Cardinality: Defines relationship participation (1:1, 1:M, M:N)
• Participation: Total (double line) or Partial (single line)

Mnemonic: "EARP - Entities And Relationships Program"

Question 2(a OR) [3 marks]

Differentiate DA vs DBA.

Answer:

Table:

• DA: Manages data as organizational resource
• DBA: Handles technical database maintenance and performance
• Collaboration: DA sets policies, DBA implements them

Mnemonic: "DA-DBA: Design Authority - Database Builder Administrator"

Question 2(b OR) [4 marks]

Explain Generalization with example.

Answer:

Generalization: Bottom-up process combining similar entities into common superclass.

• Bottom-Up Approach: From specific entities to general entity
• Common Attributes: Shared properties moved to superclass
• Specialization Reverse: Opposite of specialization process
• Example: Car and Motorcycle generalized into Vehicle

Mnemonic: "GBCS - Generalization Brings Common Superclass"

Question 2(c OR) [7 marks]

What is attribute? Explain different types of attributes with example.

Answer:

Attribute: Property or characteristic that describes an entity.

Table:

• Key Attribute: Uniquely identifies entity instances
• Null Values: Attributes that may have no value
• Default Values: Predetermined values when not specified
• Constraints: Rules governing attribute values

Mnemonic: "SCSMD-K - Simple Composite Single Multi Derived Key"

Question 3(a) [3 marks]

Explain the GRANT and REVOKE statement in SQL.

Answer:

Table:

SQL

• Privileges: SELECT, INSERT, UPDATE, DELETE, ALL
• Objects: Tables, views, databases, procedures
• Security: Controls data access and modification rights

Mnemonic: "GR - Grant Rights, Remove Rights"

Question 3(b) [4 marks]

Explain following Character functions. 1) INITCAP 2) SUBSTR

Answer:

Table:

SQL

• INITCAP: Converts string to proper case format
• SUBSTR: Parameters are string, starting position, optional length
• Usage: Text formatting and string manipulation operations

Mnemonic: "IS - Initialize String, Split String"

Question 3(c) [7 marks]

Consider following tables and write answers for the given queries. stud_master (enroll_no, name, city, dept)

Answer:

SQL

Table:

Mnemonic: "SIAC-DOC - SQL Is A Complete Database Operations Collection"

Question 3(a OR) [3 marks]

Explain equi join with example in SQL.

Answer:

Equi Join: Join operation using equality condition to combine tables based on common columns.

SQL

• Equality Operator: Uses = to match column values
• Common Columns: Tables must have related attributes
• Result: Combined data from multiple tables based on matches

Mnemonic: "EJ - Equal Join"

Question 3(b OR) [4 marks]

Explain following Aggregate functions. 1) MAX 2) SUM

Answer:

Table:

SQL

• Aggregate Functions: Operate on multiple rows, return single value
• NULL Handling: Ignore NULL values in calculations
• GROUP BY: Can be used with grouping for category-wise results

Mnemonic: "MS - Maximum Sum"

Question 3(c OR) [7 marks]

Write SQL queries for the following table: PRODUCT_Master: (prod_no, prod_name, profit, quantity, sell_price, cost_price)

Answer:

SQL

Mnemonic: "CIDFAUD - Create Insert Delete Find Add Update Distinct"

Question 4(a) [3 marks]

Explain fully functional dependency with example.

Answer:

Fully Functional Dependency: Attribute is fully functionally dependent if it depends on complete primary key, not on partial key.

Table:

Example: Student_Course(Student_ID, Course_ID, Student_Name, Grade)

Full FD: (Student_ID, Course_ID) → Grade
Partial FD: Student_ID → Student_Name

• Complete Key: All attributes of composite primary key required
• Non-key Attribute: Depends on full primary key combination
• 2NF Requirement: Eliminates partial dependencies

Mnemonic: "FFD - Full Function Dependency"

Question 4(b) [4 marks]

Consider following relational schema & give Relational Algebra Expressions: Employee (Emp_name, Emp_id, birth_date, Post, salary)

Answer:

(i) List all Employees having Post="Clerk"
σ(Post='Clerk')(Employee)

(ii) Find Emp_id and Emp_name having salary > 2000 and post='Manager'
π(Emp_id, Emp_name)(σ(salary>2000 ∧ Post='Manager')(Employee))

Table:

• Selection (σ): Chooses rows meeting specified conditions
• Projection (π): Selects required columns from result
• Combined Operations: Can use multiple operations together

Mnemonic: "SPA - Select Project And"

Question 4(c) [7 marks]

What are the criteria of 2NF? Find different functional dependencies and normalize into 2NF.

Answer:

2NF Criteria:

• Must be in 1NF
• No partial functional dependencies on primary key

Given Table: Student_Course(Student_ID, Course_ID, Student_Name, Course_Name)

Functional Dependencies:

Student_ID → Student_Name (Partial FD)
Course_ID → Course_Name (Partial FD)
(Student_ID, Course_ID) → (Student_Name, Course_Name) (Full FD)

2NF Normalization:

SQL

Mnemonic: "2NF - Two Normal Form removes partial dependencies"

Question 4(a OR) [3 marks]

Explain 3NF with example.

Answer:

3NF (Third Normal Form): Table in 2NF with no transitive dependencies on primary key.

Table:

Example: Employee(Emp_ID, Dept_ID, Dept_Name)

Transitive Dependency: Emp_ID → Dept_ID → Dept_Name

3NF Solution:
Employee(Emp_ID, Dept_ID)
Department(Dept_ID, Dept_Name)

• Transitive Dependency: A → B → C where A is primary key
• Non-key to Non-key: Dependency between non-key attributes
• Decomposition: Split table to remove transitive dependencies

Mnemonic: "3NF - Third Normal Form removes Transitive dependencies"

Question 4(b OR) [4 marks]

Consider following Relational Schema and give Relational Algebra Expression: Students (Name, SPI, DOB, Enrollment No)

Answer:

(i) List all students whose SPI is greater than 7.0
σ(SPI > 7.0)(Students)

(ii) List name, DOB of student whose enrollment number is 007
π(Name, DOB)(σ(Enrollment_No = '007')(Students))

Table:

• Selection First: Apply conditions before projection
• Specific Value: Use quotes for string literals
• Column Names: Exact attribute names required

Mnemonic: "SPI-DOB: Select Project Information - Display Output Better"

Question 4(c OR) [7 marks]

What are criteria of 1NF? Normalize given table into 1NF with two different techniques.

Answer:

1NF Criteria:

• Each cell contains single atomic value
• No repeating groups or arrays
• Each row must be unique

Given Table:

Technique 1 - Separate Rows:

Technique 2 - Separate Tables:

SQL

Mnemonic: "1NF - One Normal Form creates Atomic values"

Question 5(a) [3 marks]

Explain ACID properties of transaction.

Answer:

Table:

• Atomicity: Transaction executes completely or not at all
• Consistency: Database constraints maintained before/after transaction
• Isolation: Transactions don't interfere with each other
• Durability: Once committed, changes survive system failures

Mnemonic: "ACID - All Consistent Isolated Durable"

Question 5(b) [4 marks]

Create following table with specification: STUDENT: (stu_id, stu_name, Address, City, contact_no, Branch_name)

Answer:

SQL

Table:

• Primary Key: stu_id uniquely identifies each student
• NOT NULL: stu_name cannot be empty
• CHECK Constraint: Branch_name limited to specified values
• Data Types: Appropriate sizes for each field

Mnemonic: "CNPD - Constraints Names Primary Datatypes"

Question 5(c) [7 marks]

What is trigger? Write syntax to create trigger in oracle. Create simple trigger.

Answer:

Trigger: Special stored procedure that automatically executes in response to database events.

Oracle Trigger Syntax:

SQL

Simple Trigger Example:

SQL

Table:

• :NEW: References new values being inserted/updated
• :OLD: References old values being deleted/updated
• Automatic Execution: Fires automatically on specified events
• Business Logic: Enforces complex business rules

Mnemonic: "TBA-FEN - Triggers Before After For Each New"

Question 5(a OR) [3 marks]

Explain problems of concurrency control in transaction.

Answer:

Table:

• Phantom Read: New rows appear between queries in same transaction
• Deadlock: Two transactions wait for each other's locks
• Inconsistent Analysis: Reading data while it's being modified

Mnemonic: "LDU-PID - Lost Dirty Unrepeatable Phantom Inconsistent Deadlock"

Question 5(b OR) [4 marks]

Create following table with specification: STUDENT: (stu_id, stu_name, Address, City, contact_no, Branch_name)

Answer:

SQL

Table:

• Primary Key: stu_id serves as unique identifier
• Pattern Check: stu_id must begin with letter 'S'
• Data Types: Appropriate field sizes and types
• Constraint Validation: Database enforces rules automatically

Mnemonic: "PKC-ST - Primary Key Check Starts"

Question 5(c OR) [7 marks]

What is Explicit cursor? Explain explicit cursor with simple example.

Answer:

Explicit Cursor: User-defined cursor for handling SELECT statements that return multiple rows with programmatic control.

Cursor Operations:

SQL

Table:

• Manual Control: Programmer controls cursor operations
• Memory Management: Must explicitly open and close
• Loop Processing: Typically used with loops for multiple rows
• Cursor Attributes: %FOUND, %NOTFOUND, %ROWCOUNT

Mnemonic: "DOFC - Declare Open Fetch Close"