A sales order database needs the following two tables.

CUSTOMER: used to record all customer contact information.

CUST_ORDER: used to enter customers' order information (not order details).

Please note that CUST_ID in CUST_ORDER table correspond to CUST_ID in CUSTOMER table. In other words, customer information needs to exist before customer can take an order.

You will create a SQL script (10 points off if not in one file) in order to accomplish the following tasks.

1. Connect to scott/tiger schema (5 points)
2. Create the above two tables in the scott/tiger schema. (15 points)
3. Define column datatypes based upon the type of data to be entered, as shown in the tables. (20 points)
4. In CUSTOMER table, define CUST_ID as primary key. In CUST_ORDER table, ORDER_ID is a primary key. (20 points)
5. Define and create parent-child relation between the two tables. (10 points)
6. Insert (populate) data into the two tables. (20 points)
7. Please make sure that the exact spelling (as specified) is required on names of table and data fields. Otherwise, you will lose points.

Tip:

While the details on data type are not within the scope of this course, please use some common sense when you define the data type.  For instances, CUST_ID field will be large enough to accommodate future growth of your organization.  ZIP, D_Phone and E_Phone should be defined as VARCHAR2 instead of Number since those data will not be involved in any future computations and they will not be truncated (i.e. if ZIP code is 00212).

Create a single SQL script for the project (10 points off if not in one file).  Please test your script before submission.  Twenty (20) points will be taken if the script is not running (even with minor syntax error) while the instructor tests on his system.  Please note the importance of this practice as a database professional in the field.  This practice will be reinforced for all remaining projects in the course. 

Submit your project through D2l. If you need instructions on how to submit, click here.

Please submit your SQL script for performing the tasks of this project. (SQL script is in .sql format, which can be run in Oracle as SQL>@filepath\file.sql, for example, SQL>@C:\temp\myscript.sql.)

Please use the same file convention for this course. Please note that five (5) points will be taken for all projects this semester if the file naming convention is not followed.

At the end of this week. Please see the course schedule for exact due date.