Introduction to a relational database (SQL)
A relational database is a collection of data elements with predefined relations between them. These elements are represented in the database as tables with columns and rows. These tables are used to contain information related to the objects represented in the database, where each column (or attribute) represents specific stored information while the rows contain an entity or object data collection also called “record”. Each entity contains an index called Primary Key (PK) which uniquely identifies each table row and it might be associated with a Foreign Key (FK), which is a copy of PK that exists in another entity with the purpose of forming a relationship between the entities involved. It is important to note that while a PK value cannot be duplicated, an FK value can appear multiple times in the same table as a specific PK (which identifies a unique row) might refer to a specific FK which identify one or multiple rows. Moreover, it is worth to note that in the Fig. 1 below (which represents a physical database construct) different terminologies have been used for the same thing as “Tables/Entities”, “Columns/Attributes” or “Rows/Records”. Even though they are often used interchangeably as they refer to the same things, they actually have different reasons to exist. Without going much in detail, the main difference is that Tables, Columns, and Rows refer to physical database construct while Entities, Attributes, and Records refer to Logical constructs.
The logical way to represent a database objects is given by the figure below (Fig. 2), which, if on the one hand, it doesn’t show any information about the real data values (as for Fig. 1) on the other hand, it gives a much better understanding of how the entities are built and what are the relationships between them. In fact, Fig. 2 provides a more clean and readable representation of database objects which includes crucial information as Entity Types, the number of characters allowed, Primary Key, Foreign Key, and Entity relationships. This way of represents database objects is also used in ERD (Entity Relationships Diagram) which includes all database entities with the aim to have a higher level of understanding of the organization’s data structure.
Essentially there are 3 types of relationships between entities which are:
1. One-to-one (1:1)
2. One-to-many (1:n)
3. Many-to-many (n:m)
A good example of “one-to-one” relationship is given by the example below where the two entities are linked to each other by the same Primary Key (StudentID); this means that for each record stored in “Students” entity correspond just one record in “Contact Info” entity and vice versa.
A typical example of a “one-to-many” relationship instead, is the relationship shown in the previous image (Fig.2) between “Courses” and “Students” entities. For each course indeed (es. BSc in Business) there are multiple students enrolled while it’s not true the opposite as a student can only be enrolled in a single course (and so not to multiple courses).
Finally, the last type of relationship called “many-to-many” relationship, occurs when multiple records in an entity are associated with multiple records in another entity. A typical real-life scenario could be for example a gym’s database where each member might attend multiple classes and each class can be attended by multiple members. Relational database systems usually do not allow you to implement a direct many-to-many relationship between two entities; that’s why a third entity is needed to solve this problem. Using a third entity also called “join table”, is possible to breakdown the “many-to-many” relationship into two “one-to-many” relationships. In the example below, the join table called “Enrollments” acts indeed as a junction between Students and Classes as it is able to connect multiple students with multiple classes by connecting the FKs MemberID and ClassesID.
It is also interesting to note that join tables typically hold attributes that might not make sense to have in any other table such as a Date and Cost attributes for instance, in order to keep track of when someone enrolled in a class and how much he paid for it.
Thanks for being a part of our community!
If you found this article helpful and would like to show your support, don’t hesitate to:
- Clap on this story
- Leave a comment below telling me what you think. This will help me with the next articles
- Highlight the parts in this story you were most interested on
- Support my work on Buy Me a Coffee ☕️
These actions really really help me out, and are much appreciated!
