2

I'm currently trying to understand the nuances of Insertion/Deletion/Modification anomalies in SQL.

Currently, the example I'm trying to understand is as follows: 

ENROLLMENT
StudentID(PK)   StudentName   ClassID   ClassName
111             Joe           E1        English1
222             Bob           E1        English1
333             Mary          H1        History1

The problem the example wants me to answer is:

Which of the following causes an insertion anomaly?

with the answers being

Inserting a Student without a Class

and

Inserting a Class without a Student

I don't really understand why one of these answers is more right than the other, why, or how. It seems to me like either could be acceptable. Thanks in advance.

R.C.
  • 21
  • 5
  • 3
    There doesn't appear to be enough information in this question to make it answerable. "Insertion Anomaly" sounds like some egghead term that a professor made up. – Robert Harvey Dec 04 '18 at 18:29
  • @RobertHarvey Yes. Probably Codd. You can read more here: https://en.wikipedia.org/wiki/Database_normalization#Objectives – mypetlion Dec 04 '18 at 18:33
  • Those both seem like insertion anomalies to me. – mypetlion Dec 04 '18 at 18:37

1 Answers1

1

You need to think in terms of how data is added to a system naturalistically (i.e. what series of events occur in the real world).

In this case you would create a set of classes, prior to registration, and then create and assign students to them when they turned up to register.

You would be unlikely to create a set of students and then create and assign classes to each one.

A class might only be able to hold 30 students. How do you deal with any extra students who want to be registered for that class?

If you register 100 students and then decide to create classes, which subjects do you create?

Why do students decide to turn up to register? [Presumably because of the classes on offer.]

You can create as many classes as you're able to fit into your time-table. The number of students that actually register might mean a class is cancelled, but it has to exist in the first instance.

In summary, "Inserting a Student without a Class" would be more likely to cause an insertion anomaly.

melkisadek
  • 1,043
  • 1
  • 14
  • 33