A database for the sales department contains the following tables for a retail operation.

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.

ORDER_SOURCE: used to store information regarding the catalogs

ITEM: to store information on products.

CATEGORY: to store information on merchandise category.

INVENTORY: to store inventory information, including sizes and quantity on hand.

SHIPMENT: to store information on shipment TO the store. Please note this table is used for the store to receive products from the manufacturer(s).

SHIP_LINE: to store information on shipment details.

ORDER_LINE: contains detailed information on orders from customers. Please note that this table is used to enter data on products ordered by your customers.

COLOR: to store information on available colors.

You will create one SQL script in order to accomplish the following tasks.

1. Connect to appropriate user for the next step of this project. (5 points)
2. Create a schema (user) named "tec5323" with password "finalproject". Please note that punctuation marks such as (" and .) are not part of the name or password. (10 points)
3. Grant "dba" role to user "tec5323." (5 points)
4. Connect to "tec5323." (You may want to exam chap04.sql from your download, to get some hint.) (5 points)
5. In "tec5323" schema, create all tables with appropriate column datatypes. Please note the datatypes have to be reasonably justified with needs. See comments from previous project. (35 points)
6. Create necessary constraints for all related tables. (10 points)
7. Populate the tables with data as shown above. (20 points)
8. Create necessary trigger(s) to ensure data integrity. (10 points)
9. Please make sure the exact spelling (as specified) is required on everything we do or create.

Tips:

1. You may refer to the scripts that come with the chapter exercises on the course web site (such as chap04.sql). Those scripts will give you some hints.
   
   
2. Please note that you are supposed to create one single SQL script. In other words, once you create the script file, you may execute all above tasks in one command similar to the following:
   
   SQL>@d:\tec5323\firstname_lastname_final.sql
   
   
3. Please feel free to discuss and share ideas on Problems and Solutions. However, please refrain from sharing files or entire code you have developed.
   
   
4. Based upon students research in Summer 2016 and Summer 2017, if you have Oracle 12c, you may use one of the following three alternative methods to create a user. Any of the three (3) approaches will be acceptable for this project:
   
   

   a. Add C## in front of user name. In other words, if you define the user as c##tec5323, it will work in Oracle 12c and is acceptable for this project. b. Alter session: alter session set "_ORACLE_SCRIPT"=true; After using this alter session statement, you can create user using the name as specified (tec5323). c..Set container for the session: alter session set container=PDBORCL; After using this alter session statement, you can create user using the name as specified (tec5323).

   
   

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 projects in the course, including this final project. 

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 as follows.

Convention for File Name: Please name your file as firstname_lastname_final.sql, where firstname and lastname are your first and last name, respectively, and "_" is underscore.

Final Word

A language instructor was explaining to her class that French nouns, unlike their English counterparts, are grammatically designated as masculine or feminine. Things like 'chalk' or 'pencil,' she described, would have a gender association although in English these words were neutral.

Puzzled, one student raised his hand and asked, "What gender is a computer?"

The teacher wasn't certain which it was, and so divided the class into two groups and asked them to decide if a computer should be masculine or feminine. One group was composed of the women in the class, and the other, of men. Both groups were asked to give four reasons for their recommendation.

The group of women concluded that computers should be referred to in the masculine gender because:

1. In order to get their attention, you have to turn them on.

2.They have a lot of data but are still clueless.

3.They are supposed to help you solve your problems, but half the time they ARE the problem.

4.As soon as you commit to one, you realize that, if you had waited a little longer, you could have had a better model.

The men, on the other hand, decided that computers should definitely be referred to in the feminine gender because:

1.No one but their creator understands their internal logic.

2.The native language they use to communicate with other computers is incomprehensible to everyone else.

3.Even your smallest mistakes are stored in long-term memory for later retrieval.

4. As soon as you make a commitment to one, you find yourself spending half your paycheck on accessories for it.

Contributed by Travis Hasting, Graduate Student, Spring 2002 (You can tell how old the instructor is now.)