Homework answers / question archive / Experiment 4: View and Index Goal 1

Experiment 4: View and Index Goal 1

Computer Science

Share With

---

1. Experiment 4: View and Index
   1. Goal

1. Be familiar with the use of GUI and SQL language to create, update and delete views.

2. Proficient in creating and deleting index using GUI and SQL language.

3. Understand and verify the role of index.

1. 2. Content
1. In the student database, use SQL statement to create a view of students who have taken the database course and are born in 2001. The view includes the information of student number, name, gender and grade. (5 points)
2. Create a view for the supply situation of “SANJIAN” project, including the attributes of supplier code (SNO), part code (PNO) and supply quantity (QTY), with two ways with SQL statement.(view name: V_SPJ) (10 points)
3. Complete the following view query with SQL statement. (10 points)

1. Find out all the parts code and their quantity used by “SANJIAN” project.
2. Find out the supply situation of supplier S1.

4. Update the data of views with SQL statement.(15 points)
   1. 1. Insert a tuple into the view V_SPJ.

Hint:

• In table SPJ, when JNO is permitted to be null, the tuple can be inserted into the actual table. Because of JNO is NULL?the tuple will not appears in the view.
• When the attribute JNO in table SPJ was set to be not null, you can use ‘instead of’ trigger. MySQL does not support to create a trigger on a view, so in the MySQL environment, it is not necessary to answer this question.
  1. 2. Modify the quantity value of any tuple in the view V_SPJ.
     3. Delete one tuple from the view V_SPJ

Hint: Only when the created view can be resoluted, it can be deleted normally, otherwise it will fail to be deleted.

 

5. Create a descending index named IX_CNo for the CNO attribute of C table in student database by using GUI. (5 points)
6. Use SQL statement to complete the following index operation on student database. (15 points)

(1) Create a non-unique index named IX_CNAME on the CNAME attribute of table C.

(2) Create a composite index named IX_ngd_NGD on the table S, which is an ascending index for sname, sgender and sdept attribute sets.

Hint: you can use show index from table name to view the index information on the table.

(3) Delete index IX_ CNo of table C?

(4) Based on the above indexes (table C: primary key index of CNO, general index of CNAME; table S: primary key index of SNO, IX_ NGA composite index), use explain statement to obtain the query plan of each query statement, to observe the index usage in each query statement.

• • • explain select * from c;
    • explain select * from c where cno = ‘1’;
    • explain select * from c where cname=’database’ ;
    • explain select * from c where cname like ‘%database%’;
    • explain select * from c where cname like ‘database%’;
    • explain select * from s where sname ='Zhangli' and sno='2001';
    • explain select * from s where sname ='Zhangli' and sgender='male' and sdept='IS';
    • explain select * from s where sname ='Zhangli' and sgender='male';
    • explain select * from s where sname ='Zhangli';
    • explain select * from s where sgender ='male';
    • explain select * from s where sgender ='male' and sdept='IS';

 

7. Suppose there is a basic table userinfo as follows,  design an experiment to verify the effect of index on database query efficiency. (40 points) create table userinfo

(

          user_id int primary key,  //USER ID

          username varchar(10),   //USERNAME

          gender char(1),                        //GENDER

          age int,                        //AGE

       c_id int                           //NO OF COLLEGE

)

(1) Verify the efficiency difference between indexed and non-indexed queries.

(2) Verify the query efficiency of single field narrow index and multi field wide index, pay attention to understand the left most matching principle in wide index.

(3) Verify the difference of query efficiency between clustered index (primary key index) and secondary index: build clustered index and nonclustered index on the same field to compare query efficiency. (optional)

(4) At present, only memory engine of MySQL supports both b-tree index and hash index. Create a basic table based on memory storage engine in mysql, and verify the query efficiency difference between b-tree index and hash index based on this table. (optional)

 

Tips and requirements:

(1) Import a large amount of data (at least 100millio tuples) for validation.

(2) Use the explain command to analyze whether the SQL query uses the created index, and then analyze the performance reasons based on it.

(3) Generally, b-tree index is suitable for range query and hash index is suitable for point query.

 

 

Optional experiments

4. Refer to the "gaussdb (for MySQL) database maintenance and management manual.docx", and understand the following functions on the DAS side of Huawei cloud database:

(1) Section 1.2 manages the database cluster.

Special attention: this section of the experiment only to view the operation! Adding cluster nodes, changing cluster specifications, resetting administrator account name and password, cluster backup and recovery, parameter modification and other changes that have an impact on the global situation are only try to understand, not do the actual operations.

(2) 1.3.1 performance monitoring.

(3) 1.3.2 performance tuning.