Database Models
To provide the required durability, data is stored in physical storage devices. These files are stored in different logical formats depending on the database model selected by every particular database management system. You can find many database models in the database market:
Flat files, hierarchical, and networked models are mainly used in mainframes, whereas the other models have been ported to client/server environments, based on personal computers. Discussing these database models is out of the scope of this book. SQL Server 2000 implements the relational model, and the following section teaches you the basic theory behind this popular database model.
The Relational Model
In the relational model, data is arranged in tables in which the physical location of every value is not permanently predefined and is transparent to the data retrieval strategy. Every table is defined with a fixed set of columns that map the entity attributes.
Data from different tables is related by logical links that are dynamically defined by the database application or by the end user who sends a data request. Figure 1.1 shows a typical example of a relational database.
Figure 1.1. The relational model arranges data in tables with logical dynamically defined links.
Users access data using an industry standard query language, called SQL. This means that the database design focuses mainly on the data to store, producing a flexible database that can be used by many different applications. This flexibility contrasts with the databases stored in a hierarchical or networked model, in which the database structure was designed to solve a specific business problem.
As you can imagine, this flexibility represents more complexity for the database engine. This is the reason you can expect better performance for the same hardware platform, using hierarchical or networked databases rather than relational databases. However, the continuous improvements on relational database management systems (RDBMS) is switching the database market to this technology, even at mainframe level, where a big percentage of available data is still in hierarchical and networked format.
The relational model is based on the relational theory, in which modifications to the data are based on relational algebra and relational calculus.
Dr. E. F. Codd, an IBM mathematician, published A Relational Model of Data for Large Shared Data Banks (Communications of the ACM, Vol. 3, No. 6, June 1970). This document establishes the rules of the relational databases. Many database vendors started to implement his theories soon after this publication. IBM DB2, Oracle, Sybase, and Microsoft SQL Server are typical RDBMS products.
The language used to request information from RDBMS, SQL, is part of the ANSI standard since the ANSI SQL 86 version. Many products are based on the ANSI SQL 89 and ANSI SQL 92 standards, but every product offers different extensions. A new standard is available now, ANSI SQL 99, which expands the traditional relational model nearer to the object-oriented model.
Microsoft SQL Server 2000 is based in a special SQL dialect, called Transact-SQL, which is an expanded version of the ANSI SQL 92 standard.