Section 4 Database Management.

Data

The collection of raw, unprocessed and unorganised facts.

Information

The result of processed data to produce valuable information to the user, to enable them to make informed decisions.

Data Cycle (Not Definition)

Data → Information → company can gain knowledge → Which can be used to make informed decisions.

Characteristics of Quality Data

Any Random Villain Creates Chaos (Accurate(Verification), Relevant, Valid(Range & Requirements), Current (Up to date), Complete (No missing details))

Validation Technique & Functions

A programming technique used to test that user input is in an acceptable range and ensure that the data meets a set of requirements.

Functions
1.) Prevent program crashing due to invalid input
2.) Improve quality of data
3.) Ensure correct format is entered.

VERIFICATION of valid data is important SINCE validation does not ENSURE the data is factually correct

Range Check

Testing if the data is within certain boundaries

Format Check

Testing if the data is an acceptable pattern/structure.

Presence Check

Testing if the user entered a value or selected an option for compulsory fields *

Data Type Check

Testing if the user entered the correct data type expected for input

Check Digit

A method used to determine if a number is valid, where the last number/character is the Check digit.

Database

A database is a structured set of related data.

Entity

Represents real world topics about which data needs to be stored.

Table

Collection of data about an entity, arranged into rows - called records and columns called fields |

Records

Records consist of a collection of fields about a single entity

Fields

A field is a single characteristic entity which has a certain data type.

Field Data Types Examples

Short Text (A string of characters , Max 255 which should be changed)
Number (Integers or Real(Double))
Currency (Monetary Values)
Date/Time
Yes/No
AutoNumber (Generates unique sequential numbers)

Primary Key (PK)

A field that uniquely identifies each record in a database table.

Function : Prevent duplicates with unique identifying records (No incomplete data (Null))

Alternate Key

A field that that could be PK but is not set as the PK.

Foreign key

A field in a table that links to a field in another table to create a relationship between the two tables.

Composite Key/ Compound Key

A PK consisting of more than 1 field in combination to uniquely identify records in a table

Referential Integrity & Function

A database design concept where the data in the FK field of a secondary table refers to an existing primary key value for a record in another table.

Function:
1.) Must delete a record in both tables to delete in 1
2.) No record in child table can exist without not being present in parent table.

To Ensure RI a FK:
1.) Not be Empty
2.) Must refer to an existing PK value in parent table.

Relational Database

A database that contains multiple tables that are linked together through a relationship using COMMON keys.

ERD (Entity Relationship Diagram) & How to create

ERD is a visual illustration used to describe the relationship between two tables.

How to create:
1. Choose a noun of data being stored in tables and put in the blocks
2. Choose line type being one and only only or one oy many.
3. Add a verb above the line to describe relationship

Data Integrity & How to achieve [3]

The overall accuracy, consistency, completeness and validity of data stored in a database.

To Achieve:
1.) Normalise the database
2.) Ensure referential integrity
3.) Apply validation techniques

Data Independence & Advantages [3] & Types [2]

Is the property of DBMS that allows the database to be changed without having to change the software that accesses the database.

Advantages :
1. Improves data security
2. Reduces cost
3. Ensures quality data

Types :
1.) Physical independence - Allows the storage location of the database to be changed without it affecting the software accessing the database.
2.) Logical independence - Allows the fields, tables and relationships in the database to change, without having to rewrite the software that accesses the database.

Data Security & Measures (Physical and not physical)

The processes and controls put in place to prevent unauthorised access to data to keep it safe, private and available in case of disaster.

Physical Measures:
1. Biometric Security
2. NFC
3. RFID

Non Physical Measures:
1. Firewall
2. MFA
3. Anti Virus Software

Biometric Security & Adv Disadv

Using biometric scanners to scan the unique physical features of a person, to allow a person access to a secure system

Advantages:
1. More secure than passwords
2. Quicker to log in

Disadvantages:
1. Expensive to install
2. Fingerprint scanners could spread viruses and bacteria

NFC (Near Field Communication)

NFC is a set of communication standards that allows peer to peer data exchanges between devices when placed within a few centimetres of each other

RFID (Radio Frequency ID) & Its components

Using radio waves to read a unique identifier saved on a tag that is attached to an object.

Components:
1. Antennae - Length determines distance of tag reading
2.) Integrated microchip to store ID number
3.) Active Tags include a battery while passive tags receive power from the readers antennae that transmit electromagnetic fields

1.) The reader is used to read data on tag using SQL.

Firewall

Hardware/Software regulating the incoming and outgoing network traffic by blocking unauthorised traffic based on a set of security rules

MFA (Multi Factor Authentication)

A security technology used to authenticate the user by using 2 or more forms of ID when the user logs in.

Anti Virus Software

Utility software that detects and removes malicious software from a computer.

Insert Anomaly

An error that occurs when a new record cannot be added to a table dur to other unnecessary data that also needs to be added to the record.

Delete Anomaly

An error that occurs when it is impossible to delete data from a table without also deleting other data that should not be deleted

Update Anomaly

An error that occurs when a change needs to be made to data in a table but that same change is not applied to the other related records

Reason for anomalies

1.) Table is not normalised and all its like reasons.

Normalisation & Rules & Advantages

A database design concept that prevents data redundancy and ensures data integrity by effectively organising data into fields and tables.

Rules:
1.) Each table must contain fields related to 1 entity.
2.) Each field must store one value
3.) Each table must have a PK
4.) No redundant fields should be present

How to Enforce:
1.) Split into separate tables for separate ideas/concepts.
2.) So we can easily sort, search and edit
3.) -
4.) Fields that can be calculated from other fields.

Advantages
1.) Increased accuracy of data
2.) Queries are faster and more accurate
3.) Prevents anomalies
4.) Removes redundant data

Entity Integrity

Is the concept that each record is uniquely identified by a non null primary key in a database table.

How to ensure:
Allocate a primary key in each table.