Database Management (4331603) - Winter 2023 Solution

Question 1(a) [3 marks]

#

Define the following terms: a). Data items b). Data dictionary c).Meta data

Answer:

Mnemonic: “DDM - Data Dictionary Manages”

Question 1(b) [4 marks]

#

Explain disadvantages of File oriented system.

Answer:

Mnemonic: “RDIS - Really Difficult Information System”

Question 1(c) [7 marks]

#

Describe the responsibilities of DBA in detail.

Answer:

graph TD
    A[DBA Responsibilities] --> B[Design & Planning]
    A --> C[Security & Access]
    A --> D[Performance & Optimization]
    A --> E[Backup & Recovery]
    A --> F[User Support]
    A --> G[Maintenance]

Mnemonic: “DSPBUM - Database Specialists Provide Better User Management”

Question 1(c OR) [7 marks]

#

Define data abstraction? Explain Three level Architecture of DBMS.

Answer:

Data Abstraction: Process of hiding complex implementation details while showing only essential features to users.

graph TB
    A[External Level<br/>User Views] --> B[Conceptual Level<br/>Logical Schema]
    B --> C[Internal Level<br/>Physical Schema]
    
    A1[User 1 View] --> A
    A2[User 2 View] --> A
    A3[User 3 View] --> A

Mnemonic: “ECI - Every Computer Industry”

Question 2(a) [3 marks]

#

Define the Following Terms :a).Relationship set b).Participation c).Candidate key

Answer:

Mnemonic: “RPC - Relationship Participation Candidate”

Question 2(b) [4 marks]

#

Explain Generalization with example.

Answer:

Generalization: Bottom-up approach where common attributes of lower-level entities are combined into higher-level entity.

graph BT
    A[Car] --> D[Vehicle]
    B[Truck] --> D
    C[Bus] --> D
    
    A1[Brand, Model, Fuel Type] --> A
    B1[Brand, Model, Load Capacity] --> B
    C1[Brand, Model, Seating Capacity] --> C
    D1[Vehicle_ID, Brand, Model] --> D

Mnemonic: “GBU - Generalization Builds Up”

Question 2(c) [7 marks]

#

Define E-R diagram? Explain different symbols used in E-R diagram with example.

Answer:

E-R Diagram: Graphical representation showing entities, attributes, and relationships in database design.

erDiagram
    STUDENT {
        int student_id PK
        string name
        date birth_date
        string email
    }
    COURSE {
        int course_id PK
        string course_name
        int credits
    }
    STUDENT ||--o{ ENROLLMENT : enrolls
    COURSE ||--o{ ENROLLMENT : "enrolled in"
    ENROLLMENT {
        int student_id FK
        int course_id FK
        date enrollment_date
        string grade
    }

Mnemonic: “EARPM - Every Attribute Represents Proper Meaning”

Question 2(a OR) [3 marks]

#

Define Relational Algebra? List out various operations in relational algebra?

Answer:

Relational Algebra: Formal query language with operations for manipulating relational database tables.

Mnemonic: “SPUDC-IJDR - Simple People Use Database Concepts”

Question 2(b OR) [4 marks]

#

Explain Specialization with example.

Answer:

Specialization: Top-down approach where higher-level entity is divided into specialized lower-level entities.

graph TD
    A[Employee<br/>Emp_ID, Name, Salary] --> B[Manager<br/>Department]
    A --> C[Clerk<br/>Typing_Speed]
    A --> D[Engineer<br/>Specialization]

Mnemonic: “STD - Specialization Top Down”

Question 2(c OR) [7 marks]

#

Define attribute? Explain different types of attributes with example.

Answer:

Attribute: Property or characteristic that describes an entity.

graph TD
    A[Attributes] --> B[Simple<br/>Age, Name]
    A --> C[Composite<br/>Address]
    A --> D[Multi-valued<br/>Phone Numbers]
    A --> E[Derived<br/>Age from DOB]
    
    C --> F[Street]
    C --> G[City]
    C --> H[State]

Mnemonic: “SCSMDK - Simple Composite Single Multi Derived Key”

Question 3(a) [3 marks]

#

Explain the GRANT and REVOKE statement in SQL.

Answer:

Common Privileges: SELECT, INSERT, UPDATE, DELETE, ALL

Mnemonic: “GR - Grant Removes (via REVOKE)”

Question 3(b) [4 marks]

#

Explain following Character functions. 1) INSTR 2) LENGTH

Answer:

Mnemonic: “IL - INSTR Locates, LENGTH measures”

Question 3(c) [7 marks]

#

Write SQL statements for following table: Student(Enno,name,branch,sem,clgname,bdate)

Answer:

-- 1. Create a table Student
CREATE TABLE Student (
    Enno VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50),
    branch VARCHAR(20),
    sem INT,
    clgname VARCHAR(100),
    bdate DATE
);

-- 2. Add a column mobno in Student table
ALTER TABLE Student ADD mobno VARCHAR(15);

-- 3. Insert one record in student table
INSERT INTO Student VALUES 
('E001', 'Raj Patel', 'IT', 3, 'GTU College', '2003-05-15', '9876543210');

-- 4. Find out list of students who have enrolled in "IT" branch
SELECT * FROM Student WHERE branch = 'IT';

-- 5. Retrieve all information about student where name begin with 'a'
SELECT * FROM Student WHERE name LIKE 'a%';

-- 6. Count the number of rows in student table
SELECT COUNT(*) FROM Student;

-- 7. Delete all record of student table
DELETE FROM Student;

Mnemonic: “CAIRSCD - Create Add Insert Retrieve Search Count Delete”

Question 3(a OR) [3 marks]

#

Explain equi join with example in SQL.

Answer:

Equi Join: Join operation using equality condition to combine tables.

-- Example
SELECT s.name, c.course_name 
FROM Student s, Course c 
WHERE s.course_id = c.course_id;

Mnemonic: “EE - Equi Equals”

Question 3(b OR) [4 marks]

#

Explain following Aggregate functions. 1) MAX 2) SUM

Answer:

Mnemonic: “MS - MAX Sum”

Question 3(c OR) [7 marks]

#

Write SQL statements for the following table: Employee(EmpID,Ename,DOB,Dept,Salary)

Answer:

-- 1. Create a table Employee
CREATE TABLE Employee (
    EmpID VARCHAR(10) PRIMARY KEY,
    Ename VARCHAR(50),
    DOB DATE,
    Dept VARCHAR(30),
    Salary DECIMAL(10,2)
);

-- 2. Find sum of salaries of all employee
SELECT SUM(Salary) FROM Employee;

-- 3. Insert one record in Employee table
INSERT INTO Employee VALUES 
('E001', 'John Doe', '1990-05-15', 'IT', 35000);

-- 4. Find names of employees who salary between 25000/- and 48000/-
SELECT Ename FROM Employee WHERE Salary BETWEEN 25000 AND 48000;

-- 5. Display detail of all employees in descending order of their DOB
SELECT * FROM Employee ORDER BY DOB DESC;

-- 6. List name of all employees whose name ends with 'a'
SELECT Ename FROM Employee WHERE Ename LIKE '%a';

-- 7. Find highest and least salaries of all employees
SELECT MAX(Salary) AS Highest, MIN(Salary) AS Lowest FROM Employee;

Mnemonic: “CSIDDHL - Create Sum Insert Display Display List HighLow”

Question 4(a) [3 marks]

#

Consider a following relational schema & give Relational Algebra Expressions for the following queries.

Answer:

Student (Enrollment_No,Name,DOB,SPI)

i. σ(SPI > 7.0)(Student)
ii. π(Name)(σ(Enrollment_No = 007)(Student))

Mnemonic: “SP - Select Project”

Question 4(b) [4 marks]

#

Write a short note on partial functional dependency.

Answer:

Example: In table(StudentID, CourseID, StudentName, CourseName), StudentName depends only on StudentID (part of key).

Mnemonic: “PDPR - Partial Dependency Problems Resolved”

Question 4(c) [7 marks]

#

Explain need of Normalization? Discuss about 2NF with example.

Answer:

Need of Normalization:

Second Normal Form (2NF):

• Must be in 1NF
• No partial functional dependencies

Example:

Before 2NF:
StudentCourse(StudentID, CourseID, StudentName, CourseName)

After 2NF:
Student(StudentID, StudentName)
Course(CourseID, CourseName)
Enrollment(StudentID, CourseID)

Mnemonic: “NUID2 - Normalization Unifies Important Data to 2NF”

Question 4(a OR) [3 marks]

#

Consider a following relational schema & give Relational Algebra Expressions for the following queries.

Answer:

Student(Enno,name,age,address)

i. π(name)(σ(address = 'Surat')(Student))
ii. π(name)(σ(age > 30)(Student))

Question 4(b OR) [4 marks]

#

Define 1 NF? Explain 1NF with suitable example.

Answer:

First Normal Form (1NF): Each column contains atomic (indivisible) values, and each column contains values of a single type.

Example:

Before 1NF:
Student(ID, Name, Subjects)
1, John, Math,Science,English

After 1NF:
Student(ID, Name, Subject)
1, John, Math
1, John, Science  
1, John, English

Mnemonic: “ANU - Atomic No-repeat Unique”

Question 4(c OR) [7 marks]

#

Define Transitive Dependency? Explain 3NF with suitable example.

Answer:

Transitive Dependency: Non-prime attribute depends on another non-prime attribute rather than directly on primary key.

Third Normal Form (3NF):

• Must be in 2NF
• No transitive dependencies

graph LR
    A[Student_ID] --> B[DeptCode]
    B --> C[DeptName]
    A -.-> C
    
    D[After 3NF:] 
    E[Student_ID] --> F[DeptCode]
    G[DeptCode] --> H[DeptName]

Mnemonic: “T3ND - Transitive Third Normal Form No Dependencies”

Question 5(a) [3 marks]

#

Define Serializability? Explain rules of serializability?

Answer:

Serializability: Property ensuring concurrent transaction execution produces same result as serial execution.

Mnemonic: “SCV - Serial Conflict View”

Question 5(b) [4 marks]

#

Explain Attributes of Implicit Cursors.

Answer:

Mnemonic: “FNRI - Found NotFound RowCount IsOpen”

Question 5(c) [7 marks]

#

Explain two phase locking protocol with suitable example.

Answer:

Two Phase Locking (2PL): Protocol ensuring serializability through two phases.

Example:

Transaction T1:
1. Lock(A) - Growing
2. Lock(B) - Growing  
3. Read(A), Write(A)
4. Unlock(A) - Shrinking
5. Read(B), Write(B)
6. Unlock(B) - Shrinking

graph LR
    A[Start] --> B[Growing Phase<br/>Acquire Locks]
    B --> C[Lock Point<br/>Max Locks Held]
    C --> D[Shrinking Phase<br/>Release Locks]
    D --> E[End]

Mnemonic: “2PGS - Two Phase Growing Shrinking”

Question 5(a OR) [3 marks]

#

Explain ACID properties of transaction.

Answer:

Mnemonic: “ACID - All Changes In Database”

Question 5(b OR) [4 marks]

#

Define Triggers? Explain advantages of triggers.

Answer:

Triggers: Special stored procedures that automatically execute in response to database events.

Mnemonic: “ADAS - Automatic Data Auditing Security”

Question 5(c OR) [7 marks]

#

List down problems of concurrency control. Explain any two with suitable example.

Answer:

Problems of Concurrency Control:

Example 1 - Lost Update:

T1: Read(A=100)
T2: Read(A=100)  
T1: A = A + 50 (A=150)
T2: A = A + 30 (A=130) <- Lost T1's update
T1: Write(A=150)
T2: Write(A=130) <- Final value wrong

Example 2 - Dirty Read:

T1: Write(A=200) [Not committed]
T2: Read(A=200)  <- Dirty read
T1: Rollback     <- A back to original
T2: Continues with wrong value

Mnemonic: “LDNP - Lost Dirty Non-repeatable Phantom”