Data Sources and Database Management

Good analysis starts with good data — but how do we get it, understand it, and organize it? In this chapter we’ll explore the shapes data takes, how it’s collected, and how to build a database that makes storing, searching, and pulling out data easy.

The Big Picture#

The world is full of information, but raw data is like a messy pile of LEGO bricks. To build something useful — a reliable business report, a medical study, a sales forecast — we need to know where the bricks come from, how to sort them, and how to snap them together in a way that lets us quickly find the piece we need. This chapter answers the question, “How can we collect, store, and retrieve data so it’s trustworthy and ready for decision-making?” We’ll explore the nature of data, major collection methods, the rise of “big data,” and the core ideas behind relational databases that let us query and shape data without losing our minds.

Understanding Data: Populations, Samples, and Types#

Data can feel abstract, but it’s just recorded facts. Understanding the population we care about and the smaller group we actually measure — the sample — is the first step.

Imagine you run a cereal factory and want to know how much cereal your machine fills in each box. You can’t weigh every single box (the population); that would take forever. Instead, you pull a random set of boxes off the line and weigh those. That set is your sample.

Population: The entire collection of people, items, or events we want to learn about. Sample: A subset of the population that we actually observe or measure.

The goal is for the sample to reflect the population well, so we use random sampling — choosing members in a way that gives every member an equal chance of being picked. This helps avoid bias.

Random sampling: A method where each member of the population has a known, non-zero chance of being selected.

Once we have a sample, we need to know what kind of data each piece is, because different types lead to different analyses. Think of a student record: name, grade level, number of absences, last test score. Data generally falls into two big buckets.

Categorical data (qualitative): Values that describe a quality or category — words or labels, like eye colour, type of car, or survey choice “Yes/No.” This can be nominal (just names, like “vanilla,” “chocolate”) or ordinal (categories with a natural order, like “small,” “medium,” “large”). Quantitative data (numerical): Values that are meaningful numbers you can do math with — weight, height, income, number of customers. This splits further into discrete (whole-number counts, like “how many phones sold”) and continuous (measurements on a scale, like “height in cm”).

Data also varies across time. A snapshot taken today is different from tracking a variable over many years.

Cross-sectional data: Data collected at a single point in time, like a census on one day or a survey conducted in a week. Each observation is a snapshot of different subjects. Time series data: Data collected repeatedly over successive time periods, like daily stock prices or monthly sales for three years. The order matters — we want to see trends and patterns.

Knowing these types helps us pick the right tool later. A graph for time series (line chart) is different from a graph for categorical data (bar chart), and the math we can do with a sample changes depending on whether we have numbers or categories.

📝 Section Recap: Data starts with a population; we usually work with a random sample to draw conclusions. The sample’s measurements are either categorical (labels) or quantitative (numbers), and the timing — cross-sectional snapshot or time series over periods — shapes how we analyse it.

Collecting Data: Studies and Sources#

Where does the data actually come from? Broadly, we either create it ourselves through a planned study or we use data that already exists somewhere else.

Designed Studies#

When we actively design a study, we’re often trying to find cause and effect. There are two main flavours.

• Experimental study: The researcher deliberately controls one or more variables. For example, a drug trial randomly splits patients into a treatment group and a control group, then measures the outcome. The key is the researcher assigns the condition. This is the strongest setup for showing causation.
• Observational study: The researcher simply observes and records without interfering. A study linking coffee consumption to heart health where people report their own habits is observational. No one is told to drink more coffee. Because we don’t control who gets the “treatment,” we can only find associations, not definite causes.

Experimental study: A study where researchers apply a treatment or condition and observe the effect, using random assignment when possible. Observational study: A study where researchers observe subjects and measure variables without assigning treatments.

Internal and External Data#

Organizations often have a lot of data they already own, plus vast public resources they can tap into.

• Internal data: Data that originates within an organization — sales transactions, customer service logs, employee records, inventory levels. This data is usually highly specific and often well-structured, but it may be scattered across different software systems.
• External data: Data collected by someone outside the organization. This can be free government data (census figures, economic indicators, weather records), purchased market research, social media feeds, or public datasets from organisations like the World Bank. Government databases are a goldmine for analysts: they provide large, carefully collected datasets on population, health, trade, and more — often freely accessible.

When you combine internal sales data with external economic data like regional unemployment rates, you can ask richer questions: “Do our sales drop when unemployment rises in a certain area?”

📝 Section Recap: Data can be collected actively (experimental gives strong causal clues, observational gives real-world patterns) or sourced from within an organization and from external public repositories. Understanding the source helps you judge reliability and what conclusions you can draw.

Big Data and Its Characteristics#

Modern technology generates data at a mind-boggling scale. The term big data isn’t just “a lot of rows” — it’s usually described by a few key features, often called the “V’s.”

Big data: Datasets so large or complex that traditional data-processing methods are inadequate.

Four of the most important V’s are:

• Volume: The sheer amount of data — terabytes, petabytes, or beyond. Think of a social media platform processing billions of posts daily.
• Velocity: The speed at which new data is created and needs to be handled. Stock tickers streaming live prices or sensors on a jet engine sending real-time data are high-velocity examples.
• Variety: The many different forms data can take — structured tables, text documents, images, videos, audio files, sensor logs. A hospital might hold patient records (structured), doctor’s notes (unstructured text), and MRI scans (images) all at once.
• Veracity: The quality and trustworthiness of the data. With so much data pouring in from different sources, inaccuracies, noise, and missing values are common. Poor-quality data leads to poor decisions, so cleaning and verifying are critical.

Together these characteristics push us beyond simple spreadsheets and into more powerful database and analytical systems. But the underlying principles of good data management — like the ones we’ll study next — still apply.

📝 Section Recap: Big data is defined not just by size (volume) but also by the speed of inflow (velocity), the mix of formats (variety), and the need to check data quality (veracity). Handling it still demands careful organization and querying.

Designing a Relational Database#

When you have more than a few thousand rows, a spreadsheet starts to groan. A relational database organizes data into multiple related tables, each with a specific subject, to reduce duplication and make retrieval lightning-fast.

Tables, Primary Keys, and Foreign Keys#

Think of a library catalogue. Instead of writing every single detail of a book and its author on one giant card, you can have one file for books and another for authors, linked by an author ID. In a database, these files are tables.

Table: A structure of rows and columns that holds data about a single subject. Each row is a record (like one customer), and each column is a field (like name, address, phone).

Every table needs a way to uniquely identify each row. That’s the primary key.

Primary key: A field (or combination of fields) that uniquely identifies each record in a table. No two records can have the same primary key value, and it cannot be left blank.

For a Customers table, “CustomerID” works perfectly. For a Products table, “ProductID” is the natural choice.

Now, how do we connect an order to the customer who placed it? By using a foreign key.

Foreign key: A field in one table that matches the primary key of another table. It establishes a logical link between the two tables.

An Orders table would contain “CustomerID” as a foreign key, referring back to the primary key in the Customers table. This is like saying, “This order belongs to customer 42.” Through this simple link, we can find every piece of information about that customer without storing their name and address in every single order row.

Relationships and Referential Integrity#

The links between tables are called relationships. The most common type is a one-to-many relationship: one customer can place many orders, but each order belongs to exactly one customer. In the tables, this is represented by the foreign key (CustomerID) on the “many” side (Orders).

A powerful rule that databases enforce is referential integrity. It means that a foreign key value must already exist as a primary key in the related table — or be left blank if allowed. If you try to create an order for CustomerID 999, but that ID doesn’t exist in Customers, the database will politely refuse. This keeps your data clean and prevents orphan records — orders that float around without a real customer.

Referential integrity: A constraint that ensures foreign keys correctly reference existing primary keys, maintaining consistency between linked tables.

Together, these design principles give you a solid, flexible foundation. You can add customers, add orders, and join them later without ever worrying about mismatched data.

📝 Section Recap: A relational database splits data into subject-based tables connected by primary keys (unique IDs) and foreign keys (IDs that link back). Referential integrity acts like a safety net, ensuring all links are valid and no orphan records slip through.

Building and Populating Your Database#

With the design in mind, it’s time to actually create the tables and fill them with data. Most database systems like Microsoft Access give you two ways to build a table.

• Datasheet view: This looks and feels like a spreadsheet. You can start typing data directly into rows and columns, and the database will guess the data type for each column (text, number, date). It’s quick and intuitive for small, simple tables.
• Design view: This is the behind-the-scenes blueprint. You specify each field’s name, data type (Text, Number, Date/Time, Currency, etc.), and any extra rules like field size or whether it’s required. You also set the primary key from here. Design view gives you precise control and is the professional way to build a solid database.

For our Customers table, you might use Design view to create fields: CustomerID (AutoNumber, primary key), FirstName (Short Text), LastName (Short Text), SignupDate (Date/Time). Then you can switch to Datasheet view to see the empty table and start entering records.

Importing Data from External Sources#

Typing in thousands of records by hand is nobody’s idea of fun. So databases let you import data from other files.

Common import sources:

• Excel workbooks: You can pull in a whole spreadsheet or a named range. If the first row has column headings, the database can often map them to table fields.
• CSV (comma-separated values) files: A simple text format where values are separated by commas — almost any software can create these.
• Text files, XML, or other databases: Databases often speak many languages.

During import, you’ll be asked which table to target (or you can create a new one), whether to append the data to existing records, or replace everything. If your external file has the same structure as your table, the data flows in smoothly.

📝 Section Recap: Tables can be created with a spreadsheet-like Datasheet view or a precise Design view where you define every field. To fill them, you can import structured data from Excel, CSV, and other sources — saving immense manual effort.

Finding and Shaping Data with Queries#

Having data is one thing; finding exactly the slice you need is where the power of a database really shines. This is done with queries — requests the database understands to search, filter, combine, or update data.

Simple Filtering and Sorting#

The easiest way to narrow your view is sorting and filtering directly in a table’s Datasheet view.

• Sorting: order records by a column (alphabetically, numerically, by date ascending or descending). One click puts your customers in order of last name.
• Filtering: display only rows that meet a condition. “Filter by Selection” lets you instantly hide everything except records that match a cell you click. Or you can apply a filter that shows only customers who signed up after 1 January 2024.

Select Queries#

A select query is the database’s bread and butter. It lets you ask, “Show me just the customer’s first name, last name, and email, but only for those in postal code 2000, sorted alphabetically by last name.” You don’t see any extra clutter — only what you request.

Building a select query involves:

1. Choose the table(s) you want to pull from.
2. Drag the fields you want into the query grid.
3. Add criteria — e.g., "2000" under the PostalCode field.
4. Add a sort order if needed.
5. Run the query — the result looks like a temporary table, but it’s a live view of the data that matches your rules.

Select queries can also combine information from multiple related tables. By linking a foreign key to its primary key in the query design, you can see customer names alongside each order’s details in one combined result — even though the data lives in separate tables.

Action Queries#

Sometimes you want to change the data in bulk, not just view it. Action queries let you do that safely and efficiently.

Action query: A query that makes changes to the data — either updating existing records, appending new ones, deleting records, or creating a new table.

Four common types:

• Update query: changes values in one or more fields across many records. Example: “Increase all product prices by 5%.”
• Append query: adds records from one table to the end of another table. Great for merging newly imported batch data into a master table.
• Delete query: removes records that meet a condition. “Delete all customers who haven’t placed an order in two years.” Use with caution!
• Make-table query: creates a brand-new table from the results of a select query, capturing a snapshot of the data at that moment. Useful for preparing data before exporting.

Action queries should always be previewed as a select query first (so you see exactly which rows will be affected) before you run them.

Crosstab Queries#

When you want to summarize data in a compact, grid-like format — for example, total sales per product per month — a crosstab query does the magic.

A crosstab uses three ingredients:

• A row heading (product name)
• A column heading (month)
• A value (sum of sales amount), calculated at the intersections.

It’s like a pivot table in Excel: you turn a long list of transactions into an easy-to-read matrix. This makes it incredibly simple to compare categories across time or any other dimension.

Crosstab query: A query that summarizes data by grouping it into a two-dimensional grid with row headings, column headings, and a calculated value in each cell.

📝 Section Recap: Queries are the true workhorse of a database. Select queries filter and combine tables without altering the underlying data. Action queries update, append, delete, or make new tables. Crosstab queries reshape data into summarized grids that are much easier to scan and analyse.

Bridging to Analysis: Exporting Data#

The final piece of the workflow is getting the results out. While databases are fantastic for storage and retrieval, they’re not always the best tool for in-depth statistical analysis or creating rich charts. Exporting query results to Excel (or other analysis software) lets you pick up exactly where the database’s strengths end and analysis begins.

The process is straightforward:

1. Run your select or crosstab query so you can see the result set.
2. Use the “Export” command (often found in an external data menu) and choose Excel as the destination.
3. Specify options — whether to keep formatting, open the file automatically, or export only the filtered records.
4. Save the Excel file.

Now you have a clean, focused dataset ready for pivot tables, charts, formulas, or even feeding into more advanced analysis tools. This smooth handoff from database to spreadsheet is the backbone of many business reporting workflows.

📝 Section Recap: Databases excel at storing and querying, but exporting query results to Excel gives you a flexible environment for deeper analysis and visualization, creating a seamless pipeline from raw data to insight.

Summary#

We’ve covered the entire journey of data — from where it originates and the forms it takes, to how we design solid databases and fire off queries that slice and dice it into exactly the shape we need. The key takeaway: data isn’t scary when you break it down. By knowing its type and source, storing it cleanly in related tables, and mastering simple query tools, you can turn a flood of raw facts into a neatly organized stream ready for smart decisions.