Introduction to Databases and SQL
Database files are binary files.
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?
- 1Calculate
While the program runs, the CPU performs the calculations and main memory holds the program's variables and results.
- 2Display
The program can display the income, expenses, and remaining balance, but displaying them does not by itself create persistent storage.
- 3Write
The program must explicitly write the results to a file in secondary memory.
- 4Read 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
Common mistake Assuming that displaying data saves it
What it looks likeA program displays a remaining budget balance and then closes.
Why it failsThe balance may have existed only in the program's CPU and main memory. Displaying it does not explicitly write it to secondary memory.
Do this insteadWrite the result to a file if it must be available during a later program run.
Common mistake Treating a variable as permanent storage
What it looks likeA program stores a list of expenses in a variable and expects the list to be present tomorrow.
Why it failsThe list is part of the running program's data in volatile memory.
Do this insteadStore the information in a file before the program ends, then read the file later.
Common mistake Expecting data in main memory to survive power loss
What it looks likeA user turns off the computer without saving a calculation.
Why it failsMain memory is volatile and its contents are erased when power is lost.
Do this insteadWrite important data to secondary memory.
Common mistake Expecting to read a database file like a text document
What it looks likeA binary database file is opened in a text editor and appears unreadable.
Why it failsDatabase files use a structured binary format intended to be read and written by specialized database software.
Do this insteadUse 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
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
- 1The CPU and main memory support program execution and temporarily hold the program's data.
- 2This working memory is volatile: its contents are erased when power is lost.
- 3Secondary memory, including files and storage devices, retains data without power.
- 4A program must explicitly write data to a file for it to persist across program runs.
- 5Text files contain readable plain text, while binary database files use specialized structures managed by database software.
At a glance
- 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.