Month: February 2024

John Aghadiuno SNHU Courses and Academic Evaluations

Southern New Hampshire University Courses and Academic Evaluations

John Aghadiuno – Master of Science in Information Technology Degree

Program Requirements:
1: MS Information Technology Foundation Courses (Complete)

Course Title Needed Term Grade Credits Notes
1. IT-505 Core Technologies 04/03/18:     A

MS Information Technology Major Courses (Complete)

Course Title Needed Term Grade Credits Notes
1. IT-510 Advanced Information Technology 18TW4:     A-
2. IT-511 Object Oriented App Development 18TW1:      A
3. IT-515 Innovation in Info Technology 19TW3:      C
4. IT-520 Technical Communication 18TW5:      A
5. IT-600 Operating Systems 18TW2:      A
6. IT-640 Telecommunications/Networking 19TW4:      A
7. IT-700 Capstone in Information Technology 22TW4:      A

1. IT-650 Principles of Database Design 20TW4:      A
1. IT-625 Info Tech Project/Team Management 22TW3:      A

MS Information Technology Electives (Complete)

Course Title Needed Term Grade Credits Notes
1. IT-655 Database Application Development 20TW5:      A
2. IT-665 Client/Server Systems 21TW3:      A
3. IT-647 Website Construction 21TW1:      A-

MS Information Technology Major Courses
Course

1. IT-510

 Title

Advanced Information Technology

 Needed Term

18TW4

 Grade

A-
2.1T-511 Object Oriented App Develop 18TW1 A
3.1T-515 Innovation in Information Technology 19TW3 c
4.1T-520 Technical Communication 18TW5 A
5.1T-600 Operating Systems 18TW2 A
6.1T-640 Telecommunications/Networking 19TW4 A
7.1T-700 Capstone in Information Technology 22TW4 A
 

1.1T-650

  

Principles of Database Design

  

20TW4

  

A
IT-625 or QS0-640 (Complete)
 

1.1T-625

  

Information Technology Project and Team Management

  

22TW3

  

A
MS Information Technology Electives
Course Title Needed Term Grade Credits
1. IT-655 Database Application Development 20TW5 A 3
2.1T-665 Client-Server Systems 21TW3 A 3
3.1T-647 Website Construction 21TW1 A- 3

Courses and Academic Evaluations

SNHU-AcademicEvaluation

Google Project Management

John Aghadiuno Google Project Management

Those who earn the Google Project Management Certificate have completed six courses, developed by Google, that include hands-on, practice-based assessments and are designed to prepare them for introductory-level roles in Project Management. They are competent in initiating, planning and running both traditional and agile projects.

Course Certificates Completed

  • Agile Project Management
  • Foundations of Project Management
  • Project Initiation: Starting a Successful Project
  • Project Planning: Putting It All Together
  • Capstone: Applying Project Management in the Real World
  • Project Execution: Running the Project

 

Database Application Development: Action Plan Report

9-2 Final Project Submission: Action Plan Report

John C. Aghadiuno

Nasser Halwani

8/23/2020

Table of Contents

Executive Overview

“The County of Everstone has a total of 34 incorporated cities within its boundaries and a population of 4 million residents. The county uses a database management system that is responsible for the county’s property tax assessment, parcel, and payment information. In a recent program, the county has been highly advocating the use of its online property tax payment and lookup information to reduce mailed payments and in-office visits.

The marketing campaign for online property tax payments and information lookup has been phenomenally popular. However, the county’s database systems have not sufficiently handled the increased traffic. As such, the web application open to residents often experiences excessive amounts of latency, often locks up, or in many instances is unavailable. The board of directors has asked executive IT management to probe the issue and provide a speedy solution to the matter.

A review of the hardware and software systems that the database management systems uses shows that these systems should be able to support the capacity and load. Therefore, the IT management has ruled out the hardware systems as being the issue.

Upon further examination, the issue appears to be caused by a need to provide performance tuning on the database. The database design is the culprit, since this database systems administrator noted copious quantities of duplicated data, poorly written database queries, and triggers.

Furthermore, the chief information officer (CIO) has also instructed the web programmers and web developers to examine at the web application for potential issues. The web development team has reported that while no design or programming issues were found with the web application, the stored procedures (queries) that it runs from the database server take long periods of time to run and frequently lock up the web servers. They suspect the queries were not designed for efficiency, since they were created before more individuals started using the online property tax payment system” (Southern New Hampshire University, 2020).

Performance Tuning

Types of Performance Tuning

Performance tuning refers to the improvement of system performance. “The need for performance tuning can either be real or expected because most systems will react to increased load with some level of decreasing performance, performance tuning can therefore be proactive or reactive” (Dickens, Heidelberger, & Nicol, 1994).

SQL Server Performance Tuning can be a challenging task, especially when working with a massive database where even minor changes can raise a significant impact on the existing query performance.

Performance tuning deals with the configuration and specifications of the Server, Client, Design & Architecture, Database Engine, Hardware & Network design. As the first step of SQL Server configuration, it is essential to calculate the required or optimal Buffer Memory (RAM), Storage, CPU’s, etc. Various kinds of hardware are available in the market with a variety of performance capabilities to choose from.

When executing queries, SQL Server will load data into the RAM, which is then processed before returning to the client. Therefore, we should know what the RAM value for the SQL Server should be? Buffer memory should be of a size that is enough to support concurrent active data of the database.

Defense

Multithreading by processors improves the performance of parallel SQL Server thread executions. SQL Server provides support for specifying the processor sockets with the cores and their Logical CPUs for the processing affinity and I/O affinity.

Physical Disk and Logical Disk can both be used in Infra standard. Logical Disk is the logical partitions of the physical disk with the drive letter. The logical disk is used to distribute the data into multiple logical units. Each disk has the IOPS life with the (n) number of total IOPS. When it crosses this digit, disk refreshment may be required to sustain and maintain the database performance.

Bytes per Second Disk IO is also one of the performance parameters for the disk. Different quality disks have various capacities for performance in a range of IO per second on disk. (SQL Server Hardware Performance Tuning, 2020)

Query Design

Explanation

SQL performance tuning consists of making the queries of a relation database run as fast as possible. SQL performance tuning is not a single tool or technique. Rather, it is a set of practices that makes uses of a wide array of techniques, tools, and processes.

When querying a database, optimization is crucial. An inefficient query will deplete the database’s resources and cause slow performance or loss of service for other users, especially if the query has errors. It is important that queries are optimized for minimum impact on database performance.

Efficiency

When running exploratory queries, many SQL developers use SELECT * (read as “select all”) as a shorthand to query all available data from a table. However, if a table has many fields and many rows, this taxes database resources by unnecessarily querying a lot of needless data.

Using the SELECT statement will make the database to query only the data needed to meet the business requirements. Here is an example SQL query where the business request names and addresses for customers against the database.

Inefficient Query:

SELECT *
FROM Students

This query will fetch all data stored in the customer table, such as phone numbers, activity dates, and notes from sales and customer service.

Efficient:

SELECT FirstName, LastName, Address, City, State, Zip
FROM Students

This query is much better and only fetches the required customer information for customer invoice table.

Some developers and administrators also prefer to make joins with WHERE clauses, such as the following:

SELECT Students.StudentID, Students.Name, Exams.LastTestDate
FROM Students, Exams
WHERE Students.StudentID = Exams.StudentID

The problem with this type of join is that it creates a Cartesian Join, also called a Cartesian Product or a CROSS JOIN.

In a Cartesian Join, all combinations of the variables are created. In this example, if we had 1,000 customers with 1,000 total sales, the query would first generate 1,000,000 results, then filter for the 1,000 records where StudentID is correctly joined. This is an inefficient use of database resources, as the database has done 100x more work than needed. Cartesian Joins are especially problematic in large-scale databases, because a Cartesian Join of two large tables could create billions or trillions of results.

Design

In order to avoid a Cartesian Join, an INNER JOIN command is used instead and a query such as this should be used:

SELECT Students.StudentID, Students.Name, Exams.LastTestDate

FROM Students

INNER JOIN Exams

ON Students.StudentID = Exams.StudentID

The database would return only the 1,000 desired records where StudentID supplied is equal to StudentID in Exams schema.

Some DBMS systems can recognize WHERE joins and run them internally as INNER JOINs instead. In these DBMS systems, there is no difference in performance between a WHERE join and INNER JOIN. However, INNER JOIN is understood by all database systems.

Data Duplication and Redundancy

Explanation

Data redundancy occurs when the same piece of data exists in multiple places, whereas data inconsistency is when the same data exists in different formats in multiple tables. Unfortunately, data redundancy can cause data inconsistency, which can provide a company with unreliable and/or meaningless information.

Database normalization is the process of efficiently organizing data in a database so that redundant data is eliminated. This process can ensure that all of a company’s data looks and reads similarly across all records. By implementing data normalization, an organization standardizes data fields such as student names, addresses, and phone numbers.

Defense

Normalizing data involves organizing the columns and tables of a database to make sure their dependencies are enforced correctly. The “normal form” refers to the set of rules or normalizing data, and a database is known as “normalized” if it is free of delete, update, and insert anomalies.

When it comes to normalizing data, each organization is different and might have their own unique set of criteria. Therefore, what one organization believes to be “normal”, may not be “normal” for another organization. For instance, one company may want to normalize the state or province field with two digits, while another may prefer the full name. Regardless, database normalization can be the key to reducing data redundancy across any company. (Pearlman, 2019)

Multiple-User Access Issues and Errors

Strategy

Most database applications are multi-user applications. This means that, at any given point in time, there are multiple persons and/or processes reading from and writing to a database. Given that multiple persons/processes are updating data in a database, it is only a matter of time before two separate persons/processes will try to update the same piece of data.

A typical update cycle consists of:

  • Read into memory
  • Update in memory, and
  • Write back to the database

Therefore, there will be times where two users will both read the same data into memory. User 1 will update the data and write those changes back to the database before user 2 does the same thing. Now you have a concurrency control conflict because user 1 read the data before user 2 wrote it back to the database. Why? Because if user 1 writes his data back into the database he will overwrite the changes made by user 2, causing them to be lost. Whoever saves their changes second will win, overwriting the changes made by whoever saves first.

This kind of database concurrency issue can occur both with humans or automated processes or a combination of the two. A concurrency issue is more likely to occur with human users as the read/update/write cycle is likely to take much longer. However, that said, the same concurrency issue can occur between automated processes and it is harder to solve because in the case of an update by a human you can ask what the user wants (do they want to overwrite changes made by another user?) and respond to that while a process needs to have all actions fully automated. (De Beijer, n/a)

Methods

The technique I recommend for avoiding concurrency issues is called timestamping, in which each record in the database has a "last modified date/time" field. This value is then updated whenever a new record is inserted and when an existing record is updated. Timestamping then works much like the read before write technique, except that instead of saving the entire record as a before image, only its last modified date/time field needs to be saved as user state information.

Similarly, just before changes to a record are saved to the database, only the timestamp field is retrieved and compared with the timestamp that was saved when the record was first retrieved. At this point, timestamping works just like read before write. If the timestamp values are different, the application knows that another user has updated the record, so it cancels the update process and an error message is displayed to the user.

Timestamping is recommended as a general solution because:

  • It is DBMS-independent.
  • It works even with non-DBMS data such as random-access files.
  • It does not require the application to keep a connection with the database, so it works in stateless environments such as HTTP.
  • It is simpler to code than read before write.

Bibliography

(2020). Retrieved from Southern New Hampshire University: https://learn.snhu.edu

Conceptual Models – What Are They and How Can You Use them? (2017 , March 23). Retrieved from Airbrake : https://airbrake.io/blog/sdlc/conceptual-model

Conceptual schema. (n.d.). Retrieved from Wikipedia: https://en.wikipedia.org/wiki/Conceptual_schema

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

Connolly, M. T., & Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). n/a: Pearson Education, Inc. Retrieved July 2, 2020, from https://bookshelf.vitalsource.com/#/books/9780134410951/pageid/0

De Beijer, M. (n/a). Database Concurrency Conflicts in the Real World. Retrieved from Code Magazine: https://www.codemag.com/Article/0607081/Database-Concurrency-Conflicts-in-the-Real-World

Dickens, P. M., Heidelberger, P., & Nicol, D. M. (1994, July). A distributed memory LAPSE: parallel simulation of message-passing programs. Retrieved from The ACM Digital Library: https://dl.acm.org/doi/10.1145/182478.182488

SQL Server Hardware Performance Tuning. (2020, April 14). Retrieved from SQLShack: https://www.sqlshack.com/sql-server-hardware-performance-tuning/

The 5 Major Stages of the Database Development Lifecycle. (2019, November 20). Retrieved from Apex Technology: https://trustapex.com/the-5-major-stages-of-the-database-development-lifecycle/

Operating Systems: Technical Evaluation of an Operating System

9-2 Final Project Submission - Technical Evaluation of an Operating System

John C. Aghadiuno

Robert C. Pratt, PhD

2/24/2019

Contents

  Organizational Profile

Top Secret, Inc. (TSI) is a company that develops and sells embedded operating systems around the world for a variety of devices including camera systems for drones, and alarm systems for top-secret installations. TSI operating systems are highly responsive to sensor input and features includes highly reliable operation, limited memory utilization, small size on disk, and low power consumption.

Due to a bad management decision, the organization decided to use the OS, which they designed for single purpose use, in their enterprise work stations and servers within their organization.

This has, as would be expected, resulted in an increase in costs, low performance, compatibilities issues with modern software, and devices the staff need to get their jobs done, and so on.

Other business-related challenges faced by the organization include the most serious to its reputation and integrity; the security of its data and operating systems. It’s operating system, designed as a single purpose, single use operating system, does not have any security features built-in. Running this software on an enterprise network exposes the organization to serious risks and theft of its data and information.

Evaluation of Operating System

GPOS Feature Profile Criteria Student Analysis

Multiprogramming

 Tech Description TSI OS does not support more than one program running at a time. TSI customers need one program resident, and that is the program that handles sensor input and (e.g., from cameras and motion sensors).
Business Requirement A back-office operating system requires preemptive multitasking and advanced scheduling features.
Application Benefit  
Implementation Tasks  

Multiprocessing

 Tech Description TSI OS does not support more than one processor on a physical device. The operating system locks up when interrupts are generated by a second processor.
Business Requirement Since most processors on the market are multicore, TSI has to purchase old, decommissioned hardware with single-core processors for its data center.
Application Benefit  
Implementation Tasks  

Multithreading

 Tech Description TSI OS lacks a system call interface beyond basic file open, close, read, and write. As such, it does not provide a CreateThread() or pthread_create() API call like Windows or Linux.
Business Requirement Back-office applications that offer multithreaded operation hang at launch, so TSI has to use open-source software so that a team of TSI software developers can remove multithreading functionality.
Application Benefit Incorporating multithreading into TSI’s OS will lead to efficiencies because multithreading allows the OS to run multiple tasks simultaneously. This would also decrease development time, improve OS responsiveness and optimize use of OS resource.
Implementation Tasks TSI OS should allow multithreading various tasks can be run simultaneously. Tasks including printing, saving, reading, etc.

Virtual Memory

 Tech Description TSI OS uses a flat memory model without paging.
Business Requirement As a result, TSI OS administrators in the back office often have to reboot the operating systems when they crash due to insufficient memory.
Application Benefit The primary benefit would be increased security because of memory isolation. Other benefits include being able to conceptually use more memory than is physically available, by using paging.
Implementation Tasks TSI’s operating system should be remodeled to use memory paging.

System Call Interface

 Tech Description As previously mentioned, TSI OS has only a basic system call interface.
Business Requirement This causes severe software compatibility issues. To get around this limitation at TSI headquarters, developers have had to modify traps to kernel mode and develop custom system call responses.
Application Benefit  
Implementation Tasks  

Security

 Tech Description Given the fact that TSI OS operates based on sensor input, it does not have any security for log in, file system modifications, or network security. In customer installations, the system is typically located on a closed TCP/IP network, so operators can quickly get access to device statistics.
Business Requirement As a result, anyone with network access at TSI headquarters can log into any TSI OS server in the back office.
Application Benefit Implementing strong security including authentication and authorization would safe-guard TSI from threats from hackers, identity theft, copyright theft, etc.
Implementation Tasks TSI should encrypt all data and communication on all data points including those between sensors, or other inputs, and back offices. Additionally, access to systems should require some form of authentication or authorization mechanism.

Device Drivers

 Tech Description TSI software developers write custom device drivers for each customer.
Business Requirement That does not work well for the TSI back office because the variety of devices is so large and sophisticated, TSI developers are unable to code for them.
Application Benefit  
Implementation Tasks  

Fault Tolerance

 Tech Description Given the device driver limitations of TSI software developers, they are unable to code device drivers for RAID cards.
Business Requirement As a result, all company data resides on individual SATA and SCSI drives.
Application Benefit Coding for RAID cards would lead to improved performance and redundancy benefits.
Implementation Tasks TSI developers should learn to write device drivers that are compatible with RAID cards.

Recommendation

TSI would benefit greatly from upgrading their OS and providing the ability to execute multiple processes or threads concurrently by using thread-level parallelism, as well as instruction-level parallelism. They would also benefit from adding multi-processing ability where available, if the system supports that. Finally, adding virtual memory management technique would improve the inter-operability and performance of their system.

References

Characteristics of Modern Web Applications. (2018, June 06). Retrieved from Microsoft: https://docs.microsoft.com/en-us/dotnet/standard/modern-web-apps-azure-architecture/modern-web-applications-characteristics

Principles of Database Design: Law, Ethics, Security and Database Management System

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

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 (800x600) 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.

  1. 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.

  1. 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.

  1. 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.

  1. 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/