Step1: Using MS-Access
Create the data base for the below ERD diagram with four entity (patient, doctor, nurse and ward)using MS-Access database as shown
Entity 1:
| pat_id | pat_name | pat_disease |
|---|---|---|
| 101 | raju | tb |
| 102 | rani | cancer |
| 103 | kani | fever |
| 104 | vani | cough |
| 105 | dhoni | typhoid |
Entity 2:
| doc_id | doc_name | pat_id |
|---|---|---|
| 201 | rk | 101 |
| 202 | pk | 102 |
| 203 | ck | 103 |
| 204 | dk | 104 |
| 205 | sk | 105 |
Entity 3:
nurse
| n_id | ward_id | pat_id |
|---|---|---|
| 301 | 401 | 101 |
| 302 | 402 | 102 |
| 303 | 403 | 103 |
| 304 | 404 | 104 |
| 305 | 405 | 105 |
Entity 4:
| ward_id | pat_id |
|---|---|
| 401 | 101 |
| 402 | 102 |
| 403 | 103 |
| 404 | 104 |
| 405 | 105 |
Draw the ERD as shown in fig using MS Visio
click on file menu -> new -> Software and Database -> Database Model Diagram
patient entity
doctor entity
ward entity
Step 2:
Now we shall establish hospital management system using the above four entity relations using the relationship component as shown
Step 3:
click on Data menu bar -> Link Data to Shapes and then select Microsoft Access Database which will be as shown
Step 4:
Now patient entity database created in the ms access will be on to your ms visio as shown
Step 5:
Repeat the same procedure for the remaining entities database (doctor, nurse and ward) created in the ms access will be on to your ms visio as shown
Step 6:
How to run the query in MS-Visio 2007 : To run the query in MS-Visio we need to create a query in MS-Access as follows
Click on Create menu bar -> Query Design
Step 7: Now add all the entity on to the query wizard
Step 8: Now write your query using the sql view by clicking on design menu bar -> View
Queries:
1. select * from patient where pat_disease='tb';
2. select * from patient where pat_name like 'r%';
3. select * from patient where pat_id=105;
4. select * from patient where pat_name like '%i';
5. select * from doctor where doc_name like '%k';
2. select * from patient where pat_name like 'r%';
3. select * from patient where pat_id=105;
4. select * from patient where pat_name like '%i';
5. select * from doctor where doc_name like '%k';
