Understanding relational databases
Large enterprises must find a structured and reliable way to handle vast amounts of data. By offering a framework for managing information via tables and establishing clear relationships between data points, relational databases play a central role in keeping your data organized for easy access and analysis. They also facilitate data storage, retrieval, and update requirements.
This post will discuss relational databases, their components, how they work, and the characteristics that make them so powerful. We will also explore the concept of entity relationship modeling.
What are relational databases?
A relational database is built to systematically store and organize data so that companies can access it easily. It does this by presenting data in tables made up of rows and columns:
- Rows represent individual records or entries, such as a specific customer or transaction.
- Columns represent different attributes (fields) of those records, such as a customer’s name, email, or the date of a purchase.
Fig.1: Pictorial examples of rows and columns in a table
Each table in a relational database focuses on a specific subject. For example, in a customer relationship management (CRM) system, you might find one table for customer details, another for sales transactions, and a third for support tickets. Each table is linked to others through relationships, allowing you to connect relevant data across tables.
The relational aspect of these databases comes from how tables are connected using common fields, such as a Customer ID in both the customer and sales tables.
Use of primary and foreign keys in relational databases
A main attribute of a relational database is its use of primary and foreign keys. These enforce the data's integrity and structure, as well as determine any relationships between tables.
Primary keys
Each table typically has one or more columns that serve as a unique ID for each record; this is called the primary key. For instance, a customer table might use a “Customer ID” as the primary key to distinguish one customer from another.
Foreign keys
Foreign keys allow data to be shared between tables to eliminate redundancy, with a given field (or fields) in one table referencing the primary key in a second table. For instance, a Customer ID in the sales table might link to the Customer ID in the customer table.
Fig. 2: Example of a primary and a foreign key
The image below shows tables for Student Details (student information) and Student Marks (grades). The primary key (ID) identifies each student, while the foreign key (ID) links grade entries to valid student IDs in the Student Details table.
Fig. 3: Illustration of how a primary key works with a foreign key
Applications of relational databases
Relational databases are crucial in numerous industries. Their ability to manage large data sets and ensure data integrity makes them a perfect fit for enterprise demands.
Use cases for relational databases per industry
| Sector | Use cases | Benefits |
|---|---|---|
| Finance | Financial institutions handle customer account monitoring, transactions, and reporting. | Ensure data consistency across different departments and allow for secure, accurate financial reporting |
| Healthcare | Healthcare providers manage patient medical data, appointments, etc. | Facilitate compliance with privacy regulations and ensure secure management of sensitive data |
| E-commerce | Businesses manage their inventories, customer orders, and transactions. | Ensure seamless operations, from order processing to personalized recommendations |
| Retail and Supply Chain | Retailers track inventory, supplier data, and sales transactions. | Track inventory, oversee supplier relations, and analyze purchasing trends |
| CRM Tools | Software like Zoho CRM stores customer information, sales, and support tickets. | Maintain up-to-date, comprehensive customer profiles for better customer relationship management |
Why are relational databases popular?
The widespread use of relational databases boils down to four factors:
- Reliability: Relational databases have been around for a long time and are well understood; they also enjoy a rich ecosystem of tools, expertise, and best practices, making them the go-to choice for most enterprises.
- Accuracy: Their built-in capabilities guarantee data consistency; by enforcing rules like primary and foreign keys, they minimize errors and maintain high data quality.
- Powerful: To query and analyze data, relational databases use SQL, a powerful language that allows users to easily filter, sort, and join data from multiple tables; this makes relational databases ideal for reporting and decision-making.
- Scalable: Modern relational databases can scale to handle massive amounts of information.
Understanding entities and relationships
An entity is data regarding any object or concept stored in a relational database. Each entity has distinct characteristics, often referred to as attributes (e.g., name, address, or age), that describe it.
Properly defined entities in a relational database can be tangible (e.g., person, car, or bank) or intangible (e.g., order, process, or schedule).
However, entities require a primary key to ensure they are distinguishable from one another. While different entities can share similar attributes (such as name), the primary key guarantees that each record is a unique entity within the database.
Relationships between entities
Understanding how entities in a database relate to one another is crucial for effective data management. This connection is known as a relationship. It establishes how information in one table is related to data in another so that data can be observed and connected between tables.
Three primary relationships hold tables together:
- One-to-one (1:1) relationship: A single entity is related to just one other entity, and vice versa; e.g., a person has just one social security number, and that number is linked only to that one person.
- One-to-many (1:M) relationship: One record is associated with many others, all of which are only associated with that one record; e.g., an organization employs multiple individuals, but those individuals are employed only by that one organization.
- Many-to-many relationships (M:M): Numerous entities are related to several other entities; e.g., several siblings have numerous nieces and nephews, who all have several uncles and aunts.
Cardinality and modality
Cardinality and modality are two basic concepts in relational database systems that define the relationships between tables and attributes. Although not the same as relationships between entities, cardinality and modality are closely related, as they are used to define and describe these relationships.
Cardinality
Cardinality indicates how many times one entity’s instance can be linked to a single instance of another entity in a relationship, i.e., the number of rows in a table associated with a single row in another table.
Examples of cardinality:
- One-to-one: A person has one passport.
- One-to-many: A customer can place many orders.
- Many-to-many: A movie has numerous actors, each of whom can be seen in multiple other movies.
Modality
Modality refers to the mandatory or optional nature of a relationship between entities in a database. It defines whether or not an entity must have a relationship with another entity.
Mandatory relationship
A mandatory relationship exists when both entities are obligated to take part in said relationship. For instance, in a "Student" and "Course" relationship, a student may be required to enroll in at least one course. With this relationship, the foreign key in the dependent table is not null.
Optional relationship
An optional relationship occurs when an entity can choose whether or not to take part in the relationship. For example, a "Customer" field and an "Order" field relationship are optional, as a customer may not have placed any orders yet. Here, the foreign key in the dependent table can be null.
Entity relationship modeling
An entity relationship model (ERM) outlines the structure of a relational database. It involves identifying entities (tables), attributes (fields), and the relationships between them.
Key components of an ERM include:
- Entities (tables): Objects or concepts identified in the database, e.g., customers or transactions
- Attributes (fields): Features or characteristics of an entity, e.g., customer name, order date, and product price
- Relationships: Connections between entities, e.g., one client can have many orders, or one order is associated with one client
An entity relationship (ER) diagram serves as a blueprint for the physical database design, helping developers visualize how data will be stored and accessed to avoid issues such as redundancy or inconsistency.
An ER diagram represents all entities, attributes, and relationships in a database via the following components:
- Rectangles represent entities
- Ovals represent attributes
- Diamonds represent relationships
- Lines link each entity to its attributes and relationships
A diagram for an entity relationship model example is shown in figure 4 below:
Fig. 4: ER diagram illustrating three entities, Professor, Student, and Course (Source: ResearchGate)
Steps in creating an ER model
Entity relationship modeling entails the following steps:
- Identify entities: Identify all primary objects in your database.
- Define attributes: Identify and create a list of each entity’s characteristics.
- Establish relationships: Identify how entities interact and define the nature of these relationships.
- Create the ER diagram: Develop a visual representation using the ER symbols to depict entities, attributes, and relationships.
- Review and refine: Work with stakeholders to verify that the model accurately meets the requirements of the application.
Conclusion
Relational databases are powerful tools for managing structured data efficiently. By understanding the core concepts of entities, relationships, and ER modeling, businesses can create robust and scalable databases.
Site24x7’s comprehensive capabilities for database monitoring help you track key metrics, optimize queries, and ensure your database environment runs smoothly, preventing downtime and performance bottlenecks.
In Part 2 of this series, we will dive deeper into the relational database model, explore relational database management systems, and compare relational and non-relational data models.