1 22 Database SQL My SQL ADO NET

2 It is a capital mistake to theorize before one has data. — Arthur Conan Doyle Now go, write it before them in a table, and note it in a book, that it may be for the time to come for ever and ever. — The Holy Bible, Isaiah 30: 8 2008 Pearson Education, Inc. All rights reserved.

3 Get your facts first, and then you can distort them as much as

4 OBJECTIVES In this chapter you will learn: § Relational database concepts. § To use Structured Query Language (SQL) to retrieve data from and manipulate data in a database. § To install and configure My. SQL. § To create a My. SQL database. § The ADO. NET 2. 0 object model. 2008 Pearson Education, Inc. All rights reserved.

5 22. 1 Introduction 22. 2 Relational Databases 22. 3 Relational Database Overview: The books Database 22. 4 SQL 22. 4. 1 Basic SELECT Query 22. 4. 2 WHERE Clause 22. 4. 3 ORDER BY Clause 22. 4. 4 Combining Data from Multiple Tables: INNER JOIN 22. 4. 5 INSERT Statement 22. 4. 6 UPDATE Statement 22. 4. 7 DELETE Statement 2008 Pearson Education, Inc. All rights reserved.

6 22. 5 My. SQL 22. 6 Instructions for Installing My. SQL 22. 7 Instructions for Setting Up a My. SQL User Account 22. 8 Creating a Database in My. SQL 22. 9 ADO. NET Object Model 22. 10 Java DB/Apache Derby 22. 11 Wrap-Up 22. 12 Web Resources 2008 Pearson Education, Inc. All rights reserved.

7 22. 1 Introduction • A database is an integrated collection of data. A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data. • Today’s most popular database management systems are relational database systems. • SQL is the international standard language used almost universally with relational database systems to perform queries and manipulate data. • Programs connect to, and interact with, relational databases systems via an interface—software that facilitates communications between a database management system and a program. 2008 Pearson Education, Inc. All rights reserved.

8 22. 2 Relational Data • A relational database stores data in tables. Tables are composed of rows, and rows are composed of columns in which values are stored. • A primary key is provides unique values that cannot be duplicated in other rows of the same table. • Each column of a table represents a different attribute in a row of data. • The primary key can be composed of more than one column. • SQL provides a rich set of language constructs that enable you to define complex queries to retrieve data from a database. 2008 Pearson Education, Inc. All rights reserved.

9 22. 2 Relational Data (Cont. ) • Every column in a primary key must have a value, and the value of the primary key must be unique. This is known as the Rule of Entity Integrity. • A one-to-many relationship between tables indicates that a row in one table can have many related rows in a separate table. • A foreign key is a column in a table that matches the primary-key column in another table. • The foreign key helps maintain the Rule of Referential Integrity: Every foreign-key value must appear as another table’s primary-key value. Foreign keys can be used to combine information from multiple tables. There is a oneto-many relationship between a primary key and its corresponding foreign key. 2008 Pearson Education, Inc. All rights reserved.

10 Fig. 22. 1 | Employee table sample data. 2008 Pearson Education, Inc. All

11 Fig. 22. 2 | Result of selecting distinct Department and Location data from

22. 3 Relational Database Overview: A books Database 12 • Foreign keys also allow related data in multiple tables to be selected from those tables for analytic purposes—this is known as joining the data. 2008 Pearson Education, Inc. All rights reserved.

13 Fig. 22. 3 | authors table from the books database. 2008 Pearson Education,

14 Fig. 22. 4 | Sample data from the authors table. 2008 Pearson Education,

15 Fig. 22. 5 | author. ISBN table from the books database. 2008 Pearson

16 Fig. 22. 6 | Sample data from the author. ISBN table of books.

17 Fig. 22. 7 | titles table from the books database. 2008 Pearson Education,

18 Fig. 22. 8 | Sample data from the titles table of the books

22. 3 Relational Database Overview: A books Database (Cont. ) 19 • An entity-relationship (ER) diagram shows the database tables and the relationships among them. • Every row must have a primary-key value, and that value must be unique in the table. This is known as the Rule of Entity Integrity. • An infinity symbol (∞) indicates a one-to-many relationship, in which an entry from a table can have an arbitrary number of entries in another table. • A many-to-many relationship indicates that multiple entries can be related between tables. 2008 Pearson Education, Inc. All rights reserved.

20 Common Programming Error 22. 1 Not providing a value for every column in a primary key breaks the Rule of Entity Integrity and causes the DBMS to report an error. 2008 Pearson Education, Inc. All rights reserved.

21 Common Programming Error 22. 2 Providing the same value for the primary key

22 One-to-many relationship Primary keys Foreign keys Many-to-many relationship Fig. 22. 9 | Table

23 Common Programming Error 22. 3 Providing a foreign-key value that does not appear as a primary-key value in another table breaks the Rule of Referential Integrity and causes the DBMS to report an error. 2008 Pearson Education, Inc. All rights reserved.

24 22. 4 SQL • The next several sections will discuss most of the

25 Fig. 22. 10 | SQL query keywords. 2008 Pearson Education, Inc. All rights

26 22. 4. 1 Basic SELECT Query • The basic form of a query is SELECT * FROM table. Name where the asterisk (*) indicates that all columns from table. Name should be selected, and table. Name specifies the table in the database from which rows will be retrieved. • To retrieve specific columns from a table, replace the asterisk (*) with a comma-separated list of column names. 2008 Pearson Education, Inc. All rights reserved.

27 Software Engineering Observation 22. 1 For most queries, the asterisk (*) should not be used to specify column names. In general, you process results by knowing in advance the order of the columns in the result —for example, selecting author. ID and last. Name from table authors ensures that the columns will appear in the result with author. ID as the first column and last. Name as the second column. Programs typically process result columns by specifying the column number in the result (starting from number 1 for the first column). Selecting columns by name also avoids returning unneeded columns and protects against changes in the actual order of the columns in the table(s). 2008 Pearson Education, Inc. All rights reserved.

28 Common Programming Error 22. 4 If you assume that the columns are always returned in the same order from a query that uses the asterisk (*), the program may process the results incorrectly. If the column order in the table(s) changes or if additional columns are added at a later time, the order of the columns in the result changes accordingly. 2008 Pearson Education, Inc. All rights reserved.

29 SELECT author. ID, last. Name FROM authors Fig. 22. 11 | Sample author.

30 22. 4. 2 WHERE Clause • The optional WHERE clause in a query specifies the selection criteria for the query. The basic form of a query with selection criteria is SELECT column. Name 1, column. Name 2, … FROM table. Name WHERE criteria • The WHERE clause can contain operators <, >, <=, >=, =, <> and LIKE. Operator LIKE is used for string pattern matching with wildcard characters percent (%) and underscore (_). • A percent character (%) in a pattern indicates that a string matching the pattern can have zero or more characters at the percent character’s location in the pattern. • An underscore (_) in the pattern string indicates a single character at that position in the pattern. 2008 Pearson Education, Inc. All rights reserved.

31 Portability Tip 22. 1 See the documentation for your database system to determine whether SQL is case sensitive on your system and to determine the syntax for SQL keywords (i. e. , should they be all uppercase letters, all lowercase letters or some combination of the two? ). 2008 Pearson Education, Inc. All rights reserved.

32 SELECT title, edition. Number, copyright FROM titles WHERE copyright > '2005' Fig. 22. 12 | Sampling of titles with copyrights after 2005 from table titles. 2008 Pearson Education, Inc. All rights reserved.

33 SELECT author. ID, first. Name, last. Name FROM authors WHERE last. Name LIKE 'D%' Fig. 22. 13 | Authors whose last name starts with D from the authors table. 2008 Pearson Education, Inc. All rights reserved.

34 SELECT author. ID, first. Name, last. Name FROM authors WHERE last. Name LIKE '_o%' Fig. 22. 14 | The only author from the authors table whose last name contains o as the second letter. 2008 Pearson Education, Inc. All rights reserved.

35 22. 4. 3 ORDER BY Clause • The result of a query can be sorted in ascending or descending order using the optional ORDER BY clause. The simplest form of an ORDER BY clause is SELECT column. Name 1, column. Name 2, … FROM table. Name ORDER BY column ASC SELECT column. Name 1, column. Name 2, … FROM table. Name ORDER BY column DESC where ASC specifies ascending order, DESC specifies descending order and column specifies the column on which the sort is based. The default sorting order is ascending, so ASC is optional. • Multiple columns can be used for ordering purposes with an ORDER BY clause of the form ORDER BY column 1 sorting. Order, column 2 sorting. Order, … • The WHERE and ORDER BY clauses can be combined in one query. If used, ORDER BY must be the last clause in the query. 2008 Pearson Education, Inc. All rights reserved.

36 SELECT author. ID, first. Name, last. Name FROM authors ORDER BY last. Name

37 SELECT author. ID, first. Name, last. Name FROM authors ORDER BY last. Name DESC Fig. 22. 16 | authors sample data in descending order by last. Name. 2008 Pearson Education, Inc. All rights reserved.

38 SELECT author. ID, first. Name, last. Name FROM authors ORDER BY last. Name, first. Name Fig. 22. 17 | authors sample data in ascending order by last. Name and first. Name. 2008 Pearson Education, Inc. All rights reserved.

SELECT isbn, title, edition. Number, copyright 39 FROM titles WHERE title LIKE '%How to Program' ORDER BY title ASC Fig. 22. 18 | Sampling of books from table titles whose titles end with How to Program in ascending order by title. 2008 Pearson Education, Inc. All rights reserved.

22. 4. 4 Combining Data from Multiple Tables: INNER JOIN 40 • An INNER JOIN combines rows from two tables by matching values in columns that are common to the tables. The basic form for the INNER JOIN operator is: SELECT column. Name 1, column. Name 2, … FROM table 1 INNER JOIN table 2 ON table 1. column. Name = table 2. column. Name The ON clause specifies a condition that determines which rows are joined. This condition often compares columns from each table If a SQL statement uses columns with the same name from multiple tables, the column names must be fully qualified by prefixing them with their table names and a dot (. ). 2008 Pearson Education, Inc. All rights reserved.

41 Software Engineering Observation 22. 2 If a SQL statement includes columns with the same name from multiple tables, the statement must precede those column names with their table names and a dot (e. g. , authors. author. ID). 2008 Pearson Education, Inc. All rights reserved.

42 Common Programming Error 22. 5 Failure to qualify names for columns that have

43 SELECT first. Name, last. Name, isbn FROM authors INNER JOIN author. ISBN ON authors. author. ID = author. ISBN. author. ID ORDER BY last. Name, first. Name Fig. 22. 19 | Sampling of authors and ISBNs for the books they have written in ascending order by last. Name and first. Name. 2008 Pearson Education, Inc. All rights reserved.

44 22. 4. 5 INSERT Statement • An INSERT statement inserts a new row into a table. The basic form of this statement is INSERT INTO table. Name ( column. Name 1, column. Name 2, …, column. Name. N ) VALUES ( value 1, value 2, …, value. N ) where table. Name is the table in which to insert the row. The table. Name is followed by a comma-separated list of column names in parentheses. The list of column names is followed by the SQL keyword VALUES and a comma-separated list of values in parentheses. • SQL uses single quotes (') as the delimiter for strings. To specify a string containing a single quote in SQL, the single quote must be escaped with another single quote. 2008 Pearson Education, Inc. All rights reserved.

45 Good Programming Practice 22. 1 Always explicitly list the columns when inserting rows. If the table’s column order changes or a new column is added, omitting the columns list may cause an error. 2008 Pearson Education, Inc. All rights reserved.

46 Common Programming Error 22. 6 It is normally an error to specify a

47 Common Programming Error 22. 7 SQL uses the single-quote (') character as a delimiter for strings. To specify a string containing a single quote (e. g. , O’Malley) in a SQL statement, the string must have two single quotes in the position where the single-quote character appears in the string (e. g. , 'O''Malley'). The first of the two single-quote characters acts as an escape character for the second. Not escaping single-quote characters in a string that is part of a SQL statement is a SQL syntax error. 2008 Pearson Education, Inc. All rights reserved.

48 INSERT INTO authors ( first. Name, last. Name ) VALUES ( 'Sue', 'Smith'

49 22. 4. 6 UPDATE Statement • An UPDATE statement modifies data in a table. The basic form of an UPDATE statement is UPDATE table. Name SET column. Name 1 = value 1, column. Name 2 = value 2, …, column. Name. N = value. N WHERE criteria where table. Name is the table in which to update data. The table. Name is followed by keyword SET and a comma-separated list of column name/value pairs in the format column. Name = value. The optional WHERE clause criteria determines which rows to update. 2008 Pearson Education, Inc. All rights reserved.

50 UPDATE authors SET last. Name = 'Jones' WHERE last. Name = 'Smith' AND first. Name = 'Sue' Fig. 22. 21 | Sample data from table authors after an UPDATE operation. 2008 Pearson Education, Inc. All rights reserved.

51 22. 4. 7 DELETE Statement • A DELETE statement removes rows from a table. The simplest form for a DELETE statement is DELETE FROM table. Name WHERE criteria where table. Name is the table from which to delete a row (or rows). The optional WHERE criteria determines which rows to delete. If this clause is omitted, all the table’s rows are deleted. 2008 Pearson Education, Inc. All rights reserved.

52 DELETE FROM authors WHERE last. Name = 'Jones' AND first. Name = 'Sue'

53 22. 5 My. SQL • My. SQL (pronounced “my sequel”) is a robust and scalable relational database management system (RDBMS) that was created by the Swedish consulting firm Tc. X in 1994. • My. SQL is a multiuser, multithreaded RDBMS server that uses SQL to interact with and manipulate data. • Multithreading capabilities enable My. SQL database to perform multiple tasks concurrently, allowing the server to process client requests efficiently. • Implementations of My. SQL are available for Windows, Mac OS X, Linux and UNIX. 2008 Pearson Education, Inc. All rights reserved.

54 22. 9 ADO. NET Object Model • The ADO. NET object model provides an API for accessing database systems programmatically. • ADO. NET was created for the. NET framework to replace Microsoft’s Active. X Data Objects (ADO) technology. • Namespace System. Data is the root namespace for the ADO. NET API. • Namespace System. Data. Ole. Db contains classes that are designed to work with any data source that supports the OLE DB API, whereas System. Data. Sql. Client contains classes that are optimized to work with Microsoft SQL Server databases. • An object of class Sql. Connection represents a connection to a SQL Server data source. • A Sql. Connection object keeps track of the location of the data source and any settings that specify how the data source is to be accessed. 2008 Pearson Education, Inc. All rights reserved.

55 22. 9 ADO. NET Object Model (Cont. ) • A Sql. Command object represents a SQL command that a DBMS can execute on a database. • A connection that remains active for some length of time to permit multiple data operations is known as a persistent connection. • A Data. Table contains a collection of Data. Rows that represent the table’s data. A Data. Table also has a collection of Data. Columns that describe the columns in a table. • A Data. Set, which consists of a set of Data. Tables and the relationships among them, represents a cache of data—data that a program stores temporarily in local memory. • The structure of a Data. Set mimics the structure of a relational database. • An advantage of using class Data. Set is that it is disconnected—the program does not need a persistent connection to the data source to work with data in a Data. Set. • A Sql. Data. Adapter object connects to a SQL Server data source and executes SQL statements to both populate a Data. Set and update the data source based on the current contents of a Data. Set. 2008 Pearson Education, Inc. All rights reserved.

56 22. 10 Java DB/Apache Derby • As of the Java SE 6 Development Kit (JDK), Sun Microsystems now bundles the open source, pure Java database Java DB (the Sun branded version of Apache Derby) with the JDK. 2008 Pearson Education, Inc. All rights reserved.