Copyright

Practical Application for Database Programming: Database Creation in Oracle Database Express

Instructor: Ee Teik OH
In this lesson, you will be given the opportunity to work with an Oracle Express database and practice your skills in creating database objects and working with the objects to build a mini database.

Oracle databases and database objects

The is a practical learning session where Oracle Express 11g database will be used as the test database platform for creating and manipulating database objects. A GUI based tool like SQL Developer or PL/SQL Developer is recommend for use in this exercise if available - this significantly simplifies the task of entering and testing the sample commands given. The Oracle command line tool SQLPLUS can also be used.

Prerequisites

  • Some exposure to Oracle or compatible SQL database system
  • Basic understanding of relational database systems/architecture and database objects
  • Basic understanding of SQL commands, especially the DML ( Data Manipulation Language ) commands

Topics covered in this lesson

  • Creating Tables - tables are the basic 'containers' for data in a database, and are the main components in any database
  • Creating Keys & Indexes - keys uniquely identify records and both keys/indexes are used to speed up data retrieval
  • Creating Rules & Constraints - rules and constraints are features available to help preserve data integrity and validity
  • Putting it all together - integrating everything together to build a 'mini' database

Test Data to be used for this lesson

The tables shown in Figure 01 below will be used throughout this lesson. The five tables shown can actually form a 'mini' database to manage a customer order management system for a small organization. This lesson will provide guided step-by-step instructions, along with exercises, to enable the learner to build the 'mini' database from scratch. It is recommended that a new database test user be created for this exercise and sufficient privileges be assigned to the user to be able to create databases and other related objects. Make sure that all the sample statements provided as well as those which you are requested to write, are properly tested and executed successfully. The final part of this exercise is dependent up on all these being executed successfully, otherwise you may get unexpected results.


Figure01 - tables to be used for this lesson
Diagram01


Exercises

For any new database system, the first task would obviously be to design and build the database. This step is not covered here as creating databases would need privileges not normally be granted to students. Thus, we would be using an existing default database and begin with the creation of tables.

Before database tables are created, it is normal to put some effort to plan and design them carefully. Instead of just being a container for data, the design of tables should also take into consideration the rules, constraints and their relationships to other objects within the database. The table creation process can include the creation of rules, constraints and relationships concurrently. The creation of these items could also be executed later independent of the table creation process - however this involves making changes to tables already populated with live data and may pose challenges.

The command to create a new table in Oracle is as given below. Use this command to create the Customer table.

CREATE TABLE Customer
(
  CustomerID nchar(5) NOT NULL,
  CustomerName nchar(30) NOT NULL,
  ContactPerson nchar(30),
  ContactNo nchar(12),
  Address nchar(50),
  CreditLimit numeric(8,2),
  CONSTRAINT Customer_PK PRIMARY KEY (CustomerID)
);

Notice that in the SQL statement, a CONSTRAINT named Customer_PK, which is a primary key constraint, is created along with the table. This defines that the CustomerID field is to be used as the primary key for the table. Other constraints could also be created along with the table creation process.

Question 1

Next, using the sample SQL statement provided earlier, write the SQL statements to create the other four tables. There are no constraints required yet to be defined for these tables. As mentioned earlier, it would be best that all known constraints be created along with the tables. However, a different approach will be adopted here since this is a learning exercise.

Primary keys uniquely identify records - the earlier example shows how a primary key can be defined during the table creation process. Primary keys can also be created after the table is created. The SQL command to accomplish this is as shown below. Here, the field ItemID is made the primary key for the table Item. Make sure that you execute this command and ensure that it executes correctly.

ALTER TABLE Item ADD CONSTRAINT Item_PK PRIMARY KEY(ItemID);

Question 2

Add the following primary keys to the other three tables. SONumber is the primary key for SOHeader, the combination of the SONumber and SOLineItem fields the primary key for SODetail and finally, the combination of SONumber, SOLineItem and DeliveryDate for the SODelivery.

Foreign keys are field(s) in one table which refers to a primary key in another table. In this example, the CustomerID field in the SOHeader table is a foreign key as it refers to the primary key CustomerID in the Customer table. It is not possible for the foreign key value to exist if the same value is not present as a primary key value in the main table. Two alternative SQL statements are shown below where the CustomerID field in SOHeader table is made a foreign key when it references the CustomerID primary key in the Customer table. The first statement explicitly names the foreign key constraint as SOHeader_FK01 while the second specifies no name for the foreign key - this will be assigned automatically by Oracle. It is better to name the foreign key, because when there is a requirement to drop this key (shown as the third statement), the name can be used directly. Otherwise, the name has to be retrieved from an Oracle SQL system table. Run the statement below to create the foreign key for the SOHeader table and ensure that it executes correctly.

ALTER TABLE SOHeader ADD CONSTRAINT SOHeader_FK01 FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID);

OR

ALTER TABLE SOHeader ADD FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID);

ALTER TABLE SOHeader DROP CONSTRAINT SOHeader_FK01; (dropping the foreign key SOHeader_FK01 from SOHeader)

Question 3

Repeat the process to create the foreign keys for the other two tables. The SODetail table should have two foreign keys while the SODelivery table should have one. You should be able to decide what are the foreign keys based on the information provided. Then drop the foreign key in the SODelivery and re-create it as an exercise.

Indexes

Indexes are common mechanisms used to speed up the data retrieval process. Indexes will be maintained (in separate tables from the data tables) for common field(s) which are used frequently to retrieve data (like the index in books). Indexes can be created and dropped using the statements below. Note that it is possible to create multiple indexes for the same table.

CREATE INDEX Customer_IDX01 ON Customer(ContactPerson);

DROP INDEX Customer_IDX01;

Question 4

It has been decided that the ContactNo field is commonly used to search for customer records and is to be used as an index in the Customer table. Write an SQL statement to accomplish this task.

Other CONSTRAINTS

There are several other types of constraints that can be created in Oracle - among them are CHECK constraint and NOT NULL constraint. The CHECK constraint will help to validate data and ensure that only valid values are provided, thus helping to enforce data integrity. For example, it is clear that the price of all items must be greater than 0.00. The SQL statement below can be used to create this constraint. Test out this statement and ensure that the constraint is created in the table.

ALTER TABLE Item ADD CONSTRAINT Item_PriceCheck CHECK ( ItemPrice > 0.00 );

Similarly, the NOT NULL constraint can be added using the below statement.

ALTER TABLE Item MODIFY ( ItemDesc CONSTRAINT ItemDesc_NotNull NOT NULL);

The SQL statement to specify the NOT NULL constraint can also be written as :

ALTER TABLE Item MODIFY ItemDesc NOT NULL;

Notice the constraint name is not specified here. Oracle will automatically assign it.

Question 5

Write Oracle SQL statements to accomplish the following, run them and ensure that they execute correctly.

  • The QtyOrdered field in SODetail table must only have values greater than 0.00
  • The ItemUOM field in Item table can have one of the following valid values 'kg', 'pcs', 'litre'
  • The ContactPerson field in the Customer cannot accept a null value

After completing the exercise in Question 5, you should now have a working model of the 'mini' database. Make sure that you have executed all the statements mentioned (as well as those you are requested to create) as the next part of the exercise will depend on all these actions being executed successfully. You will now populate the tables with test data and test out the tables you have created. A test script to fill the tables with initial data is provided at the end of this lesson.

Question 6

Test out the following Oracle SQL statements provided below, observe the results obtained and try to explain why the outcome was achieved.

  • INSERT INTO SOHeader ( SONumber, CustomerID, SODate, SOStatus ) VALUES ( 'S0004', 'C0002', '14-JAN-2018', 'O' );
  • INSERT INTO SOHeader ( SONumber, CustomerID, SODate, SOStatus ) VALUES ( 'S0005, 'C0007', '14-JAN-2018', 'O' );
  • INSERT INTO SODetail ( SONumber, SOLineItem, ItemID ) VALUES ( 'S0001', '03', 'I0001' );
  • INSERT INTO SODetail ( SONumber, SOLineItem, ItemID, QtyOrdered ) VALUES ( 'S0001', '04', 'I0001', -100.00 );

Lesson Summary

In this lesson, you have been guided through the process of creating a simple database and establish relationships between tables. Common database objects such as tables, primary and foreign keys, indexes and constraints have been discussed and you have been given the opportunity to work with them. Finally, you were given some test data to populate the database and to test the constraints and links you established earlier.

===============================================================================

Script to populate tables with test data

INSERT INTO Customer ( CustomerID, CustomerName, ContactPerson, ContactNo, Address, CreditLimit ) VALUES ( 'C0001', 'First Company', 'Mr. One', '00-408-306-2', '12, Green Drive, Road Two, 90313 CA, USA', 450000 );

INSERT INTO Customer ( CustomerID, CustomerName, ContactPerson, ContactNo, Address, CreditLimit ) VALUES ( 'C0002', 'Second Company', 'Mr. Two', '00-651-763-1', 'Building East Wing, Suite 4-2 East, 10313, NY, USA', 500000 );

INSERT INTO Customer ( CustomerID, CustomerName, ContactPerson, ContactNo, Address, CreditLimit ) VALUES ( 'C0003', 'Third Company', 'Mr. Three', '00-407-519-5', '110 West View Boulevard, 9310 CA, USA', 600000 );

INSERT INTO Item ( ItemID, ItemDesc, ItemUOM, ItemPrice, StockQty ) VALUES ( 'I0001', 'Item Number 01', 'pcs', 15.20, 2000 );

INSERT INTO Item ( ItemID, ItemDesc, ItemUOM, ItemPrice, StockQty ) VALUES ( 'I0002', 'Item Number 02', 'kg', 1500.00, 125 );

To unlock this lesson you must be a Study.com Member.
Create your account

Register for a free trial

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Free 5-day trial

Earning College Credit

Did you know… We have over 160 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it free for 5 days!
Create An Account
Support