Lec-18: Many to Many Relationship in DBMS | M-N Relationship

Understanding Many to Many Relationships in Databases

Introduction to Many to Many Relationships

• The video begins with a greeting and a wish for a Happy Independence Day, followed by an introduction to the topic of many-to-many relationships in databases.

• The speaker mentions that they have previously covered one-to-one and one-to-many relationships, setting the stage for discussing many-to-many relationships using students and courses as examples.

Defining the Relationship

• Emphasis is placed on understanding what constitutes a many-to-many relationship, which is crucial for exams like UGC NET & GATE.

• Two entities are introduced: "student" with attributes roll number, name, age; and "course" with attributes course ID (CID), course name, credits.

Primary Keys and Attributes

• Roll number is identified as the primary key for the student entity while course ID serves as the primary key for the course entity.

• The speaker explains that questions regarding these entities will typically provide information about primary keys and attributes upfront.

Implementation in SQL

• The discussion transitions into how these entities are represented in an Entity-Relationship (ER) model and implemented using SQL.

• Tables are created for both student and course entities along with a relationship table that captures their association.

Foreign Keys in Relationship Table

• In the relationship table, roll number acts as a foreign key referencing the student table's primary key while course ID references the course table's primary key.

• This structure allows tracking which students are enrolled in which courses through this relationship table.

Data Representation

• The speaker illustrates how data can be entered into this relationship table. For example, roll numbers 1 through 5 could be associated with various courses (C1, C2).

• A clear explanation follows on how multiple entries can exist due to the nature of many-to-many relationships—students can enroll in multiple courses while each course can have multiple students.

Conclusion of Example Data Entry

• An example is provided where roll number 1 studies both C1 and C2. This demonstrates how one student can take multiple courses.

Understanding Primary Keys and Relationships in Database Tables

Identifying Primary Keys

• The discussion begins with identifying the primary key in a table that includes students and courses. The roll number is identified as a primary key in one table, while course ID serves as the primary key in another.

• It is clarified that neither roll number nor course ID can serve as standalone primary keys due to their repeating values within the tables.

Composite Keys

• A composite key is introduced, which combines both roll number and course ID to create a unique identifier for each record. This combination allows for individual identification of records.

• The importance of using both roll number and course ID together as a composite key is emphasized, ensuring uniqueness across entries.

Table Reduction Challenges

• The speaker addresses the question of whether tables can be reduced. With three tables present, it’s noted that reduction isn't feasible due to the nature of the combined primary keys.

• An explanation follows on why combining these two sets (roll numbers or course IDs alone) would lead to inaccuracies, reinforcing that no reduction can occur in many-to-many relationships.

Relationship Types and Their Implications

• In many-to-many relationships, it is stated that there cannot be any reduction of tables; thus, all three must remain intact.

• The speaker contrasts this with one-to-one and one-to-many relationships where reductions are possible.

Exam Relevance