DBMS Physical Design in file organization

1. DBMS Physical Design in file organization Physical design is concerned with the placement of data and selection of access methods for efficiency and ongoing maintenance

2. File Organization Physical arrangement of the records of a file on secondary storage devices • Sequential • Linked List • Indexed List

3. Sequential File Sequential file sorted in alphabetical order. Sequential files are usually sorted in ID. Sequence order to facilitate batch processing.

4. Sequential File Processing Old Master New Master Process Transaction Sequential files must be recopied from the point of any insertion or deletion to the end of the file. They are commonly used in batch processing where a new master file will be generated each time the file is updated.

5. The Sequential File • fixed format used for records • records are the same length • field names and lengths are attributes of the file • one field is the key filed • uniquely identifies the record • records are stored in key sequence

6. Linked List Linked list to sort data alphabetically within department.

7. Linked List File Processing The next record in a linked list is found at the address stored in the record. Records are added at any location and pointers adjusted to include them. Deletions are not erased, but pointers changed to omit the deleted record.

8. Indexed File(sequential index) Index to access data by department abbreviation.

9. Indexed File Processing Index Index Data File When a record is inserted or deleted in a file the data can be added at any location in the data file. Each index must also be updated to reflect the change. For a simple sequential index this may mean rewriting the index for each insertion.

10. Indexed Sequential File • index provides a lookup capability to quickly reach the vicinity of the desired record. • indexed is searched to find highest key value that is equal or less than the desired key value. • search continues in the main file at the location indicated by the pointer.

11. Some considerations • Only the 1st nodes contain records, but in turn, they point to the next node that contains the next group of sequential records in order to support sequential processing. • The index file is sequential. • Sometimes we need a directory. So, ifthere is a one-to-one correspondence between an index record and a data record, we call the index file a directory.

12. B-Tree Insertion process • Example Node: Inserted items: 1. 70 2. 10 and 30 3. 15 Conclusion: the larger the node, the fewer the levels needed; the more records within a node, the more RAM is needed. 20 40 60 80

13. Auxiliary Storage and File Organization • Auxiliary storage refers to storage other than the main storage (e.g. magnetic tape or direct access devices). • Other names given to auxiliary storage are secondary and backing storage.

14. Following are the categories each storage medium falls under:

15. Magnetic Disk • Magnetic disks range in size from the old 3” (not used nowadays) to the 3 ½”. • Hard disks are usually found to be fixed within computers and are faster and have larger storage capacities.

18. Magnetic Tape • With magnetic tapes, each character is recorded across the width of the tape.

20. Magnetic tape reads in sequence, hence all records must be read irrespective if those records are required. For example to locate a record, stored half way on the magnetic tape will entail all records stored prior to that being read!