Database Management System

Definition of Data Abstraction in DBMS

Data abstraction is the database design technique of hiding complex low-level physical storage details from end-users and developers, presenting them only with simplified logical views relevant to their tasks.

Three Levels of Data Abstraction (ANSI/SPARC Architecture):

1. Physical (Internal) Level: Lowest level; describes how the data is actually stored on disks (byte offsets, B-tree indexes, compression).
2. Logical (Conceptual) Level: Middle level; describes what data is stored in the database and the relationships among data (tables, columns, constraints).
3. View (External) Level: Highest level; describes customized subsets of the database tailored for specific user groups.

Definition of Candidate Key

A Candidate Key is a minimal superkey of a relation—a set of one or more attributes that uniquely identifies every tuple (row) in the relation, such that no proper subset of those attributes is itself a superkey.

Key Properties:

1. Uniqueness: No two distinct tuples can have identical values for the candidate key attributes.
2. Minimality (Irreducibility): If any attribute is removed from the candidate key, the remaining set loses its unique identification property.
3. Primary Key Selection: The database designer designates one candidate key as the Primary Key; remaining candidate keys are called Alternate Keys.

Advantages of Object-Relational Data Model (ORDBMS)

The Object-Relational Data Model combines the relational model (tables, SQL, ACID transactions) with object-oriented paradigms (classes, inheritance, encapsulation).

Key Advantages:

1. Complex User-Defined Types (UDTs): Supports non-atomic complex data types, geospatial coordinates, multimedia images, and composite structures within table columns.
2. Inheritance & Polymorphism: Allows table inheritance (CREATE TABLE SubTable UNDER SuperTable), promoting reuse of attributes and behaviors.
3. Encapsulation with Member Methods: Allows stored functions and methods to be bound directly to structured data types.
4. Preserves Relational Querying: Retains standard declarative SQL querying capabilities and relational indexing.

Syntax to Rename a Table in SQL

In SQL (Data Definition Language - DDL), renaming a table is performed using the ALTER TABLE statement:

Standard ANSI SQL Syntax:

ALTER TABLE old_table_name
RENAME TO new_table_name;

MySQL / Oracle Alternative:

RENAME TABLE old_table_name TO new_table_name;

Example:

ALTER TABLE student_records
RENAME TO students;

Syntax for ORDER BY Clause in SQL

The ORDER BY clause in SQL is used to sort the result-set returned by a SELECT query in ascending or descending order.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name1 [ASC | DESC], column_name2 [ASC | DESC];

• ASC: Sorts in ascending order (default if omitted).
• DESC: Sorts in descending order.

Example:

SELECT EmpID, FirstName, Salary
FROM employees
ORDER BY Salary DESC;

Use of GROUP BY Clause in SQL

The GROUP BY clause in SQL collapses rows that have the same values in specified columns into summary rows (groups).

Primary Uses:

1. Aggregate Calculations: Almost always used in conjunction with aggregate functions (COUNT(), SUM(), AVG(), MIN(), MAX()) to produce grouped summary statistics per category.
2. Filtering Groups via HAVING: Used with the HAVING clause to filter aggregated groups based on summary conditions.

Example:

-- Calculate total salary expense per department
SELECT DeptID, COUNT(*) AS TotalEmployees, SUM(Salary) AS TotalSalary
FROM employees
GROUP BY DeptID;

Different Types of Outer Join Operations in SQL

An Outer Join returns all rows that satisfy the join condition plus unmatched rows from one or both tables, padding missing columns with NULL.

Three Types of Outer Joins:

1. LEFT OUTER JOIN: Returns all rows from the left table, along with matching rows from the right table. If no match exists, right columns are filled with NULL.
2. RIGHT OUTER JOIN: Returns all rows from the right table, along with matching rows from the left table (left columns padded with NULL if no match).
3. FULL OUTER JOIN: Returns all rows from both tables. When no match exists on either side, the missing columns are populated with NULL.

Definition of Lossless-Join Decomposition

A decomposition of relation schema (R) into sub-schemas ({R_1, R_2}) is a lossless-join decomposition if the natural join of the projections on (R_1) and (R_2) yields the exact original relation (R) with no loss of information and no spurious (extraneous) tuples:

$$R_1 \bowtie R_2 = R$$

Necessary & Sufficient Condition:

$$(R_1 \cap R_2) \longrightarrow R_1 \quad \text{or} \quad (R_1 \cap R_2) \longrightarrow R_2$$

The common attributes must form a superkey for at least one of the decomposed tables.

Use of Mapping Cardinalities in Database Design

Mapping cardinalities (or cardinality ratios) specify the number of entities in one entity set that can be associated with the number of entities in another entity set via a relationship set.

Four Types & Uses:

1. One-to-One (1:1): (e.g., Citizen has one NationalID). Can often be merged into a single table.
2. One-to-Many (1:N): (e.g., Department employs many Employees). Foreign key placed on the “many” side.
3. Many-to-One (N:1): Symmetric to 1:N.
4. Many-to-Many (M:N): (e.g., Student enrolls in Course). Requires a separate bridge/junction table.

Definition of Durability Property of a Transaction

Durability (the ‘D’ in ACID properties) guarantees that once a transaction successfully commits, all of its updates and state modifications persist permanently in non-volatile storage, surviving even catastrophic operating system crashes, database restarts, or power failures.

Implementation Mechanism:

Enforced using Write-Ahead Logging (WAL):

• Log records containing “redo” information must be flushed to non-volatile disk before the transaction is acknowledged as committed.
• On recovery from a crash, the recovery manager replays these logs to restore all committed transactions.

Need of Aggregation with Example in ER Modeling

1. Limitation of Basic ER Modeling:

In standard ER modeling, a relationship cannot participate directly in another relationship; relationships can only link entity sets.

2. The Concept & Need of Aggregation:

Aggregation is an abstraction through which relationships are treated as higher-level entities.

• It eliminates redundancy and avoids artificial ternary relationships when an entity set needs to be associated with an existing relationship set.

3. Concrete Example:

Consider an Employee working on a Job at a Branch.

• Relationship: Works_On links Employee and Branch.
• Now, management requires that a Manager monitors/supervises that specific assignment.
• Instead of creating a messy 4-way relationship, we aggregate the Employee-Works_On-Branch relationship into a single composite entity and link it to Manager via the Manages relationship:

+------------------------------------+
|  +----------+      +------------+  |
|  | Employee |-(Works_On)-| Branch| |  <== Aggregated Entity
|  +----------+      +------------+  |
+------------------------------------+
                   |
               (Manages)
                   |
             +-----------+
             |  Manager  |
             +-----------+

ER Diagram for Bank Database System

1. Identified Entities and Attributes:

• Bank: BankCode (PK), BankName, HeadOfficeAddress.
• Branch: BranchID (PK), BranchName, City, BankCode (FK).
• Account: AccountNo (PK), AccountType, Balance.
• Loan: LoanNo (PK), LoanAmount, InterestRate.
• Customer: CustomerID (PK), Name, Address, Phone.

2. Structural Relationships:

• Bank Has Branch (1:N cardinality; each branch belongs to one bank).
• Branch Maintains Account (1:N cardinality; branch manages multiple customer accounts).
• Branch Issues Loan (1:N cardinality; branch issues multiple loans).
• Customer Holds Account (M:N; customer can have multiple accounts, accounts can be joint).
• Customer Borrows Loan (M:N).

3. ER Diagram:

+--------+       1            N       +----------+
|  Bank  |--------(Has)--------------|  Branch  |
+--------+                            +----------+
                                       |        |
                       1               |        | 1
        +--------------(Maintains)-----+        +------(Issues)-------------+
        |                                                                   |
        v N                                                                 v N
  +-----------+              M                 N                      +-----------+
  |  Account  |<-----------(Holds)---------[Customer]----------(Borrows)->|   Loan    |
  +-----------+                                                       +-----------+

Participation Constraints in ER Modeling

1. Definition:

Participation constraint defines the minimum number of relationship instances in which each entity instance must participate (minimum cardinality).

2. Types:

1. Total Participation (Mandatory Existence):

   • Every entity in the set must participate in the relationship.
   • Denoted by a double line in ER diagrams.
   • Example: In a university system, every Student must be enrolled in at least one Program. An unenrolled student cannot exist in the active records.

2. Partial Participation (Optional Existence):

   • Some entities may participate, while others do not.
   • Denoted by a single line.
   • Example: Faculty members and Directs_Project. Not every faculty member directs a project.

Normalizing Student Table to 3NF

Given Table: StudentID, StudentName, CourseID, Course Name, Credit, Contact_No

Given Functional Dependencies:

• StudentID -> StudentName
• CourseID -> CourseName, Credit
• Candidate key for combined table: {StudentID, CourseID, Contact_No} (since Contact_No has multiple values).

Analysis of Current Normal Form:

1. Not in 1NF: The Contact_No column contains multiple comma-separated telephone numbers in row 101, 103, and 104 (violates atomicity).
2. Not in 2NF: Non-prime attributes StudentName, CourseName, and Credit exhibit partial dependencies on subsets of candidate key.

Step 1: Normalize to 1NF (Atomic Values)

Split multi-valued contact numbers into individual atomic rows.

Step 2: Normalize to 2NF (Eliminate Partial Dependency)

Decompose into tables where every non-key attribute is fully functionally dependent on the entire primary key:

• Student (StudentID, StudentName) with PK: StudentID
• Student_Contact (StudentID, Contact_No) with PK: {StudentID, Contact_No}
• Course (CourseID, CourseName, Credit) with PK: CourseID
• Enrollment (StudentID, CourseID) with PK: {StudentID, CourseID}

Step 3: Check for 3NF

• In Student: StudentID -> StudentName (no transitive dependency).
• In Course: CourseID -> CourseName, Credit (no transitive dependency).
• All relations are in 3NF.

Why Concurrency Control is Needed with Examples

When multiple database transactions execute concurrently, their interleaved operations can violate database consistency if not properly controlled.

Three Primary Concurrency Problems:

1. Lost Update Problem:

   • Occurs when two transactions access the same data item and their updates overwrite each other.
   • Example: Seat balance is 10. (T_1) and (T_2) read balance 10 simultaneously. (T_1) books 2 seats and writes 8. (T_2) books 1 seat based on its old read and writes 9. (T_1)'s booking update of 2 seats is permanently lost!

2. Dirty Read (Temporary Update) Problem:

   • Occurs when transaction (T_2) reads a value updated by (T_1) before (T_1) commits; (T_1) subsequently aborts/rolls back.
   • Example: (T_1) updates balance from Rs. 5000 to Rs. 7000. (T_2) reads Rs. 7000 and prints receipt. (T_1) fails and rolls back to Rs. 5000. (T_2) has operated on non-existent, invalid dirty data.

3. Inconsistent Analysis (Unrepeatable Read) Problem:

   • Occurs when a transaction reads a value twice, but another transaction updates or deletes the row in between.

Timestamp-Based Protocol for Concurrency Control

The Timestamp Ordering Protocol determines serializability order in advance using monotonic timestamps:

Mechanism:

Each transaction (T_i) is assigned a unique timestamp (TS(T_i)) when it enters the system. For every data item (Q), the system maintains two timestamps:

• (W\text{-timestamp}(Q)): Largest timestamp of any transaction that successfully executed Write(Q).
• (R\text{-timestamp}(Q)): Largest timestamp of any transaction that successfully executed Read(Q).

Rules:

1. Transaction (T_i) issues Read(Q):

   • If (TS(T_i) < W\text{-timestamp}(Q)): (T_i) needs to read an overwritten value. Reject and Rollback (T_i).
   • Else: Execute read, and set (R\text{-timestamp}(Q) = \max(R\text{-timestamp}(Q), TS(T_i))).

2. Transaction (T_i) issues Write(Q):

   • If (TS(T_i) < R\text{-timestamp}(Q)) OR (TS(T_i) < W\text{-timestamp}(Q)): Reject and Rollback (T_i).
   • Else: Execute write, and set (W\text{-timestamp}(Q) = TS(T_i)).

Guarantees freedom from deadlock because transactions never wait.

Types of Database Architecture with Examples

1. Centralized Database Architecture:
   • All DBMS software, storage databases, and application programs run on a single central computer server (e.g., Mainframe / Single High-Performance Server).
2. Client-Server Architecture:
   • Two-tier: Client (Fat application) + Database server.
   • Three-tier: Web Browser (Thin client) + Application Server + Database Server.
3. Distributed Database Architecture:
   • Database data is distributed across multiple geographically dispersed database servers connected via communication networks (e.g., Google Spanner, Cassandra).
4. Parallel Database Architecture:
   • Multiple processors and disks run in parallel to process queries with high throughput (Shared Memory, Shared Disk, Shared Nothing).

Database Architecture Recommendation for a Multinational Company

As a Senior Database Consultant, for a Multinational Company (MNC) operating across diverse global regions (e.g., South Asia, Europe, North America), I strongly recommend a Distributed Database Architecture over a Centralized Architecture.

Justification & Comparison:

Decision Factor	Centralized Architecture	Distributed Architecture (RECOMMENDED)
Network Latency & Response Time	High international WAN network latency; users across oceans face slow response times.	Ultra-low latency; local branch requests are processed by regional replica nodes.
System Availability & Single Point of Failure	Vulnerable to catastrophic single point of failure. If the central site fails, global business halts.	High Fault Tolerance; if one regional cluster experiences an outage, other regions continue uninterrupted.
Scalability	Vertical scaling (Scale-Up) is exponentially expensive and hits hardware ceiling.	Horizontal Scalability (Scale-Out); new regional nodes can be added economically as business expands.
Data Sovereignty & Legal Compliance	Violates strict regional privacy regulations (e.g., GDPR in Europe, data localization laws).	Complies with Data Sovereignty; sensitive employee/customer data can be partitioned locally.

Implementation Architecture:

• Implement Distributed Database System with Multi-Master / Geographic Partitioning (e.g., CockroachDB or AWS Aurora Global Database).
• Maintain local read replicas with asynchronous cross-region replication for non-critical analytics and synchronous consensus (Raft/Paxos) for mission-critical financials.

SQL Queries for HR Database System

Schema:

• employees (EmpID, FirstName, LastName, Salary, DeptID)
• departments (DeptID, DeptName, LocationID)
• locations (LocationID, StreetAddress, PostalCode, City, ProvinceNo)

---

a) Display EmpID, first name, salary where salary is between 25,000 and 50,000:

SELECT EmpID, FirstName, Salary
FROM employees
WHERE Salary > 25000 AND Salary < 50000;

---

b) Display last name, salary, department name of employees whose department id is 26:

SELECT E.LastName, E.Salary, D.DeptName
FROM employees E
JOIN departments D ON E.DeptID = D.DeptID
WHERE E.DeptID = 26;

---

c) Display first name, department ID, department name, city for employees who work in Lalitpur:

SELECT E.FirstName, D.DeptID, D.DeptName, L.City
FROM employees E
JOIN departments D ON E.DeptID = D.DeptID
JOIN locations L ON D.LocationID = L.LocationID
WHERE L.City = 'Lalitpur';

---

d) Update salary of employee with 50000 whose EmpID is 220:

UPDATE employees
SET Salary = 50000
WHERE EmpID = 220;

---

e) Create a view for employees table named EmpView with attributes EmpID, FirstName, and Salary:

CREATE VIEW EmpView AS
SELECT EmpID, FirstName, Salary
FROM employees;