Databases (Microsoft Access or similar)

TL;DR

Databases help you store and organise information efficiently, making it easy to find and use specific data. They use tables to hold related data, and these tables can be linked together to create powerful connections. Microsoft Access is a popular program that lets you create, manage, and query databases without needing complex coding.

1. The Mental Model

Think of a database as a super-organised digital filing cabinet. Instead of loose papers everywhere, everything has its specific place, making it simple to retrieve exactly what you need in seconds.

2. The Core Material

A database is a structured collection of information, or data, that's stored electronically in a computer system. The main goal is to store data in a way that makes it easy to add, update, delete, and retrieve information efficiently.

2.1 Tables

The foundation of a database is the table. A table is like a spreadsheet, organised into rows and columns.
- Rows (also called records) represent a single entry or item (e.g., one student, one product).
- Columns (also called fields) represent specific pieces of information about each entry (e.g., Student Name, Product Price).

For example, a table for students might have fields like StudentID, FirstName, LastName, and DateOfBirth.

2.2 Primary Keys

Photo by Jan van der Wolf on Pexels

A Primary Key is a special field (or combination of fields) in a table that uniquely identifies each record. It's like your ID number – no two people have the same one.
- It must contain unique values for each record.
- It cannot contain null (empty) values.
- It's crucial for linking tables together.

Example: In a Students table, StudentID would be a good primary key.

2.3 Relationships

Databases become really powerful when you link tables together using relationships. This prevents redundant data and ensures data integrity.

• Foreign Key: A foreign key is a field in one table that refers to the primary key in another table. It's how the link is made. For example, if you have a Courses table and a Students table, the Students table might have a CourseID field which refers to the CourseID primary key in the Courses table.

There are different types of relationships:
- One-to-Many: The most common. One record in the first table can relate to many records in the second table. (e.g., One teacher teaches many students).
- One-to-One: One record in the first table relates to exactly one record in the second table. (Less common, often used to split a very wide table).
- Many-to-Many: Many records in the first table can relate to many records in the second table. (e.g., Many students can take many courses). This usually requires an extra "linking table".

graph LR
    A["Students (StudentID, FirstName, LastName)"] --"StudentID (PK)"--> B["Enrollments (EnrollmentID, StudentID, CourseID)"];
    C["Courses (CourseID, CourseName, Credits)"] --"CourseID (PK)"--> B;
    B --"StudentID (FK)"--> A;
    B --"CourseID (FK)"--> C;

This diagram shows a Many-to-Many relationship between Students and Courses, resolved by the Enrollments linking table.

2.4 Queries

A query is a request for data or information from a database. You use queries to:
- Retrieve specific data: "Show me all students enrolled in 'Computer Applications'."
- Filter data: "Show me all products with a price greater than R500."
- Perform calculations: "Calculate the total number of students per course."
- Update or delete data: (Use with caution!)

In Access, you can build queries using the Query Design View, which lets you drag and drop fields and set criteria without writing complex code.

2.5 Forms

Forms provide a user-friendly interface for entering, viewing, and editing data in your tables. Instead of working directly with the raw table, which can be intimidating, a form presents the data in a more organised and attractive way. You can design them with buttons, text boxes, and drop-down menus.

2.6 Reports

Reports are used to present your data in a professional, printable format. You can summarise data, perform calculations, and format the output beautifully. For example, you might create a report showing all students' grades, grouped by course, or an inventory report showing all products currently in stock.

3. Worked Example

Let's say you're managing a small library. You have books and borrowers.

Step 1: Design Tables

• Books Table:

  • BookID (Primary Key, e.g., 001, 002)
  • Title (e.g., "The Hitchhiker's Guide to the Galaxy")
  • Author (e.g., Douglas Adams)
  • ISBN (e.g., 978-0345391803)
  • Genre (e.g., Science Fiction)
  • AvailableCopies (e.g., 3)

• Borrowers Table:

  • BorrowerID (Primary Key, e.g., 101, 102)
  • FirstName (e.g., Alice)
  • LastName (e.g., Smith)
  • PhoneNumber (e.g., 082-123-4567)

• Loans Table (to link Books and Borrowers for a Many-to-Many relationship, as one book can be borrowed by many people over time, and one person can borrow many books):

  • LoanID (Primary Key)
  • BookID (Foreign Key, links to Books.BookID)
  • BorrowerID (Foreign Key, links to Borrowers.BorrowerID)
  • DateBorrowed (e.g., 2023-10-26)
  • DateDue (e.g., 2023-11-09)
  • DateReturned (e.g., 2023-11-05, or empty if not yet returned)

Step 2: Create a Query

You want to see all books currently on loan and who has them.

1. In Access, you'd go to "Create" > "Query Design".
2. Add the Books, Borrowers, and Loans tables.
3. Drag the following fields to the design grid:
   • Books.Title
   • Books.Author
   • Borrowers.FirstName
   • Borrowers.LastName
   • Loans.DateBorrowed
   • Loans.DateDue
4. In the Criteria row for Loans.DateReturned, type Is Null. This will filter for loans where the DateReturned field is empty, meaning the book is still out.
5. Run the query. The result will show a list of books currently borrowed, by whom, and their due dates.

4. Key Takeaways

• Databases help organise large amounts of information efficiently using tables.
• Tables are made of records (rows) and fields (columns) to store specific data.
• Primary Keys uniquely identify each record within a table, ensuring no two are identical.
• Foreign Keys are used to link tables, creating relationships and preventing data duplication.
• Queries are powerful tools for searching, filtering, and summarising data from one or more tables.
• Forms provide an easy-to-use interface for data entry and viewing, without directly editing tables.
• Reports are used to present processed and summarised data in a professional, print-ready format.

Common mistakes you should avoid:
- Not setting a Primary Key: This makes linking tables and ensuring data uniqueness very difficult.
- Duplicating data: Don't put the same information in multiple tables; use relationships instead.
- Incorrect data types: Assigning the wrong data type (e.g., text for numbers) can cause errors and limit functionality.
- Poorly named fields: Use clear, descriptive names for your fields (e.g., FirstName instead of Fld1).

5. Now Try It

Create a simple database in Microsoft Access to manage your personal movie collection. Design two tables: Movies and Actors.
- The Movies table should have MovieID (PK), Title, Director, ReleaseYear, and Genre.
- The Actors table should have ActorID (PK), FirstName, LastName, and DateOfBirth.
- Now, think about how you'd link these two tables, given that a movie can have many actors, and an actor can be in many movies. Create the necessary linking table.

What success looks like: You'll have three tables (Movies, Actors, MovieActors - your linking table) with appropriate primary and foreign keys established, ready to accept data.