11.5 Database Management Systems (DBMS) & MS Office Suite

Database Management Systems (DBMS) & MS Office Suite

Database Management Systems store, query, and manage millions of core customer financial records reliably. Concurrently, office productivity applications like MS Excel, MS Word, and MS PowerPoint are used daily by bank officers for financial modeling, audit reporting, and operational correspondence. SBI PO Mains frequently tests DBMS concepts, SQL query syntax, MS Excel formula logic, and keyboard shortcuts.

---

1. DBMS Architecture & The Relational Model (RDBMS)

A DBMS is specialized system software used to create, manage, and query structured databases.

The ANSI-SPARC 3-Schema Architecture

+-----------------------------------------------------------------+
|                ANSI-SPARC 3-SCHEMA ARCHITECTURE                 |
|                                                                 |
|   External Level   [ View 1 ]      [ View 2 ]     [ View 3 ]    |
|                         \              |              /         |
|   Logical Level   +---------------------------------------+     |
|                   |        Conceptual Schema              |     |
|                   | (Entities, Attributes, Relationships) |     |
|                   +---------------------------------------+     |
|                                       |                         |
|   Physical Level  +---------------------------------------+     |
|                   |         Internal Schema               |     |
|                   | (Physical Storage Blocks & Indexing)  |     |
|                   +---------------------------------------+     |
+-----------------------------------------------------------------+

• Physical / Internal Level: Describes how data blocks and index structures are physically stored on storage hardware.
• Conceptual / Logical Level: Describes what data is stored and the relationships between entities (Tables, Primary Keys, Foreign Keys).
• External / View Level: User-specific views restricting data exposure based on user access privileges.
• Data Independence: The ability to modify schema at one level without forcing changes at the next higher level (Physical Data Independence vs. Logical Data Independence).

Key Relational Database Terminology

• Relation: A database Table.
• Tuple: A single Row (Record) in a table.
• Attribute: A single Column (Field) in a table.
• Cardinality: The total number of rows (tuples) in a relation.
• Degree: The total number of columns (attributes) in a relation.
• Primary Key: A minimal attribute set that uniquely identifies every tuple in a table (must be UNIQUE and NOT NULL).
• Foreign Key: An attribute in a table that references the Primary Key of another table, establishing Referential Integrity.

Transaction ACID Properties in Banking

Every core banking fund transfer (e.g., transferring ₹10,000 from Account A to Account B) must satisfy ACID properties:

1. Atomicity ("All or Nothing"): The transaction executes completely or is entirely rolled back. Money cannot be debited from Account A without being credited to Account B.
2. Consistency: Database transitions strictly from one valid state to another, maintaining all schema constraints.
3. Isolation: Concurrent transactions execute independently without interfering with each other (managed via locking protocols).
4. Durability: Once committed, transaction results persist permanently in non-volatile storage, even during system crashes.

---

2. Structured Query Language (SQL) Command Categories

SQL is the standard language for relational database management systems.

Sample SQL Query Example for Banking

To retrieve all customer accounts with a balance exceeding ₹1,00,000 sorted by account branch:

SELECT Account_No, Customer_Name, Balance, Branch_Code
FROM Customer_Accounts
WHERE Balance > 100000
ORDER BY Branch_Code ASC;

---

3. MS Excel Architecture, Formulas & Financial Functions

An MS Excel workbook consists of worksheets structured into a grid of 1,048,576 Rows (numbered $1, 2, 3...$) and 16,384 Columns (lettered $A, B... XFD$).

Cell Reference Types

• Relative Reference (A1): Adjusts automatically when copied across rows or columns.
• Absolute Reference ($A$1): Both column and row are locked using dollar signs ($). Formula references remain fixed regardless of where the formula is copied.
• Mixed Reference ($A1 or A$1): Locks either the column ($A1) or the row (A$1) while allowing the other to adjust dynamically.
• Shortcut: Press F4 while selecting a cell reference in the formula bar to cycle through reference modes (A1 -> $A$1 -> A$1 -> $A1).

Essential Financial & Analytical Excel Functions

Step-by-Step Worked Excel Calculation

Scenario: A bank analyst evaluates quarterly branch loan performance data in Excel:

• Cell A2: Loan Amount = 500000
• Cell B2: Interest Rate = 0.08 (8%)
• Cell C2: Customer Risk Score = 750
• Cell D2 contains the formula: =IF(C2 >= 700, A2 * B2, A2 * B2 * 1.5)

Step-by-Step Evaluation:

1. Evaluate condition C2 >= 700: 750 >= 700 is TRUE.
2. Execute TRUE branch formula: A2 * B2 -> 500000 * 0.08 = 40000.
3. Result displayed in Cell D2 = 40,000.

If the formula in D2 is copied down to cell D3 (where A3 = 200000, B3 = 0.08, C3 = 650):

1. Evaluate condition C3 >= 700: 650 >= 700 is FALSE.
2. Execute FALSE branch formula: A3 * B3 * 1.5 -> 200000 * 0.08 * 1.5 = 24000.

---

4. MS Word & MS PowerPoint Essentials

Key Concepts in MS Word

• Mail Merge: Combines a main document (e.g., loan sanction letter template) with a structured data source (e.g., customer database) to automatically generate personalized bulk correspondence.
• Header & Footer: Text or graphics printed at the top/bottom of every page.
• Macros: Recorded sequences of commands and keystrokes using VBA (Visual Basic for Applications) to automate repetitive formatting tasks.

Key Concepts in MS PowerPoint

• Slide Master: Top slide in a hierarchy that stores information about the theme, layout, backgrounds, and fonts of a presentation. Editing the Slide Master updates all associated slides globally.
• Slide Transition vs. Animation: Transitions control visual movement between slides; Animations control movement of individual elements on a single slide.

---

5. Important MS Office Keyboard Shortcuts Table

---

Exam Strategies & Common Traps

1. TRUNCATE vs. DELETE Trap: DELETE is a DML command that can be rolled back and deletes rows conditionally. TRUNCATE is a DDL command that removes all rows quickly, releases storage space, and cannot be rolled back easily.
2. Excel Reference Dragging: Relative references shift when copied. Absolute references ($A$1) never shift. Mixed references ($A1 vs A$1) lock only the column or row containing the $ symbol.
3. COUNT vs. COUNTA: COUNT counts numeric values only. COUNTA counts all non-empty cells (including text and error codes).