SQL Bible - Brossura

Kriegel, Alex; Trukhnov, Boris M.

Brossura

ISBN 10: 0470229063 ISBN 13: 9780470229064

Casa editrice: John Wiley & Sons Inc, 2008

Riassunto

Combining theory with everyday practicality, this definitive volume is packed with the up-to-date information, new features, and explanations you need to get the very most out of SQL and its latest standard. The book is unique in that every chapter highlights how the new SQL standard applies to the three major databases, Oracle 11g, IBM DB2 9.5, and Microsoft SQL Server 2008. The result is a comprehensive, useful, and real-world reference for all SQL users, from beginners to experienced developers.

Su questo libro

Informazioni sull?autore

Alex Kriegel, PMP, MCSD/MCTS, works as a Systems Architect for the State of Oregon; prior to this he worked for Pope and Talbot, Inc., Psion Teklogix International, Inc., and for Belorussian Academy of Sciences. He received his MSc. in Physics of Metals from Belarus State Polytechnic Institute in 1988, discovered PC programming and relational databases in 1992, and has never looked back since. Alex is certified as a Project Management Professional (PMP) by the Project Management Institute, and also holds various Microsoft certifications. He is also the author of Microsoft SQL Server 2000 Weekend Crash Course (Wiley, 2001), SQL Functions (Wrox, 2005), and co-author of Introduction to Database Management (Wiley, 2007), and the first edition of this book.

Boris M. Trukhnov, OCP, has been working as Senior Technical Analyst/Oracle DBA for Pope & Talbot, Inc., in Portland, Oregon, since 1998. His previous job titles include Senior Programmer Analyst, Senior Software Developer, and Senior Operations Analyst. He has been working with SQL and relational databases since 1994. Boris holds a B.S. in Computer Science from the University of Minnesota. He is a co-author of Introduction to Database Management (Wiley, 2007).

Dalla quarta di copertina

Harness SQL for Oracle 11g, IBM DB2, and Microsoft SQL Server 2008

Combining theory with everyday practicality, this de?itive volume is packed with up-to-date information, new features, and explanations you need to get the very most out of SQL and its latest standard. The book is unique in that every chapter highlights how the new SQL standard applies to the three major databases, Oracle 11g, IBM DB2 9.5, and Microsoft SQL Server 2008. The result is a comprehensive, useful, and real-world reference for all SQL users, from beginners to experienced developers.

• Learn the basics of SQL and relational databases

• Explore tables, views, sequences, and other database objects

• Examine transactions and locking in multiuser environments

• Review functions mandated by the SQL standard and the three major vendors

• Access metadata information and implement database security

• Go beyond SQL to XML integration, OLAP business intelligence, and more

Companion Web Site
Find downloadable code examples, a full book description, and more at wiley.com/go/SQLBible2.

Estratto. © Ristampato con autorizzazione. Tutti i diritti riservati.

SQL Bible

By Alex Kriegel Boris M. Trukhnov

John Wiley & Sons

Copyright © 2008 Alex Kriegel
All right reserved.
ISBN: 978-0-470-22906-4

Chapter One

SQL and Relational Database Management Systems

IN THIS CHAPTER

Understanding databases

Characteristics of a good database

The database market

Real-life database examples

Brief database history

SQL standards and implementation differences

Information may be the most valuable commodity in the modern world. It can take many different forms - such as accounting and payroll information, information about customers and orders, scientific and statistical data, graphics, or multimedia. We are virtually swamped with data, and we cannot - or at least we'd like to think about it this way - afford to lose it. These days we simply have too much data to keep storing it in file cabinets or cardboard boxes. The need to store large collections of persistent data safely, "slice and dice" it efficiently from different angles by multiple users, and update it easily when necessary is critical for every enterprise. That need mandates the existence of databases, which accomplish all the tasks listed, and then some. To put it simply, a database is just an organized collection of information - with emphasis on "organized."

A more specific definition often used as a synonym for "database" is database management system (DBMS). That term is wider and, in addition to the stored information, includes some methods to work with data and tools to maintain it.

NOTE

DBMS can be defined as a collection of interrelated data plus a set of programs to access, modify, and maintain the data. More about DBMS later in this chapter.

Desirable Database Characteristics

There are many differing ideas about what a database is and what it should do. Nevertheless, all modern databases should have at least the following characteristics.

Sufficient capacity

A database's primary function is to store large amounts of information. For example, an order management system for a medium-sized company can easily grow into gigabytes or even terabytes of data; the bigger the company, the more data it needs to store and rely upon. A company that wants to keep historical (archival) data will require even more storage space. The need for storage capacity is growing rapidly; the recent change in how audio and video files are being used is a good example of that. Just five or six years ago, storing movie files in a database, editing and remixing them using online tools, and sharing them on the Internet would sound almost unrealistic, and today it is our day-to-day reality. Just think of the popularity of YouTube and similar services (such as Eyespot, Grouper, Jumpcut, VideoEgg, and so on).

Adequate security and auditing

As was noted previously, enterprise data is valuable and must be stored safely. That means protection of the stored data not only from malicious or careless human activities, such as unauthorized logins, accidental information deletions or modifications, and so on, but also from hardware failures and natural disasters. For many companies, database security is not a "nice-to-have" feature, but rather a mandatory requirement regulated by a number of different level standards, including federal laws, such as the Sarbanes-Oxley Act of 2002 (Sarbox) and the Health Insurance Portability and Accountability Act of 1996 (HIPAA).

Multiuser environment

It is also important to note that, in order to be useful, the information stored in a database must be accessible to many users simultaneously at different levels of security, and, no matter what, the data must stay consistent. For example, if two users try to change the same piece of information at the same time, the result can be unpredictable (such as data corruption), so situations like that have to be handled appropriately by internal database mechanisms. Also, certain groups of users may be allowed to modify several pieces of information, browse other parts of it, and be prevented from even viewing yet another part. (Some company data, such as employee and customer personal information, can be strictly confidential with very restricted access.)

Effectiveness and searchability

Users need quick access to the data they want. It is very important not only to be able to store data, but also to have efficient algorithms to work with it. For example, it would be unacceptable for users to have to scroll through each and every record to find just one order among millions stored in the database - the response to someone's querying the database must be fast, preferably instantaneous.

NOTE

As an analogy, suppose you wanted to find all the occurrences of the word "object" in a book. You could physically browse through the entire book page by page until you reach the end. Or you could use the index and determine that the word is used on pages 245, 246, and 348. This situation is comparable to using bad or good programming algorithms.

Scalability

Databases must be flexible and easily adaptable to changing business needs. That primarily means the internal structure of database objects should be easily modifiable with minimum impact on other objects and processes. For example, to add a field in a pre-SQL database, you would have to bring the whole dataset offline - that is, make it inaccessible to users, modify it, change and recompile related programs, and so on. This is covered in more detail in the "Database Legacy" section of this chapter.

Another scalability aspect is that data typically lives longer than the hardware and software used to access and manipulate it, so it would not be very convenient to have to redesign the entire database to accommodate the current "flavor-of-the-month" development environment, for example, in case of a takeover or when company management suddenly decided to switch the production environment from Java to C#.

In addition, a small private company with a handful of customers can gradually grow into a large corporation with hundreds of thousands or even millions of users. In this case, it would be very useful to have the ability to start with a simplified, inexpensive (or even free) database edition on a small server with the ability to switch easily to the same vendor's Enterprise version on a powerful multiprocessor machine.

NOTE

A very popular and recent alternative to the idea of a powerful multiprocessor server is the concept of "grid" computing when a virtual supercomputer is being assembled from multiple nodes where each node is a relatively small server. The nodes can be easily added or removed from the system, which significantly improves its scalability.

User friendliness

Databases are not just for programmers and technical personnel (some would say not for programmers - period). Nontechnical users constitute the majority of all database users nowadays. Accountants, managers, salespeople, doctors, nurses, librarians, scientists, technicians, customer service representatives - for all these and many more people, interaction with databases is an integral part of their work. That means data must be easy to manipulate. Of course, most users will access it through a graphical user interface with a predefined set of screens and limited functionality, but ad hoc database queries and reports are becoming more and more popular, especially among sophisticated, computer-literate users.

NOTE

Consider this. An order management application has a screen to view all orders and another window to browse customers. It can also generate a number of reports, including one to analyze orders grouped by customer. Accountant Jerry is working on a report for his boss and needs to find the 10 customers with the highest debt. He can request a new report from the IT department, but it will take days (or even weeks) because of bureaucratic routine, programmers' busyness, or something else. The knowledge of SQL can help Jerry to create his own ad hoc query, get the data, and finish his report. The use of a GUI tool such as TOAD or Win-SQL would make Jerry's task even easier - in this case, he doesn't have to know the exact SQL syntax. The tool can build and execute the SQL statement he needs if only Jerry could specify the tables and columns he wants to retrieve as well as the relationships between these tables.

Selecting Your Database Software

Every single DBMS on the market follows essentially the same basic principles. There is a wide variety of database products on the market, and it is very difficult for a person without a solid database background to make a decision on what would be the right product to learn or use. The database market is chock-full of different relational database management systems (RDBMSs): IBM DB2, Oracle, Microsoft SQL Server, Sybase, MySQL, and PostgreSQL, to name just a few.

No two systems are exactly alike: There are relatively simple-to-use systems, and there are some that require serious technical expertise to install and operate; some products are free, and others are fairly expensive - all in addition to a myriad of other little things such as licensing, availability of expertise, and so on. There is no single formula to help you in the DBMS selection process, but rather, there are several aspects to consider when making the choice. Here are the most common considerations.

Market share

According to a study by IDC, a subsidiary of International Data Group (IDG), in 2006 the three major DBMS vendors shared over 84 percent of the database market. Oracle accounted for 44.3 percent, IBM about 21.2 percent, and Microsoft SQL Server 18.6 percent. Sybase ranked fourth with 3.2 percent, followed by Teradata (2.8 percent); the rest of the market (less than 10 percent) is shared among dozens (or maybe hundreds) of small vendors or nonrelational "dinosaurs."

It's also worth noticing that the share of the "top three" is constantly growing (at the expense of their smaller competitors). In 1997, the combined share of the "big three" was less than 70 percent. In 2001, it increased to about 80 percent, and today the number is well over 85 percent.

The total market for RDBMS software grew by 14.3 percent from 2005 to 2006. The leader here is Microsoft with a 25-percent growth rate, followed by Oracle (14.7 percent). IBM is slightly below the average with about 12 percent.

The share of all open-source RDBMS vendors is quite insignificant, and according to Gartner, Dataquest research was less than 1 percent of the database market in 2005; however, the growth rate among RDBMS vendors was about 47 percent, thanks to few popular products such as MySQL and PostgreSQL.

Total cost of ownership

The prices for the three major implementations are comparable but could vary depending on included features, number of users, and computer processors from under a thousand dollars for a standard edition with a handful of licenses to hundreds of thousands or even millions for enterprise editions with unlimited user access. Many small database vendor implementations are free; moreover, during the last few years, all "top three" vendors released their own versions of free RDBMS. Oracle has offered a starter XE database since 2005; Microsoft has SQL Server Express available at no cost; and IBM recently released an Express-C edition of DB2.

Skills are a different story. Database expertise is a costly thing and usually is in short supply. On average, Oracle expertise is valued a little higher than comparable expertise for Microsoft SQL Server or DB2. The total cost of ownership (TCO) analysis released by vendors themselves tends to be biased, so use your best judgment and do your homework before committing your company to a particular vendor. Make no mistake about it - this is a long-term commitment, as switching the database vendors halfway into production is an extremely painful and costly procedure.

Support and persistence

One may ask, why spend thousands of dollars on something that can be substituted with a free product? The answer is quite simple: For a majority of businesses, the most important thing is support. They pay money for company safety and shareholders' peace of mind, in addition to all the bells and whistles that come with an enterprise-level product with a big name. (As the adage goes: "No one was ever fired for buying IBM.") First, they can count on relatively prompt support by qualified specialists in case something goes wrong. Second, the company management can make a reasonable assumption that vendors such as IBM, Microsoft, and Oracle will still be around 10 years from now. (Nobody can guarantee that, of course, but their chances definitely look better against the odds of their smaller competitors.) In addition, "free" products rarely scale as well as the costlier products, and are rarely as manageable, as robust, or as clever at optimizing wide varieties of queries over many orders of magnitude of data size. So, the less expensive (and sometimes free) products by smaller database vendors might be acceptable for small businesses, nonprofit organizations, or noncritical projects, but very few serious companies would even consider using them for, say, their payroll or accounting systems.

NOTE

We should mention that more and more companies are using open-source RDBMS for certain tasks as the products become more reliable and more functional. Good examples include Sony Online Entertainment switching to EnterpriseDB (an enterprise-class relational database management system built on PostgreSQL) or Google, Yahoo, and Ticketmaster using MySQL for their key projects. Also, RDBMS tools vendors have started to provide support for some popular open-source database products. For example, TOAD by Quest Software, a database tool popular among Oracle developers, is now available for MySQL. In addition, some serious companies specializing in software support now offer their services for certain open-source products.

Major DBMS Implementations

One book cannot possibly cover all existing database implementations, so we've decided to concentrate on "the big three": Oracle; IBM DB2 for Linux, UNIX, and Windows; and Microsoft SQL Server. These implementations have many common characteristics. They are all industrial-strength, enterprise-level relational databases (the relational database model and SQL standards are covered later in this chapter). They use Structured Query Language (SQL) standardized by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO). All three are able to run on the Windows operating system. Oracle also is available on virtually any UNIX flavor, Linux, Apple Mac OS X Server, IBM z/OS. OpenVMS; DB2 for Linux, UNIX, and Windows runs on AIX, HP-UX, Solaris, Linux, and Microsoft

NOTE

ANSI is a private, nonprofit organization that administers and coordinates the U.S. voluntary standardization and conformity assessment system. ANSI's mission is to enhance both the global competitiveness of U.S. business and the U.S. quality of life by promoting and facilitating voluntary consensus standards and conformity assessment systems, and safeguarding their integrity. ANSI (www.ansi.org) was founded October 18, 1918, and is the official U.S. representative to ISO (www.iso.org) and some other international institutions.

The problem is that none of the databases mentioned earlier is 100-percent ANSI SQL-compliant. Each of these databases shares the basic SQL syntax (although some diversity exists even there), but the language operators, naming restrictions, internal functions, data types (especially date-and time-related), and procedural language extensions are implemented differently.

CROSS-REF

See Chapter 14, "Stored Procedures, Triggers, and User-Defined Functions," for more information on the SQL procedural extensions.

Table 1-1 compares some data on maximum name lengths supported by different database implementations.

In an ideal world, the standards would rule supreme, and SQL would be freely shared among different implementations for the benefit of humanity. Unfortunately, the reality looks somewhat different. While it is possible to distill a standard SQL understood by all database vendors' products, anything other than some very trivial tasks would be more quickly and efficiently accomplished with implementation-specific features.

(Continues...)

Excerpted from SQL Bibleby Alex Kriegel Boris M. Trukhnov Copyright © 2008 by Alex Kriegel. Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Dettagli dell'edizione

• Editore: John Wiley & Sons Inc
• Data di pubblicazione: 2008
• Lingua: Inglese
• ISBN 10: 0470229063
• ISBN 13: 9780470229064
• Rilegatura: Copertina flessibile
• Numero edizione: 2
• Numero di pagine: 857
• Contatto del produttore: non disponibile
• Persona responsabile: non disponibile