A duplicate record is a second row that describes the same real item as an existing row. It matters because databases count rows, so every duplicate is counted as if it were a separate person, product or order. Counts, totals and averages then describe something that does not exist.
Practical tasks ask you to find a duplicate, remove it and say why it mattered. This lesson belongs to database structure and validation, and it uses the same invented club data as the earlier lessons.
How do you find a duplicate?
Use the key and the other fields together.
- Sort by a descriptive field such as Name, so near-identical rows sit next to each other.
- Compare the other fields. Same name, same join date and same fee is a strong sign of one person entered twice.
- Check the key. A different ID does not make a different person. The row may have been typed again under a new ID.
- Decide which row to keep. Keep the original, usually the earlier one, and delete the extra. Check with the source before deleting if unsure.
Two people can share a name, so a matching name alone is not proof. Compare more than one field.
Worked example
The invented cycling club has this table after a careless second import:
| MemberID | Name | DateJoined | Fee |
|---|---|---|---|
| M001 | Aiman Rahim | 2024-03-15 | 45.50 |
| M002 | Mei Ling Tan | 2024-04-02 | 45.50 |
| M003 | Kumar Raj | 2024-05-20 | 30.00 |
| M004 | Sofia Lim | 2024-06-11 | 45.50 |
| M005 | Daniel Ong | 2024-07-01 | 30.00 |
| M006 | Kumar Raj | 2024-05-20 | 30.00 |
Step 1, sort by Name. Both Kumar Raj rows sit together.
Step 2, compare. Name, DateJoined and Fee are identical. Only MemberID differs (M003 and M006). This is one person entered twice.
Step 3, measure the damage. The table shows 6 records, but there are only 5 real members. The fee total with the duplicate is 45.50 + 45.50 + 30.00 + 45.50 + 30.00 + 30.00 = 226.50. The true total is 196.50.
Step 4, check the arithmetic a second way. Three fees of 45.50 give 136.50, and three fees of 30.00 give 90.00. 136.50 + 90.00 = 226.50. Remove one 30.00 and the total is 196.50.
Step 5, check the average fee. With the duplicate: 226.50 divided by 6 = 37.75. Without it: 196.50 divided by 5 = 39.30. The duplicate lowers the average by 1.55, since 39.30 - 37.75 = 1.55.
Step 6, fix it. Delete the row M006 and keep M003. Then check the count shows 5.
What to write in an exam: The duplicate makes the club look like it has 6 members instead of 5 and overstates the fee income by 30.00. Reports built on the table would be inaccurate.
The mistake to watch for
A common slip is to think the primary key protects the table from duplicates.
Mistaken belief: The table has a primary key on MemberID, so duplicate records cannot exist.
The key only blocks two rows with the same MemberID. M003 and M006 are different values, so both were accepted although they describe one person.
The correction is to say what a key does and does not do. It prevents repeated key values. It does not know that two different IDs belong to one real person.
Preventing that needs checks at data entry, comparing against existing records, and regular review.
Check yourself
1. A table of 40 records contains 4 duplicates. How many real items are there?
Show answer
36. 40 - 4 = 36 real items, since each duplicate is an extra row of an item already listed.
2. Each record has a fee of 25.00. With the 4 duplicates still in a table of 40 records, what total is shown, and what is the true total?
Show answer
Shown: 40 × 25.00 = 1000.00. True: 36 × 25.00 = 900.00. The duplicates overstate it by 100.00.
3. Two rows have the same Name but different dates of birth. Are they definitely duplicates?
Show answer
No. Two different people can share a name. Different dates of birth suggest they are separate people. Compare more fields before deleting anything.
Where this leads next
Now try the whole module in the database structure and validation practice set. If duplicates caused a surprising total, detecting an incorrect numeric-text interpretation shows another cause of unexpected totals. The ICT practical task and evidence checker can help you list what to verify before you finish a database task.
Students often find duplicates easily and lose marks on the explanation. A teacher in online one-to-one ICT tuition can practise short, exact answers with you using fresh tables.