CampusLearn
← BIT 202 Database Systems

Normalisation, Indexes and Transactions

Notes

Reducing avoidable redundancy Normalisation structures relations to reduce harmful duplication and update anomalies. A functional dependency means that one attribute set determines another. First normal form requires single values from the defined domain in each cell and no repeating groups. Second normal form removes dependencies on only part of a composite candidate key. Third normal form addresses inappropriate transitive dependencies of non-key attributes on keys. For example, storing a supplier telephone in every product row creates repeated facts; a separate Supplier relation lets the organisation change that fact in one controlled place. Performance and reliable changes An index is an auxiliary search structure that can accelerate selective lookups and ordering, but requires storage and maintenance during writes. Choose indexes from measured query patterns rather than indexing every column. A transaction groups operations into a logical unit. Atomicity means all its changes succeed together or none do; consistency preserves declared rules; isolation controls interference between concurrent operations; durability preserves committed work after failure. Applications still need appropriate constraints, isolation levels and retry logic because transactions do not automatically correct faulty business rules or eliminate all concurrency conflicts. Practical example A Nakuru SACCO transfers KES 3,000 from account A, holding KES 12,000, to account B, holding KES 5,000. One transaction debits A to KES 9,000 and credits B to KES 8,000. The combined balance remains 12,000 plus 5,000 = KES 17,000. If the credit fails, rollback must also undo the debit, rather than leaving only KES 14,000 recorded. A unique index on AccountNumber supports fast account identification and prevents duplicate numbers. Separate account-holder contact records reduce repeated telephone updates, while the transfer transaction protects the monetary relationship between both account changes.

Quiz

1. Why store supplier contact details separately from product records?

2. Which property prevents a failed transfer from leaving only its debit recorded?

3. What is a realistic disadvantage of adding an index?

Sign in to submit and securely grade your quiz.