1.) Will each of the following SQL statements be executed successfully? In other

Question

1.) Will each of the following SQL statements be executed successfully? In other words, do they violate any constraints for the given database state? Please list all violated constraints and justify your answer. Assuming S#, P#, and J# in SPJ table are foreign keys.

a.) Delete from P where P# = 'P1';

b.) Insert into SPJ values ('S1', 'P1', 'J1', 200);

C.) Update J set J#='J11' where J#='J1";

d.) Insert into SPJ values ('S1', 'P1', NULL, 200);

e.) Drop table S;

schema and a snapshot of SUPPLIER-PART PROJECT database is given below. Table S, P and J represent Supplier, Part, and Project, respectively. Table SPJ contains the shipment information such that a supplier shipped a quantity of a part to a project. S, P# and J# in table SPJ are foreign keys. 5. The Schema S | S# | Snamc | Status | City P | P# | Pname | Color | Weight | City Ja Citv Values: ,SPJ S# P# J# Qty. sl | PI | .11 | 200 700 3 100 2 200 3 J3200 S2 P3 500 3 600 52 P3 JG400 s# sname Status City SSmit S2 Joncs 20 aris Si 30 aris SAdams30 5 S2 Athens Pname Color Weight City P1 ut 12 0n atis reen Serew Serew CaBlue S2 P5 2 100 200 S3 P 200 tle ondon 53 12 l'aris S4 S4 0g 7300 J# Jname City orter 2Display Rom 14 0 S P00 S5 P57100 aris thens 2 200 P100 3200 S P 800 London JG 7 Tape EDS | Oslo ondon

Explanation / Answer

Answer)
Assuming S#, P#, and J# in SPJ table are foreign keys, we perform the following actions:

a.) Delete from P where P# = 'P1';

Here there will be an error as:
Cannot delete or update a parent row: a foreign key constraint fails.
This means that as P#=P1 is a foreign key in the SPJ table. Thus the entry cannot be deleted.

b.) Insert into SPJ values ('S1', 'P1', 'J1', 200);

Here there will be an error also, considering (S#,P#,J#) in SPJ is the composite primary key.
Such an entry already exists.

C.) Update J set J#='J11' where J#='J1";

Here there will be an error as:
Cannot delete or update a parent row: a foreign key constraint fails.
Here also we cannot update J1 to J11, as it is being referenced in SPJ table.

d.) Insert into SPJ values ('S1', 'P1', NULL, 200);

Will be a successful entry in the SPJ table, provided the table is configured to take in null. Such that there are no constraints regarding the entry.

e.) Drop table S;

While dropping S, we will get the error-
Cannot delete or update a parent row: a foreign key constraint fails.
As the entries in S are being referred by the entries in SPJ as foreign keys.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.