1. Define candidate key and give an example.
Answer:
A candidate key is an attribute or set of attributes that can uniquely identify a tuple in a relation.
A relation can have multiple candidate keys, but one is chosen as the primary key.
Example: In a Student table with attributes (StudentID, RollNo, Email), each of these can be a
candidate key if all are unique.
2. List various responsibilities performed by the database management system.
Answer:
• Data storage, retrieval, and update
• User access control and security
• Backup and recovery
• Concurrency control
• Data integrity management
• Data independence
• Data abstraction
3. Explain the syntax of CREATE TABLE command with the help of an example.
Answer:
Syntax:
sql
CopyEdit
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
...
);
Example:
sql
CopyEdit
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Email VARCHAR(100) UNIQUE
);
4. What is transitive dependency?
Answer:
A transitive dependency occurs when a non-prime attribute depends on another non-prime
attribute rather than depending on the primary key.
Example: If A → B and B → C, then A → C is a transitive dependency.
5. How can we compute candidate key from a given set of functional dependencies in a
relation?
Answer:
To compute the candidate key:
1. Identify all attributes.
2. Determine the closure of attribute sets using functional dependencies.
3. The attribute set whose closure includes all attributes is a superkey.
4. Find the minimal superkeys (with no redundant attributes) — these are candidate keys.
6. Differentiate between specialization and generalization.
Answer:
Aspect Specialization Generalization
Direction Top-down Bottom-up
Purpose Create sub-classes from a super-class Combine sub-classes into a super-class
Example Employee → Engineer, Manager Car, Bike → Vehicle
7. Differentiate between inner join and outer join.
Answer:
Join Type Description
Inner Join Returns only matching rows from both tables.
Outer Join Returns matching rows and non-matching rows filled with NULLs.
Types of Outer Join: LEFT, RIGHT, FULL OUTER JOIN
8. Explain transition of states of a transaction.
Answer:
A transaction passes through the following states:
• Active: Transaction is being executed.
• Partially Committed: Last operation executed.
• Committed: Changes are saved permanently.
• Failed: Some error occurred.
• Aborted: Changes are rolled back.
9. Differentiate between authentication and authorization.
Answer:
Term Description
Authentication Verifying the identity of the user.
Authorization Granting access rights to the user.
Authentication comes before authorization.
10. Explain DAC (Discretionary Access Control).
Answer:
DAC is a type of access control where the data owner determines who can access the data.
Permissions are granted or revoked by the user who owns the object (like a table).
Features:
• Flexible but less secure than mandatory access control.
• Used in systems like Windows and UNIX.
11. Differentiate between Traditional File System and Database Management
System (DBMS).
Feature Traditional File System Database Management System (DBMS)
Data Redundancy High Low
Data Integrity Difficult to enforce Easy to enforce with constraints
Data Access Manual or application-dependent SQL and query languages supported
Security Limited Granular control via user roles
Concurrency Control Not supported Supported
Backup and Recovery Manual Automatic and managed
Example File storage in OS MySQL, Oracle, PostgreSQL
12. Illustrate various operations of Relational Algebra with the help of an
example each.
1. Selection (σ):
Selects rows that satisfy a condition.
Example:
σ_dept = 'IT'_(Employee)
2. Projection (π):
Selects specific columns.
Example:
π_name, salary_(Employee)
3. Union (∪):
Combines tuples from two relations.
Example:
Employee ∪ Manager
4. Set Difference (−):
Finds tuples in one relation but not in another.
Example:
Employee − Manager
5. Cartesian Product (×):
Combines all tuples from two relations.
Example:
Employee × Department
6. Rename (ρ):
Renames a relation or its attributes.
Example:
ρ(EmpName/Name, Emp)
7. Join (⨝):
Combines related tuples.
Example:
Employee ⨝ Employee.dept_id = [Link]
13. Discuss Lock-Based Concurrency Control. How can it lead to deadlock?
Lock-based concurrency control ensures that multiple transactions do not access the same data
concurrently in conflicting ways.
Types of locks:
• Shared Lock (S): Read-only access.
• Exclusive Lock (X): Read and write access.
Working:
A transaction must acquire a lock on data before accessing it. After use, it must release the lock.
Deadlock:
Occurs when two or more transactions are waiting for each other to release locks.
Example:
• T1 holds a lock on A, waiting for B
• T2 holds a lock on B, waiting for A
This leads to circular wait and deadlock.
Prevention/Detection: Use wait-die, wound-wait protocols, or timeout strategies.
14. Discuss the role of B-Tree in storage management.
B-Tree is a self-balancing tree data structure used in databases for indexing and fast data
retrieval.
Role in Storage:
• Maintains sorted data and allows searches, sequential access, insertions, and deletions in
logarithmic time.
• Keeps data balanced by ensuring minimal disk I/O.
• Stores large volumes of data by reducing the height of the tree.
• Reduces the need to scan the entire data.
Advantages:
• Fast retrieval from secondary storage.
• Efficient for range queries.
• Balanced tree structure ensures consistent performance.
15. Explain ER Diagram with the help of an example.
Entity-Relationship (ER) Diagram is a visual representation of entities and their relationships
in a database.
Components:
• Entities: Represented as rectangles (e.g., Student, Course)
• Attributes: Represented as ovals (e.g., Name, ID)
• Relationships: Represented as diamonds (e.g., Enrolls)
Example:
objectivec
CopyEdit
Student ── Enrolls ── Course
| |
(ID, Name) (CID, Title)
Use:
• Helps in database design and normalization.
• Facilitates understanding between developers and stakeholders.
16. What is normalization? Explain different types of normalizations with the
help of an example.
Normalization is the process of organizing data in a database to reduce redundancy and improve
data integrity. It involves decomposing tables to eliminate anomalies.
Normal Forms:
1. First Normal Form (1NF):
o Removes multivalued attributes.
o Ensures atomicity (each cell has a single value).
o Example:
Student Courses
John Math, Science
2. → Convert to:
Student Course
John Math
John Science
2. Second Normal Form (2NF):
o Should be in 1NF.
o Removes partial dependency (non-prime attribute depends on a part of the
composite key).
o Example:
If a table has (StudentID, CourseID) as composite key and StudentName depends
only on StudentID, it violates 2NF.
3. Third Normal Form (3NF):
o Should be in 2NF.
o Removes transitive dependency.
o Example:
| StudentID | Name | DeptID | DeptName |
→ DeptName depends on DeptID, not directly on StudentID.
4. Boyce-Codd Normal Form (BCNF):
o A stricter version of 3NF.
o Every determinant must be a candidate key.
17. Explain in detail log-based recovery. Explain the recovery algorithm using
redo and undo phase.
Log-Based Recovery uses a log file to record all database transactions. It helps to restore the
system in case of a crash.
Log Entry Format:
php-template
CopyEdit
<START T1>
<T1, A, old_value, new_value>
<COMMIT T1>
Undo Phase:
• For all transactions that did not commit before crash.
• Undo changes by using the old value from log.
Redo Phase:
• For all committed transactions.
• Redo changes to ensure all committed data is saved.
Steps in Recovery Algorithm:
1. Scan log backwards for transactions without commit → UNDO.
2. Scan log forward for committed transactions → REDO.
3. Ensure atomicity and consistency.
18. Write short notes on:
a. Web Databases:
• Web databases are hosted on the internet and accessed through web applications.
• Used for e-commerce, online forms, and content management.
• Examples: MySQL, MongoDB used with PHP/[Link] web applications.
Features:
• Remote access
• Real-time data availability
• Scalability
b. Distributed Databases:
• A distributed database is stored across multiple physical locations but appears as a single
logical database.
Advantages:
• Improved reliability and availability
• Faster local access
• Scalability
Types:
• Homogeneous (same DBMS)
• Heterogeneous (different DBMS)