Welcome to database development using SQL, the industry-standard database query language. Many database management system (DBMS) tools run on a variety of hardware platforms. The differences among the tools can be great, but all serious products have one thing in common: They support SQL data access and manipulation. If you know SQL, you can build relational databases and get useful information out of them.

About This Book

Relational database management systems are vital to many organizations. People often think that creating and maintaining these systems must be extremely complex activities — the domain of database gurus who possess enlightenment beyond that of mere mortals. This book sweeps away the database mystique. In this book, you

• Get to the roots of databases.
• Find out how a DBMS is structured.
• Discover the major functional components of SQL.
• Build a database.
• Protect a database from harm.
• Operate on database data.
• Determine how to get the information you want out of a database.

The purpose of this book is to help you build relational databases and get valuable information out of them by using SQL. SQL is the international standard language used to create and maintain relational databases. This edition covers the latest version of the standard, SQL:2016.

This book doesn’t tell you how to design a database (I do that in Database Development For Dummies, also published by Wiley). Here I assume that you or somebody else has already created a valid design. I then illustrate how you implement that design by using SQL. If you suspect that you don’t have a good database design, then by all means fix your design before you try to build the database. The earlier you detect and correct problems in a development project, the cheaper the corrections will be.

Foolish Assumptions

If you need to store or retrieve data from a DBMS, you can do a much better job with a working knowledge of SQL. You don’t need to be a programmer to use SQL, and you don’t need to know programming languages, such as Java, C, or BASIC. SQL’s syntax is like that of English. If you are a programmer, you can incorporate SQL into your programs. SQL adds powerful data manipulation and retrieval capabilities to conventional languages. This book tells you what you need to know to use SQL’s rich assortment of tools and features inside your programs.

Icons Used in This Book

When something in this book is particularly valuable, we go out of our way to make sure that it stands out. We use these cool icons to mark text that (for one reason or another) really needs your attention. Here’s a quick preview of the ones waiting for you in this book and what they mean.

Tips save you a lot of time and keep you out of trouble.

Pay attention to the information marked by this icon — you may need it later.

Heeding the advice that this icon points to can save you from major grief. Ignore it at your peril.

This icon alerts you to the presence of technical details that are interesting but not absolutely essential to understanding the topic being discussed.

Where to Go from Here

Now for the fun part! Databases are the best tools ever invented for keeping track of the things you care about. After you understand databases and can use SQL to make them do your bidding, you wield tremendous power. Co-workers come to you when they need critical information. Managers seek your advice. Youngsters ask for your autograph. But most importantly, you know, at a very deep level, how your organization really works.

IN THIS PART…

The essentials of relational databases

Basic SQL concepts

Fundamental database tools

Keeping Track of Things

Today people use computers to perform many tasks formerly done with other tools. Computers have replaced typewriters for creating and modifying documents. They’ve surpassed calculators as the best way to do math. They’ve also replaced millions of pieces of paper, file folders, and file cabinets as the principal storage medium for important information. Compared with those old tools, of course, computers do much more, much faster — and with greater accuracy. These increased benefits do come at a cost, however: Computer users no longer have direct physical access to their data.

When computers occasionally fail, office workers may wonder whether computerization really improved anything at all. In the old days, a manila file folder “crashed” only if you dropped it — then you merely knelt down, picked up the papers, and put them back in the folder. Barring earthquakes or other major disasters, file cabinets never “went down,” and they never gave you an error message. A hard-drive crash is another matter entirely: You can’t “pick up” lost bits and bytes. Mechanical, electrical, and human failures can make your data go away into the Great Beyond, never to return. Backing up your data frequently is one thing you can do to enhance your peace of mind. Another thing you can do is store your data in the cloud and let your cloud provider do the backing up.

Taking the necessary precautions to protect yourself from accidental data loss allows you to start cashing in on the greater speed and accuracy that computers provide.

If you’re storing important data, you have four main concerns:

• Storing data must be quick and easy because you’re likely to do it often.
• The storage medium must be reliable. You don’t want to come back later and find some (or all) of your data missing.
• Data retrieval must be quick and easy, regardless of how many items you store.
• You need an easy way to separate the exact information you want now from the tons of data that you don’t want right now.

State-of-the-art computer databases satisfy these four criteria. If you store more than a dozen or so data items, you probably want to store those items in a database.

What Is a Database?

The term database has fallen into loose use lately, losing much of its original meaning. To some people, a database is any collection of data items (phone books, laundry lists, parchment scrolls … whatever). Other people define the term more strictly.

In this book, I define a database as a self-describing collection of integrated records. And yes, that does imply computer technology, complete with programming languages such as SQL.

A record is a representation of some physical or conceptual object. Say, for example, that you want to keep track of a business’s customers. You assign a record for each customer. Each record has multiple attributes, such as name, address, and telephone number. Individual names, addresses, and so on are the data.

A database consists of both data and metadata. Metadata is the data that describes the data’s structure within a database. If you know how your data is arranged, then you can retrieve it. Because the database contains a description of its own structure, it’s self-describing. The database is integrated because it includes not only data items but also the relationships among data items.

The database stores metadata in an area called the data dictionary, which describes the tables, columns, indexes, constraints, and other items that make up the database.

Because a flat-file system (described later in this chapter) has no metadata, applications written to work with flat files must contain the equivalent of the metadata as part of the application program.

What Is a Database Management System?

Glad you asked. A database management system (DBMS) is a set of programs used to define, administer, and process databases and their associated applications. The database being managed is, in essence, a structure that you build to hold valuable data. A DBMS is the tool you use to build that structure and operate on the data contained within the database.

You can find many DBMS programs on the market today. Some run on large and powerful machines, and some on personal computers, notebooks, and tablets. Some even run on smartphones. A strong trend, however, is for such products to work on multiple platforms or on networks that contain different classes of machines. An even stronger trend is to store data in data centers or even to store it out in the cloud, which could be a public cloud run by a large company such as Amazon, Google, or Microsoft, via the Internet, or it could be a private cloud operated by the same organization that is storing the data on its own intranet.

These days, cloud is a buzzword that is bandied about incessantly in techie circles. Like the puffy white things up in the sky, it has indistinct edges and seems to float somewhere out there. In reality, it is a collection of computing resources that is accessible via a browser, either over the Internet or on a private intranet. The thing that distinguishes the computing resources in the cloud from similar computing resources in a physical data center is the fact that the resources are accessible via a browser rather than an application program that directly accesses those resources.

A DBMS that runs on platforms of multiple classes, large and small, is called scalable.

Whatever the size of the computer that hosts the database — and regardless of whether the machine is connected to a network — the flow of information between database and user is always the same. Figure 1-1 shows that the user communicates with the database through the DBMS. The DBMS masks the physical details of the database storage so that the application need only concern itself with the logical characteristics of the data, not with how the data is stored.

Flat Files

Where structured data is concerned, the flat file is as simple as it gets. No, a flat file isn’t a folder that’s been squashed under a stack of books. Flat files are so called because they have minimal structure. If they were buildings, they’d barely stick up from the ground. A flat file is simply a collection of data records, one after another, in a specified format — the data, the whole data, and nothing but the data — in effect, a list. In computer terms, a flat file is simple. Because the file doesn’t store structural information (metadata), its overhead (stuff in the file that is not data but takes up storage space) is minimal.

Say that you want to keep track of the names and addresses of your company’s customers in a flat file system. The system may have a structure something like this:

Database Models

The first databases, back at the dawn of time (1950s), were structured according to a hierarchical model. They suffered from redundancy problems, and their structural inflexibility made database modification difficult. They were soon followed by databases that adhered to the network model, which strove to eliminate the main disadvantages of the hierarchical model. Network databases have minimal redundancy but pay for that advantage with structural complexity.

Some years later, Dr. E. F. Codd at IBM developed the relational model, which featured minimal redundancy and an easily understood structure. The SQL language was developed to operate on relational databases. Relational databases eventually consigned the hierarchical and network databases to the dustbin of history.

A relatively new phenomenon is the emergence of the so-called NoSQL databases, which lack the structure of the relational databases and do not use the SQL language. I don’t cover NoSQL databases in this book. If this topic interests you, check out NoSQL For Dummies, by Adam Fowler (Wiley Publishing, Inc.).

Dealing with your relations

At holiday time, many of my relatives come to my house and sit down at my table. Databases have relations, too, but each of their relations has its own table. A relational database is made up of one or more relations.

A relation is a two-dimensional array of rows and columns, containing single-valued entries and no duplicate rows. Each cell in the array can have only one value, and no two rows may be identical. If that’s a little hard to picture, here’s an example that will put you in the right ballpark… .

Most people are familiar with two-dimensional arrays of rows and columns, in the form of electronic spreadsheets such as Microsoft Excel. A major-league baseball player’s offensive statistics, as listed on the back of a baseball card, are an example of such an array. On the baseball card are columns for year, team, games played, at-bats, hits, runs scored, runs batted in, doubles, triples, home runs, bases on balls, steals, and batting average. A row covers each year that the player has played in the Major Leagues. You can also store this data in a relation (a table), which has the same basic structure. Figure 1-2 shows a relational database table holding the offensive statistics for a single major-league player. In practice, such a table would hold the statistics for an entire team — or perhaps the whole league.

Columns in the array are self-consistent: A column has the same meaning in every row. If a column contains a player’s last name in one row, the column must contain a player’s last name in all rows. The order in which the rows and columns appear in the array has no significance. As far as the DBMS is concerned, it doesn’t matter which column is first, which is next, and which is last. The same is true of rows. The DBMS processes the table the same way regardless of the organization.

Every column in a database table embodies a single attribute of the table, just like that baseball card. The column’s meaning is the same for every row of the table. A table may, for example, contain the names, addresses, and telephone numbers of all an organization’s customers. Each row in the table (also called a record, or a tuple) holds the data for a single customer. Each column holds a single attribute — such as customer number, customer name, customer street, customer city, customer state, customer postal code, or customer telephone number. Figure 1-3 shows some of the rows and columns of such a table.

The relations in this database model correspond to tables in any database based on the model. Try to say that ten times fast.

Enjoy the view

One of my favorite views is of the Yosemite Valley from the mouth of the Wawona Tunnel, late on a spring afternoon. Golden light bathes the sheer face of El Capitan, Half Dome glistens in the distance, and Bridal Veil Falls forms a silver cascade of sparkling water, while wispy clouds weave a tapestry across the sky. Databases have views as well — even if they’re not quite that picturesque. The beauty of database views is their sheer usefulness when you’re working with your data.

Tables can contain many columns and rows. Sometimes all that data interests you, and sometimes it doesn’t. Only some columns of a table may interest you, or perhaps you want to see only rows that satisfy a certain condition. Some columns of one table and some other columns of a related table may interest you. To eliminate data that isn’t relevant to your current needs, you can create a view — a subset of a database that an application can process. It may contain parts of one or more tables.

Views are sometimes called virtual tables. To the application or the user, views behave the same as tables. Views, however, have no independent existence. Views allow you to look at data, but views are not part of the data.

Say, for example, that you’re working with a database that has a CUSTOMER table and an INVOICE table. The CUSTOMER table has the columns CustomerID, FirstName, LastName, Street, City, State, Zipcode, and Phone. The INVOICE table has the columns InvoiceNumber, CustomerID, Date, TotalSale, TotalRemitted, and FormOfPayment.

A national sales manager wants to look at a screen that contains only the customer's first name, last name, and telephone number. Creating from the CUSTOMER table a view that contains only the FirstName, LastName, and Phone columns enables the manager to view what he or she needs without having to see all the unwanted data in the other columns. Figure 1-4 shows the derivation of the national sales manager's view.

A branch manager may want to look at the names and phone numbers of all customers whose zip codes fall between 90000 and 93999 (southern and central California). A view that places a restriction on the rows it retrieves, as well as the columns it displays, does the job. Figure 1-5 shows the sources for the columns in the branch manager’s view.

The accounts-payable manager may want to look at customer names from the CUSTOMER table and Date, TotalSale, TotalRemitted, and FormOfPayment from the INVOICE table, where TotalRemitted is less than TotalSale. The latter would be the case if full payment hasn't yet been made. This need requires a view that draws from both tables. Figure 1-6 shows data flowing into the accounts-payable manager’s view from both the CUSTOMER and INVOICE tables.

Views are useful because they enable you to extract and format database data without physically altering the stored data. They also protect the data that you don’t want to show, because they don’t contain it. Chapter 6 illustrates how to create a view by using SQL.

Database Design Considerations

A database is a representation of a physical or conceptual structure, such as an organization, an automobile assembly, or the performance statistics of all the major-league baseball clubs. The accuracy of the representation depends on the level of detail of the database design. The amount of effort that you put into database design should depend on the type of information you want to get out of the database. Too much detail is a waste of effort, time, and hard-drive space. Too little detail may render the database worthless.

Decide how much detail you need now and how much you may need in the future — and then provide exactly that level of detail in your design (no more and no less). But don’t be surprised if you have to adjust the design eventually to meet changing real-world needs.

Today’s database management systems, complete with attractive graphical user interfaces and intuitive design tools, can give the would-be database designer a false sense of security. These systems make designing a database seem comparable to building a spreadsheet or engaging in some other relatively straightforward task. No such luck. Database design is difficult. If you do it incorrectly, not only is your database likely to suffer from poor performance, but it also may well become gradually more corrupt as time goes on. Often the problem doesn’t turn up until after you devote a great deal of effort to data entry. By the time you know that you have a problem, it’s already serious. In many cases, the only solution is to completely redesign the database and reenter all the data. The up side is that by the time you finish your second version of the same database, you realize how much better you understand database design.