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.