Data Indexing - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Data Indexing - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Data Indexing

Purposes of Data Indexing

• What is Data Indexing?
• Why is it important?

Database Management Systems

• The file system that manages a database.
• Database - is an organized collection of logically related data.

How DBMS Accesses Data?

• The operations read, modify, update, and delete are used to access data from database.
• DBMS must first transfer the data temporarily to a buffer in main memory.
• Data is then transferred between disk and main memory into units called blocks.

Physical Storage Devices

• Random Access Memory – Fastest to access memory, but most expensive.
• Direct Access Memory – In between for accessing memory and cost
• Sequential Access Memory – Slowest to access memory, and least expensive.

More Time Factors

• Querying data out of a database requires more time.
• DBMS must search among the blocks of the database file to look for matching tuples.

Properties of Data Index

• It contains a search key and a pointer.
• Search key - an attribute or set of attributes that is used to look up the records in a file.
• Pointer - contains the address of where the data is stored in memory.
• It can be compared to the card catalog system used in public libraries of the past.

Two Types of Indices

• Ordered index (Primary index or clustering index) – which is used to access data sorted by order of values.
• Hash index (secondary index or non- clustering index ) - used to access data that is distributed uniformly across a range of buckets.

Hash Index

Definition of Bucket

• Bucket - another form of a storage unit that can store one or more records of information.
• Buckets are used if the search key value cannot form a candidate key, or if the file is not stored in search key order.

Indexing Definitions

• Access type is the type of access being used.
• Access time - time required to locate the data.
• Insertion time - time required to insert the new data.
• Deletion time - time required to delete the data.
• Space overhead - the additional space occupied by the added data structure.

Types of Ordered Indices

• Dense index - an index record appears for every search-key value in the file.
• Sparse index - an index record that appears for only some of the values in the file.

Dense Index Insertion

• if the search key value does not appear in the index, the new record is inserted to an appropriate position
• if the index record stores pointers to all records with the same search-key value, a pointer is added to the new record to the index record
• if the index record stores a pointer to only the first record with the same search-key value, the record being inserted is placed right after the other records with the same search-key values.

Dense Index Deletion

• if the deleted record was the only record with its unique search-key value, then it is simply deleted
• if the index record stores pointers to all records with the same search-key value, delete the point to the deleted record from the index record.
• If the index record stores a pointer to only the first record with the same search-key value, and if the deleted record was the first record, update the index record to point to the next record.