Suppose that the following Update operations is applied directly to the above da

Question

Suppose that the following Update operations is applied directly to the above database state. Discuss all integrity constraints violated by each operation. (If any) and the different ways of enforcing these constraints.

Insert < '00016', 'Jessica', 'Taylor', '2365 Newcastle Rd.', 'Be llaire ', 'TX', 'jessica123@aol.com', '943-554-7415'> into Customers.

Answer:

Insert < '0032298234', '059',12,405.60> into OrderItem.

Answer:

Question 2 (20 points) Relational Database Constraints Database Schema: TABLE Customers( TABLE Orders custID CHAR(5), custFirstName VARCHAR(20), custLasttName VARCHAR(20) address VARCHAR(40), city VARCHAR(20), state CHAR(2), custEmail vArCHAR(30) custPhone CHAR(12), PRIMARY KEY(custID)); orderID CHAR(10), ordDate DATE, custID CHAR(5) ordAmt FLOAT(10,2), PRIMARY KEY (orderID) FOREIGN KEY(custID) REFERENCES Customers(custID)) TABLE orderItem( orderlD CHAR(10) itemID CHAR(3) qty SMALLINT unitPrice FLOAT(8,2), PRIMARY KEY(orderID, itemID), FOREIGN KEY(orderlD) REFERENCES Orders(orderID), FOREIGN KEY(itemID) REFERENCES Items (itemID)) TABLE Items( itemID CHAR(3), itemName VARCHAR(30), qtylnStock SMALLINT, listedPrice FLOAT(8,2), PRIMARY KEY(itemID)); TABLE Suppliers( TABLE Supplies( supID CHAR(4) supName VARCHAR(20), city VARCHAR(20), state CHAR(2) phone CHAR(12) ContactPerson VARCHAR(40), PRIMARY KEY(supID); supID CHAR(4), itemID CHAR(3), unitCost FLOAT(7,2), PRIMARY KEY(supID,itemID), FOREIGN KEY(supID) REFERENCES Suppliers(supID) FOREIGN KEY(itemID) REFERENCES Items(itemID);

Explanation / Answer

Answer)

All of the below are Insert operations and not Update operations.

Insert < '00016', 'Jessica', 'Taylor', '2365 Newcastle Rd.', 'Be llaire ', 'TX', 'jessica123@aol.com', '943-554-7415'> into Customers.

Answer:

In Customers table Primary key is custID, thus if it is repeated upon inserting the record into the table, then the primary key constraint will be violated.

Here the insert statement has -

custID as 000016, which is not present in the table Customers and thus there will be no primary key violation.

Insert < '0032298234', '059',12,405.60> into OrderItem.

Answer:

OrderItem has the primary key as - orderID, itemID.

orderID and itemID are foreign keys to Orders and Items table respectively.

OrderItem consists of columns: orderID, itemID, qty, unitPrice.

orderID = '0032298234', itemID = '059'

orderID = 0032298234 exists with itemID = 062

Thus entry of orderID = '0032298234', itemID = '059' will be unique and primary key contraint will not be violated.

orderID = 0032298234 exists in Orders table and itemID = '059' exists in Items.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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