Insertion Anomaly

Question

I am learning insertion anomaly from here. Following data is written in it,

Insert Anomalies

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?

Thanks in advance

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