Com5ej308 — Module 3

• MODULE III: DATABASE MANAGEMENT SYSTEMS (DBMS)
• FOUNDATIONAL CONCEPTUAL ARCHITECTURE: THE DATA HIERARCHY & DBMS PARADIGM CONCEPTUAL FOUNDATION In modern commerce, data is the most strategic institutional asset. Traditional file-processing systems stored operational records in isolated, application-specific flat files, resulting in catastrophic data redundancy, rampant inconsistency, program-data dependence, and severe security vulnerabilities. A Database Management System (DBMS) solves these systemic deficiencies by providing an integrated, centralized software environment that decouples physical data storage from logical business applications.

Database (The Data Store) A logically coherent, selfdescribing collection of related data elements representing real-world commercial entities, transactions, and relationships (e.g., General Ledger records, Customer accounts, Inventory balances).

DBMS (The Software Engine) A sophisticated software suite (e.g., Oracle, Microsoft Access, MySQL, PostgreSQL) that facilitates the definition, construction, manipulation, security enforcement, and concurrent querying of databases.

Database System (The Totality) The collective ecosystem comprising the database itself, the DBMS software, underlying computing hardware, enterprise applications, and human stakeholders (DBAs, accountants, systems analysts).

1. Traditional: File Systems vs Modern DBMS Architecture To comprehend the strategic necessity of a DBMS in commerce and accounting, one must contrast its architecture with legacy flat-file computing environments:

Architectural Dimension Traditional File-Processing System Database Management System (DBMS) Data Redundancy & Inconsistency High; identical customer or employee data is duplicated across payroll files, sales spreadsheets, and shipping records.

Updating an address in one file leaves other files inconsistent.

Minimal to zero; data is normalized and stored centrally. A single update propagates immediately across all enterprise views, ensuring uniform consistency.

Program-Data Dependency High; file structures, field lengths, and storage layouts are hardcoded directly into application programs. Changing a postal code field length requires rewriting code.

Complete Data Independence; the ThreeSchema architecture isolates logical and physical schemas from end-user business programs.

Data Sharing & Multi-User Access Extremely difficult; files are locked by single users. Simultaneous access causes file corruption, concurrency conflicts, and lost updates.

Seamless concurrent multi-user access controlled via sophisticated record-level locking, two-phase locking protocols, and ACID transaction rules.

Data Security & Access Control Crude; access permissions are managed at the gross operating system file level.

Granular column-level or row-level permissions cannot be enforced.

Robust security governance; role-based access control (RBAC), multi-factor authentication, cryptographic data encryption, and detailed audit logging.

Data Integrity Enforcement Integrity checks must be manually coded into each application program; prone to programmer omissions and data corruption.

Integrity constraints (primary keys, foreign keys, domain checks, referential integrity) are defined directly within the database schema and automatically enforced.

Disaster Backup & Crash Recovery Manual, fragmented, and vulnerable; dependent on individual users remembering to copy files. System crashes often result in irreversible data loss.

Automated, continuous point-in-time recovery via write-ahead transaction logging (WAL), shadow paging, and automated mirrored backups.

The Three-Schema Architecture (ANSI/SPARC Framework) The cornerstone of modern database theory is the Three-Schema Architecture, formulated by the ANSI/SPARC committee to achieve complete Data Independence: 1 Internal Level

• Physical Schema: Describes physical storage structures, indexing, block allocation, data compression, and hashing algorithms on disk. ➔ 2 Conceptual Level
• Logical Schema: Describes the overall community logical structure: all entities, relationships, constraints, and data types without physical bias. ➔ 3 External Level
• View / User Schema: Describes customized subsets of the database tailored to distinct user groups (e.g., Accounts View, Sales View, HR View).

LOGICAL DATA INDEPENDENCE The capacity to modify the conceptual schema (e.g., adding a new entity, altering an attribute definition, introducing a new relationship) without requiring alterations to external views or existing application programs. This insulates commercial software from structural database expansions.

PHYSICAL DATA INDEPENDENCE The capacity to modify the internal physical schema (e.g., reorganizing disk partitions, migrating from spinning hard drives to enterprise SSDs, rebuilding B-tree indexes) without altering the conceptual schema or application programs.

1. The: Entity-Relationship (ER) Data Model The Entity-Relationship (ER) Model, introduced by Peter Chen in 1976, is the standard conceptual modeling paradigm used to design enterprise databases. It provides a visual, semantic representation of real-world business environments using three core concepts: Entities, Attributes, and Relationships.

BUILDING BLOCKS OF THE ENTITY-RELATIONSHIP FRAMEWORK CONCEPTUAL MODELING

1. Entities &: Entity Sets An Entity is an identifiable real-world object, person, event, or concept having an independent existence (e.g., Customer,

Voucher, Product). An Entity Set is a collection of similar entities.

• Strong Entity: Possesses an intrinsic primary key attribute that uniquely identifies each occurrence independently (e.g., Customer with CustomerID).

Represented by a single rectangle.

• Weak Entity: Cannot exist without an associated owner entity; lacks an independent primary key. Relies on a partial key (discriminator) combined with the owner's primary key (e.g.,

VoucherDetails dependent on VoucherHeader). Represented by a double rectangle.

1. Attributes (Properties of: Entities) Properties or characteristics that describe an entity (represented by ovals in Chen's notation):

• Simple vs Composite: Simple attributes cannot be divided (e.g., Age); Composite attributes can be subdivided into subparts (e.g., Address broken into Street,

City, State, Pincode).

• Single-Valued vs Multi-Valued: Singlevalued holds one value per entity (e.g.,

PAN Number); Multi-valued holds multiple values (e.g.,

ContactPhoneNumbers). Multi-valued attributes use double ovals.

• Stored vs Derived: Stored values exist permanently in the database (e.g.,

DateOfBirth); Derived values are calculated dynamically (e.g., Age computed from CurrentDate DateOfBirth). Represented by dashed ovals.

Relationships and Structural Cardinality Constraints A Relationship is an association among two or more entities (e.g., Customer "PLACES" SalesOrder).

Relationships are categorized by their degree and structural constraints:

• Degree of a Relationship: The number of participating entity sets. Unary (Recursive) involves one entity set relating to itself (e.g., Employee "MANAGES" Employee); Binary involves two entity sets (e.g., Vendor "SUPPLIES" Product); Ternary involves three entity sets simultaneously.
• Cardinality Ratios (Mapping Constraints): The number of relationship instances in which an entity can participate:

One-to-One (1:1): One entity in A associates with at most one entity in B. Example: One Department is managed by exactly one DepartmentManager.

One-to-Many (1:N): One entity in A associates with zero, one, or multiple entities in B, but an entity in B associates with only one in A. Example: One Customer places many SalesOrders; each SalesOrder belongs to exactly one Customer.

Many-to-Many (M:N): Entities in A associate with multiple entities in B, and vice versa. Example: One Invoice contains many Products; one Product appears on many Invoices. In relational database implementation, M:N relationships must be decomposed into two 1:N relationships linked by a junction/bridge table.

• Participation Constraints: Total Participation (existence dependency, represented by double lines) means every entity in the set must participate in the relationship (e.g., every VoucherDetail must belong to a VoucherHeader). Partial Participation means only some entities participate (e.g., not every Employee manages a Department).

1. The: Relational Database Model & Concept of Keys Formulated by Dr. Edgar F. Codd of IBM in 1970, the Relational Model represents data as two-dimensional tables called Relations. Mathematical rigor, conceptual simplicity, and declarative querying make it the dominant database technology globally.

MATHEMATICAL VOCABULARY VS COMMERCIAL DATABASE TERMINOLOGY RELATIONAL TERMINOLOGY Formal Relational Term (Codd) Commercial DBMS Term (SQL/Access) Enterprise Accounting Equivalent Relation Table Ledger / Register / Master File Tuple Row / Record Single Transaction Entry / Customer Record Attribute Column / Field Data Element (e.g., Debit, Credit,

AccountCode) Domain Data Type & Validation Constraints Permissible Input Values (e.g.,

Positive Currency) Degree Number of Columns Number of Fields in the Accounting Schema Cardinality Number of Rows Number of Journal Entries / Customer Rows Comprehensive Taxonomy of Database Keys In relational theory, Keys are fundamental constraints that guarantee the unique identification of records and enforce structural links across tables:

SUPER KEY Any set of one or more attributes within a relation that, taken collectively, uniquely identifies an individual tuple. A super key may contain extraneous attributes that are not strictly necessary for uniqueness. Example: {CustomerID,

CustomerEmail, Phone} is a valid super key for the Customer relation.

CANDIDATE KEY A minimal super key—a super key from which no attribute can be removed without destroying the uniqueness property. A relation may have multiple candidate keys. Example: In an employee relation, both {EmployeeID} and {PAN_Number} are candidate keys.

PRIMARY KEY (PK) The single candidate key explicitly chosen by the database designer to uniquely identify tuples across the table. Must strictly satisfy the Entity Integrity Rule: it can never contain duplicate values and can never contain a NULL value.

ALTERNATE KEY (SECONDARY KEY) Any candidate key that was not selected as the primary key. Alternate keys are frequently indexed to accelerate search queries. Example: If EmployeeID is selected as the PK, PAN_Number functions as the alternate key.

FOREIGN KEY (FK) An attribute (or group of attributes) in one table whose values must match the Primary Key in another referenced table. Enforces the Referential Integrity Rule: it ensures that child records cannot reference non-existent parent records.

COMPOSITE KEY & SURROGATE KEY A Composite Key consists of two or more attributes combined to form a primary key (e.g., {VoucherID, LineItemNo}). A Surrogate Key is a system-generated artificial identifier (such as an AutoNumber sequence) having no intrinsic business meaning.

1. Database: Designs for Accounting and Business Applications Accounting is inherently relational. Every commercial transaction alters financial state according to doubleentry mechanics. Designing a robust relational accounting database demands translating financial principles into normalized relational schemas:

CORE ACCOUNTING RELATIONAL SCHEMAS ACCOUNTING DB ARCHITECTURE

1. Chart of: Accounts Table (tbl_ChartOfAccounts): AccountCode (PK, Text) | AccountTitle (Text) | AccountType (Text: Asset, Liability,

Equity, Revenue, Expense) | NormalBalance (Text: Dr/Cr) | CurrentBalance (Currency)

1. Journal: Voucher Header Table (tbl_VoucherHeader):

VoucherID (PK, AutoNumber) | VoucherDate (Date/Time) | VoucherType (Text: Payment,

Receipt, Sales, Journal) | ReferenceNo (Text) | Narration (Long Text) | AuthorizedBy (Text)

1. Journal: Voucher Line Items Table (tbl_VoucherDetails):

DetailID (PK, AutoNumber) | VoucherID (FK, Number) | AccountCode (FK, Text) | DebitAmount (Currency) | CreditAmount (Currency) | LineNarration (Text)

1. Customer: Master Table (tbl_Customer): CustomerID (PK, Text) | CompanyName (Text) | GSTIN (Text) | CreditLimit (Currency) | OutstandingBalance (Currency) | BillingAddress (Text)
2. Sales: Invoice Header & Line Items Schema: tbl_SalesInvoice: InvoiceNo (PK) | InvoiceDate | CustomerID (FK) | TotalAmount | CGST | SGST | NetPayable tbl_InvoiceLines: LineID (PK) | InvoiceNo (FK) | ItemCode (FK) | Quantity | UnitPrice | DiscountRate | LineTotal Relational Double-Entry Validation Constraint In a computerized accounting database, an automatic integrity constraint must verify that for any given VoucherID, the mathematical sum of all DebitAmount entries in tbl_VoucherDetails exactly equals the sum of all CreditAmount entries before the transaction is committed to the database:

SUM(DebitAmount WHERE VoucherID = X) - SUM(CreditAmount WHERE VoucherID = X) = 0.00

1. Normalization of: Relational Database Schemas Normalization is a formal, step-by-step mathematical process of analyzing relational schemas based on their functional dependencies to eliminate data redundancy and prevent destructive update anomalies. A poorly designed, un-normalized table suffers from three fatal anomalies:

• Insertion Anomaly: The inability to record certain facts without artificially introducing unrelated data.
• Example: In an un-normalized sales table, a company cannot record a newly manufactured product's price until a customer actually purchases it, because the primary key includes CustomerID.
• Deletion Anomaly: The unintended, catastrophic loss of vital information when deleting an unrelated transaction. Example: If the sole customer who bought a specialized product cancels their order, deleting the sales record inadvertently erases all product specification data.
• Update / Modification Anomaly: When an attribute's value changes, multiple duplicate rows must be updated. If any row is missed due to system interruption, the database enters an inconsistent state.

The Core Normal Forms (1NF, 2NF, 3NF, and BCNF) Normal Form Governing Structural Requirement Anomalies Eliminated & Practical Implementation Rule First Normal Form (1NF) Every column must contain only atomic (indivisible) values. There must be no repeating groups, no multi-valued arrays, and each record must be uniquely identifiable via a Primary Key.

Eliminates comma-separated values (e.g., storing multiple item codes in a single cell). Solved by creating separate rows or child tables for repeating entries.

Second Normal Form (2NF) The table must satisfy 1NF, and no non-prime attribute may be partially dependent on any proper subset of a composite candidate key. All non-key attributes must depend on the entire primary key (Full Functional Dependency).

Eliminates Partial Functional Dependencies. Only applies to tables with composite primary keys. If a column depends on only part of the key (e.g.,

ItemDescription depending solely on ItemCode in a {InvoiceNo, ItemCode} composite key), it must be split into its own table.

Third Normal Form (3NF) The table must satisfy 2NF, and no non-prime attribute may be transitively dependent on the primary key through another nonprime attribute. Formally: For every non-trivial functional dependency X ➔ A, either X is a Super Key, or A is a prime attribute.

Eliminates Transitive Dependencies ("A determines B, and B determines C"). Example: If InvoiceNo determines CustomerID, and CustomerID determines CustomerCity,

CustomerCity is transitively dependent on InvoiceNo.

Solved by moving Customer details into a dedicated tbl_Customer.

Boyce-Codd Normal Form (BCNF) A stricter, refined variation of 3NF.

For every non-trivial functional dependency X ➔ Y, X must be a candidate super key without exception.

Addresses rare anomalies in tables possessing multiple overlapping composite candidate keys. Guarantees that every determinant is a super key.

• Step-by-Step Normalization Walkthrough: Commercial Sales Invoice
• DECONSTRUCTION: FROM UN-NORMALIZED FORM (UNF) TO THIRD NORMAL FORM (3NF) APPLIED NORMALIZATION Consider an un-normalized sales invoice record containing customer, product, and financial entries:
• UNF Schema: Invoice[InvoiceNo, Date, CustID, CustName, CustCity, {ItemCode, ItemDesc, Qty,

UnitPrice, LineTotal}, InvoiceTotal] Step 1: Conversion to 1NF (Eliminate Repeating Groups):

Expand multi-valued item lines into distinct rows, forming a composite primary key {InvoiceNo,

ItemCode}: tbl_Invoice1NF[InvoiceNo, ItemCode, Date, CustID, CustName, CustCity,

ItemDesc, Qty, UnitPrice, LineTotal] Step 2: Conversion to 2NF (Eliminate Partial Dependencies):

Identify attributes that depend on only part of the composite key. ItemDesc and UnitPrice depend solely on ItemCode, not InvoiceNo. Decompose into two tables: tbl_InvoiceHeader[InvoiceNo, Date, CustID, CustName, CustCity] tbl_Item[ItemCode, ItemDesc, UnitPrice] tbl_InvoiceLines[InvoiceNo, ItemCode, Qty, LineTotal] Step 3: Conversion to 3NF (Eliminate Transitive Dependencies):

In tbl_InvoiceHeader, InvoiceNo determines CustID, which in turn determines CustName and CustCity (transitive dependency). Extract customer attributes into a distinct entity: tbl_Customer[CustID, CustName, CustCity] tbl_InvoiceHeader[InvoiceNo, Date, CustID (FK)] tbl_Item[ItemCode, ItemDesc, UnitPrice] tbl_InvoiceLines[InvoiceNo (FK), ItemCode (FK), Qty]

• Note: LineTotal and InvoiceTotal are calculated values and should never be stored physically, preserving data integrity.

1. DBMS: Software: Microsoft Access for Business Applications Microsoft Access is an intuitive, desktop-based Relational Database Management System (RDBMS) that combines the relational Microsoft Jet/ACE database engine with a graphical user interface and rapid application development tools. In commerce and business management, MS Access is widely utilized for tracking small-to-medium enterprise operations, departmental accounting, inventory control, and financial reporting.

THE FOUR FUNDAMENTAL DATABASE OBJECTS IN MICROSOFT ACCESS ACCESS ARCHITECTURE

1. Tables (Data: Storage Object) The foundation of the database where all operational data is stored in rows (records) and columns (fields).

• Design View: Used by designers to configure field names, specify data types, define primary keys, and establish validation rules.
• Datasheet View: Used by end-users to view, edit, search, and enter records in an Excel-like grid.

1. Queries (Data: Manipulation Object) Allows users to extract, filter, calculate, sort, and transform data stored across multiple related tables.

• Select Queries: Retrieve specific rows and columns matching criteria.
• Action Queries: Execute bulk operations (Update, Append, Delete, Make-Table).
• Parameter Queries: Interactively prompt the user for criteria at runtime.
• Crosstab Queries: Pivot data into summary cross-tabulation matrices.

1. Forms (User: Interface Object) Provides intuitive, customized digital screens for data entry, editing, and application navigation. Forms shield end-users from raw database tables, preventing accidental schema corruption. Supports visual controls: textboxes, dropdown combo boxes, checkboxes, command buttons, and nested Subforms (essential for master-detail invoice designs).
2. Reports (Information: Presentation Object) Designed specifically for formatting, summarizing, grouping, and presenting business data for management review, statutory compliance, or physical printing.

Features multi-level grouping (e.g., grouped by Sales Region, then by Branch), automatic subtotals, grand totals, and professional page headers and footers.

Key Data Types and Field Properties in MS Access Table Design When designing tables in MS Access Design View, selecting appropriate Data Types and configuring Field Properties ensures absolute data integrity:

Access Data Type Permissible Content & Storage Capacity Accounting & Commercial Usage Short Text Alphanumeric strings up to 255 characters.

Customer names, Account codes, PAN numbers, GSTIN, invoice prefixes.

Long Text (Memo) Alphanumeric text up to 1 gigabyte (64,000 characters via GUI).

Detailed transaction narrations, contract clauses, audit notes.

Number Numeric values (Byte, Integer, Long Integer, Single, Double).

Quantities, employee headcount, reorder levels, foreign key linkages.

Currency High-precision monetary values (fixed-point 4 decimal places).

Debits, credits, invoice totals, unit prices, tax liabilities (prevents rounding errors).

AutoNumber Sequential unique numbers generated automatically by Access.

Ideal surrogate primary keys (VoucherID, TransactionID, CustomerID).

Date / Time Calendar dates and timestamps from years 100 to 9999.

Transaction dates, voucher dates, invoice due dates, employee birthdates.

Yes / No Boolean values (True/False, -1/0). Flags such as IsApproved, IsAudited,

IsGSTRegistered, IsActive. Establishing Relationships & Referential Integrity in MS Access In MS Access, relationships are established graphically via the Relationships Window by dragging a Primary Key from a parent table and dropping it onto the corresponding Foreign Key in a child table:

• Enforce Referential Integrity: A critical checkbox in MS Access that prevents "orphan" records. Access strictly blocks users from entering a child record with a non-existent foreign key (e.g., posting a voucher line item with an invalid AccountCode) and prohibits deleting a parent record that has linked child records.
• Cascade Update Related Fields: If a user alters the Primary Key value in a parent table (e.g., updating a CustomerID), Access automatically updates all corresponding Foreign Key values in child tables.
• Cascade Delete Related Records: If a parent record is deleted (e.g., deleting a VoucherHeader), Access automatically deletes all associated child records (all linked VoucherDetails lines), preventing orphaned line items.