Information Systems, Databases, and Data Management
Information systems and data management questions test how data is modeled, stored, queried, protected, and governed. Expect entity-relationship modeling, normalization, SQL, transaction integrity, master data, data quality, and backup and recovery.
Core rules
- Model entities and relationships before tables. A primary key uniquely identifies a row; a foreign key enforces referential integrity.
- Normalize operational data to third normal form: 1NF removes repeating groups; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies on non-key attributes.
- Use ACID transactions for operational systems: atomicity, consistency, isolation, durability. Choose isolation levels deliberately because stronger isolation reduces concurrency.
- Index columns used for selective filtering and joins, but expect write overhead and storage cost.
- Separate OLTP from analytics. A warehouse or mart may be deliberately denormalized for query performance.
- Define data quality rules for completeness, validity, uniqueness, timeliness, and consistency; assign an owner.
Worked example
A purchase order table stores OrderID, CustomerName, CustomerCity, Item, Quantity, and UnitPrice. Item repeats, so split lines into an OrderLine table keyed by OrderID plus Item. CustomerName and CustomerCity depend only on OrderID, a partial dependency, so move them to a Customer table and keep CustomerID in the order header. This yields Order, OrderLine, and Customer tables at 3NF. The reasoning is dependency analysis, not counting columns.
Traps
- Assuming NULL equals zero or an empty string; NULL means unknown and needs three-valued logic.
- Using a natural key that can change, such as an email address, as a primary key.
- Treating backups as archives; backups support recovery, archives support retention.
- Over-normalizing a reporting star schema and then paying for excessive joins.