The table below lists sample Advisor/Student data, where Adv# and Stu# form a co
ID: 3719431 • Letter: T
Question
The table below lists sample Advisor/Student data, where Adv# and Stu# form a composite primary key. (6 pts.)
Adv #
Stu #
AdvName
AdvOffice
StuName
StuMajor
S101
P100
T. Jones
10-110A
G. White
CIS
S101
P105
T. Jones
10-110A
J. Bell
MATH
A264
P108
M. Thomas
10-110B
I. McKay
ENG
A264
P110
M. Thomas
10-110B
P. Wax
CIS
V555
P105
R. Brown
10-111A
J. Bell
MATH
V555
P110
R. Brown
10-111A
P. Wax
CIS
Give examples of insertion, deletion and update anomalies with the above table.
(No SQL statements, just an example/description of each anomaly).
Adv #
Stu #
AdvName
AdvOffice
StuName
StuMajor
S101
P100
T. Jones
10-110A
G. White
CIS
S101
P105
T. Jones
10-110A
J. Bell
MATH
A264
P108
M. Thomas
10-110B
I. McKay
ENG
A264
P110
M. Thomas
10-110B
P. Wax
CIS
V555
P105
R. Brown
10-111A
J. Bell
MATH
V555
P110
R. Brown
10-111A
P. Wax
CIS
Explanation / Answer
Insertion anomaly:
Suppose a new student joins and he is not yet assingned to an advisor, then we cannot able to insert a record unless we have an advisor#. This kind of anomalies are known as insertion anomalies
Deletion anomaly:
Suppose the advisor with # S101 left the institute, then we delete those records under S101, then we cannot able to locate the student with # P100, because that student is no were located in DB. These kind of anomalies are known as deletion anomalies.
Update anomaly:
If we want to update advisor's AdvName, say if u want to change advisor# S101 AdvName, we need to change every record that comes under S101. If somehow some row's AdvName data is not updated, then it will cause an anomaly. These kind of anomalies are known as update anomaly.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.