In this chapter we will create the tables for a Library database using DDL. Suppose we require the three tables: Book, Patron, Borrow:
The above diagram (produced from the Relationships Tool) represents the database we wish to create but where we will do so using DDL commands.
We will illustrate three DDL commands (create table, alter table, create index) as we create the library database.
Create the Book table
Create the Patron table with a primary key
Create the Borrow table with a primary key and a foreign key referencing Patron
Alter the Book table so it has a primary key
Alter the Borrow table with a foreign key referencing Book
Add an attribute named gender to Patron
Create an index
DROP TABLE and DROP INDEX
Remove a table or index from the database
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. In the following we will demonstrate and run one command at a time.
Creating the Database
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.
Now consider the following Create Table command which creates a table and establishes an attribute as the primary key:
CREATE TABLE Patron ( patronId Counter PRIMARY KEY, lastName Text(50), firstName Text(50), );
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 Integer, 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 DATETIME.
- 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 YESNO.
- 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.
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);
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);
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);
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);
To remove the above index we need to identify the index by name:
DROP INDEX PatronNameIndex;
To remove a table we use the drop table command.
DROP TABLE Person;
Try running the commands in examples 1 through 3. After running a DDL statement open the corresponding table in Design View and verify that the statement worked as intended.
Try running the commands in examples 4 through 6. After running a DDL statement open the corresponding table in Design View and verify that the statement worked as intended.
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.
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 MS Access.
Consider an ERD from the previous chapter. Write the DDL that could create the required relations.