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

12SYSTEM AND DATABASE
AGE_AND_NATIONALITY_OF_MOTHER_A
T_DATE_OF_BIRTH
varchar(
100)
No
AGE_AND_NATIONALITY_OF_FATHER_AT
_DATE_OF_BIRTH
varchar(
100)
No
Test Table:
Column Type Null
Defaul
t
Links
to
Comments
Media (MIME)
type
TEST_ID (Primary
)
varchar(10) No
DATE date No
DESCRIPTION varchar(500) Yes NULL
TIME_REQUIRED varchar(500) No
3 - Evidence of time management and effective group work:
Yes, this is a group assignment and it is developed as an Agile ‘scrum’ project, as
explained by a tutor with ‘daily stand up’ and sprint retrospectives conducted each week at
the start of the seminar session.
Sprint Preparation Meeting:

13SYSTEM AND DATABASE
At beginning of every sprint, a preparation or planning meeting is organized to
schedule the work and the work needs to be done. Our team members meets up to discuss the
on sprint backlog. In the first sprint, we have figure out how many tables need to be created
then we create a sprint backlog. This sprint log contains lists of every task that need to be
completed during each sprint.
Daily scrum or daily standup:
In every sprint, we have shared what we have done and now what we are doing. Daily
scrum is helpful for us because it helps us to coordinate with the team members as they were
discussed during sprint. We have completed every meeting within 15 minutes.
Sprint Review:
After finishing one sprint our team works on the functionality that we have listed in
the sprint.
Sprint retrospective:
After finishing the sprint, we have done meetings to analysis every sprint and we
work on how we can improve every sprint.
First, we have created storyboards to gather the requirement for data. after gathering the data,
we have created the first sprint. In the first sprint, we have to create tables. In the second
sprints, we have added the primary key and foreign key in ERD. Their sprint is focused on
normalization and fourth sprint is implementation. After these sprints, we have completed
the project.
4 - Peer assessment form:
We have created an assessment form. After the first group meeting, we have set a few
criteria and every group member following these criteria. with the help of email and phone,

14SYSTEM AND DATABASE
we have communicated each other frequently. We have attended every stand-up meeting that
is held in the seminar session and participate in every group meeting.
Assignment Element 3
1- Final ER diagram:
Figure 2: Final ER Diagram of Research Department
(Source: Created by Author)
2- Database implementation:
a.
-- Table structure for table `department`

15SYSTEM AND DATABASE
CREATE TABLE `department` (
`DEPT_ID` varchar(10) NOT NULL,
`DEPT_NAME` varchar(50) NOT NULL,
`LOCATION` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Table structure for table `illness_record`
--
CREATE TABLE `illness_record` (
`T_ID` varchar(20) NOT NULL,
`ILLNESS` varchar(200) NOT NULL,
`DATE_OF_DIAGNOSIS` date NOT NULL,
`HOSPITAL_NAME` varchar(50) NOT NULL,
`DURATION` varchar(20) NOT NULL

16SYSTEM AND DATABASE
)
--
-- Table structure for table `researchers`
--
CREATE TABLE `researchers` (
`RESEARCHER_ID` varchar(20) NOT NULL,
`NAME` varchar(50) NOT NULL,
`DEPARTMENT_ID` varchar(20) NOT NULL
)
--

17SYSTEM AND DATABASE
-- Table structure for table `research_projects`
--
CREATE TABLE `research_projects` (
`PROJECT_ID` varchar(20) NOT NULL,
`T_ID` varchar(20) NOT NULL,
`TEST_ID` varchar(20) NOT NULL,
`DESCRIPTION` varchar(500) NOT NULL,
`BUDGET` int(11) NOT NULL,
`RESEARCHERS_ID` varchar(20) NOT NULL
)
--
-- Table structure for table `scholarly_journals`
--

18SYSTEM AND DATABASE
CREATE TABLE `scholarly_journals` (
`JOURNAL_ID` varchar(20) NOT NULL,
`PROJECT_ID` varchar(20) NOT NULL,
`PUBLISH_DATE` date NOT NULL,
`RESEARCHERS_ID` varchar(20) NOT NULL
)
--
-- Table structure for table `sick_twin`
--
CREATE TABLE `sick_twin` (
`T_ID` varchar(20) NOT NULL,
`AGE` int(11) NOT NULL,
`SEX` varchar(1) NOT NULL,
`CONTACT_NO` int(11) NOT NULL,

19SYSTEM AND DATABASE
`PLACE_OF_BIRTH` varchar(50) NOT NULL,
`NATIONALITY` varchar(50) NOT NULL,
`BLOOD_TYPE` varchar(10) NOT NULL,
`ILLNESS` varchar(500) NOT NULL,
`AGE_AND_NATIONALITY_OF_MOTHER_AT_DATE_OF_BIRTH` varchar(100)
NOT NULL,
`AGE_AND_NATIONALITY_OF_FATHER_AT_DATE_OF_BIRTH` varchar(100) NOT
NULL
)
--
-- Table structure for table `test`
--
CREATE TABLE `test` (
`TEST_ID` varchar(10) NOT NULL,

20SYSTEM AND DATABASE
`DATE` date NOT NULL,
`DESCRIPTION` varchar(500) DEFAULT NULL,
`TIME_REQUIRED` varchar(500) NOT NULL
)
b.
Insert statements are included into the text script.
c.
VIEW1:
create view VIEW1 AS
SELECT * from research_projects;
View 1 can provide every detail about the research project. Lead researchers or hospital
research director can use this view to get every information.
VIEW2:
CREATE view VIEW2 AS

21SYSTEM AND DATABASE
select * from scholarly_journals;
This view is helpful for hospital authority. Hospital authority can check every journal details
by using this view.
d.
Query 1:
select sj.journal_id, sj.PROJECT_ID,sj.publish_date, sj.RESEARCHERS_ID, rp.T_ID from
scholarly_journals sj
INNER JOIN research_projects rp ON
sj.RESEARCHERS_ID=rp.RESEARCHERS_ID;
Lead researcher and hospital authority can use this query to get information about journals
and twins.
Query 2:
select * from sick_twin where T_ID='tw02';

22SYSTEM AND DATABASE
This query is helpful for researchers. They can find every details of specific twins.
Query 3:
select r.RESEARCHER_ID, r.NAME, d.DEPT_NAME,d.DEPT_ID from researchers r
inner JOIN
department d ON r.DEPARTMENT_ID = d.DEPT_ID;
This is a helpful query for hospital authority and lead researchers. By this query, they can
search every researchers details and their department details.
Query 4:
SELECT * FROM test
INNER join research_projects ON
test.TEST_ID=research_projects.TEST_ID
WHERE T_ID= 'tw02';
This is a helpful query for researchers who are searching for a particular test details for a
twin.

23SYSTEM AND DATABASE
Query 5:
select * from illness_record
INNER JOIN sick_twin ON
illness_record.T_ID=sick_twin.T_ID WHERE sick_twin.T_ID='tw03'
This is a helpful query for researchers. By using this query they can get past illness
information of twins.
Query 6:
select
r.RESEARCHER_ID,r.NAME,r.DEPARTMENT_ID,rp.PROJECT_ID,rp.DESCRIPTION,rp
.BUDGET from researchers r
INNER JOIN research_projects rp
ON
r.RESEARCHER_ID=rp.RESEARCHERS_ID;
This is a helpful query for hospital research director to get project information, budget and
the researchers id who are working on the projects.
Query 7:
select il.HOSPITAL_NAME from illness_record il
WHERE T_ID = 'tw01'

24SYSTEM AND DATABASE
By using this query researchers can get twin’s past hospital name.
Query 8:
select sj.JOURNAL_ID,sj.PUBLISH_DATE, rp.RESEARCHERS_ID,
r.NAME,r.DEPARTMENT_ID from scholarly_journals sj
INNER JOIN research_projects rp ON
sj.PROJECT_ID=rp.PROJECT_ID
inner join researchers r ON
rp.RESEARCHERS_ID=r.RESEARCHER_ID;
This is one of the helpful query for hospital research head. By using This query, they can get
every information about the journal, project, researcher name and their department details.

25SYSTEM AND DATABASE
Bibliography:
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Data, M.H., 2015. Database Design. Perancangan Basis Data) merupakan salah satu.
Date, C.J., 2019. Database design and relational theory: normal forms and all that jazz.
Apress.
De Antonellis, V. and Di Leva, A., 1985. DATAID-1: A database design
methodology. Information systems, 10(2), pp.181-195.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems (Vol. 7). Pearson.
Harrington, J.L., 2016. Relational database design and implementation. Morgan Kaufmann.
King, R. and McLeod, D., 1985. A database design methodology and tool for information
systems. ACM Transactions on Information Systems (TOIS), 3(1), pp.2-21.
Letkowski, J., 2015. Doing database design with MySQL. Journal of Technology
Research, 6, p.1.
López-Martínez, J., Juárez-Ramírez, R., Huertas, C., Jiménez, S. and Guerra-García, C.,
2016, April. Problems in the adoption of agile-scrum methodologies: A systematic literature
review. In 2016 4th international conference in software engineering research and
innovation (conisoft) (pp. 141-148). IEEE.
Srivastava, A., Bhardwaj, S. and Saraswat, S., 2017, May. SCRUM model for agile
methodology. In 2017 International Conference on Computing, Communication and
Automation (ICCCA) (pp. 864-869). IEEE.

26SYSTEM AND DATABASE
ur Rehman, F., Maqbool, B., Riaz, M.Q., Qamar, U. and Abbas, M., 2018, April. Scrum
Software Maintenance Model: Efficient Software Maintenance in Agile Methodology.
In 2018 21st Saudi Computer Society National Computer Conference (NCC) (pp. 1-5). IEEE.
Watt, A. and Eng, N., 2018. Database design.