Logon to HTML DB At

Part 1, Step 1

Building Tables and Adding Constraints

Building Tables

Logon to HTML DB at

Enter your username and password in the login textboxes.

In the Welcome to HTML DB window, select SQL Workshop

In the SQL Workshop window, select Create Object

In the Create Database Object window, select the Table radio button.

Select Next to continue.

In the Select Creation Method window, select the Create Table: From Scratch radio button.

Select Next to continue.

Create the CUSTOMERS table first.

Create this table by entering the following data into the Table Name, Column Name, Type and Precision textboxes under the Table Definition window.

Select Next to continue.

In the Define Primary Key window, verify that the Primary Key shown in the drop-down list is CUSTOMER_ID(NUMBER).

Select the Primary Key Population radio button - Generated from a new sequence.

In the Sequence Nametextbox enter CUSTOMERS_SEQ

Select Next to continue.
In the Confirmation window, select the Action radio button next to Create table now.

Select Finish to continue
In the Create Table Results window, select Browse Table.

The newly created table should look like the sample below.

DO NOT MAKE ANY OTHER CHANGES TO THIS TABLE AT THIS TIME.

Create the ORDERS table.

Repeat Step 3 through Step 10 using the column names belowto create the ORDERS table.

ORDERS

primary key = ORDER_ID

sequence name = ORDERS_SEQ

Create the SUBJECTS table.

Repeat Step 3 through Step 10 using the column names belowto create the SUBJECTS table.

SUBJECTS

primary key = SUBJECT_ID

sequence name = SUBJECTS_SEQ

Create the PUBLISHERS table

Repeat Step 3 through Step 10 using the column names belowto create the PUBLISHERS table.

PUBLISHERS

primary key = PUBLISHER_ID

sequence name = PUBLISHERS_SEQ

Create the ITEM_TYPES table

Repeat Step 3 through Step 10 using the column names belowto create the ITEM_TYPES table.

ITEM_TYPES

primary key = ITEM_TYPE_ID

sequence name = ITEMS_TYPES_SEQ

Create the ITEMS table

Repeat Step 3 through Step 10 using the column names belowto create the ITEMS table.

ITEMS

primary key = ITEM_ID

sequence name = ITEMS_SEQ

NOTES:

Six tables have been created: CUSTOMERS, ORDERS, SUBJECTS, PUBLISHERS, ITEM_TYPES and ITEMS.
The next steps will add constraints to the ORDERS and ITEMS tables.

Add Foreign Key (FK) constraints to the newly created tables.

Return to SQL Workshop (click the SQL Workshop tab).

Select the Table icon in the Data Browser window.

In the Database Objects Results window, under the Name column, select the magnifying glass next to ORDERS.

In the Tasks box on the right, select Manage Table.

In the Identify Table Action window, select the Add Constraint radio button.

Select Next to continue.

Complete the form as shown below. Enter the following;

Constraint Name: ORDERS_CUSTOMER_ID_FK

Constraint Type: Foreign Key

Foreign Key Column(s): CUSTOMER_ID

Reference Table Name: CUSTOMERS

Reference Table Column List: CUSTOMER_ID

When complete, select Next to continue.

Select Finish button in the Confirmation window.

You should get this:

Begin again at Step 16: Add the second constraint to the ORDERS table.

Use the following information at Step 20:

Constraint Name: ORDERS_ITEM_ID_FK

Constraint Type: Foreign Key

Foreign Key Column(s): ITEM_ID

Reference Table Name: ITEMS

Reference Table Column List: ITEM_ID

Add constraints to the ITEMS table.

Begin again at Step 16: The following step will add a foreign key constraint to the ITEMS table. At Step 17, select the magnifying glass next to ITEMS. Follow the format shown in Step 20 to add the PUBLISHERS foreign key to the ITEMS table.

Create the foreign key to the PUBLISHERS table

Constraint Name: ITEMS_PUBLISHERS_ID_FK

Constraint Type: Foreign Key

Foreign Key Column(s): PUBLISHER_ID

Reference Table Name: PUBLISHERS

Reference Table Column List: PUBLISHER_ID

Create the foreign key to the SUBJECTS table

Constraint Name: ITEMS_SUBJECTS_ID_FK

Constraint Type: Foreign Key

Foreign Key Column(s): SUBJECT_ID

Reference Table Name: SUBJECTS

Reference Table Column List: SUBJECT_ID

Create the foreign key to the ITEM_TYPES table

Constraint Name: ITEMS_ITEM_TYPE_ID_FK

Constraint Type: Foreign Key

Foreign Key Column(s): ITEM_TYPE_ID

Reference Table Name: ITEM_TYPES

Reference Table Column List: ITEM_TYPE_ID

To confirm the constraints for the Amazing Books tables, select the TABLES icon from the SQL Workshop main window.

Select a table name by clicking on the magnifying glass icon.

In the Table Summary page drop down menu for Constraints, Foreign Keys will show all constraints for this table.

For example, the ITEMS Table Summary is:

Add NOT NULL (NN) constraints to the newly created tables.

Return to SQL Workshop (click the SQL Workshop tab).

Select the Table icon in the Data Browser section.

In the Database Objects Results window, under the Name column, select the magnifying glass next to CUSTOMERS.

In the Tasks box on the right, select Manage Table. In the Identify Table Action window, select the Modify Column radio button. Select Next to continue. (Remember, NOT NULL CONSTRAINTS have to be modified at the column level)

Click the “+” sign below to see the existing columns and their datatypes.

OracleAcademy1

Part 1, Step 1

Building Tables and Adding Constraints

According to your ERD, only email and state_province are “optional” (nullable). Nulls should be “N” (for “no) for all other columns. Because customer_id is a PK column, it automatically became a “Not Null” column. Using the list that shows when you hit the “+” sign, change the customer_name to “NOT NULL” (if not that by default). DO NOT HIT “NEXT”.

You must also then change the DATATYPE and LENGTH to what you have listed under the Existing Columns. This seems redundant, but you are modifying a column. Now hit NEXT.

Confirm your request. Select the FINISH button.

Click the “Browse Table” link or icon.

All columns except customer_id and customer_name should be nullable at this point.

Click “Manage Table” in the right-hand menu. Repeat for all other mandatory attributes in the CUSTOMERS table on your ERD. These will become “Not Null” columns.

Click the “BROWSE” tab at the top, and repeat steps 1-11 for all other tables, using your ERD. Remember, all Primary Keys will not be nullable by default. Be sure to match the TYPE and LENGTH with what is in the table already. All you want to change is the “NOT NULL” to make a “NOT NULL CONSTRAINT”.

OracleAcademy1