GUI powerpoint presentation

Here is the link to our PPT presentation:

GUI PPT on GoogleDocs

Data Warehouse Power Point File

Here is the link:

http://docs.google.com/present/edit?id=0AQGsdO58rtAZZGY5OXFrNGdfMWc4NGd4cmM2&hl=en

EDI Questions

1. When would you want to use EDI?

2. Which standard would you use if you were an international company?

3. What is one thing that a VAN offers?

4. Open or Web based EDI came into prominence because?

5. What is the main reason for the future of EDI?

6. What are the three advantages of EDI?

Condition Tables

create table Condition

(conditionID int,

name varchar(30),

primary key(conditionID));

insert into CONDITION (conditionID, name) values (1, 'Stable');

insert into CONDITION (conditionID, name) values (2, 'Recovory');

insert into CONDITION (conditionID, name) values (3, 'Critical');

insert into CONDITION (conditionID, name) values (4, 'Critical but Stable');

create table PatientCondition

(conditionID int,

clinicNum int,

primary key(conditionID, clinicNum),

foreign key (conditionID) references condition (conditionID),

foreign key (clinicNum) references patient(clinicNum));

insert into PATIENTCONDITION (conditionID, clinicNum) values (1, 100000001);

insert into PATIENTCONDITION (conditionID, clinicNum) values (2, 100000002);

insert into PATIENTCONDITION (conditionID, clinicNum) values (3, 100000003);

insert into PATIENTCONDITION (conditionID, clinicNum) values (4, 100000004);

Database Creation Update

All of the posted SQL statements posted with one exception have been successfully loaded to our class database.  The Tape table discrepancy still needs to be resolved and re-posted.

We are still missing the following:
Table Creation SQL Queries for:
Condition
PatientCondition

Data insertion SQL Queries for:
Condition

PatientCondition
VisitResearchStudy

Everyone on our team will post a couple of tables. Here are the two ICD9 tables:


CREATE TABLE mayoproject.ICD9ProcedureCode
(
ICD9ProcedureCodeID INT(9),
ICD9ProcedureCode VARCHAR(15),
clinicNum INT(9),
PRIMARY KEY(ICD9ProcedureCodeID)

);


ALTER TABLE mayoproject.ICD9ProcedureCode
ADD FOREIGN KEY (clinicNum)
REFERENCES mayoproject.patient(clinicNum);


CREATE TABLE mayoproject.ICD9Diagnostic
(
ICD9DiagnoticID INT(9),
ICD9Diagnostic VARCHAR(15),
clinicNum INT(9),
PRIMARY KEY(ICD9DiagnoticID)
);


ALTER TABLE mayoproject.ICD9Diagnostic
ADD FOREIGN KEY (clinicNum)
REFERENCES mayoproject.patient(clinicNum);


INSERT INTO mayoproject.ICD9ProcedureCode (ICD9ProcedureCodeID, ICD9ProcedureCode, clinicNum) VALUES (1, '01.02', 100000001);

INSERT INTO mayoproject.ICD9ProcedureCode (ICD9ProcedureCodeID, ICD9ProcedureCode, clinicNum) VALUES (2, '01.41', 100000002);

INSERT INTO mayoproject.ICD9ProcedureCode (ICD9ProcedureCodeID, ICD9ProcedureCode, clinicNum) VALUES (3, '01.52', 100000002);

INSERT INTO mayoproject.ICD9ProcedureCode (ICD9ProcedureCodeID, ICD9ProcedureCode, clinicNum) VALUES (4, '39.64', 100000004);

INSERT INTO mayoproject.ICD9ProcedureCode (ICD9ProcedureCodeID, ICD9ProcedureCode, clinicNum) VALUES (5, '92.32', 100000003);

INSERT INTO mayoproject.ICD9ProcedureCode (ICD9ProcedureCodeID, ICD9ProcedureCode, clinicNum) VALUES (6, '84.3', 100000005);


INSERT INTO mayoproject.ICD9Diagnostic (ICD9DiagnoticID, ICD9Diagnostic, clinicNum) VALUES (1, '001-139', 100000001);

INSERT INTO mayoproject.ICD9Diagnostic (ICD9DiagnoticID, ICD9Diagnostic, clinicNum) VALUES (2, '771.2', 100000002);

INSERT INTO mayoproject.ICD9Diagnostic (ICD9DiagnoticID, ICD9Diagnostic, clinicNum) VALUES (3, '079.53', 100000002);

INSERT INTO mayoproject.ICD9Diagnostic (ICD9DiagnoticID, ICD9Diagnostic, clinicNum) VALUES (4, '090-099', 100000004);

INSERT INTO mayoproject.ICD9Diagnostic (ICD9DiagnoticID, ICD9Diagnostic, clinicNum) VALUES (5, '062.5', 100000003);

INSERT INTO mayoproject.ICD9Diagnostic (ICD9DiagnoticID, ICD9Diagnostic, clinicNum) VALUES (6, '066.1', 100000005);

AssistiveDevice and PatientAssistiveDevice Tables

CREATE TABLE mayoproject. AssistiveDevice( AssistiveDeviceID INT (9), Name VARCHAR (15), PRIMARY KEY (assistiveDeviceID) );

CREATE TABLE mayoproject. PatientAssistiveDevice( AssistiveDeviceID INT (9), ClinicNum INT (9),
FOREIGN KEY (clinicNum) REFERENCES mayoproject.patient (clinicNum);

FOREIGN KEY (assistiveDeviceID) REFERENCES mayoproject.AssistiveDevice (assistiveDeviceID); );


INSERT INTO mayoproject.AssistiveDevice (assistiveDeviceID, Name) VALUES (1, ‘walker’);

INSERT INTO mayoproject.AssistiveDevice (assistiveDeviceID, Name) VALUES (2, ‘quad cane’);

INSERT INTO mayoproject.AssistiveDevice (assistiveDeviceID, Name) VALUES (3, ‘crutches’);

INSERT INTO mayoproject.AssistiveDevice (assistiveDeviceID, Name) VALUES (4, ‘canes’);

INSERT INTO mayoproject.AssistiveDevice (assistiveDeviceID, Name) VALUES (6, ‘walker’);

INSERT INTO mayoproject.AssistiveDevice (assistiveDeviceID, Name) VALUES (7, ‘quad cane’);

INSERT INTO mayoproject.PatientAssistiveDevice (assistiveDeviceID, clinicNum) VALUES (1, 100000001);

INSERT INTO mayoproject.PatientAssistiveDevice (assistiveDeviceID, clinicNum) VALUES (2, 100000002);

INSERT INTO mayoproject.PatientAssistiveDevice (assistiveDeviceID, clinicNum) VALUES (3, 100000004);

INSERT INTO mayoproject.PatientAssistiveDevice (assistiveDeviceID, clinicNum) VALUES (4, 100000003);

INSERT INTO mayoproject.PatientAssistiveDevice (assistiveDeviceID, clinicNum) VALUES (5, 100000006);

mayoproject database tables and data are still needed

I do not see the tables and insertion of data statements for Keith et al team.

Also the Tape table has a discrepancy in the backup data type and the data that you have in the data insertion statements that needs to be resolved.

Five Data Warehouse Questions:

1) What is the main goal of a data warehouse application?
2) What is data warehouse row level security?
3) What does ETL stand for?
4) List three Business Intelligence Tools?
5) What are the three factors that make up a Business Contextual Model?

Ray and Tom's GUI questions

Q1 - name one of the GUI front ends that we discussed
Q2 - name one of the characteristics of the GUIs
Q3 - name one of the "usability" of the GUIs
Q4 - name one of the "unique" feature of the GUI (need to name the feature and the GUI)
Q5 - name one of the GUIs that can access more than 1 database.

Tongun Data Mining

Q. what is data mining?.
Q.Name two commonly used techniques in data mining.
Q.What is a data warehouse?.
Q.What are the advantages of data mining over traditional approaches?.
Q.In database what is the validity of the stored data.
A. data integrity
B. mining
C. Processing
D. Independence.

Jena and Robbie Data Compression ?'s

Q: What are two advantages of digital data?

Q: What is data compression?

Q: What are the three reasons that data compression is needed?

Q: What is lossless compression and what is it good for?

Q: What is lossy compression and what is it good for?

Q:What is perceptual coding?

Tables: File, ResearchStudy, VisitResearchStudy, PatientStudy, SystemUsed, VisitSystemUsed

CREATE TABLE VisitSystemUsed(
systemUsedID INT,
visitID INT,
FOREIGN KEY (systemUsedID) references SystemUsed(systemUsedID),
FOREIGN KEY (visitID) references Visit(visitID)
);

INSERT INTO VisitSystemUsed(systemUsedID, visitID) VALUES (0300, 8), (0400, 9), (0200, 4);


ResearchStudy(IRBNum, name, description)
SystemUsed(systemUsedID , name)
 


 
CREATE TABLE ResearchStudy (
IRBNum INT(6),
name VARCHAR(30),
description VARCHAR(50),
PRIMARY KEY (IRBNum))
 
CREATE TABLE SystemUsed (
systemUsedID INT(4),
name VARCHAR(30) NOT NULL DEFAULT 'EMG-Surface',
PRIMARY KEY (systemUsedID))

INSERT INTO `mayo project`.`researchstudy` (`IRBNum`, `name`, `description`) VALUES ('000001', 'ResStudy1', 'Description1');
 
INSERT INTO `mayo project`.`researchstudy` (`IRBNum`, `name`, `description`) VALUES ('000002', 'ResStudy2', 'Description2');
 
INSERT INTO `mayo project`.`researchstudy` (`IRBNum`, `name`, `description`) VALUES ('000003', 'ResStudy3', 'Description3');




CREATE TABLE PatientStudy (
studyID INT,
visitID INT,
PRIMARY KEY(studyID, visitID),
FOREIGN KEY(studyID) REFERENCES Study(studyID),
FOREIGN KEY(visitID) REFERENCES Visit(visitID)
);


INSERT INTO PatientStudy(studyID, visitID) VALUES('1001','1');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1001','2');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1001','3');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1002','3');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1002','5');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1004','6');
INSERT INTO PatientStudy(studyID, visitID) VALUES('1004','2');



CREATE TABLE File(
filename CHAR(25),
clinicNum INT (9),
PRIMARY KEY(filename),
FOREIGN KEY(clinicNum) REFERENCES Patient(clinicNum)
);
INSERT INTO File(filename, clinicNum) VALUES('file1.xml', 100000001);
INSERT INTO File(filename, clinicNum) VALUES('file2.xml', 100000002);
INSERT INTO File(filename, clinicNum) VALUES('file3.xml', 100000003);




CREATE TABLE VisitResearchStudy (
visitID INT,
IRBNum INT,
Foreign Key (visitID) references Visit(visitID)),
Foreign Key (IRBNum) references ResearchStudy(IRBNum));

Tape table data insertion error to resolve

I ran the create table statement without any problems, but when I went to insert the data there is a discrepancy in the column backup which was created as a varchar(2), but the insertion of the data has 4 characters.  Please alter the table or the data insertion statements to correct the discrepancy and repost so everyone has the correct SQL statements.  Thanks much!

Tape Table

Here are the values for the tape table:

http://docs.google.com/Doc?docid=0AeZ0srsHOylXZGY3N2JkZjRfMGdkdG41Y2Zq&hl=en

billingCodes Table

CREATE TABLE BillingCodes
(billingCodeID INT NOT NULL,
visitCharge DOUBLE,
CPTCode INT,
integerIncrement INT,
visitID INT(4),
FOREIGN KEY (visitID) REFERENCES Visit(visitID))

ALTER TABLE BillingCodes
ADD PRIMARY KEY(billingCodeID)
//note: I forgot to add the primary key so I had to do this 'ALTER' query to get it done.



INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000111, 120.32, 1000, 1, 1);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000112, 98.58, 1001, 3, 2);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000113, 508.13, 1002, 1, 3);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000114, 988.46, 1003, 2, 4);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000115, 319.01, 1004, 5, 5);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000116, 274.99, 1005, 1, 6);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000117, 1014.11, 1006, 2, 7);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000118, 63.63, 1007, 10, 8);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000119, 666.74, 1008, 12, 9);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000120, 780.00, 1009, 6, 10);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000121, 381, 1010, 5, 11);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000122, 890.54, 1011, 4, 12);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000123, 200.00, 1012, 7, 13);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000124, 150.59, 1013, 8, 14);

INSERT INTO BillingCodes (billingCodeID, visitCharge, CPTCode, integerIncrement, visitID)
VALUES(10000125, 563.49, 1014, 8, 15);

study table

this syntax of study table and input value.
CREATE TABLE `study` ( `studyID` int(11) NOT NULL, `studyName` varchar(45) NOT NULL, PRIMARY KEY (`studyID`))
//////////////////////////////////////////////////////////////
INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1001', 'study A');INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1002', 'study B');INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1003', 'study C');INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1004', 'study D');INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1005', 'study E');INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1006', 'study F');INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1007', 'study G');INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1008', 'study H');INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1009', 'study I');INSERT INTO `study` (`studyID`, `studyName`) VALUES ('1010', 'study J');

Presentation Schedule

http://cs.winona.edu/faculty/nanderson/485-s10/PresentationSchedule.pdf

Patient & Visit Tables are loaded

Patient and Visit tables and the data statement have been successfully loaded into a mysql database called mayoproject - Yea!

Data insertion error to be resolved please

I cannot insert data using your statements posted.  The error message is listed below.  I get a similar error when I try to insert 1 row of data or all rows of data.

The problem is that height is a column with 3 digits, 1 of which is after the decimal point and you have listed a 4 digit number for height.

Since other people will be using this information I think it would be best if the creators of these statements make the changes they would like to have made and re-post the SQL statements.  Just my opinion.

Link to Source to Patient and Visit tables - UPDATED Saturday 3/27/10 at 1:30p

Class:

I had made an entry in Nicole's post about the schema, but in case you did not see my post there, I am creating a separate post to hightlight the MySQL source code to create the tables "Patient" and "Visit". You will need to create the MySQL database with a name of "mayoproject". (without the quotes of course)

Here is the link to the source:

Source file on GoogleDocs

UPDATE:
There was an error in the original posting of the source for the create table for patient in the height and weigth fields. They should have been 5,1 and not the 4,1 and 3,1 that was posted earlier. I have updated the source on Google Docs as well.

Post request

Ray or Tom, can you post a link/information about the mySQL product you mentioned in class tonight that you have found to work the best for you?  Thanks much!!

PRESENTATION DATES

Nicole,
Are you going to post the presentation dates

Class ERD from 03242010

Schema for Assignment

http://cs.winona.edu/faculty/nanderson/485-s10/MayoSchema.pdf

Homework 6

http://cs.winona.edu/faculty/nanderson/485-s10/hw6.pdf

Response to Travis's Question

For some reason blogger won't let me reply to the post at the moment. Regardless, yes, this is correct. The total of all items in an order.

Exam question 1

I was just kinda curious on this. With the total cost attribute did you mean the total cost of the entire thing or each individual meal. With my current setup that is how I had to do it. I just want to be sure that that is what you meant.

exam

Hi Nicole,
do we have to use create constraints check (...........) or we can use another technique?

examquestion

do we need to use alter table PAYMENT
add constraint CHK_PAYMENT_3 to
check the condition of credit use only when the total is over $5. ???

this means that we do not check cash or debit. and the cash and debit
will not be working after this constranins is inforcing.