Using DDL to Change the Database Design

From Apache OpenOffice Wiki
Jump to: navigation, search

To show the usage of statements for data definition purposes, we will show how to create the tables in our example database using CREATE statements. The first table, SALESMAN, contains essential information about the salespersons, including the first name, last name, street address, city, and birth date. The table SALESMAN that is described in more detail later, is shown here:

1 0 0 0 0 95460 02/07/46
2 0 0 0 0 95460 12/24/63
3 0 0 0 0 95460 04/01/72
4 0 0 0 0 95460 02/13/53
5 0 0 0 0 95460 09/07/49

The first column is the column SNR of SQL type INTEGER. This column contains a unique number for each salesperson. Since there is a different SNR for each person, the SNR column can be used to uniquely identify a particular salesman,the is, the primary key. If this were not the case, an additional column that is unique would have to be introduced, such as the social security number. The column for the first name is FIRSTNAME that holds values of the SQL type VARCHAR with a maximum length of 50 characters. The third column, LASTNAME, is also a VARCHAR with a maximum length of 100 characters. The STREET and STATE columns are VARCHARs with 50 characters. The column ZIP uses INTEGER and the column BIRTHDATE uses the type DATE. By using the type DATE instead of VARCHAR,the dates of birth can be compared with the current date.

The second table, CUSTOMER, in our database, contains information about customers:

100 0 0 0 0 95199
101 0 0 0 0 95460
102 0 0 0 0 93966

The first column is the personal number COS_NR of our customer. This column is used to uniquely identify the customers, and declare this column to be the primary key. The types of the other columns are identical to the first table, SALESMAN.

Another table to show joins is required. For this purpose, the table SALES is used. This table contains all sales that our salespersons could enter into an agreement with the customers. This table needs a column SALENR to identify each sale, a column for COS_NR to identify the customer and a column SNR for the sales person who made the sale, and the columns that defines the article sold.

1 100 1 0 02/12/01 $39.99
2 101 2 0 10/18/01 $15.78
3 102 4 Orange juice 08/09/01 $1.50

To show the relationship between the three tables, consider the diagram below.

The table SALES contains the column COS_NR and the column SNR. These two columns can be used in SELECT statements to get data based on the information in this table, for example, all sales made by the salesperson Jane. The column COS_NR is the primary key in the table CUSTOMER and it uniquely identifies each of the customers. The same is true for the column SNR in the table SALESMAN. In the table SALES, the fields COS_NR and SNR are foreign keys. Note that each COS_NR and SNR number may appear more than once in the SALES table, because a third column SALENR was introduced. This is required for a primary key. An example of how to use primary and foreign keys in a SELECT statement is provided later.

The following CREATE TABLE statement creates the table SALESMAN. The entries within the outer pair of parentheses consist of the name of a column followed by a space and the SQL type to be stored in that column. A comma separates the column entries where each entry consists of a column name and SQL type. The type VARCHAR is created with a maximum length, so it takes a parameter indicating the maximum length. The parameter must be in parentheses following the type. The SQL statement shown here specifies that the name in column FIRSTNAME may be up to 50 characters long:

Documentation note.png This code does not end with a DBMS statement terminator that can vary from DBMS to DBMS. For example, Oracle uses a semicolon (;) to indicate the end of a statement, and Sybase uses the word go. The driver you are using automatically supplies the appropriate statement terminator, so that you will not need to include it in your SDBC code.

In the CREATE TABLE statement above, key words are printed in capital letters, and each item is on a separate line. SQL does not require the use of these conventions, it makes the statements easier to read. The standard in SQL is that keywords are not case sensitive, therefore, the following SELECT statement can be written in various ways:

 SELECT "FirstName", "LastName"
 FROM "Employees"
 WHERE "LastName" LIKE 'Washington'

is equivalent to

 select "FirstName", LastName" from "Employees" where
 "LastName" like 'Washington'

Single quotes '...' denote a string literal, double quotes mark case sensitive identifiers in many SQL databases.

Requirements can vary from one DBMS to another for identifier names. For example, some DBMSs require that column and table names must be given exactly as they were created in the CREATE TABLE statement, while others do not. We use uppercase letters for identifiers such as SALESMAN, CUSTOMERS and SALES. Another way would be to ask the XDatabaseMetaData interface if the method storesMixedCaseQuotedIdentifiers() returns true, and to use the string that the method getIdentifierQuoteString() returns.

The data types used in our CREATE TABLE statement are the generic SQL types (also called SDBC types) that are defined in the DBMSs generally uses these standard types.

To issue the commands above against our database, use the connection con to create a statement and the method executeUpdate() at its interface In the following code fragment, executeUpdate() is supplied with the SQL statement from the SALESMAN example above:

 XStatement xStatement = con.createStatement();
 int n = xStatement.executeUpdate("CREATE TABLE SALESMAN " +
     "FIRSTNAME VARCHAR(50), " +
     "LASTNAME VARCHAR(100), " +
     "STREET VARCHAR(50), " +
     "STATE VARCHAR(50), " +
     "ZIP INTEGER, " +
     "PRIMARY KEY(SNR) " +

The method executeUpdate() is used because the SQL statement contained in createTableSalesman is a DDL (data definition language) statement. Statements that create a table, alter a table, or drop a table are all examples of DDL statements, and are executed using the method executeUpdate().

When the method executeUpdate() is used to execute a DDL statement, such as CREATE TABLE, it returns zero. Consequently, in the code fragment above that executes the DDL statement used to create the table SALESMAN , n is assigned a value of 0.

Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages