Database Management (4331603) - Winter 2024 Solution

Question 1(a) [3 marks]

Explain three-level database architecture.

Answer:

Table:

Diagram:

• External Level: Individual user views and specific application requirements
• Conceptual Level: Complete database schema without storage details
• Internal Level: Physical storage structures and access paths

Mnemonic: "ECI - Every Computer Interface"

Question 1(b) [4 marks]

Explain Total Participation and Partial Participation with example.

Answer:

Table:

Diagram:

• Total Participation: All students must be enrolled in at least one course
• Partial Participation: Not all employees manage a department
• Double lines indicate total participation constraints
• Single lines show partial participation relationships

Mnemonic: "Total = Two lines, Partial = Plain line"

Question 1(c) [7 marks]

Explain advantages of DBMS over file management systems.

Answer:

Table:

• Reduced Data Redundancy: Eliminates duplicate data storage across applications
• Data Consistency: Ensures uniform data across all applications
• Data Independence: Applications independent of data structure changes
• Concurrent Access: Multiple users can access data simultaneously
• Security Control: User authentication and authorization mechanisms
• Backup and Recovery: Automatic data protection and restoration
• Data Integrity: Constraint enforcement maintains data quality

Mnemonic: "RDCCSBI - Really Don't Copy, Control, Secure, Backup, Integrate"

Question 1(c OR) [7 marks]

List out various data models. Explain any two in brief.

Answer:

Data Models List:

• Hierarchical Data Model
• Network Data Model
• Relational Data Model
• Object-Oriented Data Model
• Entity-Relationship Model

Table:

Relational Data Model:

• Structure: Data organized in tables (relations)
• Components: Tuples (rows), attributes (columns), domains
• Operations: Select, project, join operations available

Network Data Model:

• Structure: Graph-based with owner-member relationships
• Navigation: Explicit links between record types
• Flexibility: Many-to-many relationships supported naturally

Mnemonic: "HNROE - Have Network Relational Object Entity"

Question 2(a) [3 marks]

Explain Mapping Cardinalities.

Answer:

Table:

Diagram:

• Cardinality constraints define relationship participation limits
• Maximum cardinality specifies upper bound of associations
• Helps in database design and relationship modeling

Mnemonic: "OMOM - One, One-Many, One-Many, Many-Many"

Question 2(b) [4 marks]

Explain Outer Join operation in Relational Algebra.

Answer:

Table:

Example:

EMPLOYEE ⟕ DEPARTMENT
- Includes all employees
- NULL values for employees without departments

• Preserves unmatched tuples from specified relation(s)
• NULL values fill missing attribute values
• Three types: Left, Right, and Full outer joins
• Useful for reporting incomplete data relationships

Mnemonic: "LRF - Left Right Full outer joins"

Question 2(c) [7 marks]

Explain concept of Specialization and Generalization with example.

Answer:

Table:

Diagram:

Specialization:

• Process: Creating subclasses from superclass
• Inheritance: Subclasses inherit all superclass attributes
• Additional attributes: Subclasses have specific properties

Generalization:

• Process: Creating superclass from common subclass features
• Abstraction: Identifies common attributes and relationships
• Simplification: Reduces complexity through hierarchy

Mnemonic: "SG-TD-BU - Specialization General-To-Detail, Bottom-Up"

Question 2(a OR) [3 marks]

Explain different types of Keys in Relational Algebra.

Answer:

Table:

• Super Key: Uniquely identifies tuples, may have extra attributes
• Candidate Key: Minimal super key without redundant attributes
• Primary Key: Selected candidate key for entity identification
• Foreign Key: Establishes referential integrity between tables

Mnemonic: "SCPF - Super Candidate Primary Foreign"

Question 2(b OR) [4 marks]

Explain types of attributes in ER-diagram with suitable example.

Answer:

Table:

Diagram:

goat

• Simple attributes are atomic and indivisible
• Composite attributes have meaningful sub-parts
• Derived attributes computed from other attribute values
• Multi-valued attributes store multiple values per entity

Mnemonic: "SCDM - Simple Composite Derived Multi-valued"

Question 2(c OR) [7 marks]

Explain SELECT, PROJECT, UNION and SET-INTERSECTION operation with suitable example.

Answer:

Table:

Examples:

SELECT Operation:

σ(age > 25)(STUDENT)
- Returns students older than 25 years

PROJECT Operation:

π(name, course)(STUDENT)  
- Returns only name and course columns

UNION Operation:

SCIENCE_STUDENTS ∪ ARTS_STUDENTS
- Combines students from both streams

INTERSECTION Operation:

MALE_STUDENTS ∩ SPORTS_STUDENTS
- Returns male students who play sports

Mnemonic: "SPUI - Select Project Union Intersection"

Question 3(a) [3 marks]

Differentiate Primary Key and Foreign Key constraint.

Answer:

Table:

• Primary Key: Ensures entity integrity within table
• Foreign Key: Maintains referential integrity between tables
• Uniqueness: Primary keys unique, foreign keys can repeat
• NULL handling: Primary keys never NULL, foreign keys may be NULL

Mnemonic: "PU-FN - Primary Unique, Foreign Nullable"

Question 3(b) [4 marks]

Explain DUAL table and SYSDATE with example.

Answer:

Table:

DUAL Table:

• Virtual table with one row and one column
• Used for testing expressions and functions
• Oracle-specific pseudo table

SYSDATE Function:

• Returns current system date and time
• Automatic update with system clock
• Date/time operations supported

Examples:

SQL

Mnemonic: "DT-ST - DUAL Testing, SYSDATE Time"

Question 3(c) [7 marks]

Write SQL queries to use various numeric functions:

Answer:

Table:

SQL Queries:

SQL

Mnemonic: "TACFSEPM - TRUNC ABS CEIL FLOOR SQRT EXP POWER MOD"

Question 3(a OR) [3 marks]

Explain Unique and Check Constraint with suitable example.

Answer:

Table:

Examples:

SQL

• UNIQUE constraint ensures no duplicate values in column
• CHECK constraint validates data against specified conditions
• Multiple constraints can be applied to single column

Mnemonic: "UC-DV - Unique no Copy, Check Validates"

Question 3(b OR) [4 marks]

Explain structure of PL/SQL block.

Answer:

Table:

Diagram:

DECLARE
    -- Variable declarations
BEGIN  
    -- Executable statements
EXCEPTION
    -- Error handling
END;

• DECLARE section: Variable and cursor declarations
• BEGIN-END: Mandatory executable section
• EXCEPTION section: Error handling routines
• Nested blocks: PL/SQL blocks can be nested

Mnemonic: "DBE-E - Declare Begin Exception End"

Question 3(c OR) [7 marks]

Consider the following table and solve queries:

Answer:

I) Create the BRANCH table:

SQL

II) Create the EMPLOYEE table:

SQL

III) Find employees in Ahmedabad branch:

SQL

IV) Find employees born in 1998:

SQL

V) Find female employees with salary > 5000:

SQL

VI) Find address where Ajay works:

SQL

Mnemonic: "CBEFFA - Create Branch Employee Find Female Address"

Question 4(a) [3 marks]

Explain Referential Integrity with suitable example.

Answer:

Table:

Diagram:

• Referential integrity ensures foreign key values exist in referenced table
• Orphan records prevented by constraint enforcement
• Cascade operations maintain consistency during updates/deletes

Mnemonic: "RIO - Referential Integrity prevents Orphans"

Question 4(b) [4 marks]

Differentiate Partial and Full Functional Dependency.

Answer:

Table:

Examples:

Partial Functional Dependency:

(StudentID, CourseID) → StudentName
StudentName depends only on StudentID, not CourseID

Full Functional Dependency:

(StudentID, CourseID) → Grade  
Grade depends on both StudentID and CourseID

• Partial dependency causes data redundancy and anomalies
• Full dependency required for proper normalization
• 2NF eliminates partial functional dependencies

Mnemonic: "PF-CF - Partial Few, Complete Full"

Question 4(c) [7 marks]

Explain 3rd Normal Form with example.

Answer:

3rd Normal Form Requirements:

1. Must be in 2NF
2. No transitive dependencies
3. Non-key attributes depend only on primary key

Table Before 3NF:

Problems:

• Transitive dependency: StudentID → CourseID → InstructorName
• Update anomaly: Instructor name change requires multiple updates
• Delete anomaly: Removing student may lose instructor information

3NF Solution:

STUDENT Table:

COURSE Table:

INSTRUCTOR Table:

Mnemonic: "3NF-NT - 3rd Normal Form No Transitives"

Question 4(a OR) [3 marks]

Explain Importance of Normalization.

Answer:

Table:

• Data redundancy minimized through proper table decomposition
• Update anomalies eliminated by removing duplicate information
• Storage space optimized through normalized structure
• Data integrity maintained with referential constraints
• Maintenance simplified with logical table organization

Mnemonic: "RESIM - Redundancy Eliminated, Storage Improved, Maintenance"

Question 4(b OR) [4 marks]

Differentiate Prime Attributes and Non-Prime Attributes.

Answer:

Table:

Example:

ENROLLMENT (StudentID, CourseID, Grade, Semester)
Candidate Key: (StudentID, CourseID)

Prime Attributes: StudentID, CourseID
Non-Prime Attributes: Grade, Semester

• Prime attributes participate in candidate key formation
• Non-Prime attributes provide additional entity information
• Functional dependencies between these determine normal forms
• 2NF requires no partial dependencies of non-prime on prime attributes

Mnemonic: "PN-KD - Prime in Key, Non-prime for Data"

Question 4(c OR) [7 marks]

Explain 2nd Normal Form with example.

Answer:

2nd Normal Form Requirements:

1. Must be in 1NF
2. No partial functional dependencies
3. All non-key attributes fully depend on primary key

Table Before 2NF:

Problems:

• Partial Dependencies: StudentID → StudentName, CourseID → CourseName
• Update Anomaly: Student name change requires multiple updates
• Insert Anomaly: Cannot add course without student enrollment

2NF Solution:

STUDENT Table:

COURSE Table:

ENROLLMENT Table:

Mnemonic: "2NF-FD - 2nd Normal Form Full Dependencies"

Question 5(a) [3 marks]

Explain Transaction states with proper diagram.

Answer:

Diagram:

Table:

• Active state: Transaction currently executing operations
• Partially committed: All operations executed, waiting for commit
• Failed state: Error occurred, transaction cannot continue

Mnemonic: "APCFA - Active Partial Commit Fail Abort"

Question 5(b) [4 marks]

Explain any two DDL commands with a suitable example.

Answer:

Table:

CREATE Command:

SQL

ALTER Command:

SQL

• CREATE establishes new database structures
• ALTER modifies existing table definitions
• DDL commands auto-commit changes
• Schema changes affect data structure permanently

Mnemonic: "CA-NM - CREATE Adds, ALTER Modifies"

Question 5(c) [7 marks]

Explain ACID Properties in detail.

Answer:

Table:

Atomicity:

• All operations in transaction execute completely or not at all
• Rollback mechanism undoes partial changes on failure
• Example: Bank transfer requires both debit and credit operations

Consistency:

• Database state remains valid before and after transaction
• Integrity constraints maintained throughout execution
• Example: Account balance never becomes negative

Isolation:

• Concurrent transactions do not interfere with each other
• Locking mechanisms prevent interference
• Example: Two users updating same account simultaneously

Durability:

• Committed changes survive system failures
• Write-ahead logging ensures recovery capability
• Example: Transaction survives power outage after commit

Mnemonic: "ACID - Atomicity Consistency Isolation Durability"

Question 5(a OR) [3 marks]

What is two phase locking technique?

Answer:

Table:

Diagram:

goat

• Two phases: Growing (lock acquisition) and Shrinking (lock release)
• No lock upgrades allowed after first unlock operation
• Prevents deadlocks when properly implemented
• Serializability guarantee for concurrent transactions

Mnemonic: "2PL-GS - Two Phase Locking Growing Shrinking"

Question 5(b OR) [4 marks]

Explain any two DML commands with a suitable example.

Answer:

Table:

INSERT Command:

SQL

UPDATE Command:

SQL

• INSERT adds new rows to table
• UPDATE modifies existing row values
• WHERE clause specifies update conditions
• DML commands require explicit commit

Mnemonic: "IU-AM - INSERT Adds, UPDATE Modifies"

Question 5(c OR) [7 marks]

List problems of concurrency control and explain any two in detail.

Answer:

Concurrency Control Problems:

1. Lost Update Problem
2. Dirty Read Problem
3. Unrepeatable Read Problem
4. Phantom Read Problem
5. Inconsistent Analysis Problem

Table:

Lost Update Problem:

• Scenario: Two transactions read same data, modify it, and write back
• Example:
  • T1 reads account balance: $1000
  • T2 reads account balance: $1000
  • T1 adds $100, writes$1100
  • T2 subtracts $50, writes$950
  • Result: T1's update lost, final balance incorrect

Dirty Read Problem:

• Scenario: Transaction reads data modified by another uncommitted transaction
• Example:
  • T1 updates account balance from $1000 to$1500
  • T2 reads balance as $1500 (uncommitted data)
  • T1 fails and rolls back to $1000
  • Result: T2 used incorrect data for calculations

Solutions:

• Locking protocols: Prevent simultaneous access to same data
• Isolation levels: Control visibility of uncommitted changes
• Timestamp ordering: Order transactions based on timestamps
• Multi-version concurrency: Maintain multiple data versions

Mnemonic: "LDUI - Lost Dirty Unrepeatable Inconsistent"