Introduction to Databases and SQL
Main memory (CPU and RAM) is where programs execute and store data while running, but it is volatile—everything is erased when power is lost.
The Vanishing Budget
Imagine a program that calculates a monthly budget. While it runs, you can see your income, expenses, and remaining balance on the screen. The program stores the calculations in variables and displays the results. Then you close the program. What happens to the data?
During execution, the program's calculations, variables, lists, and other data live in the computer's active workspace: the CPU and main memory. When the program stops, that active workspace is no longer enough to preserve the data. If the data was not written to a file, the program has no saved record to use the next time it runs.
Volatile Working Space
Main memory, described here as the CPU and RAM, is where programs execute and store data while they are running. It is volatile, which means that its contents are erased when power is lost.
The CPU performs the active work of a program. It evaluates conditions, calls functions, and carries out loop iterations. Main memory holds the data structures that the running program creates. Together, they support the temporary work of software while it executes.
Volatile memory requires electrical power. As long as the program is running and the computer is powered, its variables and intermediate results are available to the program. When power is cut, the contents of the CPU and main memory are erased. A calculation that existed only there cannot be recovered by simply restarting the program.
Files as Persistent Storage
A file is data stored in secondary memory. Secondary memory includes hard drives, solid-state drives, USB flash drives, and other storage devices. Unlike volatile memory, secondary memory retains written data even when power is off.
A program must explicitly write data to a file if it wants that data to persist beyond the program's execution. Writing transfers the program's work from the temporary workspace of main memory into secondary storage. On a later run, the program can read the file and recover the previously stored information.
A useful mental model is a whiteboard and a notebook. Main memory is like a whiteboard: it is where you actively work through a problem. A file is like a notebook placed on a shelf: it preserves the written result when you stop working. The analogy highlights the difference between an active workspace and storage that lasts beyond the current session.
Saving a Monthly Budget
A budget program calculates income, expenses, and a remaining balance. How can the result be available after the program is closed?
Calculate: While the program runs, the CPU performs the calculations and main memory holds the program's variables and results.
Display: The program can display the income, expenses, and remaining balance, but displaying them does not by itself create persistent storage.
Write: The program must explicitly write the results to a file in secondary memory.
Read later: During a later run, the program can read the file and use the saved budget information.
The budget survives the end of the first program run because the program wrote it to a file in secondary memory.
Text and Database Files
Files can use different representations. Text files contain readable plain text made from letters, numbers, and punctuation. They can be opened in a text editor, where the actual content is visible. Examples include CSV files containing budget data, log files recording program activity, and configuration files containing settings.
Database files are binary files. They are structured for database software rather than for direct reading in a text editor. A database file can contain records organized in tables, relationships between records, indexes for searching, and other internal structures. Database software acts as the intermediary that reads and writes this specialized format.
| Feature | Text file | Binary database file |
|---|---|---|
| Representation | Readable plain text | Binary structured format |
| Direct reading | Can be read in a text editor | Not intended to be read in a text editor |
| Typical organization | Letters, numbers, and punctuation | Records, tables, relationships, indexes, and other internal structures |
| Main reader and writer | Text editors and programs | Specialized database software |
The two file categories serve different storage and access purposes.
Mistakes About Persistence
Assuming that displaying data saves it
The balance may have existed only in the program's CPU and main memory. Displaying it does not explicitly write it to secondary memory.
Fix:
Write the result to a file if it must be available during a later program run.Treating a variable as permanent storage
The list is part of the running program's data in volatile memory.
Fix:
Store the information in a file before the program ends, then read the file later.Expecting data in main memory to survive power loss
Main memory is volatile and its contents are erased when power is lost.
Fix:
Write important data to secondary memory.Expecting to read a database file like a text document
Database files use a structured binary format intended to be read and written by specialized database software.
Fix:
Use the appropriate database software as the intermediary.
Check Your Mental Model
A program calculates a game score and keeps the score only in its running data. The player closes the program, and the computer loses power. On the next run, the program has no saved score. Identify where the score existed first, explain why it disappeared, and describe what the program would have needed to do to preserve it.
Hints
- Identify the temporary workspace used while the program was running.
- Ask what happens to volatile memory when power is lost.
- Persistent data must be explicitly written to a file in secondary memory.
What do you think happens?
A program calculates a result, displays it, and closes without writing a file. What should you predict about the result during the next run?
Reveal answer
Answer: The result is unavailable unless it was saved elsewhere.
The result existed in the program's active workspace. To persist beyond execution, the program had to explicitly write it to a file in secondary memory.
Key Takeaways
- The CPU and main memory support program execution and temporarily hold the program's data.
- This working memory is volatile: its contents are erased when power is lost.
- Secondary memory, including files and storage devices, retains data without power.
- A program must explicitly write data to a file for it to persist across program runs.
- Text files contain readable plain text, while binary database files use specialized structures managed by database software.
Key Takeaways
- Running programs use the CPU and main memory as a fast but temporary workspace.
- Power loss erases data that remains only in volatile memory.
- Files connect a running program with persistent secondary memory.
- Text files are readable directly, while binary database files are structured for specialized database software.