Über die Autorin bzw. den Autor

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).

Von der hinteren Coverseite

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.

Aus dem Klappentext

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.

Auszug. © Genehmigter Nachdruck. Alle Rechte vorbehalten.

SQL Bible

John Wiley & Sons

Chapter One

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

...