1.
Write briefly:
a) What is the benefit of foreign key? Give an example.
The main benefit of a foreign key is enforcing referential integrity, ensuring that relationships
between tables are consistent. It prevents actions that would destroy links between tables,
like deleting a customer while their orders still exist. [cite_start]Example: In a schema with
Orders(OrderID, CustomerID) and Customer(CustomerID), the CustomerID in the Orders
table is a foreign key referencing the Customer table.
b) What is benefit of implementing data abstraction?
[cite_start]The benefit of data abstraction is to hide complex physical storage details from
users, making the system easier to use and manage. It simplifies the user's interaction by
presenting only the conceptual view of the data. This also facilitates data independence,
allowing changes at one level (like physical storage) without affecting the higher levels (like
user applications).
c) Write SQL commands to create an employee table and then add one record to the same
table.
CREATE TABLE Employee (
EID INT PRIMARY KEY,
Name VARCHAR(50),
Dept VARCHAR(50)
);
INSERT INTO Employee (EID, Name, Dept)
VALUES (101, 'Alice', 'HR');
[cite_start]The first command creates the table schema, and the second inserts a single
record into it.
d) Explain the concept of lossy and lossless decomposition.
Decomposition is replacing a single relation with multiple smaller relations. A lossless
decomposition is desirable because it guarantees that the original relation can be perfectly
reconstructed (joined back) from the new relations without generating spurious tuples (false
data). [cite_start]A lossy decomposition is undesirable as the join operation does not yield
the original relation, leading to information loss or incorrect data.
e) What do you mean by composite entity? Give an example of composite entity with the
help of ER-Diagram symbols.
A composite entity is not a standard ER term; the term typically refers to a relationship
translated into a table in the relational model, or an entity with a composite attribute. A
composite attribute is an attribute that can be broken down into smaller, meaningful
sub-parts. Example: The attribute Address is composite, made up of Street, City, and
Pincode. [cite_start]In an ER Diagram, it's shown as an oval connected to another oval
representing the entity.
f) What is a join? Give an example of right outer join.
[cite_start]A Join operation combines rows from two or more tables based on a related
column between them. A Right Outer Join returns all records from the right table, and the
matched records from the left table. If there is no match, the result will have NULL from the
left side. [cite_start]Example: SELECT * FROM Employee RIGHT OUTER JOIN Department
ON [Link] = [Link];
g) What do you mean by partially committed state? How can a process move from partially
committed state to failed state?
[cite_start]A transaction is in the partially committed state after the final instruction has been
executed, but the changes have not yet been permanently written to the disk. [cite_start]A
process moves from the partially committed state to the failed state if a system failure (e.g.,
a power cut, hardware error) or a logic error occurs before the final commit-record is written
to the stable log.
h) What is non-recoverable schedule? Give an example of non-recoverable schedule.
[cite_start]A non-recoverable schedule is one where a transaction T_i reads data written by
another transaction T_j, and T_i commits before T_j commits. If T_j later aborts, the changes
read by T_i are invalid, but T_i has already committed, making the database inconsistent.
Example: S: R_1(A), W_2(A), C_2, W_1(A), A_1. (This example is modified to fit the
definition for non-recoverable: W_1(A), R_2(A), C_2, A_1). T_2 reads A written by T_1, T_2
commits, then T_1 aborts. [cite_start]T_2 is stuck with bad data.
i) Differentiate between authentication and authorization.
Authentication is the process of verifying a user's identity (e.g., by checking a username and
password) to ensure they are who they claim to be. Authorization is the process of
determining what an authenticated user is permitted to do within the system, such as
granting permissions (SELECT, UPDATE) on specific data objects. [cite_start]Authentication
answers "Who are you?", and authorization answers "What can you do?".
j) List the various benefits of using RBAC.
[cite_start]RBAC (Role-Based Access Control) provides several benefits:
* Simplified Management: Permissions are assigned to roles, not individual users,
simplifying administration.
* Scalability: Easily manage access for a large number of users by assigning them to
existing roles.
* Compliance: Helps enforce organizational security policies and meet regulatory
requirements.
* [cite_start]Least Privilege: Users only get the permissions necessary for their job function.
🛠️ Section-B (5 Marks Each - Approx. 100 Words)
2. What is relational algebra? Explain the various operations of relational algebra with the
help of an example of each.
Relational Algebra is a procedural query language that operates on relations (tables) and
produces a new relation as a result. [cite_start]It forms the theoretical basis for SQL and is
used internally by DBMS for query optimization.
Key Operations:
* Select (\sigma): Filters rows (tuples) that satisfy a given predicate. Example:
\sigma_{\text{Dept}='HR'}(\text{Employee}) selects employees in the 'HR' department.
* Project (\pi): Selects specific columns (attributes). Example: \pi_{\text{Name,
Salary}}(\text{Employee}) selects only the Name and Salary columns.
* Union (\cup): Combines two relations (must have the same schema). Example:
\text{Students} \cup \text{Alumni}.
* Set Difference (-): Returns tuples in the first relation that are not in the second. Example:
\text{AllEmployees} - \text{Managers}.
* Cartesian Product (\times): Combines every tuple of the first relation with every tuple of the
second. Example: \text{Employee} \times \text{Department}.
* Rename (\rho): Gives a new name to a relation or an attribute. [cite_start]Example:
\rho_{\text{Staff}}(\text{Employee}).
3. Explain in detail, various algorithms for query optimization.
[cite_start]Query optimization is the process of finding the most efficient way to execute an
SQL query by evaluating multiple execution plans and choosing the one with the minimum
cost (I/O, CPU time).
Common Optimization Algorithms/Techniques:
* Heuristic Optimization (Rule-Based): Uses a set of predefined rules to order operations.
For instance, the heuristic "Perform Select and Project operations as early as possible"
reduces the size of intermediate relations, saving time for subsequent operations like Join.
* Cost-Based Optimization: The optimizer estimates the cost of various execution plans
using statistics (e.g., number of tuples, index availability) and cost formulas. It typically uses
dynamic programming to explore the space of join orders (Join Ordering). Example: It
compares the cost of a Nested-Loop Join vs. a Hash Join and selects the cheapest.
[cite_start]The optimizer aims to minimize the cost, usually measured by the number of disk
accesses required.
4. What is the need of concurrency control protocols? Explain any 1 concurrency control, in
detail.
[cite_start]Concurrency control protocols are necessary to ensure that concurrent execution
of transactions maintains the Isolation and Consistency properties of ACID. Without them,
problems like the lost update problem (one transaction overwriting another's changes) or the
dirty read problem (reading uncommitted data) can corrupt the database integrity.
Two-Phase Locking (2PL) Protocol:
This is a lock-based protocol where a transaction can acquire locks but cannot release any
lock until it has acquired all the locks it needs. It has two phases:
* Growing Phase: The transaction is allowed to obtain locks (shared or exclusive) but
cannot release any.
* Shrinking Phase: The transaction is allowed to release locks but cannot acquire any new
ones. [cite_start]The 2PL protocol guarantees serializability, meaning the concurrent
schedule is equivalent to some serial execution, thus preventing integrity problems.
5. What is the benefit of implementing checkpoints? Explain which transaction requires undo
and which transaction requires redo with the help of a common example.
[cite_start]The benefit of implementing checkpoints is to reduce the time required for
recovery after a system crash. A checkpoint is a log record that marks a point where all dirty
blocks (modified data in memory) have been written to stable storage. During recovery, the
DBMS only needs to process log records from the last checkpoint, rather than the entire log.
Recovery Example: Consider a crash occurred.
* Transactions Requiring UNDO: Transactions that were active or in the partially committed
state at the time of the crash and did not write their commit record to the log. Their changes
must be undone (rolled back) because they didn't complete successfully.
* Transactions Requiring REDO: Transactions that completed their execution and wrote a
commit record to the log before the crash. [cite_start]Their changes must be redone
because, although committed, their final updates might not have been written from memory
to the disk before the crash.
6. What is SQL injection and how it can be controlled? Give a suitable example.
[cite_start]SQL Injection (SQLi) is a web security vulnerability where an attacker uses a web
application's input field to maliciously insert or "inject" part of an SQL query into a command
that is eventually passed to the database. This can allow the attacker to bypass
authentication, view sensitive data, or modify/delete database contents.
Control Methods:
* Parameterized Queries (Prepared Statements): This is the most effective defense. The
SQL structure is defined first, and then user input is passed as parameters. The database
driver ensures the input is treated as data, not as executable code.
* Input Validation: Strictly validate and sanitize all user input, checking for expected data
types and lengths, and stripping malicious characters (like single quotes or semicolons).
* Principle of Least Privilege: Configure the database account used by the web application
with only the minimum necessary permissions.
Example of Prevention (using Python/SQL):
Vulnerable: [Link]("SELECT * FROM users WHERE username = '" + user_input +
"'")
[cite_start]Safe (Prepared Statement): [Link]("SELECT * FROM users WHERE
username = %s", (user_input,))
📚 Section-C (10 Marks Each - Approx. 150 Words)
7. Explain in detail, various components of the database architecture.
Database architectures describe how the data is stored and how users and applications
interact with the DBMS. The most common is the Three-Schema Architecture, which
separates the user interface from the physical storage of data.
The architecture has three main components:
* External Level (View Level): This is the highest level, where end-users and applications
interact. It describes only the relevant part of the database to a particular user, hiding the
rest of the database for security and simplicity. Each user may have a different External
Schema or view.
* Conceptual Level (Logical Level): This middle level describes the entire database structure
for a community of users. It defines what data is stored in the database, the relationships
among the data, and all the integrity constraints. It is independent of how the data is
physically stored (physical independence). The Conceptual Schema is defined by the
database administrator (DBA).
* Internal Level (Physical Level): This is the lowest level, which describes how the data is
actually stored on the storage devices. It covers details like data compression, file
organizations (sequential, indexed, etc.), and the physical records and indexing structures
used. The Internal Schema defines the storage details.
The mappings between these levels allow the architecture to achieve data independence.
8. What is serializability? Explain in detail, conflict and view serializability with the help of an
example of each.
Serializability is the fundamental correctness criterion for concurrent transactions.
[cite_start]A concurrent schedule is serializable if its effect is equivalent to that of some serial
schedule (where transactions execute one after the other without interleaving). This ensures
that isolation is maintained and the database remains consistent.
1. Conflict Serializability:
A schedule S is conflict serializable if it can be transformed into a serial schedule by
swapping only non-conflicting operations. Two operations from different transactions are
conflicting if they access the same data item and at least one of them is a write operation
(W-R, R-W, or W-W conflict).
Example: S_1: R_1(A), W_1(A), R_2(A), W_2(A). The sequence of conflicting operations
(W_1(A) before R_2(A) and W_2(A)) is maintained, so S_1 is equivalent to T_1 followed by
T_2 and is conflict serializable. [cite_start]The scheduler uses a Precedence Graph (or
serialization graph) to check for cycles; if no cycle exists, the schedule is conflict serializable.
2. View Serializability:
A schedule S is view serializable if it is equivalent to a serial schedule S' in terms of the initial
and final values of all data items, and any transaction that reads a value written by another
transaction in S reads the same value in S'. View serializability is a weaker condition than
conflict serializability. While all conflict serializable schedules are view serializable, the
reverse is not true. It is NP-complete to check for view serializability, making it difficult to use
in practical DBMS implementations, which typically rely on conflict serializability.
[cite_start]Example: Schedules involving "blind writes" (a transaction writes without reading
the data first) can be view serializable but not conflict serializable.
9. Write short note on:
a) Various DML commands.
DML (Data Manipulation Language) commands are used for managing and manipulating
data within the database objects (tables) defined by the DDL. These commands are
essential for routine data operations.
The four primary DML commands are:
* SELECT: Used to retrieve (query) data from the database. It is the most frequently used
command, allowing filtering, sorting, and combining data from multiple tables. Example:
SELECT Name, Salary FROM Employee WHERE Dept = 'IT';
* INSERT: Used to add new rows (records) into a table. Example: INSERT INTO
Department (DeptID, Name) VALUES (30, 'Marketing');
* UPDATE: Used to modify existing data within a table. This command usually requires a
WHERE clause to specify which rows to change. Example: UPDATE Employee SET Salary
= Salary * 1.1 WHERE Dept = 'IT';
* DELETE: Used to remove rows from a table. Like UPDATE, it typically uses a WHERE
clause to target specific records. Example: DELETE FROM Employee WHERE EID = 101;
These commands form the backbone of application interaction with the database.
b) Distributed databases
A distributed database is a collection of multiple, logically interconnected databases
physically spread across various sites or computers in a network. The data is managed by a
Distributed Database Management System (DDBMS), which makes the collection of
physically dispersed data appear as a single, centralized database to the user.
Key Features:
* Data Fragmentation: A relation is divided into smaller pieces (fragments), which are stored
at different sites. This helps in efficient query processing.
* Data Replication: Copies of data or fragments are stored at multiple sites. This improves
availability (if one site fails, the data is available elsewhere) and reliability.
Benefits: Increased reliability and availability due to replication; improved performance by
accessing local data; and easier system expansion (scalability) by adding new nodes.
[cite_start]The challenge lies in maintaining global consistency and coordinating distributed
transactions across multiple sites.