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

These lecture slide are very easy to understand and very helpful to built a concept about the foundation of computers and Database Design.The key points in these slide are:Data Indexing, Purposes of Data Indexing, Concept of File Systems, Stores and Organizes, Database Management Systems, Accesses Data, Buffer in Main Memory, Time Factors, Physical Storage Devices, Search Key

Typology: Slides

2012/2013

Uploaded on 04/27/2013

arunima
arunima 🇮🇳

3

(2)

99 documents

1 / 37

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Data Indexing
Docsity.com
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24
pf25

Partial preview of the text

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.