The Database Terms of Reference
When we communicate about a particular subject area it is very important we use the common terms of reference. Relational database design is no different with its own set of unique terms, and in order to express or define our ideas and concepts we need to know the language to use. With Relational Databases this set of terms goes further being used to express and define the database design process itself and is used throughout the industry from publications and education to conversations between database professionals. In this section we are going to look at some of these terms and where they are used.
The process of creating a database can be broadly divided into two main stages:
- Data analysis, using a formalised methodology to create a database design. Two widely used methods are Entity Relationship Modelling (ER) and Normalisation.
- Physical implementation of that design in a database system. There are many examples of Relational databases including MySQL, Oracle, SQLServer to mention but a few.
As you move from a database design to a physical implementation, different terminology is used. It is important to understand these differences and ensure the correct terms are used for the appropriate Methodology or stage you are discussing or presenting.
The following table identifies each of the different disciplines and their equivalent terms in relation to the other disciplines.
It is a common misunderstanding that an Entity is like a Relation or that a Relation is a table. This is not true as they stem from very different disciplines within the Relational Database model and as such represent different descriptive types specific to that discipline. In saying this, the rational of each being representative of the other within the different disciplines can hold true. Therefore an Entity can be compared to a Relation in terms of design and used as validation. Likewise an Entity or Relation can become a table during the transition of implementation. This may seem somewhat pedantic but clarity of definition and scope will help to ensure you can correctly communicate your needs or requests to yourself and those around you. Poor use of terms can lead to confusion, misrepresentation or poor implementation.
Let us now examine each of the terms in the above table in more detail
Entity Relationship Modelling Terms
If we consider a college as an example of something we might be modelling, a department, student or module might be examples of a uniquely identifiable object of important. It is worth noting that by convention we tend to name entities in the singular as in student rather than students.
Continuing with this example, within a Student entity we would have attributes such as studentId and studentName and an entity occurrence would be a single instance of these attributes.
If we take a hotel as an example we might have Customer or Booking as examples of Relations with examples of Domains within a Booking being bookingRef, bookingDate and roomNo
Relational Database Terms
This is the implementation of the design and an example can be seen in the following SQL statement