Relational Database Design and Implementation

Jan L. Harrington

• Publisher: Morgan Kaufmann
• Year: 2016
• ISBN:
  9780128499023
• Edition: 4
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

Relational Database Design and Implementation: Clearly Explained, Fourth Edition, provides the conceptual and practical information necessary to develop a database design and management scheme that ensures data accuracy and user satisfaction while optimizing performance.

Database systems underlie the large majority of business information systems. Most of those in use today are based on the relational data model, a way of representing data and data relationships using only two-dimensional tables. This book covers relational database theory as well as providing a solid introduction to SQL, the international standard for the relational database data manipulation language.

The book begins by reviewing basic concepts of databases and database design, then turns to creating, populating, and retrieving data using SQL. Topics such as the relational data model, normalization, data entities, and Codd's Rules (and why they are important) are covered clearly and concisely. In addition, the book looks at the impact of big data on relational databases and the option of using NoSQL databases for that purpose.

• Features updated and expanded coverage of SQL and new material on big data, cloud computing, and object-relational databases
• Presents design approaches that ensure data accuracy and consistency and help boost performance
• Includes three case studies, each illustrating a different database design challenge
• Reviews the basic concepts of databases and database design, then turns to creating, populating, and retrieving data using SQL

Part I

Introduction

Introduction

Chapter 1: The Database Environment

Chapter 2: Systems Analysis and Database Requirements

Introduction

The first part of this book deals with the organizational environment in which databases exist. In these chapters, you will find discussions of various hardware and network architectures on which databases operate and an introduction to database management software. You will also learn about alternative processes for discovering exactly what a database needs to do for an organization.

Chapter 1

The Database Environment

Abstract

This chapter introduces the concept of database systems. Topics include the types of information systems that use databases, the ownership of data within the organization, database software, network architectures for database systems, the importance of security and privacy issues, and integration with legacy databases. The chapter also includes a list of open source database management systems (DBMSs).

Keywords

databases

database management systems

centralized databases

distributed databases

client/server databases

cloud storage

Can you think of a business that doesn’t have a database that is stored on a computer? It’s hard. I do know of one, however. It is a small used paperback bookstore. A customer brings in used paperbacks and receives credit for them based on the condition, and in some cases, the subject matter, of the books. That credit can be applied to purchasing books from the store at approximately twice what the store pays to acquire the books. The books are shelved by general type (for example, mystery, romance, and non-fiction), but otherwise not organized. The store doesn’t have a precise inventory of what is on its shelves.

To keep track of customer credits, the store has a 4 × 6 card for each customer on which employees write a date and an amount of credit. The credit amount is incremented or decremented based on a customer’s transactions. The cards themselves are stored in two long steel drawers that sit on a counter. (The cabinet from which the drawers were taken is nowhere in evidence.) Sales slips are written by hand and cash is kept in a drawer. (Credit card transactions are processed by a stand-alone terminal that uses a phone line to dial up the processing bank for card approval.) The business is small, and its system seems to work, but it certainly is an exception.

Although the bookstore just described doesn’t have a computer or a database, it does have data. In fact, like a majority of businesses today, it relies on data as the foundation of what it does. The bookstore’s operations require the customer credit data; it couldn’t function without it.

Data form the basis of just about everything an organization that deals with money does. (It is possible to operate a business using bartering and not keep any data, but that certainly is a rarity.) Even a Girl Scout troop selling cookies must store and manipulate data. The troop needs to keep track of how many boxes of each type of cookie have been ordered, and by whom. They also need to manage data about money: payments received, payments owed, amount kept by the troop, amount sent to the national organization. The data may be kept on paper, but they still exist and manipulation of those data is central to the group’s functioning.

In fact, just about the only “business” that doesn’t deal with data is a lemonade stand that gets its supplies from Mom’s kitchen and never has to pay Mom back. The kids take the entire gross income of the lemonade stand without worrying about how much is profit.

Data have always been part of businesses.¹ Until the mid-twentieth century, those data were processed manually. Because they were stored on paper, retrieving data was difficult, especially if the volume of data was large. In addition, paper documents tended to deteriorate with age, go up in smoke, or become water-logged. Computers changed that picture significantly, making it possible to store data in much less space than before, to retrieve data more easily, and usually to store it more permanently.

The downside to the change to automated data storage and retrieval was the need for at some specialized knowledge on the part of those who set up the computer systems. In addition, it costs more to purchase the equipment needed for electronic data manipulation than it does to purchase some file folders and file cabinets. Nonetheless, the ease of data access and manipulation that computing has brought to businesses has outweighed most other considerations.

Defining a Database

Nearly 35 years ago, when I first started working with databases, I would begin a college course I was teaching in database management with the following sentence: “There is no term more misunderstood and misused in all of business computing than ‘database.’ ” Unfortunately, that is still true to some extent, and we can still lay much of the blame on commercial software developers. In this section, we will explore why that is so, and provide a complete definition for a database.

Lists and Files

A portion of the data used in a business is represented by lists of things. For example, most of us have a contact list that contains names, addresses, and phone numbers. Business people also commonly work with planners that list appointments. In our daily lives, we have shopping lists of all kinds as well as “to do” lists. For many years, we handled these lists manually, using paper, day planners, and a pen. It made sense to many people to migrate these lists from paper to their PCs.

Software that helps us maintain simple lists stores those lists in files, generally one list per physical file. The software that manages the list typically lets you create a form for data entry, provides a method of querying the data based on logical criteria, and lets you design output formats. List management software can be found not only on desktop and laptop computers, but also on our handheld computing devices.

Unfortunately, list management software has been marketed under the name “database” since the advent of PCs. People have therefore come to think of anything that stores and manipulates data as database software. Nonetheless, a list handled by list-management software is not a database.

Databases

There is a fundamental concept behind all databases: There are things in a business environment about which we need to store data, and those things are related to one another in a variety of ways. In fact, to be considered a database, the place where data are stored must contain not only the data but also information about the relationships between those data. We might, for example, need to relate our customers to the orders they place with us and our inventory items to orders for those items.

The idea behind a database is that the user—either a person working interactively or an application program—has no need to worry about the way in which data are physically stored on disk. The user phrases data manipulation requests in terms of data relationships. A piece of software known as a database managem...