understanding the concept of Insertion Anomaly

Question

I am learning insertion anomaly from www.sqa.org.uk/e-learning. Following data is written in it,

An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes. For example this is the converse of delete anomaly - we can't add a new course unless we have at least one student enrolled on the course.

StudentNum    CourseNum   Student Name    Address     Course
S21           9201        Jones           Edinburgh   Accounts
S21           9267        Jones           Edinburgh   Accounts
S24           9267        Smith           Glasgow     physics
S30           9201        Richards        Manchester  Computing
S30           9322        Richards        Manchester  Maths

I am stuck understanding the concept. Why would we need a student to be enrolled into the course for it to exist?

Izak

Answer 1

This means that the schema is not normalized, i.e. now you have the information about a course in table Student.

So in order to insert course details, you need to provide the student's details as well.

There are different forms of normalization you need to read about, but in this example the right path to solve this anomaly most likely would be to create three tables i.e. strong entity types Student, Course, and an associative entity type linking table StudentCourse (possibly called a Registration or a Grade) which will allow you to store Student and Course data without duplicates and anomalies, as well as assign many courses to many students.

You can read through normalization examples in the following link, it will give you a better idea:

http://www.sqa.org.uk/e-learning/MDBS01CD/page_26.htm#Example

Renettarenew

I am pretty sure that in this particular question, the schema is not normalized because it does not satisfy 2nd normal form (assuming the candidate key is StudentNum, CourseNum). – Nevertheless

Answer 2

The example assumes that studentnum and coursenum form a composite primary key implementing the integrity rule that a student cannot be enrolled in the same course more than once, i.e. the combination is unqiue. Therefore attempting to add a course record requires a studentnum as well. To avoid this situation while still maintaining the integrity rule the composite key is implemented in an associative entity and course and students are in separate entities.

Chivaree

Answer 3

Insertion anomaly: According to the example taken above where in only one table is there with all the attributes. Now I want to introduce new course then I must be knowing about the student id (student who wants to study that course) which is a primary key and can't be empty. According to the table taken you must know primary key(student id). If you want to enter other fields, but right now we have only new course details without student being registered. So, you cannot just enter new course and leave primary key empty. This is insertion anomaly.

Slut

Please use standard grammar, spelling & punctuation. – Narrate

Answer 4

You may not realise that you need to insert the key values (which student is only partially dependent on). Therefore, you might need to add a fictitious student when adding a course.

Dalia