Database Fundamentals

Phu Tran
8/2/2025
We’re just being flooded in bits
The whole Wikipedia: ~ 100 TB

2
We’re just being flooded in bits

3
Database in an Application Context
You are writing Python codes for your project = You are making an application

Full application stack:

• Application logics (Backend or majority of the Python codes)
• Data storage and retrieval (Database and data models)
• User interaction (Frontend or GUI – graphical user interface)

Your first Python project may have only application logics, without any database or GUI.

Developers:
• Backend developer: who implements highly efficient logics (algorithms) of the backend
• Frontend developer: who designs and implements GUI template with great User
Experience/Interaction (UX/UI)
• Data engineer: Who designs, maintains and optimizes database for efficient & reliable
read/write operations
• Full-stack developer: Who is excited about the role at the beginning and ends up switching to
one of the above roles.
4
Why you should think in an application context
You’re at Mayo → You are not working on your first
Python project anymore. Database now becomes
part of any projects.
Backend Database:
• Backend reads data from database
GUI • Backend processes the data:
• Modify existing data
• Create new data
Backend
• Delete data
• Create data transformations
• Backend writes the results to the database:
Database • Update a row in the database
• Insert a row
• Delete a row

5
Why you should think in an application context
You’re at Mayo → You are not working on your first
Python project anymore. Database now becomes
part of any projects.
Backend GUI:
• Backend receives a request from GUI (user)
GUI • Backend determines the data required to
response to the user’s request
• Backend Database communications
Backend happening
• Backends mixes a transformation of
processed data to a GUI template and send
the mixed view (template + processed
Database
data) to the GUI for user presentation.

6
Why you should think in an application context
You’re at Mayo → You are not working on your first But why am I talking about these?
Python project anymore. Database now becomes
part of any projects. You should try to separate and ideally
isolate codes that perform different
roles in the system.

GUI You should never write functions that read and

process or write the data together!
Best practice: One function one atomic task,
Backend no matter how small the task is.

Organize the Python project such that:

Database • Codes that do app logics live in a folder
• GUI templates live in another folder
• Codes that directly call data queries live
in a different folder
• Codes that perform communications
between those components are often in
the app logics.
7
Why you should think in an application context
You’re at Mayo → You are not working on your first
Python project anymore. Database now becomes
part of any projects.

GUI Wait! I love Jupyter notebooks and

scared of writing *.py scripts. How do
Backend I separate database codes from main
project’s logics if I want to continue
Database coding in notebooks?

8
 Always think through the lens of System Design,
even if you are just writing a single Python function to query data.
You will become better after every line of code.

9
SQL Database – Structured Query Language
Let’s design a database for a simple Online English Essay Writing Course.

The initial requirements:

• More than 100 students in a cohort.
• 10 teachers in a cohort
• Each essay receives comments from 3 teachers

10
Database design for English Course
First design
This data description is called
a table schema.

How data look in the database

11
Database design for English Course
Newly added requirements:
• More than 100 students in a cohort.
• 10 teachers in a cohort
• Each essay receives comments from 3 teachers
• Students can submit up to 3 versions of the essay on a fixed topic.
• Teachers must feedback on all 3 versions of a student’s essay.

12
Database design for English Course
Second design

How data look

13
Database design for English Course
Newly added requirements:
• More than 1000 students in a cohort.
• 100 teachers in a cohort
• Each essay receives comments from 3 teachers
• Students can submit up to 3 versions of the essay on a fixed topic.
• Teachers must feedback on all 3 versions of a student’s essay.
• Now students are required to submit essays on more than one topics.
• Three comments from teachers required for an essay on any topic.

Are you going to add the following fields to the existing table?
• Essay v1 topic 1
• Essay v2 topic 1
• Essay v1 topic 2 What if:
• … • New topics keep arising during the course
• Comment 1 essay v1 topic 1 • Not all students submit 3 times for a topic
• Comment 2 essay v1 topic 1 • Any 3 teachers can give feedback, how students
• … know whose are the comments?
• Comment 1 essay v3 topic 1 • etc.
• …
14
Relational database comes to rescue
• Spread information across multiple tables
• Each table should describe only a single entity in the system
• Define relationships among all the tables
• Database efficiency depends on how well we decompose the system into
sufficiently small entities and define relationships among them.

What kinds of entity do we have for the English Course? Each of these entities is called a Data Model.
• Student So, in the database language, we have
• Teacher • student model
• Essay topic • teacher model
• Essay • essay model
• Comment • essay topic model
• comment model.

15
More operational design: First database schemas

PK

PK FK
FK

PK

FK
FK
PK
PK

16