Lec-17: One to Many Relationship in DBMS in Hindi | 1-M Relationship

One-to-Many Relationship in Databases

Introduction to One-to-Many Relationships

• The speaker introduces the topic of one-to-many relationships in databases, emphasizing its importance in Entity-Relationship (ER) modeling.

• Acknowledges that while many resources provide a basic overview, the concept is much deeper and crucial for various exams like UGC NET and GATE.

Understanding the Concept

• The focus will be on understanding the underlying concepts rather than just examples or representations of one-to-many relationships.

• Introduces two entities: Customer and Order, explaining that a customer can place multiple orders, establishing a one-to-many relationship.

Representation of Relationships

• Clarifies how to represent this relationship using notation such as "1 to M" or "1 to infinity."

• Emphasizes that representation is not the main task; instead, converting ER models into relational models is critical.

Transitioning from ER Model to Relational Model

• Discusses how ER models are used for design purposes while relational models are implemented physically using tables.

• Describes creating tables for both Customer and Order entities along with their attributes.

Primary Keys and Foreign Keys

• Identifies primary keys within each table: ID for Customer and order number for Order.

• Stresses that every table must have a primary key, which may be given or assumed if not explicitly stated.

Attributes in Relationship Tables

• Explains what attributes exist in relationship tables, highlighting mandatory attributes such as IDs functioning as foreign keys referencing primary keys from other tables.

• Mentions that relationships can also have descriptive attributes; here, 'date' serves as an example of such an attribute.

Data Insertion Example

• Discusses inserting data into the relationship table with two foreign keys: ID from Customer and order number from Order.

Understanding One-to-Many Relationships in Databases

Overview of Order and Customer Relationship

• The speaker introduces an order table containing order number, order name, and cost, emphasizing the need to identify which customer placed each order.

• A relationship table is necessary to establish connections between customers and their orders, highlighting a one-to-many relationship where one customer can place multiple orders.

• The concept of unique IDs for orders is discussed; while customer IDs may repeat due to multiple orders, each order ID remains unique as it corresponds to only one customer.

Primary Key Identification

• The speaker explains that in a one-to-many relationship, the primary key (order number) uniquely identifies each order while allowing for repeated customer entries.

• It is noted that the primary key on the "many" side (orders) will also serve as a foreign key in the relationship table.

Table Reduction Strategies

• The discussion shifts to reducing the number of tables from three to two by merging them based on shared primary keys.

• By combining tables with matching primary keys (order numbers), a new consolidated table can be created that retains essential data like item names and costs.

Merging Tables: Practical Considerations

• The process of merging involves taking relevant fields from both tables—such as ID, item name, cost, and date—to create a comprehensive dataset.

• Examples are provided illustrating how merged data would look when consolidating orders from different customers into fewer rows.

Key Takeaways on Relationships

• Customers cannot be merged with other entities if they do not have unique identifiers; however, merging is possible when dealing with many-to-one relationships where primary keys align.

• Emphasis is placed on understanding that wherever there are many entries in a database structure, those should dictate how tables can be merged or related.

Importance of Understanding Database Concepts

• The speaker stresses the significance of grasping these foundational concepts in databases for effective data management and design strategies.

• A reminder is given about common exam questions regarding one-to-many relationships and their implications for database structuring.