CREATING A DATABASE FROM A TO Z

1. CREATING A DATABASE FROM A TO Z What is the concept? How will the user use this file? Tables What are the entities? What are the relationships between these entities? What are the fields / attributes of these entities? Operations on each table Will we need to add new records? --> ADD NEW Will we need to search a specific record? --> SEARCH After finding this specific record, what do we want to do? --> READ ONLY, UPDATE, DELETE, PRINT => buttons for each N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

2. CREATING A DATABASE FROM A TO Z Menus, Forms, Reports What do you need to put in the main menu? Which options will you put on each form? How will you pass from form to form? Which reports do you need to print? Warning the user When do you need “are you sure? Yes/Cancel” forms? When do you need required fields? Which fields might need a warning involving conditional macro? Conditional Macro: We can arrange so that a macro runs only if a certain condition is met. For example, if the minimum salary is 330.000.000 TL, if the user enters a salary lower than this, he can be warned with a message. N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

3. CREATING A DATABASE FROM A TO Z Example: A health center database Concept: Bilkent Health Center wants to keep patient information in a database. * When the patient comes for the first time, he/she fills out a form stating personal information (ID, name, dept, address, phone, etc) * Each time the patient visits, the doctor fills out a form (the date of visit, diagnosis, medication, medical report if necessary, follow up date if necessary). The doctor also needs to see the details of any previous visits of this patient. * The health center keeps the personal information for each doctor * Doctors get a base salary. In addition, they get paid for each patient they see. N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

4. CREATING A DATABASE FROM A TO Z Entities * Patient personal info ID, name, address, telephone, department * Visit of each patient Date, time, diagnosis, medication, follow-up * Doctor personal info ID, name, address, telephone, specification * Doctor payment info Base salary, number of patients, extra payment, taxation and social security details according to law. N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

5. CREATING A DATABASE FROM A TO Z Relationships * Patient personal info ID, name, address, telephone, department * Visit of each patient must be related to patient general info must also be related to doctor in order to calculate extra pay Date, time, diagnosis, medication, follow-up, ID_patient, ID_doctor * Doctor personal info ID, name, address, telephone, specification * Doctor monthly payment info must be related to doctor general info Base salary, number of patients, extra payment, taxation and social security details according to law,ID N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

6. CREATING A DATABASE FROM A TO Z Types of Relationships * Patient personal info O ---- M Visit of each patient * Doctor personal info O ---- M Visit of each patient * Doctor personal info O ---- M Doctor monthly payment info N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

7. CREATING A DATABASE FROM A TO Z Operations of these entities: Do we need to... ADD NEW... * Patient? * Visit of patient? * Doctor? * Monthly payment of doctor? SEARCH A SPECIFIC... * Patient? * Visit of patient? * Doctor? * Monthly payment of doctor? N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

8. CREATING A DATABASE FROM A TO Z Operations of these entities UPDATE INFO FOR... * Patient? * Visit of patient? * Doctor? * Monthly payment of doctor? DELETE... * Patient? * Visit of patient? * Doctor? * Monthly payment of doctor? N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

9. CREATING A DATABASE FROM A TO Z Operations of these entities PRINT... * Patient? * Visit of patient? * Doctor? * Monthly payment of doctor? N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

10. CREATING A DATABASE FROM A TO Z Menus, forms and reports Main Menu: * Patient Operations Add new patient -> patient personal info form Search an existing patient -> ask for the patient ID View personal info -> use the patient personal info form View medical info (previous visits) -> visit subform Add new visit -> use the same subform * Doctor operations Add a new doctor -> doctor personal info form Doctor payment operations -> payment form (specific doctor!) N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

11. CREATING A DATABASE FROM A TO Z On click, open the patient personal info form in ADD mode On click, ask the patient’s ID no and open that patient’s personal info in Read Only Mode Buttons and macros * Patient Operations Add new patient Search an existing patient View personal info View medical info (previous visits) Add new visit * Doctor operations Add a new doctor Doctor payment operations On click, open the doctor personal info form in ADD mode N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

12. CREATING A DATABASE FROM A TO Z Main menu(autoexec form) Patient operations Doctor operations Patient operations menu (pat_menu form) Add new Search patient Add new patient (patient form in add mode) Return to main menu Visit of this patient Visit of patient (patient_visit subform) Return to main menu N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

13. CREATING A DATABASE FROM A TO Z Patient personal information form ID name address telephone department Photo Buttons and macros Delete this patient Update personal info View medical history Return to main menu On click, open this form in edit mode On click, save all, close this form, open main menu form On click, open subform On click, run a Delete query N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

14. CREATING A DATABASE FROM A TO Z Patient personal information form ID name address telephone department Photo Buttons and macros Delete this patient Update personal info View medical history Return to main menu Run delete query Open “sure_delete” form Yes Cancel Close “sure_delete” form N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring

15. CREATING A DATABASE FROM A TO Z department Delete this patient Update personal info View medical history Return to main menu Run the macro yes_delete Run Macro “sure_delete” Buttons and macros Macro “sure_delete” opens the “sure_delete” form Macro “yes_delete” runs the “patient_delete” query Are you sure you want to delete this patient? Yes Cancel N. Fenmen - CAA292 Database Applications for Business - 2003 - 2004 Spring