A charity insinuate provides healthcare services for patients in rural areas. Th

Question

A charity insinuate provides healthcare services for patients in rural areas. They organize regular visits to different cities and villages to provide medical consultation in addition to lab tests and drugs prescriptions. For each consultation, a volunteer doctors perform a medical exam and may order number of lab tests and prescribe some medication for the patient. The organization wants to develop a data warehouse application prototype that will enable them to get better understanding about patients and resource allocation. The information stored in the data warehouse include names and details of different lab tests such as Hepatitis Test and Syphilis test and so on. The system also stores information about the list of drugs they provide like drug name and dosage. The patient information includes their name, gender, age group and other basic socioeconomic information. The system also includes doctor details such as speciality, gender and so on. The data warehouse system also includes the locations details such as state, city and area in addition to date and time information like year, month, quarter and so on. Finally, the cost of each consultation is calculated for the purpose of reporting and archiving. With respect to the above business scenario, design a star schema (in Microsoft Visio) for the data warehouse that clearly identifies the fact table(s), dimensional table(s), their attributes and measures along with the primary key and foreign key relationships. Clearly state any reasonable assumptions you make.

Explanation / Answer

the fact table will be the consultation table with the foreign keys as doctors_name,Patients_name,Location,Date,time,drug prescribed and test done.

There will be the following dimension tables.

test table with test name as key ,symptoms,result

drug table with drug name as key and dosage

patient table with patient name as key,address,contact details,disease.

doctors table with doctors name,timing,consultation fees,highest degree

location table with location name as key,branches

date tabe with date as key,number of patients

time table with time as key,doctors availability.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.