Reference no: EM13971485

Create an Access database for the following scenario: Patients buy prescription drugsfrom the NHF pharmacy. Data is recorded for the following objects:

Patient

Field	Data Type
A unique patient ID	9 - digit number
Last name	Text, 25 characters
First name	Text, 25 characters
Date of birth	Date
Gender	Takes either "F" or "M"
Phone	Use input mask format as (_ _ _) _ _ _ - _ _ _ _ Example (402) 333-4567
Address	Test, 100 characters
City	Text, 20 characters
State	Test, 2 characters
Zip code	5 - digit number
Tobacco use	Takes Yes or No only

Visit

Field	Data Type
Visit ID	A unique number
Patient ID	Patients unique 9 - digit number
Visit date	Date/time
Insurance company	Text, 25 characters
Insurance group code	Text, 30 characters
Insurance member code	Text, 30 characters
Amount billed	Currency
Date insurance paid	Date/ TIme

Visit Medications

Field	Data Type
Visit ID	Number
Drug ID	Number
Drug Code	Text, 15 characters
Comments	Text,  200 characters

Drugs

Field	Data Type
Drug ID	A unique number
Label Code	Text, 12 characters
Dosage	Text, 12 characters
Brand	Text, 60 characters

There are certain relationships between these objects/tables. One patient can have Many visits, and in One visit, there might be Many medications. These relationships mean that the Patient ID is a primary key for the patient table, and a foreign key in the Visit table. The Visit ID is the primary key of the Visit table, and a foreign key in Visit Medications. The Drug ID is the primary key of the Drugs table, and a foreign key in the Visit Medications table. Based on the above definitions, create these tables in Access, set the fields as explained (data type, size, format), and create relationships between these tables. In all relationships Referential Integrity must be enforced.