Question

Based on the current data in the tables below, there is violation of _______rule in the CUSTOMR table and AGENT table.

Table Name: CUSTOMER
Cus_code	Lname	Fname	Agent_code	Area_code		Phone		Zip_ Code	Date_Joined	Referring_Cus	Balance
10010	Ramas	Alfred	A139	615		844-2573		48600	20-JAN-2001		50.00
10011	Dunne	Leona	A145	412		413-1238		15213	21-JAN-2002	10010	–78.09
10012	Smith	Kathy	A160	615		894-2285		48600	20-FEB-2002	10010	0.00
10013	Olowski	Paul	A139	615		844-2573		48600	26-FEB-2003		536.75
10014	O'Brian	Amy		412		442-3381		15213	11-APR-2004	10012	0.00
10015	Brown	James	A183	615		297-1228		48600	21-JUN-2006	10014	200.00
10016	Brown	George	A160	615		297-1228		48600	20-AUG-2009	10014	76.93
10017	Singh	Olette		412		382-7185		15213	26-SEP-2012

Table Name: AGENT
Agent_code	Lname	Fname	Area_code	Phone	Zip_Code
A139	Bowser	Alfred	622	845-2573	43201
A145	Johnson	Tony	210	248-1438	78249
A155	James	Thong	210	545-2450	78249
A160	Smith	Kathy	622	894-2285	43201
A178	Gray	Peter	412	452-4573	15213
A182	Woodz	Carrie	412	411-1234	15213

	A.	zero redundancy
	B.	duplicate attributes
	C.	entity integrity
	D.	referential integrity

Answer 1

ITs d) refrential integrity

Analyzing the data its clear that Agent_Code in Customer table is refrencing the Agent_Code in Agent table. So Agent_Code column in customer table should have only those values which are part of values in Agent_Code column of Agent table.

i.e. Values in Agent_Code(Customer) should be subset of values in Agent_Code(Agent)

But there in one value A183 in Agent_Code column of Customer table which is not present in Agent_Code column of Agent Table.

So this is a foreign key violation of refrential integrity violation

Hence d) is correct