Secure Database

Secure Database

1. Prescribed Criteria

   • You must use 4 different data types in your table
     • I will use the VARCHAR data type to store long variables of data.
     • I will use the INTEGER data type to store numerical data.
     • I will use the DATE data type to record when the patient enters the hospital.
     • I will use the DECIMAL data type to store decimal data.
   • You must have 1 column that uses Hashing to securely store that information
     • Use hashlib/sha256 in python to hash data.
     • Have one column that has hashed data for all records.
   • Your database must have at least 5 records
     • Insert 5 different complete records into the database as an example to the client.
   • You must have 1 column that uses Caesar Cipher to store secret information
     • Use the Caesar Ciphering code in python to encrypt data.
     • Have one column of data that is encrypted for all records.

2. Self-Determined Criteria

   • I will create an internal database for John Cade so that he can easily store and access personal data of his current and past patients.
     • Can easily enter and store data about patients.
     • Is an intuitive system that is easy to understand and can retrieve patients’ data.
     • Can easily hash and encrypt patients’ personal information

3. Security Measure – Hashing, SALT, Cipher

   • Hashing
     • January --> 37082e68df858e0ba76442174128811135890ae4c2c5df8b6f31aef5885d0be7
     • Hashes data using a predetermined pattern
     • Easy to break into through brute forcing if it is a known sha (sha256)
   • Hashing + SALT
     • January --> 77838ede8593f3a813e8952d9511d72329bc51693e03a5877fb7daf116e09072:adcce062f2143c7108166b0e975d25fbe4ea36bd0bdbe17bf237c7fb26bfeb5d
     • Adds randomness to the hashing
   • Cipher
     • Uses ciphers to encrypt data that can be easily reverted back if the shift is known.
     • dog --> fqi

4. User Persona Data Requirements

   • Patient_Records
     • Injury/disease
       • Hashed for privacy from other nurses doctors, and others who gain access to the records.
     • last name
       • Encrypted for privacy purposes until needed.
     • Health Conditions
       • Contains previous and current health conditions that are encrypted until needed.
   • Nurse_Doctor
     • Status
       • Allows for privacy within the system when hashed.
     • Last name
       • Encrypted for privacy in case of breach of data and against patients and other staff.

5. Data Types

   • Date
     • 2025-06-18
   • VARCHAR
     • bob@bob.com
   • PRIMARY KEY AUTOINCREMENT
     • 1, 2, 3
   • Float
     • 4.3, 8.0
   • CHAR
     • Charlie
   • INTEGER
     • 1, 178
   • DECIMAL
     • 5.879, 3.14159

6. Suggested Data Dictionary Attributes

   • healthcare_provider
     • VARCHAR NOT NULL
     • Bupa
   • phone_number
     • 0406877546
     • INTEGER UNIQUE NOT NULL
   • age
     • INTEGER NOT NULL
     • 18
   • amount_pending
     • FLOAT NOT NULL
     • 599.99
   • last_name
     • Coggins
     • CHAR NOT NULL
   • first_name
     • CHAR NOT NULL
     • Will
   • patient_id
     • PRIMARY KEY AUTOINCREMENT
     • 1
   • date_arrived
     • DATE NOT NULL
     • 2025-07-09
   • credit_debit_no
     • 1234567890987654
     • INTEGER UNIQUE NOT NULL
   • health_conditions
     • VARCHAR
     • Asthma
   • gender
     • CHAR NOT NULL
     • M
   • room_no
     • INTEGER NOT NULL
     • 105

7. Sample Data – An Example Database Record

   • 'Students'
     • id
       • 1
     • firstname
       • William
     • lastname
       • Coggins
     • gender
       • M
     • DOB
       • 10-03-2010
     • email
       • wcogg1@eq.edu.au
     • SDP
       • 9M

8. Coding - SQL, Python

   • Python
     • The language we use to hash, cipher and insert data into SQL
     • c.execute("INSERT INTO hashtest(username, password, email) VALUES (?, ?, ?)", (username, hashed_password, hashed_email))
     • Thonny
   • SQL
     • The coding language we use to create databases.
     • SQLite3
     • sqlite> .open Hashtest.db