MySQL, Java, JSP, Servlet, SSH, Tomcat Web Hosting Blog

James Blish Isaac Azimov Larry Niven Jerry Pournelle William Shakespeare Kurt Vonnegut Execute an expression on a single field of the AUTHOR table, returning a small section of the author s name: SELECT AUTHOR_ID, SUBSTR(NAME,1,10) FROM AUTHOR; AUTHOR_ID SUBSTR(NAM ———- ———- 1 Orson Scot 2 James Blis 3 Isaac Azim 4 Larry Nive 5 Jerry Pour 6 William Sh 7 Kurt Vonne Execute an expression, but this time involving more than a single field: SELECT E.ISBN, (E.LIST_PRICE * R.RANK) + R.INGRAM_UNITS FROM EDITION E JOIN RANK R ON (R.ISBN = E.ISBN); ISBN (E.LIST_PRICE*R.RANK)+R.INGRAM_UNITS ———- ———————————— 198711905 46072.5 345308999 9728 345336275 11860 345438353 24200 553278398 14430 553293362 7985 553293370 14815 553293389 8370 893402095 14026.5 1585670081 34600 5557076654 37632.5 Use aliases as substitutes for table names: SELECT A.NAME, P.TITLE, E.ISBN FROM AUTHOR A JOIN PUBLICATION P USING (AUTHOR_ID) Retrieving specific field names is very slightly more efficient than retrieving all fields using the * character. The * character requires the added overhead of metadata interpretation lookups into the metadata dictionary to find the fields in the table. In highly concurrent, very busy databases, continual data dictionary lookups can stress out database concurrency handling capacity. 128 Chapter 5
From our experience, we are can tell you that you can find a reliable and cheap webhost service at Java Web Hosting services.

. Database structure change commands These commands allow alterations to metadata (the data about the data). Metadata in a simple relational database comprises tables and indexes. Table metadata change commands allow creation of new tables, changes to existing tables, and destruction of existing tables, among other more obscure types of operations too obscure for this book. Table metadata commands are CREATE TABLE, ALTER TABLE, and DROP TABLE commands. Querying a Database Using SELECT The following sections examine database queries using the SELECT command in detail, as well as by example. Basic Queries The following syntax shows the structure of the SELECT statement and the FROM clause. The SELECT list is the list of fields, or otherwise, usually retrieved from tables. The FROM clause specifies one or more tables from which to retrieve data. SELECT { [alias.]field | expression | [alias.]* [,the rest of the list of fields] } FROM table [alias] [ , … ]; The easiest way to understand SQL is by example. Retrieve all fields from the AUTHOR table using the * (star or asterisk) character. The * character tells the query to retrieve all fields in all tables in the FROM clause: SELECT * FROM AUTHOR; AUTHOR_ID NAME ———- ——————————– 1 Orson Scott Card 2 James Blish 3 Isaac Azimov 4 Larry Niven 5 Jerry Pournelle 6 William Shakespeare 7 Kurt Vonnegut A semi-colon and carriage return is used to end the query command, submitting the query to the query engine. Not all relational databases execute on a semi-colon. Some databases use a different character; others just a carriage-return. Specify an individual field by retrieving only the name of the author from the AUTHOR table: SELECT NAME FROM AUTHOR; NAME ——————————– Orson Scott Card Please see the Introduction to this book for syntax conventions, known as Backus-Naur Form syntax notation. 127 Reading and Writing Data with SQL
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

Many of the smaller-scale database engines such as dBase and MSAccess are only written for a single platform and operating system. In the case of dBase and MSAccess, that system is PC-based, running the Windows operating system. So, what are the basics of SQL? The Basics of SQL The basics of SQL consist of a number of parts. This section briefly introduces what simple SQL can do. As you read through this chapter, each is explained individually by example. . Query commands Querying a database is performed with a single command called the SELECT command. The SELECT command creates queries, and has various optional clauses that include performing functions such as filtering and sorting. Queries are used to retrieve data from tables in a database. There are various ways in which data can be retrieved from tables: Some database engines refer to SQL commands as SQL statements. Don t get confused. The two terms mean exactly the same thing. I prefer the term command. This is merely a personal preference on my part. The term clause is usually applied to subset parts of commands. . Basic query The most simple of queries retrieves all records from a single table. . Filtered query Afiltered query uses a WHERE clause to include or exclude specific records. . Sorted query Sorting uses the ORDER BY clause to retrieve records in a specific sorted order. . Aggregated query The GROUP BY clause allows summarizing, grouping, or aggregating of records into summarized record sets. Typically, aggregated queries contain fewer records than the query would produce, if the GROUP BY clause were not used. AHAVING clause can be used to filter in, or filter out, records in the resulting summarized aggregated record set. In other words, the HAVING clause filters the results of the GROUP BY clause, and not the records retrieved before aggregation. . Join query A join query joins tables together, returning records from multiple tables. Joins can be performed in various ways, including inner joins and outer joins. . Nested queries Anested query is also known as a subquery, which is a query contained within another query (a parent or calling query). Nesting implies that subqueries can be nested in multiple layers and thus a subquery itself can also be a calling query of another subquery. . Composite queries A composite query is a query that merges multiple query results together, most often using the UNION keyword. . Data change commands The following commands are used to change data in tables in a database. . INSERT The INSERT command is used to add new records to a table. . UPDATE The UPDATE command allows changes to one or more records in a table, at once. . DELETE The DELETE command allows deletion of one or more records from a table, at once. 126 Chapter 5
You want to have a cheap webhost for your apache application, then check apache web hosting services.

The Origins of SQL IBM created the original relational database technology. SQL was created as an uncomplicated, non-procedural way of accessing data from an IBM-created relational database. SQL was initially called Sequel. Thus, SQL is often pronounced as sequel. For some other databases, SQL is pronounced by representing each letter, as in ess-queue-ell. The meaning of the two pronunciations is identical. The query language used to access an object database is called Object Database Query Language (ODQL). The acronym QL thus means query language, a language used to query a database. In its most primitive form, SQL stems from the idea of a reporting language, devised in theory by the inventor of the relational database model. The roots of SQL lie in retrieval of sets of data. What this means is that SQL is intended as a language to retrieve many records from one or many tables at once, yielding a result set. SQL was not originally intended to retrieve individual records from a relational database, as exact record matches, common in transactional and OLTP databases. However, SQL can now be used to do precisely just that, and with excellent efficiency. SQL is now used in modern-day relational database engines to retrieve both sets of records, and individual records, in transactional, OLTP, and data warehouse databases. What does all this mean without using a plethora of nasty long words? In short, SQL was developed as a shorthand method of retrieving information from relational databases. SQL has become the industry standard over the last 20 years. The following is an example of a query (a request or question put to the database) written in SQL, in this case all the records in the AUTHOR table will be retrieved: SELECT AUTHOR_ID, NAME FROM AUTHOR; AUTHOR_ID NAME ———- ——————————– 1 Orson Scott Card 2 James Blish 3 Isaac Azimov 4 Larry Niven 5 Jerry Pournelle 6 William Shakespeare 7 Kurt Vonnegut SQL for Different Databases SQL as implemented in different database engines is not standardized. Each database vendor developed a unique relational database, and relational database management system (database management toolkit). The result was different relational databases having different strengths. The vendors often altered and extended the standard form of SQL to take advantage of the way in which their individual products were written. The result is that relational database products from different vendors, although similar in nature, and even appearance, are often very different internally. Additionally, the different relational databases are different both in internal structural characteristics, and in the way they are used. And that s only the database engine itself. There is also use of different computer hardware platforms and operating systems. The larger database vendors service multiple operating systems, with completely different versions of the database software, applicable to different operating systems, even down to different flavors of Unix. 125 Reading and Writing Data with SQL
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

In this chapter, you learn about the following: . What SQL is . The origins of SQL . Many types of queries . Changing data in tables . Changing table structure Defining SQL SQL is a non-procedural language used for accessing field and record values in relational database tables. A procedural programming language is a language where there can be dependencies between sequential commands. For example, when setting a variable X = 1 on a line, that variable X can be used to reference the value 1, as the variable X in subsequent lines of programming code. A non-procedural programming language does not allow communication between different lines of programming code. Non-procedural languages also do not allow use of procedures. A procedure allows intra-program communication by passing values in and out of procedures. Keep in mind the following when considering what SQL is. . SQL is structured in the sense that it is used to access structured data, in a structured manner, or retrieve data from organized structures. Those organized structures are tables. . Use of the word language implies a computer programming language. Computer programming languages are often used to get information from a computer. . A non-procedural language essentially submits a single command, with a questioning or querying type nature. SQL is a non-procedural language consisting of single commands, where the database itself does a lot of the work in deciding how to get that information. On the other hand, a procedural language contains blocks of commands. Those blocks of commands are sequences of distinct steps, typically where each successive step is dependent on the result of the previous command in the sequence. . In most relational databases, SQL does have the capability to function in a limited procedural fashion, allowing the programmer to determine partially how a database is accessed. Traditionally, SQL procedural code is used to write what are called stored procedures, triggers, database events, or database procedures. There are other names for these code blocks, depending on the database in use. Procedural SQL code is generally simplistic and can sometimes allow the inclusion of basic programming constructs such as IF statements, CASE statements, and so on. So, SQL is a simple non-procedural programming language allowing single line commands. These singleline commands are used to access data stored in database table structures. 124 Chapter 5
Note: If you are looking for cheap and reliable webhost to host and run your mysql application check mysql web server services.

5 Reading and Writing Data with SQL Any sufficiently advanced technology is indistinguishable from magic. (Arthur C. Clarke) SQL helps to explain relational database modeling. None of this is magic, and is much easier to understand than you might think. This chapter shows how the relational database model is used from an application perspective. There is little point in understanding something, such as relational database modeling, without seeing it applied in some way, no matter how simple. With that in mind, this chapter looks at how a database model is accessed when it contains data in a database. A relational database model contains tables, and the records in those tables are accessed using Structured Query Language (SQL). SQL is used to create the database access sections of applications. When a database model is correctly designed, creation of SQL code is a simple process. Great difficulties in coding of SQL queries in particular can often indicate serious database model design flaws. This book is all about relational database modeling; therefore, it is necessary to explain only the basis of SQL in this book as pertaining directly to helping in the understanding of relational database modeling. How can describing the basic details of SQL help with the understanding of relational database modeling? The answer to this question is very simple. SQL uses a relational database model to change data in a database, and to retrieve data from that database. SQL essentially applies all the structures created by the relational database modeling process. SQL and particularly SQL queries and their simplicity is a direct result of the underlying structure of a database model. In other words, the better and more appropriate a database model is, the easier building SQL code for applications will be. Another way to look at this is that a database model is most easily utilized by applications, when matching application structure. It makes perfect sense to describe the basics of using SQL because it demonstrates the use of a relational database model in action.
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.

this business rules. It is generally common knowledge that some business rule implementation is often most effectively divided up between database and applications. DKNF is an ideal form and an ultimate or final form of relational database normalization. This level of normalization is more mathematically perfect and has beauty in its granular simplicity. In an object database model, this approach is perfectly suited where individual data items are only ever accessed as unique elements (primary keys) and records are never accessed in groups. In a relational database model where commercially most databases require not only exact matches but also range searching for reporting, this level of intensity in normalization nearly always has a seriously negative impact on general database and application performance, and thus a negative effect on end-user satisfaction. End-user satisfaction is the objective of any application. If it isn t, it should be because they usually pay the bills! Summary In this chapter, you learned about: . What normalization is, its benefits and potential hazards . A layman s method of understanding normalization . A purist, academic definition of normalization . How referential integrity is maintained using primary and foreign keys . Normal Forms from 1st through to 5th, including Boyce-Codd Normal Form and Domain Key Normal Form . Special terminology used in Normal Forms The next chapter discusses some advanced relational database modeling techniques, including denormalization, special tricks used for different database model types, followed by introductions to the object database model and data warehouse database modeling. Exercises 1. Write five CREATE TABLE commands for the tables in Figure 4-29. Ensure that all primary key, foreign key, and any potentially necessary unique keys are included. 2. Write CREATE INDEX commands to create all indexes on any foreign keys indicated in the CREATE TABLE commands written for the previous question. 122 Chapter 4
You need excellent and relaible webhost company to host your web applications? Then pay a visit to Inexpensive Web Hosting services.

Figure 4-44: 5NF transformations must return records identical to the pre-5NF transformation when joined. Domain Key Normal Form (DKNF) Domain Key Normal Form (DKNF) is quite often a conceptual state of a relational database model as opposed to a final transformation process. DKNF is the ultimate normal form and essentially describes how a completely normalized database model should appear: . There can be no insertion, change, or data removal anomalies. In other words, every record in the database must be directly accessible in all manners, such that no errors can result. . Every record in every table must be uniquely identifiable and directly related to the primary key in its table. This means that every field in every table is directly determined by the primary key in its table. . All validation of data is done within the database model. As far as application and database performance are concerned this is nearly always an extremely undesirable state in a commercial environment. It is better to split functionality between database and applications. Some may call SQL> SELECT project, manager, employee 2 FROM Employee 3 ORDER BY project, manager, employee; PROJECT ———- Analysis Analysis Analysis DW DW DW HTML HTML 8 rows selected. MANAGER ———- Jim Jim Joe Gemima Larry Larry Jackson Jackson EMPLOYEE ———- Magenta Riffraff Brad Columbia Brad Janet Columbia Riffraff SQL> SELECT project, manager, employee 2 FROM Project_Employee 3 NATURAL JOIN Project_Manager 4 NATURAL JOIN Manager_Employee 5 ORDER BY project, manager, employee; PROJECT ———- Analysis Analysis Analysis DW DW DW HTML HTML 8 rows selected. MANAGER ———- Jim Jim Joe Gemima Larry Larry Jackson Jackson EMPLOYEE ———- Magenta Riffraff Brad Columbia Brad Janet Columbia Riffraff Returns identical data and number of rows for both queries Simple query from pre-5th Normal Form transformation table NATURAL JOIN joins on column names regardless of indexes 121 Understanding Normalization
In case you need quality webspace to host and run your web applications, try our personal web hosting services.

Create the PROJECT_MANAGER table and add records: CREATE TABLE Project_Manager ( project VARCHAR2(32) NOT NULL, manager VARCHAR2(32) NOT NULL, PRIMARY KEY (project, manager) ); INSERT INTO Project_Manager VALUES( Analysis , Joe ); INSERT INTO Project_Manager VALUES( Analysis , Jim ); INSERT INTO Project_Manager VALUES( DW , Larry ); INSERT INTO Project_Manager VALUES( DW , Gemima ); INSERT INTO Project_Manager VALUES( HTML , Jackson ); COMMIT; Create the MANAGER_EMPLOYEE table and add records: CREATE TABLE Manager_Employee ( manager VARCHAR2(32) NOT NULL, employee VARCHAR2(32) NOT NULL, PRIMARY KEY (manager, employee) ); INSERT INTO Manager_Employee VALUES( Gemima , Columbia ); INSERT INTO Manager_Employee VALUES( Jackson , Columbia ); INSERT INTO Manager_Employee VALUES( Jackson , Riffraff ); INSERT INTO Manager_Employee VALUES( Jim , Magenta ); INSERT INTO Manager_Employee VALUES( Jim , Riffraff ); INSERT INTO Manager_Employee VALUES( Joe , Brad ); INSERT INTO Manager_Employee VALUES( Larry , Brad ); INSERT INTO Manager_Employee VALUES( Larry , Janet ); COMMIT; As previously stated, the one hard-and-fast rule with respect to 5NF is that the pre-5NF records must be identical to the 5NF divided up tables (as shown in Figure 4-44) when querying the database. In other words, the two queries must match in this case, records from one table must match records from the three joined tables. 120 Chapter 4
Please visit our professional web hosting services to find out about cheap and reliable webhost service that will surely answer all your demands.

Figure 4-43: 5NF transformations sometimes remove duplicates. Going further with the table-creation commands and record-insertion commands for each of the three separate 5NF tables, note that all records added are exactly the same as for adding to the pre-5NF table, with any duplicate records removed. Create the PROJECT_EMPLOYEE table and add records: CREATE TABLE Project_Employee ( project VARCHAR2(32) NOT NULL, employee VARCHAR2(32) NOT NULL, PRIMARY KEY (project, employee) ); INSERT INTO Project_Employee VALUES( Analysis , Brad ); INSERT INTO Project_Employee VALUES( Analysis , Riffraff ); INSERT INTO Project_Employee VALUES( Analysis , Magenta ); INSERT INTO Project_Employee VALUES( DW , Janet ); INSERT INTO Project_Employee VALUES( DW , Brad ); INSERT INTO Project_Employee VALUES( DW , Columbia ); INSERT INTO Project_Employee VALUES( HTML , Columbia ); INSERT INTO Project_Employee VALUES( HTML , Riffraff ); COMMIT; PROJECTALL Analysis Analysis Analysis DW DW DW HTML HTML EMPLOYEE Brad Riffraff Magenta Janet Brad Columbia Columbia Riffraff PROJECTALL Analysis Analysis DW DW HTML MANAGER Joe Jim Larry Gemima Jackson MANAGERALL Gemima Jackson Jackson Jim Jim Joe Larry Larry EMPLOYEE Columbia Columbia Riffraff Magenta Riffraff Brad Brad Janet Some duplicate rows were removed 119 Understanding Normalization
Go visit our java server pages services for a reliable, lowcost webhost to satisfy all your needs.