TEC 5323 Advanced Database Technology


Final Project

Background:

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

CUSTOMER: used to record all customer contact information.

CUST_ID LAST FIRST MIDDLE ADDRESS CITY STATE ZIP D_PHONE E_PHONE
107 Harris Puala E 1156 Jefferson Ave Charleston IL 61920 2175812223 2173480022
232 Edward Mich M 4204 Grant Street Mattoon IL 61938 2172355537 2173482322
133 Carcia Maria H 2211 Oak Lane Champaign IL 61821 2173334589 2172448973
154 Miller Reggie   699 Pruto St Urbana IL 61801 2175816645 2172337654
179 Woods Tigger   987 Durham Road Tillton IL 61833 2173350045 2175452376

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

ORDER_ID ORDER_DATE PAY_METHOD CUST_ID ORDER_SOURCE_ID
1057 5/29/2018 CC 107 2
1058 5/29/2018 CC 232 6
1059 6/10/2018 CHECK 133 2
1060 6/21/2018 CC 154 3
1061 6/30/2018 CC 179 6
1062 7/1/2018 CC 179 3

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

ORDER_SOURCE_ID SOURCE_DESC
1 Winter 2017
2 Spring 2018
3 Summer 2018
4 Outdoor 2018
5 Children 2018
6 Web site

ITEM: to store information on products.

ITEM_ID ITEM_DESC CATEGORY_ID
894 Women's hiking shorts 1
897 Women's fleece pullover 1
995 Children's beachcomber sandals 2
559 Men's expedition parka 3
786 3-season tent 4

CATEGORY: to store information on merchandise category.

CATEGORY_ID CATEGORY_DESC
1 Women's clothing
2 Children's clothing
3 Men's clothing
4 Outdoor gear

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

INV_ID ITEM_ID ITEM_SIZE COLOR PRICE QOH
11668 786   SKY BLUE 259.99 16
11669 786   LIGHT GREY 259.99 12
11775 894 S KHAKI 29.95 150
11776 894 M KHAKI 29 147
11777 894 L KHAKI 29.95 0
11778 894 S NAVY 29.95 139
11779 894 M NAVY 29.95 137
11780 894 L NAVY 29.95 115
11795 897 S EGGPLANT 59.95 135
11796 897 M EGGPLANT 59.95 168
11797 897 L EGGPLANT 59.95 187
11798 897 S ROYAL 59 0
11799 897 M ROYAL 59.95 124
11800 897 L ROYAL 59.95 112
11820 995 10 TURQUOISE 15.99 121
11821 995 11 TURQUOISE 15.99 111
11822 995 12 TURQUOISE 15.99 113
11823 995 1 TURQUOISE 15.99 121
11824 995 10 BRIGHT PINK 15.99 148
11825 995 11 BRIGHT PINK 15.99 137
11826 995 12 BRIGHT PINK 15.99 134
11827 995 1 BRIGHT PINK 15.99 123
11845 559 S SPRUCE 199.95 114
11846 559 M SPRUCE 199.95 17
11847 559 L SPRUCE 209.95 0
11848 559 XL SPRUCE 209.95 12

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).

SHIPMENT_ID DATE_EXPECTED
211 09/15/2018
212 11/15/2018
213 06/25/2018
214 06/25/2018
215 08/15/2018

SHIP_LINE: to store information on shipment details.

SHIP_ID INV_ID SHIP_QUANTITY DATE_RECD
211 11668 25 09/10/2017
211 11669 25 09/10/2017
212 11669 25  
213 11777 200  
213 11778 200  
213 11779 200  
214 11798 100 08/15/2017
214 11799 100 08/25/2017
215 11845 50 08/15/2017
215 11846 100 08/15/2017
215 11847 100 08/15/2017

 

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.

ORDER_ID INV_ID ORDER_QUANTITY
1057 11668 1
1057 11800 2
1058 11824 1
1059 11846 1
1060 11798 2
1061 11779 1
1061 11780 1
1062 11799 1
1062 11669 3

COLOR: to store information on available colors.

COLOR
SKY BLUE
LIGHT GREY
KHAKI
NAVY
ROYAL
EGGPLANT
BLUE
RED
SPRUCE
TURQUOISE
BRIGHT PINK

 
Tasks:

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).


 

 
Report:

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.

 
Due: Please check the course schedule for the due date.  

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.)

 
Class Index
 

School of Technology
College of Business & Applied Sciences
Eastern Illinois University