Structured Query Language: Data Definition and Data Manipulation

The previous lecture introduced structured query language (SQL), and presented an important part of data manipulation language (DML), namely SELECT query. This lecture presents how to create database using SQL commands.

In examples presented in this lecture, for your convenience, SQL commands are written in capital letters, so you can clearly recognize them. In general, SQL is not case sensitive. The presented examples are based on Microsoft Access. If you are using different RDBMS, small changes in data types or commands may be needed. For example, some database systems may not support specific data types or commands.

Presentations

Lecture on SQL Data Definition (SWF file).

Creating, Modifying, and Deleting Tables

SQL provides commands that can be used to create and manage databases. The most important operations are to create and modify tables. To create a table in SQL CREATE TABLE command is used. In its simplest form, the command’s syntax is:

CREATE TABLE <table name> ( <list of fields, their types and parameters> )

For example, the command:

CREATE TABLE patient
(
RecordNumber Text(10) PRIMARY KEY,
FirstName Text(30),
LastName Text(40),
SSN Text(9)
);

produces table “patient” consisting of four text fields, and sets RecordNumber as the primary key. Design view of the table after executing the command in Access is presented in Figure 1.

Figure 1: Patient table design view.

Similarly, the command:

CREATE TABLE diagnosis

(

ID INTEGER PRIMARY KEY,

PatientRecordNumber Text(10) NOT NULL,

Code INTEGER,

DiagnosisDate Date,

CONSTRAINT fk1 FOREIGN KEY (PatientRecordNumber) REFERENCES patient (RecordNumber)

);

Creates table diagnosis with four fields (ID, PatientRecordNumber, Code, and DiagnosisDate). The field ID is set as the primary key (Figure 2).

Figure 2: Design view of table diagnosis.

Additionally, the field PatientRecordNumber is set to be a foreign key linked to the primary key in table patient. Thus, the one-to-many relationship between patient and diagnosis is created as illustrated in relationships view in Figure 3.

Figure 3: Relationship between patient and diagnosis created using SQL.

Suppose now, that when creating table diagnosis the database designer forgot to add a memo field with comments. To modify existing tables, ALTER TABLE command can be used. Its syntax is following:

ALTER TABLE <table name> ADD | DROP | ALTER COLUMN <filed name> <field type>

For example, to add text field Comment to the table diagnosis the following command can be used.

ALTER TABLE diagnosis

ADD COLUMN Comment Text;

The result is illustrated in Figure 4.

Figure 4: Design view of updated diagnosis table with added Comment field.

In order to change type or size of a field, also ALTER TABLE command is used. To change type of the field Comment from Text to Memo, the following command is used. Its result is in Figure 5.

ALTER TABLE diagnosis

ALTER COLUMN Comment Memo;

Figure 5: Design view of updated diagnosis table with updated type of Comment.

Note that when modifying types or sizes of fields of tables some data may be lost. Sometimes, when format of data needs to be changed, or more advanced conversions made this process needs to be done in multiple steps. First, new column with correct data type is created. Then, data is copied and converted from one column to another. Finally, old column is removed, and new column is renamed to match old name (if needed). This process will be described more in the next lecture.

Similarly, in order to drop column from a table also ALTER TABLE command is used. For example, to remove DiagnosisDate from diagnosis the command below can be used.

ALTER TABLE diagnosis

DROP COLUMN DiagnosisDate;

To delete entire table, use command DROP TABLE <table name>. For example,

DROP TABLE diagnosis;

removes the diagnosis table from database.

Creating Indexes

In order to speed up search in databases indexes are used. An index is something that keeps ordered set of values for a specific field. Imagine a phonebook in which all numbers are not listed in any specific order (i.e. in the order the customers were connected to the network). To find a specific person in such a phonebook, one needs to look at all entries one by one, until that person is found. In the worst case, that means looking at all people in that phonebook. When names are ordered, the process of finding specific person is much faster. This is what indexes do. They provide a database system with information how to order values in a specific field.

To create an index, use CREATE INDEX command in SQL. For example, to create index of last names in ascending order in the table patient, use the command:

CREATE INDEX PatientLastNameIndex ON patient(LastName ASC);

Similarly, to remove index, use DROP INDEX <index name> command.

Be careful when creating indexes. They speed up search operations, but significantly slow down updates in your database. Create an index only if you know you will be using it frequently. For example, do not create index for diagnosis date, if you know that the database will be rarely used to find diagnoses made during a specific day.

Inserting data

Now, as you already know how to create your database, you can start filling it with data. To insert data into your database use INSTERT INTO <target> VALUES (<list of values>) command. For example, the following command inserts one record into the patient table with values for record number, first name, and last name.

INSERT INTO patient(RecordNumber, FirstName, LastName)

VALUES ( 'AA-123', 'John', 'Smith' );

Note that the command did not list all fields in the patient table leaving the value of patient’s social security number NULL as illustrated in Figure 6.

Figure 6: One record inserted into the patient table.

Similarly, one can insert multiple records resulting from a query at the same time by combining INSERT into and SELECT statements. For example, assuming that there exists table old_patients, the following statements inters names and record numbers from old_patients into patient table.

INSERT INTO patient(RecordNumber, FirstName, LastName)

SELECT Pat_RN, FN, LN FROM old_patients;

To Read

Reach chapters 4 and 5 from SQL for dummies.

Create Your Database in SQL

Using SQL write and execute queries to do the following:

1. Create database described in the lecture.

- Create tables patient, physician, diagnosis and relationships between them.

- Create Appointment table with appointment date, physician, and patient fields. Add field “Visit” which takes value “Y” when patient cane for the scheduled visit, and “N” otherwise.

- Insert at least three records into each table.

2. Create all tables and relationships for your design of Mental Health Court Database project, or your final project.

Please submit SQL queries (text), and screenshots of design view of tables and relationships in Access.