Lesson 9: Update Anomalies and the Relational Data Model¶
Why bad tables hurt, and what a relation formally is
About this lesson
Course: Database Management Systems
Unit 3: Relational Data Model · 7 Lecture Hours
Lesson duration: 1 lecture
Reading: Elmasri & Navathe, Ch. 5.1, 14.1
Learning Outcomes
- Identify insertion, deletion and modification anomalies in a badly designed table.
- Explain that anomalies are caused by storing facts about two things in one relation.
- State the formal definition of a relation, and the terms domain, tuple, attribute, degree, cardinality.
- Explain the four characteristics of relations, and why order does not matter.
- Distinguish relation schema from relation state (instance).
9.1 Update Anomalies — the Motivation¶
Unit 1 said redundancy causes inconsistency. This lesson makes that precise, because update anomalies are the reason normalisation exists (Unit 5).
Consider a college that stores everything in one table.
The bad design
STUDENT_COURSE( Rollno, Sname, Dateofbirth, SID, Cname, TotalSeats, TeacherInCharge, Dateofadmission )
| Rollno | Sname | Dateofbirth | SID | Cname | TotalSeats | TeacherInCharge | Dateofadmission |
|---|---|---|---|---|---|---|---|
| X2019 | Ankit Sharma | 2001-03-14 | CS01 | Computer Science | 60 | R. K. Gupta | 2019-07-15 |
| X2019 | Ankit Sharma | 2001-03-14 | MA02 | Mathematics | 50 | A. Gupta | 2019-08-01 |
| X2020 | Riya Verma | 2002-05-11 | CS01 | Computer Science | 60 | R. K. Gupta | 2019-07-16 |
| Z1188 | Sana Iqbal | 2001-11-02 | CS01 | Computer Science | 60 | R. K. Gupta | 2019-07-18 |
Look at the redundancy: "Computer Science has 60 seats and is taught by R. K. Gupta" is stored three times; "Ankit Sharma was born on 2001-03-14" is stored twice.
Three kinds of failure follow directly.
Insertion anomaly¶
Insertion anomaly
You cannot record a fact because some unrelated information is missing.
The college creates a new course, PH01 Physics, 40 seats, teacher-in-charge Dr Rao. No student has enrolled yet.
Try to insert it:
INSERT INTO STUDENT_COURSE
VALUES (NULL, NULL, NULL, 'PH01', 'Physics', 40, 'Dr Rao', NULL);
Rollno is part of the primary key (Rollno, SID), and a primary key may not be NULL (entity integrity, Lesson 2). The insert is rejected. The college cannot record a course until a student joins it — an absurd restriction created purely by the design.
The symmetric case: a student who has been admitted but not yet assigned a course cannot be recorded either.
Deletion anomaly¶
Deletion anomaly
Deleting a row destroys facts you wanted to keep, as a side effect.
X2020 Riya Verma and Z1188 Sana Iqbal withdraw, then X2019 Ankit Sharma drops Computer Science. All rows mentioning CS01 disappear.
Result: the college has lost the facts that a course CS01 called Computer Science exists, has 60 seats, and is taught by R. K. Gupta. Deleting an enrolment silently destroyed a course.
Modification (update) anomaly¶
Modification anomaly
A single logical change requires many physical updates, and if any is missed the database becomes inconsistent.
R. K. Gupta is replaced by Dr S. Iyer as teacher-in-charge of Computer Science. That is one real-world fact, but it appears in three rows. If a program updates only the row it happens to be looking at:
| Rollno | SID | Cname | TeacherInCharge |
|---|---|---|---|
| X2019 | CS01 | Computer Science | Dr S. Iyer |
| X2020 | CS01 | Computer Science | R. K. Gupta |
| Z1188 | CS01 | Computer Science | R. K. Gupta |
The database now asserts that one course has two different teachers-in-charge. It is inconsistent, and there is no way to tell which value is right.
The single sentence that explains all three
Anomalies arise because the relation stores facts about more than one kind of thing. STUDENT_COURSE mixes facts about students, facts about courses, and facts about admissions. A fact about a course should be stored once, in a place that exists independently of any student.
The cure¶
Split into one relation per kind of thing — exactly what the ER diagram of Unit 2 already told us:
STUDENT ( Rollno, Sname, Dateofbirth )
COURSE ( SID, Cname, TotalSeats, TeacherInCharge )
ADMISSION ( Rollno, SID, Dateofadmission )
| Anomaly | Now? |
|---|---|
| Insert a course with no students | Fine — insert into COURSE alone. |
| Delete the last enrolment in CS01 | Fine — the COURSE row survives. |
| Change the teacher-in-charge | Fine — exactly one row is updated. |
Fourth anomaly: spurious tuples
Splitting badly is also possible. If tables are decomposed along the wrong attributes, joining them back produces spurious tuples — rows that were never in the original data. Unit 5, Lesson 5 shows how to guarantee a lossless-join decomposition, which is the formal statement of "split correctly".
9.2 The Relational Model — Formal Definitions¶
Proposed by E. F. Codd in 1970 in "A Relational Model of Data for Large Shared Data Banks". Its power comes from being founded on set theory.
Domain¶
Domain
A domain D is a set of atomic values — indivisible with respect to the model. A domain has a name, a data type and a format.
| Domain name | Description | Data type / format |
|---|---|---|
Roll_numbers |
Roll numbers of students | VARCHAR(8), pattern letter + 4 digits |
Names |
Personal names | VARCHAR(40) |
Dates |
Calendar dates | DATE, YYYY-MM-DD |
Seat_counts |
Seats in a course | INT, 1 to 500 |
Course_types |
Type of course | {'Fulltime', 'Parttime'} |
Note that Course_types is an enumerated domain of exactly two values, and that giving a domain a name and a meaning is more than giving it a data type: Roll_numbers and Society_codes might both be VARCHAR(8), but comparing them is meaningless.
Relation schema¶
Relation schema
A relation schema R, written R(A1, A2, ..., An), consists of a relation name R and a list of attributes A1 ... An. Each attribute Ai is the name of a role played by some domain D in R; that domain is called the domain of Ai, written dom(Ai).
The degree (arity) of a relation is n, the number of attributes.
STUDENT ( Rollno, Name, Dateofbirth ) degree 3
dom(Rollno) = Roll_numbers
dom(Name) = Names
dom(Dateofbirth) = Dates
'Role played by a domain' — why the wording matters
Two different attributes may draw on the same domain while meaning different things. In FLIGHT(Origin, Destination) both attributes have the domain City_names, but Origin and Destination are different roles. The attribute name records the role; the domain records the permitted values.
Relation state (instance)¶
Relation state
A relation state (or relation instance, or simply relation) r of the schema R(A1, ..., An), written r(R), is a set of n-tuples:
r = { t1, t2, ..., tm }
Each tuple t is an ordered list of n values t = <v1, v2, ..., vn>, where each vi is an element of dom(Ai) or the special value NULL.
Formally: r(R) is a subset of the Cartesian product of the domains:
r(R) ⊆ dom(A1) × dom(A2) × ... × dom(An)
The number of tuples m is the cardinality of the relation.
Degree vs cardinality
Degree = number of attributes (columns) — a property of the schema, and fixed. Cardinality = number of tuples (rows) — a property of the state, and changing constantly.
The correspondence of terms¶
| Formal (relational theory) | Informal (tables) | Physical (files) |
|---|---|---|
| Relation | Table | File |
| Tuple | Row | Record |
| Attribute | Column | Field |
| Domain | Set of legal column values | Data type |
| Degree | Number of columns | Number of fields |
| Cardinality | Number of rows | Number of records |
| Relation schema | Table definition | File layout / header |
| Relation state | Table contents | File contents |
Worked example¶
Schema: COURSE(SID, Cname, TotalSeats, Coursetype), degree 4.
State r(COURSE):
| SID | Cname | TotalSeats | Coursetype |
|---|---|---|---|
| CS01 | Computer Science | 60 | Fulltime |
| CH01 | Chemistry | 45 | Fulltime |
| MA02 | Mathematics | 50 | Parttime |
Cardinality = 3.
As a set of tuples:
r(COURSE) = { <'CS01','Computer Science',60,'Fulltime'>,
<'CH01','Chemistry', 45,'Fulltime'>,
<'MA02','Mathematics', 50,'Parttime'> }
Tuple access. t[Cname] denotes the value of attribute Cname in tuple t. If t is the first tuple, t[Cname] = 'Computer Science' and t[SID, Coursetype] = <'CS01','Fulltime'>.
9.3 The Four Characteristics of Relations¶
These follow from the definition "a relation is a set of tuples", and each is examinable.
Characteristic 1 — Ordering of tuples is immaterial¶
A relation is a set, and a set has no order. These two are the same relation:
| SID | Cname |
|---|---|
| CS01 | Computer Science |
| CH01 | Chemistry |
| SID | Cname |
|---|---|
| CH01 | Chemistry |
| CS01 | Computer Science |
Consequence: you may never rely on the order in which a DBMS returns rows. If you want an order, you must say so with ORDER BY. Physically the DBMS is free to store tuples in any order it finds efficient — which is precisely the physical data independence of Unit 1.
Characteristic 2 — Ordering of values within a tuple is significant... in the basic definition¶
In the formal definition a tuple is an ordered list, so <'CS01','Computer Science'> differs from <'Computer Science','CS01'>.
However, an alternative definition treats a tuple as a set of (attribute, value) pairs:
t = { (SID,'CS01'), (Cname,'Computer Science'), (TotalSeats,60) }
Under this definition the order is immaterial, since each value carries its attribute name. This is the definition SQL follows in practice — which is why you may write columns in any order in a SELECT list, and why INSERT INTO COURSE (Cname, SID) VALUES (...) is legal.
How to answer this in an examination
State both: "In the basic definition a tuple is an ordered list, so order matters; under the alternative definition as a set of (attribute, value) pairs it does not, and this is what SQL implements."
Characteristic 3 — Values in a tuple are atomic¶
Every value must be indivisible with respect to the model. No composite and no multivalued attributes are permitted in a relation. This is the first normal form (1NF) assumption, and the model is therefore said to be in flat form.
NOT a valid relation: Valid:
+--------+---------------------+ +--------+----------+
| Rollno | Phones | | Rollno | Phone |
+--------+---------------------+ +--------+----------+
| X2019 | 98111, 98222 | -> | X2019 | 98111 |
| X2020 | 99333 | | X2019 | 98222 |
+--------+---------------------+ | X2020 | 99333 |
multivalued -- ILLEGAL +--------+----------+
This is why Unit 5's mapping rule sends every multivalued attribute to a separate table.
Characteristic 4 — No duplicate tuples¶
A set contains no duplicates, so no two tuples in a relation may be identical in all attributes. This is what guarantees that every relation has a key (in the worst case, all attributes together).
Where SQL departs from the theory
SQL tables are multisets (bags), not sets: a table without a primary key can hold duplicate rows, and SELECT returns duplicates unless you write DISTINCT.
This is a deliberate engineering compromise — eliminating duplicates requires a sort or hash on every query, which is expensive. The theory says set; the product says bag unless you ask. Be ready to state this difference; it is a common examination question, and it explains why SELECT Cname FROM COURSE may print the same name twice.
Interpretation of a relation¶
A relation schema can be read as a predicate, and each tuple as an assertion that the predicate is true of those values.
STUDENT(Rollno, Name, Dateofbirth) asserts: "There is a student with this roll number, this name and this date of birth." The tuple <'X2019','Ankit Sharma','2001-03-14'> is one such true statement. This is the closed world assumption: only facts stated in the database are true; anything absent is taken to be false.
9.4 Solved Questions¶
Solved Question 1
Q. What are update anomalies? Explain all three with the relation STUDENT_COURSE(Rollno, Sname, SID, Cname, TotalSeats, TeacherInCharge) and show how decomposition removes them.
A. Update anomalies are undesirable side effects that occur when inserting, deleting or modifying data in a relation that stores facts about more than one kind of thing.
Insertion anomaly. A new course Physics (40 seats, Dr Rao) has no students yet. Since Rollno is part of the primary key and cannot be NULL (entity integrity), the row cannot be inserted. A course cannot be recorded until a student enrols in it.
Deletion anomaly. If the last student enrolled in CS01 withdraws, the row is deleted — and with it the facts that CS01 is called Computer Science, has 60 seats and is taught by R. K. Gupta. Deleting an enrolment destroys a course.
Modification anomaly. The teacher-in-charge of CS01 changes. That is one real-world fact, but it is stored once per enrolled student. Updating some rows and not others leaves the relation asserting two different teachers for one course. One logical change requires many physical updates, and partial application causes inconsistency.
Root cause. The relation mixes facts about students, facts about courses and facts about admissions.
Cure — decompose into one relation per kind of thing:
STUDENT ( Rollno, Sname )
COURSE ( SID, Cname, TotalSeats, TeacherInCharge )
ADMISSION ( Rollno, SID )
Now a course exists independently of its students (no insertion anomaly), survives the departure of its last student (no deletion anomaly), and its teacher is stored exactly once (no modification anomaly). Unit 5 shows how to derive such a decomposition systematically, and how to verify that it is lossless.
Solved Question 2
Q. Define domain, attribute, tuple, relation schema, relation state, degree and cardinality. Illustrate with COURSE.
A.
- A domain is a set of atomic values with a name, data type and format — e.g.
Course_types = {'Fulltime','Parttime'}. - An attribute is the name of a role played by a domain in a relation —
Coursetype, whose domain isCourse_types. - A relation schema
R(A1,...,An)is a relation name together with a list of attributes —COURSE(SID, Cname, TotalSeats, Coursetype). - A tuple is an ordered list of
nvalues, one from each attribute's domain (or NULL) —<'CS01','Computer Science',60,'Fulltime'>. - A relation state
r(R)is a set of such tuples; formallyr(R) ⊆ dom(A1) × ... × dom(An). - The degree is the number of attributes — here 4. It is a property of the schema and is fixed.
- The cardinality is the number of tuples — 3 in the illustration. It is a property of the state and changes with every insert or delete.
| SID | Cname | TotalSeats | Coursetype |
|---|---|---|---|
| CS01 | Computer Science | 60 | Fulltime |
| CH01 | Chemistry | 45 | Fulltime |
| MA02 | Mathematics | 50 | Parttime |
Solved Question 3
Q. State and explain the four characteristics of relations. For each, state one practical consequence.
A. All four follow from "a relation is a set of tuples".
1. Ordering of tuples is immaterial. A set is unordered, so two relation states with the same tuples in different sequences are the same relation.
Consequence: never rely on the order rows come back in; use ORDER BY. It also frees the DBMS to store and retrieve tuples in whatever physical order is efficient — the basis of physical data independence.
2. Ordering of values within a tuple is significant in the basic definition, where a tuple is an ordered list. Under the alternative definition — a tuple as a set of (attribute, value) pairs — it is immaterial, because each value carries its attribute name.
Consequence: SQL follows the alternative definition, so columns may be listed in any order in SELECT and named explicitly in INSERT.
3. Values are atomic. Composite and multivalued attributes are forbidden; the relation is flat. This is the 1NF assumption. Consequence: a multivalued attribute from an ER diagram must be mapped to a separate table (Unit 5).
4. No duplicate tuples. A set has no repeated elements.
Consequence: every relation has a key — at worst the whole set of attributes. But SQL departs from this: SQL tables are multisets, so duplicates are allowed unless a key is declared, and SELECT returns duplicates unless DISTINCT is used. This is a performance compromise, since removing duplicates requires sorting or hashing.
Solved Question 4
Q. Distinguish relation schema from relation state, and degree from cardinality. Which changes when a row is inserted?
A.
| Relation schema | Relation state | |
|---|---|---|
| What it is | The definition: name + attributes + domains | The data: a set of tuples |
| Notation | R(A1,...,An) |
r(R) |
| Corresponds to | Intension | Extension |
| Changes | Rarely, by DDL | Constantly, by DML |
| Measured by | Degree = number of attributes | Cardinality = number of tuples |
When a row is inserted: the relation state changes and therefore the cardinality increases by one. The schema and the degree are unchanged.
When a column is added by ALTER TABLE: the schema changes and the degree increases by one. The cardinality is unchanged, though every existing tuple acquires a NULL in the new position.
Solved Question 5
Q. Which of the following are valid relations? Justify.
a. Table with columns (Rollno, Name) containing two identical rows ('X2019','Ankit').
b. Table with a column Phones holding '98111, 98222'.
c. Table with a column Address holding a composite of street, city and PIN.
d. Table whose rows are stored in no particular order.
e. Table with a NULL in a non-key column.
A.
a. Not a valid relation — in theory. A relation is a set, so duplicate tuples are impossible (Characteristic 4). However, a SQL table with no primary key permits this, because SQL tables are multisets. So the correct answer is: invalid as a relation, permitted as a SQL table — and this is exactly where SQL departs from the relational model.
b. Not a valid relation. '98111, 98222' is a multivalued attribute packed into one field, violating atomicity (Characteristic 3). The correct design puts phone numbers in a separate table STUDENT_PHONE(Rollno, Phone). Note that the DBMS cannot detect this violation — as far as SQL is concerned it is just a string — which is why it is a design error rather than a runtime error.
c. Not a valid relation, for the same reason, if the parts are meant to be accessed separately. The fix is to store Street, City and PIN as three separate atomic columns. (If the address is only ever used as an opaque whole, a single VARCHAR is acceptable and is atomic with respect to this application.)
d. Valid. Ordering of tuples is immaterial (Characteristic 1); a relation has no inherent order, so "no particular order" is exactly what is expected.
e. Valid. NULLs are explicitly permitted by the definition: each vi is an element of dom(Ai) or NULL. Only primary key attributes may not be NULL, by entity integrity (Lesson 2).
Solved Question 6
Q. Given dom(A) = {a, b}, dom(B) = {1, 2, 3}, how many tuples are in dom(A) × dom(B)? How many distinct relation states are possible for R(A, B)?
A. The Cartesian product contains |dom(A)| × |dom(B)| = 2 × 3 = **6** tuples:
{ <a,1>, <a,2>, <a,3>, <b,1>, <b,2>, <b,3> }
A relation state r(R) is any subset of this product. The number of subsets of a set of 6 elements is 2^6 = **64**.
So there are 64 possible relation states, ranging from the empty relation (0 tuples) to the full Cartesian product (6 tuples).
Generalisation. For R(A1,...,An), the number of possible tuples is ∏ |dom(Ai)|, and the number of possible states is 2 raised to that power. The figure grows explosively, which is the formal reason constraints are essential: they cut this enormous space of syntactically possible states down to the far smaller set of meaningful ones. Adding the key constraint "A is unique" to the example, for instance, immediately rules out any state containing both <a,1> and <a,2>.
9.5 Exercises¶
A. Short answer
- Name the three update anomalies.
- What is the single underlying cause of all update anomalies?
- Define domain, and give two domains with the same data type but different meanings.
- Distinguish degree from cardinality.
- State the four characteristics of relations.
- Why must values in a tuple be atomic?
- In what two ways do SQL tables depart from the formal definition of a relation?
B. Long answer
- Explain the three update anomalies with a worked example, and show how decomposition removes each.
- Give the formal definition of domain, relation schema, tuple and relation state. Explain the phrase "an attribute is the name of a role played by a domain".
- State and explain the four characteristics of relations, giving one practical consequence of each.
- Compare the formal relational model with SQL tables. Where do they differ, and why were those compromises made?
C. Application and analytical
- For
EMPLOYEE_PROJECT(Ssn, Ename, Pno, Pname, Plocation, Hours): a. Give a concrete instance with four rows showing redundancy. b. Demonstrate each of the three anomalies with a specific scenario. c. Decompose to remove them and show that each anomaly is gone. - For
SUPPLIER_PART(Sno, Sname, City, Pno, Pname, Qty), identify all redundancy and all three anomalies, then decompose. dom(A) = {x, y, z},dom(B) = {0, 1},dom(C) = {p, q}. Compute: a. The number of tuples indom(A) × dom(B) × dom(C). b. The number of possible relation states ofR(A,B,C). c. How many states remain ifAis declared a key?- State whether each is a valid relation, and justify:
a. A table with 0 rows.
b. A table with 0 columns.
c. A table where two rows agree on every column except one NULL.
d. A table whose
Skillscolumn holds'Java;Python'. - A designer argues: "Redundancy is fine — disk is cheap, and I will just be careful with updates." Write a one-paragraph rebuttal grounded in the three anomalies.
- Explain the closed world assumption. If
ADMISSIONcontains no row for (X2019, PH01), what does the database assert? What does it not assert?
D. Objective — True or False with reason
- A relation may contain duplicate tuples.
- The degree of a relation changes when rows are inserted.
dom(Origin)anddom(Destination)may be the same domain.- In SQL,
SELECT Cname FROM COURSEreturns a set. - Update anomalies are caused by having too many tables.
- A relation state is a subset of the Cartesian product of its domains.
- Ordering of tuples in a relation is significant.
Answers to Section D
- False in the relational model (a relation is a set); true for SQL tables without a primary key, which are multisets.
- False. Degree is the number of attributes, a property of the schema. Inserting rows changes the cardinality.
- True. Two attributes may play different roles over the same domain — both are city names, but one is the origin and one the destination.
- False. It returns a multiset; duplicates appear unless
DISTINCTis specified. - False. They are caused by too few tables — by storing facts about several kinds of thing in one relation. Decomposition (more tables) is the cure.
- True. That is the formal definition, allowing NULL as an additional permitted value.
- False. A relation is a set; tuple order is immaterial. Use
ORDER BYif you need a sequence.
Lesson Summary
- Insertion, deletion and modification anomalies all arise from one cause: a relation storing facts about more than one kind of thing. The cure is decomposition — formalised as normalisation in Unit 5.
- A relation state is a set of tuples, formally a subset of the Cartesian product of the attribute domains.
- Degree = number of attributes (schema, fixed); cardinality = number of tuples (state, changing).
- Four characteristics: tuple order immaterial, value order significant only in the basic definition, values atomic (1NF), no duplicate tuples.
- SQL departs from the model in two ways: tables are multisets, and duplicates are returned unless
DISTINCTis used.