Week 1

Day 4

Implementing Your
Logical Model: Physical
Database Design

The previous lesson, Day 3, "Logical Database Design," discussed the elements of a data model while ignoring the implementation of this model in a "real" database. In this lesson, you will examine how a logical data model is actually implemented in an Oracle database. This lesson is about another subset of SQL: Data Definition Language (DDL). DDL consists of SQL statements used to create, modify, and discard database objects.

DDL stands for Data Definition Language which consists of those statements in the SQL language that are responsible for creating or modifying database structures such as tables, views, and indexes.

If you're involved in developing many database applications, you definitely should consider the use of a Windows-based database design tool such as Oracle's Designer/2000 or Database Designer, LogicWorks's ERwin, or Sybase's S-Designer. These products enable you to graphically define a logical model and to generate the correct SQL statements for creating a database.

Even if you're using a database design tool, you should acquire a working knowledge of the Oracle toolset. There are many tools that provide a SQL interface to an Oracle database. Two examples are SQL*Plus and SQL Worksheet, a component of Oracle Enterprise Manager. These tools enable you to enter SQL statements to create tables, indexes, and other database objects. Most of the examples in this lesson use SQL*Plus. Figure 4.1 illustrates how you execute a SQL statement with SQL Worksheet.

Figure 4.1.
Using SQL Worksheet
to execute a SQL
statement.

The other alternative is to use a tool such as the Personal Oracle8 Navigator or Oracle Schema Manager that provides a graphical interface for manipulating an Oracle database. If you're using Personal Oracle8 for Windows 95, take advantage of the Navigator's intuitive interface for creating and modifying tables (see Figure 4.2). Unlike SQL*Plus or SQL Worksheet, the Navigator doesn't require a knowledge of SQL. The Navigator "packages" your inputs and submits them to the Oracle database engine. These tools are ideal for users who aren't familiar with SQL and don't want to learn the language. The Navigator can be used both with Personal Oracle8 and remote Oracle database engines.

Figure 4.2.
Using the Personal
Oracle Navigator to
create and modify tables.

Basics of the CREATE TABLE Statement

Because of its many options and clauses, the SQL statement CREATE TABLE can be rather complex.

Here's a simplified version of its syntax:

CREATE TABLE table_name (
column_name1 datatype [NOT NULL],
...
column_nameN datatype [NOT NULL]);

The variables are defined as follows:

table_name is the name for the table. column_name1 through column_nameN are valid column names. datatype is a valid Oracle datatype specification.

The CREATE TABLE statement can be directly invoked from SQL*Plus or SQL Worksheet. The Personal Oracle8 Navigator or Schema Manager, a component of Oracle Enterprise Manager, package the user's entries and indirectly invoke the CREATE TABLE statement. The examples in this lesson were prepared with SQL*Plus.

Naming a Table

Oracle has several restrictions on table names:

• Each table owned by an Oracle account must have a unique name.
• A table name cannot exceed 30 characters in length.
• A table name must begin with an alphabetic character.

• A table name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _ (underscore).
• A table name cannot be an SQL reserved word. (You can view a complete list of the reserved words in the Oracle8 Server SQL Reference Manual. If you've installed the Oracle documentation on your PC, you'll find the document as C:\Orawin95\Doc\A32538_1.pdf).
• You can use uppercase and lowercase characters in naming tables; Oracle is not case sensitive as long as table or column names are not enclosed in double quotes. The use of double quotes is a syntax that is supported by Oracle. However, you will never have a reason to use double quotes directly. Some third-party tools take advantage of this syntax (such as PowerBuilder), but this is transparent to the developer.
• A table name should be descriptive. Do not use excessive abbreviations when naming tables and other database objects. Many application development tools, such as PowerBuilder or Oracle Forms, provide a point-and-click interface for selecting a table. Because no typing is involved, a long table name is just as easy to select as a short table name.

Naming a Column

Here are some considerations for naming columns:

• Within a single table, a column name must be unique. However, you may use the same column name in different tables.
• Like other database objects, a column name can be up to 30 characters long.
• The column name must begin with an alphabetic character.
• The column name can contain the letters A through Z, the digits 0 through 9, and the characters $, #, and _ (underscore). The name cannot include spaces.
• A column name cannot be an SQL reserved word.

• As with tables, be descriptive in naming a column. Descriptive column names help users understand the definition of each column.
• An Oracle table may have up to 254 columns.

Examples of Creating Tables

This section starts with a simple example. You will construct a CREATE TABLE statement to create the table that contains the departments that exist at Flugle College, as shown in Listing 4.1.

Listing 4.1. Creating the Department table.

SQL> Create table Department
  2  (Department_ID     Varchar2(20) NOT NULL,
  3   Department_Name   Varchar2(25));

Table created.

Some aspects of the statement's syntax deserve special mention:

• Parentheses frame the list of columns.
• In mandatory columns, the phrase NOT NULL follows the column's datatype specification.
• Where applicable, column widths are enclosed in parentheses after the datatype.

As an option, you can explicitly specify that a column is optional by specifying NULL after the datatype specification, as illustrated in Listing 4.2.

Listing 4.2. Creating the Student table.

SQL> create table Student
  2  (Student_ID     Varchar2(20)  NOT NULL,
  3   Last_Name      Varchar2(25)  NOT NULL,
  4   First_Name     Varchar2(25)  NOT NULL,
  5   MI             Varchar2(1)   NULL,
  6   Year           Varchar2(25)  NULL,
  7   Street_Address Varchar2(25)  NULL,
  8   City           Varchar2(25)  NULL,
  9   State          Varchar2(2)   NULL,
 10   Zipcode        Varchar2(9)   NULL,
 11   Telephone      Varchar2(10)  NULL,
 12   Fax            Varchar2(10)  NULL,
 13   Email          Varchar2(100) NULL);

Table created.