cmiller1137 and Ron McFadyen
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),
firstNa