Database Solutions [With Cd-Rom] - Softcover

Connolly, Thomas; Begg, Carolyn

Softcover

ISBN 10: 0201674769 ISBN 13: 9780201674767

Publisher: Addison Wesley, 2000

Synopsis

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?

About this title

From the Back Cover:

Database Solutions: A step-by-step guide to building databases 2/e

Are you responsible for designing and creating the databases that keep your business running? Or are you studying for a module in database design? If so, Database Solutions is for you! This fully revised and updated edition will make the database design and build process smoother, quicker and more reliable.

Recipe for database success

• Take one RDMS – any of the major commercial products will do: Oracle, Informix, SQL Server, Access, Paradox
• Add one thorough reading of Database Solutions if you are an inexperienced database designer, or one recap of the methodology if you are an old hand
• Use the design and implementation frameworks to plan your timetable, use a common data model that fits your requirements and adapt as necessary

Features

• Includes hints and tips for success with comprehensive guidance on avoiding pitfalls and traps

• Shows how to create data models using the UML design notation

• Includes two full-length coded example databases written on Microsoft Access 2002 and Oracle 9i, plus 15 sample data models to adapt to your needs, chosen from seven common business areas

New for this edition!

• New chapters on SQL (Structured Query Language) and QBE (Query by Example), plus a chapter on database administration and security. A new chapter on current and emerging trends in the area ensures that the book is up to date

• The database design methodology has been improved and simplified

• A companion website contains an implementation of the StayHome database that runs throughout the book, plus SQL scripts for 15 sample data models, lecture slides, sample solutions for all exercises from the book plus suggested exam questions and answers, and a variety of possible courseworks

The authors

Thomas Connolly was a designer of RAPPORT, the world’s first commercial portable DBMS, and of the LIFESPAN configuration management tool – a winner of the British Design Award. Carolyn Begg specializes in the application of database systems in biological research. They are both authors of the best selling Database Systems, also published by Pearson Education, which has sold nearly 200,000 copies since publication in 1995.

Excerpt. © Reprinted by permission. All rights reserved.:

Chapter 1 Introduction In this chapter you will learn:

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 set of queries or where there is a proliferation of programs, giving major software management problems. We'll discuss the typical functions and services of a DBMS in the next section.

Figure 1.1 illustrates the database approach. It shows the Sales and Stock Control Departments using their application programs to access the database through the DBMS. Each set of departmental application programs handles data entry, data maintenance, and the generation of reports. The physical structure and storage of the data are managed by the DBMS. 1.2.3 Views With the functionality described above, the DBMS is an extremely powerful tool. However, as end-users are not too interested in how complex or easy a task is for the system, it could be argued that the DBMS has made things more complex because they may now see more data than they actually need or want to do their job. In recognition of this problem, a DBMS provides another facility known as a view mechanism, which allows each user to have his or her own view of the database, where a view is some subset of the database. As well as reducing Methodology covered in Chapters 7-16DBMS A software system that enables users to define, create, and maintain the database and also provides controlled access to this database.The Structured Query Language (SQL - pronounced 'S-Q-L' or sometimes 'See-Quel'), is the main query language for relational DBMSs.SQL covered in Section 2.4 and Chapter 17complexity by letting users see the data in the way they want to see it, views have several other benefits: n

Views provide a level of security. Views can be set up to exclude data that some users should not see. For example, we could create a view that allows a branch manager and the Payroll Department to see all staff data, including salary details. However, we could create a second view that excludes salary details, which other staff use. n

Views provide a mechanism to customize the appearance of the database. For example, the Stock Control Department may wish to call the Daily Rental Rate column for videos by the simpler name, Daily Rental. n

A view can present a consistent, unchanging picture of the structure of the database, even if the underlying database is changed (for example, columns added or removed, relationships changed, data files split, restructured, or renamed). If columns are added or removed from a data file, and these columns are not required by the view, the view is not affected by this change. Thus, a view helps provide additional data independence to that provided by the system catalog, as we described in Section 1.2.1. Figure 1.1 Sales and Stock Control Departments using a DBMS. 1.2.4

Components of the DBMS environment We can identify five major components in the DBMS environment: hardware, software, data, procedures, and people: (1)

Hardware The computer system(s) that the DBMS and the application programs run on. This can range from a single PC, through a single mainframe, to a network of computers. (2)

Software The DBMS software and the application programs, together with the operating system, including network software if the DBMS is being used over a network. (3)

Data The data acts as a bridge between the hardware and software components and the human components. As we've already said, the database contains both the operational data and the meta-data (the 'data about data'). (4)

Procedures The instructions and rules that govern the design and use of the database. This may include instructions on how to log on to the DBMS, make backup copies of the database, and how to handle hardware or software failures. (5)

People This includes the database designers, database administrators (DBAs), application programmers, and end-users. 1.2.5

DBMS architectures Before the advent of the Web, generally a DBMS would be divided into two parts: n

a client program that handles the main business and data processing logic and interfaces with the user; n

a server program (sometimes called the DBMS engine) that manages and controls access to the database.

This is known as a two-tier client-server architecture. Figure 1.2 illustrates a simplified client-server architecture for a video rental company called StayHome that has offices throughout America. It shows a centralized database and server located at the company's headquarters in Seattle and a number of clients, located at some of the branches around the US.

In the mid-1990s, as applications became more complex and potentially could be deployed to hundreds or thousands of end-users, the client side of this architecture gave rise to two problems: n

A 'fat' client, requiring considerable resources on the client's computer to run effectively. This includes disk space, RAM, and CPU power. n

A significant client-side administration overhead.

By 1995, a new variation of the traditional two-tier client-server model appeared to solve these problems called the three-tier client-server architecture. This new architecture proposed three layers, each potentially running on a different platform: StayHome is used throughout this book and discussed more fully in Chapter 4Figure 1.2 Simplified two-tier client-server configuration for StayHome.(1)

The user interface layer, which runs on the end-user's computer (the client). (2)

The business logic and data processing layer. This middle tier runs on a server and is often called the application server. One application server is designed to serve multiple clients. (3)

A DBMS, which stores the data required by the middle tier. This tier may run on a separate server called the database server.

The three-tier design has many advantages over the traditional two-tier design, such as: n

A 'thin' client, which requires less expensive hardware. n

Simplified application maintenance, as a result of centralizing the business logic for many end-users into a single application server. This eliminates the concerns of software distribution that are problematic in the traditional two-tier client-server model. n

Added modularity, which makes it easier to modify or replace one tier without affecting the other tiers. n

Easier load balancing, again as a result of separating the core business logic from the database functions. For example, a Transaction Processing Monitor (TPM) can be used to reduce the number of connections to the database server. (A TPM is a program that controls data transfer between clients and servers in order to provide a consistent environment for Online Transaction Processing (OLTP).)

An additional advantage is that the three-tier architecture maps quite naturally to the Web environment, with a Web browser acting as the 'thin' client, and a Web server acting as the application server. The three-tier client-server architecture is illustrated in Figure 1.3. 1.3

Functions of a DBMS In this section, we briefly look at the functions and services we would expect a full-scale DBMS to provide nowadays. Data storage, retrieval, and update This is the fundamental function of a DBMS. From our earlier discussion, clearly in providing this functionality the DBMS should hide the internal physical implementation details (such as file organization and storage structures) from the user. A user-accessible catalog A key feature of a DBMS is the provision of an integrated system catalog to hold ...

About this edition

• Publisher: Addison Wesley
• Publication date: 2000
• ISBN 10: 0201674769
• ISBN 13: 9780201674767
• Binding: Paperback
• Edition number: 1
• Number of pages: 448