Databases: A Beginner’s Guide

Databases are the backbone of storing, organising, and retrieving data efficiently.

They act as digital warehouses, housing vast amounts of information that power everything from websites to mobile apps.

There are three main different types of databases:

• Relational Databases: Organise data into tables with rows and columns, and establish relationships between tables.
• NoSQL Databases: Designed for unstructured or semi-structured data and offer flexible schema designs.
• NewSQL Databases: Combine features of relational and NoSQL databases, aiming to provide scalability and flexibility without sacrificing ACID compliance.

In this article, we’ll be primarily focusing on relational databases, and the critical role that they play in Application Programming Interfaces (APIs).

Why? Because understanding the fundamentals of relational databases lays a solid foundation for grasping more complex database concepts.

We’ll take a bottom-up approach, starting from the basics and gradually building up to a working example that will incorporate and provide context to all of the concepts we discuss.

Why not take a top-down approach? Well, diving straight into looking at the bigger picture of databases can be quite overwhelming.

Let’s start by discussing the language used to communicate with a database: SQL.

Structured Query Language (SQL)

Structured Query Language (SQL) acts as the universal language for managing and interacting with relational databases, simplifying data manipulation tasks for users.

You can enter this code directly on phpMyAdmin, a popular web-based interface for managing MySQL databases, to interact with your database in real time.

This is a simple way of adding data to your database with an intuitive interface.

Alternatively, you can incorporate SQL queries directly within your application’s codebase, seamlessly integrating database interactions into your application’s logic as and when needed.

This flexibility allows you to dynamically retrieve, modify, and manipulate data within your applications, enabling efficient and responsive data-driven functionality.

Let’s go through some examples of some SQL commands in the context of a library management system:

1. SELECT: To retrieve information from the library database, such as fetching all books authored by a specific author: SELECT * FROM Books WHERE Author = ‘J.K. Rowling’;
2. INSERT: Adding new records to the database, like adding a new book entry: INSERT INTO Books (Title, Author, ISBN) VALUES (‘Harry Potter and the Philosopher’s Stone’, ‘J.K. Rowling’, ‘9780590353403’);
3. UPDATE: Modifying existing data, for instance, updating the availability status of a book: UPDATE Books SET Available = ‘No’ WHERE Title = ‘Harry Potter and the Philosopher’s Stone’;
4. DELETE: Removing outdated or unnecessary records from the database, such as deleting a withdrawn book: DELETE FROM Books WHERE ISBN = ‘9780590353403’;

Databases in APIs

APIs enable interaction with a database within an application by providing a set of endpoints that expose database functionalities.

Through these endpoints, developers can send requests to perform operations such as querying, inserting, updating, or deleting data.

The API processes these requests and translates them into SQL queries to interact with the underlying database.

This allows developers to access and manipulate data stored in the database without needing to directly manage the database infrastructure.

As an API provider, offering database interaction capabilities involves exposing endpoints that encapsulate SQL operations.

Developers can send requests to these endpoints, specifying the desired SQL queries or actions to be executed on the database.

The API provider’s infrastructure processes these requests, executes the SQL queries on the database, and returns the results to the developers’ applications.

Basic Database Concepts

Understanding fundamental concepts like entities, attributes, relationships, cardinality, and modality is essential for effective data modelling and database design.

These concepts are also crucial for designing APIs and performing data transformations effectively, as we discussed in one of our previous articles.

Entities refer to distinct objects or concepts within a database, such as “Customer” or “Product”, which are also the resources that an API interacts with.

Attributes, on the other hand, represent the properties or characteristics of these entities, such as “CustomerID” or “ProductName”, which are essential for transforming and processing data within the API.

Relationships denote connections or associations between entities, illustrating how they interact with each other within an API’s data model.

Cardinality describes the numerical nature of these relationships, indicating the maximum number of instances of one entity that can be associated with another, guiding data transformation processes.

Modality, on the other hand, specifies the minimum participation requirements of an entity in a relationship, ensuring that data transformations adhere to defined constraints and rules.

Entity Relationship Diagrams (ERDs)

Visual representations of these concepts are often depicted using Entity-Relationship Diagrams (ERDs).

These help API developers to visualise and design a data model effectively.

In an ERD, entities are represented as rectangles, attributes as ovals connected to their respective entities.

Cardinality, modality and relationships can be illustrated in two different ways, depending on the ERD notation that you are using.

Relationships can be represented as either lines connecting related entities in the Crow’s foot notation, or in the Chen notation they’ll contain a diamond between entities stating the relationship.

If using the Crow’s Foot notation, lines and arrows indicate the nature and constraints of the relationships between entities, as shown below.

However for modality, if you’re using the Chen notation, a single line between a relationship and entity represents partial participation, whereas a double line represents total participation.

Then to represent cardinality in Chen notation, an ‘M’ or a ‘1’ will label the line between the relationship and entity, representing ‘many’ or ‘one’.

Database Normalization

APIs often interact with databases to retrieve, manipulate, and transform data to meet the needs of various applications and services.

Data normalization is the process of organising the data in a database to reduce redundancy and improve data integrity.

It involves breaking down large tables into smaller, more manageable ones and establishing relationships between them to minimise duplication of data.

This plays a crucial role in facilitating seamless API integration and efficient data exchange.

A common scenario where normalization is necessary is in a customer relationship management (CRM) system, where customer information such as name, address, and contact details may be stored in multiple tables to avoid repeating the same data for each customer interaction.

Normalization ensures that customer data is stored efficiently and consistently, enabling the API to retrieve and process it accurately.

Constraints

Constraints are rules or conditions enforced on data within a database to maintain data accuracy and consistency.

They serve to enforce data integrity and prevent invalid data from being entered into the database, which is also essential for reliable API interactions and data transformations.

Examples of constraints include:

• Primary Key Constraint: Ensures that each record in a table is uniquely identified by a primary key field. This is typically denoted in an ERD by underlining or bolding the attribute(s) that comprise the primary key.
• Foreign Key Constraint: Enforces referential integrity by linking a field in one table to the primary key in another table, which is vital for API data transformations that rely on relational data structures. This is represented in an ERD by a line connecting the foreign key attribute in one entity to the primary key attribute in another entity.
• Not Null Constraint: Requires a field to have a value, preventing it from being left blank or null. This is depicted in an ERD by placing a small circle or other indicator next to the attribute.
• Unique Constraint: Ensures that values in a field or combination of fields are unique across the table. This is often represented in an ERD by placing a small “U” or other symbol next to the attribute(s).
• Check Constraint: Specifies conditions that must be met for data to be valid, such as a range of values or format. These are typically not represented directly in an ERD.

Working Example

The following Entity-Relationship Diagram (ERD) represents a database schema for a medical facility, using the Chen notation, capturing relationships between doctors, patients, medical records, operations, and theatres.

Entities and Attributes:

• Doctor (Attributes: doctorInsuranceNo , name , DoB , etc)
• Patient (Attributes: patientInsuranceNo , name , DoB , etc)
• Medical Record (Attributes: diagnosis , date , time )
• Operation (Attributes: date , startTime , duration , etc)
• Theatre (Attributes: theatreNo )
• Mentor (Attributes: tutees )

Relationships:

• Doctors and operations have a many-to-many relationship, meaning each doctor can be involved in multiple operations, and each operation can involve multiple doctors. Doctors must be involved in at least one operation, but operations can occur even if not all doctors are participating.
• Mentors and doctors have a one-to-many relationship, where each mentor can oversee multiple doctors, but each doctor can only have one mentor. Every mentor must have at least one doctor to mentor, but not all doctors necessarily have a mentor.
• Doctors and medical records have a one-to-many relationship, indicating that each doctor can create multiple medical records, but each medical record is created by only one doctor. Doctors must create at least one medical record, but not all medical records necessarily belong to a specific doctor.
• Patients and operations have a one-to-many relationship, meaning each patient can undergo multiple operations, but each operation is performed on only one patient. Every operation must be performed on a patient, but not all patients necessarily undergo operations.
• Patients and medical records have a one-to-many relationship, indicating that each patient can have multiple medical records, but each medical record belongs to only one patient. Each patient must have at least one medical record, but not all medical records necessarily belong to a specific patient.
• Operations and theatres have a one-to-many relationship, where each operation can take place in only one theatre, but each theatre can host multiple operations. Every operation must occur in a theatre, but not all theatres necessarily host operations.

Constraints:

• Primary Key Constraints: Each entity has a primary key attribute (doctorInsuranceNo , patientInsuranceNo , theatreNo ).
• Foreign Key Constraints: Foreign keys establish relationships between entities (e.g., doctorInsuranceNo in Operation table references doctorInsuranceNo in Doctor table).
• Unique Constraints: Ensure unique values for certain attributes (e.g., theatreNo in Theatre table).
• Check Constraints: Enforce conditions on data (e.g., ensuring date is valid in Operation table).

Conclusion

In this beginner’s guide to databases, we’ve provided you with a basic understanding of databases.

Now it’s over to you! Perhaps try creating your own ERD, and then implement it in SQL.

There’s plenty more to learn about databases, especially with regards to their role in web applications and API development.

We encourage you to explore the further reading resources below to keep learning!

By leveraging autonomous agents, developers can automate tasks and reduce manual intervention, leading to increased efficiency and scalability.

Explore the power of autonomous agent technology by requesting a FREE demo of APIDNA’s new API integration platform by clicking here.

Further Reading

What is a relational database? – IBM

SQL Tutorial – w3schools

phpMyAdmin Tutorials – SiteGround

What is an Entity Relationship Diagram (ERD) – LucidChart

Database Normalization – Normal Forms 1nf 2nf 3nf Table Examples