MediConnect_Assignment3_Presentation

1.

Information and Communication Technology, Assignment 3: Database Systems and Cybersecurity
MediConnect
Giving the Referral Wallet a real data layer
by Dyussenbekov Amir, Bizhanova Yeldana, Zhanatay Bexultan
Astana IT University, 2026

2.

From prototype to a real database
Amir
In the midterm, MediConnect data were simulated inside the app. This assignment builds the real data
layer.
Referral Wallet
What was missing
What we did
Every GP referral is visible
with its end date. Booking a
specialist starts from the
referral.
Where do referrals, slots,
appointments and the live
queue actually live? In the
midterm the database was
only a box in the
architecture.
Chose a system, designed
7 entities, built a working
PostgreSQL database, ran
real queries and tested its
security.
MediConnect, ICT Assignment 3
02

3.

Five database systems compared
Amir
System
Type
Fit for MediConnect
PostgreSQL
Relational
Strong constraints, transactions, row-level security. Best fit
MySQL
Relational
Good, but fewer integrity and security features than
PostgreSQL
MongoDB
Document (NoSQL)
Flexible, but our data are highly connected and need joins
InfluxDB
Time-series
Built for sensor-like measurements, not for bookings
SQLite
Embedded relational
Great for demos, not for many clients writing over a
network
Sources: PostgreSQL, MongoDB, InfluxDB and SQLite documentation (full links on the last slide).
MediConnect, ICT Assignment 3
03

4.

Our choice: PostgreSQL
Amir
Data integrity
Performance
Scalability
One slot can be booked
only once (UNIQUE). A
referral can only be active,
used or expired (CHECK).
Booking is a transaction: all
or nothing.
MVCC: patients can read
free slots while another
patient is booking. Readers
do not block writers.
Managed service with read
replica and encrypted
backups in Kazakhstan, as
in our midterm network
design.
MediConnect, ICT Assignment 3
04

5.

7 entities, one booking flow
Yeldana
Amber tables are the Referral Wallet flow: referral, then appointment. PK = primary key, FK = foreign key.
MediConnect, ICT Assignment 3
05

6.

What the database enforces by itself
Entity
Key attributes
clinics
clinic_id, name, city, address
doctors
doctor_id, clinic_id, specialty, is_gp
patients
patient_id, iin, email, password_hash, gp_id
referrals
referral_id, patient_id, valid_until, status
Yeldana
No double booking
appointments.slot_id is UNIQUE
Valid referral states
CHECK: active, used, expired
slots
slot_id, doctor_id, slot_start, is_free
appointments
appointment_id, patient_id, referral_id,
slot_id
queue_entries
queue_id, appointment_id, position, delay
MediConnect, ICT Assignment 3
No orphan records
FOREIGN KEYS link every table
06

7.

A working mini-database
Yeldana
PostgreSQL 16, built from the ERD and exported as
mediconnect.sql
7
73
4
1
tables
sample rows
queries
SQL export
1. CREATE TABLE for each entity
2. INSERT 10 to 13 realistic rows per table
3. Run queries a patient needs
4. Export the script (Appendix A of the report)
MediConnect, ICT Assignment 3
07

8.

Demo: find my referral, find a slot
Yeldana
Query 1: lookup. Amir opens the Referral Wallet.
What the patient sees
Query 1: one active referral to a
cardiologist, valid until 10 Nov 2026.
Query 2: three free cardiologist slots at
two clinics, with doctor name and
address.
Query 2: JOIN of slots, doctors and clinics.
MediConnect, ICT Assignment 3
Both answers come from real tables, not
from a simulation.
08

9.

Demo: book, then try to cheat
Query 3: INSERT + UPDATE in one transaction
Yeldana
Integrity check: same slot, second patient
The booking has 3 steps: create appointment, take
the slot, mark referral used.
BEGIN ... COMMIT makes it all or nothing.
The database refuses the second booking even if
the app has a bug.
MediConnect, ICT Assignment 3
09

10.

What can go wrong
Bexultan
Injection
Unauthorized access
Data breach
User text is run as SQL
code. Can return or change
data. A main OWASP risk
category.
Weak passwords, accounts
with too many rights, an
open database port.
Stolen backup, lost disk,
intercepted traffic. Our data
are health-related, so the
harm is direct.
MediConnect, ICT Assignment 3
10

11.

How we protect the database
Bexultan
Measure
In MediConnect
Validation and sanitization
Check email, phone and IIN format before building a query
Parameterized queries
Placeholders ($1) instead of string concatenation
Authentication
Passwords stored only as Argon2id or bcrypt hashes; SCRAM login
Authorization
Separate low-rights API account; row-level security
Updates and patches
Minor releases at least every 3 months; 5 years support per version
Encryption
TLS in transit; disk and backup encryption at rest; data kept in
Kazakhstan
MediConnect, ICT Assignment 3
11

12.

Same query, unsafe and safe
UNSAFE: input glued into the query
Bexultan
SAFE: parameterized statement
Attacker input: x' OR '1'='1
Unsafe: returns all 10 patients with IIN.
Safe: input is only data, returns 0 rows.
MediConnect, ICT Assignment 3
12

13.

Conclusion
Bexultan
What we built
What we learned
Next steps
PostgreSQL database with
7 entities, sample data, 4
working queries and a
tested integrity rule.
Never build SQL from user
text. Give every account
the minimum rights.
Encrypt and update.
Connect the web prototype
to this database through
parameterized queries. Add
row-level security. Legal
check on data localisation.
MediConnect, ICT Assignment 3
13

14.

Sources
PostgreSQL: Constraints: https://www.postgresql.org/docs/current/ddl-constraints.html
PostgreSQL: PREPARE: https://www.postgresql.org/docs/current/sql-prepare.html
PostgreSQL: SSL connections: https://www.postgresql.org/docs/current/ssl-tcp.html
PostgreSQL: Row security policies: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
PostgreSQL: Transactions: https://www.postgresql.org/docs/current/tutorial-transactions.html
PostgreSQL: Concurrency control (MVCC): https://www.postgresql.org/docs/current/mvcc-intro.html
PostgreSQL: Encryption options: https://www.postgresql.org/docs/current/encryption-options.html
PostgreSQL: Versioning policy: https://www.postgresql.org/support/versioning/
OWASP: Query Parameterization: https://cheatsheetseries.owasp.org/cheatsheets/Query_Parameterization_Cheat_Sheet.html
OWASP: Top 10 A03 Injection: https://owasp.org/Top10/A03_2021-Injection/
OWASP: Password Storage: https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html
MongoDB: Data modeling: https://www.mongodb.com/docs/manual/core/data-modeling-introduction/
InfluxDB: Get started: https://docs.influxdata.com/influxdb/v2/get-started/
SQLite: Appropriate uses: https://www.sqlite.org/whentouse.html
Kazakhstan Law No. 94-V on personal data: https://adilet.zan.kz/eng/docs/Z1300000094
MediConnect, ICT Assignment 3
14