Introduction: Remember, from the first project assignment you were hired as a consultant to assist in converting the paperwork of a physician office to a health information database that will be more efficient, easy to query, and easy to interface and integrate with other health systems. As a consultant you were able to identify five major entities in the physician office system, PatientInfo, PatientHis, InsuranceInfo, ImagingResults, LabResults. You also identified each entity's attributes and related functional dependencies. For example:

• PatientInfo:

PatientNum

FirstName

LastName

SSN

Gender

BirthDate

HomeAddress

City

State

Zip

Phone

Email

MaritalStat

Employer

WorkAddress

WorkCity

Workstate

WorkZip

SpouseName

• InsuranceInfo

InsuranceID

GroupNum

NameOfInsured

RealtionshipToPatient

BirthDate

SSN

PatientNum

InsurerEmployer

IsurerAddress

InsurerCity

InsurrerState

InsurerZipCode

InsurnaceCompanyAddress

ICCITY

ICState

ICZip

ICPhoneNumber

• You can do the same for Patient History entity, ImagingOrders, and LabOrders entities

Functional dependencies:

• Patient # (FirstName, LastName, SSN, Gender, BirthDate, , HomeAddress, City, State, Zip,Phone, Email, MaritalStat, Employer, WorkAddress, WorkPhone, WorkCity, WorkState, WorkZip, Spouse Name, InsuranceID)
• SSN (FirstName, LastName, Email, Phone#, Gender, MaritalStatus, Address, City, State, Zip-Code, Date-of-Birth, Patient #)
• Email (FirstName, LastName, SSN, Phone#, Gender, MaritalStatus, Address, City, State, ZipCode, Date-of-Birth, Patient #)
• (InsurancePolicyNumber) (GroupNum, NameOfInsured, RelationshipToPat, BirthDate, SSN, InsurerEmployer InsurerAddress, ICity, IState, IZip, InsuranceCompanyAddress, ICCity, ICState, ICZip)
• (HistoryRecordID) (PastSurgery, CurrentConditions, PriorIllness, PriorInjuries)
• (LabOrderNumber) (CollectionDate, CollectionTime, CollectingIndividual, OrderingProvider, ConsultingDr, NarrativeResponse, Order, Test, ChemPanel, DrugLevel)
• (ImagingOrderNum) (ExamReason, ReferringPhysician, PhysicianPhone, PhysicianEmail, PhysicianFax, PhysicianRequests, ExamType, ExamFocus)

Requirement:

Watch the video related to data model and database design and the difference between the model and the design. Also watch the lecture that will assist you in using MS Visio to develop the model and design. You can use any free software package to create the drawings.