🖥️ Week 03, Day 01 - Lecture

Designing Databases and Merging Data

Author

Dr Jon Cardoso-Silva

Last updated

31 July 2025

🥅 Learning Objectives

By the end of this session, you should be able to: i) Use pandas.merge() to combine related DataFrames, ii) Explain the limitations of using flat files for relational data, iii) Design a simple, normalised database schema, iv) Store pandas DataFrames in a SQLite database, v) Write basic SQL JOIN queries to retrieve and combine data.

Welcome back! We will start by checking in on your progress with the 📦 Final Project - today’s session is is directly relevant to your project! We’ll learn how to organise your collected data into proper database structures, design relationships between different parts of your data, and query your data efficiently using SQL. This content will help you with NB02 (database design) and NB03 (analysis) - the next steps after your data collection.

⏰ Monday, 28 July 2025 | 10:00am - 1:00pm 📍 Location: CKK.2.06 (see LSE’s 🗺️ campus map)

Project Check-in & Today’s Roadmap

10:00 – 10:15

📒 Project Status Check

We will start with a quick poll to see how everyone is doing with their 📦 Final Project before we dive into today’s content.

Part 1: From pandas to Databases

10:15 – 10:45

Setting the Stage

We’ll combine Reddit posts and comments using pandas.merge(). This shows how to join related data using tools you know.

Key Concepts

Data Collection: We will reuse the data collection code from the 🖥️ Week 02 Day 04 Lecture.
The pandas Bridge: Understanding pd.merge() and join types
Limitations: Why CSV files don’t work well at scale

Notebook Focus

Watch how we link posts and comments using post_id. This shows the basics of relational data.

Quick Stretch Break & Check-in

10:45 – 10:50

Part 2: Database Design Fundamentals

10:50 – 11:30

Why Use Databases?

We’ll explore why organisations need databases instead of large CSV files, focusing on data integrity and scalability, as we explore how databases are structured in practice.

Database Schema Design

Normalisation & Keys: How to structure data relationships
SQLite Introduction: Creating tables with proper data types. A nod to our 🖥️ Week 01 Day 02 Lecture.
Populating the Database: Moving data from pandas to SQLite

Key Learning Moment

Learn to design a two-table schema - an essential step for your 📦 Final Project.

☕️ Coffee Break

11:30 – 11:45

Part 3: SQL Queries and Analysis

11:45 – 13:00

SQL JOIN

We’ll recreate our pandas merge using SQL JOIN to show how databases handle data relationships.

Query Building Blocks

Basic SELECT: Getting data
WHERE clauses: Filtering
JOIN operations: Combining tables
GROUP BY: Summarizing data

Follow Along

Compare SQL and pandas solutions side-by-side.

📓 Lecture Notebook

Get the notebook for today’s hands-on work:

Save it to your me204-study-notes repository or find it in Nuvolos under lab-notebooks/.

After the Lecture

This afternoon: we will recap and revisit some SQL and then help you organise your collected data into proper database structures and writing SQL queries to analyse it. You’ll apply today’s concepts directly to your project data.

💻 Today’s Lab

Designing schemas and joining data.

➡️ Go to Lab Instructions

Questions?

➡️ Ask on Slack

🔗 Extra Resources: Databases & SQL

Useful references and cheatsheets.