Creating tables

Physical modelIn order to extract information from a database, the data must be first stored in a table structure. To create the tables and enter data we use SQL.

This topic covers the physical implementation process of a database and highlights:

  • The Data Definition Language (DDL)
  • Using seven steps for creating a table
  • Manipulating tables and data

Data Definition Language (DDL)

The SQL Data Definition Language (DDL) allows database objects such as tables, indexes or views to be created or destroyed.  The DDL commands we will use for tables are:

  • CREATE TABLE: Used to create the table structure
  • ALTER TABLE: Used to alter the table structure
  • DROP TABLE: Used to destroy the table
Textbook reading
Ch4 pp 192-197

Before looking at how to create a table, we need to understand integrity constraints. Integrity constraints protect a database from becoming incomplete, inaccurate or inconsistent. The next reading from the textbook provides an introduction to integrity constraints. The paragraph on action assertions in this reading is not relevant.

Textbook reading
Ch6 pp 286-289

It is important that we understand the steps involved in preparing a table that is to be created. The next reading covers the seven steps to follow when creating tables as well as the general syntax of the CREATE TABLE statement. This reading will prepare you for most of the work you will do in this topic.

Have a go: the seven steps

Using the seven steps

The first step in creating a table is to identify appropriate datatypes, precision and length for all of the fields of the table. Oracle has a number of datatypes available to us. Selecting the appropriate datatype helps to ensure data integrity (allowing only valid data) in the database.

Once the datatypes for each field in the table have been identified, the rest of the steps can then be taken to create the table. These can be summarised as:

  • Constraints
    • NULL values (step two)
    • Primary keys (step three)
    • Foreign keys and referential integrity (step four)
    • Default values (step five)
  • Checks
    • Domain constraints (step six)

Step seven, the final step in the process, is creating the table(s).

The next activity implements the seven steps to build the INVOICE database you have been using.

Have a go: creating the INVOICE database

 

Manipulating tables and data

Altering tables
Planning and thought in the database design process should avoid the need to alter datatypes once the tables have been created. If datatypes are changed after table creation, problems will often arise with data integrity.

Textbook reading
Ch6 pp 290-291

On the other hand, occasions may arise when integrity constraints may be required to be added or dropped after the table has been created. The next reading from the text Changing Table Definitions and Removing Tables provides the syntax for altering tables.

Inserting, updating and deleting data
 Having created the table(s) they need to be populated with data and once populated the data may need to be modified. The next reading covers the commands:

  • INSERT INTO
  • DELETE FROM using WHERE
  • UPDATE
Textbook reading
Ch6 pp 291-293

The next reading addresses this problem: Inserting, updating and deleting data.

Destroying tables
 During the life of a database, tables may need to be removed from the database (this is especially so during the development and testing stage when problems arise).

Lecturer reading
Dropping tables

Before a table can be dropped, all the data in that table must be deleted first. This begs the question, what about referential integrity? Read the short section on dropping tables from the database.

Have a go: Deleting the INVOICE database

 

Getting your hands dirty


INVOICE databaseInvoice database

Log on to Oracle and try these queries on the Invoice database.
This exercise is particularly relevant to the first assignment.

1. Drop all of the tables from the INVOICE database.

2. Recreate all of the tables. Remember the order of creation.

3. What happens if the tables are created out of order? Try it and see. View the answer

If you try and create a child table before the parent you will get an error message saying parent table is unknown. So for example, INVOICEITEM has two parents (INVOICE and ITEM) and you can’t create INVOICEITEM before INVOICE and ITEM are created.

4. Populate the tables.
    Either select the information from your lecturers account using:
         insert into item
         select * from jtutty.item

5. Add yourself as a customer

6. Create an invoice for a customer that doesn't exist in the CUSTOMER table. What happens?View the answer

If you have set up your tables correctly, you will not be allowed to add an invoice for a non-existent customer. This breaches the referential constraint that every foreign key (CustABN in the INVOICE table) must have value in the primary table (CustABN in the CUSTOMER table)

7. Delete the customer Mellow Yellow Lighting. What happens?
View the answer

Again if you have set up your tables correctly you will not be allowed to. Once again you cannot deleting a parent record (Mellow Yellow) while related records exist in the child table (invoices for Mellow Yellow in the INVOICE table)

Try these

These activities relate to the PowerPoint Presentation “Create Tables” refer to “get your hands dirty”

1. Delete 'Genna Bear' from Customer View the answer

DELETE
FROM customer
WHERE custname=‘Genna Bear’

2. Add the following invoice line

LineNo InvoiceNo ItemNo Quantity
1000 100 8 7

View the answer

INSERT INTO InvoiceItem (lineno, invoiceno, itemno, quantity)
VALUES (1000, 100, 8, 7)