Consider the following schema with two relations TABLE employee(eName,salary,wor
ID: 3720558 • Letter: C
Question
Consider the following schema with two relations TABLE employee(eName,salary,worksInCity, PRIMARY KEY (eName)) TABLE city(cityName, zipCode, state, PRIMARY KEY (cityName)) 1/ For practice, add any necessary referential integrity constraints 2/ Consider the view empHasZip(eName,zipCode) associating directly the zip code of the city where the employee works. a) Suppose that you are told that tuple ("calvin", 99999) should appear in the view empHasZip, and that 99999 appears in table hasLocation. Assuming that every zip code is for only one city, write a trigger that implements the most reasonable change to the base tables employee and/or city. b) What happens in part d) if a zip code, like 08904, is used in two cities (Highland Park and New Brunswick)?
Explanation / Answer
Hi Friend.
1. Lets add refrential integrity constraint- ename of table city refer ename of employee.
ALTER table CITY ADD Foreign Key(ename) REFRENCES EMPLOYEE(ename);
2.
CREATE VIEW empHasZip(eName,zipCode) AS (SELECT zipCode FROM hasLocation UNION SELECT ename FROM City)
b) where is part d?
Thumbs up!