SSD7 EXAM3

**************************************************************************************************
PART 1: The database for an online computer parts store

 Question 1: State where the beginning and end of the transaction should occur

  (1).  For query information about parts that the company sells

      The beginning of the transaction : before step 2
      The end of the transaction : after step 2

  Reason:

    Because During the progress, operation in step 2 is a real operation to database, otherwise operation in step 1 is only "chooses a part category from a menu" and in step 3 is only a "displays all the retrieved parts' information".Both of these two don't have any effect on make data inconsistent.So We needn't add them in our transaction which will reduce the concurrency of the application.

  (2).  For query the quantity of each part currently in stock

    The beginning of the transaction : before step 2
    The end of the transaction : after step 2

  Reason:

    Because During the progress,there has a if-else statement ,so we should set two commit operation both at the end of each statement. The operation in step 1 is just let the user "fills in check out information and confirms the order" and don't have effect on make data inconsistent. The step 2 has a  a real operation to database, so we begin the transaction before step 2 and end in each statement.

 Question 2: State what isolation level the transaction should be run at.

  (1).  For query information about parts that the company sells

      The isolation level should be READ UNCOMMIT.

  Reason:

      Because the user only need to read the part's information and would not write them. In order to make the best concurrency. We can set the isolation to READ UNCOMMIT.

  (2).  For query the quantity of each part currently in stock

      The isolation level the transaction  as serializable.

Reason:

      Because operations in this tranction have writing operaions, it must be set as serializable which can avoid the tranction inconsistent.

**************************************************************************************************
PART 2: The database for census

Question 1:State which index supported by PostgreSQL will improve the performance of each query.

 1. Query First:

    Set a hash index on the column encrypted_name.A hash Index as followed:

    select *
    from census
    where encrypted_name='9946561'

   Reason:

   In this query, PostgreSQL is using a sequential scan on the census table on 'encrypted_name '. Because this is an equality selection condition based on 'encrypted_name'.and the hash index can improve the select operation when it is a equal operation.

 2. Query 2:

   Set a b_tree index on column family_income. A b-tree Index as followed:

   select *
   from census
   where family_income between 10500 and 55000;

   Reason:

     In this query, PostgreSQL is using a sequential scan on the census table that visits actual rows. The total run-time cost of this query is available disk-page fetch units and a total run time of 1500.89 msec. So we only need to access data range query and b_tree index can improve the select operation when it lookup in a range. Because the clustering index will improve the operation when it is used in a range and the selective on the corresponding column is very low.

Question 2 : Are any of the indexes you suggest above candidates for clustering.

  We can set a clustering index on the column family_income during the query 2. Because the clustering index will improve the operation when it is used in a range and the selective on the corresponding column is very low.

Question 3 : It occupies one or two disk blocks, would you still index the table?

  NO,If the database is small, and there are only one or two disk blocks,there is no need to use index.Because when we use index that want to minimize the number of sequential scans used in an application. And an index is only useful when the number of tuples that satisfy a condition are small compared to the total number of tuples for the entire table.Because when we set an index in another disk blocks.If we use these indexs to lookup the data.The system have to change the pages ,As we know ,the change between the disk blocks is very low efficiency .To the small database I think maybe no index is better for our database.

 

源码在我的下载，链接：