1.You are required to set up a database for your local health clinic.To begin with we are going to create a flat file and then develop the database into a relational database. You may work in pairs to do this. a.Create a new database and call it Medical records. b.Inside this database create a new table and name it personal data.This table is to include the personal data of the people at your clinic.Brainstorm with your classmates to create at least 10 fields.Test this with some test data to make sure that expected entries can be made. c.Forms and User Friendly input. Forms are used to create a more friendly input method.Create a form using the Wizard and then customize it.Once you have done this successfully, input yourselves and the rest of the class in this table. Fields should include: Title, First Name, Fathers Name, Address and contact details…… Congratulations you have now made your first flat database. d.Now we need to create another table, this time, it will store the medical history of your patients.Brainstorm with your class the sorts of fields that you might have for each patient e.g. allergies, blood type, family medical history, and vaccinations.Set up the fields. e.Helping make the data reliable.Data may be unreliable if it is entered incorrectly or if it becomes outdated.Ways to prevent data entry errors include: i.Field types ie only a certain type of data is permitted eg dates. ii.Limiting field size iii.Default values to input the most likely field iv.Validation rules to ensure that values are within a given range. v.Required fields so null values are not allowed. vi.Drop down boxes to limit the data entry of choice. Review the tables you have set up for Medical History and Personal Data and change them so that they will allow for more reliable data. f.This table needs linking to the personal data table.What linking field will you have? What type of relationship will the tables have?Make the link and then create a form and use this to add the medical history of your patients. Congratulations you have made a relational database! 2. Define the terms in a glossary: ·Field ·Key field ·Record ·Search ·Query ·Sort ·Database Management System ·Mail Merge 3.Describe a flat database and then a relational database.How are they different? 4.Describe the Advantages and Disadvantages of using a database compared to a paper system. 5.Now you have set up the database of your patients, it is time to make your database active, each time your patient visits you need to record this in your database. a)Create another new table and name it Visit.In this table, you are going to record each of the visits that your patient makes as a new record.What sort of information will be collected at each visit, brainstorm this e.g. date of visit, name of doctor, symptoms, medicine prescribed. b)Set up the new table with the fields that you have discussed and then link this to the personal data. c)Create some visit entries for your patients, including more than one visit for some of them. d)What sort of queries and reports can you create?Queries can search for multiple conditions; Reports can list data and include calculations e.g. Summary data e.g. SUM, COUNT.Try out similar queries and reports for the following: i.Women over 50 who need a mammogram ii.People on your database with blood ‘O’ Names and Tel No, as you need to contact them to donate in an emergency. iii.People on a certain type of drug – need to be contacted to change their medication e.g. Health warning that a certain drug from China has a harmful chemical. iv.Patients who have recently had a certain disease and they want to see if there is an epidemic. v.A search for symptoms e.g. detecting SARS vi.An update query..e.g. date of new vaccination has been updated to6 months after a proposed date. e)How can this sort of information improve the efficiency of the health clinic? Medical Records April 2006, Medical records go online in Florida.Doctors and patients will be able to see their records online.Doctors will be able to file prescriptions through an online system and pharmacists will be able to view patients medications. Main benefits of a system is to reduce costs and reduce the dangers of medical errors. October 2006, Singapore.SingHealth’s radiology labs digitize X ray filmsand share them with local clinics. 2006, UK, the NHS presented plans to set up an electronic medical record system.The aim was to link up all the doctors clinics and hospitals, so that they could share medical information.This is being trialled in a London hospital. Read further http://news.bbc.co.uk/2/hi/health/7887438.stm 2006, Brunei signed a deal to start the e-health project where they planned to link up all of the health clinics in Brunei and store medical records electronically.Not everyone was happy about this.On one hand to go into one clinic and by showing them your ID they can bring up your records will improve the efficiency of the clinic.On the other hand, there are huge concerns over interoperability, privacy, not full acceptance of the plan, technical limitations etc. 1. Can you think of the benefits to both the patients and of the healthcare workers of electronic medical records?Imagine if you could access your own medical records online?Explain these. 2. What issues does it raise?Why are some people concerned or not happy about this?Research the issues of the electronic storage of medical records and explain these issues, with supporting evidence. 3. What advice would you give to the Brunei Ministry of Health about setting up their new system? 4.For your Health clinic database, look at the security and permission settings.The administration staff need access to only personal data, so that reminder letters can be sent about up and coming inoculations.Only the doctors need access to your medical history and visit information.How can the relational database help with the different access required by different people?How does this help with the patient’s privacy? 5.Your administration staff need to send a letter to all female patients under the age of 30 to come for a free breast cancer scan.Write a suitable letter and perform a mail merge.To create the letters for your patients.Print out the first letter from your mail merge. 6.Data Redundancy & Database Normalisation.When only a flat file is used, there will be lots of duplication of data.This has been avoided by setting up a relational database.You will notice that information is only ever entered once and then linked together. Normalisation Rules 1. There should be No duplication of data.Data e.g. address of customer.Should only be stored once.There must be data integrity, because it is only entered once even though it may appear in more than one query or report. Reasons for having data integrity because of no duplication of data are: -To take up less space -To ensure easy editing and efficient changing of records. 2. The key field of a table has to be chosen so that it will uniquely define one record. 3. All non key fields ar to be dependent on the key.A reason for having this rule is so that removal of one record does not remove other unrelated data. 4. Many to many relationships should not be implemented.
Now you have worked with your database, describe what is meant by the following terms:
· Data redundancy
· Data integrity
· Data matching
· Data/computer profiling
· Data Mining
FurtherReadingand questions
Read the following chapters: CC Chp 8 and Gift of Fire Chp 2 to help you answer these questions:
1. The use of databases presents many issues related to Privacy. Identify the main risks associated with storing personal information on central databases.
2. The Privacy Scholar Alan Westin identified certain factors to be balanced, describe three.
3. Identify 3 uses of databases by the Government and 3 by the private sector.
4. Find out Posners economic viewpoint on the value of information and how it should be protected.
5. For each of the following Legislations make brief notes on what they are and their main purpose:
Provision of Privacy Act of 1974
Code of Fair Information Practices
1998 European Data Protection Directive
1966 Freedom of Information Act
1986 Electronic Communications Privacy Act
1988 Computer Matching and Privacy Protection Act.
1998 Data Protection Act.
Databases and Health
Introduction
Read the following chapters to help you answer the questions and use the Access Database Help guide to help you in your database creation.Computer Confluence Chapter 7 (7th Edition) for some basics or Discovering Computers 2005 Chapter 10 – Database Management Chapter and online supporting materials
http://www.scsite.com/dc2005/index.cfm?action=overviewflash&chapter=10
And in the Students Labs under Databases
http://www.scsite.com/selabs/
Activity
1.You are required to set up a database for your local health clinic. To begin with we are going to create a flat file and then develop the database into a relational database. You may work in pairs to do this.a. Create a new database and call it Medical records.
b. Inside this database create a new table and name it personal data. This table is to include the personal data of the people at your clinic. Brainstorm with your classmates to create at least 10 fields. Test this with some test data to make sure that expected entries can be made.
c. Forms and User Friendly input. Forms are used to create a more friendly input method. Create a form using the Wizard and then customize it. Once you have done this successfully, input yourselves and the rest of the class in this table. Fields should include: Title, First Name, Fathers Name, Address and contact details…… Congratulations you have now made your first flat database.
d. Now we need to create another table, this time, it will store the medical history of your patients. Brainstorm with your class the sorts of fields that you might have for each patient e.g. allergies, blood type, family medical history, and vaccinations. Set up the fields.
e. Helping make the data reliable. Data may be unreliable if it is entered incorrectly or if it becomes outdated. Ways to prevent data entry errors include:
i. Field types ie only a certain type of data is permitted eg dates.
ii. Limiting field size
iii. Default values to input the most likely field
iv. Validation rules to ensure that values are within a given range.
v. Required fields so null values are not allowed.
vi. Drop down boxes to limit the data entry of choice.
Review the tables you have set up for Medical History and Personal Data and change them so that they will allow for more reliable data.
f. This table needs linking to the personal data table. What linking field will you have? What type of relationship will the tables have? Make the link and then create a form and use this to add the medical history of your patients.
Congratulations you have made a relational database!
2. Define the terms in a glossary:
· Field
· Key field
· Record
· Search
· Query
· Sort
· Database Management System
· Mail Merge
3. Describe a flat database and then a relational database. How are they different?
4.Describe the Advantages and Disadvantages of using a database compared to a paper system.
5. Now you have set up the database of your patients, it is time to make your database active, each time your patient visits you need to record this in your database.
a) Create another new table and name it Visit. In this table, you are going to record each of the visits that your patient makes as a new record. What sort of information will be collected at each visit, brainstorm this e.g. date of visit, name of doctor, symptoms, medicine prescribed.
b) Set up the new table with the fields that you have discussed and then link this to the personal data.
c) Create some visit entries for your patients, including more than one visit for some of them.
d) What sort of queries and reports can you create? Queries can search for multiple conditions; Reports can list data and include calculations e.g. Summary data e.g. SUM, COUNT. Try out similar queries and reports for the following:
i. Women over 50 who need a mammogram
ii. People on your database with blood ‘O’ Names and Tel No, as you need to contact them to donate in an emergency.
iii. People on a certain type of drug – need to be contacted to change their medication e.g. Health warning that a certain drug from China has a harmful chemical.
iv. Patients who have recently had a certain disease and they want to see if there is an epidemic.
v. A search for symptoms e.g. detecting SARS
vi. An update query..e.g. date of new vaccination has been updated to 6 months after a proposed date.
e) How can this sort of information improve the efficiency of the health clinic?
Medical Records
April 2006, Medical records go online in Florida. Doctors and patients will be able to see their records online. Doctors will be able to file prescriptions through an online system and pharmacists will be able to view patients medications. Main benefits of a system is to reduce costs and reduce the dangers of medical errors.
October 2006, Singapore. SingHealth’s radiology labs digitize X ray films and share them with local clinics.
2006, UK, the NHS presented plans to set up an electronic medical record system. The aim was to link up all the doctors clinics and hospitals, so that they could share medical information. This is being trialled in a London hospital.
Read further http://news.bbc.co.uk/2/hi/health/7887438.stm
2006, Brunei signed a deal to start the e-health project where they planned to link up all of the health clinics in Brunei and store medical records electronically. Not everyone was happy about this. On one hand to go into one clinic and by showing them your ID they can bring up your records will improve the efficiency of the clinic. On the other hand, there are huge concerns over interoperability, privacy, not full acceptance of the plan, technical limitations etc.
1. Can you think of the benefits to both the patients and of the healthcare workers of electronic medical records? Imagine if you could access your own medical records online? Explain these.
2. What issues does it raise? Why are some people concerned or not happy about this? Research the issues of the electronic storage of medical records and explain these issues, with supporting evidence.
3. What advice would you give to the Brunei Ministry of Health about setting up their new system?
4. For your Health clinic database, look at the security and permission settings. The administration staff need access to only personal data, so that reminder letters can be sent about up and coming inoculations. Only the doctors need access to your medical history and visit information. How can the relational database help with the different access required by different people? How does this help with the patient’s privacy?
5. Your administration staff need to send a letter to all female patients under the age of 30 to come for a free breast cancer scan. Write a suitable letter and perform a mail merge. To create the letters for your patients. Print out the first letter from your mail merge.
6. Data Redundancy & Database Normalisation. When only a flat file is used, there will be lots of duplication of data. This has been avoided by setting up a relational database. You will notice that information is only ever entered once and then linked together.
Normalisation Rules
1. There should be No duplication of data. Data e.g. address of customer. Should only be stored once. There must be data integrity, because it is only entered once even though it may appear in more than one query or report.
Reasons for having data integrity because of no duplication of data are:
- To take up less space
- To ensure easy editing and efficient changing of records.
2. The key field of a table has to be chosen so that it will uniquely define one record.
3. All non key fields ar to be dependent on the key. A reason for having this rule is so that removal of one record does not remove other unrelated data.
4. Many to many relationships should not be implemented.
- Now you have worked with your database, describe what is meant by the following terms:
· Data redundancy· Data integrity
· Data matching
· Data/computer profiling
· Data Mining
Further Reading and questions
Read the following chapters: CC Chp 8 and Gift of Fire Chp 2 to help you answer these questions:1. The use of databases presents many issues related to Privacy. Identify the main risks associated with storing personal information on central databases.
2. The Privacy Scholar Alan Westin identified certain factors to be balanced, describe three.
3. Identify 3 uses of databases by the Government and 3 by the private sector.
4. Find out Posners economic viewpoint on the value of information and how it should be protected.
5. For each of the following Legislations make brief notes on what they are and their main purpose:
Provision of Privacy Act of 1974
Code of Fair Information Practices
1998 European Data Protection Directive
1966 Freedom of Information Act
1986 Electronic Communications Privacy Act
1988 Computer Matching and Privacy Protection Act.
1998 Data Protection Act.
End of Unit Revision
Try out some of the checkpoint activities in http://www.scsite.com/dc2005/index.cfm?action=CheckPoint&chapter=10Back