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.
