CHAPTER 5 DATABASES

1. CHAPTER 5 DATABASES Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@jepson.gonzaga.edu Entity-Relationship Modeling (ER-M)

2. 1. Data Modeling(Student-Advisor) • Each semester, each student must be assigned an adviser who counsels students about degree requirements, and helps students register for classes. Each student must register for chasses with the help of an adviser, but if the students’ assigned adviser is not available, the student may register with any adviser. We must keep track of students, the assigned adviser for each, and the name of the adviser with whom the student registered for the current term (therefore, there is a relationship between “Student” and “Course”). Represent this situation of students, advisers and course with an E-R diagram. Also, draw a data model for this situation using the tool you have been told to use in your course. • Entity and attributes • Relationship and cardinality

3. S_ID C_ID C_ID S_ID Advisor Student Course S_NameS_Major C_TitleC_Credits C_TitleC_Credits S_NameS_Major Ad_ID Ad_NameAd_Department Advisor Student Course Ad_ID Ad_NameAd_Department Student_Advisor Enrollment Ad_IDS_ID C_IDS_ID SemesterYear SemesterYear 1a) 1b) break down M:N into two 1:M

4. 2. Data Modeling (Project – Employee) • A company has a number of employees. The attributes of EMPLOYEE include Employee_ID (identifier), Name Address and Birthdate. The company also has many projects with Project_Name and Start_Date, Expected finish date, and Budget. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employee assigned and may have any number of employees assigned. An employee’s billing rate for a project may vary by project and the company wishes to record the applicable billing rate (Billing_Rate) for each employee when assigned to a particular project. A client normally asks the company working more than one project for them. The attributes of CLIENT includes Client_ID, Name, Address, Contact, Title and Phone. • Entity and attributes • Relationship and cardinality

5. Client Client Project Employee Employee Client_ID Client_ID Proj_ID Emp_ID Emp_ID NameAddressContactTitlePhone NameAddressContactTitlePhone Proj_NameStart_DateExp_Fin_DateBudget NameAddressDOB NameAddressDOB Project Proj_ID Proj_NameStart_DateExp_Fin_DateBudget Billing (or Project_Employee) Emp_IDProj_ID Billing Rate 2a) step-1 2 b) step -2 break down M:N into two 1:M

6. 3. Data Modeling(Physician) • A hospital has a large number of registered physicians, Attributes of Physician include Physician_ID (the identifier) and Specialty. Patients are admitted to the hospital by physicians. Attributes of Patient include Patient_ID (the identifier) and Patient_Name. Any patient who is admitted must have exactly one admitting physician. A physician may optionally admit any number of patients. Once admitted, a given patient must be treated by at least by a physician. A particular physician may treat any number of patients, or may not treat any patients. Whenever a patient is treated by a physician, the hospital wishes to record the details of the treatment (Treatment_Detail). Components of Treatment_Detail include Date, Time, and Results. Did you draw more than on e relationship between physician and patient? Why or why not? • Entity and attributes • Relationship and cardinality

7. Physician Patient_ID Patient_ID Physician_ID Patient Patient Specialty Pat_Name Patient_Name Admits Treats 3a) 3 b) break down M:N into two 1:M Physician Physician_ID Admits Specialty Treatment_Detail or (Physican_Patient) Physician_IDPatient_ID DateTimeResults *Note that the “KEY” to Treatment_Detail can be: Pat_ID and Phys_ID (CK)