Database System Learning One

Study book "Database System Tutorial" third Edition Shi Bole

1. What is an index?

a, in a relational database, an index is a database structure associated with a table, and he can make the corresponding table's SQL statements execute faster. The index acts as a catalog of books, and you can quickly find what you want based on the page numbers in the catalog. When a table has a large number of records, to query the table, the first way to search information is a full table search, is to remove all records one by one, and the query criteria for one by one comparison, and then return to meet the criteria of the record, which will consume a lot of database system time and cause a lot of disk I/O operations; , and then find the index value in the index that matches the criteria of the query, and finally find the corresponding record in the table by ROWID (equivalent page number) saved in the index.

b, according to the function of the database, you can create four kinds of indexes in Database Designer: Normal index, unique index, primary key index and clustered index;

Normal Index:

This is the most basic type of index, and it has no limitations such as uniqueness. Normal indexes can be created in the following ways:

Create indexes, such as the name of the CREATE INDEX < index > on tablename (List of columns);

Modify the table, such as ALTER TABLE TableName ADD index [name of index] (list of columns);

Specify an index when creating a table, such as CREATE TABLE TableName ([...], index [name] (List of columns))

Unique index:

A unique index is one that does not allow any two rows to have the same index value.

When duplicate key values exist in existing data, most databases do not allow a newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the Employees table, none of the two employees will have a namesake.

Several ways to create a unique index:

Create indexes such as the name of the Create UNIQUE Index < index > on tablename (List of columns);

Modify the table, such as ALTER TABLE TableName ADD UNIQUE [index name] (List of columns); ；

Specify indexes when creating tables, such as CREATE TABLE TableName ([...], UNIQUE [index name] (List of columns));

PRIMARY KEY index:

Database tables often have one column or column combination whose values uniquely identify each row in the table. This column is called the primary key of the table.

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to the data. For more information about primary keys, see Defining a primary key.

Tip Although a unique index helps locate information, for best performance results, it is recommended that you use a primary key index instead.

2, several keys in the database.

A, super-key: A property or attribute set that uniquely identifies a tuple (row) in a relationship is called a hyper-key of the relational pattern.

B, candidate key: A super-key that does not contain extra attributes is called a candidate key, that is, in the candidate key, if you delete the attribute, it is not the key. (a candidate key is a super-key, which is not necessarily a candidate key).

C, PRIMARY key: The candidate key that the user selected as the tuple identity is called the primary key.

D, foreign key: If the attribute K in mode R is the primary key of the other mode, then K is called the foreign key in the mode R. The value is either null or his primary key value.

Database System Learning One