Quiz 4 – Database Transactions and Performance Tuning (Course Code: DBMS 101-M4)

Find the solution to the problem below based on the following query:

SELECT    EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX

FROM      EMPLOYEE

WHERE    EMP_SEX = ‘F’ AND EMP_AREACODE = 0181

ORDERBY EMP_LNAME, EMP_FNAME;

 

Use the table below as an example to create two alternative access plans. Use the following assumptions:

  1. a) There are 8,000 employees
  2. b) There are 4,150 female employees.
  3. c) There are 370 employees in area code 0181.
  4. d) There are 190 female employees in area code 0181.

TABLE 4.1: COMPARING ACCESS PLANS AND INPUT/OUTPUT ( I/O) COSTS

 

PLAN STEPOPERATIONI/O OPERATIONSI/O COSTSRESULTING SET ROWSTOTAL I/O COST
AA1Cartesian product

A1 = (PRODUCT X VENDOR)

7,000+3007,3002,100,0007,300
 A2Select rows in A1 WITH MATCHING VENDOR CODES

A2 = σ PRODUCT.v_code = VENDOR.v_code(A1)

2,100,0002,100,0007,0002,107,300
 A3SELECT rows in A2 with V_COUNTRY = ‘FR’

A3 = (σV_COUNTRY = ‘FR (A2))

7,0007,0001,0002,114,300
BB1Select rows in VENDOR with V_COUNTRY – ‘FR’

B1 = σ V_COUNTRY = ‘FR’(VENDOR)

30030010300
 B2Cartesian Product

B2 = (PRODUCT X B1)

7,000 + 107,01070,0007,310
 B3Select rows in B2 with matching vendor codes

B3 = σ PRODUCT.v_code =B1.v_code(B2).

70,00070,0001,00077,310

Due Date: Submit your responses to the Reflection, Provocative Question, Assignments and Assessments by 12pm WAT, on Friday after each module.

Content | Menu | Access panel