Data and Databases

Exploring Database Structure

---

Before writing queries, it is useful to practice reading a schema. A schema is a description of what tables exist, what columns they contain, and how they are connected. The schema is the structure itself; a table list or a diagram is just one way to describe that structure.

Reading a Schema Like a Map

A schema can be read a little like a map.

• Tables tell us what kinds of things are stored
• Columns tell us what facts are stored about those things
• Keys tell us how records can be identified and connected

Suppose we see these tables:

• users(id, name, email)
• courses(id, code, title)
• exercises(id, course_id, title, deadline_at, max_points)
• submissions(id, exercise_id, user_id, submitted_at, score, status)

The list above is already part of the schema. The same schema can also be drawn as a diagram:

Figure 1 — One way to draw the schema as a diagram is to show the tables together with the key-based paths that connect them.

The table list and the diagram describe the same schema, but they are not a one-to-one mapping of identical information. The table list is often easier for reading the columns of one table carefully, while the diagram makes key markings, data types, relationship paths, and table connections easier to notice quickly.

For instance, PK stands for primary key, and FK stands for foreign key.

Even without seeing any data, we can already infer a lot.

• One course can contain multiple exercises
• One user can make multiple submissions
• Submissions store both timestamps and grading information

Loading Exercise...

Column Names and Data Types as Clues

When reading a schema, column names are only one source of meaning. Data types also tell us what kind of values the database expects and what kinds of later operations may make sense.

Common examples include:

• Integers
• Text
• Dates
• Timestamps
• Booleans

Choosing a sensible type is part of good design, but it is also part of reading structure well. For example, if a deadline column uses a timestamp type, that strongly suggests the system will later compare times rather than just display free-form text.

Here is a small example of how columns, types, and meanings fit together.

Table	Column	Example Type	Meaning
users	email	text	login or contact email
courses	code	text	course code
exercises	deadline_at	timestamp	submission deadline
submissions	score	integer	awarded points

Loading Exercise...

What Questions Does the Schema Support?

From this small schema, we can already imagine questions the current structure supports, such as:

• Which exercises belong to CS-A1150?
• Which users have submitted Exercise 3?
• Which submissions still have no score?
• What is the average score for one exercise?

We can also notice questions the schema does not yet answer clearly. A later version of the system might add an enrollments table to record which students participate in which course. Without such a table, this schema cannot clearly tell us which students were enrolled but never submitted anything.

Reading structure this way is an important skill, because later you often need to understand a database before you can query it well.

Loading Exercise...

Question Design Practice

Before learning query syntax, it is useful to practice one smaller step on its own: turning a schema into one concrete question that the schema could answer.

The exercise below asks you to do exactly that. The goal is not to write SQL yet, but to show that you can read the schema and propose one realistic, queryable question from it.

Loading Exercise...

Looking for Cardinality Clues

Cardinality means how many rows on one side of a relationship can connect to rows on the other side.

One useful way to start is to look at the diagram shape first.

Figure 2 — One course can connect to many exercises, while each exercise belongs to one course.

That shape suggests a one-to-many relationship — there’s a single line going to courses and a crow’s foot going to exercises. We can then connect the shape back to keys: if exercises.course_id points to courses.id, each exercise row belongs to one course, while many exercise rows may refer to the same course row.

Later in the course, many-to-many relationships will need their own linking tables. In a diagram, that often looks like two one-to-many connections through a middle table:

Figure 3 — A many-to-many idea is often implemented through a linking table such as enrollments.

Here too, keys explain the structure. In a simple version, enrollments.user_id could point to users.id and enrollments.course_id could point to courses.id. Then one enrollment row links one user to one course, and many enrollment rows together let one user connect to many courses and one course connect to many users.

Later, the schema might become more detailed by adding a course_instances table between courses and enrollments. In that case, enrollments.course_instance_id would point to course_instances.id, and the same linking-table idea would still apply to a more specific relationship.

At this stage, it is enough to start noticing both directions: the diagram shape gives a quick clue, and the foreign keys explain why that shape exists.

Loading Exercise...

Check Your Understanding

1. If a schema contains courses, exercises, users, and submissions, what kinds of questions might the system be able to answer?
2. What can you infer from a foreign key even before reading any application code?
3. Why is it useful to understand a schema before trying to retrieve information from it?

AI Study Aid

Create a chapter diagram

We're looking into to what extent AI-generated diagrams could help with studying.

Use this study aid to generate an AI-generated visual summary of the material you just studied. Each diagram style emphasizes the content in a different way, so you can choose the focus that feels most useful.

Using the diagram generator is voluntary and does not affect your course progress. We encourage you to try it out and see if it helps, but it's totally up to you! Your answers help us understand how to make better study aids in the future.

Diagrams tried: 0

Generate diagram

Selected style: choose a diagram style