Problems Use the contents of Figure 2.1 to work Problems 1, 2 and 3. 1. Write the business rule(s) that govern the relationship between AGENT and CUSTOMER. 2. Given the business rule(s) you wrote in Problem 1, create the basic Crow's Foot ERD. 3. Using the ERD you drew in Problem 2, create the equivalent object representation and UML class diagram. (Use Figure 2.4 as your guide.) Figure P2.4 The Dealco Relational Diagram REGION REGION_CODE REGION_DESCRIPT Details STORE STORE_CODE STORE_NAME STORE_YTD_SALES REGION_CODE EMPLOYEE EMP_CODE EMP_TITLE EMP_LNAME EMP_FNAME EMP_INITIAL EMP_DOB JOB_CODE STORE_CODE JOB JOB_CODE JOB DESCRIPTION JOB_BASE_PAY 64 Using Figure P2.4 as your guide, work Problems 4 and 5. The DealCo relational diagram shows the initial entities and attributes for the DealCo stores, which are located in two regions of the country. 4. Identify each relationship type and write all of the business rules. 5. Create the basic Crow's Foot ERD for DealCo. Using Figure P2.6 as your guide, work Problems 6, 7 and 8. The Tiny College relational diagram shows the initial entities and attributes for the college. Figure P2.6 The Tiny College Relational Diagram COURSE CRS_CODE DEPT_CODE CRS DESCRIPTION CRS CREDIT Details CLASS CLASS CODE CRS_CODE CLASS SECTION CLASS TIME CLASS ROOM PROF NUM ENROLL CLASS CODE STU_NUM ENROLL_GRADE STUDENT STU_NUM STU_INAME STU_FNAME STU INIT STU_DOB STU_HRS STU_CLASS STU_GPA STU_TRANSFER DEPT_CODE STU_PHONE PROF_NUM Figure P2.6 The Tiny College Relational Diagram COURSE CRS_CODE DEPT_CODE CRS DESCRIPTION CRS_CREDIT Details CLASS CLASS CODE CRS_CODE CLASS SECTION CLASS_TIME CLASS ROOM PROF NUM ENROLL CLASS CODE STU_NUM ENROLL_GRADE 6. Identify each relationship type and write all of the business rules. 7. Create the basic Crow's Foot ERD for Tiny College. STUDENT STU_NUM STU INAME STU_FNAME STU_INIT STU_DOB STU_HRS STU_CLASS STU_GPA STU_TRANSFER DEPT_CODE STU_PHONE PROF NUM 8. Create the UML class diagram that reflects the entities and relationships you identified in the relational diagram. 9. Typically, a hospital patient receives medications that have been ordered by a particular doctor. Because the patient often receives several medications per day, there is a 1:M relationship between PATIENT and ORDER. Similarly, each order can include several medications, creating a 1:M relationship between ORDER and MEDICATION. 65 9. Typically, a hospital patient receives medications that have been ordered by a particular doctor. Because the patient often receives several medications per day, there is a 1:M relationship between PATIENT and ORDER. Similarly, each order can include several medications, creating a 1:M relationship between ORDER and MEDICATION. a. Identify the business rules for PATIENT, ORDER, and MEDICATION. b. Create a Crow's Foot ERD that depicts a relational database model to capture these business rules. 10. United Broke Artists (UBA) is a broker for not-so-famous artists. UBA maintains a small database to track painters, paintings, and galleries. A painting is created by a particular artist and then exhibited in a particular gallery. A gallery can exhibit many paintings, but each painting can be exhibited in only one gallery. Similarly, a painting is created by a single painter, but each painter can create many paintings. Using PAINTER, PAINTING, and GALLERY, in terms of a relational database: a. What tables would you create, and what would the table components be? b. How might the (independent) tables be related to one another? 11. Using the ERD from Problem 10, create the relational schema. (Create an appropriate collection of attributes for each of the entities. Make sure you use the appropriate naming conventions to name the attributes.) 65 11. Using the ERD from Problem 10, create the relational schema. (Create an appropriate collection of attributes for each of the entities. Make sure you use the appropriate naming conventions to name the attributes.) 12. Convert the ERD from Problem 10 into a corresponding UML class diagram. 13. Describe the relationships (identify the business rules) depicted in the Crow's Foot ERD shown in Figure P2.13. Figure P2.13 The Crow's Foot ERD for Problem 13 Details advises PROFESSOR H+ STUDENT teaches CLASS 14. Create a Crow's Foot ERD to include the following business rules for the ProdCo 14. Create a Crow's Foot ERD to include the following business rules for the ProdCo company: a. Each sales representative writes many invoices. b. Each invoice is written by one sales representative. c. Each sales representative is assigned to one department. d. Each department has many sales representatives. e. Each customer can generate many invoices. f. Each invoice is generated by one customer. 15. Write the business rules that are reflected in the ERD shown in Figure P2.15. (Note that the ERD reflects some simplifying assumptions. For example, each book is written by only one author. Also, remember that the ERD is always read from the "1" to the M side, regardless of the orientation of the ERD components.) Figure P2.15 The Crow's Foot ERD for Problem 15 66 Figure P2.15 The Crow's Foot ERD for Problem 15 Details publishes 11 submits writes signs H PUBLISHER H+ CONTRACT BOOK AUTHOR 16. Create a Crow's Foot ERD for each of the following descriptions. (Note that the word many merely means more than one in the database modeling environment.) a. Each of the MegaCo Corporation's divisions is composed of many departments. Each department has many employees assigned to it, but each employee works for only one department. Each department is managed by one employee, and each of those managers can manage only one department at a time. b. During some period of time, a customer can download many ebooks from BooksOnline. Each of the ebooks can be downloaded by many customers during that period of time. c. An airliner can be assigned to fly many flights, but each flight is flown by only one airliner. d. The KwikTite Corporation operates many factories. Each factory is located in a region, and each region can be "home" to many of KwikTite's factories. Each factory has many employees, but each employee is employed by only one factory. e. An employee may have earned many degrees, and each degree may have been earned by many employees. 17. Write the business rules that are reflected in the ERD shown in Figure P2.17. e. An employee may have earned many degrees, and each degree may have been earned by many employees. 17. Write the business rules that are reflected in the ERD shown in Figure P2.17. Figure P2.17 The Crow's Foot ERD for Problem 17 Details THEATER REVIEWER HH shows writes MOVIE receives REVIEW