SQL Query to Demonstrate Addition Anomaly in Referential Integrity in a Table

Introduction

To demonstrate an addition anomaly in a referential integrity in a table, we can create a simple database with two tables: a parent table and a child table. The parent table should have a primary key column, and the child table should have a foreign key column that references the primary key column in the parent table. We can then insert some rows into both tables and run a SELECT statement on the child table that filters the results based on a column from the parent table. If we delete a row from the parent table, the result of the SELECT statement may no longer be accurate, as it will not include any rows from the child table that were associated with the deleted row in the parent table. This is an example of an addition anomaly.

Definition

An SQL query to demonstrate an addition anomaly in a referential integrity in a table is a query that is used to create a database with a parent table and a child table, and to insert and delete rows in these tables in a way that demonstrates an addition anomaly.

An addition anomaly in a referential integrity in a table occurs when a delete operation on a parent table causes the result of a SELECT statement on a child table to be incorrect. This can occur when the child table has a foreign key constraint that references the parent table, and the SELECT statement includes a WHERE clause that filters the child table based on a column from the parent table.

Example 1

Here is an example of an SQL query that demonstrates an addition anomaly in referential integrity in a "orders" table −

SQL Query

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (1, 1, 1, 2);

INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (2, 1, 2, 3);

INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (3, 2, 3, 4);

SELECT SUM(quantity)
FROM orders
WHERE customer_id = 1;

In this example, we have a table called "orders" that stores information about orders placed by customers. The table has a foreign key constraint on the "customer_id" column, which references the "customer_id" column in the "customers" table. The "orders" table also has a foreign key constraint on the "product_id" column, which references the "product_id" column in the "products" table.

We insert three rows into the "orders" table, each representing an order placed by a customer. The first order is placed by customer 1 and consists of 2 units of product 1. The second order is also placed by customer 1 and consists of 3 units of product 2. The third order is placed by customer 2 and consists of 4 units of product 3.

Finally, we run a SELECT statement to get the sum of the quantities of all orders placed by customer 1. The result of the query is 5, which is the correct total for the quantity of products ordered by customer 1.

However, if we delete one of the orders placed by customer 1 (for example, the order for 3 units of product 2), the result of the SELECT statement will be incorrect, as it will only include the quantity of the remaining order placed by customer 1 (2 units of product 1). This is an example of an addition anomaly, as the result of the query does not accurately reflect the total quantity of products ordered by customer 1.

Example 2

Here is an example of an SQL query that demonstrates an addition anomaly in referential integrity in a "student" table −

SQL Query

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE grades (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  grade INT NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

INSERT INTO students (student_id, name)
VALUES (1, 'John Smith');

INSERT INTO students (student_id, name)
VALUES (2, 'Jane Doe');

INSERT INTO grades (student_id, course_id, grade)
VALUES (1, 1, 90);

INSERT INTO grades (student_id, course_id, grade)
VALUES (1, 2, 85);

INSERT INTO grades (student_id, course_id, grade)
VALUES (2, 1, 95);

SELECT AVG(grade)
FROM grades
WHERE student_id = 1;

In this example, we have a "students" table that stores information about students, and a "grades" table that stores information about grades that students receive in courses. The "grades" table has a foreign key constraint on the "student_id" column, which references the "student_id" column in the "students" table.

We insert two rows into the "students" table, each representing a student. We also insert three rows into the "grades" table, representing grades that the two students received in different courses.

Finally, we run a SELECT statement to get the average grade of all grades received by student 1. The result of the query is 87.5, which is the correct average for the grades received by student 1.

However, if we delete student 1 from the "students" table, the result of the SELECT statement will be incorrect, as it will only include the grade received by student 2 in course 1. This is an example of an addition anomaly, as the result of the query does not accurately reflect the average grade received by student 1.

Conclusion

An addition anomaly in referential integrity in a table occurs when a delete operation on a parent table causes the result of a SELECT statement on a child table to be incorrect.