Welcome to Database Management!

Ever wondered how music streaming apps keep track of millions of songs, or how your school knows exactly which classes you are in? They use databases. A database is simply a structured way of storing information so that it can be searched and updated easily. In this chapter, we will learn how to build, organize, and use these powerful tools.

1. The Building Blocks of a Database

To understand a database, think of it like a digital filing cabinet. Here are the terms you need to know:

  • Table: This is where all the data about a specific "thing" (like students or products) is kept. It looks like a grid with rows and columns.
  • Field: A single column in a table. It stores one specific piece of information, like a First Name or Price.
  • Record: A single row in a table. It contains all the information about one specific item, like everything we know about "Student ID 001".
  • Primary Key: This is a unique field that identifies every record. No two records can have the same Primary Key. Example: Your Passport Number or School ID.
  • Foreign Key: This is a field in one table that is the Primary Key in another table. It is used to link tables together.
  • Relationships: This is the "link" created between two tables using Primary and Foreign keys so that data can be shared across the database.

Quick Tip: If you find it hard to remember the difference between a field and a record, remember: Field is Format (the column title), and Record is Real data (the whole row).

2. Data Types: Telling the Computer What’s What

When you create a Field, you must tell the database what kind of data will be inside it. This helps the computer save space and prevent errors.

  • Alphanumeric / Text: Used for letters, symbols, and numbers that don't need calculation (like a phone number or address).
  • Numeric / Number: Used for numbers that you might want to add, subtract, or average.
  • Date: Stores dates (e.g., \(DD/MM/YYYY\)).
  • Currency: Automatically adds a currency symbol (like \(£\) or \$\) and sets the decimal places.
  • Logical / Boolean: Only has two options, like Yes/No, True/False, or On/Off.

Did you know? We use Text for phone numbers because we never need to do math with them, and they often start with a zero (which Number fields might delete!).

3. Validation: The "Bouncer" of the Database

Validation is an automatic check performed by the software to ensure the data entered is sensible and allowed. It doesn't check if the data is "correct" (that's verification), only that it follows the rules.

  • Presence Check: Ensures a field is not left empty. Example: You must enter an email address to sign up for a site.
  • Range Check: Ensures a number falls within a certain limit. Example: An exam score must be between \(0\) and \(100\).
  • Type Check: Ensures the data is the right type. Example: You can't type "Blue" into a Number field.
  • Length Check: Ensures the data has the right number of characters. Example: A password must be at least 8 characters long.

Key Takeaway: Validation prevents "Garbage In, Garbage Out" (GIGO). If you put bad data in, you'll get bad results out!

4. Working with Data: Sorting and Searching

Once your data is in, you need to be able to find what you're looking for.

Sorting

You can organize records into a specific order:

  • Ascending: \(A\) to \(Z\) or \(1\) to \(10\).
  • Descending: \(Z\) to \(A\) or \(10\) to \(1\).
  • Multiple Fields: You can sort by more than one field. Example: Sort by Surname (Ascending), then by First Name (Ascending).

Searching (Queries)

A Query is a way of asking the database a question to find specific records. You use criteria to filter the data.

Relational Operators:

  • \( = \) (Equal to)
  • \( < \) (Less than)
  • \( > \) (Greater than)
  • \( <= \) (Less than or equal to)
  • \( >= \) (Greater than or equal to)
  • \( <> \) (Not equal to)

Logical Operators:

  • AND: Finds records that meet all criteria. Example: Price \( < 10 \) AND Color \( = \) "Red".
  • OR: Finds records that meet any of the criteria. Example: City \( = \) "London" OR City \( = \) "Paris".

Quick Review: If you want to find students who are in Grade 10 and also play Football, use AND. If you want students who play either Football or Basketball, use OR.

5. Input and Output

How do we get data in and out of the system?

  • Forms: These are used for inputting data. They are much easier for people to use than typing directly into a big table. They can include buttons and drop-down menus.
  • Reports: These are used for outputting data. They present the results of a query in a professional way, ready for printing.
  • Mail Merge: Databases can be linked to Word Processors to create personalized letters or labels for thousands of people at once.

The Two "Views"

Don't worry if you get confused between these two in the exam—just remember what you want to do:

  • Design View: Used to build the structure (naming fields, setting data types, and creating validation). You don't see the actual data here.
  • Data View (or Datasheet View): Used to see, enter, or edit the actual records in the table.

Common Mistakes to Avoid

1. Using Number for Postcodes: Postcodes (like SW1A 1AA) contain letters, so they must be Alphanumeric/Text.

2. Confusing Primary and Foreign Keys: Remember: The Primary Key is the "Boss" in its own table. When it visits another table to create a link, it becomes a Foreign Key.

3. Forgetting the Unit: In the practical exam, if you are asked for a currency field, make sure it is set to 2 decimal places and shows a currency symbol!

Summary Checklist

  • Do I know the difference between a Field and a Record?
  • Can I choose the correct Data Type for any given example?
  • Can I explain how a Range Check or Presence Check works?
  • Do I understand how to use AND and OR in a search?
  • Can I identify a Primary Key in a table?