Secure database

Secure database

1. Security measure

   • Hashing
     • Hashing is a way of turning information into a fixed, short code (a hash) that’s fast to make, easy to check, and hard to reverse.
       • Input: hello Hash (SHA-256): 2cf24dba5fb0a30e26e8 3b2ac5b9e29e1b161 61e5c1fa7425e73 043362938b9824
   • Salt
     • In computing, a salt is a random extra data added to a password before hashing, so hackers cant guess or reverse the hash easily
       • Password: apple123 salt: X7!@ combined: apple123X7!@
   • Caesar cipher
     • The Caesar Cipher is a simple code where each letter in a message is shifted by a set number in the alphabet
       • Plain text: HELLO Cipher text: KHOOR Shift:3

2. User persona data requirements

   • Goals
     • A database that stores client information
       • Providing the company with a database that holds all their client information
   • Needs and expectations
     • Easy navigation for employees and the manager, secure and store all information about their clients
       • Putting all the information into easy to read tables that are clearly labelled and using hashing and Caesar cipher to store secret information
   • Frustrations
     • Their current database is not secure, messy, and employees can’t find what data belongs to which member
       • Provide a newer, improved version of their current database that is easier for them to use and where the information is coordinated to the owner

3. Data types

   • Integer
     • In SQL, an integer is a numeric data types used to store whole numbers, meaning numbers without any fractional or decimal components. These numbers can be positive, negative, or zer0.
       • 55
   • Varchar
     • In SQL VARCHAR stands for variable character and is a data types used used to store character strings of variable lentgths
       • John
   • Float
     • The float data type in SQL is used to store approximate numeric values with floating-point precision. Float allows for decimals.
       • 3.7
   • Date
     • The date data type in SQL is used to store only date values, without any time components. It represents a calendar date and is independent of time zones.
       • 2001-05-23
   • Time
     • In SQL, the time data type is used to store a time of day, without including any date information. It typically represents time based on a 24-hour clock.
       • 07:30:25

4. Suggested data dictionary attributes

   • ID
     • Integer
       • 50
         • Example: 1,2,3
   • Name
     • Varchar
       • 100
         • Example: Will
   • Date_of_birth
     • Date
       • 50
         • Example: 98/07/03
   • Amount_owed
     • Decimal
       • 250
         • Example: 79.50
   • Active_Status
     • Boolean
       • 100
         • Example: Active
   • Gender
     • Varchar
       • 50
         • Example: female

5. Sample data

6. Coding

   • SQL
   • Python

7. Task requirements

   • Prescribed criteria
     • You must use 4 different data types in your data
       • Create a database for the user
       • Create a database for the employees at the gym
       • At least 4 different data types must be used in the table. For example, integer, decimal, float, date, time etc.
       • All 4 data types must be different be from each other
     • You must have 1 column that uses hashing to securely store that information
       • The table must include a column that has the hashed version of the password
       • The password displayed must not be the clients actual password but the hashed version of it
       • The table must use hashing to safely store the clients passwords
     • You musty have 1 column that uses Caesar cipher to store secret information
       • Secret information must be stored in the table
       • Caesar cipher must be used to secure this information so that it cannot be retrieved
       • The Caesar cipher shift must be greater than zero
     • Your database must have at least 5 records
       • The database must have information that is relevant to the company and meets the user criteria
       • All 5 records must be different from each other
       • Each record must have all the essential fields completed (not null)
   • Self determined criteria
     • The database must maintain data integrity
       • Each record must have a unique identifier (e.g. primary key)
       • Constraints (e.g. not null, unique) must prevent invalid entries
       • Data types must match the expected format for each field (e.g. dates, integer, varchar)
     • The table must use salt
       • Every password and sensitive field must be salted before hashing
       • The salt must be randomly generated for each entry
       • The salt and hash must be stored together in one column
     • The table must be easy to read and neatly organised
       • The table must have distinct columns which are all clearly labelled
       • All the column names must be clear and meaningful
       • No duplicate or unnecessary columns should be included