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 otherhospitals (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. Proceduregroup 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. Adrug 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 : 48Procedure 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 holdsboth a short name for the drug and a dosage size, two distinct facts. The dosage size in turn consists of a numeric size and a unit of measure. The three facts should be recorded in separate columns. The Hospital Category identifies whether the hospital is public or private (first fact) as well as whether the hospital provides teaching (second fact). We should establish two columns, Hospital Typeand Teaching Status, to capture these distinct ideas. hidden data We must make sure we have not lost any data in the translation to tabular form. derivable data Our basic goal is nonredundancy. We should remove any data that can be derived from other data in the entity and amend the columns accordingly. The Total Drug Cost is derivable by adding together the Dose Costs multimplied by the Numbers ofDoses. We therefore remove it, noting in our supporting documentation how it can be derived (since it is presumably of interest to the database users, and we need to know how to reconstruct it when required). We can also drop the practice of prefixing hospital numbers with H and surgeon numbers with S. The prefixes add no information, at least when we are dealing with them as data in thedatabase, in the context of their column names. determining the primary key Finally, we determine a primary key for the entity. The choice of primary keys is a critical (and sometimes complex) task. We will simply note that the primmary key is a minimal set of columns that contains a different combination of values for each row of the corresponding table (logical model). Another way of looking at primarykeys is that each value of the primary key uniquely identifies one row of the table. In this case, a combination of Hopital Number and Operation Number will do the job. And the purpose of the primary key is exactly this : to enable us to refer unambiguously to a specific row of a table. What about repeating groups and 1NF ? We need to clean up the mess though. Earlier, we saw that our first task...
Lire le document complet
Veuillez vous inscrire pour avoir accès au document.