Database Management and Information Systems
Database Management and Information Systems tests relational design, SQL, transactions and how systems turn data into management information.
Core rules
- A relation has rows and columns; a primary key uniquely identifies a row; a foreign key references another relation and enforces referential integrity.
- Normalisation reduces redundancy: 1NF removes repeating groups; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies.
- ACID means atomicity, consistency, isolation and durability. Indexes speed reads but add write and storage cost.
- An MIS supports operational reporting and decision-making; it is not the same as the transaction database that feeds it.
Worked example
OrderLine(OrderID, ProductID, ProductName, Quantity) has composite key (OrderID, ProductID). ProductName depends only on ProductID, so it is a partial dependency, violating 2NF. Split into OrderLine(OrderID, ProductID, Quantity) and Product(ProductID, ProductName). Quantity depends on the full key and stays. This removes repeated product names and update anomalies.
Traps
A primary key is not the same as a foreign key; NULL is not zero or an empty string; DELETE removes rows while TRUNCATE removes all rows quickly; and more indexes are not always better. Do not normalise so far that routine queries become impractical.