Informatics Practices · Ch 6 — Database Concepts
Relational Data Model
Relational Data Model
Every DBMS is built on some underlying data model — a description of how the database is structured: how data are defined and represented, what relationships exist among the data, and what constraints apply. DBMSs are in fact classified by their data model. The most commonly used one is the Relational Data Model, and it is the model this course is built around. Other data models include the object-oriented data model, the entity-relationship data model, the document model and the hierarchical data model.
Tables become relations
In the relational model, tables are called relations. A relation stores data under different columns, and every column name within a table must be unique. Each row of the table represents one related set of values — for instance, each row of the GUARDIAN table (Table 7.5) describes one particular guardian, tying together that guardian's ID, name, address and phone number. In this sense a table is a collection of relationships: each row relates a set of values to one another.
Crucially, relations in a database are not independent tables — they are associated with each other:
- The ATTENDANCE relation carries the attribute RollNumber, which links each attendance record to the corresponding student record in STUDENT.
- The attribute GUID is placed in the STUDENT table so that the guardian details of a particular student can be extracted from GUARDIAN.
If these linking attributes were missing from the appropriate relations, it would be impossible to keep the database in a correct state or to retrieve valid information from it. Figure 7.3 shows the relational database StudentAttendance with its three relations (tables): STUDENT, ATTENDANCE and GUARDIAN.
A database modelled on the relational data model concept is called a Relational Database.
The relation schemas of StudentAttendance
Table 7.7 lists the relation schemas of the StudentAttendance database and describes their attributes:
| Relation scheme | Description of attributes |
|---|---|
| STUDENT(RollNumber, SName, SDateofBirth, GUID) | RollNumber: unique id of the student; SName: name of the student; SDateofBirth: date of birth of the student; GUID: unique id of the guardian of the student |
| ATTENDANCE(AttendanceDate, RollNumber, AttendanceStatus) | AttendanceDate: date on which attendance is taken; RollNumber: roll number of the student; AttendanceStatus: whether present (P) or absent (A). The combination of AttendanceDate and RollNumber is unique in each record of the table |
| GUARDIAN(GUID, GName, GPhone, GAddress) | GUID: unique id of the guardian; GName: name of the guardian; GPhone: contact number of the guardian; GAddress: contact address of the guardian |
Each tuple (row) in a relation corresponds to the data of a real-world entity — a Student, a Guardian, an Attendance record. In the GUARDIAN relation, each row states the facts about one guardian, and each column name tells us how to interpret the data stored under it.
The vocabulary of the relational model
Figure 7.4 shows the GUARDIAN relation populated with data (its relation state), and it is the running example for the five standard terms:
i) Attribute — a characteristic or parameter for which data are stored in a relation. Plainly put, the columns of a relation are its attributes, also referred to as fields. GUID, GName, GPhone and GAddress are the attributes of GUARDIAN.
ii) Tuple — each row of data in a relation (table). In a table with n columns, a tuple is a relationship between the n related values in that row. Tuple, row and record all name the same thing.
iii) Domain — the set of values from which an attribute can take its value in each row. Usually a data type is used to specify the domain of an attribute. In the STUDENT relation, RollNumber takes integer values, so its domain is a set of integers; the set of character strings forms the domain of SName. …
The figure presents the StudentAttendance database as it looks under the relational data model: three table boxes, each with a title bar, listing their columns. The GUARDIAN box carries GUID, GName, GPhone and GAddress; the STUDENT box carries RollNumber, SName, SDateofBirth and GUID; the ATTENDANCE box carries AttendanceDate, RollNumber and AttendanceStatus. What is new here is the plain connector lines drawn between the boxes: one line joins GUARDIAN to STUDENT, and another joins STUDENT to ATTENDANCE.
Those two lines are the whole point. In the relational model, tables — called relations — are not independent islands of data; they are associated with one another through shared attributes. The GUARDIAN–STUDENT line exists because STUDENT carries the attribute GUID, which lets the database pull up the guardian details of any particular student. The STUDENT–ATTENDANCE line exists because ATTENDANCE carries RollNumber, which ties every attendance entry back to the corresponding student record. If such linking attributes were missing from the appropriate relations, the database could not be kept in a correct state and valid information could not be retrieved from it. …
The figure takes one concrete relation — GUARDIAN, with its columns GUID, GName, GPhone and GAddress and its five rows of guardian data — and annotates it so that every core term of the relational data model can be seen on a real table rather than defined in the abstract.
Three callouts do the teaching. An arrow labelled "Relation GUARDIAN with 4 attribute/columns" points at the header row, which is ringed by an oval: the column headings are the attributes of the relation, and counting them gives four. A second oval is drawn around the last row of data, labelled "Record/tuple/row": each horizontal line of values is one tuple, describing one guardian. Along the right side, a brace labelled "Relation State" spans all five data rows — the collection of tuples present in the table at this moment is the state of the relation, which changes as rows are inserted, updated or deleted. …
| Relation Scheme | Description of attributes |
|---|---|
| STUDENT(RollNumber, SName, SDateofBirth, GUID) | RollNumber: unique id of the student SName: name of the student SDateofBirth: date of birth of the student GUID: unique id of the guardian of the student |
| ATTENDANCE (AttendanceDate, RollNumber, AttendanceStatus) | AttendanceDate: date on which attendance is taken RollNumber: roll number of the student AttendanceStatus: whether present (P) or absent(A) Note that combination of AttendanceDate and RollNumber will be unique in each record of the table |