Database Management

• Databases are made of tables, which are made of fields (columns) and records (rows).
• Relational databases link multiple tables together using primary keys and foreign keys to avoid repeating data.
• Data types (numerical, alphanumerical, date/time, Boolean, currency) control what kind of data a field can store.
• Validation (length, type, range, presence checks) stops bad data getting into the database in the first place.
• Sorting arranges records in ascending/descending order, using one or more criteria.
• Searching uses queries, operators (AND/OR/NOT/comparison) and wildcards (%, _, *) to pull out exactly the records you want.
• Outputs (reports) present the data neatly using headers, footers, tabular/columnar layouts, and formatted numbers.

Think of a database like a giant, super-smart spreadsheet system. Instead of scribbling information on random bits of paper, a database is an organised collection of data that lets you store, find, and manage huge amounts of information easily. Because it can be stored on remote servers, lots of people can access and use it at the same time — this is exactly how apps like online shops or banking systems work behind the scenes.

Imagine a table as a grid, like a mini spreadsheet. Every database table is built from two things:

• Field = a single column. It holds ONE type of information about every entry, e.g. "colour".
• Record = a single row. It's the COMPLETE set of information about one thing, e.g. everything about car #2.
• Table = the whole grid. A complete set of records about the same subject, e.g. all cars.

A relational database spreads data across multiple linked tables instead of cramming everything into one giant table. Why bother? Because it stops you repeating the same information over and over (this is called reducing data redundancy), which makes the database smaller, quicker, and easier to keep accurate. Tables are connected using keys.

This is one of the most important ideas in the whole chapter, so let's build it up with the example from the notes: an orders table and a customers table.

order_id