Database Design Verification and Logical Design: Best Exam Study Guide, Exams of Information Technology

Download Database Design Verification and Logical Design: Best Exam Study Guide and more Exams Information Technology in PDF only on Docsity!

Appendix C:Conceptual

Design,Verification, Logical

Design Best Exam Study

Guide Updated Version

Verification, Logical Design Best Exam

Study Guide Updated Version

Discussion Focus

How is a database design verified, and why is such verification necessary?

Use our detailed answer to question 1 to focus class discussion 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 mind 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 learn 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 consultant. (Anticipation of such developments must be factored into the contract negotiations for consulting fees.)

Discuss the role of the system modules.

The use of system modules can hardly be overemphasized in a database design environment. Stress these 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 functions 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 is likely to be enhanced.

Module interfaces must be clear if the modules are expected to work well within the overall system.

Verification, Logical Design Best Exam

Study Guide Updated Version

3. Identify and define each of the module's processes.

Verification, Logical Design Best Exam

Study Guide Updated Version

Specifically, this step requires the identification and definition of the database transactions that represent the module's real-world operations.

Verification, Logical Design Best Exam

Study Guide Updated Version

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 these important performance factors:

Verification, Logical Design Best Exam

Study Guide Updated Version

 Concurrent users For any given system, the more users connected to the system, the longer the data retrieval time.

Verification, Logical Design Best Exam

Study Guide Updated Version

The verification process must include the following steps:

Verification, Logical Design Best Exam

Study Guide Updated Version

1. Identify and define the main entities, attributes, and 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 conclude 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 with 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 modules 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 module. For brevity's sake, we will use the inventory module as an example. 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

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

Verification, Logical Design Best Exam

Study Guide Updated Version

Inventory 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 example, 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 single-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 QC.5A.

Table QC.5A The Classification Hierarchy

Category Class Type Subtype

Hardware

Computer

Desktop Desktop Laptop

P

P

P

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 important to note that each item can belong to only one specific inventory type. 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 description 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 identified until the ITEM is received!) However, to make the request, we must know the requested item's inventory type. Therefore, ORDER is related to the INVENTORY_TYPE, and not to the ITEM.

The hierarchy shown here has led us to develop the classification scheme shown in the text's Inventory Classification Hierarchy, illustrated in table QC.5B:

Verification, Logical Design Best Exam

Study Guide Updated Version

Table QC.5B An Inventory Classification Hierarchy

Verification, Logical Design Best Exam

Study Guide Updated Version

An employee can take many courses, and each course can be taken by many employees.

Verification, Logical Design Best Exam

Study Guide Updated Version

Once you have traced the development of the ERD segment, verify it and then provide sample data for each of the three tables to illustrate how the design would be implemented.

Figure QC.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 QC.6 reflects part of the verification process that began with the M:N relationship between EMPLOYEE and COURSC. (An employee can take many courses and many employees can take each course.)

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 QC.6 allows us to add attributes 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 QC.6’s ERD cannot handle recurrent training requirements. 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_DATC. This approach is illustrated in the examples shown in Tables QC.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 the addition of the TRAIN_DATE to the composite PK prevents the duplication of training records. For example, if you tried 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 QC.6A The EMPLOYEE Table Contents

EMP_NUM EMP_LNAME 105 Ortega

Verification, Logical Design Best Exam

Study Guide Updated Version

Table QC.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 QC.6C The COURSE Table Contents

COURSE_CODE COURSE_DESCRIPTION FAR- 135 - P Aircraft charter regulations for pilots FAR- 135 - M Aircraft maintenance for charter operations HM- 01 Hazardous materials handling WEA- 01 Aviation weather – basic operations 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_DATE. 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 TRAINING table shown in Table QC.6D.

Table QC.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

Verification, Logical Design Best Exam

Study Guide Updated Version

We would recommend 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” table. But if