Redirecting to

  Prev   Next
In RDBMS, data is stored in the form of table but in actual this huge amount of data is stored in physical memory in the form of files. File: File is a collection of related information that can be recorded on secondary storage. File Organization: Files organization specifies that how file records are mapped on to disk block. It mainly refers to the logical relationship among various records. Files of the fixed length records are easier to implement than the files of variable length records. File structure in DBMS: File structure Objective of file organization: 1. Records can be selected as fast as possible. 2. Operations on the records (insert, update, delete) should be quick and duplicated records cannot be induced as a result of these operations. 3. Records should be stored efficiently so that the cost of storage is minimal. Methods of organizing files: 1. Sorted file 2. Unsorted file 1. Sorted file: in this, records of a file are sorted according to some attribute so that access is very fast. 2. Unsorted file: in this, records are ordered randomly. In this, access is very slow as it requires the linear search. Difference between sorted and unsorted files: Difference between sorted and unsorted file Types of file organization: 1. Sequential File Organization. 2. Heap File Organization. 3. Hash File Organization. 4. B+ tree File Organization. 5. Cluster File Organization. Tabular difference between types of file organization: Tabular difference between types of file organization 1. Sequential File Organization: Records are stored and accessed in an order using a key field. Searching is sequential throughout the file i.e. record by record. Theory with examples: sequential-file-organization file-organization-set-1 2. Heap File Organization: Records are stored at the end of the file but the address in the memory is random. No sorting or ordering is required. Theory: file-organization-set-1 heap-file-organization 3. Hash File organization: Records are stored at the hash address generated. It uses the computation of hash function on some fields of the records and the output of the hash function determines the address of the disk block where the records are to be placed. The field on which hash function is calculated is known as hash field and if that field is acts as a key of the relation then it is known as hash key. Theory: hashing-in-dbms hash-file-organization 4. B+ Tree organization: Records are stored in tree like structure. B+ tree mainly used to store large amount of data that will not fit in main memory so for this mainly secondary memory is used to store the leaf nodes of the tree and internal nodes of the tree are stored in main memory. In B+ tree the leaf nodes store data and all other nodes (index nodes) are used as indexing. Theory: file-organization-set-3 b-plus-file-organization 5. Cluster file Organization: In this, frequently joined tables are clubbed into one file based on cluster key. This method reduces the searching cost for various records in different files. Types of cluster file organization: a. Indexed clusters. b. Hash clusters. a. Indexed clusters: records are grouped based on the cluster key and stored together. b. Hash clusters: value of hash key is generated for the cluster key and store the records with the same hash key value. Indexing in DBMS: It is used to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed. Index is a type of data structure which is used to locate and access the data in a database table quickly. Indexing in the database is like the what we see in books. Index table: it is a small table having only two columns in which first column contains a search key (which is a copy of the primary or candidate key of a table) and the second column contains pointers holding the reference of the disk block where that particular key value can be found.
Search key Data reference
Where search key is primary or candidate key of a table. Video on Indexing : Indexing Theory: indexing-in-databases-set-1 Types of indexing: 1. Primary indexing. 2. Clustered indexing. 3. Secondary indexing. Video on types of indexing: types of indexing 1. Primary indexing: Primary index is defined on an ordered data file. The data file is ordered on a primary key (key field) of a table. In this, primary keys are stored in sorted order so that searching operation become fast and efficient. Index can be of two types: a. Dense index b. Sparse index a. Dense index: there is an index record for every search key value in the database therefore searching is faster. In this, number of records in the index table is equal to the number of records in the main table. It needs more space to store index record itself. dense_index b. Sparse index: in this, index records are not created for every search key i.e. index records are created for few search keys. Instead of pointing to each record in the main table, the index points to the records in the main table in a gap. sparse_index Video on primary indexing: primary indexing Numerical on primary indexing: primary indexing 2. Cluster indexing: Cluster index is defined on sorted data file with two columns, one for non-key values and one for block pointer. The data file is ordered on a non-key field. The index is created on non-key (key may be primary key or candidate key) columns which may not be unique for each record so for this we will group two or more columns to get the unique value and create index out of them. Video on cluster index: cluster index 3. Secondary indexing: Secondary index may be generated from a field which is a primary/candidate key or a non-key. Video on secondary index: secondary index Numerical on secondary index: Numerical on secondary index Secondary Vs Primary Index: Secondary Vs Primary Index SQL queries on clustered and non-clustered indexes: SQL queries on clustered and non-clustered indexes
Id Name