Tag Archives: Extent and Pages

Database Concepts


• What is database or database management systems (DBMS)?
The following are examples of database applications:
* computerized library systems
* automated teller machines
* flight reservation systems
* computerized parts inventory systems

• What is difference between DBMS and RDBMS?


Many DBA’s think that RDBMS is a Client Server Database system but thats not the case with RDBMS.


• What are CODD rules?
A relational DBMS must use its relational facilities exclusively to manage and interact with the database.
The rules:

These rules were defined by Codd in a paper published in 1985. They specify what a relational database must support in order to be relational. These rules have been considerably extended in reference [1].
1. Information rule

* Data are represented only one way: as values within columns within rows.
* Simple, consistent and versatile.
* The basic requirement of the relational model.

2. Guaranteed access rule

* Every value can be accessed by providing table name, column name and key.
* All data are uniquely identified and accessible via this identity.

3. Systematic treatment of null values

* Separate handling of missing and/or non applicable data.
* This is distinct to zero or empty strings
* Codd would further like several types of null to be handled.

4. Relational online catalog

* Catalog (data dictionary) can be queried by authorized users as part of the database.
* The catalog is part of the database.

5. Comprehensive data sublanguage

* Used interactively and embedded within programs
* Supports data definition, data manipulation, security, integrity constraints and transaction processing
* Today means: must support SQL.

6. View updating rule

* All theoretically possible view updates should be possible.
* Views are virtual tables. They appear to behave as conventional tables except that they are built dynamically when the query is run. This means that a view is always up to date. It is not always theoretically possible to update views. Codd himself, did not completely understand this. One problem exists when a view relates to part of a table not including a candidate key. This means that potential updates would violate the entity integrity rule.

7. High-level insert, update and delete

* Must support set-at-a-time updates.
* ie. Transactions
* eg: UPDATE mytable SET mycol = value WHERE condition;
Many rows may be updated with this single statement.

8. Physical data independence

* Physical layer of the architecture is mapped onto the logical layer.
* Users and programs are not dependent on the physical structure of the database.
* (Physical layer implementation is dependent on the DBMS.)

9. Logical data independence

* Users and programs are independent of the logical structure of the database.
* i.e.: the logical structure of the data can evolve with minimal impact on the programs.

10. Integrity independence

* Integrity constraints are to be stored in the catalog not the programs.
* Alterations to integrity constraints should not affect application programs.
* This simplifies the programs.
* It is not always possible to do this.

11. Distribution independence

* Applications should still work in a distributed database (DDB).

12. Nonsubversion rule

* If there is a record-at-a-time interface (eg via 3GL), security and integrity of the database must not be violated.
* There should be no backdoor to bypass the security imposed by the DBMS.

• Is access database a RDBMS?

Yes Access is RDBMS

• What are page splits?

A page is 8Kbytes of data which can be index related, data related, large object binary (lob’s) etc…

When you insert rows into a table they go on a page, into ‘slots’, your row will have a row length and you can get only so many rows on the 8Kbyte page. What happens when that row’s length increases because you entered a bigger product name in your varchar column for instance, well, SQL Server needs to move the other rows along in order to make room for your modification, if the combined new length of all the rows on the page will no longer fit on that page then SQL Server grabs a new page and moves rows to the right or left of your modification onto it – that is called a ‘page split’.

• What are E-R diagrams?

What is collation?

Case sensitivity
If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity
If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.

Kana Sensitivity

Width sensitivity
Database, Tables and columns with different collation

SQL Server 2000 allows the users to create databases, tables and columns in different collations.

• What is Extent and Page?
The fundamental unit of data storage in Microsoft SQL Server™ is the page. In SQL Server 2000, the page size is 8 KB. This means SQL Server 2000 databases have 128 pages per megabyte.

The start of each page is a 96-byte header used to store system information, such as the type of page, the amount of free space on the page, and the object ID of the object owning the page.

Types of pages in SQL Server
Global Allocation Map
Secondary Global Allocation Map
Index Allocation Map
Bulk Changed Map
Differential Changed Map

Extent is a collection of 8 pages. There are two types of extents. 1. Uniform Extents 2. Mix Extents

• What is normalization? What are different types of normalization?

There are three main normal forms, each with increasing levels of normalization:


• What is denormalization?