Database Management (4331603) - Summer 2024 Solution

Question 1(a) [3 marks]

#

Define Following Terms: 1. Data 2. Information 3. Metadata

Answer:

Table: Data vs Information vs Metadata

• Data: Basic building blocks of information systems
• Information: Result of data processing for decision making
• Metadata: Essential for database design and management

Mnemonic: “DIM - Data gives Information using Metadata”

Question 1(b) [4 marks]

#

Compare File System vs Database System

Answer:

Table: File System vs Database System Comparison

• File System: Simple but with data duplication issues
• Database System: Complex but efficient data management
• Main Advantage: DBMS eliminates data redundancy and inconsistency

Mnemonic: “DBMS = Data Better Managed Systematically”

Question 1(c) [7 marks]

#

Draw and Explain Network Data Model

Answer:

Diagram:

Table: Network Model Components

• Owner Record: Controls the set and can have multiple members
• Member Record: Belongs to one or more sets
• Set Occurrence: Instance of set type linking owner to members
• Navigation: Uses pointers for record access

Mnemonic: “Network = Nodes with Multiple Connections”

Question 1(c) OR [7 marks]

#

What is Schema? Explain different types of Schema with example

Answer:

Definition: Schema is the logical structure or blueprint of a database that defines how data is organized.

Diagram:

graph TD
    A[External Schema] --> B[Conceptual Schema]
    B --> C[Internal Schema]
    A --> D[View 1]
    A --> E[View 2]
    B --> F[Logical Structure]
    C --> G[Physical Storage]

Table: Types of Schema

• External Schema: Provides data independence for users
• Conceptual Schema: Database designer’s complete view
• Internal Schema: Database administrator’s physical view

Mnemonic: “ECI - External Conceptual Internal”

Question 2(a) [3 marks]

#

Define Following Terms: 1. Entity 2. Attributes 3. Relationship

Answer:

Table: ER Model Basic Concepts

• Entity: Represented by rectangles in ER diagrams
• Attributes: Represented by ovals connected to entities
• Relationship: Represented by diamonds connecting entities

Mnemonic: “EAR - Entity has Attributes and Relationships”

Question 2(b) [4 marks]

#

Describe Weak Entity Sets with example

Answer:

Definition: Weak entity is an entity that cannot be uniquely identified by its own attributes and depends on a strong entity.

Diagram:

Table: Weak vs Strong Entity

• Partial Key: Attribute that partially identifies weak entity
• Identifying Relationship: Connects weak entity to strong entity
• Total Participation: Weak entity must participate in relationship

Mnemonic: “Weak entities are DEPENDent”

Question 2(c) [7 marks]

#

Draw ER Diagram for University Management System

Answer:

Diagram:

erDiagram
    STUDENT {
        int student_id PK
        string name
        string email
        date birth_date
        string address
    }
    
    COURSE {
        int course_id PK
        string course_name
        int credits
        string department
    }
    
    TEACHER {
        int teacher_id PK
        string name
        string department
        string qualification
    }
    
    ENROLLMENT {
        int enrollment_id PK
        date enrollment_date
        char grade
    }
    
    STUDENT ||--o{ ENROLLMENT : enrolls
    COURSE ||--o{ ENROLLMENT : has
    TEACHER ||--o{ COURSE : teaches

Table: Entity Relationships

• Primary Entities: Student, Course, Teacher
• Associative Entity: Enrollment (resolves M:N relationship)
• Key Attributes: All entities have unique identifier

Mnemonic: “University = Students Take Courses from Teachers”

Question 2(a) OR [3 marks]

#

Define Following Terms: 1. Primary Key 2. Foreign Key 3. Candidate Key

Answer:

Table: Database Keys

• Primary Key: Cannot be NULL and must be unique
• Foreign Key: Maintains referential integrity
• Candidate Key: Alternative unique identifiers

Mnemonic: “PFC - Primary Foreign Candidate”

Question 2(b) OR [4 marks]

#

Write a Short note on Generalization and Specialization

Answer:

Generalization: Process of extracting common attributes from multiple entities to create a general entity.

Specialization: Process of defining subclasses of an entity based on distinguishing characteristics.

Diagram:

graph TD
    A[Person] --> B[Student]
    A --> C[Teacher]
    A --> D[Staff]
    B --> E[Undergraduate]
    B --> F[Graduate]

Table: Generalization vs Specialization

• ISA Relationship: “Is-A” relationship between superclass and subclass
• Inheritance: Subclasses inherit attributes from superclass

Mnemonic: “General goes UP, Special goes DOWN”

Question 2(c) OR [7 marks]

#

Explain different Relational Algebra operation with example

Answer:

Table: Relational Algebra Operations

Example Relations:

Student: (ID=1, Name=John, Age=20) Course: (CID=101, CName=DBMS, Credits=3)

• Selection: σ(Age>18)(Student) returns students above 18
• Projection: π(Name)(Student) returns only names
• Join: Student ⋈ Enrollment combines student and enrollment data

Mnemonic: “SPUDIJ - Select Project Union Difference Intersection Join”

Question 3(a) [3 marks]

#

List out Numeric Functions in SQL. Explain any Two

Answer:

Table: SQL Numeric Functions

Detailed Examples:

• ABS(number): Returns absolute value, removing negative sign
• ROUND(number, decimal_places): Rounds number to specified decimal places

Mnemonic: “Math functions make Numbers Nice”

Question 3(b) [4 marks]

#

Describe Having and Order by Clause with example

Answer:

HAVING Clause: Used with GROUP BY to filter groups based on aggregate conditions.

ORDER BY Clause: Used to sort result set in ascending or descending order.

Table: HAVING vs WHERE

Example:

SELECT department, COUNT(*) as emp_count
FROM employees 
WHERE salary > 30000
GROUP BY department 
HAVING COUNT(*) > 5
ORDER BY emp_count DESC;

• WHERE: Filters employees with salary > 30000
• HAVING: Shows only departments with more than 5 employees
• ORDER BY: Sorts by employee count in descending order

Mnemonic: “WHERE filters rows, HAVING filters groups, ORDER BY sorts results”

Question 3(c) [7 marks]

#

Perform the following Query on the table student having the fields Student_ID, Stu_Name, Stu_Subject_ID, Stu_Marks, Stu_Age in SQL

Answer:

1. Create student table:

CREATE TABLE student (
    Student_ID INT PRIMARY KEY,
    Stu_Name VARCHAR(50),
    Stu_Subject_ID INT,
    Stu_Marks INT,
    Stu_Age INT
);

2. Insert record in student table:

INSERT INTO student VALUES 
(1, 'John', 101, 85, 22),
(2, 'Mary', 102, 90, 21);

3. Find minimum and maximum marks:

SELECT MIN(Stu_Marks) as Min_Marks, 
       MAX(Stu_Marks) as Max_Marks 
FROM student;

4. Students with marks > 82 and age = 22:

SELECT * FROM student 
WHERE Stu_Marks > 82 AND Stu_Age = 22;

5. Students whose name begins with ’m’:

SELECT * FROM student 
WHERE Stu_Name LIKE 'm%';

6. Find average marks:

SELECT AVG(Stu_Marks) as Average_Marks 
FROM student;

7. Add Stu_address column:

ALTER TABLE student 
ADD Stu_address VARCHAR(100);

Mnemonic: “CRUD + Analytics = Complete Database Operations”

Question 3(a) OR [3 marks]

#

Describe different date function in SQL with example

Answer:

Table: SQL Date Functions

Examples:

• SYSDATE: Returns current system date and time
• ADD_MONTHS: Useful for calculating future dates like loan due dates

Mnemonic: “Date functions help with Time Management”

Question 3(b) OR [4 marks]

#

List out Constraints in SQL. Explain any two with example

Answer:

Table: SQL Constraints

Detailed Examples:

PRIMARY KEY Constraint:

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(50)
);

CHECK Constraint:

CREATE TABLE Employee (
    Emp_ID INT,
    Salary INT CHECK (Salary > 0)
);

• PRIMARY KEY: Ensures each record has unique identifier
• CHECK: Validates business rules during data entry

Mnemonic: “Constraints Control Data Quality”

Question 3(c) OR [7 marks]

#

Explain different types of joins with example in SQL

Answer:

Table: Types of SQL Joins

Example Tables: Students: (ID=1, Name=John), (ID=2, Name=Mary) Enrollments: (StudentID=1, Course=DBMS), (StudentID=3, Course=Java)

INNER JOIN Example:

SELECT s.Name, e.Course 
FROM Students s 
INNER JOIN Enrollments e ON s.ID = e.StudentID;

Result: Only John with DBMS course

LEFT JOIN Example:

SELECT s.Name, e.Course 
FROM Students s 
LEFT JOIN Enrollments e ON s.ID = e.StudentID;

Result: John-DBMS, Mary-NULL

Mnemonic: “JOIN connects Related Tables”

Question 4(a) [3 marks]

#

Give an example of Grant and Revoke command in SQL

Answer:

GRANT Command: Provides specific privileges to users on database objects.

REVOKE Command: Removes previously granted privileges from users.

Table: Common Privileges

Examples:

-- Grant SELECT privilege
GRANT SELECT ON Student TO john;

-- Revoke INSERT privilege  
REVOKE INSERT ON Student FROM john;

• WITH GRANT OPTION: Allows user to grant privileges to others
• CASCADE: Revokes privileges from all users who received them

Mnemonic: “GRANT gives rights, REVOKE removes rights”

Question 4(b) [4 marks]

#

Write a short note on SQL Views

Answer:

Definition: A view is a virtual table based on the result of an SQL statement containing rows and columns like a real table.

Table: View Characteristics

Example:

CREATE VIEW active_students AS
SELECT Student_ID, Name, Age 
FROM Student 
WHERE Status = 'Active';

-- Using the view
SELECT * FROM active_students;

Advantages:

• Security: Restrict access to sensitive data
• Simplicity: Hide complex joins from end users
• Consistency: Standardized data access

Mnemonic: “Views are Virtual Windows to Data”

Question 4(c) [7 marks]

#

What is Normalization? Explain 2NF with example

Answer:

Normalization: Process of organizing database to reduce redundancy and improve data integrity by dividing large tables into smaller related tables.

2NF (Second Normal Form):

• Must be in 1NF
• Remove partial functional dependencies
• Non-key attributes must depend on entire primary key

Example - Unnormalized Table:

Problems:

• Student_Name depends only on Student_ID (partial dependency)
• Course_Name and Instructor depend only on Course_ID

After 2NF:

Student Table:

Course Table:

Enrollment Table:

Benefits:

• Eliminates Redundancy: Student names not repeated
• Reduces Storage: Less duplicate data
• Improves Consistency: Update student name in one place

Mnemonic: “2NF = No Partial Dependencies”

Question 4(a) OR [3 marks]

#

Give an example of Group By Clause in SQL

Answer:

GROUP BY Clause: Groups rows with same values in specified columns and allows aggregate functions on each group.

Table: GROUP BY Usage

Example:

SELECT Department, COUNT(*) as Total_Students, AVG(Marks) as Avg_Marks
FROM Student 
GROUP BY Department;

Result:

• Groups: Creates separate groups for each department
• Aggregates: Calculates count and average for each group

Mnemonic: “GROUP BY creates Summary Reports”

Question 4(b) OR [4 marks]

#

Describe Set Operators in SQL with example

Answer:

Set Operators: Combine results from two or more SELECT statements.

Table: SQL Set Operators

Example:

-- Students who are also teachers
SELECT name FROM students
INTERSECT
SELECT name FROM teachers;

-- All people in university
SELECT name, 'Student' as type FROM students
UNION
SELECT name, 'Teacher' as type FROM teachers;

Rules:

• Column Count: Must be same in all queries
• Data Types: Corresponding columns must have compatible types
• Order: ORDER BY can only be used at the end

Mnemonic: “Set operators Unite, Intersect, and Subtract data”

Question 4(c) OR [7 marks]

#

Justify the importance of Normalization. Explain 1NF with example

Answer:

Importance of Normalization:

Table: Benefits of Normalization

1NF (First Normal Form):

• Eliminate duplicate columns from same table
• Create separate tables for related data
• Each cell contains single value (atomic values)

Example - Unnormalized Table:

Problems:

• Subjects column contains multiple values
• Difficult to query specific subjects
• Update anomalies when adding/removing subjects

After 1NF:

Student Table:

Student_Subject Table:

Benefits:

• Atomic Values: Each cell contains single value
• Flexible Queries: Easy to find students studying specific subjects
• Easy Updates: Add/remove subjects without affecting other data

Mnemonic: “1NF = One value per cell, No repeating groups”

Question 5(a) [3 marks]

#

Explain Serializability in Transaction Management

Answer:

Serializability: Property that ensures concurrent execution of transactions produces same result as some serial execution of those transactions.

Table: Types of Serializability

Example: Transaction T1: R(A), W(A), R(B), W(B) Transaction T2: R(A), W(A), R(B), W(B)

Serial Schedule: T1 → T2 or T2 → T1 Concurrent Schedule: Interleaved operations

• Conflict Operations: Operations on same data item where at least one is write
• Serializable Schedule: Equivalent to some serial schedule
• Non-serializable: May lead to inconsistent database state

Mnemonic: “Serializability ensures Transaction Consistency”

Question 5(b) [4 marks]

#

Describe Partial Functional Dependency with example

Answer:

Partial Functional Dependency: When a non-key attribute is functionally dependent on only part of a composite primary key.

Table: Functional Dependency Types

Example: Enrollment Table: Primary Key: (Student_ID, Course_ID)

Partial Dependencies:

• Student_ID → Student_Name (Student_Name depends only on Student_ID)
• Course_ID → Course_Name (Course_Name depends only on Course_ID)

Problems:

• Update Anomaly: Changing student name requires multiple updates
• Insertion Anomaly: Cannot add student without enrolling in course
• Deletion Anomaly: Deleting enrollment may lose student information

Solution: Normalize to 2NF by removing partial dependencies

Mnemonic: “Partial dependency = Part of key determines attribute”

Question 5(c) [7 marks]

#

Write a Short note on Locking Mechanism with example in Transaction Management

Answer:

Locking Mechanism: Concurrency control technique that prevents simultaneous access to data items during transaction execution.

Table: Types of Locks

Two-Phase Locking (2PL) Protocol:

1. Growing Phase: Acquire locks, cannot release any lock
2. Shrinking Phase: Release locks, cannot acquire new locks

Example:

Transaction T1: Read(A), Write(A), Read(B), Write(B)
Transaction T2: Read(A), Write(A), Read(C), Write(C)

T1: S-lock(A), Read(A), X-lock(A), Write(A), S-lock(B), Read(B), X-lock(B), Write(B), Unlock(A), Unlock(B)
T2: Wait for A, S-lock(A), Read(A), X-lock(A), Write(A), S-lock(C), Read(C), X-lock(C), Write(C), Unlock(A), Unlock(C)

Lock Compatibility Matrix:

Problems:

• Deadlock: Two transactions waiting for each other’s locks
• Starvation: Transaction waits indefinitely for lock

Solutions:

• Deadlock Detection: Use wait-for graph
• Deadlock Prevention: Timestamp-based protocols

Mnemonic: “Locking prevents Concurrent Conflicts”

Question 5(a) OR [3 marks]

#

Explain Deadlock in Transaction Management

Answer:

Deadlock: Situation where two or more transactions are waiting indefinitely for each other to release locks, creating a circular wait condition.

Table: Deadlock Components

Example:

Transaction T1: Lock(A), Lock(B)
Transaction T2: Lock(B), Lock(A)

Time 1: T1 gets Lock(A)
Time 2: T2 gets Lock(B) 
Time 3: T1 waits for Lock(B) - held by T2
Time 4: T2 waits for Lock(A) - held by T1
Result: DEADLOCK!

Detection: Use wait-for graph to identify cycles Prevention: Use timestamp ordering or wound-wait protocols

Mnemonic: “Deadlock = Circular Waiting for Resources”

Question 5(b) OR [4 marks]

#

Describe Full Functional Dependency with example

Answer:

Full Functional Dependency: A non-key attribute is functionally dependent on the entire primary key (not just part of it).

Table: Dependency Comparison

Example: Enrollment Table: Primary Key: (Student_ID, Course_ID)

Full Functional Dependencies:

• (Student_ID, Course_ID) → Grade ✓
• (Student_ID, Course_ID) → Hours ✓

Explanation:

• Grade depends on both Student_ID AND Course_ID (specific student in specific course)
• Hours also depends on both (student’s hours in specific course)
• Cannot determine Grade from Student_ID alone
• Cannot determine Grade from Course_ID alone

Benefits:

• No Update Anomalies: Changes affect only relevant records
• Proper Normalization: Supports 2NF requirements
• Data Integrity: Ensures accurate relationships

Mnemonic: “Full dependency needs Complete Key”

Question 5(c) OR [7 marks]

#

Explain ACID Properties of Transaction with example

Answer:

ACID Properties: Four fundamental properties that guarantee database transaction reliability.

Table: ACID Properties

Detailed Examples:

Atomicity Example:

BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 1000 WHERE AccNo = 'A001';
UPDATE Account SET Balance = Balance + 1000 WHERE AccNo = 'A002';
COMMIT;

If either update fails, entire transaction is rolled back

Consistency Example:

-- Before: A001 = 5000, A002 = 3000, Total = 8000
-- Transfer 1000 from A001 to A002
-- After: A001 = 4000, A002 = 4000, Total = 8000
-- Total money in system remains constant

Isolation Example:

T1: Read(A=100), A=A+50, Write(A=150)
T2: Read(A=100), A=A*2, Write(A=200)
Serial Result: A=300 or A=250
Isolated execution must produce one of these results

Durability Example:

After COMMIT is executed, even if system crashes,
the transferred amount remains in destination account

Implementation:

• Atomicity: Using transaction logs and rollback
• Consistency: Using constraints and triggers
• Isolation: Using locking mechanisms
• Durability: Using write-ahead logging

Mnemonic: “ACID keeps Transactions Reliable”