For library Database Management System

For library Database Management System

You want to set up a database for the books in a library. Purely for the sake of illustration, suppose the library contains 14 books. The same discussion would apply to a library of perhaps a few hundred books. Table 1-1 shows the LIBRARY_FLAT database in the form of a single table.

Table 1-1. The LIBRARY_FLATsample database

Columns labeled AuID and PubID are included for identitification purposes, i.e., to identify an author or a publisher uniquely. In any case, their presence or absence will not affect the current discussion.

Here is one possibility for the LIBRARY_FLAT example, which splits the original database into four separate tables.

•  A BOOKS table, shown in Table 1-2, in which each book has its own record
•  An AUTHORS table, shown in Table 1-3, in which each author has his own record
• A PUBLISHERS table, shown in Table 1-4, in which each publisher has its own record
• BOOK/AUTHOR table, shown in Table 1-5, the purpose of which we will explain a bit later

Table 1-2. The BOOKS table from the LIBRARY_FLAT database

Table 1-3. The AUTHORS table from the LIBRARY_FLAT database

Table 1-4. The PUBLISHERS table from the LIBRARY_FLAT database

Table 1-5. The BOOK/AUTHOR table from the LIBRARY_FLAT database

The attributes of the entity classes in the LIBRARY database are:

Books attributes	Authors attributes	Publishers attributes
Title ISBN Price	AuName AuPhone AuID	PubName PubPhone PubID

The LIBRARY Relational Database

We can now complete the implementation of the LIBRARY relational database (without

the Contributors entity class) in Microsoft Access. If you open the LIBRARY database in

Microsoft Access, you will see four tables:

• AUTHORS
• BOOK/AUTHOR
• BOOKS
• PUBLISHERS

               (The LIBRARY_FLAT table is not used in the relational database.)

These four tables correspond to the following four entity classes (or table schemes):

• Authors (AuID, AuName, AuPhone)
• Book/Author (ISBN, AuID)
• Books (ISBN, Title, PubID, Price)
• Publishers (PubID, PubName, PubPhone)

The actual tables are shown in Tables Table 3-3 through Table 3-6.

Table 3-3. The AUTHORS table from the Access LIBRARY database

Table 3-4. The BOOK/AUTHOR table from the LIBRARY database

Table 3-5. The BOOKS table from the LIBRARY database

Table 3-6. The PUBLISHERS Table from the LIBRARY Database

Figure 3-7. Relationships view showing various table relationships

Database System Architecture

Why Program?

There is no doubt that SQL is a powerful language—as far as it goes. However, it is a somewhat unfriendly language, and it lacks the sophisticated control structures of a more traditional language, such as For...Next... loops and If...Then... statements.

This is not really a problem, since SQL is designed for a very specific purpose related to database-component creation and manipulation. SQL is not designed to provide an overall programming environment for Microsoft Access itself. This role is played by Visual Basic for Applications (VBA).

VBA is the macro or scripting language for all of the major Microsoft Office products:

Microsoft Access, Excel, PowerPoint, and Word. It is a very powerful programming language that gives the programmer access to the full features of these applications, as well as the means to make the applications work together.

One of the major components of VBA is its support for Data Access Objects model, (DAO). DAO is the programming-language interface for the Jet database management system (DBMS) that underlies Microsoft Access. It provides a more-or-less object-oriented data definition language (DDL) and data manipulation language (DML), thereby allowing the VBA programmer to define the structure of a database and manipulate its data.

Of course, it is natural to wonder why you would want to use DAO, and VBA in general, rather than using the built-in graphical interface of Microsoft Access. The answer is simple. While the graphical interface is very easy to use and is quite adequate for many purposes, it is simply not as powerful as the programming languages. The database creator gains more power and flexibility over the database by directly manipulating the basic objects of the database (such as the tables, queries, relationships, indexes, and so on) through programming.

As a simple example, there is no way to get a list of the fields of a given table (i.e, the table's table scheme) using the Access graphical interface. However, this is a simple matter using programming techniques.

The following short program:

Sub Example()

Dim db As DATABASE

Dim tdf As TableDef

Dim fld As Field

Set db = CurrentDb

Set tdf = db.TableDefs("BOOKS")

For Each fld In tdf.Fields

Debug.Print fld.Name

Next

End Sub

displays the following list of fields for the BOOKS table in the Debug window:

ISBN

Title

PubID

Price

This is a good place to discuss the relationship between DAO and SQL. The fact is that DAO both uses SQL and overlaps SQL. That is, there are many commands in DAO that can accept an SQL statement as an argument. For instance, the following VBA code opens a recordset (discussed later in the book) using an SQL statement to define the records in the recordset:

' Get current database

Set dbs = CurrentDb()

' Write SQL statement

strSelect = "Select * FROM Books WHERE Price=10"

' Open recordset using SQL statement

Set rsCheap = dbs.OpenRecordset(strSelect)

On the other hand, DAO overlaps SQL in the sense that many actions can be performed using either language. For instance, a table can be created using either the SQL statement CREATE TABLE or the DAO method CreateTable. The choice is up to the programmer. Our main goal in the remaining portion of this book is to discuss the DAO model. Before doing so, however, we need to set the stage by discussing the overall architecture of a database management system, and of the Jet DBMS in particular, so we can put DAO in its proper context. We will do so in this chapter and also take a quick peek at DAO programming.

Database Systems

A database system is often pictured as a three-level structure, as shown in Figure 7-1.

Figure 7-1. The three-level structure of a database system

At the lowest level of the structure is the physical database, which consists of the raw data existing on a physical object, such as a hard disk. At this level, the data has no logical meaning, as related to the database. However, the data does have a very definite physical structure to allow efficient access. In other words, the data is more than just a string of bits.

In fact, there are a variety of structures in which the data might be stored, including hash tables, balanced trees, linked lists, nested records, and so on, and the choice of data structure is not a simple one. However, I will not pursue a discussion of the physical database in this book. Suffice it to say that, at the physical level, the data is viewed as a structured collection of bits, and the sole purpose of the structure is to provide efficient access to the data. The physical level of a database is often referred to as the internal level.

The conceptual database is a conceptual view of the database as a whole. It gives the data a logical structure. For instance, in a relational database system, the data is viewed as a collection of tables, with column headings describing the attributes of the corresponding entity class. Moreover, tables are related to one another through certain columns.

The conceptual model is intended to model the entire database. However, individual users may be interested in views of only specific portions of the data. For instance, in the LIBRARY database, a student using the library's online database catalog is probably not interested in the price of the book, but is interested in where it is located on the shelves. Thus, a single database, such as LIBRARY, may need different views for the student than

for the librarian.

The highest level in the three-tier structure consists of the individual views of the data that may be held by users of the database. Views are also referred to as subschemes, and this level of the tier is also referred to as the external level.

As another example, we can think of the Microsoft Visual Basic programming language as providing an external view of the Jet database management system that is geared toward database programmers. We can think of Microsoft Access as providing an external view that is geared, not just to programmers, but also to high-level users of varying degrees of sophistication. After all, a user does not need to know anything about database programming to create a database in Microsoft Access, although he does need to have a familiarity with the conceptual level of a relational database.

Thinking of a database system as a three-tier structure has distinct advantages. One advantage is that it allows for a certain level of independence that permits the individual tiers to be changed or replaced without affecting the other tiers. For instance, if the database is moved to a new computer system that stores the data in hash tables rather than

balanced trees, this should not affect the conceptual model of the data, nor the views of users of the database. Also, if we switch from the Visual Basic view of the database to the Access view, we can still use the same conceptual database model. Put more bluntly, a database table in Visual Basic is still a database table in Microsoft Access.

Database Management Systems

A DBMS is a software system that is responsible for managing all aspects of a database, at all levels. In particular, a DBMS should provide the following features, and perhaps more:

• A mechanism for defining the structure of a database, in the form of a data definition language, or DDL.

• A mechanism for data manipulation, including data access, sorting, searching, and filtering. This takes the form of a data manipulation language, or DML.

• Interaction with a high-level host language or host application, allowing programmers to write database applications designed for specific purposes. The host language can be a standard programming language, such as C or Visual Basic, or a database application language, such as Microsoft Access.

• Efficient and correct multiuser access to the data.

• Effective data security.

• Robustness—that is, the ability to recover from system failures without data loss.

• A data dictionary, or data catalog. This is a database (in its own right) that provides a list of the definitions of all objects in the main database. For instance, it should include information on all entities in the database, along with their attributes and indexes. This "data about data" is sometimes referred to as metadata. The data dictionary should be accessible to the user of the database, so that she can obtain this metadata.

The Jet (?) DBMS

As the title of the book suggests, our primary interest is in the DBMS that underlies Microsoft Access (and also Visual Basic). Accordingly, we will take our examples from this DBMS, called the Jet DBMS or the Jet Database Engine. The relationship between the Jet DBMS and other database-related programs, including Microsoft Access and Visual Basic, can be pictured as in Figure 7-2.

Figure 7-2. The relationships and structure of the Jet Database Engine

(DBMS)

Microsoft's application-level products Visual Basic, Access, and Excel play host to VBA, which is the underlying programming language (also called scripting or macro language) for these applications. (Microsoft Word Version 7 does not use VBA—it uses a similar language called Word Basic. However, as of Microsoft Word 97, Word does use VBA.) As expected, each of these applications integrates VBA into its environment in a specific way, since each application has a different purpose.

In turn, Visual Basic for Applications is the host language for the Jet DBMS. The Jet DBMS contains the DAO component, which is the programming-language interface for the Jet DBMS. The DAO provides a more-or-less object-oriented DDL and DML, thereby allowing the VBA programmer to define the structure of a database and manipulate its data.

The Jet Database Engine is a collection of components, generally in the form of dynamic link libraries ( DLLs), designed to provide specific functions within the Jet DBMS. (A DLL is essentially a collection of functions for performing various tasks.) The Jet Query Engine handles the translation of database queries into Access SQL, and the subsequent compilation, optimization, and execution of these queries. In short, it handles queries. The Internal ISAM component is responsible for storing and retrieving data from the physical database file. ISAM stands for Indexed Sequential Access Method and is the method by which data is stored in a Jet database file. The Replication Engine allows exact duplicates of a database to coexist on multiple systems, with periodic synchronization.

The host languages for the Jet DBMS, such as Visual Basic and Access, are used by database programmers to create database applications for specific purposes. For instance, we might create a Library database application, which a library can use to maintain information about its books, or an Order Entry database application for a small business. Incidentally, the Jet DBMS is also capable of interfacing with non-Access-formatted databases, such as those with format Xbase (dBase), Paradox, Btrieve, Excel, and delimited text formats. It can also interface with open database connectivity  to access server database applications across networks.

Let us take a closer look at the components of the Jet DBMS. We will study these components in much greater detail in separate chapters of the book.

Data Definition Languages

We have already mentioned that a DBMS needs to provide a method for defining new databases. This is done by providing a data definition language (DDL) to the  programmer.

A DDL is not a procedural language; that is, its instructions do not actually perform operations. Rather, a DDL is a definitional language.

The Jet Data Definition Language

Example 7-1 illustrates the use of the Jet data definition language. The code will run in Visual Basic or in an Access code module, so feel free to key it in and try it yourself.

(Use a new database in Access, since some of this code will conflict with the LIBRARY database that we have been working with in earlier chapters.) The purpose is to create a new database called LIBRARY, along with a table called BOOKS, containing two fields, ISBN and TITLE, and one index. (Don't worry if some portions of this code don't make sense to you at this point.) Note that Access uses a space followed by an underscore character ( _ ) to indicate that the next line is a continuation of the current line.

Example 7-1. Use of the Jet data definition language

' Data Definition Language example

' Declare variables of the required types

Dim ws As Workspace

Dim dbLibrary As Database

Dim tblBooks As TableDef

Dim fldBooks As Field

Dim idxBooks As Index

' Use the default workspace, called Workspaces(0)

Set ws = DBEngine.Workspaces(0)

' Create a new database named LIBRARY

' in the default Workspace

Set dbLibrary = _

ws.CreateDatabase"d:\dao\library.mdb", _

dbLangGeneral)

' Create a new table called BOOKS

Set tblBooks = dbLibrary.CreateTableDef("BOOKS")

' Define ISBN field and append to the

' table's Fields collection

Set fldBooks = tblBooks.CreateField("ISBN", dbText)

fldBooks.Size = 13

tblBooks.Fields.Append fldBooks

' Define Title field and append to the

' table's Fields collection

Set fldBooks = tblBooks.CreateField("Title", dbText)

fldBooks.Size = 100

tblBooks.Fields.Append fldBooks

' Add the table to the db's Tables collection

dbLibrary.TableDefs.Append tblBooks

' Create an index

Set idxBooks = tblBooks.CreateIndex("ISBNIdx")

idxBooks.Unique = False

' Indices need their own fields

Set fldBooks = idxBooks.CreateField("ISBN")

' Append to the proper collections

idxBooks.Fields.Append fldBooks

tblBooks.Indexes.Append idxBooks

As you can see, the clue that we are dealing with a DDL are the commands CreateDatabase, CreateTableDef, CreateField, and CreateIndex (in boldface for easier identification). You can also see from this code that the Jet DBMS uses the collections to hold the properties of an object. For instance, the fields that we create for a table must be appended to the Fields collection for that table. This has the advantage that we don't need to keep a separate reference to each field—the collection does that for us. This approach is typical of object-oriented programming.

Data Manipulation Languages

A DBMS must also provide a language designed to manipulate the data in a database. This language is called a database manipulation language, or DML. To the database programmer, however, the distinction between a DDL and a DML may be just a logical one, defined more by the purpose of the language than the syntax.

The Jet Data Manipulation Language

Example 7-2 is Jet DML code to add two records to the BOOKS table, set the index, and display the records.

Example 7-2. Jet DML code altering the BOOKS table

' Data Manipulation Language example

Dim rsBooks As Recordset

' Open the database

Set dbLibrary = DBEngine.OpenDatabase("d:\dao\library.mdb")

' Create a recordset for the BOOKS table

Set rsBooks = dbLibrary.OpenRecordset("BOOKS")

' Add two records

rsBooks.AddNew

rsBooks!ISBN = "0-99-345678-0"

rsBooks!Title = "DB Programming is Fun"

rsBooks.Update

rsBooks.AddNew

rsBooks!ISBN = "0-78-654321-0"

rsBooks!Title = "DB Programming isn't Fun"

rsBooks.Update

' Set index

rsBooks.Index = "ISBNIdx"

' Show the records

rsBooks.MoveFirst

MsgBox "ISBN: " & rsBooks!ISBN & " TI: " & rsBooks!Title

rsBooks.MoveNext

MsgBox "ISBN: " & rsBooks!ISBN & " TI: " & rsBooks!Title

As you can see even from this small example, the DML is designed to perform a variety

of actions, such as:

• Moving through the data in the database

• Adding data to the database

• Editing or updating data in the database

• Deleting data from the database

• Querying the data and returning those portions of the data that satisfy the query

Host Languages

Data is seldom manipulated without some intended purpose. For instance, consider a LIBRARY database consisting of information about the books in a library. If a student wishes to access this data, it is probably with the intention of finding a certain book, for which the student has some information, such as the title. On the other hand, if a librarian wishes to access the information, it may be for other purposes, such as determining when the book was added to the library or how much it cost. These issues probably don't interest the student.

The point here is that a DBMS should supply an interface with a high-level language with which programmers can program the database to provide specific services—that is, with which programmers can create database applications. Thus, when a student logs onto a library's computer to search for a book, he may be accessing a different database application than the librarian might access. The language that is used for database application programming is the hostlanguage for the DBMS. As mentioned earlier, a host language may be a traditional programming language, such as C or COBOL, or it may be an application-level language, such as Microsoft Access or Visual Basic, as it is for the Jet DBMS.

In fact, the Jet DBMS is so tightly integrated into both of these applications that it is hard to tell where one leaves off and the other begins. Put another way, it sometimes seems as though Microsoft Access is the Jet DBMS, whereas it is more accurate to say that Access and Visual Basic are front ends, or host applications, for the Jet DBMS.

The Client/Server Architecture

The client/server model of a database system is really very simple, but its meaning has evolved somewhat through popular usage. The client/server model is shown in Figure 7-3.
Figure 7-3. The client/server mode example

The server in a client/server model is simply the DBMS, whereas the client is the database application serviced by the DBMS. (We could also think of Visual Basic and Access as clients of the Jet DBMS server.)

The basic client/server model says nothing about the location of the various components. However, since the components are distinct, it is common to find them on different computers. The two most common configurations are illustrated in Figures Figure 7-4 and Figure 7-5. The distributed client/server model (Figure 7-4), wherein the client is on one computer and the server and database are on another, is so popular that it is usually simply referred to as the client/server model. The remote database model (Figure 7-5) refers to the case in which the client and server are on the same computer, but the database is on a remote computer.

Figure 7-4. The distributed client/server model example

Figure 7-5. The remote database example