9-2 Final Project Submission: Law, Ethics, Security Plan and Database Management System

Scenario: Westlake Research Hospital

John C. Aghadiuno

Prof. Arend Clayborn

6/7/2020

Contents

• Scenario: Westlake Research Hospital 1
• Organization. 5
• Problem.. 5
• Departments and Operations. 5
• Analysis and Design: Conceptual Model 7
• Analysis and Design: Logical Model 8
• Analysis and Design: Physical Design. 9
• Explanation of Logic for the Models. 10
• Doctor 10
• Drug. 11
• MedicalHistory. 11
• Patient 12
• PatientNote. 13
• Person. 13
• Research. 13
• Study. 14
• StudyVisit 15
• Supervisor 16
• Research. 17
• Analysis. 17
• Oracle. 17
• Pros. 17
• Cons. 18
• MySQL.. 18
• Pros. 18
• Cons. 18
• DB2. 19
• Pros. 19
• Cons. 19
• Microsoft SQL Server 20
• Pros. 20
• Cons. 20
• Recommendation. 21
• Hardware, Software Requirements. 21
• Hardware Requirements. 21
• Software Requirements. 22
• Enterprise Data Model 23
• Operating Rules. 24
• Rule Reflection. 26
• Bibliography. 30

Organization

Problem

Westlake urgently needs a database system to track a double-blind drug study for a new anti-depressant. The study will involve twenty doctors and 400 patients.

The patients will be divided into two groups, with two hundred making up the control group and the other 200 making up the experimental group. The control group will receive the new antidepressant under study, while the experimental group will receive the traditional antidepressant Prozac.

Two supervisors will know who is getting which drug, while neither the doctors nor the patients will know. The test is expected to last about 18 months, with each doctor managing 20 patients. Some patients are expected to drop out of the study before the study is completed.

Each patient will be scheduled to come in twice a month for a routine checkup and interviews with their assigned doctor.

To track this study, the hospital needs a database to store and track patients’ information from their first screening through each of their interviews. Of interest to the doctors will be signs of adverse side effects on the patients. They will also be checking to see if symptoms get worse or better.

Departments and Operations

Access to the information in the database will be based on the following roles and business rules:

• Doctors need to be able to see only their own patient’s information.
• Doctors will need to be able to enter data about their patient. Data will include information such as the depression indicators, blood pressures, blood test results, their own notes, etc. for each session.
• Patients will have access only to their own medical information, including doctor’s notes.
• The two researchers will have access to all data and will be able to see everything: all patient information, all doctors’ notes, and which drug each patient is being administered.

Because healthcare information is protected by privacy laws, building a secure database is necessary. Implementation of strict security protocols and access control is important in guarding against not only the competition, but also the safety of patients’ personal information and the integrity of the study. The database therefore needs to be secured from unauthorized access both internally and externally.

Analysis and Design: Conceptual Model

Analysis and Design: Logical Model

Analysis and Design: Physical Design

 

 

 

Explanation of Logic for the Models

Westlake Research hospital database will be used to track a research study of a new depression drug. The stakeholders include doctors, patients, and supervisors.

To achieve this, Microsoft SQL Server is used to create the database and store its objects and data. Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications – which may run either on the same computer or on another computer across a network (Microsoft SQL Server).

The database uses a highly relational table structure, mostly with one-to-many relationships, to store and arrange data. These table objects are organized as a set of tables with rows and columns.

Each column in a table holds data that is relevant to the study, and a field stores the actual value of an attribute. The rows in the table represent a collection of related values of one entity. Each row in a table has a unique identifier called a primary key, and rows among multiple tables are made “related” using foreign keys (What is a Relational Database?).

Because of this structure, data can be accessed in many ways through joins and views on other tables without reorganizing the database tables themselves.

The Westlake Research hospital database has the following tables, listed below, with a description of each.

Doctor

This table will be used for storing information about the doctors participating in the study. This table has foreign keys to both the Person table and the StudyVisit table.

 

Drug

Information about the drug under study is stored here and the table has been designed to store basic information such as the drug id, name and notes. This table is referenced by a foreign key to the Study table.

MedicalHistory

The MedicalHistory table is used for storing medical information about each client based on Westlake Hospital’s existing practices. The table structure mirrors, and will capture, the same information that is currently done with paperwork by the staff of the hospital. This information include allergies, lung disorders, and other medical history of patients. The table has a foreign key to the Patient table.

Patient

The Patient table holds columns specific to patients, such as Patient ID, Medical Record Number, etc., while linked back to the Person object through it’s foreign key.

PatientNote

The PatientNote table is used for storing arbitrary information about the patient during the study. This will be helpful in storing patient information and observations by the doctor after each visit.

Person

The person table is the table that all persons and entities associated with people, inherit from. For example, the Supervisors, Doctors and Client tables inherit certain common attributes and values such as first and last name, date of birth, etc. from the Person table.

Research

The research table is the primary table, at the top of the relational hierarchy from which all other tables inherit from. It contains columns for storing information about the research, such as research name, start and end dates, etc. It also includes a column that serves as the primary key for each row of columns in the table. The primary key uniquely identifies each row of records.

 

Study

The study table stores information about the study and is also one of the most important tables in the relational structure. In some ways, it is the table that ties other tables together. The table is referenced by foreign keys to the research, Patient and drug tables, as well as columns for storing start, end dates and notes associated with the study.

StudyVisit

One of the most important tables, the StudyVisit table is designed to store patient metrics under observation during the study. Each time the patient visits, the doctors will use this information to determine if the study or the patient is going as expected.

Supervisor

The supervisor table contains objects for storing the supervisors of the study including the specific research they are associated with, along with the start and end dates.

 

 

Research

There are several databases available that would carry out the needs of Westlake Research Hospital and choosing one over the other is not a simple task. Several factors would need to be considered, including features, costs, compatibility and ease of use and maintenance.

Among the plethora of database systems available, the most popular remain Oracle, MySQL, DB2 and Microsoft SQL Server. They are all ACID-compliant, meaning that they meet the requirements for Atomicity, Consistency, Isolation and Durability.

Most importantly, they are all relational databases. A relational database is a type of database that stores and supplies access to data that are related to one another. Relational databases are based on the relational model, a way of representing data in tables. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points (Oracle, n.d.).

We will analyze and evaluate each of the databases and decide on which is best for our project.

Analysis

Oracle

 Pros

• Oracle database management tools are incredibly robust.
• Sophisticated security system that supports groups or roles. There is also the ability to deny a privilege in Oracle.
• Has several external authentication options like LDAP, Active Directory, etc.
• Comes with true full-text search capabilities.
• Sophisticated administration tools and IDEs for debugging.
• Integration with business intelligence applications.

Cons

• The system can require significant resources once installed, so hardware upgrades may be needed to even implement Oracle.
• High price. The Oracle product can be prohibitive and cost much more than comparable solutions.
• Unlike most relational database systems, Oracle requires deep skills to install and support because of its complex and sophisticated engine.

Ideal for: Large organizations and enterprises that handle enormous database transactions and need a variety of features to support that.

MySQL

MySQL database engine has an easy-to-use interface, and batch commands let you process enormous amounts of data. The system is also incredibly reliable and is not as resource intensive as others.

Pros

• It is available for free.
• It can be made to work with other databases, including DB2 and Oracle.
• There are a variety of user interfaces that can be implemented.
• It offers a lot of functionality even for a free database engine.

Cons

• There is no built-in support for XML or OLAP.
• While support is available for the free version, it is a paid service.
• Does not support fractional part of seconds for time and datetime types.
• Does not support user-defined types.
• Does not support recursive queries.
• Does not support materialized views.
• Does not support roll-back recovery.
• Foreign keys are not supported in most storage engines.
• Does not support functional columns that are calculated as expressions.

Ideal for: Organizations that need a robust database management tool but are on a budget.

 

DB2

Created by IBM, DB2 is IBM’s database engine. It has NoSQL capabilities, and it can read JSON and XML files. It is designed to be used on IBM’s iSeries servers, but the workstation version works on Windows, Linux and Unix. The latest version of DB2 provides improved disaster recovery functions, compatibility, and analytics.

Pros

• It can be hosted from the cloud, a physical server or both at the same time.
• Multiple jobs can be run at once using the Task Scheduler.
• Error codes and exit codes can determine which jobs are run via the Task Scheduler.

Cons

• The cost is outside of the budget of many individuals and smaller organizations.
• Third party tools or additional software is required to make clusters or multiple secondary nodes work.
• Basic support is only available for three years; after that, it’s a paid subscription.

Ideal for: Large organizations that need to make the most of available resources and handle large databases.

 

Microsoft SQL Server

As with other popular databases, several editions of Microsoft SQL server exist depending on your needs and budgets. This database management engine works on cloud-based servers as well as local servers, and it can be set up to work on both at the same time. it is available on Linux as well as Windows-based platforms.

Some of the standout features for the 2016 edition include temporal data support, which makes it possible to track changes made to data over time. The latest version of Microsoft SQL Server also allows for dynamic data masking, which ensures that only authorized individuals will see sensitive data (Dynamic Data Masking, 2019).

 

Pros

• Intuitive interface and easy to use and manage.
• Extremely fast and stable engine.
• The engine offers the ability to adjust and track performance levels, which can reduce resource use.
• Provides access to visualizations on mobile devices.
• It works very well with other Microsoft products.

Cons

• For enterprise editions, pricing may be beyond what many organizations can afford.
• Even with performance tuning, Microsoft SQL Server can resource intensive.
• Ideal for: Large organizations that use several Microsoft products.

Recommendation

Microsoft SQL Server is recommended as the best DBMS product for fully addressing the technological and operational needs of Westlake Research Hospital. SQL Server provides high scalability because it can be used for small projects, such as the drug study, as well as large applications. It can take care of millions of transactions per day. It gives superior performance and high speed while retrieving the data for the application (MySQL vs SQL Server, n.d.).

Hardware, Software Requirements

The minimum hardware and software requirements to install and run SQL Server 2016 and SQL Server 2017 on the Windows operating system are:

Hardware Requirements

Hard Disk	SQL Server requires a minimum of 6 GB of available hard-disk space. Disk space requirements will vary with the SQL Server components you install.
Drive	A DVD drive, as appropriate, is required for installation from disc.
Monitor	SQL Server requires Super-VGA (800×600) or higher resolution monitor.
Memory	Minimum: Express Editions: 512 MB All other editions: 1 GB Recommended: Express Editions: 1 GB All other editions: At least 4 GB and should be increased as database size increases to ensure optimal performance.
Processor Speed	Minimum: x64 Processor: 1.4 GHz Recommended: 2.0 GHz or faster
Processor Type	x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support

 

 

 

Software Requirements

The table in this section lists the minimum software requirements for running SQL Server. The following software requirements apply to all installations:

.NET Framework	SQL Server 2016 (13.x) and later require .NET Framework 4.6 for the Database Engine, Master Data Services, or Replication. SQL Server setup automatically installs .NET Framework.
Network Software	Supported operating systems for SQL Server have built-in network software. Named and default instances of a stand-alone installation support the following network protocols: Shared memory, Named Pipes, TCP/IP, and VIA.

 

 

 

 

Enterprise Data Model

Figure 1- A comprehensive enterprise data model for Westlake Research Hospital

Operating Rules

Patient medical information is collected and stored every time the patient visits Westlake Research hospital during the study. The protocol is to get all vital information about the health and well-being of the patient.

The following processes and rules are followed.

1. Patient

Patient data is retrieved from the Patient table which is joined to the Person table.

1. Patient ID and Medical Record Number is retrieved from the Person table.
2. Patient’s First Name and Last Name are retrieved from the Person table.

Patient information is available to patients, doctors (and assistants) and supervisors.

2. Medical History

Patient’s medical history is retrieved from the MedicalHistory table.

1. Medical Notes is always retrieved first.
2. Other medical information such as blood pressure, allergies, heart problems are also retrieved.

Medical history information is available to patients, doctors (and assistants) and supervisors. Patients have access to their medical records, doctors have access to the patients, and supervisors have access to all data.

3. Study

This is retrieved from the Study table using the Patient ID. Information about the research and drug that the patient is participating in are also retrieved from this table.

1. Research ID is retrieved.
2. Research Name is retrieved.
3. Research Notes is retrieved.
4. Drug ID is retrieved.
5. Drug name is retrieved.
6. Study Notes is retrieved.

Study information is available to Doctors and Supervisors. Doctors have access to all their patient data under their care, while supervisors have unrestricted access to all data.

4. Doctor

Doctor data will be retrieved from the Doctor table. This table is joined to the Person table to retrieve additional information about the person.

1. Doctor Name
   • First Name and Last Name are retrieved
   • Specialty is retrieved

Supervisors have access to all doctor information, while doctors have access only to their own data.

5. Visit

Patients meet with doctors at regular intervals throughout the duration of the study. These visits are pre-determined or by schedule.

1. The date and time are recorded in VisitDate column in the StudyVisit Dates and time MUST be current or future dates.
2. When the patient meets the doctor, the medical practitioners will vital information about the patient and save to the StudyVisit
3. The blood pressure is stored in format the ‘130/96’.
4. Weight is saved a decimal value.
5. Sleep Difficulty is checked or unchecked based on patient responses.
6. Loss of Appetite is checked or unchecked based on patient responses.
7. Loss of Libido is checked or unchecked based on patient responses.
8. Social Anxiety is checked or unchecked based on patient responses.
9. Suicidal tendency is checked or unchecked based on patient responses.
10. Pulse is stored in numeric format.
11. Depression is stored in text format.
12. Doctor’s notes, based on questions and observation of patient, are stored in StudyVisitNotes.

Doctors have access to all their patient data under their care, while supervisors have unrestricted access to all data.

Based on information collected, the doctor will then recommend whether the patient should continue or drop out of the study.

If the doctor decides to drop the patient from further study, the information is stored in the Study table, with date the study ends saved in the StudyEndDate column. Study notes and recommendations are entered into the StudyNotes column.

Rule Reflection

The extent to which my data model reflects the operating rules of the Westlake Research Hospital are as follows:

• Supervisors manage all aspects of the study, including duration of study, patient assignments to doctors, control designations and drug information.
• Patient schedules and appointments must be with the doctor they are assigned to.
• Patient’s appointments can be done during the visits or online.
• Doctors can only view the medical history of their own patients.
• Patients can only see their information and their own medical data.
• While the study is ongoing, doctor must meet the patient periodically at specified intervals.
• Doctors can read and enter medical information about their patients. They can also enter additional notes for each visit.
• After each visit, doctors can recommend if the patient should continue or drop from the study.
• Medical assistants would only take and store vital information about the patients. These include information such as allergies, lung disorder, high blood pressure, heart trouble, nervous disorder, cancer, diabetes, malaria, and depression.

 

                                       Laws, Ethics and Security

Standards

When developing and deploying a database user access rights and roles, user authorization and authentication must be implemented so there is no unauthorized   access to the database. Sensitive and protected patient information must also be in compliance with the standards and regulations of HIPPA (Health Insurance Portability and Accountability Act).

Consider also the Health Insurance Portability and Accountability Act, commonly referred to as HIPAA. This legislation mandates that health care providers protect individual’s health care information, going as far as to state that the provider must be able to document everyone who even so much as looked at their information. HIPAA audits frequently require the examination of the processes used to create, document and review exception reports and logs. When confronted with a HIPAA audit, organizations can be required to produce a list of exceptions to policy, such as, “When were patient records accessed during off hours and by whom?”  Without database auditing software, it is impossible to produce a list of users who looked at a specific row or set of rows in any database (Regulatory Compliance and Database Administration, 2012).

 

 

Legal Compliance

Because Westlake is a research hospital they must comply with government and legal standards for protecting the information of patients according to HIPPA due to the database design. The transfer of information over the network can be compromised. All patient information in a database could be compromised. healthcare facilities use EHR (Electronic health records) as a safe way to enter and store patient information.

 

The Health Insurance Portability and Accountability Act of 1996 established policies and procedures for safeguarding the privacy and security of individually identifiable health information to control fraud and abuse in the health care system. Effectively, any part of an individual’s medical record or payment history is protected by this regulation. Organizations with a footprint in healthcare must carefully manage database systems that contain personally identifiable information and payment information to meet this regulation and must have means to allow individuals to access and request corrections to PHI (HIPAA Database Compliance, n.d.).

 

Ethical Practices

Westlake Research Hospital must also meet the highest ethical standards in handling patient information. It is particularly important during the study that access is only granted to those who should have access to them.  Care must be taken that data integrity is enforced while doing so.

For example, there are some federal and state privacy laws (e.g., 42 CFR Part 2, Title 10) that require health care providers to obtain patients’ written consent before they disclose their health information to other people and organizations, even for treatment.  Many of these privacy laws protect information that is related to health conditions considered “sensitive” by most people (Health Information Privacy Law and Policy, n.d.).

 

Security Needs of Solution

Adequate security must be for the physical and electronic storage and transfer of data. In areas where the data is stored physically, only authorized persons should be allowed access. Care must be taken to restrict access to all storage areas. Security and encryption must also be implemented when data is both at rest and in transit.

 

 

 

 

 

 

Bibliography

Back Up and Restore of SQL Server Databases. (2018, 08 30). Retrieved from Microsoft: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases?view=sql-server-2017

Conger, S. (n.d.). Hands-On Database. Retrieved from VitalSource Bookshelf: https://bookshelf.vitalsource.com/#/books/9780133927078

Dynamic Data Masking. (2019, 05 02). Retrieved from Microsoft: https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15

Health Information Privacy Law and Policy. (n.d.). Retrieved from HealthIT.Gov: https://www.healthit.gov/topic/health-information-privacy-law-and-policy

HIPAA Database Compliance. (n.d.). Retrieved from https://www.liquibase.com/blog/database-compliance-security

Last Name, F. M. (Year). Article Title. Journal Title, Pages From – To.

Last Name, F. M. (Year). Book Title. City Name: Publisher Name.

Microsoft. (2020, 02 19). SQL Server 2016 and 2017: Hardware and software requirements. Retrieved from Microsoft Docs: https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-ver15

Microsoft SQL Server. (n.d.). Retrieved from Wikipedia: https://en.wikipedia.org/wiki/Microsoft_SQL_Server

MySQL vs SQL Server. (n.d.). Retrieved from EDUCBA: https://www.educba.com/mysql-vs-sql-server/

Oracle. (n.d.). Retrieved from What a Relational Database Is: https://www.oracle.com/database/what-is-a-relational-database/

Regulatory Compliance and Database Administration. (2012, 8 21). Retrieved from https://www.dbta.com/Editorial/Think-About-It/Regulatory-Compliance-and-Database-Administration-84231.aspx

SQL Server Indexed View. (n.d.). Retrieved from SQLServerTutorial.Net: http://www.sqlservertutorial.net/sql-server-views/sql-server-indexed-view/

What is a Relational Database? (n.d.). Retrieved from Amazon Web Services: https://aws.amazon.com/relational-database/