Number of records fit into a disk block and number of disk blocks required in DBMS

Find the number of disk blocks to store certain number of records, find the number of records that fit into one block in DBMS,  blocking factor, number of records without spanning, number of records with spanning

Exercise:

Suppose we have blocks of size 2048 bytes that we use to store fixed-length records. Each block has a 32 byte header used to store information including the number of records in the block.

(a) Suppose we have records consisting of a 20 byte header, and 3 columns of size 15 bytes, 6 bytes and 12 bytes respectively. Within each record, fields can start at any byte. How many records can fit in a block?

(b) Suppose that we have records each with a 20 byte header and 500 bytes of data. How many blocks will we need to store 4 records if no spanning is allowed?

 

Solution:

As each block has a 32 byte header, the available space for storing records is,

Available space = Block size – Header size = 2048 – 32 = 2016 bytes

(a) The space required to store each record (fixed length) is the sum of the record’s header and the column sizes.

Record size = Header size + column size = 20 + 15 + 6 + 12 = 53 bytes.

The number of records that can fit in each block is,

Blocking factor = Block size / record size = 2016 / 53 = 38 records

 

Blocking factor is a measure to count the number of records that can fit into a disk block. The block size is divided by the record size in case of fixed length records, to calculate the blocking factor. The average of record size may be used if the records are variable length.

 

(b) If no spanning is allowed, we cannot store partial records into disk blocks. We can only store full length record in any disk block. If a record doesn’t fit entirely into the remaining space of a disk block, we need to move that record into the next block.

Available block space = 2016 bytes

Record size = 20 + 500 = 520 bytes per record.

We like to store 4 such records, hence 520 * 4 = 2080 bytes.

As spanning is not permitted, only 3 records of 520 byte each can fit into a single block. The fourth record has to be stored in the next block. Hence, we need 2 blocks to store 4 records.

 

********************

Related posts:

• Go to Multiple Choice Questions in DBMS home

• Go to Normalization - MCQs page

• Go to General Mixed Quiz in all topics of DBMS page

• Go to Advanced DBMS Concepts page

What are the benefits of 3nf and bcnf - question and answers

What are the benefits of third normal form and boyce-codd normal form, the benefits of 3nf and bcnf

 

Benefits of 3NF and BCNF

 

Third Normal Form (3NF) insists that all facts in a table are about the key (or the thing that the key identifies), the whole key and nothing but the key. [G K Gupta, DBMS, Tata McGraw Hill]. It eliminates non-key dependencies.

 

Boyce Codd Normal Form (BCNF) demands that every non-trivial determinant is a candidate key. It is even stricter that all the functional dependencies must have the candidate keys on the left hand side. Though 3NF insist on removing non-key dependencies, it accepts a transitive FD where a key attribute determined by non-key attribute. BCNF eliminates this FD too to become a stricter normal form.

 

Both 3NF and BCNF provides the following benefits;

 

• Stricter than 2NF

          Eliminates partial-key dependencies and non-key dependencies. [Please refer below]

 

• Data redundancy is reduced

          Due to non-key dependency, whenever a value occurs in the table for determinant attribute, the dependent attribute value is repeated. 3nf insist on every non-key attribute to non-transitively depend on the candidate key, the redundancies caused by non-key dependencies are eliminated.

 

• Data modification anomalies are reduced

          Insert, delete and update anomalies that are present in a non-3nf table are eliminated.

 

• Increased data quality

          Ensures that business rules (expressed as functional dependencies) are correctly enforced by keys in your data model.

 

Few requried descriptions

 

What is partial-key dependency? A functional dependency where the determinant (left hand side attribute) uniquely determines a non-key attribute.

What is non-key dependency? A functional dependency where both the determinant and determiners are not keys.

 

Example:

Consider a relation R(A, B, C, D, E) with set F of functional dependencies F = {A→B, C→D, D→E}

For this relation, AC is the candidate key.

As per the given set of functional dependencies, B depends on A alone (A→B), D depends on C alone (C→D). These dependencies are referred as partial-key dependencies because A and C are parts of candidate key AC.

 

The FD D→E is said to be a non-key dependency because neither D nor E is a key.

 

***************************

 

Related links:

• Normalization – solved exercises

• How to find closure

• Database Closures - Home

• 2NF, 3NF and BCNF

• Multiple Choice Questions in DBMS

• Normalization - MCQs

• General Mixed Quiz in all topics of DBMS

• Advanced DBMS Concepts

 

CP7202 - Advanced Databases November December 2016 Anna University Question

CP 7202 Advanced Databases question paper – November/December 2016 

Question Paper Code : 17312 M.E./M.Tech. DEGREE EXAMINATION, NOVEMBER/DECEMBER 2016 Second Semester Computer Science and Engineering CP 7202 — ADVANCED DATABASES (Common to M.E. Computer Science and Engineering (With specialization in Networks) and M.Tech. Information Technology) (Regulation 2013) Time : Three Hours                                                          Maximum : 100 marks Answer ALL Questions PART A – (10 X 2 = 20 marks) 1. What is three-tier client-server architecture? 2. Define commit protocol. What are its types? 3. What is meant by object persistence? 4. Differentiate between ODL and OQL. 5. Mention the design principles for active rules. 6. Write the different types of queries used in spatial database. 7. Mention the significance of handoff management. 8. How is mining different from searching? 9. Can you store XML in databases? Justify your answer. 10. List some of the cloud data models. PART B – (5 X 13 = 65 marks) 11. (a) Explain inter and intra query parallelism in parallel databases. (13) Or (b) (i) Explain two-phase commit protocol used in distributed databases. (7) (ii) What is distributed query processing? Explain in detail with an example. (6) 12. (a) (i) Describe about object identity, object structure and type constructors in Object oriented databases. (6) (ii) Distinguish between object oriented and object relational databases. (7) Or (b) Explain the components of ODMG model. (13) 13. (a) Describe the syntax and semantics of Active databases explain how the active rules are specified in Oracle. (13) Or (b) (i) How are recursive queries specified in SQL? Explain. (8) (ii) Describe about Temporal databases. (5) 14. (a) Explain about the mobile transaction models. (13) Or (b) Explain the various data classification methods for data mining. (13) 15. (a) Write a note on: (i) Native XML database. (7) (ii) Web databases. (6) Or (b) Describe cloud storage architecture with neat diagram. (13) PART C – (1 X 15 = 15 marks) 16. (a) How is concurrency control achieved in distributed databases? Justify your answer with an example. Or (b) Mention the problems of location-dependent data distribution. How does it affect the data integrity and data consistency? Explain your answer. ________________________

Go to ME/MTech Anna University Previous Year Questions page

CS 9221 - Database Technology Anna University ME/MTech Question paper
CS 921 - Database Technology Anna University ME/MTech question paper
CP 7202 - Advanced Databases Anna University ME/MTech Question paper
Anna University Exam Questions with Answers