Engineering/Database

Database Indexes and SQL Execution Plans

Suppose we have the following orders table:CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, order_status VARCHAR(20) NOT NULL, ordered_at DATETIME NOT NULL, total_amount BIGINT NOT NULL);When the table contains only a few hundred rows, the following query returns almost immediately:SELECT *FROM ordersWHERE customer_id = 1001;The situation changes whe..

Engineering/Database

Transactions and Concurrency Control in Database Systems

uppose a database contains the following bank accounts.Account IDOwnerBalanceA-100Isaac1,000,000B-200Sophie500,000To transfer 100,000 from Isaac to Sophie, the system must perform at least two changes:Subtract 100,000 from account A-100Add 100,000 to account B-200A simplified SQL implementation might look like this:UPDATE accountsSET balance = balance - 100000WHERE account_id = 'A-100';UPDATE ac..

Engineering/Database

Why Does Database Normalization Matter?

Database normalization is often introduced through a short list of rules:First Normal Form: atomic valuesSecond Normal Form: remove partial dependenciesThird Normal Form: remove transitive dependenciesBCNF: every determinant must be a candidate keyThese definitions are useful when preparing for an exam.On their own, however, they do not explain how to examine a real table, identify what is wrong..