Welcome!
Please look over the Syllabus. We will be using Canvas and Gradescope to manage the class. Also homework assignments will be posted to this site.
Why Databases?
Modern computing is full of exciting things: artificial intelligence, social networks, graphics, robotics, distributed systems, and the web.
But underneath nearly all of them is a much less glamorous problem:
Where does the data go?
Consider some ordinary questions that computer systems have to answer:
- Who is this user?
- What classes is this student taking?
- Who follows whom?
- Has this customer already paid?
- How many items are left in inventory?
- What happens if two people try to buy the last item at the same time?
- What happens if the computer crashes halfway through an update?
- How do we find one record among a billion?
- How do we make sure that the answer we get is actually correct?
These are database problems.
A database is not simply a place where data is stored. A database management system (DBMS) provides tools for organizing, querying, updating, protecting, and maintaining data.
The important word here is managing.
You Could Just Use a File…
Suppose you are writing a program and need to save some information.
At first, the solution seems obvious:
I’ll put it in a text file.
Then the data gets more complicated:
Maybe I’ll use JSON.
Then you need to search it:
I’ll write some Python to find the records I need.
Then multiple people need to use it:
I’ll write a server.
Then two people try to modify the same record at the same time.
Then the server crashes halfway through an update.
Then you have 500 million records.
Then users want to ask questions that you never anticipated.
At some point:
Congratulations. You are inventing a database.
Database systems are the accumulated solutions to problems that seem easy until we try to solve them correctly, efficiently, and at scale.
What is a Database?
At a basic level, a database is an organized collection of data.
But storing data is the easy part. A useful database system also needs to answer questions such as:
- How should the data be represented?
- How do we prevent contradictory information?
- How do we efficiently retrieve the information we want?
- How do we update data without accidentally corrupting it?
- What happens when many users access the database simultaneously?
- What happens when hardware or software fails?
Different database systems solve these problems in different ways.
What Databases Can You Name?
Take a moment to think about the databases you’ve used or heard of. Here are a few examples:
- Relational databases (e.g., MySQL, PostgreSQL, Oracle)
- NoSQL databases (e.g., MongoDB, Cassandra)
- Cloud databases (e.g., AWS RDS, Google Firebase)
- File-based databases (e.g., SQLite)
Each of these has its own structure and uses, but they all have one thing in common: they store data that needs to be organized and managed efficiently. We will think about these principles in this class
This Course Is Not Really About SQL
We will learn SQL, and you will write quite a lot of it.
But SQL is not the main point of this course.
SQL is a language for interacting with relational databases. The more important goal is learning how to think about data.
By the end of the course, you should be able to reason about questions like:
| Question | Database Concept |
|---|---|
| How should information be represented? | Data models and schemas |
| How do we avoid contradictory data? | Constraints and normalization |
| How do we ask questions about our data? | Relational algebra and SQL |
| How do we answer those questions quickly? | Indexing and query processing |
| What happens when many users act at once? | Transactions and concurrency |
| What happens when something crashes? | Recovery and durability |
| How do we manage very large datasets? | Storage and database architecture |
You can always look up SQL syntax.
You can ask an AI system to generate a query.
But you still need to understand enough to ask:
- Is this query correct?
- Does this database actually represent the thing I think it represents?
- Will this query take 20 milliseconds or 20 hours?
- Can two simultaneous updates corrupt my data?
- What assumptions did this program make about the database?
- Syntax is cheap. Mental models are expensive.
This course is primarily about developing those mental models.
Thinking About Data Organization
Smart people think carefully about how data is organized. Data doesn’t just exist in isolation: it needs structure to be useful. In this class, we’ll explore the principles and best practices behind organizing data. This includes learning about normalization, table design, and query optimization.
We will also discuss how databases can be used to improve data integrity, prevent redundancy, and minimize errors.
Let’s consider a simple table:
| Student Name | SID | Major | Course | CRN | Term | Instructor |
|---|---|---|---|---|---|---|
| Alice | 9019958 | CS | CSE30246 | 12028 | F24 | Weninger |
| Robert | 9025547 | CPEG | CSE40497 | 27364 | S23 | Weninger |
| Felicity | 9015863 | CS | CSE30246 | 12028 | F22 | Weninger |
| James | 9018654 | EE | CSE60246 | 12029 | F24 | Weninger |
What happens if James takes another course?
| Student Name | SID | Major | Course | CRN | Term | Instructor |
|---|---|---|---|---|---|---|
| Alice | 9019958 | CS | CSE30246 | 12028 | F24 | Weninger |
| Robert | 9025547 | CPEG | CSE40497 | 27364 | S23 | Weninger |
| Felicity | 9015863 | CS | CSE30246 | 12028 | F22 | Weninger |
| James | 9018654 | EE | CSE60246 | 12029 | F24 | Weninger |
| James | 9018654 | EE | CSE30332 | 11013 | F24 | Morrison |
Notice that James‘s course is duplicated, and now there are inconsistencies like his instructor changing mid-term.
What happens if James switches from EE to CPEG?
| Student Name | SID | Major | Course | CRN | Term | Instructor |
|---|---|---|---|---|---|---|
| Alice | 9019958 | CS | CSE30246 | 12028 | F24 | Weninger |
| Robert | 9025547 | CPEG | CSE40497 | 27364 | S23 | Weninger |
| Felicity | 9015863 | CS | CSE30246 | 12028 | F22 | Weninger |
| James | 9018654 | CPEG | CSE60246 | 12029 | F24 | Weninger |
| James | 9018654 | CPEG | CSE30332 | 11013 | F24 | Morrison |
Now we have to go through all instances of James’ data and manually update them. Imagine the consequences if there were hundreds or thousands of records like this!
Do we update earlier semesters? If not then now apparently EE no longer exists.
What happens if James drops CSE30332?
| Student Name | SID | Major | Course | CRN | Term | Instructor |
|---|---|---|---|---|---|---|
| Alice | 9019958 | CS | CSE30246 | 12028 | F24 | Weninger |
| Robert | 9025547 | CPEG | CSE40497 | 27364 | S23 | Weninger |
| Felicity | 9015863 | CS | CSE30246 | 12028 | F22 | Weninger |
| James | 9018654 | CPEG | CSE60246 | 12029 | F24 | Weninger |
In this case, Prof. Morrison is also removed from the system since the course he was teaching is now deleted.
What Are Data Anomalies?
These issues (duplicate data, unnecessary updates, and deletion of important information) are all examples of data anomalies. There are three types of data anomalies:
- Update Anomalies: These happen when some records are updated and others are not. For example, if James’ major changes from EE to CPEG, but we forget to update it in some instances, this will lead to inconsistency.
- Insertion Anomalies: These occur when we cannot insert data unless other data already exists. For instance, when James enrolled in CSE30332, we had to invent a course and a professor.
- Deletion Anomalies: These occur when deleting one piece of information leads to unintended consequences. For example, when James drops CSE30332, Prof. McMillan is also deleted from the system, even though he is still teaching other courses.
These principles will form the backbone of our discussions throughout this course.
We will explore how database design, normalization, and proper schema management help prevent anomalies and make the data management process more efficient.