Database Solutions : A Step-by-Step Guide to Building Databases

Do you carry the world on your shoulders? Does every request for a new business system from senior management land on your desk? Are you responsible for designing and creating the databases that keep your business running? Would you like to make the design and build process smoother, quicker and more reliable?

Some common uses of database systems.

The meaning of the term database.

The meaning of the term Database Management System (DBMS).

The major components of the DBMS environment.

The typical functions and services a DBMS should provide.

The advantages and disadvantages of DBMSs.

The database is now such an integral part of our day-to-day life that often we're not aware we are using one. To start our discussion of databases, we briefly examine some applications of database systems. For the purposes of this discussion, we consider a database to be a collection of related data and the Database Management System (DBMS) to be the software that manages and controls access to the database. We also use the more inclusive term database system to be a collection of application programs that interact with the database. We provide more accurate definitions in Section 1.2. Later in the chapter, we'll look at the typical functions of a modern DBMS and briefly review the main advantages and disadvantages of DBMSs. 1.1

Examples of the use of database systems Purchases from the supermarket When you purchase goods from your local supermarket, it's likely that a database is accessed. The checkout assistant uses a bar code reader to scan each of your #purchases. This is linked to an application program that uses the bar code to find the price of the item from a product database. The program then reduces the number of such items in stock and displays the price on the cash register. If the reorder level falls below a specified threshold, the database system may automatically place an order to obtain more stocks of that item. Purchases using your credit card When you purchase goods using your credit card, the assistant normally checks that you have sufficient credit left to make the purchase. This check may be carried out by telephone or it may be done automatically by a card reader linked to a computer system. In either case, there is a database somewhere that contains information about the purchases that you've made using your credit card. To check your credit, there is a database application program that uses your credit card number to check that the price of the goods you wish to buy, together with the sum of the purchases you've already made this month, is within your credit limit. When the purchase is confirmed, the details of your purchase are added to this database. The application program also accesses the database to check that the credit card is not on the list of stolen or lost cards before authorizing the purchase. There are other database application programs to send out monthly statements to each cardholder and to credit accounts when payment is received. Booking a holiday at the travel agents When you make inquiries about a holiday, the travel agent may access several databases containing holiday and flight details. When you book your holiday, the database system has to make all the necessary booking arrangements. In this case, the system has to ensure that two different agents don't book the same holiday or overbook the seats on the flight. For example, if there is only one seat left on the flight from London to New York and two agents try to reserve the last seat at the same time, the system has to recognize this situation, allow one booking to proceed, and inform the other agent that there are now no seats available. The travel agent may have another, usually separate, database for invoicing. Using the local library Whenever you visit your local library, there is probably a database containing details of the books in the library, details of the readers, reservations, and so on. There will be a computerized index that allows readers to find a book based on its title, or its authors, or its subject area. The database system handles reservations to allow a reader to reserve a book and to be informed by mail when the book is available. The system also sends out reminders to borrowers who have failed to return books on the due date. Typically, the system will have a bar code reader, similar to that used by the supermarket described earlier, which is used to keep track of books coming into and going out of the library.

These are only a few of the applications for database systems, and you'll no doubt be aware of plenty of others. Although we take many of these applications for granted, behind them lies some highly complex technology. At the center of this technology is the database itself. For the system to support the applications that the end-users want, in as efficient a manner as possible, requires a suitably structured database. Producing this structure is known as database design, and it's this important activity that we're going to concentrate on in this book. Whether the database you wish to build is small, or large like the ones above, database design isw a fundamental issue, and the methodology presented in this book will help you build your database correctly with relative ease. Having a well-designed database will allow you to produce a system that satisfies the requirements of the users and, at the same time, provides acceptable performance. 1.2

Database approach In this section, we provide a more formal definition of the terms database and Database Management System (DBMS) than we used in the last section. 1.2.1 The database Let's examine the definition of a database in detail to understand this concept fully. The database is a single, large repository of data, which can be used simultaneously by many departments and users. All data that is required by these users is integrated with a minimum amount of duplication. And importantly, the database is normally not owned by any one department or user but is a shared corporate resource.

As well as holding the company's operational data, the database also holds a description of this data. For this reason, a database is also defined as a self-describing collection of integrated records. The description of the data, that is the meta-data - the 'data about data', is known as the system catalog or data dictionary. It is the self-describing nature of a database that provides what's known as data independence. This means that if new data structures are added to the database or existing structures in the database are modified then the application programs that use the database are unaffected, provided they don't directly depend upon what has been modified. For example, if we add a new column to a record or create a new table, existing applications are unaffected. However, if we remove a column from a table that an application program uses, Database A shared collection of logically related data (and a description of this data), designed to meet the information needs of a company.System catalog Holds data about all objects in the database.then that application program is affected by this change and must be modified accordingly.

The final term in the definition of a database that we should explain is 'logically related'. When we analyze the company's information needs, we attempt to identify the important objects that need to be represented in the database and the logical relationships between these objects. The methodology we'll present for database design will give you guidelines for identifying these important objects and their logical relationships. 1.2.2 The Database Management System (DBMS) The DBMS is the software that interacts with the users, application programs, and the database. Among other things, the DBMS allows users to insert, update, delete, and retrieve data from the database. Having a central repository for all data and data descriptions allows the DBMS to provide a general inquiry facility to this data, called a query language. The provision of a query language (such as SQL) alleviates the problems with earlier systems where the user has to work with a fixed...