Defining Database Relationships

Explore the art of defining relationships, understanding their significance, and identifying different types in Infoveave. From practical examples to hands-on experience, discover how Infoveave helps you to build database relationships, enhancing the depth of your data analysis.

What are Database Relationships?

Relationship refers to the connection between tables in a database. These relationships are established based on key columns shared between tables. The purpose of defining relationships is to create a logical link/bridge between data in different tables, enabling more efficient and meaningful analysis. In Infoveave, these relationships are established through key columns/primary keys and foreign keys.

Key Concepts

• Key Column-The Unique Identifier

  • The key column is the unique identifier in a table.
  • It serves as a reference point for establishing connections.
  • In Infoveave, when creating a relationship, the key column of one table becomes the basis for connecting with another.

Example Scenario: Let us say we have two tables - Customers and Orders. The “CustomerID” is the key column in the Customers table, and the “CustomerID” is the key column in the Orders table (we may call it aa foreign key). So, with the column “CustomerID” being common in both tables we can bridge between the two tables.

Importance of Database Relationships

Imagine you are in a library where books are scattered randomly without any system. Finding a specific book would be a nightmare. Now, picture the same library with organized shelves, each holding books by genre and also telling where to find you the related books and in which shelve, that’s precisely what relationships do.

• Efficient Data Access

  • Relationships allows you to retrieve related information effortlessly, similar to having a roadmap – you can quickly navigate through tables, fetching exactly what you need.

• Maintaining Data Integrity:

  • Relationships ensure that your data align correctly, and this alignment maintains the accuracy and reliability of your data.

• Facilitating Complex Queries:

  • Relationships allow you to perform complex SQL queries seamlessly.

• Enabling Structured Analysis:

  • Relationships set the path for organized structured data analysis, creating a robust foundation for insights.

Types of Database Connection

Relationships influences how data is structured and how queries can efficiently retrieve information based on these relationship connection types. Infoveave supports the below connection types:

• One to One
• One to Many
• Many to Many

One to One

In a one-to-one relationship, each record in one table is associated with one and only one record in another table. The connection is established through a shared key column, usually the primary key of one table becoming the foreign key of the other. This type of relationship is useful when certain attributes or details are relatively distinct and can be separated into a different table to avoid redundancy.

Example: Employee Database In a company database, there’s a need to store general information about each employee in one table and more specific details in another table.

Employees Table:

• • Primary Key: EmployeeID (Unique identifier for each employee)
  • Fields: EmployeeID, FirstName, LastName, Department

EmployeeID	FirstName	LastName	Department
101	John	Doe	IT
102	Jane	Smith	HR

EmployeeDetails Table:

• Foreign Key: EmployeeID (Links to the EmployeeID in the Employees table)
  • Fields: EmployeeID, Address, ContactNumber, Position

EmployeeID	Address	ContactNumber	Position
101	123 Main St	555-1234	Developer
102	456 Oak Ave	555-5678	HR Specialist

Relationship:

• The DepartmentID in the Departments table is the Primary Key.
• The DepartmentID in the Employees table is a Foreign Key, linking to the DepartmentID in the Departments table.

Explanation:

• Each employee in the Employees table has a unique EmployeeID.
• The EmployeeDetails table stores additional details for each employee, and its EmployeeID field links to the corresponding employee in the Employees table.
• This ensures that each employee has only one record in the EmployeeDetails table, creating a one-to-one relationship.

One to Many

A one-to-many relationship in a relational database signifies that a single record in one table can be associated with multiple records in another table. This association is established through a shared key column, typically with the primary key of the first table serving as the foreign key in the second table.

Example: Department Database Imagine a university with various departments, each having employees and associated assets. The goal is to model the relationships between these entities for efficient management.

Entities Table: Represents the different university departments.

• Primary Key: EntityID(Unique identifier for each employee)
  • Fields: EntityID, EntityName

EntityID	EntityName
501	Computer Science
502	Mathematics

Employees Table: Contains information about employees in different departments.

• Foreign Key: EntityID(Links to the EntityID in the Entities table)
  • Fields: EmployeeID, EmployeeName, Position, EntityID

EmployeeID	EmployeeName	Position	EntityID
301	John Doe	Professor	501
302	Jane Smith	Assistant	501
303	Bob Johnson	Lecturer	502

Assets Table: Stores details about assets owned by each department.

• Foreign Key: EntityID(Links to the EntityID in the Entities table)
  • Fields: AssetID, AssetName, Value, EntityID

AssetID	AssetName	Value	EntityID
101	Desktop	$800	501
102	Projector	$200	502
103	Whiteboard	$300	501

Relationship:

• The EntityID in the Entity table is the Primary Key.
• The EntityID in the Employee table is the Primary Key.
• The EntityID in the Assest table is the Primary Key.
• The EntityID in the Employee table and Assest table are Foreign Keys, linking to the respective Primary Keys in the Entity table.

Explanation:

• Each department (Entity) in the Entity table has a unique EntityID.
• The Employees table stores additional details for each employee, and its EntityID field links to the corresponding department(Entity) in the Entity table.
• The Assets table stores additional details for each department(Entity) asset, and its EntityID field links to the corresponding department (Entity) in the Entity table.

Many to Many

A many-to-many relationship in a relational database indicates a complex association where multiple records in one table can be linked to multiple records in another, forming a bi-directional connection. This relationship requires the introduction of a third table, known as a junction or link table, which includes foreign keys from both original tables.

Example: Library Database In a library database, we want to manage information about books, authors, and the relationship between them.

Books Table:

• Primary Key: BookID (Unique identifier for each book)
  • Fields: BookID, Title, Genre

BookID	Title	Genre
101	”Introduction to Programming”	Computer Science
102	”The Great Gatsby”	Fiction

Authors Table:

• Primary Key: AuthorID (Unique identifier for each author)
  • Fields: AuthorID, AuthorName

AuthorID	AuthorName
201	John Smith
202	Jane Doe

BookAuthor Relationship Table:

• Foreign Keys: BookID (Links to BookID in Books table), AuthorID (Links to AuthorID in Authors table)
  • Fields: RelationshipID, BookID, AuthorID

RelationshipID	BookID	AuthorID
301	101	201
302	101	202
303	102	202

Relationship:

• The BookID in the Books table is the Primary Key.
• The AuthorID in the Authors table is the Primary Key.
• The BookID and AuthorID in the BookAuthor Relationship table are Foreign Keys, linking to the respective Primary Keys in the Books and Authors tables.

Explanation:

• Each book in the Books table has a unique BookID.
• Each author in the Authors table has a unique AuthorID.
• The BookAuthor Relationship table captures the many-to-many relationship between books and authors, indicating which books are authored by which authors.

Define Relationship in Infoveave

Creating a relationship on Infoveave start with creating related Datasource in Infoveave.

Note: We’ve prepared a customer dataset exclusively for you to download and utilize to learn defining one to one relationship this lesson. Make sure the dataset is downloaded and a Datasource is created within Infoveave. This will also enhance your understanding of creating and configuring Datasources in Infoveave.

Dataset: CustomerData

Let us delve into the detailed steps and methods for seamless data batch upload and ingestion.

1. Navigate to the Studio module in the Infoveave main menu.
2. Select Datasources from the drop-down options.
3. Locate and identify the specific Datasource to which you intend to upload batch data. In our case, the Datasource name is “Sales Data”.
4. Once you have identified the target Datasource (Sales Data), click on the edit icon associated with that Datasource.

5. Drag and drop both the newly created table to the designer canvas, in this case it will be customerdata.
6. Identify the common key column between the two tables. In this case, the “Customer ID” column is common.
7. Define the “Customer ID” column on the customerdata table as a dimension.

8. Start defining the hierarchy by giving it a name under the field Hierarchy Name.
9. Under the field Attribute, select the attribute column you want to access through the key column. In this case, choose “Customer Name.”
10. Select the “Customer ID” as the attribute key column and give it a name under the field Attribute Name.
11. Click on the Add Attribute button to add the attribute column to the hierarchy.
12. Checkmark the option Use in Hierarchy to use the defined attribute in the hierarchy.
13. You can define multiple hierarchies under the selected key column if needed.
14. Click on Add Dimension to save the dimension.

15. Establish the relationship between the identified key columns (in this case, “Customer ID”) by drawing and linking the columns on the canvas.

  You’ve successfully established a relationship between the salesdata and Customerdata tables in Infoveave, connecting them through the common key column “Customer ID.” This relationship enables you to analyze and derive insights based on the linked data from both tables.

Conclusion

In conclusion, the importance of relationships with real-world scenarios illustrated how one-to-one, one-to-many, and many-to-many relationships streamline data management. The hands-on exercise, establishing a connection between “Sales Data” and “Customer Data” for more insightful analysis, offers a valuable opportunity to learn defining relationships within the Infoveave platform.