Lesson 38: Database Management System (DBMS)
Master computerized relational databases using MS Access. Learn database design stages, account classification & codification, tables, queries, forms, reports, primary keys, and transaction voucher structures for accounting applications.
DBMS Definition & The 3 Database Design Stages
What is a Database & DBMS?
A Database is an organized collection of interrelated data stored digitally for generating reliable, decision-worthy information. A Database Management System (DBMS) eliminates paper-based limitations such as data redundancy, inconsistency, duplicity, isolation, and security vulnerabilities.
Why DBMS Over Flat Spreadsheets?
While spreadsheets struggle with large-scale data integrity, DBMS uses relational tables linked by Primary and Foreign Keys to enforce normalization, multi-user concurrency, and secure back-end data processing.
The 3 Sequential Stages of Database Design
Requirements Analysis
Evaluates the informational needs ("Reality") of the organization: storage volume, update frequency, user types, hardware, and front-end integration.
Conceptual Design (ER Diagram)
Develops an Entity-Relationship (ER) diagram containing real-world entities, their descriptive attributes, and relationships binding them together.
Logical Design
Transforms ER models into relational data tables (e.g., Accounts, AccountType, Vouchers, Employees, Support tables).
Account Categorization & Hierarchical Codification
5 Core Accounting Categories
- • 5 : Capital (e.g., Sanjana's Capital, Naveen's Capital)
- • 4 : Liabilities (e.g., Jyoti Bros, Drawings)
- • 3 : Assets (e.g., Furniture, Cash, Bank, Debtors)
- • 2 : Revenue (e.g., Sales)
- • 1 : Expenditure (e.g., Purchases, Carriage, Rent, Wages)
Sample Unique Electronics Codes
5001 : Sanjana's Capital Account (Group 5)4301 : Jyoti Bros. - Creditor (Group 4)3215 : Cash Account (Group 3)3216 : Bank Account (Group 3)2001 : Sales Account (Group 2)1201 : Purchases Account (Group 1)1219 : Salaries Expense (Group 1)Microsoft Access Objects, Data Types & Field Properties
Store raw data structured in rows (records) and columns (fields/attributes).
SQL-compatible statements used to extract, filter, sort, and update data across tables.
User interfaces designed for convenient data entry, navigation, and front-end interaction.
Formatted printable output summaries (Columnar or Tabular formats) for decision makers.
MS Access Data Types & Field Properties Summary
| Data Type | Storage Capacity / Behavior | Key Field Property |
|---|---|---|
| Text | Max 255 characters (strings) | Field Size: Controls max characters allowed. |
| Memo | Accommodates 65,536 characters | Used for lengthy comments or narrations. |
| Number / Currency | Subjected to mathematical operations | Decimal Places: Specifies currency precision. |
| Validation Rule | Logical check (e.g. <= 10) |
Validation Text: Error message displayed if rule fails. |
| OLE Object | Object Linking & Embedding | Stores external images, photos, barcodes, or PDFs. |
Relational Accounting Database Structure & UPDATE Queries
Primary Key vs Foreign Key
A Primary Key (e.g. code in Accounts table, v_no in Vouchers table) uniquely identifies every record in a table. A Foreign Key (e.g. acc_type in Accounts table referencing cat_id in AccountType table) creates relational links between tables.
Automating Registers via UPDATE Queries
To maintain automated Purchase or Sales Registers, DBMS uses UPDATE Queries to recalculate outstanding balances dynamically:
Relational Schema & Live Voucher Query Simulator
Explore the ER relational structure of Unique Electronics and test real-time accounting voucher posting into relational DBMS tables.
Unique Electronics Relational Database Schema
Inter-table relationships created via Primary Keys (PK) and Foreign Keys (FK).
Accounting Voucher Entry & SQL Query Engine
Select a transaction scenario to generate its backend DBMS row entry and simulate SQL query processing.
5 Golden Rules for Database Management System
Memorize these 5 key database design rules, MS Access object properties, and key constraints for high marks in NIOS exams.
3 Sequential Database Design Stages
Design MethodologyDatabase creation follows 3 strict steps: (1) Requirements Analysis (evaluating business reality), (2) Conceptual Design (creating ER diagram of entities & attributes), and (3) Logical Design (building relational tables).
Primary Key vs Foreign Key Rule
Relational IntegrityA Primary Key uniquely identifies every record in a table without nulls. A Foreign Key in a secondary table points directly to a Primary Key in a master table to establish relationships.
4 Core MS Access Objects
MS Access Architecture
MS Access relies on 4 primary objects: Tables (store raw data), Queries (SQL retrieval & updates), Forms (user data entry UI), and Reports (formatted printable summaries). Default file extension is .accdb.
Text Data Type Limits & Validation Rules
Data Integrity
The Text data type has a maximum length of 255 characters, whereas Memo holds up to 65,536 characters. Use Validation Rules (e.g. <=10) and Validation Text to reject erroneous entries automatically.
5 Account Group Digit Rules & UPDATE Queries
Codification & AutomationAccounts are codified using 5 leading digits: 5 = Capital, 4 = Liabilities, 3 = Assets, 2 = Revenue, and 1 = Expenditure. Automated purchase/sales registers use UPDATE Queries to calculate outstanding amounts dynamically.
10 MCQ Practice Quiz
Test your knowledge of database design, MS Access objects, field properties, and codification schemes.
10 Interactive 3D Flashcards
Click or tap the card to flip between terms, definitions, and database properties.
Loading Term...
Click card to reveal definition & textbook property
Loading Definition...