AccessProjectIS312—Fall2015Last Updated:10/09/2015David Liu, Ph.D. © 2015page1of4IS312 Access ProjectThis is an academic assignment: Report all unethical conduct to [email protected]Submission Instruction:1.Turn in yourprojectanytime on box.com, butdefinitelybeforelab ends on thedue date.2.The file naming convention is“Last-name,First-name.accdb”. Pay attention to thecapitalization, coma and blank space, e.g.“Doe,John.accdb”3.An image of theRelationshipDiagram shouldautomatically pop-up when the databaseinitiallyopens. The imageof the RelationshipDiagrammustalso includethefollowingfourpieces ofidentifyinginformation:3.1.Name(i.e. Last Name, First Name)3.2.SID3.3.E-mail3.4.Profile picture4.Ifthe file you turnedin hasa virus, youwillget an automatic zero for this project.5.Projects that are submitted late or via e-mail willNOTbe 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 thesame naming convention (i.e. Last Name, First Name) and turn it in with your .accdb file.Note:Additionalupdates,clarificationsand requirementsmay begivenduring classes and/orthelabs.Hence, it is your responsibility tobe cognizant of any additions and toincorporatethem in yourassignment.Learning objectives:1.Be self-sufficient inacquiring knowledgeaboutthe usage ofAccessby using the built-in Help(?) function inAccessand by searching YouTube and Googlewhennecessary.2.To transform written requirements into a well thought out database design.3.Be able to analyze data requirements and model them ina relational database managementsystem, i.e. Access.4.Usethe relationships window in Accessto create a RelationshipDiagram.5.Be able to properly configure/constraint fields by setting data types and field properties.6.Be able torepresent many-to-many relationshipswith one-to-many relationships.7.Be able to normalize adatabasedesign so that it has minimumdata redundancy.Note: There should be only one relationship betweeneach pairoftables.In addition, thereisNOneedto populate the database with data.Optimization ofdatabaseperformance isnotacriterionforthedesign in thisassignment.Narrative description ofthe“medical practice”databaseassignment:Design a database for a small medical practicewithseveralphysicians.The purpose of thedatabase is to support the administrative functions such asbilling, scheduling andpatienttracking.The medicalpractice has multiple offices; hence,physicians maybe scheduledto beatdifferent and/or multiple locations.However,each physician has be assign a primary location.Any patient may see any physician, and, overtime,apatientmayseedifferentphysicians. Apatient may complain aboutmultiplesymptoms (orailments)during a single appointment.Auniqueailmentshouldonlybelisted oncein a singleappointment.However, a single ailment(e.g.bronchitis)can span multiple appointments. In addition,apatientmaysee onlyone physician perappointment.Since themedicalpracticeprefers to useastandardset ofterminology/phraseologyfordescribingallthe aliments, atableshould be createdin thedatabasetokeeptrack ofall thepossibleailments and their associatedattributes(e.g.descriptionof the ailment,recommendedtreatment,chargesfor that treatmentand so on).During anappointment, the attending physiciancantreatmultiple ailments.AccessProjectIS312—Fall2015Last Updated:10/09/2015David Liu, Ph.D. © 2015page2of4IS312 Access ProjectThis is an academic assignment: Report all unethical conduct to [email protected]Your solution should include separate tables for patients,physicians,appointmentsand ailments.For each table, analyzewhat should be theappropriateand necessaryfieldsin order to satisfythe purpose of the database design. Points will be deducted when essential fields are missingfrom the table. Point will also bedeductedwhen unnecessarynecessary fields areincluded, e.g.if you have foreign keys in a table that are not linked, then it is unnecessary.The solution thattheprofessor islooking forhasa pure junction table—one thatis used todepicta many-to-manyrelationship from two one-to-many relationships.Aprimarykeywith multiple fieldsis neededinthe pure junction tableto ensure that there are no duplicate diagnostic codes for the sameappointment.The final results for the assignment includea database that containsonlytherequiredtables(i.e.5 of them)andadata modelshownin the form ofaRelationshipsDiagram.Be sure that all thefields are appropriately typedand their properties configured/constrained.Do not enter any datainto the tables.Recommended steps to following:1.Start a new database from the Blank databasetemplate inAccess 2013, i.e. do not startfrom any other templatesor existing databases.2.You are toinitiallycreate the following 4 tables:PatientsPhysiciansAppointmentsAilments3.General notes regarding database designDecide what fields areappropriate for which tables.Keep in mind the purpose of the databasedesign, i.e.to support theadministrative functions such asbilling, scheduling andpatient trackingforthemedical practice.It would be helpful for you tosearchfor open sourceapplications for these functions to serve as a reference on what fields arenecessary.Always work in the Design View when you are assignment field names and fieldproperties, i.e. do not work in the Datasheet View.Do NOT enter data into the tables, i.e.just define the field properties of thetables.4.Patient table designWhat kind ofpatientinformation does the medical practice needin order to doassure that it gets paid?For example: contact information, employmentinformation, insurance information, etc.For each piece of information thatis needed in the database design, it shouldbecomea field that you need to specify.It is important to have the appropriate level ofgranularityin the design ofthefields. For example, a patient’s name is not justone field, itshould be at least firstname, last name, etc. Likewise,anaddress is not just one field, butit should beseparated intostreet, city, state, zip, etc.For each field, be sure to assign the appropriateData Typeto ensure dataintegrity andthesupportofdownstreammanipulation/extraction of the data.Also for each field, be sure to assign the appropriateField Propertiestoconstraint the valuesitshould holdand to facilitate formattingof the content.What is the unique identifier for a patient? Assign that field to be the Primary Keyfor this table.5.Repeatthe process in4for the remaining 3 tablesSample designconsiderationsfor the Physician table: what kind ofphysicianinformation would an insurance company want whenitprocess a claim forAccessProjectIS312—Fall2015Last Updated:10/09/2015David Liu, Ph.D. © 2015page3of4IS312 Access ProjectThis is an academic assignment: Report all unethical conduct to [email protected]reimbursement?What kind of physician informationwould theother stakeholders(e.g. the patient andthe medical practiceitself) like to have on the doctors?There is no need to create tables for office locations and insurance companies.Sample designconsiderationsfor the Appointments table: who is scheduledtomeet? When? Where? Why?Sample designconsiderationsfor the Ailments table: what is the problem?Is it asymptom, an ailment or pathology? Be clearabout thedifference between thevalues that a field holds(i.e. the content) versus the field itself (i.e. the container).For example,the field“ailmentdescription”can holdexplanatoryvalue such as“migraine headache”,“tornrightmeniscus”, “subdural hematoma”, etc.Thosedescriptivevaluesshouldnot be individual fields.BTW, there are more hintsabout the fields of the Ailment tablein the narrative descriptionsectionon page 1of this document.6.Preparethe RelationshipcanvasOpen the Relationship canvasby clicking the Relationship button on theDatabase Tool tab.Populate it with the 4 tablesby right-clicking on theblankRelationship canvasand select the desired option in the context menu.Enlarge the window for each tableso that most (if not all) the fields are visiblewithout scrolling.Positionthe tables so thatthey do not obscure each other.Common mistakes to avoid:a)If Access complains that someone else is working on the table(s) orrelationshipwhen you attempt to make modifications, itisbecauseyouhave more than oneassociatedobject openedwith modification(s).Hence, thebest remedy in this situationis to Save and/or Close thoseother objects before you continue.b)If Access complains whenyou try to link two fields together,afrequentmistake is thatthe two fields are of different Data Types, e.g. aNumber field cannot be linked to a Text field. BTW, an AutoNumberfield can be linked to a Number field. However, you should never linktwo AutoNumber fields together.c)If Access does not let you assign a field to be the primary key,thecausemay bethat you have populated the table withrecords and thein thevaluesinthat fieldare not unique.Hence, by definition,that fieldcannot be the primary key. Thebest remedy in this situationis todelete all the records in the table.7.DefinerelationshipsRecall that you need toassociatetwo one-to-many relationships in order todepicta many-to-many relationship.There is a many-to-manyrelationshipbetweenpatients and physicians.The Appointment table brings together the Patients table and the Physicianstable.There is a many-to-many relationship between (a patient’s) appointment andhis/herailments.A5thtableis needed and it servesthe following purposes:a)The 5thtablebrings together the Appointments table and Ailmentstable.b)Since the 5thtable isapure junction table, the only two fields in it arethe primary keys of theAppointments table and Ailments table.c)The 5thtableensures thateach unique ailment should only be listedonce per appointment.You do this by creating a key that is acombination ofthetwo fieldsin it.Highlightthetwo fields,then right-AccessProjectIS312—Fall2015Last Updated:10/09/2015David Liu, Ph.D. © 2015page4of4IS312 Access ProjectThis is an academic assignment: Report all unethical conduct to [email protected]clickand select the desired option in the context menu.If doneproperly, youshould see a key symbolbyeach of thetwofields.There is only one relationship between each set of tables.At least one side ofeachrelationship is a primary key.Thereshould beno foreign keys in any of the tableswhichare notuse tolinktoanother table.Open up the tables so that most (if not all) the fields are visible without scrolling.Donotcross your relationships in the Relationships Diagram.Show the relationship type (e.g. one-one, one-to many, etc.) by right-clicking on arelationshiplineand check the Enforce Referential Integrity checkbox.8.Createa reportWhen you are all donewith the above 7 stepsand are satisfied with yourdatabase design, thencreatea report from the Relationship Diagram.There is abutton in the Design tab titled Relationship Report that automatically createsareport from the Relationship Diagram.This report is a static image snap shot of the Relationship Diagram. Hence, if youmakesubsequentchanges tothedatabaseand/or the Relationship Diagram,changeswill not be reflected in the reportautomatically. Therefore,create thereporttoward the endof the assignment when everything is finalized. Otherwise,you will need tore-snap animagefrom the Relationship Diagram,andredo alltheembellishments forthe report.Open up the report in Design View.Add in the3pieces of identifying information (describedat the top of page 1ofthis document)onthe report.Applysome rendering features toembellishthe reportso that it is moreappealing.Saveyour reportandgive it aname.9.Create a macro to launch(i.e. OpenReport)the reportby the name.As an additionalchallenge,youcanadd an additional action to the macro so that thefirstactionof themacrois toopen a form that contains the 3 pieces of identifying information andthesecondactionof the macro is toopen the report withimage ofthe Relationship Diagram.10.In order for this macro to be automatically invoked by Access at the opening ofyourdatabase,the macroneeds tohaveacertainreservedname.Gosearch the internet tofind out what thatnameisand named your macro accordingly.In addition to the demonstrating your ability to follow verbal and written instructions, you aregraded onthefollowing:1.Overall table design2.Completenessof thefield specifications, i.e. right fields in the right tables3.Thoroughnessof thefield specifications, i.e. data types anddetails in thefield properties4.Assignment of primary keys5.Assignment of relationships6.Use ofamacroFinal notes:1.Make sure everyviewis positioned properly forclear presentation when it is open.2.Quality and presentation counts, e.g. don’t cross lines in theRelationship Diagram or obscureany of theinformation.3.This is not a teamproject and plagiarism will not be tolerated.4.Donote-mail your project becausethe CSUN faculty mail-severwill notpass it through.

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.