Database question

Answer fully for thumbs up:

Question 1 The country of Dataria is divided into constituencies. Each constituency has a single member of parliament who represents all the people who live in the constituency. There is a rule that a person is only allowed to represent a constituency if they have, at some time, lived in a town which is in that constituency. Every town is in only one constituency The following Entity-Relationship diagram (ER diagram) has been proposed as a model for an information system for the above scenario. It shows three entity types, each having a single attribute, and three relationship types. haslivedt in erson ame oww tname onstitue isin can-rpresentcname (a) Redraw the diagram in your answer book and add to it appropriate participation con- straints on all the relationship types. Explain which of the statements in the scenario you have used to decide what participation constraints to add, and specify any as sumptions you have made (b) What would you add to the diagram to model the additional requirement that the length of time someone has lived in a town should also be stored in the information system? (c) Two schemas are proposed for the database, assuming the addition of time as in (b) Two tables. One, called lived, has attributes pname, tname, time. The other, called location, has attributes tname, cname. Three tables. Two are lived and location as before, the third, called representation, has attributes pname, cname. The person proposing the three table schema said that it was needed to record all three relationship types shown in the ER diagram. The proposer of the first schema said "you don't need three because the third is just a view". Explain what was meant by this and make clear why in fact two tables is a better solution. (d) Assume now that the database has only the two tables lived and location (i) What SQL statements would you use to create the tables including specifying the (ii) Write an SQL query to find for each person the town in which they have lived the (iii) Write an SQL query to find for each constituency all the people who have lived in primary keys? longest a town in the constituency for more than 10 years