Homework Assignment: Constraints

Base your responses on these relations:

LOCATION $($Zip$\_$Code, City, State$)$

CUSTOMER$($Id$,$ Name, Zip$\_$Code$)$

ORDER$($CustId$,$ Date, Item, Price$)$

Exercises

Express in relational algebra the foreign key constraint: CUSTOMER.Zip$\_$Code references LOCATION.Zip$\_$Code

Express in relational algebra the constraint that Zip$\_$Code is a key of the LOCATION relation

Define an additional constraint that might apply to the LOCATION, CUSTOMER, or ORDER relations $($individually$,$ or in some combination$).$ Include the following in your response:

$($a$)$ Describe your constraint as an English$-$language statement

$($b$)$ Construct a relational algebra formula that represents your constraint.

$($c$)$ Provide relation instances $($sample LOCATION, CUSTOMER, and$/$or ORDER tables$)$ that demonstrate a violation of your constraint.

Given the relational algebra constraint:

${}_{Zi{p}_C\text{o}\text{d}\text{e}}(CUSTOMER)={}_{Zi{p}_C\text{o}\text{d}\text{e}}(LOCATION)$

$($a$)$ Translate this constraint into an English$-$language statement.

$($b$)$ Does this constraint differ in any way from the foreign key constraint you defined in question $1?$ If so$,$ briefly describe a scenario that would meet the constraint from question $1,$ but would not meet this new constraint.