From a Spreadsheet to a Database

The spreadsheet works fine for one student to record their own course history. It is a personal record, and the student owns the file. CourseTracker needs more than a shared file. Several students may add history entries at the same time. Administrators must control shared catalog facts. The application must search those facts, reject invalid ones, and keep all of this data after the program or machine restarts.

A database provides persistent storage and controlled ways to retrieve and update that storage. It can coordinate writes from several users and enforce some rules at the point where shared data is stored. Those responsibilities are broader than organizing the data in a spreadsheet. A database provides authorization, concurrent updates, durable writes, and efficient queries.

There are several kinds of databases, and this course will examine some of them. For CourseTracker, the current requirements and the shape of the data give us enough reason to choose a relational database as the starting design.

A relational database stores data in related relations. A relation is just the formal, mathematical name for what we usually call a table. A table consists of rows and columns, and it stores data about an entity, a real-world thing like a student, a course, or a professor. Each row in the table represents one record of that entity, and each column represents a property (attribute) of that entity. For example, a table of students might have columns for student ID, name, and email address. Each row would then represent one student with their specific ID, name, and email.

A relationship is a connection between two tables that allows you to link related data. For example, a course offering might be linked to a specific course and a specific professor. A relationship lets you store a fact once instead of repeating it in many rows. Instead of storing an offering’s room and meeting time in every student’s history entry, you can store them once in the offering table and link to it from the history entries. Now if there is a room change, the administrator can update the offering once, and every student sees the change.

A relational database imposes a defined structure on the data. You decide in advance which tables exist, which columns each table has, and how the tables link to each other. Because the structure is defined in advance, the database can refuse data that does not fit it. It can reject a row that is missing a course number, and it can reject a link that points to a row that is not there. It also provides a powerful query engine that allows you to retrieve and manipulate data in complex ways, including querying several related tables together.

Four things about CourseTracker make a relational database a good fit:

• The same facts are shared by many students. In the spreadsheet, every student who took Data Structures typed the professor, the meeting time, and the room into their own file. In CourseTracker, the offering is stored once and each student’s history entry links to it. When the room changes, an administrator updates one row, and every student sees the new room.
• One course can count toward several degree requirements. Look at Intermediate Programming in the spreadsheet. Its requirement cell reads “CS core and FA2 Computing and Data Science”. That is fine to read, but it is hard to use. To find every course that satisfies FA2, you would have to search inside the text of that column and hope every student typed the requirement the same way. A relational database can hold the course and its requirements as separate linked facts rather than as one line of text. The application can then ask which requirements a course satisfies, and it can ask which courses satisfy a requirement.
• Some entries must be refused. One of the requirements says that CourseTracker must not record a history entry for an offering that does not exist. Because the tables and their links are defined in advance, the database can enforce that rule itself. The application does not have to remember to check it in every place that writes a history entry.
• One screen needs facts from several tables. A course history shows the course, the offering, and the professor together, even though those are stored separately. A relational database can gather related rows in a single query.

A relational database asks for something in return. You have to settle the structure before you store anything, and you have to spread the data across several tables rather than keeping it in one place. For CourseTracker that is an easy trade. The requirements for the first release are already settled, so we know which things we need to store and how they relate. A relational database is a reasonable starting choice for building the application.