File System to DBMS

A file system stores each department's data in its own separate file; a database stores all of it in one place as a set of related tables. The move from the first to the second is not just copying data across — the files have to be redesigned so that every fact is stored exactly once and tables can be connected through common columns. This section walks through that redesign using the school example, where the office maintained a STUDENT file (Table 7.1) and the class teacher maintained a separate ATTENDANCE file (Table 7.2).

How tables in a database connect

Tables in a database are linked (related) through one or more common columns or fields. In the school example, the STUDENT file and the ATTENDANCE file share two field names: RollNumber and SName. This overlap is the raw material for linking — but as it stands, it also duplicates data. Converting the two files into a proper database therefore needs three deliberate changes.

1. Remove SName from ATTENDANCE. Since the student's name already lives in STUDENT, there is no need to repeat it in ATTENDANCE. Whenever the teacher needs a student's details, they can be retrieved through the common field RollNumber, which appears in both files. One fact, stored once.
2. Split STUDENT into STUDENT and GUARDIAN. If two siblings study in the same class, the same guardian details (GName, GPhone and GAddress) get recorded twice — once with each sibling. That is redundancy, exactly the kind a database is meant to eliminate. The fix is to pull the guardian columns out into a separate GUARDIAN file, so each guardian's data is maintained only once, no matter how many children they have in the school.
3. Add a unique GUID column. Splitting creates a new problem: two or more guardians can have the same name, so a name alone cannot tell us which guardian belongs to which student. The solution is an extra column, GUID (Guardian ID), which takes a unique value for each record in the GUARDIAN file. The same GUID column is also kept in the STUDENT file, and this shared column is what relates the two files.
   Note

   Guardians could also be told apart by their phone numbers — but a phone number can change, so it may not truly distinguish a guardian over time. A deliberately created ID that never changes is the safer identifier. This is a general principle when choosing identifying columns.

The resulting structure

Figure 7.1 shows the record structure of the three files in the STUDENTATTENDANCE database:

This diagram is not the complete database schema, because it shows only the record structures — it does not yet show the relationships among the tables.

The structures in Figure 7.1 are empty shells; they get populated with actual data as in Tables 7.4, 7.5 and 7.6. A snapshot of the STUDENT table:

A snapshot of the GUARDIAN table:

And the ATTENDANCE table records one row per student per date, with status P (present) or A (absent) — for example, on 2018-09-01 roll numbers 1, 2, 4 and 6 were present while 3 and 5 were absent; on 2018-09-02 roll numbers 1, 2, 5 and 6 were present while 3 and 4 were absent.

Notice how the split pays off: each guardian's name, phone and address appear exactly once in GUARDIAN, and STUDENT simply points to the right guardian through GUID.

One central repository, many users

Figure 7.2 shows the simplified STUDENTATTENDANCE database maintaining data about students, guardians and attendance. The DBMS keeps a single repository of data at a centralized location, and that one repository can be used by multiple users at the same time — the office staff and the teacher no longer maintain private copies; both work on the same data.

How users interact with a DBMS …