Database Assignment: The Database Application Design

After thoroughly understanding the principles and methods of designing a database, you should select a specific area/topic for your database.

Design a database using MS Access with the following specifications (at least):

1. Create appropriate tables (you should have at least 5 tables) and identify relationships.
2. Identify clearly the primary and foreign key (s).
3. Tables need to be normalized to level at least III.
4. Data should be entered using a data entry form.
5. Design queries that ask for user input (i.e., ask for a patient id to retrieve patient information.)
6. Generate a report on your database to answer questions (for example, how many patients were hospitalized due to cardiac problems)
7. Design queries to retrieve information from multiple tables.
8. After any update or manipulation, your database should maintain referential integrity.
9. Documentation your application for users.
10. Stored procedures
11. Triggers
12. Portal

Example database project:

Scenario: Assume you are a new person hired to create and maintain a small hospital patient database. The hospital has the following scenario:

Patient visits the hospital. First, patients have to register with the registration clerk, where the clerk records patient demographics (name, id, address, gender, race etc.). Then they are called by an assigned nurse to record information such as body temperature, blood pressure etc. After that they will see assigned doctor. Depending on situation, doctors may or may not admit patients for inpatient services. Doctor provides new prescriptions for patients and records all the medications that a patient is currently taking. Each medication has start date as well as end date and physician information who prescribes the medication. Doctor may also order laboratory works for the patient. Laboratory attendant updates the lab test results for each of the patients with proper order and information.

Design a hospital patient management database with tables for each of the proper entities (patient, medications, hospital staff, lab test, hospitalization of patients, doctor information, prescriptions etc.). For each patient's visit, patient should have a primary diagnosis (reason for visit) also. Populate your tables with appropriate dummy data (make up your own data).

This is just a sample to give you an idea about how your database project should be. You can choose this for your project if you want to. You are welcome to choose your own project for different settings (other than patient management). Please let us know about what you choose and a brief description of what you are going to do for the project.

Try to implement such scenario in a database, create a schema for it, an ER diagram for it and try to normalize it.

What to submit

• Your master thesis
• A detail description of your project
• Diagram of your database including tables with attributes and relationships.
• SQL for queries.
• Full documentation includes technical ER diagram and user instruction
• Screen shots of the queries and results (at least for specifications 4,5 and 6 mentioned above)
• Your database project in MS Access (the .mdb file)

Paper guidelines and deliverables: There is no set length for the master's essay; the important thing is whether or not the different areas of the project have been adequately discussed.

Students will be asked to submit work on the essay as a set of deliverables which make up the different components of a research project. These include:

Research question, hypotheses, or type of study (~1/2 page)

What are you trying to do? What is the question that will be addressed in the project/paper? What is its significance in the larger context of biomedical informatics and healthcare IT? What will we know when the project is finished that we didn't know before? How generalizable is it? (i.e. how will what we learn help others understand different situations?).

Literature Review/proposed bibliography (to determine validity of the question, available literature on issues, to provide background, and to determine suitable methods)

Students will read the literature related to their topic, with a view to:

-determine the validity of the question (can we answer the question with the project and time available? Has the question already been answered? Is there anything in the literature that makes the question invalid?)

-determine whether there is sufficient background literature related to the topic to answer the question (if it is not based on an internship project)

-help elucidate the methods suitable for the project and paper (e.g. what methods have other people used to address this type of question? What are the standard methods, if any?)

-students will submit an annotated bibliography consisting of references to the articles (properly formatted for the journal chosen) and a brief summary (one-two paragraphs) of the article and its significance for your paper.

-this will be modified (in prose) to become the introduction and background sections of your master's essay

Outline of research/clinical internship activities planned (to make sure this is feasible) This should a brief statement of:

1. the site, system, location, and purpose of the project (if internship-related) or the research question (if not internship related)

2. general methods (e.g. "I will do a survey study of 20 stakeholders before and after implementation to test their perceptions of usability/ease of use and usefulness of the system. Survey question topics will include their previous IT experience, years in healthcare, attitudes to IT, training in this software, and general demographics")

3. What this will tell us?

Proposed Methods, including proposed statistical analyses

4. Methods broken into stages (e.g. observation phase, identification of stakeholders/study subjects, survey instrument, conducting the study itself, data analysis)

5. Any statistical measures you propose to use, and the justification for them; sample sizes and how they were calculated

6. Any equipment or consultation you will need (e.g. statistical services, computer with screen recording software)

Proposed schedule - state the dates during which you expect to complete the different parts of the study, data analysis and write-up

Proposed journal - identify a journal or conference based on the type of study you are doing and the things the journals say they are interested in. The instructor can make suggestions. The two major biomedical informatics journals are JAMIA - Journal of the American Medical Informatics Assocation, and Journal of Biomedical Informatics. Others are IMIA - the

International Journal of Medical Informatics, JMIR - Journal of Medical Internet Research (which has a focus especially on consumer health, internet-related and patient-facing applications), American Journal of Public Health, and so on. Identify a journal even if you expect not to publish. See ‘instructions for authors' on each journal's website to see the kinds of articles they are interested in. If you wish you may want to consider submitting it for a student paper competition, such as HIMSS or AMIA.

Students should submit the above deliverables and have the research plan outline and schedule approved by the instructor before submitting the subsequent parts of the essay.

Proposal presentation - make a brief powerpoint presentation in the class session on March 19th . Include discussion of your methods and any preliminary data or observations. Your classmates will provide comments. The instructor will provide comments privately. You may have to modify your proposal if any major flaws are discovered or parts of it are unfeasible.

After the proposal is approved the majority of your time will be spent on-site carrying out the study (if your paper is internship-related) or library or other research (if not internship-related). The instructor will meet with you remotely (e.g. via Skype or phone/webex) or in-person regarding your progress and any problems or arrangements. Be sure to contact the instructor if you encounter problems that might delay your progress. Problems and unexpected events happen all the time in both research and implementations; they don't reflect on the student. It's better that we are aware and work through them than to let a problem result in your being unable to graduate on time.

Parts of the Essay:

Introduction, background (based on literature review) and significance

Methods - based on your proposal. Include any modifications to the methods you made during the study

Findings - include the data

Discussion - this is the section where you discuss what the findings mean. What implications are there? What questions remain? What things are uncertain, and what possible explanations or alternate explanations are there for what you found? Did anything happen that might make your conclusions ambiguous or invalid? Are there any confounders?

Conclusions - a short (few sentences) statement of what we have learned from this project Limitations - discuss the limitations based on the scope and methods.

Future work - what you found likely suggests future interesting or useful work. Discuss it briefly here. What are the next logical steps?

References - include relevant references formatted for the journal you chose (Endnote does this easily, and does ‘cite while you write' - inserting and numbering the references automatically - the instructor can help with this).