Powerful Data Models: Building Strong Database Relationships

Overview of Data Models

Data models are the underlying structure of a database. They serve as conceptual tools to describe the data, the relationships among data, data semantics, and constraints. Since a database is a collection of interrelated data, it is essential to handle data semantics and constraints effectively. Data models assist in designing databases at three levels:

1. Physical Level
2. Logical Level
3. View Level

Since databases use a three-tier architecture, they must be designed considering these levels.

---

Categories of Data Models

There are four major types of data models:

1. Relational Model
2. Entity-Relationship Model (ER Model)
3. Object-Based Data Model
4. Semi-Structured Data Model

While their other models also exist, this discussion focuses on these three.

---

Entity-Relationship (ER) Model

The Entity-Relationship Model (ER Model) is also known as the ER Data Model. In this model:

• E stands for Entity (real-world objects)
• R stands for Relationship (associations between entities)

Key Characteristics:

• The ER model consists of a collection of basic objects called entities.
• Each entity is distinguishable and has attributes.
• Entities have relationships with other entities.
• The ER model is widely used in database design before creating actual tables.

Example of an ER Model:

Entity	Attributes	Relationships
Employee	Name, SSN, Salary, Gender	Works for (Department)
Department	Dept Name, Dept ID	Manages (Employees)
Project	Project ID, Name	Works on (Employee)

ER Data Model Diagram

ER Example

In the above diagram, we have seen many-to-many relationships, and few of us might doubt what is this Relationship.

Now we are going to see what Relationships.

Why Are Relationships Required in DBMS?

In a relational database management system (RDBMS), data is represented in the form of rows and columns, which are arranged into tables. A database consists of multiple tables, such as Table-1, Table-2, Table-3, up to Table-N. where data is distributed across different/multiple tables. To maintain the integrity and efficiency of the data, it is essential to establish relationships between these tables.

Relationships link records from one table to records in another table, allowing data to be interconnected and retrieved efficiently. For example, we have our own data like name, age, and phone number. When we apply to jobs, we are entering our data, so our data is stored and related in those databases. So, in there data applicants id is stored and relation is establishing between applicant and company.

To establish these relationships, we use two primary keys:

• Primary Key
• Foreign Key

These keys help link different tables in a well-structured manner. The concept of primary and foreign keys will be discussed in detail in a separate session when we learn about constraints. Now, let’s explore the different types of relationships in DBMS.

Types of Relationships in DBMS

There are three main types of relationships in a database:

• One-to-One Relationship.
• One-to-Many Relationship.
• Many-to-Many Relationship.

One-to-One Relationship

In a one-to-one relationship, each record in Table-1 corresponds to a single record in Table-2 and vice versa. This means that for every record in the first table, there is exactly one related record in the second table. Like one person having one adhar card.

Example:

Table 1: Person

Person_ID	Name	Age
1	A	30
2	B	25

Table 2: Passport

Passport_ID	Person_ID	Passport_Number
101	1	P12345
102	2	P67890

Here, each person has exactly one passport, and each passport is linked to only one person. This defines a one-to-one relationship.

So person one with ID 1 has one passport and has ID 101. In a similar way, person two holding ID 2 has one passport with ID 102.

One-to-Many Relationship

In a one-to-many relationship, one record in Table-1 can be associated with multiple records in Table-2, but each record in Table 2 is linked to only one record in Table-1. For example, if one company has multiple employees, one book can have multiple authors.

Example:

Table 1: Branch

Branch_ID	Branch_Name
B1	ABC Bank
B2	XYZ Bank

Table 2: Accounts

Account_ID	Branch_ID	Account_Type
1001	B1	Savings
1002	B1	Current
1003	B2	Savings

In this example, one bank branch can have multiple accounts, but each account belongs to only one branch. This is an example of a one-to-many relationship.

Many-to-Many Relationship

A many-to-many relationship occurs when multiple records in Table-1 relate to multiple records in Table-2. To establish this type of relationship, we use a junction table (an intermediary table) to link the two tables. The junction table is mandatory because one cell can hold one value; it can’t store multiple values.

Representing multiple values in a single Cell is not possible in RDBMS.

CID	SID
101	S1,S2

Example:

Table 1: Students

Student_ID	Name
S1	Alice
S2	Bob
S3	Charlie

Table 2: Courses

Course_ID	Course_Name
101	Math
102	Science
103	History

Table 3: Student_Course (Junction Table)

Student_ID	Course_ID
S1	101
S1	102
S2	101
S2	103
S3	102
S3	103

In this scenario, a student can enroll in multiple courses, and a course can have multiple students. The junction table (Student_Course) establishes the many-to-many relationship.

---

Object-Based Data Model

The Object-Based Data Model incorporates object-oriented concepts such as:

• Encapsulation
• Inheritance
• Polymorphism

Features:

• Uses ER model concepts along with object-oriented features.
• Suitable when the front end is designed using object-oriented programming languages like Java, C++, or C#.
• Supports complex data structures.

Example:

Object	Attributes
Person	Name, Age, Address, Phone

W3Hiring

Id Subscribers Services Experience

Object-Relational Data Model

A variation of this model is the Object-Relational Data Model, which combines relational and object-based models.

---

Semi-Structured Data Model

The Semi-Structured Data Model deals with data specification and allows for flexibility in data representation.

Key Characteristics:

• Individual data items of the same type may have different attributes.
• Mostly used for data transfer between applications.
• The primary language supporting this model is XML (Extensible Markup Language).

XML Example:

<note>
    <from>W3 Hiring</from>
    <to>All Subscribers</to>
    <subject>Thank You</subject>
    <body>Thank you for subscribing W3 Hiring. Good luck!</body>
</note>

XML Diagram

---

Other Data Models

Some older data models include:

1. Network Data Model
2. Hierarchical Data Model

These models are not widely used today because they are closely tied to underlying implementation structures, making data modeling more complex.

Model	Structure Used	Usage Today
Network Data Model	Graphs	Obsolete
Hierarchical Data Model	Trees	Obsolete

Hierarchical Data Model Diagram

---

Conclusion

In this discussion, we explored the following data models:

1. Relational Model (to be discussed in the next article)
2. Entity-Relationship Model (ER Model)
3. Object-Based Data Model
4. Semi-Structured Data Model

Additionally, we touched upon older models like the Network Data Model and Hierarchical Data Model. Understanding these models is crucial for designing efficient and structured databases.

We also discussed Relationships and their types and how they are helping in the real world.