Introduction
The current database project was elaborated in order to create a database for saving the screening results in patients affected by infectious diseases. This database project is designed to help nurses track the development of the diseases, record the screening results, and analyze the outcomes in a much easier way. This presentation dwells on the key features of the database. The descriptions of its structure and tables are also present.
The database is normalized and is currently in the 3rd normal form. The primary and foreign keys are thoroughly explained. The author presents the reason and rationale for the creation of the current database project and develops three questions concerning the database plan. There is an extensive explanation of how the database was tested. The database testing results are also presented and explicated. In conclusion, the author discusses the overall success of the project, general implications, and possible ways to improve the performance of the current database.
Reason and Rationale
The database covers the infectious diseases in numerous patients and stores the screening results. On a bigger scale, this is a major advantage for the nurses due to the increased probability of discovering and diagnosing an infection before it harms the patient (Li & Manoharan, 2013). The screening results can be accessed by any authorized nurses and database administrators. Overall, the database project was elaborated to help nurses work more efficiently and increase their performance in terms of diagnosing various infections. Moreover, the use of this database would significantly improve the performance of the organization as a whole (Li & Manoharan, 2013).
The utilization of the reports generated by the database would minimize the amount of resources involved in the stages of the screening process and the process of electronically storing this data (Li & Manoharan, 2013). In other words, the nurses would dramatically save time when addressing the electronic database instead of going through excessive amounts of the papers that were previously written by other nurses. This kind of database also solves the issues of miscommunication and misunderstanding that may arise between nurses by means of storing and presenting all the necessary data (Rob, Coronel, & Crockett, 2014).
Database Structure
The conceptual model of the database is in compliance with the organizational structure of the department (Rob et al., 2014). It does not require any comprehensive information concerning the data on primary and foreign keys, unique fields, and other constraints. This model includes table names and field titles. The basic relations between tables are shown (Rob et al., 2014).
The logical model is more complex that the conceptual. It features column types (data types) and primary keys. This model is intended to help in business analysis, but it does not relate to the actual database creation (Rob et al., 2014). Nonetheless, this model is rather important because it gives an overview of the database in a more specific way and lets the developer spot possible implications.
The physical model is the actual design of the database that is going to be created. It includes all of the info that was gathered (but not used) during the two previous stages (data types, primary and foreign keys, and unique fields). This model represents the data structure as it would look in the database and avoids the utilization of the reserved words in the names of the entities and columns (Rob et al., 2014). This model is also characterized by the highest level of strictness when it comes to the data types.
The concept of the database includes five tables. The following representation comprises the table names and data types for each field. The primary key for the table Nurse is the field Nurse_ID as it is an exclusive value for the reason that there cannot be two nurses with the same identification number. The foreign key for the table Nurse is the field Patient_ID as the relation between the nurse and the patient is essential for the existence of the health care system. Another FK for this table is Screening_ID as it is also important to have the ability to display the screenings performed by a certain nurse. The primary key for the table Patient is the field SSN_Number as it is a unique value and will not be duplicated (Rob et al., 2014). The foreign keys are Patient_ID, Screening_ID, and Infection_ID.
In this case, we will be able to receive an extensive array of the necessary data when organizing a query to the database (Ben-Gan, Sarka, Machanic, & Farlee, 2015). The tables Infection, Screening, and Screening_Result are auxiliary and serve as an asset to support the database and its normalized form.
Database Plan Questions
Throughout the process of the database development, three questions concerning the database plan were elaborated.
- What to include in the database?
- Will staff training be necessary and why?
- How should the database be supported by the developer?
The first question is rather relevant for the reason that the database should be specific and present only the information on infectious diseases, screening results, and outcomes in patients suffering from infectious diseases. This would significantly increase the time that nurses would spend with the patients instead of completing the routine paperwork.
The question of staff training is also critical as it affects the time that nurses spend actually working and helping their patients. Arguably, time is one of the key resources in nursing. It is reasonable to suppose that staff training is obligatory. This would minimize the risks of nursing mistakes during the process of interacting with the database.
The database should be continuously supported by the developer. This may be explained by the fact that any database project requires recurrent updates. The developer should be able to resolve the issues arising within the database and help the users in case if any additional questions appear. Maintaining the database is the utmost responsibility of the developer.
Database View
The view was created for the table Screening. This view is intended to show all the screenings that were successful:
- CREATE VIEW [Successful Screening Outcomes] AS
- SELECT Screening_Status, Patient_Last_Name, Patient_First_Name, Nurse_Last_Name, Nurse_First_Name WHERE Screening_Status = true
- FROM Screening_Result, Screening, Patient, Nurse
- WHERE SSN_Number=Not NULL
The main benefit of the views, in this particular case, is the fact that the developer can easily manage the database GRANTS straight on views, rather than the genuine database tables (Umanath & Scamell, 2014). It is much easier to manage the database if the administrator/ developer knows that only certain nurses/ staff members may access a view. Moreover, views can assist the developer in creating and maintaining backward compatibility (Umanath & Scamell, 2014). In other words, the developer will be able to change the original schema, but the views can make this data unavailable to certain clients that should not be able to access it. Nevertheless, the main disadvantage of the views is that the database manager/ administrator/ developer may lose information concerning the relations within the database and other fundamental data such as primary or foreign keys (Umanath & Scamell, 2014).
Database Testing
The main reason to test the database is to check the data mapping and ACID properties. The developer should make sure that the data travels back and forth between UI and backend properly (Umanath & Scamell, 2014). It is essential to test the ACID properties for the reason that all of the database transactions depend on the atomicity, consistency, isolation, and durability. The process of testing in the case of the current database involved five steps – preparing the environment, running the test, checking the results, validating the outcomes, reporting the findings (Umanath & Scamell, 2014).
In order to develop the test, SQL queries were used (Ben-Gan et al., 2015). The simplest query involved the operator SELECT – SELECT * FROM Nurse. With the intention of testing the database more thoroughly, more complex queries were involved in the testing process. So as to guarantee the data integrity and overall eminence of the database, the developer tested the transactions (BEGIN TRANSACTION TRANSACTION# -> END TRANSACTION TRANSACTION#) and then checked if the transaction was successful by means of SELECT * FROM Nurse AND Patient (Harrison, 2012). The database schema was the next aspect used to test the database.
This includes several important points – primary keys should be created before any other fields, foreign keys should be indexed, field names should possess titles starting with definite characters, and fields with constraints should be present so that certain restricted values would not be introduced. In order to validate the database schema, the developer used another SQL query – DESC Nurse (Feist, 2012).
Another method used to test the database was the utilization of triggers. The prevalent method that was used to test the database was to execute SQL query implanted in the trigger autonomously first and store the outcome (Ben-Gan et al., 2015). The results of the previous step were followed up with the execution of the trigger as a whole. The results were compared so as to assess the database structure. Field constraints were the last features used to test the database. This was the easiest part of the testing, and it was done manually (Umanath & Scamell, 2014). The developer tested the correctness of the implemented constraints and their impact on the schema.
Database Testing Results
The results of the database testing showed that the current database is properly normalized and complies with all the requirements set before the development started (Date, 2012). All transactions were successful and were checked using the SELECT queries. Primary and foreign keys were created in order to comply with the database schema. All database fields are titled properly and do not contain any restricted characters that would negatively impact the database functionality. The trigger that was created to check the database functionality looks like this:
CREATE OR REPLACE TRIGGER nurse_wage_change
BEFORE DELETE OR INSERT OR UPDATE ON Nurse
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
wage_change;
BEGIN
wage_change:= :NEW.wage – :OLD.wage;
dbms_output.put_line(‘Old wage: ‘ || :OLD.wage);
dbms_output.put_line(‘New wage: ‘ || :NEW.wage);
dbms_output.put_line(‘Wage difference: ‘ || wage_change);
END;
Triggers can be utilized as a substitute technique for the implementation of referential integrity constraints. By means of triggers, transactions can be easily stored in the current database and used steadily even if there are impending updates to the present database. Such triggers may as well control the updates that are permitted in the database. When a change happens in the database, the trigger can fine-tune the change to the whole database. In this case, the trigger may also be used for calling stored procedures.
Conclusion
This database project was beneficial in many directions. First, it helped me gain an insight into the database design and properly build the three models (conceptual, logical, and physical) of the database. Second, I realized the mechanisms of triggers and constraints that are used in the current database. The existing prototype of the database covering the screening results in patients suffering from infectious diseases still lacks complexity, but it may be gradually updated and recurrently revised. The present functionality is enough for everyday use, but would not be able to perform multifaceted tasks regarding major JOINs and UNIONs. Overall, the experience was great, the objectives of the project were achieved, and the research questions were answered.
References
Ben-Gan, I., Sarka, D., Machanic, A., & Farlee, K. (2015). T-SQL querying. Redmond, CA: Microsoft Press.
Date, C. J. (2012). Database design and relational theory: Normal forms and all that jazz. Sebastopol, CA: O’Riley Media.
Feist, R. E. (2012). Query processing in database systems. New York, NY: Springer.
Harrison, I. (2012). 100 SQL queries T-SQL for Microsoft SQL Server. New York, NY: Routledge.
Li, Y., & Manoharan, S. (2013). A performance comparison of SQL and NoSQL databases. PACRIM, 3(11), 1-34. Web.
Rob, P., Coronel, C., & Crockett, K. (2014). Database systems: Design, implementation & management. London: Cengage Learning.
Umanath, N. S., & Scamell, R. W. (2014). Data modeling and database design (2nd ed.). New York, NY: Cengage Learning.