PLEASE CONTACT ME - DUE TUESDAY

Spring 2017 CSCI 6623 Database Project MDC 9

SPRING 2017– CSCI 6623 - DATABASE SYSTEMS PROJECT

This project is about a company called the Merchandise Distribution Company or MDC. MDC buys merchandise from suppliers both American and foreign and sells the merchandise to stores. When the merchandise arrives MDC first stores the merchandise in warehouses and then the sales representatives sell the merchandise to the different stores. The following tables are used in this project.

The “ST” or STORE table has 6 columns and each row describes one of the stores to whom we sell merchandise. Each store is given a unique store number (STORENO) which is the PK of the table. NAME is the next column and is the name of the store. CITY and STATE are the next two columns and tells us the location of the store. MGR_NAME is the name of the manager of the store. COUNTRY is the country where the store is located.

The “I” or ITEM table has 6 columns. The first column is ITEMNO which is a unique number assigned to each item so we can identify it easily. It is the PK of the table. NAME is the next column and is the name of the item. COLOR is the color of the item. WEIGHT is the weight of the item. PRICE is the current price of the item that we sell to stores. DESC is the last column and it is a description of the item.

S table is the SUPPLIER table. This table has 10 columns. The first column is SUPNO which is a unique supplier number and is the PK. The next column is NAME which is the name of the supplier. CITY, PROVINCE and COUNTRY are the next three columns and give the location of the supplier. LANG tells us the language spoken by the supplier while CURRENCY tells us the name of the currency used by the supplier. START_DATE is the date we started to buy from this supplier. VOL_DOLLAR is the amount of dollars spent by us to this supplier for merchandise in this calendar year. TYPE tells us if the supplier is American (A) or foreign (F).

C is the SOURCE table which tells us which supplier is supplying us with which item. So SUPNO is the supplier number and ITEMNO is the item number. COST is the current cost of this item from this supplier. STARTDATE is the date that the supplier started selling this item.

CH is the next table and contains HISTORICAL SOURCE. This tells us historically which items the suppliers used to supply but no longer supplies. SUPNO is the supplier number and ITEMNO is the item number. STARTDATE is when the supplier starting selling the item while ENDDATE is the date when the supplier stopped selling the item. COST is the last price the supplier sold the item just before the supplier suspended sale of the item.

R is the next table. The R table describes the SALES REP. REPNO is the first column and is the unique number given to each sales rep. A sales rep sells the merchandise to stores. NAME is the name of the rep. DOB is the date of birth of the rep. SSNO is the social security number of the rep. STARTDATE is the date when the rep started working for our company. SALARY is the current salary of the Rep. CITY, STATE and COUNTRY tells us where the rep is situated.

The next table is the T or territory table. This table tells us which stores is the territory for which sales reps. This means that a sales rep is assigned to certain stores and cannot sell merchandise to any other store. Likewise no sales rep can sell merchandise to the stores belonging to a different sales rep. The columns REPNO and STORENO form the PK of the table. The third column is STARTDATE which is the date when the rep first started selling to the store.

The next table is the W or WAREHOUSE table. The W table contains 5 columns. The WHNO is the warehouse number which is unique since every warehouse gets a unique number which is a PK for this table. CITY and STATE tells us the location of the table. MGRNAME is the name of the manager of the warehouse. STARTDATE is the date the warehouse opened.

V is the INVENTORY table. The PK consists of WHNO and ITEMNO. This tells us which items are stored in which warehouse. QTY is the quantity of the item in the warehouse. ASOFDATE is the date the inventory was counted.

TR is the SALES TRANSACTION table. It tells us the sale to each store. SALENO is the unique sale number transaction which is the PK. SALEDATE is the date of the sale. STORENO is the store where the item is sold to. REPNO is the rep who sold the items to the store. TOTALAMT is the total amount of money the sale was worth.

The last table is D or SALES DETAIL. This table tells us the items sold in each sales transaction. SALENO is the sales transaction. ITEMNO is the item. QTY is the number of items sold and PRICE is the price the items were sold at.

TABLES

ST- STORE S –SUPPLIER

STORENO - store number SUPNO - supplier number

NAME - name of store NAME - name of supplier

CITY - city where store is located CITY - city of supplier

STATE - state where store is PROVINCE - province of supplier

MGR_NAME - name of manager COUNTRY - country of supplier

COUNTRY - country where store is LANG - language of the supplier

CURRENCY - currency used in that country

I – ITEM START_DATE - when we began to use supplier

ITEMNO - item number VOL-DOLLAR - current year sales in dollars

NAME - name of item TYPE - “A”merican or “F”oreign

COLOR - color of item

WEIGHT - weight of item

PRICE - current price of item

DESC - description of item

C - SOURCE

SUPNO - supplier number

ITEMNO - item number

COST - item cost

STARTDATE - date sup started to sell item R – SALES REP REPNO - sales rep number

CH – HISTORICAL SOURCE NAME - name of sales rep

SUPNO - supplier number DOB - date of birth

ITEMNO - item number SSNO - social security number

STARTDATE - date sup started to sell item STARTDATE - date rep started working

ENDDATE - date sup stopped selling item SALARY - salary of rep

COST - item cost CITY - city of rep

STATE - state of rep

COUNTRY - country of rep

T – TERRITORY W – WAREHOUSE

REPNO - rep number WHNO - warehouse number

STORENO - store number CITY - city of warehouse

STARTDATE - date started STATE - state of warehouse

MGRNAME - manager name

V – INVENTORY STARTDATE - date warehouse opened

WHNO - warehouse number

ITEMNO - name of item TR – SALES TRANSACTION

QTY - qty of item in this warehouse SALENO - sales number

ASOFDATE - date inventory last counted SALEDATE - date of sale

STORENO - number of store

TRD – SALES DETAIL REPNO - number of rep

SALENO - sales transaction number TOTALAMT - total amount of sales transaction

ITEMNO - item number

QTY - qty of item sold

PRICE - price per unit item at time of sale

EQUIVALENT DOMAINS

The following columns all share the same domains:

The following columns all share the same domains:

The following columns all share the same domains:

The following columns all share the same domains:

The following columns all share the same domains:

PLEASE NOTE: FOR ALL DATES, USE CHAR (8).

Thus, for November 27, 2016 use: ‘11272016’

ASSIGNMENT

Part I Create all the tables and insert the rows as shown on the following pages. Show

All work. This part is worth 10 points. Show EVERY “create”, show every “insert” and use “select * from tablename” to display each table after all the inserts are completed.

Part II Answer the nine questions listed below, using these tables and SQL. This part is worth 90 points. For each question, please provide the following:

1. question in English

2. the SQL code that answers it

3. the results from the question

If the question requires a “SELECT” then the result from the “SELECT” is a temporary table that you need to print out. If the question is an UPDATE, INSERT or DELETE then you need to print out the affected tables both before and after the operation. There is one question where you must use views since I specifically ask for it. However, you may use views in any question where you think that they would be helpful to you. Each question is 10 points.

Use the document in the Course Information section of blackboard to help you understand how to use Oracle at FDU

Questions

1. Find the name and number of all stores that have not sold any items that could be supplied by foreign suppliers.

1. Give the name and number of all stores along with the average amount of sales and the total amount of sales as long as there are at least 2 sales for the store.

1. Find the name and number of the sales rep who makes the smallest salary.

1. Find the number of items that we offer for sale in each color.

1. Find the name and number of all items that have no known weight.

1. Create a view that has for each Repno the number of stores in his territory (use table T). Then, using this view write a select statement that finds the Repno of the sales rep who has the most stores in his territory compared to the other sales reps.

1. Find the name and number of all foreign suppliers where the province is unknown and the language spoken has the letter “z” (could be upper or lower) and the name of the supplier does not have the letter “P” (upper) and does not have the letter “t” (lower).

1. Find the name and number of all items that are either supplied by a foreign supplier but not an American supplier or by an American supplier but not by a foreign supplier.

1. Which sales transaction had the most number of different items? Provide the sales transaction number.