9.2.2: Creating the Database

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), titleText(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
    • (
    • patronIdCounterPRIMARY KEY, lastNameText(50),
    • firstNameText(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.

Example 3

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

• • • CREATE TABLE Borrow
    • (
    • patronIdInteger,
    • callNoText(50),
    • dateDueDATETIME,
    • returnedYESNO,
    • 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 a book has been returned or not, 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.

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, 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;

Example 9

To remove a table we use the drop table command.

• • • DROP TABLE Person;

Exercises

1) 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.

 

2) Example 7 creates an index. Run this command in your database and then verify the index has been created. You can view index information: put the table in Design View and then click the Indexes icon:

 

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