Reading 2: The Relational Model

A Conceptual View of the Data

In Reading 1, we established that a database management system (DBMS) sits between users and the actual stored data, translating requests into database operations. One of the key things the DBMS provides is a conceptual view of the data — a mental model that users and application software can work with, without needing to know how the data is physically stored on disk.

The most widely used conceptual view is called the relational model. In the relational model, all data is conceptualized as being organized into rectangular tables. You can think of these tables as similar to the spreadsheet format you already know — rows and columns — but with important differences in vocabulary, precision, and capability.

Most of the databases you interact with in everyday life — your bank, your school's student information system, online shopping sites, healthcare records systems — are relational databases. The relational model has been the dominant approach to database design since the 1970s, and it remains so today.

The Vocabulary: Relations, Tuples, and Attributes

The relational model has its own precise vocabulary. Let's introduce the three most important terms using a familiar example: a table of student records.

Relation

In the relational model, each table is called a relation. The name comes from mathematics — specifically from the concept of a mathematical relation between sets — but for practical purposes, you can think of a relation as a precisely defined table. The table above is a relation. We might call it the STUDENT relation.

Tuple

Each row in a relation is called a tuple (pronounced either "TOO-pul" or "TUP-ul" — both are acceptable). A tuple represents one complete record — all the information about one entity in the relation. In the STUDENT relation, each tuple represents one student. The row for Marcus Hernandez is a tuple.

An important property of relations: no two tuples are identical. Every record must be uniquely identifiable. This is why the Student ID column exists — even if two students happened to share the same name, grade, and homeroom, their IDs would be different, keeping their tuples distinct.

Attribute

Each column in a relation is called an attribute. An attribute describes one characteristic, or property, of the entity represented by each tuple. In the STUDENT relation, the attributes are Student ID, Last Name, First Name, Grade Level, and Homeroom Teacher. Every tuple in the relation has a value for each attribute (or a designated marker indicating the value is missing or unknown).

Schemas and Subschemas: Who Sees What

A database typically contains many relations, not just one. A school's student information system might have separate relations for students, staff, courses, enrollments, grades, attendance, and bus routes, all linked together. The schema is a complete description of all of these relations and how they are connected — essentially, the blueprint of the entire database.

The schema is used internally by the DBMS to maintain and navigate the database. Individual users, however, rarely need — or should have — access to the entire schema. This is where subschemas come in.

A subschema is a description of only that portion of the database relevant to a particular user's role. Consider how different roles in a school need different views of the same underlying data:

Each of these users works with a subschema — a restricted view of the database tailored to their role. The DBMS enforces these restrictions automatically. A teacher using the gradebook application cannot accidentally (or deliberately) access the payroll records, because the teacher's subschema does not include them. The data is all in the same database; access is controlled by what each user's subschema permits.

It is worth noting that subschemas, while powerful, are not a perfect security solution. Real-world databases are complex, and gaps in subschema design, errors in configuration, or vulnerabilities in the application software sitting on top of the DBMS can all create opportunities for unauthorized access. Database security is an ongoing concern, not a one-time solved problem.

Data Independence: Change Without Breaking Things

One of the most practically valuable properties of the relational model is something called data independence — the ability to change the structure or organization of the database without requiring changes to every application that uses it.

Here is why this matters. Imagine a school district's student information system is used by a dozen different applications: the gradebook, the attendance tracker, the state reporting tool, the parent communication portal, the transportation scheduler, and so on. The personnel office decides they need to add a new field to each student's record — say, an indicator of whether the student has opted into a new district wellness program.

In an old file-based system, adding that field might require modifying every application that reads student records — all twelve of them — because each application was written to expect a specific file structure. One personnel department's change ripples into a district-wide software update project.

In a relational database system, the change is made to the schema and to the subschemas of users who need to see the new field. Applications that do not need the new field continue working with their existing subschemas, unchanged. The DBMS absorbs the structural change; the applications on top of it are insulated from it.

This property — that changes to the underlying data structure do not cascade into changes throughout every application built on top of it — is one of the key reasons relational databases became and remain dominant.

Now You Try

Use the following relation to answer the questions below. A suggested response is in the answer key.

1. How many tuples are shown in this relation (not counting the continuation row)?
2. How many attributes does this relation have? Name them.
3. Notice that teacher T-108 appears in two tuples. What does that tell you about the real world this relation is modeling?
4. A new district policy requires tracking whether each course is offered in-person, online, or hybrid. Describe what change would need to be made to this relation to accommodate that information.
5. A guidance counselor needs to see course names, departments, and credits to advise students on scheduling. A department chair needs to see all attributes including teacher assignments. Describe the difference between their subschemas for this relation.