Terminology
We strive to avoid using acronyms and abbreviated terms because they can create barriers to understanding. However, where such terms are used, we have provided clear definitions.
2 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
2
- 2FA
- Two Factor Authentication
- Two-factor authentication (2FA) is a security process in which users provide two different authentication factors to verify themselves. This process is done to better protect both the user's credentials and the resources the user can access. 2FA helps enhance security by requiring two types of information from the user — something they know (like a password) and something they have (like a smartphone app to approve authentication requests).
- Two common smartphone apps are the Google Authenticator and the Microsoft Authenticator.
A
B
- Back End
- In an Access application, the backend typically contains the tables that store the data, while the frontend includes the forms, queries, reports, and other user interface elements that interact with the data.
- It is typically a separate Access database file (with an .accdb or .mdb extension) that contains all the tables needed for the application. This backend database is linked to the frontend database, which contains the user interface components like forms, reports, queries, and VBA (Visual Basic for Applications) code.
- Key advantages of using a separate backend file:
-
- Separation of data: By separating the backend from the front-end, multiple users can access the same data concurrently without interfering with each other’s front-end interface. This simplifies maintenance and updates since changes to the data structure (backend) do not directly impact the user interface (front-end).
- Concurrency: Multiple users can access and manipulate the data simultaneously.
- Scalability: The backend can be upgraded to a more robust database system like SQL Server or PostgreSQL or when the data or user load increases. Using PostgreSQL (open source and free) can reducing licencing costs.
C
- Commodity Currency
- Commodity currency, also known as commodity money, is a type of currency that derives its value from the material it is made of or backed by. This is in contrast to fiat currency, which has value primarily because a government maintains it and people have faith in its value.
- An example of a commodity currency was The United States dollar prior to 1971. It used the gold standard
until 1971 when President Richard Nixon ended the system, moving to a fiat currency system.
- Currently all countries use fiat currencies.
- Client Server Database System
- A client-server database architecture involves a setup where the database management system (DBMS) runs on a central server, and client machines connect to this server to request data or perform database operations.
- The server handles all database processing tasks, such as query execution, transaction management, and data storage, thus offloading the computational burden from the clients. Clients typically use a network connection to communicate with the server, sending SQL queries and receiving results.
- This architecture enhances performance, security, and scalability, making it suitable for larger, more complex applications compared to file server architectures. It allows multiple clients to access and manipulate data simultaneously while maintaining data integrity and reducing network traffic.
D
- Data Cleansing
- Data cleansing (also known as data cleaning or data scrubbing) is the process of identifying and correcting errors, inconsistencies, and inaccuracies in a dataset to improve its quality and ensure it is accurate, complete, and reliable.
- Data cleansing involves various tasks, including the removal of duplicate records, correcting errors, filling in missing data, and standardizing formats.
- Key advantages of data cleansing:
-
- Improved Data Quality: Ensures the accuracy, completeness, and reliability of the data, leading to better decision-making.
- Increased Efficiency: Reduces the time and effort needed to correct errors during data analysis, leading to more efficient workflows.
- Enhanced Data Usability: Clean data is easier to analyze and use in various applications, including reporting and data analysis.
- Better Decision Making: High-quality data supports more accurate and informed decisions, reducing the risk of errors in business processes.
- DCI
- Data Complexity Index
- The Data Complexity Index (DCI), developed by Ferntree Computer Services, quantifies the complexity of Microsoft Access Databases. This metric evaluates the structure and number of data tables, queries, forms, reports, and the extent of VBA code used. The DCI scale ranges from 1 to 5, where a score of 1 represents low complexity and a score of 5 denotes high complexity.
- This index is crucial for accurately estimating the required maintenance or enhancement efforts, thereby aiding in efficient database management and scalability.
- In conjunction with the Well Formed Index (WFI), the DCI provides a comprehensive assessment of the overall effort needed for database projects.
- Dimension Tables
- See Lookup Tables
- DMI
- Database Maintainability Index
- This is a propriety index that puts a quantative measure on how difficult or easy it is do perform maintenance or enhancements on a Microsoft Access Database.
- In order for the DMI to be calculated you need to calculate the Well Formed Index (WFI) and the Database Complexity index (DCI). Both are scored between 1 and 5 where 5 in the Well Formed Index indicates the database is well designed and constructed (well formed). A Data Complexity Index of 5 means the database is very complex.
- The formula used to calculate the Database Maintainability Index is:
- Database Maintainability Index = (6-WFI) * DCI.
- The Database Maintainability Index can range from 1 to 25. The ranges are linear.
- If a database is well formed and has low complexity, it will have a low Database Maintainability Index (easy to work on). If a database is not well formed and has highl complexity, it will have a high Database Maintainability Index (hard to work on).
E
F
- Fact Table
- A fact table is a central table in a database that contains the quantitative data for analysis.
- Fact tables store the measurable events or transactions that occur in the business, such as sales transactions, order details, or financial records. These tables are designed to be large and hold a significant amount of data, which is then used for reporting and data analysis.
- Fact table is often supported by lookup tables.
- Example Fact Table:
-
Sales Fact SalesID ProductId CustomerId SalesDate QuantitySold 1 101 1001 20220101 5 2 102 1002 20220102 3 - Example of a lookup table:
-
Product Lookup ProductId ProductName Category Price 101 8" Frying Pan Kitchen 225.00 102 12" Frying Pan Kitchen 360.00
- Fiat Currency
- Fiat currency is a type of currency that is issued by a government and is not backed by a physical commodity, such as gold or silver. Instead, its value is derived from the trust and confidence that people have in the government that issues it.
- As opposed to Commodity Money.
- File Server Database System
- A file server database architecture refers to a system where the database is stored on a central server, and client machines access the data files over a network.
- In this setup, the file server merely stores the data files, while the processing of database queries and transactions is handled by each client machine.
- This architecture is relatively simple and cost-effective for small-scale applications but can lead to significant network traffic and reduced performance as the number of clients increases because each client must retrieve and process entire data files across the network.
- It's often contrasted with client-server database architecture, where a dedicated database server handles all the processing, significantly improving efficiency and scalability.
- Front-End
- A Microsoft Access Front-End refers to the user interface component of a Microsoft Access application when a database is split into two separate files.
- It includes all the elements that users interact with, such as forms, reports, queries, and macros. The front-end is typically a separate Access database file (with an .accdb or .mdb extension) that is linked to the back-end database, which contains the data tables.
- Key advantages of using a separate front-end file:
-
- Separation of data: The separation from the back-end data file allows for multiple users to access the same data simultaneously without conflicts.
- Customization and Flexibility: Different user groups can have custom front-ends that provide them with specific capability. This can also enhance segmented access to various data elements.
- Data Integrity and Security: By separating the user interface from the data, you can implement better data integrity and security measures. Users interact with the data through controlled forms and reports, reducing the risk of accidental data corruption.
- Enhanced Performance: By offloading the data storage to a back-end database, the front-end can operate more efficiently, especially in multi-user environments.
G
H
I
J
K
L
- Lookup Tables
- A lookup table (also known as a dimension table) is a table in a database that provides descriptive information to complement data stored in a Fact Table.
- Lookup tables are used to describe dimensions; they contain keys, values and attributes.
- Lookup tables are typically small, usually 2 to 3 colums and ranging from a few to several thousand rows.
- For example, the states in Australia could be stored in a lookup table. For example, the first 3 records could be:
-
Key Name Description 1 QLD Queensland 2 NSW New South Wales 3 ACT Australian Capital Territory - This lookup table could then be linked to a grant application that stores the residential state of the
applicant.
- Key advantages of lookup tables:
- Data Integrity: Uses would select data values from the lookup table, usually from a drop-down list, ensuring that only the defined values are entered. This prevents different variations of the data being stored.
- Data Normalization: By storing descriptive information in separate tables, lookup tables reduce data redundancy and promote normalization. This leads to a more efficient and scalable database structure.
- Efficient Query Performance: Lookup tables can improve query performance by reducing the size of fact tables. Storing only keys in fact tables instead of full descriptive information makes them smaller and faster to query.
- Enhanced Performance: The main data table may have tens or hundreds of thousands of records. By using a lookup table, only the key to lookup table is required to be stored in the each record. This can significantly reduce processing times.
- Reusability: Once a lookup table is created, it often can be used in a number of applications without any change. This can significantly streamline your development work.
M
N
- Normalisation
- Normalization is the process of organizing data to minimize redundancy and improve data integrity.
- This process involves structuring a database in a way that ensures data dependencies are logical and efficiently organized. The primary goal of normalization is to eliminate redundant data and ensure that the data is stored logically.
- Key principles of Normalisation:
-
- Eliminating Redundancy: Data should be stored in one place and referenced where needed, reducing duplication and inconsistency.
- Ensuring Data Integrity: By organizing data into related tables, normalization helps maintain data integrity and accuracy.
- Improving Efficiency: Normalised databases are generally more efficient in terms of storage and data retrieval, as they reduce the amount of duplicate data.
- There are varying degrees of normalisation labelled as First Normal Form (1NF) to the Fifth Normal Form (5NF). Achieving the Third Normal Form (3NF) is often considered the most useful level of normalization to quickly gain significant benefits in database design.
O
P
- PostgreSQL
- PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.
- It is known for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of its open-source community.
- Privacy Window
- The privacy window function in web browsers, often referred to as "Private Browsing" or "Incognito Mode," is a feature that allows users to browse the internet without storing certain data on their computers. This mode provides a degree of privacy for the user by not saving the browsing history, cookies, site data, or form inputs on the device after the browser window is closed.
- The use of a Privacy Windows does not give you completel anonymity. This mode does not make you invisible online. Your internet service provider, employer, or the websites you visit can still track your activity.
Q
R
S
- SQL Server
- SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is designed to handle a wide range of data processing applications in corporate IT environments, from small-scale applications with few users to large-scale enterprise applications with many users and complex data management requirements.
- SQL Server is considered a critical tool for businesses needing a reliable, secure, and scalable database management system capable of supporting sophisticated and high-volume applications.
T
U
V
- VBA
- Visual Basic for Applications
- Visual Basic for Applications (VBA) is a programming language developed by Microsoft that is primarily used for automating tasks and extending the functionality of Microsoft Office applications, such as Excel, Word, Access, and Outlook.
- VBA allows users to write scripts and macros to automate repetitive tasks, create custom functions, and even build complex applications within these Office programs. It is a highly capable functional programming language.
- Key features of VBA:
-
- Integration with Microsoft Office: VBA is built into most Microsoft Office applications, providing deep integration and allowing users to automate and customize these applications.
- Automation: VBA enables users to automate repetitive tasks, such as data entry, report generation, and formatting, thereby saving time and reducing errors.
- Custom Functions and Procedures: Users can create custom functions, subroutines, and procedures to perform specific tasks, enhancing the capabilities of the Office applications.
- User Interface Customization: VBA allows for the creation of custom forms, dialogs, and user interfaces within Office applications, improving user interaction and experience.
- Custom Application Development: VBA can be used to create a custome application with its own screens and UI elements that completely hide the Access components and allows the user to do their work without needing to know Microsoft Access. This can include online help and multi-user capability
- VPN
- Virtual Private Network
- A Virtual Private Network (VPN) is a technology that creates a safe and encrypted connection over a less secure network, such as the internet. VPNs establish secure and encrypted connections to provide greater privacy than even a secured Wi-Fi hotspot.
- VPNs are a crucial tool for enhancing online privacy, securing data transmission, and bypassing geographical restrictions on internet content. They are widely used both by individuals and organizations to protect their internet activities from external threats and surveillance.
W
- WFI
- Well Formed Index
- The Well Formed Index (WFI) is a proprietary metric developed by Ferntree Computer Services to evaluate the quality of Microsoft Access Databases. It assesses various aspects such as object naming conventions, coding standards, and visual layout. The index ranges from 1 to 5, with a score of 5 indicating a well-constructed database that adheres closely to best practices.
- This metric is essential for estimating the effort required for maintaining or enhancing the database, ensuring efficient management and scalability.
- WFI is used in conjunction with the Database Complexity Index (DCI) to comprehensively assess the overall effort needed for database work.