Demonstrate anomalies that can occur in improperly designed tables.

Question:

Demonstrate anomalies that can occur in improperly designed tables.

a. Create a table of your choosing that has at least three columns.

b. Using the table, demonstrate an anomaly that occurs when the same data is inserted multiple times with different values, and explain what the anomaly means for data integrity.

c. Using the table, demonstrate a deletion anomaly with SQL, and explain what the anomaly means for data integrity.

And also in this step, you compare the table created with a file that contains all of the same information. Create a file in any format you’d like that contains all the same columns and at least 4 rows of information as the table you created. There are many formats you can use. Some examples include XML, flat file, binary, text, and JSON; this list is not exhaustive. All columns and at least 4 rows should be present in the file in its new format. Make sure to provide the file or a screenshot of the file and to explain your choices.

Summary:

Here we have to create a table to demonstrate anomalies that can occur in improperly designed tables. A table employee is created with four attributes employee id, name, department, and club. Then, some records are added such that multiple times the same record is present with a small change in one attribute value, which leads to an update anomaly. It creates problems while updating. Similarly, deletion anomaly would delete unintended data. Such that some of the domain values would cease to exist.

Explanation:

a) Here is the table employee has created with attributes employee_id, employee_name, department, student_club and insert the values for respective attributes.

Employee_ID	Employee_Name	Department	Student_Club
3240	Pravallika	CSE	Sports
3241	Mounika	IT	Dance
3241	Mounika	IT	Singing
3242	Mahitha	ECE	Singing
3242	Mahitha	ECE	Dance

Source code for creating a table employee in SQL:

CREATE TABLE Employee (
  Employee_id INTEGER,
  Employee_name text,
  department text,
  Student_club text 
);
-- insert some values
INSERT INTO Employee VALUES (3240, 'Pravallika', 'CSE', 'Sports');
INSERT INTO Employee VALUES (3241, 'Mounika', 'IT', 'Dance');
INSERT INTO Employee VALUES (3241, 'Mounika', 'IT', 'Singing');
INSERT INTO Employee VALUES (3242, 'Mahitha', 'ECE', 'Singing');
INSERT INTO Employee VALUES (3242, 'Mahitha', 'ECE', 'Dance');
-- fetch some values
SELECT * FROM Employee;

Output:

 

b) Using the table, demonstrate an anomaly that occurs when the same data is inserted multiple times with different values, and explain what the anomaly means for data integrity.

Two records are inserted multiple times with different values of student_club. Every student can be a part of more than one club. every student_club has an entry of student details.

This anomaly of inserting multiple times the same record with a change in the single attribute is said to be an Update anomaly.

This creates data inconsistency.

From the above table, if we have entered the wrong department for Mounika, we want to change it from “IT” to “CSE”. So we might update one record and in the other record, it might remain with the same wrong department.

 

 

c) Using the table, demonstrate a deletion anomaly with SQL, and explain what the anomaly means for data integrity.

The unintended loss of data due to the deletion of other data is called a deletion anomaly.

For example, If student_club “Sports” is deleted from the table. In that case, “Pravallika” the employee and the department “CSE” would cease to exist. This causes data inconsistency.

 

Also, read Z Three Numbers.