Group1 Report

VIETNAM NATIONAL UNIVERSITY - HO CHI MINH CITY
INTERNATIONAL UNIVERSITY
SCHOOL OF COMPUTER SCIENCE AND ENGINEERING
PRINCIPLES OF
DATABASE MANAGEMENT
IT079IU
FINAL REPORT
Course by Assoc. Prof. Nguyen Thi Thuy Loan
TOPIC: MENTAL HEALTHCARE SYSTEM

BY GROUP 01 – MEMBER LIST
1. NGUYEN HOANG ANH TU ITDSIU20090 Team Leader
2. NGUYEN QUANG DIEU ITDSIU20031 Team Member

3. PHAM MINH VU ITITIU20354 Team Member
4. NGUYEN LUAN CONG BANG ITITIU20163 Team Member
5. HUYNH TRAN KHANH ITCSIU21011 Team Member

International University Principles of Database Management
School of Computer Science and Engineering GROUP 1 – MENTAL HEALTHCARE SYSTEM
TABLE OF CONTENT
I. INTRODUCTION ..............................................................................................................................................................................4
1. Abstract ..................................................................................................................................................................................4
2. System Overview ...............................................................................................................................................................4
3. Goal ...........................................................................................................................................................................................5
4. The techniques & tools used. ......................................................................................................................................6
II. TASK TIMELINE & DIVISION ..................................................................................................................................................6
1. Contribution.........................................................................................................................................................................6
2. Project Timeline & Task Division..............................................................................................................................6
III. PROJECT ANALYSIS ...................................................................................................................................................................8
1. Requirements Analysis ..................................................................................................................................................8
2. Approach Analysis ............................................................................................................................................................9
2.1 Reviewed Materials .................................................................................................................................................9
2.2 Research Approach..................................................................................................................................................9
3. System Analysis ..............................................................................................................................................................10
3.1 Database Design .....................................................................................................................................................10
3.2 Database and Tables Creation ........................................................................................................................11
3.3 Database Data Insertion.....................................................................................................................................18
3.4 Database Queries ...................................................................................................................................................26
4. Application Java structure .........................................................................................................................................35
4.1 Project structure ....................................................................................................................................................36
4.2 Class structure ........................................................................................................................................................37
4.3 Connection implementation ............................................................................................................................39
4.4 G.U.I. Design – Frames implementation.....................................................................................................46
4.5 Buttons implementation ....................................................................................................................................51
4.6 Application Demo – Screenshots ...................................................................................................................56
IV. CONCLUSION..............................................................................................................................................................................61
1. Achieved goals .................................................................................................................................................................61
2. Future work ......................................................................................................................................................................62
3. Concluding thoughts.....................................................................................................................................................62
V. REFERENCES ...............................................................................................................................................................................63
Project’s GitHub: LINK 2

LIST OF FIGURES
Figure 1. Primary Crow's Foot Format E.R.D. Diagram ................................................................... 10
Figure 2. Schema design created by using SQL Server.................................................................... 11
Figure 3. The project structures in IDE ................................................................................................... 36
Figure 4. UML Diagram of the project ...................................................................................................... 37
Figure 5. Free database hosting website for the project ................................................................. 39
Figure 6. Testing connection with the database ................................................................................. 40
Figure 7. Create a new G.U.I. Form in IDE .............................................................................................. 47
Figure 8. G.U.I. Designer Form for making a reservation................................................................ 48
Figure 9. Login interface for the first time running ........................................................................... 56
Figure 10. Specialist account registration successfully ...................................................................... 57
Figure 11. Patient dashboard with searched result ............................................................................. 58
Figure 12. Change user password successfully ...................................................................................... 58
Figure 13. Making a reservation successfully ......................................................................................... 59
Figure 14. Test-taking in progress................................................................................................................ 59
Figure 15. Score announcement .................................................................................................................... 60
Figure 16. Specialist posting reservation successfully ....................................................................... 61
Figure 17. Delist reservation successfully ................................................................................................ 61
Figure 18. Administrator mode for debugging purposes .................................................................. 61
LIST OF TABLES
Table 1. Individual responsibility and contribution ...............................................................................6
Table 2. Sprint planning and task division for individuals and teams ...........................................6
Table 3. Normal form achievement .............................................................................................................. 11
Table 4. An overview of all database entities and their attributes ............................................... 12

I. INTRODUCTION
1. Abstract
The Principles of Database Management course project involved developing a
mental healthcare services system with several features. These features included account
creation, searching for disease symptoms and solutions, finding reservations with
psychological specialists, and conducting mental health tests. The project also emphasized
creating a perfect database that adhered to the B.C. normal form. The database design
process logically defined data entities, relationships, attributes, and constraints. Achieving
the B.C. normal form ensured data integrity, eliminated anomalies, and optimized data
retrieval and storage ("1NF, 2NF, 3NF, and BCNF in Database Normalization | Studytonight,"
n.d.). The logical design was meticulously crafted to ensure system efficiency, reliability, and
scalability. The database contained data uploaded by reputable organizations from around
the world. This ensured that the information stored in the database was credible and
dependable. The system's user interface was designed to be user-friendly, enabling easy
access to various features. The search function allowed users to find information about
disease symptoms and solutions quickly. The reservation system provided a straightforward
way to schedule appointments with psychological specialists.
The mental health test feature also allowed users to assess their mental health status.
Overall, the project provided a valuable opportunity to apply database management
principles to a real-world problem in the field of mental health. It underscored the
significance of efficient and reliable data management in this domain. The thoughtful
approach to system features and design demonstrated a commitment to developing a robust
mental healthcare system. The project showcased the practical application of database
management principles in addressing challenges in mental health.
2. System Overview
The global pandemic has profoundly impacted mental health, resulting in increased
recognition of the importance of psychological therapy ("Depression," n.d.). In recent years,
there has been a significant shift in attitudes towards therapy, with more individuals
acknowledging its benefits and seeking professional help for their mental health concerns.

However, accessing traditional therapy can still be challenging for many people due to
factors such as long waitlists, high costs, and the enduring stigma associated with mental
health ("Suicide mortality rate (per 100,000 population) | Data," n.d.).
To address these barriers and meet the growing demand for mental health services,
online therapy platforms have emerged as a popular and accessible option. These platforms
offer a wide range of mental health services, including individual and group therapy sessions,
online resources, and support from licensed therapists. One of the critical advantages of
online therapy is its flexibility, allowing individuals to schedule sessions at their convenience
and receive therapy from the comfort of their homes. Moreover, these platforms often offer
more affordable pricing than traditional in-person therapy, making therapy accessible to a
broader audience.
Among the well-known online therapy platforms are BetterHelp, Talkspace, and
Amwell. BetterHelp provides various therapy options, including live sessions, messaging,
and chat support. Talkspace specializes in unlimited messaging therapy with licensed
therapists. On the other hand, Amwell offers mental health services as part of its broader
telehealth platform, including access to medical doctors and specialists.
The COVID-19 pandemic has further highlighted the significance of online therapy
platforms. With social distancing measures in place, many individuals have turned to these
platforms to continue receiving mental health support. Online therapy offers the added
benefits of anonymity and privacy, which can be particularly appealing to those who may
feel hesitant about seeking therapy in person due to stigma or other concerns.
It is important to note that while online therapy platforms offer numerous
advantages, they are not a substitute for traditional therapy, especially for individuals with
severe mental health conditions. These platforms, however, provide a valuable resource for
individuals seeking mental health support, offering a convenient and accessible alternative
to in-person therapy. While they are not a replacement for all types of therapy, online
platforms have become a valuable tool in supporting individuals' mental well-being and
addressing the increased demand for psychological support in today's challenging times.
3. Goal
- Design the database to suit the requirements of Normalization BC Normal Form.

- Connect the front-end interface (application) to the back-end database using a Java
connection driver.
- Develop the functions such as login, account creation, and booking; take a test using
complex queries.
- Evaluate the data and give more precise predictions in the future for the users.
- Take security measures in the database to ensure robust privacy for the users' data
and prevent any hacking into the system (known as SQL Injection).
4. The techniques & tools used.

- SQL Server manages data in a relational database management system (RDBMS)
using the SQL Server Management Studio and free hosting from
www.freeasphosting,net, which stores our database remotely.
- Java language using JetBrains IntelliJ IDEA GUI design the interface for users from
the database and application logic flow.
- Figma to design briefly about the interface.
- Version Control System from GitHub to easily collaborate on the project.
- Some other means of contact: are Microsoft Teams and Notion.
- SQL additional tool (Ltd, n.d.).
II. TASK TIMELINE & DIVISION

1. Contribution
Table 1. Individual responsibility and contribution
Name Responsibility Contribution
Nguyen Hoang Anh Tu Database Developer, Data Analyst 20%
Nguyen Quang Dieu Interface Developer, Data Analyst 20%
Nguyen Luan Cong Bang Interface Developer 20%
Pham Minh Vu Interface Developer 20%
Huynh Tran Khanh Interface Developer 20%
2. Project Timeline & Task Division

Table 2. Sprint planning and task division for individuals and teams

STAGE ACTION MEMBER WEEK

Research technical information about
mental symptoms, tests, and Tu
treatments
Collect sample data for analyzing Tu
Research for references and technical
Tu
documentation
1
Agree on communication, workflow,
All
and tools to use
Determine scopes, learning objectives,
All
PLANNING and goals for the project
Analyze proper approach and
All
methodology for project development
Specify requirements for the project All
Establish development timeline All
Determine proper technology stack and
All
database management system 2
Propose necessary features and their
All
priorities
Phase 1 review and retrospective All
Identify use cases and actors of the
All
system
Specify types of information that are
Tu, Dieu 3
essential to be stored in the database
Design user interface for core
CONCEPTUAL Dieu
functionalities
DESIGN Design the relational models Tu
Prepare the E.R. diagram and Class
Tu, Dieu 4
diagram
Database creation and setup All 5 ~ 12

Setup database tables, their

Tu
relationship, and constraints
Implement necessary queries for each
Tu
functionality
Develop application interface and
Bang, Vu, Khanh
client-side functionalities
Work on new user registration Khanh
Work on user login/logout and
Khanh
authentication
Develop searching system among
specialists, diseases, symptoms, and Dieu
IMPLEMENTATIONS
solutions through queries
Develop a booking system between
Vu
specialists and patients
Develop mental health test system and
give the approach sets based on the Bang
tests' results
Code review and refactoring Bang, Vu, Khanh
Bug detection and fixing Bang, Vu, Khanh
TESTING Design and implement test cases All
12 ~ 14
Fix and modify the application until
All
stable
Final report Tu, Dieu
PRESENTATION 14
Presentation slides Tu, Dieu
III. PROJECT ANALYSIS

1. Requirements Analysis
The proposed project aims to develop a comprehensive mental healthcare system with
features including secure account login, password reset, and user roles for patients and

specialists. Users can access and extract data based on their role and request additions to the
system's database. Patients can take tests to identify psychological disorders and receive
recommended solutions. The system will provide information on common mental illnesses,
symptoms, and potential solutions.
2. Approach Analysis
2.1 Reviewed Materials
- Advanced SQL Server language structure ("SQL Tutorial," n.d.).
- Java, JDBC, Swing Worker official usage documents ("Lesson: JDBC Basics (The
JavaTM Tutorials > JDBC Database Access)," n.d.), ("SwingWorker (Java Platform S.E.
8 )," n.d.).
2.2 Research Approach
a) Methodology
The Scrum framework, a part of the Agile approach, has been adopted for the project.
Teams can deliver products using Scrum, which emphasizes adaptability and responsiveness
to change in iterative and incremental stages. It is frequently called an "agile project
management framework" and concentrates on controlling circumstances to manage time
and expense. Traditional project management techniques, on the other hand, focus on set
requirements to manage time and costs. To support efficient project management, Scrum
uses several strategies, including time boxes, group rituals, a prioritized product backlog,
and regular feedback loops.
b) Process
The product backlog, which serves as a to-do list for the project, contains the features.
The team sets a sprint backlog with features and activities at the sprint planning meeting by
choosing the product backlog items they think can be completed during the sprint period.
When the team decides on the sprint backlog, they begin working on the assigned tasks with
a single, undivided concentration on reaching the sprint goal. The product backlog can be
altered before the start of the subsequent sprint, while the sprint backlog is left alone during
the sprint.

The team meets for a 15-minute scrum during the sprint to discuss remaining tasks,
offer updates on their progress, and resolve any issues they encounter. Each team member
presents their work to the other members of the team, who provide insightful feedback that
helps them plan the subsequent sprint and make required modifications. The three Scrum
pillars of transparency, inspection, and adaptability, which stress the value of open
communication, ongoing review, and the capacity to adjust to changing conditions, serve as
the framework for these meetings.
3. System Analysis
3.1 Database Design
Figure 1. Primary Crow's Foot Format E.R.D. Diagram

Figure 2. Schema design created by using SQL Server

Moreover, our database has achieved the highest B.C. normal form because of the
following table:
Table 3. Normal form achievement
Normal Form Description
1 N.F. The database does not have any multivalued tuples.
2 N.F. All the non-key attributes depend on the primary key.
3 N.F. There are no transitive dependencies between non-key attributes.
Every non-trivial functional dependency in the database depends on a
BCNF
candidate key.
3.2 Database and Tables Creation

This part discusses the database creation, schemas, and tables following the E.R.D.
the diagram in SQL Server Database:
a. Database Creation
CREATE DATABASE [congbang0711_]
USE [congbang0711_]

b. Schemas and Tables Analysis

The current version of the Entity-Relationship Diagram (E.R.D.) has the following twelve
entities:
Table 4. An overview of all database entities and their attributes
Table Attributes
User_ID bigint IDENTITY(1, 1) PRIMARY KEY
User_name varchar(50) NOT NULL
Account
Password varbinary(20) NOT NULL
Salt varchar(100) NOT NULL
Specialist_ID int IDENTITY(1, 1) PRIMARY KEY
UserID bigint NOT NULL
FullName varchar(50) NOT NULL
DoB date NOT NULL
Sex varchar(10) NOT NULL
Specialist
Email varchar(50) NOT NULL
Phone varchar(15) NOT NULL
IndentifyNumber varchar(50) NOT NULL
GraduationUniversity varchar(50) NOT NULL
Patient_ID int IDENTITY(1, 1) PRIMARY KEY

UserID bigint NOT NULL
FullName varchar(50) NOT NULL
Patient
DoB date NOT NULL
Sex varchar(10) NOT NULL
Email varchar(50) NOT NULL
HealingInformation_ID int IDENTITY(1, 1)
PRIMARY KEY
Healing SpecialistID int NOT NULL
Information Place varchar(50) NOT NULL
Date date NOT NULL
Fee money NOT NULL

Description varchar(200) NOT NULL

Extra_Information varchar(100)
Result_ID int PRIMARY KEY
TestID int NOT NULL
Result
Weight int NOT NULL
Description varchar(200)
Solution_ID int PRIMARY KEY
Name varchar(50) NOT NULL
Type varchar(50) NOT NULL
Solution
Benefit varchar(50) NOT NULL
Platform varchar(30) NOT NULL
Description varchar(400))
Disease_ID int PRIMARY KEY
Disease Name varchar(50) NOT NULL
Description varchar(200) NOT NULL
Symptom_ID int PRIMARY KEY
Symptom Name varchar(50) NOT NULL
Description varchar(100) NOT NULL)
Test_ID int PRIMARY KEY
Total int
Test
No_Question int
DiseaseID int NOT NULL
Question_ID int PRIMARY KEY
TestID int NOT NULL
Question
Title varchar(500) NOT NULL
AnswerSetID int NOT NULL
AnswerSet_ID int PRIMARY KEY
Answer Set
No_Answer int NOT NULL
Answer_ID int PRIMARY KEY
Answer Title varchar(100) NOT NULL
Weight int NOT NULL

From the above table, we can conclude some requirements as follows:

o The Account table has a field of Password that contains the binary result from
the SHA1 hashing result with randomly generated Salt. ("Add a Salt with the
SQL Server HASHBYTES Function," n.d.)
o The Patient and Specialists tables have fields that store user
information and can be extracted for their actions with the system.
o The HealingInformation table is responsible for healing reservations
between patients and specialists.
o The Disease and Symptom tables store the information users can search
using their names.
o The Test, Question, AnswerSet, Answer, Result, and
Solution tables serve to conduct tests and solutions given per patient and
track their records after taking tests.
c. Account Schema
Account Table:
CREATE TABLE [Account].[Account] ([User_ID] [bigint] IDENTITY(1, 1) PRIMARY
KEY,
[User_name] [varchar](50) NOT NULL,
[Password] [varbinary](20) NOT NULL,
[Salt] [varchar](100) NOT NULL)
Patient Table:
CREATE TABLE [Account].[Patient] ([Patient_ID] [int] IDENTITY(1, 1) PRIMARY
KEY,
[UserID] [bigint] NOT NULL,
[FullName] [varchar](50) NOT NULL,
[DoB] [date] NOT NULL,
[Sex] [varchar](10) NOT NULL,
[Email] [varchar](50) NOT NULL,
FOREIGN KEY(UserID)REFERENCES [Account].[Account](User_ID))
PatientRecord Table:

CREATE TABLE [Account].[PatientRecord] ([ID] [int] PRIMARY KEY,

[PatientID] [int] NOT NULL,
[ResultID] [int] NOT NULL,
FOREIGN KEY(PatientID)REFERENCES [Account].[Patient](Patient_ID),
FOREIGN KEY(ResultID)REFERENCES [Test].[Result](Result_ID))
Specialist Table:
CREATE TABLE [Account].[Specialist] ([Specialist_ID] [int] IDENTITY(1, 1)
PRIMARY KEY,
[UserID] [bigint] NOT NULL,
[FullName] [varchar](50) NOT NULL,
[DoB] [date] NOT NULL,
[Sex] [varchar](10) NOT NULL,
[Email] [varchar](50) NOT NULL,
[Phone] [varchar](15) NOT NULL,
[IdentifyNumber] [varchar](50) NOT NULL,
[GraduationUniversity] [varchar](50) NOT NULL,
FOREIGN KEY(UserID)REFERENCES [Account].[Account](User_ID))
d. Booking Schema
HealingInformation Table:
CREATE TABLE [Booking].[HealingInformation] ([HealingInformation_ID] [int]
IDENTITY(1, 1) PRIMARY KEY,
[SpecialistID] [int] NOT NULL,
[Place] [varchar](50) NOT NULL,
[Date] [date] NOT NULL CHECK(Date>CAST(GetDate() as Date)),
[Fee] [money] NOT NULL,
[Description] [varchar](200) NOT NULL,
[Extra_Information] [varchar](100),
FOREIGN KEY(SpecialistID)REFERENCES [Account].[Specialist](Specialist_ID))
Booking Table:
CREATE TABLE [Booking].[Booking] ([Book_ID] [int] IDENTITY(1, 1) PRIMARY
KEY,
[PatientID] [int] NOT NULL,

[HealingInformationID] [int] NOT NULL UNIQUE,

FOREIGN KEY(PatientID)REFERENCES [Account].[Patient](Patient_ID),
FOREIGN KEY(HealingInformationID)REFERENCES
[Booking].[HealingInformation](HealingInformation_ID))
e. Disease Schema
Disease Table:
CREATE TABLE [Disease].[Disease] ([Disease_ID] [int] PRIMARY KEY,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](200) NOT NULL)
Symptom Table:
CREATE TABLE [Disease].[Symptom] ([Symptom_ID] [int] PRIMARY KEY,
[Description] [varchar](100) NOT NULL)
DiseaseSymptom Table:
CREATE TABLE [Disease].[DiseaseSymptom] ([ID] [int] PRIMARY KEY,
[DiseaseID] [int] NOT NULL,
[SymptomID] [int] NOT NULL,
FOREIGN KEY(DiseaseID)REFERENCES [Disease].[Disease](Disease_ID),
FOREIGN KEY(SymptomID)REFERENCES [Disease].[Symptom](Symptom_ID))
f. Test Schema
Test Table:
CREATE TABLE [Test].[Test] ([Test_ID] [int] PRIMARY KEY,
[Total] [int],
[No_Question] [int],
[DiseaseID] [int] NOT NULL,
FOREIGN KEY(DiseaseID)REFERENCES [Disease].[Disease](Disease_ID))
Result Table:
CREATE TABLE [Test].[Result] ([Result_ID] [int] PRIMARY KEY,

[TestID] [int] NOT NULL,

[Weight] [int] NOT NULL,
[Description] [varchar](200),
FOREIGN KEY(TestID)REFERENCES [Test].[Test](Test_ID))
Question Table:
CREATE TABLE [Test].[Question] ([Question_ID] [int] PRIMARY KEY,
[TestID] [int] NOT NULL,
[Title] [varchar](500) NOT NULL,
[AnswerSetID] [int] NOT NULL,
FOREIGN KEY(TestID)REFERENCES [Test].[Test](Test_ID),
FOREIGN KEY(AnswerSetID)REFERENCES [Test].[AnswerSet](AnswerSet_ID))
AnswerSet Table:
CREATE TABLE [Test].[AnswerSet] ([AnswerSet_ID] [int] PRIMARY KEY,
[No_Answer] [int] NOT NULL)
AnswerSetContent Table:
CREATE TABLE [Test].[AnswerSetContent] ([ID] [int] PRIMARY KEY,
[AnswerSetID] [int] NOT NULL,
[AnswerID] [int] NOT NULL,
FOREIGN KEY(AnswerID)REFERENCES [Test].[Answer](Answer_ID),
FOREIGN KEY(AnswerSetID)REFERENCES [Test].[AnswerSet](AnswerSet_ID))
Answer Table:
CREATE TABLE [Test].[Answer] ([Answer_ID] [int] PRIMARY KEY,
[Title] [varchar](100) NOT NULL,
[Weight] [int] NOT NULL)
g. Solution Schema
Solution Table:
CREATE TABLE [Solution].[Solution] ([Solution_ID] [int] PRIMARY KEY,
[Type] [varchar](50) NOT NULL,

[Benefit] [varchar](50) NOT NULL,

[Platform] [varchar](30) NOT NULL,
[Description] [varchar](400))
Recommendation Table:
CREATE TABLE [Solution].[Recommendation] ([ID] [int] PRIMARY KEY,
[SolutionID] [int] NOT NULL,
[ResultID] [int] NOT NULL,
FOREIGN KEY(SolutionID)REFERENCES [Solution].[Solution](Solution_ID),
FOREIGN KEY(ResultID)REFERENCES [Test].[Result](Result_ID))
CureOneByOne Table:
CREATE TABLE [Solution].[CureOneByOne] ([ID] [int] PRIMARY KEY,
[SolutionID] [int],
[SymptomID] [int] NOT NULL,
FOREIGN KEY(SolutionID)REFERENCES [Solution].[Solution](Solution_ID),
FOREIGN KEY(SymptomID)REFERENCES [Disease].[Symptom](Symptom_ID))
3.3 Database Data Insertion

a. Source of data insertion
In general, the data insert into the database comes from three primary sources:
o Self-generated data for testing purposes, as in Account, Patient,
Specialist, HealingInformation.
o Look up the web for accurate data such as Disease, Symptom, Test,
Question, AnswerSet, Answer, Result, and Solution.
("FREE Mental Health Tests, Quizzes, Self-Assessments, & Screening Tools,"
2022)
o When the system goes public, the data from real user input is considered and
validated before inputting into the database.
o We also have some update and removal procedures for users to make
changes to suit their needs.

In this report, the first five rows of each table are demonstrated to preview the data insert
only, and the complete data can be found using the backup file attached to this report.
b. Account Schema
Account Table:
INSERT [Account].[Account]([User_ID], [User_name], [Salt], [Password])
VALUES(1, N'tucute123', N'9Pn(2',
0xDD4689A6BF12DFA64DDD713199BA53B51F65AD53)
VALUES(2, N'lamthanhluan2907', N'6Eb(2',
0x698BF12AA5B33A21903D296B5784158A6DFB1378)
VALUES(3, N'vanbui.work', N'0Lw%2',
0x01D1D332798FD5D6EE495294886ABD5886418F1A)
VALUES(4, N'guangyaoaoao', N'0Rk$8',
0xAC9B7169C63453090BDAA6E857338528B77BC43F)
VALUES(5, N'congbang', N'5Sj%3', 0xC17EDC261652EC7CD357BC6850B8962B9EE9216A)
Patient Table:
INSERT [Account].[Patient]([Patient_ID], [UserID], [FullName], [DoB], [Sex],
[Email])
VALUES(1, 1, N'Nguyen Hoang Anh Tu', CAST(N'2002-06-13' AS Date), N'Male',
N'nghganhtu@gmail.com')
[Email])
VALUES(2, 3, N'Bui Thi Cam Van', CAST(N'2002-12-08' AS Date), N'Female',
N'buicamvan.work@gmail.com')
[Email])
VALUES(3, 4, N'Nguyen Quang Dieu', CAST(N'2002-05-29' AS Date), N'Male',
N'guangyao@gmail.com')
[Email])

VALUES(4, 5, N'Nguyen Luan Cong Bang', CAST(N'2002-11-07' AS Date), N'Male',

N'congbang.1107@gmail.com')
[Email])
VALUES(5, 7, N'Huynh Tran Khanh', CAST(N'2003-01-13' AS Date), N'Male',
N'htk2003@gmail.com')
PatientRecord Table:
INSERT [Account].[PatientRecord]([ID], [PatientID], [ResultID])
VALUES(1, 1, 2)
VALUES(2, 5, 19)
VALUES(3, 1, 4)
VALUES(4, 3, 15)
VALUES(5, 4, 1)
Specialist Table:
INSERT [Account].[Specialist]([Specialist_ID], [UserID], [FullName], [DoB],
[Sex], [Email], [Phone], [GraduationUniversity], [IdentifyNumber])
VALUES(1, 2, N'Lam Thanh Luan', CAST(N'1990-06-13' AS Date), N'Male',
N'lamthanhluan@gmail.com', N'0913517039', N'Sai Gon University',
N'403697021687539')
VALUES(2, 6, N'Pham Minh Vu', CAST(N'1985-06-13' AS Date), N'Male',
N'laimaybay@gmail.com', N'0947513068', N'International University',
N'5103697140687439')
VALUES(5, 28, N'Trinh Duc Bao', CAST(N'2002-09-06' AS Date), N'Male',
N'trducbo@gmail.com', N'0944738291', N'International University',
N'032428934334')

c. Booking Schema
HealingInformation Table:
INSERT [Booking].[Booking]([Book_ID], [PatientID], [HealingInformationID])
VALUES(1, 5, 1)
VALUES(3, 2, 2)
VALUES(5, 1, 4)
VALUES(6, 1, 8)
Booking Table:
INSERT [Booking].[HealingInformation]([HealingInformation_ID],
[SpecialistID], [Place], [Date], [Fee], [Description], [Extra_Information])
VALUES(1, 1, N'Quan 3', CAST(N'2023-05-01' AS Date), 300000.0000, N'I will
help you to get out of your mental problem within 1 hour', N'Please come in
the morning')
VALUES(2, 2, N'Quan 1', CAST(N'2023-05-01' AS Date), 400000.0000, N'If you
need help, come to me', NULL)
need help, come to me', N'I am free in the afternoon')
d. Disease Schema

Disease Table:
INSERT [Disease].[Disease]([Disease_ID], [Name], [Description])
VALUES(1, N'Depression', N'Depression is a mood disorder that causes a
persistent feeling of sadness and loss of interest.')
VALUES(2, N'Stress', N'Stress is a consequence of experiencing pressure or
tension.')
VALUES(3, N'Anxiety', N'Anxiety is an emotion caused by a perceived or
experienced threat, which often leads to an avoidance or evasion thereof.')
VALUES(4, N'Borderline', N'Borderline is a personality disorder. People with
borderline have fast-changing feelings toward friendship, love, and so on.')
VALUES(5, N'Migraine', N'Migraine is an illness, where regular intense
attacks of headache occur. In the case of migraine, this headache often only
takes place at one side of the head, and causes a pounding type of pain')
Symptom Table:
INSERT [Disease].[Symptom]([Symptom_ID], [Name], [Description])
VALUES(1, N'Eating disorder', N'Physical')
VALUES(2, N'Sleeping disorder', N'Physical')
VALUES(3, N'Having fewer feelings', N'Mental')
VALUES(4, N'Decrease libido', N'Physical')
VALUES(5, N'Tension', N'Mental')
DiseaseSymptom Table:
INSERT [Disease].[DiseaseSymptom]([I.D.], [DiseaseID], [SymptomID])
VALUES(1, 1, 15)
VALUES(2, 1, 30)

VALUES(3, 1, 19)
VALUES(4, 1, 31)
VALUES(5, 1, 2)
e. Test Schema
Test Table:
INSERT [Test].[Test]([Test_ID], [Total], [No_Question], [DiseaseID])
VALUES(1, 27, 9, 1)
VALUES(2, 40, 10, 2)
VALUES(3, 40, 10, 3)
VALUES(4, 17, 9, 4)
VALUES(5, NULL, NULL, 5)
Result Table:
INSERT [Test].[Result]([Result_ID], [TestID], [Weight], [Description])
VALUES(1, 1, 80, N'Little Severe')
VALUES(2, 1, 100, N'Severe')
VALUES(4, 2, 100, N'Severe')
Question Table:
INSERT [Test].[Question]([Question_ID], [TestID], [Title], [AnswerSetID])
VALUES(1, 1, N'Little interest or pleasure in doing things?', 1)

VALUES(2, 1, N'Feeling down, depressed, or hopeless?', 1)

VALUES(3, 1, N'Trouble falling or staying asleep, or sleeping too much?', 1)
VALUES(4, 1, N'Feeling tired or having little energy?', 1)
VALUES(5, 1, N'Poor appetite or overeating?', 1)
AnswerSet Table:
INSERT [Test].[AnswerSet]([AnswerSet_ID], [No_Answer])VALUES(1, 4)
AnswerSetContent Table:
INSERT [Test].[AnswerSetContent]([I.D.], [AnswerSetID], [AnswerID])
VALUES(1, 1, 1)
VALUES(2, 1, 2)
VALUES(3, 1, 3)
VALUES(4, 1, 4)
VALUES(5, 2, 5)
Answer Table:
INSERT [Test].[Answer]([Answer_ID], [Title], [Weight])
VALUES(1, N'Not at all', 0)
VALUES(2, N'Several days', 1)
VALUES(3, N'More than half the days', 2)

VALUES(4, N'Nearly every day', 3)

VALUES(5, N'Never', 0)
f. Solution Schema
Solution Table:
INSERT [Solution].[Solution]([Solution_ID], [Name], [Type], [Benefit],
[Platform], [Description])
VALUES(1, N'Sheep in Dream', N'Game', N'Sleep', N'Mobile App', N'Sheep in
Dream is a game about getting a boy to sleep by making sheep jump over a
log. The sheep are in the top left corner of the screen on a dream hill and
the boy is in his bed at the bottom. ')
VALUES(2, N'Love is in small things', N'Game', N'Relationship', N',Mobile
App', N'Love is…in small things is one of the romantic games made
by Lunosoft. The game showcases the lively artwork of a South Korean artist
who goes by the name Puuung and is a romantic title')
VALUES(3, N'My Oasis: Relaxing, Satisfying', N'Game', N'Communicate-Calm',
N'Mobile App', N'My Oasis: Anxiety Relief Game is a relaxing game that helps
in calming your mind, relieve your stress. This anti anxiety game helps in
relaxation and calm sleeping.')
VALUES(4, N'Focus Plant: Pomodoro timer', N'App', N'Focus', N'Mobile App',
N'Focus is a gamified focus study timer app to help people better manage
their time and focus on the important things in their life. Some times self-
discipline is just not enough to keep us away from the phones. This is when
gamification comes in, the in-game achievements encourage users to stick
with the app, better manage their time and get things done.')
VALUES(5, N'Wokamon - Walking app game', N'Game', N'Exercise', N'Mobile
App', N'Wokamons are running out of resources and they need your help! Every

step you take is turned into energy. Use it to feed, grow and collect
Wokamons. The more you walk, the more Wokamons you can collect and the
further you get to explore the magical Woka-worlds like candy desert, icy
realm, mystical forest and more! Lend a helping hand and soon you will find,
the more you help, the fitter you get!')
Recommendation Table:
INSERT [Solution].[Recommendation]([ID], [SolutionID], [ResultID])
VALUES(1, 30, 1)
VALUES(2, 18, 1)
VALUES(3, 13, 1)
VALUES(4, 53, 2)
VALUES(5, 11, 2)
CureOneByOne Table:
INSERT [Solution].[CureOneByOne]([I.D.], [SolutionID], [SymptomID])
VALUES(1, 58, 1)
VALUES(2, 1, 2)
VALUES(3, 3, 3)
VALUES(4, 59, 4)
VALUES(5, 56, 5)
3.4 Database Queries

a. Queries analysis

Most queries shown below are in the same form of this structure to ensure the user
input is sanitized, where each question mark shows it is parameterized to suit the content
user data:
o DECLARE AS SET: to declare additional variables used in the query
o INSERT INTO VALUES: to insert data into the tables
o SELECT: to show the columns for return
o FROM: to specify the table use
o INNER JOIN: to join the table for more linked data query
o WHERE: to set a condition for each query
o ORDER BY: to sort the results in ascending order
o GROUP BY: to pack the same data into groups
b. Register account
Our system users can create accounts with two roles: Patient and Specialist. In
addition, we provide several features, such as password change and display of the user's
name in the welcome message.
User login authentication
The following query is used to check if user login credentials are correct and exist in
the database; this query returns the role also for later use in other queries.
DECLARE @UserName AS VARCHAR(50) = ?
DECLARE @Pwd AS VARCHAR(30) = ?
SELECT CASE WHEN A.User_ID=P.UserID AND P.UserID IS NOT NULL THEN 'Patient'
WHEN A.User_ID=S.UserID AND S.UserID IS NOT NULL THEN 'Specialist' ELSE NULL END
AS Role, CASE WHEN A.User_ID=P.UserID AND P.UserID IS NOT NULL THEN P.Patient_ID
WHEN A.User_ID=S.UserID AND S.UserID IS NOT NULL THEN S.Specialist_ID ELSE NULL END AS
ID
FROM [Account].[Account] A
FULL JOIN [Account].[Patient] P ON A.User_ID=P.UserID
FULL JOIN [Account].[Specialist] S ON A.User_ID=S.UserID
WHERE A.User_name=@UserName AND A.Password=HASHBYTES('SHA1', CONCAT(@Pwd, (SELECT A.Salt
FROM [Account].[Account] A WHERE A.User_name=@UserName)))

Patient registration
The following query is used to create an account for a user with the patient role,
containing basic information such as account name, password, and personal information.
The password is secured by adding a salt value after each password input to create a hash,
which cannot be retrieved back to string one.
DECLARE @Username VARCHAR(50) = ?
DECLARE @Salt AS VARCHAR(5)
SET @Salt=CONCAT(CHAR(FLOOR(RAND()* 10)+48), CHAR(FLOOR(RAND()* 26)+65),
CHAR(FLOOR(RAND()* 26)+97), CHAR(FLOOR(RAND()* 15)+33), CHAR(FLOOR(RAND()* 10)+48))
INSERT INTO [Account].[Account]([User_name], [Password], [Salt])
VALUES(@Username, HASHBYTES('SHA1', CONCAT(@Pwd, @Salt)), @Salt)
INSERT INTO [Account].[Patient]([UserID], [FullName], [DoB], [Sex], [Email])
VALUES((SELECT User_ID FROM [Account].[Account] A WHERE A.User_name = ?), ?, ?, ?, ?)
Specialist registration
The following query is used to create an account for a user with the specialist role,
containing basic information such as account name, password, personal information, and
identity information. The password is secured by adding a salt value after each password to
create a hash that cannot be retrieved back to the string value.
DECLARE @Username AS VARCHAR(50) = ?
SET @Salt=CONCAT(CHAR(FLOOR(RAND()* 10)+48), CHAR(FLOOR(RAND()* 26)+65), CHAR(FLOOR(RAND()*
26)+97), CHAR(FLOOR(RAND()* 15)+33), CHAR(FLOOR(RAND()* 10)+48))
INSERT INTO [Account].[Account]([User_name], [Password], [Salt])
VALUES(@Username, HASHBYTES('SHA1', CONCAT(@Pwd, @Salt)), @Salt)
INSERT INTO [Account].[Specialist]([UserID], [FullName], [DoB], [Sex], [Email], [Phone],
[IdentifyNumber], [GraduationUniversity])
VALUES((SELECT User_ID FROM [Account].[Account] A WHERE A.User_name=?), ?, ?, ?, ?, ?, ?, ?)
Username display
This query displays the username on the system welcoming message based on the
user's I.D.
DECLARE @UserID AS INT = ?

DECLARE @Role AS VARCHAR(30) = ?

SELECT FullName
FROM(SELECT CASE WHEN @UserID=P.Patient_ID AND @Role='Patient' THEN P.FullName
WHEN @UserID=S.Specialist_ID AND @Role='Specialist' THEN S.FullName END AS
FullName
FROM [Account].[Account] A
FULL JOIN [Account].[Patient] P ON A.User_ID=P.UserID
FULL JOIN [Account].[Specialist] S ON A.User_ID=S.UserID) AS SUBQUERY
WHERE FullName IS NOT NULL
User's password change

The query below changes the password provided; the user must correctly enter their
old password. The new password will be secured by adding salt to form a hash like when the
user registers into the system.
DECLARE @oldPwd AS VARCHAR(30) = ?
DECLARE @newPwd AS VARCHAR(30) = ?
SET @Salt=CONCAT(CHAR(FLOOR(RAND()* 10)+48), CHAR(FLOOR(RAND()* 26)+65),
CHAR(FLOOR(RAND()* 26)+97), CHAR(FLOOR(RAND()* 15)+33), CHAR(FLOOR(RAND()* 10)+48))
UPDATE [Account].[Account]
SET Password=HASHBYTES('SHA1', CONCAT(@newPwd, @Salt)), Salt=@Salt
WHERE User_name=@UserName AND Password=HASHBYTES('SHA1', CONCAT(@oldPwd, (SELECT A.Salt
FROM [Account].[Account] A WHERE A.User_name=@UserName)))
c. Search for specific disease

Search for disease's symptoms and solutions
Suppose the user enters the right disease name. In that case, the following query is
designed to return an interface with information about the symptoms of any disease and
the appropriate solutions for each symptom in string result.
SELECT S.Name AS symptom, So.Name AS solution, So.Platform, So.Description
FROM [Disease].[Disease] D
INNER JOIN [Disease].[DiseaseSymptom] DS ON D.Disease_ID=DS.DiseaseID
INNER JOIN [Disease].[Symptom] S ON DS.SymptomID=S.Symptom_ID
INNER JOIN [Solution].[CureOneByOne] C ON S.Symptom_ID=C.SymptomID
INNER JOIN [Solution].[Solution] So ON C.SolutionID=So.Solution_ID

WHERE D.Name= ?
d. Make healing reservations

Regarding the appointment booking feature, users and patients are provided with
several useful features such as posting information, booking, deleting, and showing
upcoming appointment information.
Healing information posted by specialist
The following query adds information to the database whenever the specialist posts
details about potential future treatment, provided that the treatment date must be later
than the current date, checked by integrity constraint on the table creation.
INSERT INTO [Booking].[HealingInformation] ([SpecialistID], [Place], [Date], [Fee],
[Description], [Extra_Information])
VALUES (?,?,?,?,?,?)
Check the healing information state of each specialist

To see if any treatment appointments from specialists are scheduled or open, use
the query below.
SELECT H.HealingInformation_ID AS ID, CONCAT(DAY(H.Date), ' / ', MONTH(H.Date)) AS
DATE, H.Place, H.Fee, P.FullName, P.Sex, P.Email, CASE WHEN
H.HealingInformation_ID=B.HealingInformationID THEN 'RESERVED' ELSE 'VACANT' END AS
State
FROM [Account].[Specialist] S
FULL JOIN [Booking].[HealingInformation] H ON S.Specialist_ID=H.SpecialistID
FULL JOIN [Booking].[Booking] B ON H.HealingInformation_ID=B.HealingInformationID
FULL JOIN [Account].[Patient] P ON B.PatientID=P.Patient_ID
WHERE S.[Specialist_ID] =? AND H.Date>=CAST(GETDATE() AS DATE)
ORDER BY H.Date
Delete vacant listed information

If the specialist is busy at that time, use the command below to have the information
about scheduled sessions deleted.
DELETE FROM [Booking].[HealingInformation]
WHERE SpecialistID = ? AND HealingInformation_ID = ?

Check the vacant healing information

Use the query below to look for available treatment sessions and details on that
treatment; this is returned in a tabular form for easier looking.
SELECT H.HealingInformation_ID AS ID, H.Date, H.Place, CAST(H.Fee AS INT) AS Fee,
S.FullName, S.Sex
FROM [Booking].[HealingInformation] H
INNER JOIN [Account].[Specialist] S ON S.Specialist_ID=H.SpecialistID
WHERE H.HealingInformation_ID NOT IN(SELECT DISTINCT B.HealingInformationID FROM
[Booking].[Booking] B)AND H.Date>=CAST(GETDATE() AS DATE)
ORDER BY H.Date
Book the reservation by patient

Whenever a patient wants to book a treatment, use the query below to store the
information.
INSERT INTO [Booking].[Booking] ([PatientID], [HealingInformationID])
VALUES (?,?)
Cancel the booked reservation by patient

The patient can use the query below to ask the system to cancel the scheduled
treatment sessions.
DELETE FROM [Booking].[Booking]
WHERE PatientID = ? AND HealingInformationID = ?
Booked patient's reservation

The system will execute the query below to return the information to the patient
whenever they need to review the details of the scheduled treatment sessions.
SELECT H.HealingInformation_ID AS ID, CONCAT(DAY(H.Date), ' / ', MONTH(H.Date)) AS
DATE, H.Place, S.FullName, S.Phone
INNER JOIN [Booking].[HealingInformation] H ON S.Specialist_ID=H.SpecialistID
INNER JOIN [Booking].[Booking] B ON
H.HealingInformation_ID=B.HealingInformationID
INNER JOIN [Account].[Patient] P ON B.PatientID=P.Patient_ID
WHERE P.Patient_ID = ? AND H.Date>=CAST(GETDATE() AS DATE)
ORDER BY H.Date

e. Take a patient mental health test

The system offers a test function and sets of solutions for each distinct type of disease
result so that users can assess their level of mental illness.
Show the test I.D.s
The query below displays the test's id for more complex queries.
SELECT Test_ID AS ID FROM [Test].[Test]
Show the question I.D.s

The query below displays the question's I.D.s for more complex queries.
SELECT Question_ID AS questionID FROM [Test].[Question] WHERE TestID = ?
Show the question's content

The query below displays the questions' content in the interface.
SELECT Title FROM [Test].[Question] WHERE TestID = ? AND Question_ID = ?
Show the answer's content

The query below displays answers content for specific question I.D. in the interface.
SELECT A.Title
FROM [Test].[Question] Q
INNER JOIN [Test].[AnswerSet] ASET ON Q.AnswerSetID=ASET.AnswerSet_ID
INNER JOIN [Test].[AnswerSetContent] ASCon ON
ASET.AnswerSet_ID=ASCon.AnswerSetID
INNER JOIN [Test].[Answer] A ON ASCon.AnswerID=A.Answer_ID
WHERE Q.Question_ID= ?
Show the total score of a particular test

The query below shows the total score of a particular test.
SELECT Total FROM [Test].[Test] WHERE Test_ID= ?
Show weight per answer

The query below is used to show the weight of each answer to calculate the result of
the patient for the test.
SELECT A.Weight
FROM [Test].[Question] Q
INNER JOIN [Test].[AnswerSet] ASET ON Q.AnswerSetID=ASET.AnswerSet_ID
INNER JOIN [Test].[AnswerSetContent] ASCon ON
ASCon.AnswerSetID=ASET.AnswerSet_ID
INNER JOIN [Test].[Answer] A ON ASCon.AnswerID=A.Answer_ID
WHERE Q.Question_ID= ?
Show solutions for specific tests.

The query below gives the patient the solution set for the corresponding result.
DECLARE @TestID AS INT = ?
DECLARE @Weight AS INT = ?
SELECT R.Result_ID AS ID, S.Name, S.Benefit, S.Platform, S.Description
FROM [Test].[Result] R
INNER JOIN [Solution].[Recommendation] Re ON R.Result_ID=Re.ResultID
INNER JOIN [Solution].[Solution] S ON Re.SolutionID=S.Solution_ID
WHERE R.TestID=@TestID AND R.Weight=@Weight AND R.Result_ID IN(SELECT R.Result_ID
FROM [Test].[Result]
R
WHERE TestID=@TestID
AND Weight=@Weight)
Record the patient's result

The query below stores the results of each test a patient completes in their medical
record.
INSERT INTO [Account].[PatientRecord]
VALUES (?, ?)
f. Analyze the system for administrator

For admin, they can use query commands to find the information they need for
research and marketing after entering a special mode with a secret password.
Check the patients who are in diseases severe level

The system will run the query below to check the patients at the severe disease
level.
SELECT TOP 1 Pa.FullName, Pa.Sex, Pa.DoB, COUNT(Description) NumberOfSevereDisease
FROM [Account].[Patient] Pa
INNER JOIN [Account].[PatientRecord] Pr ON Pa.Patient_ID=Pr.PatientID
INNER JOIN [Test].[Result] R ON PR.ResultID=R.Result_ID
WHERE R.Description LIKE 'Severe'
GROUP BY Pa.FullName, Pa.Sex, Pa.DoB
ORDER BY NumberOfSevereDisease DESC
Find the patient who booked the most reservations

The system will run the query below to display the patient who booked the most
reservations.
SELECT TOP 1 Pa.FullName, Pa.Sex, Pa.DoB, COUNT(B.HealingInformationID)
NumberOfReservation
FROM [Account].[Patient] Pa
INNER JOIN [Booking].[Booking] B ON Pa.Patient_ID=B.PatientID
GROUP BY Pa.FullName, Pa.Sex, Pa.DoB
ORDER BY NumberOfReservation DESC
Find the specialist who posted the most reservations

The system will run the query below to display the specialist who posted the most
reservations.
SELECT TOP 1 S.Specialist_ID, S.FullName, COUNT(H.HealingInformation_ID)
NumberOfReservation
GROUP BY S.Specialist_ID, S.FullName
ORDER BY NumberOfReservation DESC
Find the specialist who has the most booked reservations

The system will run the query below to display the specialist with the most booked
reservation.
SELECT TOP 1 S.Specialist_ID, S.FullName, COUNT(B.HealingInformationID)
NumberOfBookedReservation

INNER JOIN [Booking].[Booking] B ON
H.HealingInformation_ID=B.HealingInformationID
GROUP BY S.Specialist_ID, S.FullName
ORDER BY NumberOfBookedReservation DESC
Find the patient who has not made any reservation

The system will run the query below to display the patient who has not made any
reservation.
SELECT P.FullName
FROM [Account].[Patient] P
WHERE P.Patient_ID NOT IN(SELECT B.PatientID FROM [Booking].[Booking] B)
Find the specialist who has not posted any reservation

The system will run the query below to display the specialist who has not posted
any reservations.
SELECT S.FullName
WHERE S.Specialist_ID NOT IN(SELECT H.HealingInformation_ID FROM
[Booking].[HealingInformation] H)
Find the disease for which the patients have the most
The system will run the query below to display the disease the patients have the
most.
SELECT TOP 1 D.Name, D.Description, COUNT(PR.ResultID) NumberOfDoneTest
FROM [Disease].[Disease] D
INNER JOIN [Test].[Test] T ON D.Disease_ID=T.Test_ID
INNER JOIN [Test].[Result] R ON T.Test_ID=R.TestID
INNER JOIN [Account].[PatientRecord] PR ON R.Result_ID=PR.ResultID
GROUP BY D.Name, D.Description
ORDER BY NumberOfDoneTest DESC
4. Application Java structure

4.1 Project structure

For this project, the group has decided to use IntelliJ IDEA as an IDE to develop the
application, which members have frequently used, and excellent community support when
they get stuck. This IDE also provides tools like SQL Console inside, Terminal, and
Debugging Tool for testing and assurance purposes when tackling hard-to-achieve tasks.
When we first start the project, IntelliJ IDEA auto-generates the structure for the
project, which is in the following figure:
Figure 3. The project structures in IDE

We can summarize the folders and files' purpose as follows:
o .idea: for storing IDE-specific project configurations (JDK level, the pattern,
the structure, and many more).
o out: the compiled code to run the application.
o sql: contains the SQL files used for database creation.

o src: the project's source code mainly contains the class and frm files used
by G.U.I. Designer.
o .gitgnore, mindfulness.iml, README.md: used for project setup
on GitHub Version Control System and provide based configuration per
developers.
4.2 Class structure
Taking a closer look at each class used, we have the following UML Diagram to show the
dependence of each class on others and provide a quicker view of the methods
implementations
Figure 4. UML Diagram of the project

We can declare the class purpose as follows, as their corresponding class controls
each frame, which we will discuss later in the G.U.I. parts:

o ConnectSQL.java: manages all the databases connection and return

result from the query.
o Main.java: manages the main() function; we use this class to start the
application process.
public class Main {
public static void main(String[] args) {
frmIndex.getInstance().setVisible(true);
}
}
o frmBooking.java: manages the BOOKING frame and logic for buttons

inside it for reservation booking functions.
o frmExplorer.java: manages the EXPLORER frame and logic for
buttons inside it for admin query functions.
o frmIndex.java: manages the INDEX frame and logic for buttons inside
it for signup/login functions. This returns values to use in other methods as
well
o frmPatientDashboard.java: manages the PATIENT DASHBOARD
frame and logic for buttons inside it for patient users.
o frmPatientSign.java: manages the PATIENT SIGNUP frame and
logic for buttons inside it for patient registration.
o frmRoles.java: manages the ROLES frame and logic for buttons inside
it for the role chooser function.
o frmSignDone.java: manages the SIGNUP DONE frame and logic for
buttons inside it to take the user back to the index frame.
o frmSpecialistDashboard.java: manages the SPECIALIST
DASHBOARD frame and logic for buttons inside it for specialist users.
o frmSpecialistSign.java: manages the SPECIALIST SIGNUP
frame and logic for buttons inside it for specialist registration.

o frmTest.java: manages the TEST frame and logic for buttons inside it
for patients to take tests in the system.
4.3 Connection implementation
First, we go to the www.freeasphosting.net website to create a remote database like
the figure below:
Figure 5. Free database hosting website for the project
Then we connect to the database (for use in the IDE) using the information provided
and run the CREATE DATABASE, CREATE TABLE, and INSERT INTO queries
above to begin initializing our database.

Figure 6. Testing connection with the database
After doing all the creation, it is time to implement our ConnectSQL.java class to manage
all the queries used in the project. First is to import the JDBC Driver using and setting the
connection URL of:
import java.sql.*;
import net.proteanit.sql.DbUtils;
static final String connectionUrl =
"jdbc:sqlserver://sql.bsite.net\\MSSQL2016;databaseName=congbang0711_;user=con
gbang0711_;password=mindfulness;encrypt=true;trustServerCertificate=true;";

Then, there are some methods to help with closing connections and displaying the
result in tabular form:
public static void closeConnect(Connection con) {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
System.out.println("Error closing connection");
}
}
}
private static TableModel resultSetToTableModel(ResultSet rs) {

try {
ResultSetMetaData metadata = rs.getMetaData();
int numberOfColumn = metadata.getColumnCount();
Vector<String> headline = new Vector<>();
for (int i = 0; i < numberOfColumn; ++i) {
headline.addElement(metadata.getColumnLabel(i + 1));
}
Vector<Vector<Object>> content = new Vector<>();
while (rs.next()) {
Vector<Object> row = new Vector<>();
for (int i = 1; i <= numberOfColumn; ++i) {
row.addElement(rs.getObject(i));
}
content.addElement(row);
}
return new DefaultTableModel(content, headline) {
@Override
public boolean isCellEditable(int row, int column) {
// all cells false
return false;
}

};
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
Finally, we can make the methods that use the queries above by following the
structure grouped by and call these methods when the button is clicked when checking the
return have any value ("How to check if ResultSet is empty in JDBC Java - Example
Tutorial," n.d.):
o Standard queries (can return any data based on the results)
public static ArrayList<Integer> showTestQuery() {
Connection con = null;
PreparedStatement stmt;
ResultSet rs;
ArrayList<Integer> results = new ArrayList<>();
try {
con = DriverManager.getConnection(connectionUrl);
String preparedQuery =
"""
SELECT Test_ID AS ID
FROM [Test].[Test]""";
stmt = con.prepareStatement(preparedQuery);
rs = stmt.executeQuery();
while (rs.next()) {
results.add(rs.getInt("id"));
}
throw new RuntimeException(e);
} finally {
closeConnect(con);
}
return results;
}

o Parameterized queries (can also return any data based on the result)
public static String showSearchQuery(String queryTxt) {
ResultSet rs;
StringBuilder result = new StringBuilder();
try {
String preparedQuery =
"""
SELECT S.Name AS symptom, So.Name AS solution, So.Platform,
So.Description FROM [Disease].[Disease] D INNER JOIN
[Disease].[DiseaseSymptom] DS ON D.Disease_ID = DS.DiseaseID
INNER JOIN [Disease].[Symptom] S ON DS.SymptomID =
S.Symptom_ID
INNER JOIN [Solution].[CureOneByOne] C ON S.Symptom_ID =
C.SymptomID
INNER JOIN [Solution].[Solution] So ON C.SolutionID =
So.Solution_ID WHERE D.Name = ?""";
stmt = con.prepareStatement(preparedQuery);
stmt.setString(1, queryTxt);
rs = stmt.executeQuery();
while (rs.next()) {
result
.append("Possible symptom: ")
.append(rs.getString("symptom"))
.append(" has the solution of: ")
.append(rs.getString("solution"))
.append(". Please see at: ")
.append(rs.getString("platform"))
.append(", more details: ")
.append(rs.getString("description"))
.append("\n\n");
}

throw new RuntimeException(e);
} finally {
closeConnect(con);
}
return result.toString();
}
o Update queries (return true/false Boolean value for system to notify user)
public static boolean submitPasswordUpdate(String userTxt, String oldPwdTxt,
String newPwdTxt) {
int rs;
boolean isUpdated = false;
try {
con.setAutoCommit(false);
String updateString =
"""
DECLARE @oldPwd AS VARCHAR(30) = ?
DECLARE @newPwd AS VARCHAR(30) = ?
S.E.T. @Salt
= CONCAT(
CHAR(FLOOR(RAND() * 10) + 48),
CHAR(FLOOR(RAND() * 10) + 48))
UPDATE [Account].[Account]
SET Password = HASHBYTES('SHA1', CONCAT(@newPwd, @Salt)),
Salt = @Salt
WHERE User_name = @UserName

AND Password = HASHBYTES(

'SHA1', CONCAT(@oldPwd, (SELECT A.Salt FROM
[Account].[Account] A WHERE A.User_name = @UserName)))
""";
stmt = con.prepareStatement(updateString);
stmt.setString(1, userTxt);
stmt.setString(2, oldPwdTxt);
stmt.setString(3, newPwdTxt);
rs = stmt.executeUpdate();
if (rs > 0) {
isUpdated = true;
}
con.commit();
return isUpdated;
} finally {
closeConnect(con);
}
return isUpdated;
}
o Removal queries (return true/false Boolean value for system to notify user)
public static boolean delistHealingUpdate(String specialistTxt, String
healingTxt) {
int rs;
boolean isUpdated = false;
try {
con.setAutoCommit(false);
String updateString =
"""
DELETE FROM [Booking].[HealingInformation]
WHERE SpecialistID = ? AND HealingInformation_ID = ?

""";
stmt = con.prepareStatement(updateString);
stmt.setString(1, specialistTxt);
stmt.setString(2, healingTxt);
rs = stmt.executeUpdate();
if (rs > 0) {
isUpdated = true;
}
con.commit();
return isUpdated;
} finally {
closeConnect(con);
}
return isUpdated;
4.4 G.U.I. Design – Frames implementation

Next, we can begin to design the frames for Graphical User Interface directly on
IntelliJ IDEA GUI Designer, as follows:

Figure 7. Create a new G.U.I. Form in IDE
From then, we can drag-and-drop the elements to the JFrame created; some of the
frequent usages are:

Figure 8. G.U.I. Designer Form for making a reservation
o HSpacer, VSpacer: to add spacing between the elements.

o JPanel: to add a frame for other elements, it must be extended from our
frame classes.
public class frmPatientDashboard extends JFrame
o JButton: add a button with ActionListeners to perform logical code

when clicking (discuss more in the later part).
o JScrollPane: add a frame that can be scrollable, especially for long
content.
o JLabel: to add title and instruction on the interface.
o JTextField, JPasswordField: to receive user input on the interface;
the latter is used for the password field to protect privacy.
txtField.getText()
txtField.setText("");
String.valueOf(pwdField.getPassword())
pwdField.setText("");

o JTextArea: to display the message after the query has been done.
txtArea.selectAll();
txtArea.replaceSelection("");
txtArea.setText("");
txtArea.setEditable(false);
o JComboBox: to make a drop-down list for the user to choose from.

comboBox.setModel(new DefaultComboBoxModel<>(questions));
o JTable: to display the query result in tabular form.

table.setModel(Objects.requireNonNull(resultSetToTableModel(rs)));
o JOptionPane: to display the dialog for the user to interact with.

JOptionPane.showMessageDialog(
null, "Field(s) are empty!", "Warning", JOptionPane.WARNING_MESSAGE);
null,
"Account: "
+ frmIndex.getInstance().getCredentials()[0]
+ " registered successfully. Thank you!",
"Success",
JOptionPane.INFORMATION_MESSAGE);
int option =
JOptionPane.showConfirmDialog(
null,
"Please check the information carefully!",
"Confirmation",
JOptionPane.YES_NO_OPTION,
JOptionPane.QUESTION_MESSAGE);
JPasswordField masterPwd = new JPasswordField();

Object[] message = {"Master password: ", masterPwd};
int option =
null,

message,
"Password Required",
To initialize the frames, we will use the constructor provided; with the use of the
Singleton Design Pattern, we can save time by not reloading all the elements again as:
o Default constructor
public frmTest() {
setContentPane(panel);
setTitle("mindfulNESS - Self-diagnosis test");
setSize(1200, 800);
setLocationRelativeTo(null);
setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
}
frmTest frmTest = new frmTest();

frmTest.setVisible(true);
setVisible(false);
o Singleton constructor
private static frmIndex instance;
private frmIndex() {
setContentPane(panel);
setTitle("mindfulNESS - Homepage");
setSize(800, 800);
setLocationRelativeTo(null);
setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
}
public static synchronized frmIndex getInstance() {
if (instance == null) {
instance = new frmIndex();
}
return instance;
}

frmIndex.getInstance().setVisible(true);
setVisible(false);
o Frame class that returns a value also has these methods

public String[] getID() {
return results;
}
o The frame that needs to be loaded with content uses these methods:
@Override
public void setVisible(boolean visible) {
super.setVisible(visible);
if (isVisible()) {
pwdField.setText("");
}
}
All together, they are put inside a class that runs like this with all the variables and
methods for working:
public class frmPatientDashboard extends JFrame {}
4.5 Buttons implementation

As many buttons need to be implemented, we will only show some common
patterns for this button. Note that all buttons have the logic to validate user input (blank,
not long enough, duplicate, etc..) and follow the SwingWorker design pattern:
o Log in, sign up buttons: use user input (account, password) for
authentication and perform the subsequent actions.
o Run the query, Search buttons: to run the query and return the result to
the interface.
runQueryButton.addActionListener(
e -> {
runQueryButton.setEnabled(false);
if (queryField.getText().isEmpty()) {
null, "Please do not leave query blank!", "Warning",
JOptionPane.WARNING_MESSAGE);
runQueryButton.setEnabled(true);

return;
}
SwingWorker<Void, Void> worker =
new SwingWorker<>() {
@Override
protected Void doInBackground() {
ConnectSQL.showQuery(queryField.getText(), resultTable);
return null;
}
@Override
protected void done() {
runQueryButton.setEnabled(true);
}
};
worker.execute();
});
o Place a booking, confirm registering, and Post booking information

buttons: to put the user input into the database.
o Patient, Specialist. Place healing Mode, Explorer Mode, and Self-test
Mode buttons: to set them according to the visible frame and dispose of the
current frame.
healButton.addActionListener(
e -> {
healButton.setEnabled(false);
@Override
frmBooking frmBooking = new frmBooking();
frmBooking.setVisible(true);
setVisible(false);
return null;
}

@Override
healButton.setEnabled(true);
}
};
worker.execute();
});
o Cancel healing, and delist healing buttons: for users to cancel any
reservations they made.
o Answer A, B, C, and D buttons: to show answer content and accompany the
user when doing tests.
o Clear all button: to clear all the fields after confirming dialog.
clearAllButton.addActionListener(
e -> {
int option =
null,
"Are you sure you want to clear all field(s)?",
"Confirmation",
if (option == JOptionPane.YES_OPTION) {
clearAllButton.setEnabled(false);
@Override
fullNameField.setText("");
emailField.setText("");
dobField.setText("");
genderField.setSelectedItem("<please choose>");
return null;
}

@Override
clearAllButton.setEnabled(true);
}
};
worker.execute();
}
});
o Reset password button: to the reset password function.

o Refresh button: to refresh the results if the system fails to do so.
o Log out button: to log out of the application and end that program session.
logOutButton.addActionListener(
e -> {
int option =
null,
"Are you sure you want to log out?",
"Confirmation",
logOutButton.setEnabled(false);
@Override
try {
setVisible(false);
null,
"Logged out! See you again.",
"Success",
JOptionPane.INFORMATION_MESSAGE);

Thread.sleep(1000);
System.exit(0);
} catch (InterruptedException ex) {
throw new RuntimeException(ex);
}
return null;
}
@Override
logOutButton.setEnabled(true);
}
};
worker.execute();
}
o Go back button: to go back to the previous frame.

goBackButton.addActionListener(
e -> {
int option =
null,
"Are you sure you want to go back?",
"Confirmation",
goBackButton.setEnabled(false);
@Override
frmPatientDashboard.getInstance().setVisible(true);
setVisible(false);
return null;

@Override
goBackButton.setEnabled(true);
}
};
worker.execute();
}
});
4.6 Application Demo – Screenshots
Figure 9. Login interface for the first time running

Figure 10. Specialist account registration successfully

Figure 11. Patient dashboard with the search result
Figure 12. Change user password successfully

Figure 13. Making a reservation successfully
Figure 14. Test-taking in progress

Figure 15. Score announcement

Figure 16. Specialist posting reservation successfully
Figure 17. Delist reservation successfully
Figure 18. Administrator mode for debugging purposes
IV. CONCLUSION
1. Achieved goals

During the project, our team successfully achieved the goal of developing the mental
healthcare system. Firstly, we designed the database to adhere to the requirements of B.C.
Normal Form, ensuring efficient data storage and retrieval while eliminating data anomalies.
Secondly, we connected the front-end interface of the application to the back-end database
using a Java connection driver, establishing a seamless and secure connection between the
user interface and the database. Thirdly, we developed essential functions such as login,
account creation, and booking, implementing complex queries to manage various scenarios
effectively.
Additionally, we incorporated data evaluation techniques to give users more precise
predictions and recommendations for their mental health. Finally, we implemented robust
security measures in the database to safeguard users' data and prevent unauthorized access,
including protection against SQL injection attacks. Through completing these goals, our
project has delivered a reliable and user-friendly mental healthcare system that meets the
requirements of modern database management, security, and user experience.
2. Future work
The achievement of these goals opens exciting possibilities for the future evolution of this
project into a fully-fledged real-world application. Our team is committed to enhancing the
user experience by implementing more advanced features. We will introduce functionalities
that allow users to update their information according to their preferences, expand the role
of specialists to provide additional support and resources and improve the overall interface
of the application. Additionally, we aim to optimize the connection process, reducing user
waiting times. Furthermore, we plan to integrate a scheduling system into the app, enabling
users to conveniently manage their therapy appointments and receive guidance throughout
their treatment journey. We will focus on continuous improvement and delivering a
comprehensive and user-centric mental healthcare solution as we move forward.
3. Concluding thoughts
In conclusion, we are optimistic about the future development and improvement of our
mental healthcare service system. We aspire to make the system even more user-friendly
and practical, focusing on enhancing our users' overall experience. Throughout the project,
our team members have learned valuable lessons in collaboration, communication, and

problem-solving, which will benefit us in future endeavors. We sincerely thank our

esteemed lecturers for their guidance and support, without which this project would not
have been possible. We are grateful for the opportunity to contribute to the mental health
field and look forward to positively impacting the lives of individuals seeking support and
care. With a commitment to continuous learning and innovation, we are excited about the
potential of our mental healthcare service system and its ability to improve the well-being
of our users.
V. REFERENCES
1NF, 2NF, 3NF and BCNF in Database Normalization | Studytonight [WWW Document], n.d.
URL https://www.studytonight.com/dbms/database-normalization.php (accessed
5.3.23).
Add a Salt with the SQL Server HASHBYTES Function [W.W.W. Document], n.d. URL
https://www.mssqltips.com/sqlservertip/3293/add-a-salt-with-the-sql-server-
hashbytes-function/ (accessed 5.3.23).
Depression [W.W.W. Document], n.d. URL https://www.who.int/news-room/fact-
sheets/detail/depression (accessed 2.27.23).
FREE Mental Health Tests, Quizzes, Self-Assessments, & Screening Tools [W.W.W. Document],
2022. URL https://www.psycom.net/quizzes (accessed 2.26.23).
How to check if ResultSet is empty in JDBC Java - Example Tutorial [W.W.W. Document], n.d.
URL https://javarevisited.blogspot.com/2016/10/how-to-check-if-resultset-is-empty-
in-Java-JDBC.html#axzz80XaDldWk (accessed 5.3.23).
Lesson: JDBC Basics (The JavaTM Tutorials > JDBC Database Access) [W.W.W. Document], n.d.
URL https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html (accessed
5.3.23).
Ltd, R.G.S., n.d. Free Online SQL Formatter [W.W.W. Document]. URL https://www.red-
gate.com/website/sql-formatter (accessed 5.3.23).
SQL Tutorial [W.W.W. Document], n.d. URL https://www.w3schools.com/sql/ (accessed
5.3.23).
Suicide mortality rate (per 100,000 population) | Data [W.W.W. Document], n.d. URL
https://data.worldbank.org/indicator/SH.STA.SUIC.P5 (accessed 5.3.23).

SwingWorker (Java Platform S.E. 8 ) [W.W.W. Document], n.d. URL

https://docs.oracle.com/javase/8/docs/api/javax/swing/SwingWorker.html
(accessed 5.3.23).
THE END

Group1 Report

Uploaded by

Copyright:

Available Formats

Group1 Report

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Group1 Report

Uploaded by

Copyright:

Available Formats

VIETNAM NATIONAL UNIVERSITY - HO CHI MINH CITY

TOPIC: MENTAL HEALTHCARE SYSTEM

2. NGUYEN QUANG DIEU ITDSIU20031 Team Member

5. HUYNH TRAN KHANH ITCSIU21011 Team Member

Project’s GitHub: LINK 2

Project’s GitHub: LINK 3

Project’s GitHub: LINK 4

Project’s GitHub: LINK 5

4. The techniques & tools used.

II. TASK TIMELINE & DIVISION

2. Project Timeline & Task Division

Project’s GitHub: LINK 6

STAGE ACTION MEMBER WEEK

Project’s GitHub: LINK 7

Setup database tables, their

III. PROJECT ANALYSIS

Project’s GitHub: LINK 8

Project’s GitHub: LINK 9

Figure 1. Primary Crow's Foot Format E.R.D. Diagram

Project’s GitHub: LINK 10

Figure 2. Schema design created by using SQL Server

3.2 Database and Tables Creation

Project’s GitHub: LINK 11

b. Schemas and Tables Analysis

Patient_ID int IDENTITY(1, 1) PRIMARY KEY

Project’s GitHub: LINK 12

Description varchar(200) NOT NULL

Project’s GitHub: LINK 13

From the above table, we can conclude some requirements as follows:

Project’s GitHub: LINK 14

CREATE TABLE [Account].[PatientRecord] ([ID] [int] PRIMARY KEY,

Project’s GitHub: LINK 15

[HealingInformationID] [int] NOT NULL UNIQUE,

Project’s GitHub: LINK 16

[TestID] [int] NOT NULL,

Project’s GitHub: LINK 17

[Benefit] [varchar](50) NOT NULL,

3.3 Database Data Insertion

Project’s GitHub: LINK 18

Project’s GitHub: LINK 19

VALUES(4, 5, N'Nguyen Luan Cong Bang', CAST(N'2002-11-07' AS Date), N'Male',

Project’s GitHub: LINK 20

Project’s GitHub: LINK 21

Project’s GitHub: LINK 22

Project’s GitHub: LINK 23

VALUES(2, 1, N'Feeling down, depressed, or hopeless?', 1)

Project’s GitHub: LINK 24

VALUES(4, N'Nearly every day', 3)

Project’s GitHub: LINK 25

3.4 Database Queries

Project’s GitHub: LINK 26

o DECLARE AS SET: to declare additional variables used in the query

o INSERT INTO VALUES: to insert data into the tables

o SELECT: to show the columns for return

o FROM: to specify the table use

o WHERE: to set a condition for each query

o ORDER BY: to sort the results in ascending order

o GROUP BY: to pack the same data into groups

Project’s GitHub: LINK 27