Creating the INVOICE database

Step 1: Datatypes

It is important that appropriate datatypes are used in the table as changing them at a later date when the table is populated with data can cause problems. This link opposite will give an overview of the basic Oracle datatypes and their use.

Define the datatypes for each field in the tables of the INVOICE database.

`CUSTOMER`	datatype	number
`CustABN`
`CustName`
`CustAddress`
`CustSuburb`
`CustState`
`CustPostcode`

`INVOICE`	datatype	number
`InvoiceNo`
`CustABN`
`InvDate`

`INVOICEITEM`	datatype	number
`LineNo`
`InvoiceNo`
`ItemNo`
`Quantity`

`ITEM`	datatype	number
`ItemNo`
`ItemDesc`
`ItemPrice`

Step 2: `NULL` Values

Apart from primary keys (which are implicitly NOT NULL), all other fields in the table must have an explicit statement declaring whether NULL values are allowed or not. Most fields (name, business address) would require a value that is NOT NULL, while some fields (phone numbers, email addresses) need to allow for NULL values.

Question
What fields in the INVOICE database tables require NOT NULL values or allow NULL values?

Complete the table below indicating which fields should be NOT NULL.

`CUSTOMER`		VALUE
`CustABN`	`VARCHAR2(11)`	`NOT NULL`
`CustName`	`VARCHAR2(35)`	`NOT NULL`
`CustAddress`	`VARCHAR2(32)`	`NOT NULL`
`CustSuburb`	`VARCHAR2(20)`	`NOT NULL`
`CustState`	`VARCHAR2(3)`	`NOT NULL`
`CustPostcode`	`VARCHAR2(4)`	`NOT NULL`

`INVOICE`		VALUE
`InvoiceNO`	`NUMBER`	`NOT NULL`
`CustABN`	`VARCHAR2(11)`	`NOT NULL`
`InvDate`	`DATE`	`NOT NULL`

`INVOICE ITEM`		VALUE
`LineNo`	`NUMBER`	`NOT NULL`
`InvoiceNo`	`NUMBER`	`NOT NULL`
`ItemNo`	`VARCHAR2(9)`	`NOT NULL`
`Quantity`	`NUMBER`	`NOT NULL`

`ITEM`		VALUE
`ItemNo`	`VARCHAR2(9)`	`NOT NULL`
`ItemDesc`	`VARCHAR2(30)`	`NOT NULL`
`ItemPrice`	`NUMBER`	`NOT NULL`

So the SQL statements to create the tables now look like:

Step 3: Primary keys

A primary key is a field or fields that uniquely identify a record in a table and is a constraint that must be declared in the CREATE TABLE statement. For example, the syntax for declaring InvoiceNo as a primary key in the INVOICE tableis:

CONSTRAINT pkInvoice PRIMARY KEY (InvoiceNo)

Constraints should always be given a unique name to identify them should they need to be dropped. In this instance

CONSTRAINT (required keyword) declares the constraint
pkInvoice is the identifying name
PRIMARY KEY (required keyword) declares that it is a primary key constraint
(InvoiceNo) identifies the field that is the primary key

In the event there is more than one field making up the primary key, the syntax is the same except a comma is used to separate the fields. For example, if the fields InvoiceNo and ItemNo were combined to make a primary key in the table INVOICEITEM from the INVOICE database, the declaration would look like this:

CONSTRAINT pkInvoiceItem PRIMARY KEY (InvoiceNo, ItemNo)

Question
How are primary keys declared in the INVOICE database tables?

`TABLE`	SQL Clause
`CUSTOMER`
`INVOICEITEM`
`ITEM`

`TABLE`	SQL Clause
`CUSTOMER`	`CONSTRAINT pkCustomer PRIMARY KEY (CustABN)`
`INVOICEITEM`	`CONSTRAINT pkInvoiceItem PRIMARY KEY (LineNo)`
`ITEM`	`CONSTRAINT pkItem PRIMARY KEY (ItemNo)`

So the SQL statements to create the tables now look like:

Step 4: Foreign Keys

Associations between tables are defined through the use of foreign keys. We use foreign keys to establish referential integrity. This means that we cannot insert a record into the child table unless a related record already exists in the parent table. As with primary keys, foreign keys must be declared as a constraint. The relationship between the tables CUSTOMER and INVOICE use the field CustABN which is the primary key in CUSTOMER and a foreign key in INVOICE. The syntax for declaring a foreign key in the table INVOICE is:

CONSTRAINT fkInvoiceCust FOREIGN KEY (CustABN) REFERENCES CUSTOMER (CustABN)

Where

CONSTRAINT (required keyword) declares the constraint
fkInvoiceCust is the identifying name
FOREIGN KEY (required keyword) declares that it is a foreign key constraint
(CustABN) identifies the field that is the foreign key
REFERENCES CUSTOMER (required keywords) identifies the table the foreign key references
(CustABN)identifies the primary key in the table referenced

Question
How are foreign keys declared in the INVOICE database tables?

`FOREIGN KEY`	SQL Clause
`INVOICEITEM to ITEM`
`INVOICEITEM to INVOICE`

`FOREIGN KEY`	SQL Clause
`INVOICEITEM to ITEM`	`CONSTRAINT fkInvoiceitemItem FOREIGN KEY (ItemNo)REFERENCES ITEM (ItemNo)`
`INVOICEITEM to INVOICE`	`CONSTRAINT fkInvoiceInvoice FOREIGN KEY (InvoiceNo)REFERENCES INVOICE (InvoiceNo)`

So the SQL statements to create the tables now look like:

Step 5: Default Values

Default values are useful in ensuring data integrity. Date fields that load the system date every time a record is entered is one example. The default value is declared after the datatype. For example:

ItemPrice NUMBER DEFAULT '0.00'

Question
What fields in the INVOICE database tables have default values?

`COLUMN`	SQL Clause

`COLUMN`	SQL Clause
`InvDate`	`DEFAULT SYSDATE`
`ItemPrice`	`DEFAULT '0.00'`

So the SQL statements to create the tables now look like:

Step 6: Domain Constraints

We can use CHECK on a field to allow only valid data to be entered into that field. For example, for the field CustState in the table CUSTOMER, we could allow only Australian states and territories to be entered:

CHECK (CustState IN('QLD', 'NSW', 'ACT', 'VIC', 'TAS', 'SA', 'WA', 'NT'))

Question
How does the above CHECK statement fit into the CUSTOMER table?

So the SQL statements to create the tables now look like:

Step 7: Create tables

Order of creation

By using the appropriate SQL commands and applying the CREATE TABLE syntax the final step should not cause any problems. But, the tables must be created in a specific order. The next reading will explain why.

Question
What order are the tables for the INVOICE database created in?

So the SQL statements to create the tables now look like:

database concepts

Using a database : creating tables