Pinkton Psychiatric Hospital Twin Register – Group Work Assignment

You will be put into a group of 4 students and allocated a case study for which your group needs to produce a database design. Note that this is a group assignment. Individual submissions will not be accepted.

•A: a single Report containing the following (Note that each member of the group must upload their group’s report through the Blackboard submission link to get a mark for this element). Late reports will incur the usual penalties (40% cap, or 0% if later than 1 week)

1. User requirements: Identify the main stakeholders in the scenario and 12 major functional requirements that your database system must be able to meet. These should be outputs linked to your stakeholders.  As the case study contains only an outline of a business scenario, you will also need to state any business rules, assumptions, etc, that your group has made in the course of putting together the database design. These need to be submitted with your design.

2. An ER diagram covering the data requirements of the scenario, together with a completed entity specification form for each entity shown on your ER diagram. The entity specification template can be found on Blackboard.

The group assignment will be run as an Agile ‘scrum’ project, as explained by your tutor, with ‘daily stand up’ and sprint retrospectives conducted each week at the start of your seminar session. Note that from week 6 onwards time will be set aside at the start of the seminar sessions for formal ‘daily stand up’ group meetings (with attendance being recorded). In addition, your group will need to schedule regular meetings outside of the class, which should also be minuted. All group meeting minutes/reviews, group member interactions, and sprint backlogs must be uploaded to your Blackboard Group facility on a weekly basis.

4. Peer assessment form – completed separately by each group member. The peer assessment should be based on evidence available on Blackboard.
 
At your first group meeting you should establish the criteria that your group is going to use for the peer assessment exercise.  This should be recorded in your Blackboard Group facility during your first meeting.

Group work is successful when group members are communicating effectively. Blackboard ‘Group’ facilities (blog, file exchange, email etc) have been set up to support and document group communication and interaction.
    
Assignment marks for each group member may be moderated by your tutor based on evidence (or lack of evidence) provided on Blackboard (ie individual member interactions, sprint backlog records, meeting minutes etc), and peer assessment forms; in the most extreme cases, this may result in a fail grade for this element of the assignment.

Assignment Element 1

Your group is required to implement the design produced in Element 2 (if necessary, amended appropriately following feedback provided by your tutor).   This must be implemented using Oracle SQL Developer, and a Notepad file containing your SQL scripts produced to allow your tutor to set up your complete implementation. Your group will also be required to prepare a powerpoint presentation which will be delivered in week 15/16 (exact date/time TBC).

1. Your group’s final ER diagram clearly showing all attributes, Primary and Foreign Keys, as implemented in your database.

a. SQL Table creation and constraint scripts (in the order in which they need to be created).

b. SQL Insert statements for the sample data you have inserted into your database. The data should fully test the functionality of the system, as illustrated by your SQL queries/outputs (below).

c.  SQL view creation script for two views that would benefit this application (the script should be annotated to explain the rationale for this view – ie who would use the view, what does it show/do?).

d. SQL scripts for 8 SQL queries/reports (ie 2 per group member) that meet some significant user requirements (as outlined in assignment Element 2) and demonstrate the functionality of the system. Each query script should be annotated to explain the rationale for the query (what it shows, who would use it and why). Your choice of queries should also illustrate high technical knowledge.

3. Evidence of group activity – ie weekly group member interaction, sprint backlogs, meeting minutes, etc uploaded to your Blackboard group.

4.A completed Peer Assessment Form backed up by evidence found in your Blackboard group area.

Assignment marks for each group member may be moderated by your tutor based on evidence (or lack of evidence) provided on Blackboard (ie individual member interactions, sprint backlog records, meeting minutes), and peer assessment forms; in the most extreme cases, this may result in a fail grade for this element of the assignment.

5. Database Demonstration – Week 15/16 in normal timetabled sessions. Powerpoint slide presentation is to be uploaded the day before your allotted presentation time.

a)Measures you have proposed to secure your database;

b)Proposals for additional information systems or databases that could aid the organisation now or in the future; All group members must be present at the presentation and are expected to be able to answer questions on any aspect of the work presented. Any group member who is absent will receive a mark of zero for Element 3 of this assignment, unless documented mitigating circumstances apply.

Assignment Element 2

The Pinkton Psychiatric Hospital’s Research department currently maintains an internationally renowned database of twins known as the ‘Pinkton Twin Register’ (PTR). The register was set up in 1930 in order to collect data on twins for the purpose of research, and now contains data on more than 10,000 pairs of twins. Records on all twins have been maintained and updated throughout the years and sample populations drawn from the PTR for specific research studies, of which there may be between 50 and 100 on-going at any one time, ranging from schizophrenia and anxiety neurosis to obesity and melanoma research.

When the Register was started it was set up in a hard cover book which recorded, chronologically, the name, address and diagnoses of all patients of the Pinkton Hospital who were born one of a twin and whose co-twin (other twin) was of the same sex as the patient. Later this was expanded to include twins whose co-twin was of the opposite sex and these lists were kept separately, so that the PTR was actually two separate registers.

Later still, the register collected twin details from a number of neighbouring hospitals.  Over the years increased interest in genetic studies led to a renewed interest in the PTR and the setting up of a spreadsheet containing basic details of twins, together with centralised paper files for each registered twin.

These files contain information on present and past illness, the twins’ hospital visits etc, and also the tests and research studies in which the twins have been involved.  The records vary from a few pages of case notes to volumes totalling over 100 pages. This information needs to be available in a more user friendly way. In particular, the record of the twin’s diagnosis and illness history – when and where they were treated, and what for - needs to be digitised so that it is more easily accessible as it is used to pick sample populations for projects.

PPH’s Research department runs a number of twin research projects. Each is assigned to a team of researchers, headed by a lead researcher. Each project uses a sample of twins who are picked on the basis of a diagnosed illness and twin type. There are three possible categories of twin type – same sex identical twins, same sex non-identical twins and differing sex twins. There are also some triplets on the register.

Information Systems and Databases – Group Work Assignment Scenario

As soon as a sample is established for a project, researchers need to track down the relevant twins, who may have moved since they were in hospital. This often involves a great deal of time and duplication of effort, as researchers on other projects may have previously found the patients, but failed to record the new address in the file.

There are also suspicions that some researchers deliberately withhold information that they have spent weeks, or sometimes months, tracking down. This has been a great source of frustration for the research manager who believes that this has slowed down progress on a number of projects. He needs to be able to provide regular detailed updates on the progress of all projects to the Hospital Research Director.

The projects are divided up into a number of documented stages, each with their estimated completion dates so that the Research Director is easily able to spot if a project is slipping or running ahead of schedule.

1) A record of basic contact information relating to each sick twin and his/her co-twin. This will enable speedy and flexible retrieval of twins based on given criteria. (eg age, sex, contact details, place of birth, nationality, blood type, illness, age and nationality of mother at date of birth, age and nationality of father at date of birth); The database also needs to keep a record of all previous addresses for the twin, in order to monitor twin migration/movements.

2) Records of all illness and date of diagnosis for each twin along with additional information (eg whether hospitalised – if so, which hospital and for how long etc), so that there is an easily accessible health history for each twin to help researchers to locate suitable twins for their research projects and to learn the background health history from the records.

3) A record of which twins were involved in which research projects, and the result of any standard tests carried out on twins as part of that research project. A project will involve any number of tests administered to individual twin members; a twin may re-take a test in a new project. The system should be able to provide a complete ‘testing record’ for each patient, showing when they were tested, which test they took, for which project, what the results were, etc. The system should also be able to generate various reports based on the tests.

Assignment Scenario - Pinkton Psychiatric Hospital (PPH)

4) The twin record should show the number of research studies that he/she has participated in so that researchers can avoid aggravating twins by approaching them repeatedly to ask if they would mind taking part in a new project. There should also be some indication of whether a twin wishes to participate in future projects – sometimes twins will ask not to be contacted at all, or they might say they don’t want to take part now, but are happy to be contacted in 5 years time.

5) Each project has a budget agreed at the start. This consists of a ‘manpower budget’ which represents the amount of time (in hours) allotted to carry out the tests deemed to be necessary for the project, and a ‘miscellaneous budget’ (in £s) which covers just about any other expense incurred on the project (eg travelling expenses, hotel costs, consultancy etc). Each standard test is allotted a set amount of time to complete (ranging from 0.5 to 6 hrs).  

The time is fixed and is the same for whichever project the test is run on. The hospital would like the system to allow lead researchers and their managers to see at a glance the manpower budget remaining on any given project. The same goes for miscellaneous expenses. All miscellaneous expenses should be recorded in an appropriate way, so that a report can potentially be run showing who is incurring the highest expenses, and what for.

This area of operations has not been controlled very well in the past and various researchers have lodged complaints of ‘favouritism’ – saying that certain colleagues have been allowed to attend conferences in exotic locations, whereas they have been restricted to conferences and events in the UK.

6) Easily accessible information on the publications relating to projects: With health department finances being squeezed and increasingly directed to the most successful institutions, Pinkton needs to ensure its research outputs are of a high quality (and quantity) to continue to attract government and commercial grants. Each successful research project should lead to a number of publications in scholarly journals.

The hospital wishes to monitor the publication activity of individual researchers and to this end wants the new system to record, for each researcher,  details of all their published articles, together with the journals in which they were published and the date of publication etc. Many of the publications are linked to conferences, and this information also needs to be recorded (ie which researcher is going to which conference, and whether it is linked to a publication).