Infromation System – Access Project

Access Project IS31 2 — Fall 2015 Last Updated: 10 / 09 /20 1 5 David Liu, Ph.D. © 20 1 5 page 1 of 4 IS312 Access Project This is an academic assignment: Report all unethical conduct to david.liu@csun.edu Submission Instruction: 1. Turn in your project anytime on box.com , but definitely before lab ends on the due date . 2. The file naming conventi on is “ L ast – name , F irst – name . accdb ” . Pay attention to the capi talization , coma and blank space , e.g. “ Doe , John . accdb ” 3. An image of the R elationship D iagram should automatically po p – up when the database initially opens . The image of the R elationship D iagram must also include the following four pieces of identifying information : 3.1. Name ( i.e. Last Name, First Name ) 3.2. SID 3.3. E – mail 3.4. Profile picture 4. If the file you turn ed in has a virus, you will get an automatic zero for this project. 5. Projects that are submitted late or via e – mail will NOT be accepted . 6. If you did more than which the assignment has required (e.g. query, form, VBA, etc.), describe what you did in a .TXT file (using Notepad) for extra credit consideration. Use the same naming convention (i.e. Last Name, First Name) and turn it in with your .accdb file. Note: A dditional updates , clarifications and requirements m ay be given dur ing classes and/or the labs . Hence, it is your responsibility to be cognizant of any additions and to incorporate the m in your assignment . Learning objectives : 1. B e self – sufficient in acquiring knowledge about the usage of Access by using the built – in Help (?) function in Access and by searching YouTube and Google when necessary . 2. To transform written requirements into a well thought out database design. 3. Be able to a nalyze data requirements and model the m in a relational database m anagement system, i.e. Access . 4. Us e the relationships window in Access to create a R elationship D iagram . 5. Be able to properly configure/constraint fields by setting data types and field properties . 6. Be able to represent many – to – many relationships with one – to – many relationships . 7. Be able to normalize a database design so that it has minimum data redundancy . Note: There should be only one relationship between each pair of tables. In addition, there is NO need to populate the database with data. O ptimization of database performance is not a criterion for the design in this assignment . Narrative d escription of the “ medical p ractice ” dat a base assignment : Design a database for a small medical practice with several physicians . The purpose of the database is to support the administrative functions such as billing , scheduling and patient tracking . The medical practice has multiple offices ; hence, physicians may be scheduled to be at different and/or multiple locations . However, each physician has be assign a primary location. Any patient m ay see any physician, and, over time, a patient may see different physicians. A patient may complain about multiple symptoms (or ailments ) during a single appointment . A unique ailment should o nly be listed once in a single appointment. However, a single ailment (e.g. bronchitis) can span multiple appointments. In addition , a patient may see only one physician per appointment. Since t he medical practice prefers to use a standard set of terminology/ phraseology for describing all the alimen ts , a table should be created in the database to keep track of all the possible ailment s and their associated attributes ( e.g. description of the ailment , recommended treatment, charges for that treatmen t and so on) . D uring an appointment , the attending physician can treat multiple ailments. Access Project IS31 2 — Fall 2015 Last Updated: 10 / 09 /20 1 5 David Liu, Ph.D. © 20 1 5 page 2 of 4 IS312 Access Project This is an academic assignment: Report all unethical conduct to david.liu@csun.edu Your solution should incl ude separate tables for patients , physicians , appointments and ailment s. For each table , analyze what should be the appropriate and necessary fields in order to satisfy the purpose of the database design . Points will be deducted when essential fields are missing from the table. Point will also be deducted when unnecessary necessary fields are included , e.g. if you have foreign key s in a table that are not linked, then it is unnecessary . The solution that the professor is looking for has a pure j unction table — one that is used to depict a many – to – many relationship from two one – to – many relationships . A primary key with multiple fields is needed in the pure junction table to ensure that there are no duplicate diagnostic codes for the same appointment. The final results for the assignment include a database that contains only th e require d tables (i.e. 5 of them) and a data model shown in the form of a R elationship s D iagram. Be sure that all the fields are appropriately typed and their properties configured/constrained . D o not enter any data into the tables . Recommended steps to f ollowing : 1. Start a new database from the Blank database template in Access 2013 , i.e. do not start from any other template s or existing databases. 2. You are to initially c reate the following 4 tables: ? Patients ? Physicians ? Appointments ? Ailments 3. General notes regarding database design ? Decide what fields are appropriate for which tables. ? Keep in mind the purpose of the database design , i.e. to support the administrative functions such as billing, scheduling and patient tracking for the medical practice . It would be helpful for you to search for open source ap plications for these functions to serve as a reference on what fields are necessary. ? Always work in the Design View when you are assignment field names and field properties, i.e. do not work in the Datasheet View . ? Do NOT enter data into the tables, i.e. just define the field properties of the tables . 4. Patient table design ? What kind of patient information does the medical practice need in order to do assure that it gets paid? For example: contact information, employment information, insurance information, e tc. ? For each piece of information that is needed in the database design, it should become a field that you need to specify . ? It is important to have the appropriate level of granularity in the design of the fields. For example, a patient’s name is not just one field , it should be at least first name, last name, etc. Likewise, an address is not just one field, but it should be separated into street, city, state, zip, etc. ? For each field, be sure to assign the appropriate Data Type to ensure data integrity and the support of downstream manipulation/extraction of the data. ? Also for each field, be sure to assign the appropriate Field Properties to constraint the value s it should hold and to facilitate formatting of the content . ? What is the unique identifier for a patient? Assign that field to be the Primary Key for this table. 5. Repeat the process in 4 for the remaining 3 tables ? Sample design considerations for the Physician table: what kind of physician information would an insur ance company want when it process a claim for Access Project IS31 2 — Fall 2015 Last Updated: 10 / 09 /20 1 5 David Liu, Ph.D. © 20 1 5 page 3 of 4 IS312 Access Project This is an academic assignment: Report all unethical conduct to david.liu@csun.edu reimbursement? What kind of physician information would the other stakeholders (e.g. the patient and the medical practice itself) like to have on the doctor s ? ? There is no need to create tables for office locat ions and insurance companies. ? Sample design considerations for the Appointments table: who is schedule d to meet? When? Where? Why? ? Sample design considerations for the Ailments table : what is the problem? Is it a symptom, an ailment or pathology? Be clear about the difference between the values that a field holds (i.e. the content) versus the field itself (i.e. the container). For example, the field “ ailment description” can hold explanatory value such as “migraine headache”, “torn right meniscus”, “ subdural hematoma”, etc. T hose descriptive values should not be individual fields. BTW, t here are more hints about the fields of the Ailment table in the narrative description section on page 1 of this document . 6. Prepare the R elationship canvas ? Open the Rel ationship canvas by clicking the Relationship button on the Database Tool tab. ? P opulate it with the 4 tables by right – clicking on the blank Relationship canvas and select the desired option in the context menu. ? Enlarge the window for each table so that mos t (if not all) the fields are visible without scrolling. ? Position the tables so that they do not obscure each other. ? Common mistakes to avoid : a) If Access complains that someone else is working on the table(s) or relationship when you attempt to make modifications , it is because you have more than one associated object open ed with modification(s). Hence, t he best remedy in this situation is to Save and/or Close those other objects before you continue. b) If Access complains when you try to link two fields together, a frequent mistake is that the two fields are of different Data Types, e.g. a Number field cannot be linked to a Text field. BTW, an AutoNumber field can be linked to a Number field. However, you should never link two A utoNumber fields together. c) If Access does not let you assign a field to be the primary key, the cause may be that you have populated the table with records and the in the values in that field are not unique. Hence, by definition, that field cannot be the p rimary key. The best remedy in this situation is to delete all the records in the table. 7. Define r elationships ? Recall that you need to associate two one – to – many relationships in order to depict a many – to – many relationship. ? There is a many – to – many relationship between patients and physicians. ? The Appointment tabl e brings together the Patients t able and the Physicians table. ? There is a many – to – many relationship between (a patient’s) appointment and his/her ailments. ? A 5 th table is needed and it serve s the following purpose s : a) The 5 th table brings together the Appointments table and Ailments table. b) Since the 5 th table is a pure junction table, the only two fields in it are the primary keys of the Appointments table and Ailments table . c) The 5 th table ensu res that each unique ailment should only be listed once per appointment. You do this by creating a key that is a c ombination of the two fields in it . H ighlight the two fields, then right – Access Project IS31 2 — Fall 2015 Last Updated: 10 / 09 /20 1 5 David Liu, Ph.D. © 20 1 5 page 4 of 4 IS312 Access Project This is an academic assignment: Report all unethical conduct to david.liu@csun.edu click and select the desired option in the context menu. If done properly, you should see a key symbol by each of the two fields. ? There is only one relationship between each set of tables. ? At least one side of each relationship is a primary key. ? There should be no foreign keys in any of the tables which are not use to link to another table . ? Open up the tables so that most (if not all) the fields are visible without scrolling. ? Do not cross your relationships i n th e Relationships D iagram. ? Show the relationship type (e.g. one – one, one – to many, etc.) by right – clicking on a relationship line and check the Enforce Referential Integrity checkbox. 8. Create a report ? When you are all done with the above 7 steps and are satisfied with your database design , then c reate a report from the Relationship D iagram . There is a button in the Design tab titled Relationship R eport that automatically creates a report from the Relationship Diagram. ? This report is a static image snap shot of the Relationship Diagram. Hence , if you make subsequent changes to the database an d/or the Relationship Diagram , changes will not be reflected in the report automatically. Therefore, create the report toward the end of the assignment when everything is finalized . Otherwise , you will need to re – snap an image from the Relationship Diagram , and redo all the embellishments for the report . ? Open up the report in Design View. ? Add in the 3 pieces of identifying information ( described at the top of page 1 of this document ) on the report. ? Apply some rendering features to embellish the report so th at it is more appealing. ? Save your report and give it a nam e . 9. Create a macro to launch (i.e. OpenReport) the report by the name . As an additional challenge, you can add an additional action to the macro so that the first action of the macro is to open a form that contains the 3 pieces of identifying information and the second action of the macro is to open the report with image of the Relationship D iagram. 10. In order for this macro to be automatically invoked by Access at the opening of your databa se, the macro needs to have a certain reserve d name. Go s earch the internet to find out what that name is and named your macro accordingly . In addition to the demonstrating your ability to follow verbal and written instructions, you are graded on the following : 1. Overall table design 2. Completeness of the field specification s , i.e. right fields in the right table s 3. Thoroughness of the field specification s , i.e. data types and details in the field properties 4. Assignment of primary keys 5. Assignment of relation ships 6. Use of a macro Final n ote s : 1. Make sure every view is positioned properly for clear presentation when it is open . 2. Quality and presentation counts, e.g. don’t cross lines in the Relationship D iagram or obscure any of the info rmation . 3. This is not a team project and plagiarism will not be tolerated. 4. Do not e – mail your project because the CSUN faculty mail – sever will not pass it through .