Unit 2: Storage, indexing and security
Database Management Systems-II notes · PTU syllabus (UGCC2523)
On this page
Unit summary
How data is stored on disk decides how fast it can be found. This unit covers external storage, file organisations, indexing and index data structures, guidelines for choosing indexes, and the basics of database security and access control.
After this unit you can
- Explain how data is stored on external storage
- Compare heap, sorted and hashed file organisations
- Explain primary, clustered, secondary, dense and sparse indexes and B+ tree indexes
- Apply index selection guidelines and explain discretionary access control
PTU syllabus topics
- Data on external storage
- file organizations and indexing
- index data structures
- comparison of file organizations
- index selection guidelines
- database security introduction
- access control
- discretionary access control
Data order
Rows stored in index order
Separate structure pointing to rows
Per table
Only one
Many
Speed
Fast range queries
Fast lookups on extra columns
Cost
Slower inserts in the middle
Extra storage
Topic 1
Data on external storage
Databases live on disks (HDD, SSD). Data is read and written in blocks (pages); the cost of a query is mostly the number of page I/Os. The buffer manager keeps frequently used pages in memory.
Topic 2
File organisations
Heap (unordered)
In insertion order
Fast inserts, full scans
Sorted (sequential)
Ordered on a search key
Range queries on that key
Hashed
Placed by a hash function on a key
Equality searches (key = value)
Topic 3
Indexing and index data structures
An index is an auxiliary structure that speeds up searches on a search key, like a book's index.
| Index type | Meaning |
|---|---|
| Primary index | On the ordering key of a sorted file |
| Clustered index | Data records are physically ordered like the index; at most one per table |
| Secondary (non-clustered) | On a non-ordering field; many allowed |
| Dense index | An entry for every record |
| Sparse index | Entries for only some records (one per block) |
Tree-based indexes (B+ trees) support both equality and range searches and stay balanced; hash-based indexes are best for equality searches only.
Topic 4
Comparison and index selection guidelines
- Index columns used often in WHERE, JOIN and ORDER BY.
- Prefer B+ tree indexes for range queries and hash indexes for exact-match lookups.
- Use composite indexes for queries filtering on several columns (column order matters).
- Avoid too many indexes on tables with heavy inserts and updates — every index must be updated too.
- Consider a clustered index on the attribute used most for range queries.
Topic 5
Database security and access control
Security goals: secrecy (only authorised users read data), integrity (only authorised changes) and availability. Discretionary access control (DAC) lets owners grant and revoke privileges:
sqlGRANT SELECT, INSERT ON student TO clerk;
REVOKE INSERT ON student FROM clerk;
GRANT SELECT ON student TO hod WITH GRANT OPTION;Mandatory access control (MAC) uses system-wide security labels (top secret, secret …); role-based access control grants privileges to roles rather than individuals.
Key terms
- Block (page)
- The unit of data transfer between disk and memory
- Index
- A structure that speeds up searching on a key
- Clustered index
- Index whose order matches the physical record order
- Sparse index
- Index with entries for only some records
- Discretionary access control
- Owner-controlled privileges via GRANT and REVOKE
Quick revision
- Cost = page I/Os.
- Heap for inserts, sorted for ranges, hashed for equality.
- One clustered index per table; many secondary indexes.
- B+ tree: equality and range; hash: equality only.
- GRANT/REVOKE implement DAC.
Important exam questions
Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).
Short-answer questions
- Q1.What is a file organisation?
- Q2.Differentiate between dense and sparse indexes.
- Q3.What is a clustered index?
- Q4.Why are B+ trees preferred for database indexes?
- Q5.What does WITH GRANT OPTION do?
Long-answer questions
- Q1.Compare heap, sorted and hashed file organisations.
- Q2.Explain the types of indexes with examples.
- Q3.Discuss the guidelines for selecting indexes.
- Q4.Explain database security and discretionary access control with SQL commands.
Stuck on this unit?
Message SBS on WhatsApp for help with Database Management Systems-II, or to ask about studying BCA at Synetic.