QuestionQuestion

Transcribed TextTranscribed Text

Question 1 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 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 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 ina specific project. twin (twinID, twinName, gender) project (projID, projDescrip, startDate, no_ of _twins) researcher (staffID, staffName) twinProject(twinID projID) researcherProj (staffID, projID, role) a) 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) b) A pool of cars is available for researchers to use for visits to twins, and other trips related to research project. For each car in the pool, the registration number and type of car needs to be recorded. Every time car is issued to researcher, record needs to be kept of the issue date, the return date and the mileage used by the researcher. There should also be some way of identifying which project the journey was for. Amend the ER diagram provided to show the additional entities attributes and relationships required to accommodate the above requirements. (10 marks) c) The pool cars are all serviced regularly. A car can be serviced at anumber of garages. The manager would like to add further table to the database to record details of where each car is serviced and when it was serviced. The structure of the table he has proposed is shown below, with some sample data inserted: carService(carRegNo, serviceDate, make, garageID, garage name, garage address) carReg Service make garagelD garage_name garage address Date AAA 12 1-DEC-16 Golf G01 VW Motors Brixton BBB 456 16-AUG-16 Mercedes G22 Prime Motors Ealing CCC E 567 18-AUG-16 Passat G01 VW Motors Brixton DDD 888 2-MAY-16 Seat G55 Seat Motors Kew AAA 123 JAN- 17 Golf G22 Prime Motors Ealing Normalise the table to Third Normal Form (3NF), showing the interim stages (First and Second Normal Forms) (10 marks) Question a. Write the SQL statement(s) required to create the 'twinProject' table shown on the ER diagram, and listed below: twinProject(twinID, proilD) You may make any reasonable assumptions about the data types required. Include relevant primary and foreign key constraints. (7 marks) b. Write the SQL statement required to insert the following record into the "twinProject' table: twin ID: 1234 projectID: 1000 (2 marks) c. Write the SQL statement required to delete the above record from the 'orders' table. (4 marks) d. The start date for project 1000 needs to be changed to 10 May- 2017. Write the SQL statement required to do this. (4 marks) e. 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. Question Refer to the case study ER diagram and tables. Write the SQL statements required for the following queries: a. List the project descriptions for projects with more than 40 twins. (4) b. List the twinID of all twins who are part of any project starting before 1st January 2017. (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) d. List the name of any twins who have not yet been involved in any projects. (5) e. The hospital is to get funding from the National Health Service for projects with 'obesity' in their description. The funding £20 for each twin who has been registered on such projects. Produce query that will show the funding that can be claimed for EACH obesity project. Show the ID of the project and the funding claimed for that project. In addition, include row which displays the TOTAL funding being claimed (ie total for ALL projects with 'obesity in their description). (6) f. The hospital has decided to create 'budget' table which will keep track of the budget left on each project. The structure of the table is shown below. 'budgetRemaining' refers to the amount that is still remaining for given project. budget (projectID, budgetRemaining) Every time twin is added to project, £20 needs to be deducted from the budget. This can be achieved using trigger. Provide the code required for trigger in order to ensure the 'budgetRemaining attribute reflects any new twins being added to project (ie on the basis of each new twin using up £20 of the available budget). (5) Pinkton Hospital tables: ER diagram: Twin TwinProject Project twinlD twinID projID TwinName proilD ProjDescrip Gender startDate no_of_twins Researcher ResearcherProj staffID staffID staffName projID role Sample Data: Twin twinProject twinID twinName gender twinID proild 9999 Bloggs, M 9999 1000 9988 Evans. P F 9977 1000 9977 Black, M 9988 1100 Project proilD projDescrip startDate No twins 1000 OBESITY TWINS 01-FEB-16 3 1100 INSOMNIA STUDY 10-APR-17 45 1103 GENETIC ANOMALIES 15-MAR- 16 15 Researcher ResearcherProj staffID staffName staffID projID role 9999 Bloggs, 9999 1000 Team Leader 9988 Evans. p 9988 1000 Team Member 9977 Black. 9988 1103 Team Leader

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

1. Question #1
a. Task a:
i. Purpose of primary keys and foreign keys
1. Foreign keys are a type of reference attribute used to represent relationships
2. Primary key constraint uniquely identifies each record in a database table
ii. All found primary keys
1. twin
a. twinID
2. project
a. projID
3. researcher
a. staffID
4. twinProject
a. twinID
b. projID
5. reseacherproj
a. staffID
b. projID
iii. All found foreign keys
1. twinProject
a. twinID
b. projID
2. reseacherproj
a. staffID
b. projID...

By purchasing this solution you'll be able to access the following files:
Solution.docx.

$69.00
for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats