The Pinkton Psychiatric Hospital Database Design Project: CP0054E

This assignment details a comprehensive database design project for The Pinkton Psychiatric Hospital's research department. It encompasses stakeholder analysis, functional requirements, business rules, and the creation of an Entity-Relationship (ER) model. The project includes an ER diagram and its documentation, demonstrating the relationships between entities such as twins, researchers, research projects, and tests. The solution also presents the database implementation using SQL, including table structures and insert statements. Furthermore, the assignment addresses time management and group work effectiveness using Agile scrum methodology, with sprint planning, daily stand-ups, sprint reviews, and retrospectives. The project aims to create a robust database system to store and manage twin-related research data, facilitating efficient data retrieval and analysis.

Running head: SYSTEM AND DATABASE
SYSTEM AND DATABASE
Name of the Student
Name of the University
Author Note

1SYSTEM AND DATABASE
Table of Contents
Assignment Element 2: -............................................................................................................2
1 - User requirements:............................................................................................................2
Stakeholder analysis:..........................................................................................................2
Functional Requirements:..................................................................................................3
Business rules and Assumptions:.......................................................................................5
2 - ER Model Design and Documentation:............................................................................7
ER Diagram:.......................................................................................................................7
ER Model documentation:.................................................................................................7
3 - Evidence of time management and effective group work:.............................................12
4 - Peer assessment form:.....................................................................................................13
Assignment Element 3.............................................................................................................14
1- Final ER diagram:......................................................................................................14
2- Database implementation:.........................................................................................14
Bibliography:............................................................................................................................25

2SYSTEM AND DATABASE
Assignment Element 2: -
1 - User requirements:
Stakeholder analysis:
This section of this report discussed about the stakeholders of The Pinkton Psychiatric
Hospital Research department. The key stakeholders are:
Twins patients:
Twins are primary and internal stakeholder in this system. This system is developed to
store information of twins. Sick twin table is able to capture every detail in this system. this
database is developed for research purpose of various twins.
The Pinkton Psychiatric Hospital Research department:
This is the research department of the hospital. This department is internationally
record information of twins and search about their problems and working on their treatment.
This is the internal stakeholder of the system.
Neighbouring Hospital:
This system is also collected twin information from different neighbouring hospitals.
This is an external stakeholder for the system. Every twin detail is helpful for the research
that is the reason this system collects twin information form the different hospitals.
Researchers:
In this system, researchers are the primary stakeholder of this system. Researchers are
the guided by the Lead researchers in the hospital. A group of people can work on a particular
project.
Lead Researchers:

3SYSTEM AND DATABASE
Every group of researchers have a lead researcher who can lead their team. They are
the primary stakeholder for this system. Every lead researcher is guided by the research
manager.
Research manager:
Research managers are the primary stakeholder of the system. research manages
supervises the lead manages and their team. Research manages to submit an update of every
research to research director.
Hospital research director:
Hospital research director is the primary and internal stakeholder of this system.
research managers monitor and manage every research and create a schedule for it.
Functional Requirements:
The database has been developed meet the below listed functional requirements:
Requirement Id #1 – Priority – High:
Every information regarding twins is stored in the sick twins and illness record table.
These tables will help to register a twin into this system. Sick twins table store contact details,
sex, age, place of birth of twins, their nationality, blood goup, every information regarding
their illness, age and nationality of mother and father at the date of birth. In order to track or
migration this table keep every record of all previous address of each twin. Every information
can be retrieved with the SQL query.
Requirement Id #2 – Priority – High:
Illness record table can store every illness record and their diagnosis date. Apart from
this information this table also records they are admitted into a new hospital or not and the
duration of their stay. These records will help researchers to get their history.

4SYSTEM AND DATABASE
Requirement Id #3 – Priority – High:
Research projects table store every information about the research. Every information
regarding the research store in this table.
Requirement Id #4 – Priority – High:
Test table can store every information of tests. A twin can go through many tests for
diagnosis purpose. With the help of the test table this system stores every test record for the
research project.
Requirement Id #5 – Priority – medium:
This system is able to generate the complete testing record by using test and research
project table.
Requirement Id #6– Priority – medium:
Apart from the only test result, test and research table can help this system to produce
when they were tested, which test they took, for which project. By using queries, a database
administrator can retrieve this information from the database. this system also generates
various reports based on the tests.
Requirement Id #7 – Priority – high:
This system can produce a total number of researches on a particular twin patient.
With the help of this feature, researchers can avoid disturbing twin’s parents by approaching
them repeatedly to ask if they are going to participate in the project or not.
Requirement Id #8 – Priority – medium:

5SYSTEM AND DATABASE
With the help of this database, this system can allocate a budget for a particular
research project. This budget includes the travelling expenses, hotel costs and consultancy
expenses.
Requirement Id #9 – Priority – medium:
This system is capable to set an amount of time to complete a test. Task table can help
this system to perform this task. This system can fix the time for only activate projects.
Requirement Id #10 – Priority – High:
This system allows lead researchers and their managers to see the manpower budget
remaining on a specific project.
Requirement Id #11 – Priority – High:
One of the main problems in previous system is “various researchers have lodged
complaints of ‘favoritism’ – 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”. To overcome this problem this system can store every budget information in the
research project table and provide the all researchers id so that hospital authority can check
every researcher get sufficient money according to their project or not.
Requirement Id #12 – Priority – High:
This system can store every information for publishing a project. This system can
monitor and store journal and publication information of every researchers. Every researchers
details, their published journals details, and publication date can be found in the database.
Business rules and Assumptions:
As the case study contains only an outline of a business scenario, few business rules
and assumptions have been made. Assume that:

6SYSTEM AND DATABASE
 Sick twin entity can store every information about twins. It is the most helpful entity
to store twin information. Each twin can be identified with their unique T_ID number.
 Research projects is the most important entity in this database. one or more research
can be done on one twin. Every project can be uniquely identified with their project
id. Every project must have test details. For one project many tests can be done.
 For scholarly journals, this system stores every information of projects and the
researchers. Every journal can be based on only one project.
 Illness information can be store in the illness record. one twin can have only record.
every record is uniquely identified with its T_ID number.
 Test table store every information of tests. Every test is necessary for the research. For
one project many tests can be done. Every test can be identified with its test id
number.
 Every researcher can belong to departments. Every department has a unique
department id number. One department can contain many researchers.

7SYSTEM AND DATABASE
2 - ER Model Design and Documentation:
ER Diagram:
Figure 1: ER Diagram of Research Department
(Source: Created by Author)
ER Model documentation:
Department Table:
Column Type Null Defaul Links Comments Media (MIME)

8SYSTEM AND DATABASE
t to type
DEPT_ID (Primary
)
varchar(10) No
DEPT_NAME varchar(50) No
LOCATION varchar(100) Yes NULL
illness_record Table:
Column Type Null Default Links to Comments
Media
(MIME)
type
T_ID (Primary) varchar(20) No
sick_twin -
> T_ID
ILLNESS varchar(200) No
DATE_OF_DIAGNOSI
S
date No
HOSPITAL_NAME varchar(50) No
DURATION varchar(20) No

9SYSTEM AND DATABASE
Researchers Table:
Column Type
Nul
l
Default Links to Comments
Media
(MIME)
type
RESEARCHER_ID (Primary
)
varchar(20) No
NAME varchar(50) No
DEPARTMENT_ID varchar(20) No
department
->
DEPT_ID
research_projects Table:
Column Type
Nul
l
Defaul
t
Links to
Comment
s
Media
(MIME
) type
PROJECT_ID (Primar
y)
varchar(20) No
T_ID varchar(20) No sick_twin -> T_ID
TEST_ID varchar(20) No test -> TEST_ID

10SYSTEM AND DATABASE
DESCRIPTION
varchar(500
)
No
BUDGET int(11) No
RESEARCHERS_ID varchar(20) No
researchers ->
RESEARCHER_I
D
scholarly_journals Table:
Column Type
Nul
l
Defaul
t
Links to
Comment
s
Media
(MIME
) type
JOURNAL_ID (Primar
y)
varchar(20
)
No
PROJECT_ID
varchar(20
)
No
research_projects -
> PROJECT_ID
PUBLISH_DATE date No
RESEARCHERS_ID
varchar(20
)
No
researchers ->
RESEARCHER_I
D

11SYSTEM AND DATABASE
sick_twin Table:
Column Type
Nu
ll
Defa
ult
Lin
ks
to
Comm
ents
Medi
a
(MI
ME)
type
T_ID (Primary)
varchar(
20)
No
AGE int(11) No
SEX
varchar(
1)
No
CONTACT_NO int(11) No
PLACE_OF_BIRTH
varchar(
50)
No
NATIONALITY
varchar(
50)
No
BLOOD_TYPE
varchar(
10)
No
ILLNESS
varchar(
500)
No