150 Chapter 1 DEPARTMENT COURSE TERM INSTRUCTORS DAY DBMS 110 SPRING 2020 ROYAL W DBMS 110 SPRING 2020 PERRY DBMS 130 SPRING 2020 ROYAL | . DBMS SPRING 2020 FLOOD, PERRY W DBMS 151 SPRING 2020 OKLAK NA DBMS 210 | SPRING 2020 PERRY MW DBMS 235 SPRING 2020 PERRY DBMS 240 SPRING 2020 FLOOD | W DBMS | 260 | SPRING 2020 PERRY | R DBMS 288 SPRING 2020 PERRY NA DBMS 110 SPRING 2020 PERRY MW 11. Discuss the data redundancies in the table above. (3 points) ROOM NMC 243 NMC 243 NMC 239 NMC 239 NA | NMC 239 NMC 239 NMC 239 NMC 235 NA NMC 239 COURSE TYPE LECTURE/LAB LECTURE/LAB LECTURE/LAB LECTURE/LAB LECTURE LECTURE/LAB LECTURE/LAB LECTURE/LAB LECTURE/LAB LECTURE/LAB LECTURE/LAB + Your answer: Data redundancy in the above table is that Perry is coming up a lot like Perry have 3 different on Thursday so they should be in one column instead on three different columns. Describe a situation that would cause a data anomaly. Your answer can describe an update, insertion, or delete anomaly. Refer to 1-6c of the book for guidance. (3 points) Your answer: Chapter 2 FIGURE 2.1 LINKING RELATIONAL TABLES Database name: Cho2_InsureCo Table name: AGENT (first six attributes) AGENT_CODE AGENT_LNAME AGENT_FNAME AGENT_INITIAL AGENT_AREACODE AGENT PHONE 501 Alby Alex 713 228-1249 502 Hahn Leah 615 882-1244 503 Okon John 615 123-5589 Link through AGENT_CODE 501 W Table name: CUSTOMER CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL 10010 Ramas Alfred 10011 Dunne Leona 10012 Smith Kathy 10013 Olowski Paul 10014 Orlando Myron 10015 O Brian Army 10016 Brown James O 10017 Wiliams George 10018 Farriss Anne 10019 Smith Olette S1 CUS_AREACODE CUS_PHONE CUS_INSURE_TYPE 615 844-257311 713 894-1238 J1 615 894-2285 S2 615 894-2180 615 222-1672 713 442-3381 T2 615 297-1228 615 290-2556 S1 382-7185 615 297-3809 S2 CUS_INSURE AMT CUS_RENEW_DATE AGENT_CODE 100.00 05-Apr-2018 250.00 16-Jun-2018 150.00 29-Jan-2019 300.00 14-Oct-2018 100.00 28-Dec-2019 850.00 22-Sep-2018 120.00 25-Mar-2019 250.00 17-Jul-2018 03-Dec-2018 500.00 14-Mar-2019 713 T2 100.00 K Create the business rules based on the tables shown in figure 2.1 (the figure is on page 43). There should be 3 business rules (1 point each, 3 points total). FIGURE P2.6 THE TINY COLLEGE RELATIONAL DIAGRAM COURSE Y CRS CODE DEPT_CODE CRS_DESCRIPTION CRS CREDIT CLASS Y CLASS_CODE CRS CODE CLASS SECTION CLASS_TIME CLASS_ROOM PROF NUM ENROLL Y CLASS CODE STU_NUM ENROLL_GRADE STUDENT Y STU_NUM STU_LNAME STU FNAME STU INIT STU DOB STU HRS STU_CLASS STU_GPA STU_TRANSFER DEPT_CODE STU_PHONE PROF NUM Create the business rules based on the tables shown in figure P2.6. There should be 6 business rules (1 point each, 6 points total). Your answer: Business Rule 1 Business Rule 2 Business Rule 3 Business Rule 4 Business Rule 5 Business Rule 6 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. Create the business rules for the tables PATIENT, ORDER, and MEDICATION. There should be 2 business rules for each table (1 point each, 6 points total). Your answer: Business Rule 1 Business Rule 2 FIGURE P2.15 THE CROW'S FOOT ERD FOR PROBLEM 15 PUBLISHER BOOK publishes submits writes CONTRACT AUTHOR signs th 15. Create the business rules based on the diagram shown in figure P2.15. There should be 8 business rules (1 point each, 8 points total). Your answer: Business Rule 1 Business Rule 2 Business Rule 3 Business Rule 4 Business Rule 5 Business Rule 6 Business Rule 7 Business Rule 8 FIGURE P2.17 THE CROW'S FOOT ERD FOR PROBLEM 17 THEATER MOVIE shows receives REVIEWER REVIEW writes 17. Create the business rules based on the diagram shown in figure P2.17. There should be 6 business rules (1 point each, 6 points total). Your answer: Business Rule 1 Business Rule 2 Business Rule 3 Business Rule 4 Business Rule 5 Business Rule 6