Relational Database Concepts: Tables, Keys and Relationships

Alex: We're moving into Unit 4: Database Design and Development, and today we're covering the foundational concepts of relational databases. Sam, what is a relational database and why is it so dominant?

Sam: A relational database organises data into tables, where each table represents a single type of entity, and each row in the table represents one instance of that entity. The relational model was developed by Edgar Codd at IBM in the early 1970s, and it dominated data storage because of its mathematical foundation and its ability to efficiently manage structured data with integrity.

Alex: What are the key components?

Sam: Tables, also called relations, are the core structure. Each table has columns, also called attributes or fields, that define what type of information the table stores. Each row, also called a record or tuple, contains the actual data for one entity instance. So a Customer table might have columns for customer ID, name, email address, and phone number, with each row representing one customer.

Alex: And primary keys and foreign keys?

Sam: A primary key is the column or set of columns that uniquely identifies each row in a table. No two rows can have the same primary key value, and it can't be null. This is the database's way of guaranteeing that it can always find and uniquely identify any specific record. A customer ID is a classic example.

Alex: And foreign keys create the relationships?

Sam: Exactly. A foreign key is a column in one table that references the primary key of another table. If you have an Orders table, you might have a CustomerID column that references the ID column in the Customer table. This tells the database that each order belongs to a specific customer. The database can then enforce what's called referential integrity: it won't let you create an order for a customer that doesn't exist.

Alex: Why does referential integrity matter?

Sam: It prevents orphaned records, which are records that refer to something that no longer exists. Without referential integrity, you might delete a customer but leave all their order records behind, pointing at a customer ID that no longer means anything. This creates data inconsistency, which causes all sorts of problems for applications that try to use that data.

Alex: How does this differ from just storing data in a spreadsheet?

Sam: Several important ways. A database can handle multiple simultaneous users with full concurrency control, ensuring that two users updating the same record at the same time don't corrupt each other's changes. It enforces constraints that a spreadsheet simply doesn't; anyone can put anything in any cell of a spreadsheet. It can handle relationships between data efficiently and at scale. And it provides a query language, SQL, that allows you to extract precisely the data you need from potentially millions of records in milliseconds.

Alex: That's a compelling case. So understanding the relational model properly is the essential foundation for everything that follows.

Sam: Absolutely. The entities, their attributes, and the relationships between them are the conceptual model from which everything else in database design flows.

Alex: Brilliant. Next we'll be looking at how to visualise these relationships using Entity-Relationship diagrams.