9

Many of the tools available for constructing entity relational diagrams (ERDs) are capable of generating data definition language (DDL) commands that are used for creating tables, indexes, and relationships. You can find many references easily to DDL. For instance, if you are interested try http://en.wikipedia.org/wiki/Data_Definition_Language, or enter the phrase Data Definition Language in your favorite search engine.

9.1 Running DDL In Microsoft Access

Most database systems provide a way for you to run data definition language commands. When such facility exists, it can be relatively easy to create and re-create databases from a file of DDL commands. One way to run DDL commands in Microsoft Access is through a query that is in SQL View. To run a DDL command, we follow these two steps:

• • Open a database and choose to create a query, and then instead of adding tables to your query, you just close the Show Table window:

Figure 9.1 Close the Show Table window with no tables selected

• • Then, choose SQL View and you will be able to type a DDL command or paste one in :

Figure 9.2 Choose SQL view for the query

9.2 Example

In this chapter, we will creating tables and modifying tables for a new Library-DDL database using DDL. In Access, create a new blank database named Library-DDL database to apply your DDL skills. Suppose we require the three tables: Book, Patron, Borrow:

Figure 9.3 Sample database to create

The above diagram (produced from the Relationships Tool) represents the database we wish to create but where we will do so using DDL commands.

9.2.1 DDL Commands

We will illustrate three DDL commands (create table, alter table, create index) as we create tables and modify tables using the Library database.

Figure 9.4 Data Definition Commands

In some database environments, we can run more than one command at a time. The commands would be located in a file and would be submitted as a batch to be executed.

Before applying these DDL commands, verify that you have created a new blank Access database named Library-DDL. In the following, we will demonstrate SQL syntax commands supporting Microsoft Access and run one command at a time.

9.2.2 Creating and Modifying  Database Tables

Example 1

Consider the following create table command which is used to create a table named Book. The table has two fields:  callNo and title.

CREATE TABLE Book
(
callNo Text(50),
title Text(100)
)
;

The command begins with the keywords CREATE TABLE. It’s usual for keywords in DDL to be written in upper case, but it’s not required to do so. The command is just text that is parsed and executed by a command processor. If humans are expected to read the DDL then the command is typically written on several lines as shown, one part per line.

Example 2

Now consider the following CREATE TABLE command which creates a table and establishes an attribute as the primary key:

CREATE TABLE Patron
(
PatronID Number NOT NULL PRIMARY KEY,
lastName Text(50),
firstName Text(50)
);

Example 3

The primary key of Patron is the patronId field. Notice the data type is shown as Counter. After running this command you will be able to see that the Counter data type is transformed to AutoNumber.

Our last example of the create table command is one that creates a table, sets its primary key and also creates a foreign key reference to another table:

CREATE TABLE Borrow
(
patronId Number,
callNo Text(50),
dateDue DATETIME,
returned YESNO,
PRIMARY KEY (patronId, callNo, dateDue), FOREIGN KEY (patronId) REFERENCES Patron
)
;

There are several things to notice in the above command:

• The primary key is composite and so it is defined in a separate PRIMARY KEY clause.
• The data type of patron id must match the data type used in the Patron table and so the data type is defined as Integer.
• The dateDue field will hold a due date and so its data type is defined as DATE/TIME.
• The returned field will hold a value to indicate whether or not a book has been returned and so its data type is defined as YES/NO.
• A row in the Borrow table must refer to an existing row in Patron and so we establish a relationship between Borrow and Patron using the FOREIGN KEY clause. After running this create table command you can see the relationship in Access by opening the Relationships Tool.

Example 4

The Book table was created previously, but there is no specification for a primary key. To add a primary key, we use the ALTER TABLE command as shown below.

ALTER TABLE Book
ADD PRIMARY KEY (callNo)
;

Example 5

Now that Book has a primary key, we can define the relationship that should exist between Borrow and Book. To do so, we use the ALTER TABLE command again:

ALTER TABLE Borrow
ADD FOREIGN KEY (callNo)
REFERENCES Book (callNo)
;

Example 6

Notice that the Patron table does not have a gender attribute. To add this later on, we can use the ALTER TABLE command:

ALTER TABLE Patron ADD
COLUMN gender Text(6)
;

Example 7

For performance reasons, we can add indexes to a table. DDL provides CREATE INDEX and DROP INDEX commands for managing these structures. To create an index for Patron on the combination last name and first name, we can execute:

CREATE INDEX PatronNameIndex ON Patron (LastName, FirstName);

Example 8

To remove the above index, we need to identify the index by name:

DROP INDEX PatronNameIndex ON Patron;

Example 9

To remove a table, we use the DROP TABLE command.

DROP TABLE Person;

DDL Exercises

Complete the following exercises using the Library database.

1. Try running the commands in Examples 1 through 3. After running each DDL statement, open the corresponding table in Design View and verify that the statement worked as intended.
2. Try running the commands in Examples 4 through 6. After running each DDL statement, open the corresponding table in Design View and verify that the statement worked as intended.
3. The effect of executing the commands in the first 6 examples can be accomplished by 3 create table commands. Example 9 shows a DROP TABLE command; use similar DROP commands to delete all the tables you created in Exercises 1 and 2. Now, write 3 create table commands that have the same effect as Examples 1 through 6. After running the DDL statements, open the Relationships Tool to verify your commands created the 3 tables and the 2 relationships.
4. Example 7 creates an index. Run this command in your database and then verify the index has been created. You can view index information by clicking the Indexes icon:

Notice that the (primary) index has a name that was generated by Microsoft Access.

5. Consider an ERD from the previous chapter. Write the DDL that could create the required relations.