Question 2 - Relational Database Constraints Database Schema Suppose that the fo
ID: 3756971 • Letter: Q
Question
Question 2 - Relational Database Constraints
Database Schema
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:
atabase Instance ustomers custID custFirstName custLasttNane l address city I Charlotte l Concard state custEnail custPhone 80001 Roger 88082 I Chris 88093 l Joe 880B4 I John 8085 Ari 98086 I Barbara 88087 I Ton 88088 I George 80389 Dan 80019 David 89011 I Ion 88012 I Jerry 88013 Roy 88614 i Christy 88015 l Jessica Brown Austins l 432 Rice Street l 1233 Ridge DriveICharlotte 12 Hain Street 6208 Ryder Ave 3321 Wannamaker Lane New York City NY 2122 Tryon Street ICharlotte I NC rogerCaol.con 704-544-3219 I I NC chrisaol.con 704-967-8845I 1 NC JOeBaol.con | 980-344-5654 | i NC johnCaol.com7B4-854-4431 I Yeri Shepherd Durhan 212-866-9812 784-988-4322 I 704-655-7410 ariCaol.con 1 Paterek Golen I NCI barbaralaol.con INCtonCaol.con l 4321 West Main Street Durhan Elio 544 Maker Lane I Colorado Springs l Co I george@aol.com 1 719-556-7410 anlaol.con784-577-8922 Gibby Boatsnan Bank iNCd I NC davidCaol.con 784-522-1075 I NC1ton.bankCaol.con 980-877-2301 I I NCl jerrylaol.con 784-585-4432 H 43 Austria Lane 22 Master Street l2234 Reagan Ave 9008 MainStreet Concard Charlotte Greensboro I Seinfeld I Charlotte l New York City I NY 83-433-8122 Irennenan 43 Jay Ave royCaol.con i Taskaly l Iaylor l 1222 Corn Street Durhan NC christylaol.con ! 7B4-822-3812 I NC jessicalaol.con 784-988-4923 I 2333 Round CircleDurhanExplanation / Answer
1. The first update doesnot violate any integrity constraint, assuming custID is the primary key of the relation. Since the value 16 doesnot exist previously, there is no issue.
2. The second update operation violates foreign key constraint because the custID '059' does not exist in the Customers table. To enforce the constraint use 'ON UPDATE SET NULL'.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.