ISTM 4121 Database Anomalies Fall 2016

This document provides a solution to a database assignment, likely from ISTM 4121, focusing on database anomalies. It includes two example databases, one related to student courses and the other to customer sales. For each database, the solution identifies the primary key and explains insertion, deletion, and modification anomalies. The student course database uses a composite key of course ID and student ID, while the customer database uses CustomerNum as the primary key. The solution details how these database designs can lead to issues when adding, removing, or changing data, highlighting the importance of proper database normalization.

ISTM 4121 – Fall 2016
Name:
Ans:
NAME GPA MAJOR ID ADDRESS COURSE-ID C-DESC GRADE CREDIT
Lincoln
Plato
Plato
Jones
Jones
Jones
Jones
Dunn
William
William
William
Brown
Brown
3.6
3.4
3.4
3.0
3.0
3.0
3.0
3.2
3.1
3.1
3.1
3.5
3.5
Poly-sci
Philo
Philo
Eng
Eng
Eng
Eng
Eng
Phys
Phys
Phys
Chem
Chem
4444
1234
1234
7890
7890
7890
7890
3333
1111
1111
1111
2233
2233
33Main
11 1st
11 1st
22 2nd
22 2nd
22 2nd
22 2nd
33 3rd
44 4th
44 4th
44 4th
1 Broad
1 Broad
SA101
SA101
BA202
SA101
BA202
DP209
AC101
DP209
SA101
AC101
BA202
DP209
AC101
Sandbox 1
Sandbox 1
Bskweav 2
Sandbox 1
Bskweav 2
Database
Acctng
Database
Sandbox 1
Acctng
Bskweav 2
Database
Acctng
A
A
C
D
E
A
B
B
F
A
D
C
A
3
3
2
3
2
3
3
3
3
3
2
3
3
Primary Key: here primary key is a composite key that is: (courseID and ID)
Insertion:
If want to add new course with new course id and its description then currently according to this
database we are unable to add new course details. We have to add at least one student details to
add new course details.
Deletion:
Suppose the batch of students are done with the course and know we need to delete the record of
students from the database but we also want to keep course details because they are still running
in university. This type of database design create a problem because we have to delete unwanted
data too.
There may be one more reason for deletion anomaly and that is supposed we want to keep data
for all student but we want to delete that course details from the database that is not supposed to

run anymore by the university. This also creates a problem where we have to delete unwanted
data too.
Modification:
Suppose the student wants to change his/her address. This requires that all tuples corresponding
to that student name of the table are modified by given new address. If we fail to update all the
tuples for that student then two different records for that student may store and this leads
inconsistent database.

Customer
Num
Customer
Name
Balance Credit
Limit
Rep
Num
Last
Name
First
Name
Commission
%
148 Al’s
Appliance and
Sport
$6,550.00 $7,500.00 20 Kaiser Valerie 8
282 Brooking
Direct
$431.50 $10,000 35 Hull Joe 4
356 Ferguson’s $5,785.00 $7,500.00 65 Perez Juan 5
408 The
Everything
Shop
$5,285.25 $5,000.00 35 Hull Joe 4
462 Bargains
Galore
$3,412.00 $10,000.0
0
65 Perez Juan 5
524 Kline’s $12,762.0
0
$15,000.0
0
20 Kaiser Valerie 8
608 Johnson’s
Department
Store
$2,106.00 $10,000.0
0
65 Perez Juan 5
687 Lee’s Sport
and Appliance
$2,851.00 $5,000.00 35 Hull Joe 4
725 Deerfield’s
Four Seasons
$248.00 $7,500.00 35 Hull Joe 4
842 All Season $8,221.00 $7,500.00 20 Kaiser Valerie 8
Primary Key: CustomerNum
Insertion:
Suppose if need to add new customer category then we need at least one customer details that
must has to come under this category then we able to store that category in table. This creates
problems.
Deletion:
Suppose we want to remove a customer details but there is a category where only one customer
exits and we want to delete this customer. This leads a problem if we delete this customer details
from the database then an unwanted customer category type will also remove from the database.

Modification:
There is no modification anomaly.