+1 (208) 254-6996 essayswallet@gmail.com
  

Database Schema-SQL Statements

1) Based on the feedback received on your Topic 2 assignment Database Schema, provide SQL statements to create the (revised) database and populate it with sample data (at least four rows per table).

2) Modify the database by adding an additional column (“Deleted_Date”) to those tables that represent entities that could contain data which can be deleted and justify your rationale in a short paragraph. In addition, explain what the tradeoffs are in using a marker-column for deletion, as opposed to actually deleting a row from a table?

APA format is not required, but solid academic writing is expected.

Running Head: DATABASE SCHEMA-SQL STATEMENTS 1

DATABASE SCHEMA 5

Database Schema-SQL Statements

HIM-650- Health Care Data Management

Grand Canyon University

Kaleena Moore

September 21, 2018

SQL Statements

// Kaleena Moore

// HIM 650

// 09/25/2018

// This is my own work.

Sqlite> .header on sqlite> .mode column

CREATE TABLE Provider ( Prov_ID char(5) not null, Prov_FirstName varchar(15) not null, Prov_LastName varchar(15) not null, PRIMARY KEY (Prov_ID) );

INSERT INTO Provider ( Prov_ID, Prov_LastName, Prov_FirstName) VALUES ( ‘45375’, ‘Lawrence’, ‘Nelson’ ) ;

INSERT INTO Provider ( Prov_ID, Prov_LastName, Prov_FirstName) VALUES ( ‘45258’, ‘Pamela’, ‘Lee’ ) ;

INSERT INTO Provider ( Prov_ID, Prov_LastName, Prov_FirstName) VALUES ( ‘46572’, ‘Brian’, ‘Cook’ ) ;

INSERT INTO Provider ( Prov_ID, Prov_LastName, Prov_FirstName) VALUES ( ‘45479’, ‘Michelle’, ‘Jones’ ) ;

// Kaleena Moore

// HIM 650

// 09/25/2018

// This is my own work.

CREATE TABLE Patient ( Pat_ID char(6) not null, Pat_FirstName varchar(15) not null, Pat_LastName varchar(15) not null, DOB date(8) not null, Pay_Name varchar(20), Ins_ID char(9), Sex char(1) not null, PRIMARY KEY (Pat_ID) ) ;

INSERT INTO Patient ( Pat_ID, Pat_FirstName, Pat_LastName, DOB, Pay_Name, Ins_ID, Sex) VALUES ( ‘158623’, ‘Willy’, ‘Nelson’, ‘07081956’, ‘Mercy Care’, ‘A45687259’, ‘M’ );

INSERT INTO Patient ( Pat_ID, Pat_FirstName, Pat_LastName, DOB, Pay_Name, Ins_ID, Sex) VALUES ( ‘154726’, ‘Samantha’, ‘Berringer’, ‘02181992’, ‘Health Choice’, ‘A84572612’, ‘F’ );

INSERT INTO Patient ( Pat_ID, Pat_FirstName, Pat_LastName, DOB, Pay_Name, Ins_ID, Sex) VALUES ( ‘104726’, ‘Ann’, ‘Briggs’, ‘02091982’, ‘Aetna’, ‘974125896’, ‘F’ );

INSERT INTO Patient ( Pat_ID, Pat_FirstName, Pat_LastName, DOB, Pay_Name, Ins_ID, Sex) VALUES ( ‘105891’, ‘David’, ‘Ham’, ‘09151979’, ‘Cigna’, ‘948746398’, ‘M’ );

// Kaleena Moore

// HIM 650

// 09/25/2018

// This is my own work.

CREATE TABLE Recorded Service ( Pro_Code char(5) not null, Mod char(2), Rec_Ser_ID char(6) not null, DOS date(8) not null, PRIMARY KEY (Rec_Ser_ID) );

INSERT INTO Recorded Service ( Pro_Code, Mod, Rec_Ser_ID, DOS) VALUES ( ‘T1016’, ‘HN’, ‘788452’, ‘12152017’ );

INSERT INTO Recorded Service ( Pro_Code, Mod, Rec_Ser_ID, DOS) VALUES ( ‘T1016’, ‘HN’, ‘767893’, ‘11262017’ );

INSERT INTO Recorded Service ( Pro_Code, Mod, Rec_Ser_ID, DOS) VALUES ( ‘T1016’, ‘HN’, ‘748529’, ‘05182018’ );

INSERT INTO Recorded Service ( Pro_Code, Mod, Rec_Ser_ID, DOS) VALUES ( ‘T1016’, ‘HN’, ‘754376’, ‘07252018’ );

// Kaleena Moore

// HIM 650

// 09/25/2018

// This is my own work.

CREATE TABLE Claim ( Pat_FirstName varchar(15) not null, Pat_LastName varchar(15) not null, Pay_Name varchar(20) not null, Ins_ID char(9) not null, Claim_No char(6) not null, Balance decimal(5,2) not null, DOS date(8) not null, Prov_ID char(5) not null, Prov_FirstName varchar(15) not null, Prov_LastName varchar(15) not null, PRIMARY KEY (Claim_No) );

INSERT INTO Claim ( Pat_FirstName, Pat_LastName, Pay_Name, Ins_ID, Claim_No, Balance, DOS, Prov_ID, Prov_FirstName, Prov_LastName) VALUES ( ‘Willy’, ‘Nelson’, ‘Mercy Care’, ‘A45687259’, ‘985214’, ‘$41.92’, ‘12152017’, ‘45375’, ‘Lawrence’, ‘Nelson’ ) ;

INSERT INTO Claim ( Pat_FirstName, Pat_LastName, Pay_Name, Ins_ID, Claim_No, Balance, DOS, Prov_ID, Prov_FirstName, Prov_LastName) VALUES ( ‘Samantha’, ‘Berringer’, ‘Health Choice’, ‘A84572612’, ‘976412’, ‘$55.40’, ‘11262017’, ‘45258’, ‘Pamela’, ‘Lee’ ) ;

INSERT INTO Claim ( Pat_FirstName, Pat_LastName, Pay_Name, Ins_ID, Claim_No, Balance, DOS, Prov_ID, Prov_FirstName, Prov_LastName) VALUES ( ‘Ann’, ‘Briggs’, ‘Aetna’, ‘974125896’, ‘986751’, $83.50’, ‘05182018’, ‘46572’, ‘Brian’, ‘Cook’ ) ;

INSERT INTO Claim ( Pat_FirstName, Pat_LastName, Pay_Name, Ins_ID, Claim_No, Balance, DOS, Prov_ID, Prov_FirstName, Prov_LastName) VALUES ( ‘David’, ‘Ham’, ‘Cigna’, ‘948746398’, ‘965375’, ‘$50.75’, ‘07252018’, ‘45479’, ‘Michelle’, ‘Jones’ ) ;

// Kaleena Moore

// HIM 650

// 09/25/2018

// This is my own work.

CREATE TABLE Payor ( Pay_Name varchar(20) not null, Pay_ID char(7) not null, EDI_No char(7) not null, PRIMARY KEY (Pay_ID) ) ;

INSERT INTO Payor ( Pay_Name, Pay_ID, EDI_No) VALUES ( ‘Mercy Care’, ‘1589467’, ‘2459761’ );

INSERT INTO Payor ( Pay_Name, Pay_ID, EDI_No) VALUES ( ‘Cigna’, ‘1596231’, ‘2487465’ );

INSERT INTO Payor ( Pay_Name, Pay_ID, EDI_No) VALUES ( ‘Aetna’, ‘1524612’, ‘2463253’ );

INSERT INTO Payor ( Pay_Name, Pay_ID, EDI_No) VALUES ( ‘Health Choice’, ‘1537647’, ‘2473185’ );

ADDING DELETED DATE COLUMNS

// Kaleena Moore

// HIM 650

// 09/25/2018

// This is my own work.

ALTER TABLE Provider ADD COLUMN Deleted_Date date;

UPDATE Provider SET Deleted_Date = 09212018;

ALTER TABLE Patient ADD COLUMN Deleted_Date date;

UPDATE Patient SET Deleted_Date = 09212018;

The provider table could have data that could be deleted. This might be in the instance where the database is growing and there are several providers who no longer are employed at the organization and it has been quite a few years that have passed. There could be a set date in history where you would want to delete those past providers whom you no longer need the audit trails for. The same goes for patients. If the patients haven’t been seen in several years, such as ten years or longer, a date could be set to search for anything prior and those patients could be marked for deletion in the database.

When you actually delete a row from a table, you’re deleting the data permanently and can not retrieve it. When you use a maker-column for deletion, you’re simply marking it for deletion but there would be an additional step to actually delete it afterwards.

There are two methods you can use to delete a DataRow object from a DataTable object: The Remove method of the DataRowCollection object, and the Delete method of the DataRow object. Whereas the Remove method deletes a DataRow from the DataRowCollection, the Delete method only marks the row for deletion. The actual removal occurs when the application calls the AcceptChanges method. By using Delete, you can programmatically check which rows are marked for deletion before actually removing them. When a row is marked for deletion, its RowState property is set to Delete (DataRow Deletion, 2017).

It is always useful to keep deleted data around, especially in the case of medical billing. Audits will usually happen, and you will need all patient data including previous payors and claims. Providers could leave the practice, but you would still want to keep their previous history including which patients they saw and so forth. There could be instances where it would be necessary to replace or alter data instead of deleting it, as well. An example would be when a lot of pseudo insured ID needs to be updated but the current one is already in the database. Then a Update data segment could be used in the data base to update the ID to the new Insured ID which has the same effective date.

References

DataRow Deletion. (2017, March 29). Retrieved September 22, 2018, from https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/datarow-deletion