PLEASE CHECK THE ATTACHMENTS
HIM22HID Assignment 2 Health Informatics B Create a Database Assignment 2 A small hospital needs a database to keep track of patient prescriptions. Their current data is in an excel spreadsheet, and a patient database. The new database needs to track the information in the spreadsheet. Create a normalised database for this data. Do this by first creating an ER diagram for the data, and then following the steps to generate the tables needed to hold the data, in third normal form. Capture the information in the most efficient manner, and include all possible error checking, to ensure the data is of highest quality. Table 1 - Sample prescription data to be stored in new database Do the following tasks. List the data you need in your database From table 1 above, and the data in the attached Excel File and Access database, make a list of all the different types of data you need. These will be the fields in your database. Create ER diagram Decide which tables you need. Decide which fields go in which table, and where the relationships should exist. Draw the ER diagram, using the rules from the lectures. Convert the ER diagram into tables, using the instructions in the lectures. Setup your database Set up a database so that you can collect all the information necessary for your database, specifically: · Implement the tables, and primary keys, · Setup data edit rules, appropriate field lengths, names, descriptions and captions, · Setup lookups and relationships, and, · Enter values into control tables. Form Design Build the prescription data entry form, including drop down menus and edit rules where appropriate. This form must capture all data related to new prescriptions, assigned to existing patients. Include appropriate coloration, layout, and read only fields (to prevent inadvertent changing). Data Import Import the spreadsheet data. This step is optional, but it makes the next step easier, because you don't have to do so much data entry if you import some of it. Data entry Enter data from the sample dataset into your database, using your data entry form that you created above. You can either import some and data enter some, or data enter it all. Based on your data entry experience, modify your form to streamline the data entry task and make it easier for the users. Document the changes you made, or would like to make, to streamline the data entry process. Note where Access is limited, and how the form could be improved. Testing Test your data entry form, and document the testing. Document specifically what you tested (the actual data entered, both correct and erroneous), and document the results, i.e. how your database keeps out erroneous data. Show at least two examples of correct and erroneous data for each field. Data query and reports Create reports: · listing the name and birth year of five patients who had the greatest quantity of drugs. · For each drug, count the total number of patients, the total quantity, and the percentages of the total for each count Database Documentation (500 words) Your database documentation should include: · Data dictionary (defining all the fields and the tables). · ER Diagram showing database structure and relationships. · Steps to go from ER diagram to a database. · Proof that database is in third normal form. · Improvements made, or would like to make, to your data entry form. · Testing results, consisting of 1) what was tested, 2) data used, and 3) results, i.e. Access result. · No introduction or conclusion is needed. Submission Both the database and the documentation must be submitted to LMS combined into a single zip file ( See tutorial week 1 tips and tricks). This is an individual assignment so your submission must entirely be your own work. Reference Tables for Assignment Create your reference files first. Once these have been created enter the data (using the data view of the tables) into reference tables as indicated below: Sex Note: As project manager you might find that you need to add values to these reference values, or you may need more reference tables. Code Description F Female M Male Assessment Evaluation Summary The assessment will be graded according to the following criteria. Criteria Excellent Good Poor Needs significant improvement External Documentation (20/60) Documentation clear and well presented Documentation unclear or poorly written Documentation difficult to understand or sparse Documentation absent or nonsensical Internal Database Layout and Documentation (20/60) Well-chosen and described field and table names and structure. Good internal documentation Table names, field names, and/or structure OK. Some internal documentation. Poor field, table names and/or structure. Little internal documentation Bad field and table names and/or structure. No internal documentation Form (10/60) Beautiful layout, correct structure, excellent error checking Good layout Poor layout Form absent or does not function Query / Reports (10/60) Functions and is beautiful Something wrong and/or not so nice Query and/or report broken Completely broken and/or missing Assessment Evaluation Detail The assessment will be graded according to the following criteria. Criteria Section – Marks External Documentation (20) Data Dictionary (screen shots of descriptions, and table of constraints)– 5 ER diagram and steps to create database structure– 7 Why is this table in Third Normal form? (3) Data entry form testing and improvements 5 Internal Database Layout and Documentation (20) See below Form (10) -1 for every error, maximum -2 for any category Categories: Missing fields, extra fields, inappropriate coloration/layout/alignment/labels, error trapping Query / Reports (10) -1 for every error, maximum -2 for any category As per previous assessment Detailed Marking criteria – Internal database In general, subtract one mark per transgression Section Max deduction for section Transgressions Name and descriptions -5 · Field names could be more descriptive and/or consistent · Fields need descriptions · Primary and foreign key field names should be consistent between themselves · Query, report and table names could be more clear and/or consistent Foreign Keys -5 · Foreign key lookup fields should be restricted to values from the primary keys from their associated lookup tables · It would have been good to adjust field lengths from default 255 to something more appropriate · Table fields need some error checking · Inappropriate primary keys Tables -5 · Unnecessary duplication of tables · You have tables in database that are not in ER diagram or vice versa Third Normal form punish this severely, -5 if any of this exists · Database is not in 3rd normal form (e.g. repeating group) · Data duplication is the first sin of database design - you should have NO data duplication (except for primary key / foreign key linkages) Relationship -5 · Some foreign keys missing lookups · You should only have lookups on the M side of M:1 relationships · Relationships should only go from primary key to foreign key · Redundant relationships in relationship window · Missing relationships in relationship window 4