Relational databases

Welcome to the World of Databases!

Ever wondered how Spotify remembers your favorite songs, or how your school keeps track of thousands of students without losing their grades? The answer is databases! In this chapter, we are going to explore how data is organized, stored, and linked together using Relational Databases. Don't worry if it seems like a lot of technical words at first—we’ll break it down piece by piece!

Did you know? Nearly every app on your phone uses a database to store your settings, messages, and high scores.

1. What is a Database?

Simply put, a database is a persistent (long-lasting) store of organized data. Instead of keeping information in a messy pile, a database keeps it tidy so it’s easy to find and update.

What makes it a "Relational" Database?

A Relational Database is a type of database that stores data in more than one table. These tables are then "related" (linked) to each other using special keys.

Analogy: Imagine a school's paper filing system. If they kept everything in one giant folder, it would be impossible to find anything! Instead, they have different drawers: one for Students, one for Teachers, and one for Classes. These drawers are like the tables in a relational database.

Key Takeaway: A relational database uses multiple linked tables to stay organized and efficient.

2. The Building Blocks: Tables, Records, and Fields

To understand how a database works, we need to look at the structure of a single table. It’s very similar to a spreadsheet (like Excel or Google Sheets).

• Table: The whole collection of data about one specific thing (e.g., a "Students" table).
• Record: A single row in the table. It contains all the information about one specific item or person. Example: All the details for one student named Sarah.
• Field: A single column in the table. It holds one specific type of information. Example: A column for "Date of Birth".
• Data Type: Each field must have a specific data type (like Integer for age, or String for a name). This ensures the data is entered correctly!

Memory Aid:
Field = Form (the little box you fill in).
Record = Row (the horizontal line of data).

Quick Review: Which one is a row? A Record! Which one is a column? A Field!

3. Linking Tables: Primary and Foreign Keys

In a relational database, we need a way to tell the computer that a record in Table A belongs to a record in Table B. We do this using Keys.

The Primary Key

Every table must have a Primary Key. This is a unique identifier for every record in that table. No two records can have the same Primary Key.

Example: You probably have a Student ID number. Even if there are two people named "John Smith" in your school, your ID numbers will be different. That ID is your Primary Key.

The Foreign Key

A Foreign Key is a field in one table that is the Primary Key in a different table. This acts as a link or a "bridge" between the two tables.

How it works:
1. In the Students table, StudentID is the Primary Key.
2. In the Library_Books table, we add a field for StudentID so we know who has borrowed which book.
3. In the Library_Books table, StudentID is the Foreign Key because it links back to the main Students table.

Key Takeaway: Primary Keys identify; Foreign Keys link.

4. Why bother with Relational Databases?

You might be thinking, "Why not just put everything into one big table?" Using multiple tables helps us solve two big problems: Redundancy and Inconsistency.

Eliminating Data Redundancy

Data Redundancy means storing the same data more than once. This is a waste of storage space!
Example: If we had one big table for "Library Loans," we would have to type the student’s name, address, and phone number every single time they borrowed a book. In a relational database, we just store that info once in the "Students" table and use their ID to link them.

Eliminating Data Inconsistency

Data Inconsistency happens when the same data is stored in two places and one version is updated but the other isn't.
Example: If Sarah moves house, we only have to update her address in one place (the Students table). If we had many tables with her address, we might forget to update one, leading to "inconsistent" data where the computer doesn't know which address is correct.

Common Mistake to Avoid: Students often think "Redundancy" and "Inconsistency" mean the same thing.
- Redundancy = Duplicated data (waste of space).
- Inconsistency = Conflicting data (errors).

Key Takeaway: Relational databases make data more accurate and save storage space by reducing duplication.

Quick Review Box

Database: Persistent, organized store of data.
Table: Organized in Rows (Records) and Columns (Fields).
Primary Key: Unique ID for a record.
Foreign Key: Links to a Primary Key in another table.
Relational Advantage: Stops data Redundancy and Inconsistency.

Don't worry if this feels like a lot to remember. Just keep in mind the "School Drawer" analogy! Once you understand how tables link together using IDs, the rest of database theory becomes much easier.