SQL Server Mentalist

Hi Folks,

This post is part of Series Database Management Systems

Currently running topic for this series is listed as below :

Series of Database Management Systems

>>Chapter 1 : DBMS [Database Management Systems]

>>Chapter 2 : Database Core Concepts and  Applications

>>Chapter 3 : Record Storage and Primary File Organization <You are Here>

Continuing from my previous post on this series, If you have missed any link please visit link below

We are going to Cover the Following Points in this article

• Buffering of Blocks
• Placing File Records on disk

Buffering of Blocks

Before processing any data, data in terms of blocks is copied into the main memory buffer.

· When several blocks need to be transferred from disk to main memory, several buffers can be reserved in the main memory to speed up the transfer. While one buffer is being read or written, with the help of Input/Output processor, the CPU can process data in the other buffer.

The figure below illustrates how two processes can proceed in parallel. Process A and B are running concurrently in an interleaved fashion. Buffering is most useful when processes can run concurrently in a simultaneous fashion by both 1/0 processor and CPU. The CPU can start processing a block when the data is transferred to main memory from secondary memory, thus transferring the next block into a different buffer. This technique is called double buffering. Double buffering permits continuous reading or writing of data on consecutive disk blocks, which eliminates the seek time and rotational delay for all but the first block transfer. Moreover, data is kept ready for processing, thus reducing the waiting time in the programs.



Interleaved concurrency versus parallel execution

Placing File Records on disk

Record Types: Data is usually stored in the form of records. Each record consists of a collection of related data values. Records usually describe entities and their attributes. For example, an EMPLOYEE record represents an employee entity, and each field value in the record specifies some attribute of that employee, such as NAME, BIRTHDATE, SALARY or SUPERVISOR. A collection of field names and their corresponding data types constitutes a record type or record format definition.

Files, Fixed-length Records, and Variable-length Records: A file is a sequence of records.

Fixed length records:

· All records in a file are of the same record type. If every record in the file has exactly the same size [in bytes], the file is said to be made up of fixed length records.

1	Modern Database Management	MCFadden
2	Database Solutions	Connolly

Variable length records:

If different records in the file have different sizes, the file is said to be made up of variable length records. The variable length field is a field whose maximum allowed length would be specified. When the actual length of the value is less than the maximum length, the field will take only the required space. In the case of fixed length fields, even if the actual value is less than the specified length, the remaining length will be filled with spaces of null values.

A file may have variable-length records for several reasons:

· Records having variable length fields:

The file records are of the same record type, but one or more of the fields are of varying size. For example, the NAME field of EMPLOYEE can be a variable-length field.

1	Modern Database Management	MCFadden
2	Database Solutions	Connolly

· Records having Repeating fields

The file records are of the same record type, but one or more of the fields may have multiple values for individual records. Group of values for the field is called repeating group.

1	Modern Database Management	3	MCFadden	Hoffer	Prescott
2	Database Solutions	2	Connolly	Begg

Here the record length varies depending on the number of authors.

· Records having optional fields:

The file records are of the same record type, but one or more of the fields are optional. That is some of the fields will not have values in all the records. For example there are 25 fields in a record, and out of 25 if 10 fields are optional, there will be wastage of memory. So only the values that are present in each record will be stored.

· Record Blocking and Spanned versus Unspanned Records:

The records of a file must be allocated to disk blocks. If the block size is larger than the record size, each block will contain numerous records. Some files may have unusually large record sizes that cannot fit in one block. Suppose that the block size is B bytes. For a file of fixed length records of size R bytes, with B ³ R we can fit bfr = [(B/R)] records per block. The value bfr is called the blocking factor for the file. In general R may not divide B exactly, so we have some unused space n each block equal to R – (bfr *R) bytes.

To utilize this unused space, we can store part of the record on one block and the rest on another block. A pointer at the end of the first block points to the block containing the remainder of the record. This organization is called spanned, because records can span more than one block. Whenever a record is larger than a block, we must use a spanned organization. If records are not allowed to cross block boundaries, the organization is called unspanned. This is used with fixed-length records having B ³ R.

We can use “bfr” to calculate the number of blocks b needed for file of records

b = [(r/bfr)} blocks.



Figure Types of record organization (a) Unspanned (b) Spanned

Allocating File Blocks on Disk:

There are several standard techniques for allocating the blocks of a file on disk. In contiguous (sequential) allocation the file blocks are allocated to consecutive disk blocks. This makes reading the whole file very fast, using double buffering, but it makes expanding the file difficult. In linked allocation each file block contains a pointer to the next file block. A combination of the two allocates clusters of consecutive disk blocks, and the clusters are linked together. Clusters are sometimes called segments or extents.

File Headers: A file header or file descriptor contains information about a file that is needed by the header and includes information to determine the disk addresses of the file blocks as well as to record format descriptions, which may include field lengths and order of fields within a record for fixed- length unspanned records and field type codes, separator characters.

To search for a record on disk, one or more blocks are copied into main memory buffers. Programs then search for the desired record utilizing the information in the file header. If the address of the block that contains the desired record is not known, the search programs must do a linear search through the file blocks. Each file block is copied into a buffer and searched until either the record is located. This can be very time consuming for a large file.

Hope you will like Series of Database Management Systems series !

If you have not yet subscribe this Blog , Please subscribe it from “follow me” tab !

So that you will be updated @ real time and all updated knowledge in your mail daily for free without any RSS subscription OR news reading !!

Happy Learning and Sharing !!

For More information related to BI World visit our all Mentalist networks Blog

SQL Server Mentalist … SQL Learning Blog

Business Intelligence Mentalist … Business Intelligence World

Microsoft Mentalist … MVC,ASP.NET, WCF & LinQ

MSBI Mentalist … MS BI and SQL Server

NMUG Bloggers …Navi Mumbai User Group Blog

Architectural Shack … Architectural implementation and design patterns

DBA Mentalist …Advance SQL Server Blog

MVC Mentalist … MVC Learning Blog

Link Mentalist … Daily Best link @ your email

Infographics Mentalist … Image worth explaining thousand Words

Hadoop Mentalist … Blog on Big Data

BI Tools Analysis … BI Tools

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS | About Me |