(Solution Manual) Database Systems Design Implementation and Management

Coronel, 12

Business

Solution Manual

12th Edition

• $15.00 $50.00

---

Description

Solution Manual for Database Systems Design Implementation and Management, The downloadable file consists of 735 pages, and the total size of the file is 11.5 MB. Purchase now only for highly discounted price.

Author(s): Coronel

---

---

Study Note Preview

Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 1 Appendix C The University Lab: Conceptual Design, Verification, Logical Design, and Implementation Discussion Focus How is a database design verified, and why is such verification necessary? Use our detailed answer to question 1 to focus class discuss ion on database design verification. Stress that the verification process uses the initial ER model as a communication tool. The designer may begin the verification process by describing the organization's operations to its end users, basing the detailed description on the initial ER model. Next, explain how the operations will be supported by the database design. Stress that the design must support the end -user application views, outputs, and inputs. Points to be addressed include such questions as:  Is the description accurate? If not, what aspects of the description must be corrected?  Does the model support the end -user requirements? If not, what aspects of the end -user requirements have not been addressed or have been addressed inadequately? Keep in m ind that even a model that perfectly addresses all initially determined end user requirements is likely to need adjustments as those end users begin to understand the ramifications of the database design's capabilities. In many cases, the end users may lea rn what the organization's processes and procedures actually are, thus leading to new requirements and the perception of new opportunities. The database designer must keep such likely developments in mind, especially if (s)he works as a database design con sultant. (Anticipation of such developments must be factored into the contract negotiations for consulting fees.) Discuss the role of the system modules. Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 2 The use of system modules can hardly be overemphasized in a database design environment. Stress thes e module characteristics and features:  Modules represent subsets of the database model: Smaller "pieces" are more easily understood.  Modules are self -contained and accomplish a specific system function; if such a system function must be modified, other fun ctions remain unaffected.  Modules fit into a modular database design, which is more easily modified and adapted to new circumstances. Because modification efforts are focused on a database subset, productivity of both designers and application developers i s likely to be enhanced. Module interfaces must be clear if the modules are expected to work well within the overall system. Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 3 Answers to Review Questions 1. Why must a conceptual model be verified? What steps are involved in the verification process? The v erification of a conceptual model is crucial to a successful database design. The verification process allows the designer to check the accuracy of the database design by:  Re-examining data and data transformations.  Enabling the designer to evaluate the design efficiency relative to the end user's and system's design goals. Keep in mind that, to a large extent, the best design is the one that serves the end -user requirements best. For example, a design that works well for a manufacturing firm may not fit the needs of a marketing research firm, and vice versa . The verification process helps the designer to avoid implementation problems later by:  Validating the model's entities. (Remember the minimal data rul e.)  Confirming entity relationships and eliminat ing duplicate, unnecessary, or improperly defined relationships.  Eliminating data redundancies.  Improving the model's semantic precision to better represent real -world operations.  Confirming that all user requirements (processing, performance, or security) are met. Verification is a continuous activity in any database design. The database design process is evolutionary in nature: It requires the continuous evaluation of the developing model by examining the effect of adding new entities and by confirming t hat any design changes enhance the model's accuracy. The verification process requires the following steps: 1. Identify the database's central entity. The central entity is the most important entity in our database, and most of the other entities depend on it. Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 4 2. Identify and define each module and its components. The designer divides the database model into smaller sets that reflect the data needs of particular systems modules such as inventory, orders, payroll, etc. 3. Identify and define each of the module' s processes. Specifically, this step requires the identification and definition of the database transactions that represent the module's real -world operations. Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 5 4. Verify each of the transactions against the databas e. 2. What steps must be completed before th e database design is fully implemented? (Make sure that you list the steps in the correct sequence and discuss each step briefly.) The DBLC , discussed in detail in Chapter 9, “Database Design,” constitutes a database's history, tracing it from its concept ual design to its implementation and operation. We highly recommend that the database designer follow the DBLC's steps carefully in order to ensure that the database will properly meet all user and system requirements. Before a database can be successfull y implemented, the following steps must be completed: 1. Define the conceptual model's components: entities, attributes, domains, and relationships. 2. Normalize the database to ensure that all transitive dependencies are eliminated and that each entity's attrib utes are solely dependent on its key attribute(s). 3. Verify the conceptual model to ensure that the proposed database will meet the system's transaction requirements and that the end -user and systems requirements will be met. The verification process will p robably delete and/or create entities, attributes, and relationships. It may also refine existing entities, attributes, and relationships. 4. Create the logical design which requires the definition of the table structures , using a specific DBMS (relational, n etwork or hierarchical). Logical design also includes, if necessary, appropriate indexes and views . 5. Create the physical design to define access paths, including space allocation, storage group creation, table spaces, and any other physical storage characte ristic that is dependent on the hardware and software to be used in the system's implementation. 6. Implement the design. Somehow, this last step seems to suffer from planning neglect, to the detriment of the system's operation. Implementation, operation, an d maintenance plans must (at least) include careful definition and description of the activities required to implement the database design:  loading and conversion  definition of database standards  system and procedures documentation: security, backup, and r ecovery  operational procedures to be followed by users  a detailed training plan  identification of responsibilities for operation and maintenanc e. 3. What major factors should be addressed when database system performance is evaluated? Discuss each factor bri efly. Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 6 Database systems performance refers to the system's ability to retrieve information within a reasonable amount of time and at a reasonable cost. Keeping in mind that "reasonable" means different things to different people, we must address at least t hese important performance factors:  Concurrent users For any given system, the more users connected to the system, the longer the data retrieval time. Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 7  Resource limits The fewer resources that are available to the user, the longer the access queues will b e.  Communication speeds Lower communication speeds mean longer response times.  Query response time Queries must be tuned to provide optimum query response tim e. (See Appendix C, “Database Performance Tuning.”) Lack of query response tuning means slow respon se times. Depending on how good the design and the program code are, the query response time can vary from minutes to hours for the same query. Although the preceding discussion is focused on the speed aspect of performance, there are other equally import ant issues that must be considered. A successful database implementation requires a balanced approach to all database issues, including concurrency control, query response time, database integrity, security, backup and recovery, data replication, and data distribution. 4. How would you verify the ER diagram shown in Figure Q C.4? Make specific recommendations. Figure Q C.4 The ERD for Question 4 Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 8 The verification process must include the following steps: 1. Identify and define the main entities, attributes, an d domains. In this case, the main entities are PARTS, SUPPLIER, PRODUCT, and CUSTOMER. Identify proper primary keys and composite and multi -valued attributes. 2. Identify and define the relationships among the entities. By examining the diagram, we may conclu de that several M:N relationships exist: PARTS and SUPPLIER PARTS and PRODUCTS PRODUCT and CUSTOMER 3. Identify the composite entities and their primary and foreign keys. Each composite (bridge) entity creates the connection to maintain a 1:M relationship wit h each of the original entities. 4. Normalize the model. 5. Verify the model, starting with the identification of the central entity. Given the ER diagram's layout, we conclude that the central entity is PRODUCT. 6. Identify each module and its components. Three mo dules can be identified:  Inventory, containing PARTS and SUPPLIER  Production, containing PARTS and PRODUCT  Sales, containing PRODUCT and CUSTOMER 7. Identify each module's processes or transaction requirements. Start by listing known transaction descriptions by modul e. For brevity's sake, we will use the inventory module as an exampl e. The inventory module supports the following transactions:  Add a new product to inventory  Modify an existing product in inventory  Delete a product from inventory  Generate a list of products by product type  Generate a price list with product by product type  Query the product database by product description Check the database model against these transaction requirements, verify the model's efficiency and effectiveness, and make the necessary changes. 5. Describe and discuss the ER model's treatment of the UCL's inventory/order hierarchy: a. Category b. Class c. Type d. Subtype Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 9 The objective here is to focus student attention on the details of the UCL's approach to inventory management. Note that the UCL's ER model uses two closely related entities to manage items in inventory: ITEM and INVENTORY_TYP C. These two entities maintain a 1:M relationship: One item belongs to only one inventory type, but an inventory type can contain many items. Invento ry types are classified through the use of a hierarchy composed of CATEGORY, CLASS, and TYPC. (We may even identify SUBTYPE for each TYPE!) Basically, the hierarchy may be described this way: A category has many classes, and a class has many types. For ex ample, the category hardware includes the classes computer and printer . The class computer has many types that are defined by their CPU: 486 and Pentium computers. Similarly, the category supplies can have several classes : diskette, paper, etc. Each class can have many types : 3.5 DD diskette, 3.5 HD diskette, 8.5x11 paper, 8.5x14 paper, and so on. We may even identify subtypes : Each type can have many subtypes . For example, the class "paper" includes the types “single -sheet ” and “continuous -feed ”; the singl e-sheet type may be classified by subtype 8 x 11 inches or 11 x 14 inches. The following table summarizes some of the inventory types identified in the system. Note that the hierarchy may be illustrated as shown in Table Q C.5A. Table Q C.5A The Classificat ion Hierarchy Category Class Type Subtype Hardware Computer Desktop Desktop Laptop P4 P3 P4 Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 10 Printer Laser Laser Inkjet Inkjet Plotter 8 ppm 12 ppm Color Black 2 x 3 Supply Paper Continuous -feed Single sheet Single sheet 8 x 10 11 x 14 It is importan t to note that each item can belong to only one specific inventory typ e. Also, keep in mind that the ORDER_ITEM entity interfaces with the INVENTORY_TYPE, rather than with the ITEM entity. The reason for this interface is clearly based on the chapter's des cription of the UCL operations: "The CLD requests items without specifying a specific brand and/or vendor." Given this requirement, it is clear that the ITEM can't be identified in the request. (The ITEM's primary key is its serial number, which can't be i dentified until the ITEM is received!) However, to make the request, we must know the requested item's inventory typ e. Therefore, ORDER is related to the INVENTORY_TYPE, and not to the ITEM. The hierarchy shown here has led us to develop the classificatio n scheme shown in the text's Inventory Classification Hierarchy, illustrated in table Q C.5B: Table Q C.5B An Inventory Classification Hierarchy GROUP CATEGORY CLASS TYPE SUBTYPE HWPCDTP5 Hardware (HW) Personal Computer (PC) Desktop (DT) Pentium (P5) HWPC LP48 Hardware (HW) Personal Computer (PC) Laptop (LT) Pentium IV HWPRLS Hardware (HW) Printer (PR) Laser (LS) Standard HWPRDM80 Hardware (HW) Printer (PR) Inkjet (IJ) 80-column Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 11 SUPPSS11 Supply (SU) Paper (PP) Single Sheet (SS) 8.5" x 11" l HWEXHDID Hardware (HW) Expansion Board (EX) Video (VI) XX SWDBXXXX Software (SW) Database (DB) XX XX The classification hierarchy may also be illustrated with the help of the tree diagram shown in Figure QC.5: Figure Q C.5 The INV_TYPE Classification Hierarchy As a Tree Diagram CATEGORY CLASS TYPE SUBTYPEHardware Personal Computer (PC ) Printer (PR) Desktop (DT) Inkjet(IJ) Intel P4 (300) Intel P5 (600) Black (BL) Color (CO) 6. Modern businesses tend to provide continuous training to keep their employees productive in a fast-changing and competitive world. In addition, government regulations often require certain types of training an d periodic retraining. (For example, pilots must take semiannual courses involving weather, air regulations, and so on.) To make sure that an organization can track all training received by each of its employees, trace the development of the ERD segment in Figure QC.6 from the initial business rule that states: An employee can take many courses, and each course can be taken by many employees. Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 12 Once you have traced the development of the ERD segment, verify it and then provide sample data for each of the th ree tables to illustrate how the design would be implemented. Figure Q C.6 The ERD Segment for Question 6 Follow the verification steps described in the answer to question 4. Note that the composite TRAINING entity shown in Figure Q C.6 reflects part of the verification process that began with the M:N relationship between EMPLOYEE and COURS C. (An employee can take many courses and many employees can take each cours e.) Part of the verification process involves the elimination of multi -valued attributes. For example, an EMPLOYEE table that contains an attribute EMP_TRAINING containing strings such as “fire safety, weather, air regulations” have already been eliminated by the composite TRAINING entity. The structure shown in Figure Q C.6 allows us to add att ributes to ensure that training details – such as dates, grades, training locations, etc. -- can be traced, too. One additional – and very important -- point is worth mentioning: at this point, Figure Q C.6’s ERD cannot handle recurrent training requireme nts. That is, if some courses must be retaken periodically, as is common in many transportation businesses, the TRAINING entity’s PK – at this point composed of the EMP_NUM + COURSE_CODE – will not yield a unique value if the course is retaken from time to time. The solution to this problem can be found in either one of two ways: 1. Add the training date to the TRAINING entity’s composite PK to become EMP_NUM + COURSE_CODE + TRAIN_DAT C. This approach is illustrated in the examples shown in Tables Q C.6A through QC.6C. Note that employee 105 took the FAR -135-P course on 26-Sep-2013 and on 11-Feb-2014. Employee 101 took the WEA -01 course on 26- Sep-2013 and on 26-Mar-2014. Note that th e addition of the TRAIN_DATE to the composite PK prevents the duplication of trai ning records. For example, if you tried Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 13 to enter the first TRAINING record twice, the combination of EMP_NUM+COURSE_CODE+TRAIN_DATE would not be unique and the DBMS would diagnose an entity integrity violation. Table Q C.6A The EMPLOYEE Table Contents EMP_ NUM EMP_LNAME 105 Ortega 101 Williams Table Q C.6B The TRAINING Table Contents EMP_NUM COURSE_CODE TRAIN_DATE TRAIN_GRADE 105 FAR-135-P 26-Sep-2013 90 105 HM-01 18-Dec-2013 92 101 FAR-135-P 23-Nov-2013 93 105 WEA -01 10-Mar -2014 87 101 HM-01 15-Sep-2013 91 101 WEA -01 26-Sep-2013 85 105 FAR-135-P 11-Feb-2014 97 101 WEA -01 26-Mar -2014 89 Table Q C.6C The COURSE Table Contents COURSE_CODE COURSE_DESCRIPTION FAR-135-P Aircraft charter regulations for pilots FAR-135-M Aircraft maintenance for charte r operations HM-01 Hazardous materials handling WEA -01 Aviation weather – basic operations Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 14 WEA -02 Aviation weather – instrument operations 2. Create a new PK attribute named TRAIN_NUM to uniquely identify each entity occurrence in the TRAINING entity , and then create a composite index composed of EMP_NUM + COURSE_CODE + TRAIN_ DAT E. This action will remove the weak/composite designation from the TRAINING, because the TRAINING entity’s PK is no longer composed of the PK attributes of the EMPLOYEE and COURSE entities. (And the “receives” and “is used in” relationships will no longer be classified as “identifying” – thus changing the relationship descriptions from “identifying” or “strong” to “non -identifying” or weak”). The composite index will prevent the duplication of records. Note the change in the structure and contents of the TRA INING table shown in Table Q C.6D. Table Q C.6D The Modified TRAINING Table Structure and Contents TRAIN_NUM EMP_NUM COURSE_CODE TRAIN_DATE TRAIN_GRADE 1203 105 FAR-135-P 26-Sep-2013 90 1204 105 HM-01 18-Dec-2013 92 1205 101 FAR-135-P 23-Nov-2013 93 1206 105 WEA -01 10-Mar -2014 87 1207 101 HM-01 15-Sep-2014 91 1208 101 WEA -01 26-Sep-2013 85 1209 105 FAR-135-P 11-Feb-2014 97 1210 101 WEA -01 26-Mar -2014 89 We would recommen d the second approach. Generally speaking, single -attribute PKs are preferred over composite PKs. Single -attribute PKs are more easily handled if the table is to be linked to a related table later. (The linking is done through a FK – which is the PK in the “parent” tabl e. But if the parent table uses a composite PK, how can you then create the appropriate FK?) In any case, the declaration of a composite PK automatically generates a matching composite index, so you would not decrease the index library if you used approach 1. 7. You read in this appendix that: Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation 15 An examination of the UCL's Inventory Management module reporting requirements uncovered the following problems:  The Inventory module generates three reports, once of which is an Inventory Movement Repor t. But the inventory movements are spread across two different entities (CHECK_OUT and WITHDRAW). That spread makes it difficult to generate the output and reduces the system's performanc e.  An item's quantity on hand is updated with an inventory movement t hat can represent a purchase, a withdraw al, a check -out, a check -in, or an inventory adjustment. Yet only the withdrawals and check -outs are represented in the system. What solution was proposed for that set of problems? How would such a solution be usefu l in other types of inventory environments? The proposed solution was to create a common entry point for all inventory movements. This common entry point is represented by a new entity named INV_TRANS. The INV_TRANS entity is used to record an entry for e ach inventory transaction. In other words, the system keeps track of all inputs to and withdrawals from inventory by using this INV_TRANS entity. It is important to realize that the INV_TRANS entity is a crucial entity in the system, because it reflects all item transactions. Such a solution is not unique to the UCL's inventory system: Most inventory systems must be able to keep track of such transactions. Having a central point of reference facilitates the processing, updating, querying, and reporting cap abilities of the inventory system. The UCL's data model keeps track of several types of inventory transaction purposes or motives: checkouts, withdrawals, adjustments, and purchases. Note the system's flexibility: The user is able to classify all inventor y transactions by type and/or motiv e. In addition to being flexible, the UCL system is easily expandable: If necessary, the system can support additional types of inventory transaction motives. For example, the system may be expanded to include inter -ware house inventory transfers, items retired from inventory because they are date - limited, and so on. (Date -limited inventory is typical for such things as pharmaceuticals, food, etc.) Given its flexibility and expandability, we may conclude that the UCL syst em's inventory data model represents a very viable solution to modeling real world inventory transactions. Therefore, it may be used to fit into just about any inventory environment.