DataBase System Index of table c Types of index H Primary index: in a sequentially ordered file, the index whose search key specifies the sequential order of the file Also called clustering index The search key of a primary index is usually but not necessarily the primary key H Secondary index: an index whose search key specifies an order different from the sequential order of the file. Also called non-clustering index A Unique index: an index was the accepted way in some database systems to guarantee a uniqueness constraint for a candidate ke Haichang Gao, Software School, Xidian University 23
DataBase System Haichang Gao , Software School , Xidian University 23 Index of Table Types of index Primary index: in a sequentially ordered file, the index whose search key specifies the sequential order of the file. ➢ Also called clustering index ➢ The search key of a primary index is usually but not necessarily the primary key. Secondary index: an index whose search key specifies an order different from the sequential order of the file. Also called non-clustering index. Unique index: an index was the accepted way in some database systems to guarantee a uniqueness constraint for a candidate key
DataBase System Index of table G SQL DDL about index: E CREATE [UNIQUE CLUSTER INDEX <index name> on table name > <column name/> [ASCI dESCI <column name2> ASC desc]].) E. g CREaTE iNDEX b-index on branch(branch name H DROP INDEX index name> Haichang Gao, Software School, Xidian University 24
DataBase System Haichang Gao , Software School , Xidian University 24 Index of Table SQL DDL about index: CREATE [UNIQUE | CLUSTER] INDEX <index_name> ON < table_name > ( <column_name1> [ASC | DESC] [<column_name2 > [ASC | DESC] ]…) ; E.g.: CREATE INDEX b-index ON branch(branch_name) DROP INDEX < index_name > ;
DataBase System ta Unit 3 SQL 43.1 Introduction 13.2 Setting Up the database 43.3 Queries 43. 4 View 43.5 Data manipulation M 3.6 Security Haichang Gao, Software School, Xidian University 26
DataBase System Haichang Gao , Software School , Xidian University 26 Unit 3 SQL 3.1 Introduction 3.2 Setting Up the Database 3.3 Queries 3.4 View 3.5 Data manipulation 3.6 Security
DataBase System b Basic Query Structure G SQL is based on set and relational operations with certain modifications and enhancements G a typical sQl query has the form: SELECT ALA2.. An FROM rl,r2,…,m WHERE P >A represents an attribute >R represents a relation > P is a predicate w This query is equivalent to the relational algebra expression: A1,A2 A(o(1×2×…xm) H The result of an sQL query is a relation. Haichang Gao, Software School, Xidian University 27
DataBase System Haichang Gao , Software School , Xidian University 27 Basic Query Structure SQL is based on set and relational operations with certain modifications and enhancements A typical SQL query has the form: SELECT A1, A2, ..., An FROM r1, r2, ..., rm WHERE P ; ➢ Ai represents an attribute ➢Ri represents a relation ➢P is a predicate This query is equivalent to the relational algebra expression: ( ( )) A1 ,A2 , ,A P 1 2 m r r r n The result of an SQL query is a relation
DataBase System TThe selec clause e The select clause list the attributes desired in the result of a query E corresponds to the projection operation of the relational algebra E Example: find the names of all branches in the loan relation selECt branch name fROM loan w In the relational algebra, the query would be Branch name (loan) H NOTE: SQL names are case insensitive (i.e, you may use upper-or lower-case letters) Haichang Gao, Software School, Xidian University 28
DataBase System Haichang Gao , Software School , Xidian University 28 The SELECT Clause The SELECT clause list the attributes desired in the result of a query corresponds to the projection operation of the relational algebra Example: find the names of all branches in the loan relation: SELECT branch_name FROM loan ; In the relational algebra, the query would be: branch_name (loan) NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.)