(SEM VI) THEORY EXAMINATION 2023-24 BASICS OF DATA BASE MANAGEMENT SYSTEM

BASICS OF DATABASE MANAGEMENT SYSTEM – KOE067H

Section-wise Important Questions & Ready Answers

SECTION A

(Attempt all questions in brief – 2 marks each)

(a) Super Key, Candidate Key and Primary Key

A super key is a set of one or more attributes that uniquely identifies a tuple in a relation. A candidate key is a minimal super key, meaning no attribute can be removed without losing uniqueness. A primary key is one of the candidate keys selected to uniquely identify records in a table.

(b) Schema and Instance

A schema defines the logical structure of a database, including tables, attributes, and relationships. An instance represents the actual data stored in the database at a particular moment. The schema remains relatively stable, while instances change frequently.

(c) Entity Integrity vs Referential Integrity

Entity integrity ensures that the primary key of a table is unique and never null. Referential integrity ensures that foreign key values in a table must either match a primary key value in another table or be null, maintaining consistency between related tables.

(d) Relational Algebra and Its Significance

Relational algebra is a procedural query language consisting of operations such as selection, projection, union, difference, and join. It forms the theoretical foundation of relational databases and helps in query optimization.

(e) Views and Indexes in SQL

A view is a virtual table created using a SQL query to present data in a customized form. An index is a database object that improves data retrieval speed by providing fast access paths to rows in a table.

(f) Creating and Altering Tables in SQL

Tables are created using the CREATE TABLE command by defining attributes and constraints. Existing tables can be modified using the ALTER TABLE command to add, delete, or modify columns and constraints.

(g) Serializability in Transaction Processing

Serializability ensures that concurrent transaction execution produces the same result as some serial execution order. It maintains database consistency in multi-user environments.

(h) Two-Phase Commit Protocol

The two-phase commit protocol ensures atomicity in distributed databases. In the prepare phase, all participants vote to commit or abort. In the commit phase, the coordinator decides the final outcome and informs all participants.

(i) Role of Backup and Recovery Techniques

Backup and recovery techniques protect databases from data loss caused by system failures, crashes, or disasters. They help restore the database to a consistent state.

(j) Importance of Spatial and Temporal Databases

Spatial databases manage geographic data, while temporal databases store time-dependent data. Both are crucial in applications such as GIS, traffic systems, climate studies, and historical data analysis.

SECTION B

(Attempt any three – 10 marks each)

2(a) Generalization and Aggregation in ER Model

Generalization is a bottom-up approach where common features of multiple entities are combined into a higher-level entity, reducing redundancy. Aggregation represents a relationship between relationships, useful when a relationship itself needs attributes. Both simplify complex data relationships and are represented clearly in ER diagrams.

2(b) Tuple Relational Calculus vs Domain Relational Calculus

Tuple relational calculus is based on selecting tuples that satisfy a given condition, while domain relational calculus focuses on values from attribute domains. Tuple calculus is more intuitive, whereas domain calculus provides fine-grained control over attributes.

2(c) Database Triggers in PL/SQL

Triggers are procedures automatically executed in response to database events. BEFORE triggers execute before operations, AFTER triggers execute after operations, and INSTEAD OF triggers replace the triggering action. Triggers enforce business rules and maintain data integrity.

2(d) Log-Based Recovery in DBMS

Log-based recovery maintains a log of all database changes. Undo logs reverse incomplete transactions, while redo logs reapply committed changes. Checkpoints reduce recovery time by limiting the number of log entries to be processed.

2(e) Centralized vs Client-Server DBMS Architecture

Centralized DBMS stores data and processing at a single location, offering simplicity but limited scalability. Client-server architecture distributes processing between clients and servers, improving performance, scalability, and maintenance at the cost of complexity.

SECTION C

3(a) ER Diagram to Relational Tables

Entities are converted into tables, attributes into columns, and primary keys are assigned. Relationships are represented using foreign keys. For many-to-many relationships, a separate relation is created to maintain associations.

3(b) Roles and Responsibilities of a DBA

A Database Administrator ensures database security, performance tuning, backup and recovery, user management, and integrity enforcement. Tools such as monitoring utilities, backup software, and access control mechanisms are commonly used.