Database solutions

Welcome to Database Solutions!

In this chapter, we are going to explore how the digital world keeps track of everything. From your favorite streaming service remembering which episode you watched last, to a school keeping track of thousands of students, databases are the secret engines behind it all. Don't worry if it sounds a bit technical at first—we'll break it down into simple pieces using everyday examples!

5.1 Data and Information

Before we build a database, we need to understand what we are putting inside it. People often use the words "data" and "information" as if they are the same thing, but in IT, they have very specific meanings.

Data vs. Information

Data consists of raw facts and figures. On its own, it doesn't have much meaning. Think of it like raw ingredients (flour, eggs, sugar) sitting on a counter.
Information is data that has been processed, organized, or structured so that it is useful. This is like the finished cake you bake using those ingredients!

Example: The number "190524" is raw data. If we tell you it is a date (19/05/24), it becomes information.

Structured vs. Unstructured Data

Data comes from many sources, and it usually falls into two categories:
• Structured Data: This is very organized. It fits perfectly into tables with rows and columns. Think of an Excel spreadsheet or a digital address book.
• Unstructured Data: This is "messy" data. It doesn't have a pre-defined format. Examples include emails, social media posts, videos, and audio files. It is much harder for a computer to analyze this automatically.

Why is this valuable?

For an organization, extracting meaningful information from data is like finding gold. It helps them make better decisions. For example, a supermarket can look at data about what people buy to get information on which items are popular, helping them decide what to restock.

Quick Review: Data is raw; Information is processed and useful. Structured data is tidy; Unstructured data is messy.

5.2 Structured Data & Relational Databases

When we have lots of structured data, we use a Relational Database. Why? Because it keeps things organized and prevents us from having to type the same thing over and over again!

The Structure of a Relational Database

Imagine a database as a collection of Tables. Each table is made up of:
• Fields: These are the columns. Each field holds one specific piece of detail (like "First Name" or "Phone Number").
• Records: These are the rows. One record represents one complete "thing" (like one student or one order).

The "Keys" to Success

To keep everything connected, we use special types of keys:
• Primary Key: A unique identifier for every record. Just like your ID card number, no two people in the table can have the same one.
• Foreign Key: This is a primary key from one table that appears in another table to link them together. It’s like a "pointer" connecting a student to their class.
• Composite Key: Sometimes, one field isn't enough to be unique. A composite key uses two or more fields together to create a unique ID.

Entities and Relationships

In a database, an Entity is just a fancy word for a "thing" we want to store data about (like a Customer, a Product, or a Car). Relationships describe how these things connect:
• One-to-One (1:1): One person has one passport. One passport belongs to one person.
• One-to-Many (1:M): One mother can have many children, but each child has only one biological mother. (This is the most common type!)
• Many-to-Many (M:M): Many students take many subjects. One subject has many students.

Entity Relationship Diagrams (ERDs)

An ERD is a map of the database. It uses boxes for Entities and lines to show the Relationships. When you see a line with a single dash, it means "one." A line that spreads out like a "crow's foot" means "many."

Key Takeaway: Tables are linked using Keys. The Primary Key is the "Unique ID," and the Foreign Key is the "Link."

5.3 Structured Query Language (SQL)

SQL (often pronounced "Sequel") is the language we use to talk to databases. It allows us to ask questions (queries) and change the data.

Creating and Managing Tables

Before we add data, we need to build the "box" it sits in:
• CREATE TABLE: Used to set up a new table and define the data types (like Text, Integer, or Date) for each field.
• INSERT: Adds a brand new record to a table.
• AMEND (UPDATE): Changes data that is already there (like updating a customer's address).
• DELETE: Removes a record forever.

Finding Data (Queries)

The most common command is SELECT. It follows a simple pattern:
SELECT [which fields you want]
FROM [which table]
WHERE [the rule/condition]
ORDER BY [alphabetical or numerical order]

Advanced SQL Tools

Sometimes we need to get fancy:
• JOIN: Used to link two tables together in a query using their keys.
• UNION: Combines the results of two different queries into one list.
• Wildcards: The % symbol stands for "any number of characters," and the _ symbol stands for "exactly one character."
Example: WHERE Name LIKE 'S%' will find "Sam," "Sarah," and "Steven."

Functions

SQL can also do math for you!
• COUNT: Tells you how many records match your search.
• GROUP BY: Gathers identical data into groups (like grouping all students by their "Grade" level).
• ORDER BY: Sorts your results (A-Z or High-Low).

Don't worry if the code looks strange at first! Just remember: SELECT the fields, FROM the table, WHERE the rule is met. It’s like giving the computer a very specific set of instructions.

Summary Checklist:

• Can you explain the difference between data and information?
• Do you know the difference between a Primary Key and a Foreign Key?
• Can you identify a 1:M relationship?
• Do you know the basic structure of a SELECT statement?

Common Mistake to Avoid: Never forget the WHERE clause when using DELETE! If you just type DELETE FROM Students, the computer will delete every single student in the school! Always be specific.