IST 220 – Intro to Databases

1. IST 220 – Intro to Databases Analyzing Data Needs

2. Business Scenario I • A doctor write a prescription for a patient. Doctor and clinic data is stored in the database. • Nouns used • Doctor • Prescription • Patient • Clinic • Data (not a domain concept) • Database (not a domain concept) • The way data is currently collected/recorded • Excel-based file that kept track of Px data on pages 23 ~ 25 • Excel-based file that kept track of doctor data on page 25

3. Business Scenario II • A patient presents prescription to pharmacist. Data about pharmacists and other employees is stored in the database. • Nouns used • Patient (already identified) • Prescription (already identified) • Pharmacist (one type of employee) • Employee • The way data is currently collected/recorded • Outlook-, Excel-, and paper-based files that kept track of employee data on pages 27 ~ 29

4. Business Scenario III • Pharmacy staff enters customer, insurance, and prescription data into the database. • Nouns used • Customer (a better term for patient) • Insurance (with additional discussion with the client, you got to know that they kept track of health plans for customer households) • Staff (essentially employees, but, together with pharmacist, it can be implied that there is a need for)  job title • The way data is currently collected/recorded • Paper-based form used to collect data on page 20 • Excel-based file that kept track of the data on pages 21 ~ 22

5. Business Scenario IV • Pharmacist fills prescription. The database records the prescription and reduces the drug inventory by the correct amount. • Nouns used • Fill refill • Drug (inventory, actually the amounts available are not of interest; just the types of drugs are recorded.) • The way data is currently collected/recorded • Excel-based file that kept track of the data on page 26

6. Business Scenario V • Customer picks up prescription and makes payment. The database stores the cost and price of the drug, which are used to generate the profit amount. • Nouns used • Payment (not really kept tracked in detail; just the amount due is recorded for each household) • Cost and price of the drug, attributes of drug • Profit amount: report, not raw data to be recorded

7. Missing Information • Maria (the HR director) needs to keep track of • Training classes for staff to maintain their certifications • Performance review • Nouns used • Training class (and employee training enrollment) • Review(attribute of employee)

8. ER Modeling – Entities • A List of Entities Identified • Clinic • Doctor • Prescription • Customer • Health plan • Household • Employee • Job title • Refill • Drug • Training class • Employee training

9. Problems in Identifying Fields • Use employee entity as an example • Name: break down to atomic parts: f.n., l.n., etc • Address: same as name, use street #, city, state, etc • Age: derived from B-date • Years w/ company: same as age • Position: shared by employees w/ in the same title • Job description: same as position • Pay rate: two ways an employee may get paid • Annual review • Training classes attended: some have more some have none • Bilingual and attendance info • Prescriptions filled: same as training classes

10. ER Modeling – Relationships • Cardinality • One to many (1:m, or many to one, m:1) • Many to many (m:n) • One to one (1:1)

11. ER Modeling – Relationships • Referential integrity • Primary key: a unique identifier that can distinguish records from each other, such as ClinicID in the Clinic table • Foreign key: a field added to a child table (one the many side) to relate its records to the associated record in the parent table (on the one side), such as the ClinicID in the Doctor table • Foreign: a field doesn’t naturally belong to a child table, yet copied from the parent table • Key: it’s a (primary) key in the parent table, though not in the child table in which the FK exists