Skip to main content

Multiple Tables

Up to now you have being selecting and manipulating data from one table at a time.

However, as discussed in Section 1, a relational database has its data spread over several tables. As multiple tables are fundamental to relational databases we will revisit, and expand upon, that discussion.

In the student database you have been using seven tables, i.e.:

student, staff, department, course, unit, result, attendance

These tables can be linked together and, within one SQL SELECT command, you can select data from several tables. This is achieved by a further use of the WHERE clause of the SQL SELECT command.

The reason why seven tables were used for this database is outside the scope of this unit. Database design is covered in other HN units, e.g. D77C 35 Systems Development: Relational Database Systems. You may be following this unit later in your studies. For now, it is sufficient to know that a relational database contains many tables that can be linked together.

How those tables are linked is described in this section.

Let us look at how the above tables relate to each other.

Looking at staff and department, the relationship is that a member of staff belongs to a department. Furthermore, a department has many members of staff.

Relationship diagram between staff and department (Many to one)

This link is established by the dept_number column in the staff table. Remember from Section 1 that the linking column goes in the many side of the relationship.

Similarly, there is a relationship between student and department. A student is registered in a department. A department has many students enrolled.

Relationship diagram between student and department (Many to one)

This link is established by the dept_number column in the student table.

Next: Relationships