Explain many to many relationships and how to deal with them

Explain many to many relationships and how to deal with them in design. Describe referential integrity. Describe in detail the three delete rules. Consider relations A and B. Relation A represents the entity on the \"one side\" of a one-to-many relative Relation B represents the entity on the \"many side\" of the one-to-many relationship. The delete rule b relations A and B is cascade. What happens if an attempt is made to delete a record in relation A and its primary key value appears foreign key value in one or more records of relation B? List and describe first through third normal form Indicate the normal form for this relation. If this relation is not in third normal form decompose it into points)

Solution

1. Many to Many relationship means many columns in one table are associated with many columns is another table.It is one type of cardinality which shows the relationship between two tables.

For example if we take student and class table, Student can take many classes and class can have many students.Now these both have many to many relationship. But in the table design it is not possible to establish a relationship between two tables in order to achieve this we need a another reference table to show this many to many relationship.

If we want to show this with student and class tables with many to many relation we need another table which is reference table. For instance we will name this as signup. student table contains unique student id and class table will contain unique class id. But in the reference table we will maintain different records for the same student with all the classes he registered.

2. Referential intigrity is the concept to maintain the database tables consistent. All the foreign key attributes imposed on the table must be in synch with the primary key attributes. i.e. We use foreign key constraint between the tables to connect the those tables. This foreign key can be imposed on the attributes which is defined as the primary key in the another table which the foreign key table is referring.

To main concept behind the referential intigrity constraint is whenever the changes in the primary key attributes is happened the respective attribute changes must be reflected in the fellow tables which is holding foreign key constraint.Then only both the tables are in synch otherwise there will be no accuracy and the meaningfull data in the tables.

3. For every tables having foreign keys the attributes will have some delete rules which means there are some restrictions to delete the data for the attributes having foreign key\'s.The three DELETE rules are discussed below.

4. If we attempt to delete the record from the relation A then the corresponding records in relation B which is related to the deleting record in A will also get deleted. Because CASCADE rule is implemented, whenever cascade is implemented in the table and when we delete the row from the primary key table then it deletes the associated rows from the foreign key table automatically inorder to maintain the consistency.