Database Storage and File Systems

• After this unit, students will be able to understand and differentiate storage mechanisms.
• Indexing and Hashing Techniques can be applied for Efficient Data Retrieval.
• Query Processing and Optimization Techniques can be evaluated.
• Database Performance can be optimized using Indexing and Query Optimization.

Storage Mechanisms & File Systems

• Secondary storage examples include Hard disks, SSDs, and Magnetic Tapes.
• RAID stands for Redundant Array of Independent Disks.
• RAID has Levels and Benefits.
• Sequential, Heap, Hashed, and Clustered are types of File Organization.

Storage Management

• Magnetic disks include Magnetic tape, Floppy disks, and Hard-disk drives with Tens of milliseconds access time.
• Flash-based Storage include USB, SSD, SD cards, MicroSD cards with 20-100 microseconds access time.
• Main memory includes RAM, ROM, Cache, and Registers with Tenth of microsecond access time.
• Optical Storage includes Digital Video Disk (DVD).
• Tape Storage is also available.

Storage Structure – Main Memory

• Primary Storage is also known as Main Memory.
• RAM (Random Access Memory) is implemented using semiconductor technology – DRAM (Dynamic RAM).
• The CPU automatically loads instructions to main memory for execution.
• All the program and data cannot reside in the main memory permanently because it is too small and volatile.

Storage Structure – Secondary Memory

• Secondary Storage is an extension of main storage.
• Large quantities of data can be held permanently
• A common storage system consists of Registers, Memory and Magnetic Disc
• Many options exist for secondary storage.
• Differences among storage types: Speed, Cost, Size, and Volatility.

Storage Device Hierarchy

• Registers are at the top, followed by cache, main memory, solid-state disk, magnetic disk optical disk and finally magnetic tapes.
• As you move up the hierarchy, memory becomes more volatile, expensive and faster.
• Semiconductor memory represents a higher level, while solid-state, magnetic, and optical disks fall lower in the hierarchy.
• As you move down the hierarchy, the cost per bit decreases and the access time increases.

RAID (Redundant Array of Independent/Inexpensive Disks)

• RAID combines multiple physical disk drives into one logical unit to improve performance, reliability, or both using data storage virtualization technology.
• RAID enhances performance through read and write optimization on disks.
• Security and Availability are enhanced (24 x 7).

RAID Levels

• RAID 0 splits data (striped) across disks, offering high performance but no redundancy.
• RAID 1 mirrors (duplicates) data on 2+ disks, providing high fault tolerance but with a storage efficiency of 50%.
• RAID 5 stripes data and parity across 3+ disks, balancing performance and fault tolerance, but rebuild time can be long.
• RAID 6 is like RAID 5 but includes extra parity, allowing for more fault tolerance but resulting in more complex and slower writes.
• RAID 10 (1+0) combines mirroring and striping, offering high performance and redundancy but at a high cost due to the number of disks needed.

File Organization Types:

• Heap: uses random inserts.
• Sequential: uses range queries and batch jobs
• Hashed: uses exact-match lookups.
• Clustered: uses frequent joins, grouped data.

Indexing

• A data structure technique used to quickly retrieve records from a database table based on some columns (attributes), just like an index in a book
• Reason: Speeds up search and query performance.
• Reason: Reduces disk I/O.
• Reason: Essential for large databases with millions of records.

Time Complexity and Indexing

• Hard disks can have a block size of 1000 bytes, and record size of 250 bytes.
• The average time to search an unordered hard drive is the bottleneck.
• Indexing reduces the average search time

Indexing Techniques

• Types: Primary, Secondary, and Clustered, Non-clustered.