Database Management System — Notes

Data are raw facts as recorded: the number, date, customer and amount of an invoice. Information is data processed (sorted, filtered, totalled, related) into a form that supports a decision: dues by customer, sales by city.

A database is an organised collection of related data stored together. A database management system (DBMS) is the software that defines the database, stores and retrieves records, enforces rules on them, lets many users share them and protects them. Accounting software is built on a DBMS: vouchers are stored once and ledgers, trial balance and statements are drawn from them.

Terms. A relational database stores data in tables (relations); each column is a field (attribute) with a data type; each row is a record (tuple). A field left empty holds NULL.

• Hierarchical: records form a tree; each child record has exactly one parent (region → depot → retailer). Fast top-down access; a record needing two parents must be repeated, and changes to the structure are hard.
• Network: records form a graph linked by pointers; a record may have several owners (a consignment owned by a customer and a truck). Flexible but complex.
• Relational: data is kept in tables linked by common key fields (CustCode in Customers and in Invoices). Simple to understand, flexible queries in SQL; the model used by most accounting software.
• Object-oriented: data is stored as objects that carry both their data and the operations on it; suits complex data such as designs and media.

Features and advantages:

1. Controlled redundancy and consistency: each fact stored once.
2. Integrity: rules on keys, data types and links stop wrong entries.
3. Data independence: programs do not change when storage changes.
4. Sharing and concurrency control: many users at once, with record locking.
5. Security: passwords, user rights, audit trail.
6. Backup and recovery from a failure using backups and a transaction log.
7. Query language and reports: information on demand.

Limitations: cost of software, hardware and training; complexity; dependence on one system, which must itself be secured and backed up.

Designing a table: name it after the thing it records; list its fields; give each a data type (text, number, currency, date, Yes/No); choose a primary key.

Keys:

• Primary key: a field whose value is unique and never blank in every record (CustCode, InvNo, AssetNo).
• Candidate key: any field that could be the primary key.
• Composite key: two or more fields unique only together (BoatCode + TripDate).
• Foreign key: a field that refers to the primary key of another table (CustCode in Invoices). It may repeat but must match an existing record.

Integrity rules the DBMS enforces (see the table): it refuses a duplicate or blank primary key, a foreign key with no matching parent, a value of the wrong type, and the deletion of a parent record still referred to.

Relationships: one-to-one (an employee and one salary account), one-to-many (a customer and its invoices: the primary key of the one side goes into the many side as a foreign key), many-to-many (invoices and products: a linking table holds both keys).

Database objects:

• Form: screen for entering, viewing and editing one record at a time, with checks and drop-down lists.
• Query: a question asked of the data (in SQL).
• Report: formatted output of many records, grouped and sorted with subtotals and totals, for printing; may combine related tables (sales by city: invoices linked to customers).
• Index: a sorted list of a field’s values with pointers to records; speeds up searching and sorting, costs space and slows saving a little.

• SELECT field1, field2 FROM table WHERE condition; shows the chosen fields of the records that meet the condition; SELECT * shows all fields. SELECT never changes data.
• AND: both conditions true (narrows). OR: at least one true (widens). AND is applied before OR; use brackets to change the order.
• UPDATE table SET field = value WHERE condition; changes values; the new value may use the old one (CreditLimit = CreditLimit + 20000).
• DELETE FROM table WHERE condition; removes records; the table design remains.
• Without WHERE, UPDATE and DELETE act on every record. Run the same WHERE with SELECT first.

Text and dates are written in single quotes ('Vidisha', '2026-04-15'); numbers are not. Dates written year-month-day compare correctly.

Aggregate functions return one value from many records: COUNT(*) (number of records), COUNT(field) (records with a value in that field), SUM(field), AVG(field), MAX(field), MIN(field).

• WHERE is applied first, then the function: SELECT SUM(Amount) FROM Invoices WHERE CustCode = 'C05'; gives the sales to one customer.
• NULLs are skipped by every function except COUNT(*); AVG divides by the number of values present.
• Several functions may be used together: SELECT MAX(Balance), MIN(Balance) FROM Debtors;.

Accounting uses: total sales or dues, number of invoices, average invoice, highest balance, smallest receipt.

Accounting information. Each source document is entered once (through a form) into linked tables; ledgers, the trial balance, statements and management reports are drawn from them, so they always agree.

Debtors and creditors. Customers and Suppliers are master tables; invoices, bills, receipts and payments carry the party code as a foreign key. A report gives each balance (opening + invoices − receipts), dues past their date, and parties over their credit limit.

Bank reconciliation. The bank statement is imported as a table and matched with the bank column of the cash book on the reference. Unmatched items are the reconciling items: cheques issued but not presented and bank-only credits are added to the cash book balance; cheques deposited but not credited and bank-only debits are subtracted, giving the pass book balance.

Asset accounting. A fixed asset register table holds each asset’s number, category, date, cost, rate and location; the DBMS computes depreciation and written-down values and totals them by category.