Data and databases
Organisations depend on huge amounts of data. Databases store it so it can be found, updated, and protected reliably, even when many people use it at once.
Why databases
Storing data in loose files and spreadsheets leads to problems: duplicated and inconsistent copies, difficulty searching large amounts, and conflicts when several people edit at once. A database is an organised collection of data, and a database management system (DBMS) is the software that stores, retrieves, secures, and maintains it.
The relational model
Edgar F. Codd, working at IBM, proposed the relational model in 1970. Data is stored in tables (relations) made of rows (records) and columns (fields or attributes). A table of students might have columns for student ID, name, and year, with one row per student. Relational databases such as PostgreSQL, MySQL, Oracle, and SQL Server dominate business computing.
Keys
A primary key uniquely identifies each row, such as a student ID number. A foreign key is a column that refers to the primary key of another table, linking them: an enrolments table might store a student ID and a course ID, connecting students to courses. Keys let data be stored once and referenced wherever needed.
Relationships
Tables relate in three ways. One-to-one: each person has one passport record. One-to-many: one teacher teaches many classes. Many-to-many: students take many courses, and courses have many students; this needs a linking table, such as enrolments. Entity-relationship diagrams sketch these designs before building them.
Normalisation
Normalisation organises tables to reduce redundancy and avoid update problems. If a customer's address is copied into every order, changing it means updating many rows, and missing one creates inconsistency. Moving addresses into a separate customers table, linked by a key, stores each fact once. Designers balance normalisation against query speed.
Transactions and ACID
A transaction is a group of operations treated as one unit, such as moving money between accounts: debit one and credit the other. Databases guarantee ACID properties. Atomicity: all of it happens or none does. Consistency: rules stay satisfied. Isolation: simultaneous transactions do not interfere. Durability: once committed, changes survive crashes.
Indexes
An index is an extra data structure, often a B-tree, that speeds up finding rows by a particular column, like the index at the back of a book. Without an index, the database may scan every row; with one, it can jump to matches quickly. Indexes speed reads but take space and slow writes slightly, since they must be updated too.
Worked example
A school stores Students(StudentID, Name), Courses(CourseID, Title), and Enrolments(StudentID, CourseID). StudentID is the primary key of Students and a foreign key in Enrolments. To list Ana's courses, the database finds Ana's ID in Students, finds matching Enrolments rows, and looks up each CourseID in Courses. Each student's name is stored only once, however many courses they take.
Key takeaways
Databases avoid the duplication, inconsistency, and conflicts of loose files; a DBMS manages them. Codd's 1970 relational model uses tables of rows and columns. Primary keys identify rows; foreign keys link tables. Relationships are one-to-one, one-to-many, or many-to-many, the last needing a linking table. Normalisation reduces redundancy. Transactions follow ACID. Indexes speed searches at a small cost to storage and writes.
Flashcard
Organised collection of data
Check your understanding
A bank transfer debits one account, but the system crashes before crediting the other. Which ACID property prevents money from vanishing?