Data Retrieval in Healthcare Systems: Let’s Clarify Some Concepts around Databases and Some of the Key Terms
Running a healthcare system does not just involve setting up the right technologies, equipment, and staff, but it also involves the correct and secure management of data. Healthcare data should be made accessible to nurses, physicians, and other associated bodies continuously. PACS analysts and administrators are responsible for the persistent availability of this data to all Picture Archive and Communication System users. This central data accessibility is made possible with the right database system in place. In this course module, we will explore more about the database’s role in the healthcare system and cover some of the key terms in this perspective. The understanding of how healthcare data is stored and utilized by databases is essential in PACS administrator training.
Structured Query Language
To manage healthcare data, information must be compiled in a database. What else can serve this job effectively other than Structured Query Language (SQL). SQL is a standard database programming language that is used in creating, maintaining, and retrieving the relational database. A relational database is a collection of data that includes datasets with defined relationships between them and is organized in the form of tables consisting of rows and columns of data, such as MySQL, Microsoft SQL Server, etc.
Basic Principles of SQL
SQL is basically a standard language for performing data manipulation in a database management system (DBMS). It is used by developers, database administrators, and data analysts to create the database, modify the database (table and index structures), update/delete database rows, retrieve datasets, etc. SQL queries are provided in the form of command statements.
Some of the basic principles of SQL are as follows:
- Data are presented in tables form.
- Data is accessible from the table, column, and primary key.
- A single language is used to define, modify, and view data along with conducting data manipulation.
- A single operation is capable to insert, update, delete, or retrieve data.
In order to gain a better grip on SQL, let’s say you have a list of phone calls made by employees. The data is organized in columns such as employee ID, customer ID, call duration, etc. Now if you want to sort the “record” table by employee ID, then the SQL query will look like this:
That’s it. This way, you have sorted the record table in ascending order considering the employeeID.
Data Analytics and Data Mining
Database is not just used to have an organized place for the data to reside. It also empowers data administrators and analysts to carry out data analytics and data mining tasks.
Data analytics is the process of analyzing databases to pinpoint trends, extract insights, and draw a conclusion. On the other hand, data mining is the process of turning raw data into meaningful data. It is used to discover and extract patterns/structures within the data. The key difference between data analytics and data mining is that the former is meant more for making business decisions, while the latter is meant more for making data more functional.
Once a database is created, data administrators or analysts can use it to conduct data analytics and data mining around that organized data. As an example, consider that you want to count the number of rows in a table. So, the SQL query around it will be:
SELECT COUNT(*) FROM table_name;
Similarly, different SQL queries can be executed to conduct data analytics and mining.
Key Performance Indicators
Key performance indicators are measurable values that reflect the performance with respect to time for some specific goals. KPIs for the database are important to keep track of database performance and optimize them accordingly. Some of the crucial KPIs for the database include:
- Query performance.
- Database connections.
- Database response (average response time per query).
- Number of errors.
- Most frequent queries.
Database Design, Management, and Maintenance
There are plenty of elements to consider when designing a database in order to ensure that it is able to do what it is meant to do. Some of the key points of considering when planning a database design include:
- You should know how much data the database is required to handle. It should also include the future data growth plan.
- You should know how often a database will be used.
- You should pick the right database management system (DBMS) depending on your usage needs. Once you have finalized the DBMS system, then you should fulfill the related hardware needs. For example, Microsoft Access requires a 1.6 GHz, 2-core processor, 4 GB of available space, DirectX 10 graphics card, and similar other requirements.
Once the database is all set, it is the duty of the database administrator (DBA) to manage the database, ensuring its performance, security, and integrity. The DBA is responsible for authorizing database access, monitoring its use, contributing to the planning and development of the database, etc.
Other than managing the database effectively, there is also the need for proper database maintenance. With database maintenance, it is ensured that the database is well organized and clean so that it functions effectively and continuously. Some of the common database maintenance activities include inspecting corruption signs in the database, removing duplicate data, looking for troublesome areas, rebuilding indexes, etc. In short, the goal of database maintenance is to ensure that it functions smoothly for all users.
Relational DB vs. Hierarchical DB
As narrated above, a relational database is a collection of data that includes datasets with defined relationships between them and is organized in the form of tables consisting of rows and columns of data. Common examples of relational databases include Oracle, MySQL, IBM DB2, Microsoft SQL Server, etc.
Pros of Relational DB
- Data can be easily structured into categories.
- It is easy to define relationships between data points.
- Data remains consistent in meaning and input.
- Data is easy to navigate.
- Data is secure because only authorized users can access the data.
Cons of Relational DB
- A large relational DB with more tables makes its performance slower.
- It demands significant physical storage space.
- Database maintenance becomes challenging with the passage of time due to the increase in data.
- It costs a lot to set up and maintain.
On the other hand, the hierarchical database is a database model in which data is organized in a tree-like structure. The data is stored in the form of records, which are connected with each other via links. This database is more suited for situations where the data is based on a solid hierarchy, such as employees involved in a single department. Examples of hierarchical database systems include IBM Information Management System (IMS) and RDM Mobile.
Pros of Hierarchical Database
- It is easy to design and makes adding/deleting new data a quick process.
- It is perfect for systems that involve one-to-many relationships.
- Its tree structure makes it easy to manage large datasets.
Cons of Hierarchical Database
- It requires data be to stored repetitively in different entities owing to the tree structure.
- It is easy to design but difficult to implement.
- There is a lack of structural independence.
- It requires sequential searching, which implies that the DBMS has to go through the whole model to search the targeted data. Overall, it makes queries operation slow.
Other Terms to know about in SQL Databases
There are other few common terms you might hear when talking about SQL databases. Some of the predominant ones are listed below:
- Keys: A key is an attribute or a combination of attributes that helps in identifying rows in a table. Keys are meant to accelerate access to data and also set up links among different tables.
- Normalization: Normalization is a process of database structuring that involves creating tables, setting relationships between tables, and organizing all data in the database.
- Table Joining: Table joining is a process of combining rows or data from two or more tables via the common column present in those tables.
- Performance: Performance or database performance is defined as the rate at which the database provides information to the users.
Having an effective database in place is crucial to smoothly store, manage, and access healthcare data. However, it also requires that you pick the right database management system that is capable of handling the present and future data management needs. Similarly, top-notch database administration and timely database maintenance also play a crucial role in the overall smoothness of the database. At the same time, this information should not scare away a prospective PACS administrator. A good PACS administrator should have a ground level understanding of databases but in most cases, they do not need to manage the DB on a technical level. In most institutions, the vendor or the DBA can serve as the principal subject matter expert.