MIS - data modeling : drug expenditure survey
The survey team wanted to determine which drugs and dosages were being used for various operations, to ensure that correct clinical decisions were being made and that patients and taxpayers were not paying for unnecessary (or unnecessarily expensive) drugs. Each hospital in the survey was given a unique hospital number to distinguish it from other hospitals (in some cases two hospitals had the same name). All hospital numbers were prefixed H (for hospital). Operation numbers were assigned sequentially by each hospital. Hospitals fell into three categories : T for teaching, P for public and V for private. All teaching hospitals were public (T implied P). The operation code was a standard international code for the named operation. Procedure group was a broader classification. The surgeon number was allocated by individual hospitals to allow surgeons to retain a degree of anonymity. The prefix S stood for surgeon. Only a single surgeon number was recorded for each operation. Total drug cost was the total cost of all drug doses for the operation. The bottom of the form recorded the individual antibiotic drugs used in the operation. A drug code was made up of a short name for the drug plus the size of the dose. Here is the original form :

Hospital Number : H17 Hospital Category : P Operation Name : Heart Transplant Surgeon Number : S15 Drug Code MAX 150 mg MIN 500 mg MIN 250 mg

Hospital Name : st Vincent's Contact at Hospital : Fred Fleming Operation Code : 7A Surgeon Specialty : Cardiology

Operation Number : 48

Procedure Group : Transplant

Full Name of Drug Manufacturer Maxicillin Minicillin Minicillin ABC Pharmaceuticals Silver Bullet Drug Co. Silver Bullet Drug Co.

Method of Administration Cost of Dose Number of Doses ORAL IV ORAL 3.50 1.00 0.30 15 20 10

normalization process : working out the conceptual model and determining columns one fact per column (data atomicity) The Drug Code column holds