And list all the primary keys found the above tables

The Pinkton Hospital carries out research on twins. The hospital’s database contains a number of tables among them the following, which store data relating to patients of the hospital who are members of a twin set, the projects being run by researchers in the hospital, and data on the researchers themselves. Twins can take part in numerous projects (or none at all), and projects are always run by a team of researchers. Researchers are keen to involve themselves in as many projects as possible to enhance their status. The twinProject table records the twinIDs of all twins who are taking part in a specific project.

twin (twinID, twinName, gender)

1. Explain clearly the purpose of primary keys and foreign keys, and list a) all the primary keys found in the above tables, and b) all the foreign keys in the above tables. (10 marks)

2. A pool of cars is available for researchers to use for visits to twins, and other trips related to a research project. For each car in the pool, the registration number and type of car needs to be recorded.

Normalise the table to Third Normal Form (3NF), showing the interim stages (First and Second Normal Forms) (10 marks)

Question 2

2. Write the SQL statement required to insert the following record into the ‘twinProject’ table:

twin ID: 1234

(4 marks)

5. Another column, ‘dateRegistered’ needs to be added to the twinProject table to show the date when the twin was registered onto a project. Provide the SQL required to make this change to the twinProject table. (3 marks)

(5)

c. List the total number of projects that each researcher has worked on but only for researchers who have worked on at least 3 projects. (5)

In addition, include a row which displays the TOTAL funding being claimed (ie a total for ALL projects with ‘obesity’ in their description).

(6)

Question 4

Please provide relevant references in Harvard Referencing style.

Sample Data:

Twin twinProject