The Office Supplies Company (OSC)

The Office Supplies Company (OSC)

A Description of OSC The Office Supplies Company (OSC) is a wholesale company which buys office furniture from suppliers and sells them to customers. OSC operates a number of warehouses which hold stocks of products and which deliver orders to all custome

This scenario relates to a database which is to be set up for an organization called OSC which sells office furniture:
A Description of OSC
The Office Supplies Company (OSC) is a wholesale company which buys office furniture from suppliers and sells them to customers. OSC operates a number of warehouses which hold stocks of products and which deliver orders to all customers in a geographical area assigned to them. OSC has a number of Sales Representatives who take orders from customers. There are certain business rules that OSC applies:

• Each product is supplied by only one supplier who may, however, supply more than one product. OSC also keeps details of potential suppliers (i.e. suppliers who are not currently supplying any products).

• Each product is supplied from the supplier to a particular warehouse, its supply warehouse, and then moved on into stock in other warehouses as well (so product P106 is always supplied to warehouse D4 but stocks of product P106 are also held in warehouse D1, D2 etc). Particular products are not held in stock in all warehouses and some warehouses may not hold any stock (e.g. they just act as transhipment points).

• The entire area OSC operates in is divided into geographical areas, one for each warehouse, and each customer is delivered to from the warehouse for the geographical area in which the customer is located. Every warehouse has at least 1 customer in its area. Each customer is located in one place only, i.e. in only one warehouse’s area.

• Customers place orders. Some customers, however, may not have yet placed any orders. An order contains order lines (at least one), with each line ordering a different product (i.e. no two order lines on the same order are for the same product) specifying the quantity of the product to be supplied.

• When an order is delivered, the order is marked as delivered by entering the date delivered on the order. All delivered orders are paid for by the customer at the time of delivery and no goods are ever returned (this is, after all, a simplified theoretical case study) so the customers never owe money nor are they ever paid money.

• Each customer has a credit limit. This limit is the value of outstanding orders which may not be exceeded.

• The company employs sales representatives. Each sales representative looks after all the customers who are supplied from the one or more warehouses which are allocated exclusively to that rep.
• In addition, each product is assigned to a particular sales rep who is responsible for marketing that product, but not all sales representatives are responsible for marketing any products.

The Database

Study the Supply Database case above. The information is to be held in a Relational database and has been implemented using Oracle. Two files are available on OasisPlus for you to create the relations and to load the relations with sample data. The tables in the database are shown below.

Table Attributes
Supplier Supplier_No, Name, Address
Product Product_No, Description, Price, Supplier_No, Marketing_Rep_No, Supply_Warehouse_No
SalesRep Rep_No, Name, Yearly_Bonus
Warehouse Warehouse_No, Location, Address, Rep_No
Customer Customer_No, Name, Address, Warehouse_No, Credit_Limit
Corder (Order) Corder_No, Customer_No, Date_Placed, Date_Delivered
Oline (Order Line) Corder_No, Product_No, Quantity
Stock Warehouse_No, Product_No, Quantity, Aisle, Bay, Bin_No

NB – the table recording Orders is called Corder as ORDER is a reserved word in SQL. Similarly the identifier on the order table (in narrative, the order number) is Corder_No. The domains of attributes are the same if their names are the same (ie Warehouse_No on Stock is based on the same domain as Warehouse_No on Warehouse) and, in addition, Marketing_Rep_No is based on the same domain as Rep_No and Supply_Warehouse_No is based on the same domain as Warehouse_No.

It is essential that you draw an Entity-Relationship diagram mapped to a relational database, showing the entities and relationships involved. Assume that attributes with the same name are based on the same domain. Do not hand in this diagram – it is not assessed.

 
Coursework 2 – Further SQL
Use Oracle iSQL*PLUS to answer the following queries. It is recommended that you use Notepad to initially create the queries. Please indicate which question a particular solution refers by using comments e.g. /* Question 1 List employees…*/. Note that marks will be awarded for SQL code which includes this comment, is easy to read and hence easy to debug.

1. Give the total number of items (Quantity) in Stock in Aisle 5 in Warehouse D3.

2. List the customer Name and the warehouse location for the warehouse delivering to that customer for all customers who receive deliveries from warehouses looked after by Sales Rep Jim Burns.
3. List the Name of each Supplier with the Location of each Warehouse and the number of Products supplied by that supplier and Stocked at that Warehouse
4. List Customers by name who are located in the same warehouse as customer Donaldson. Do not include Donaldson in your answer.

5. List the warehouse number and location of all Warehouses which stock any product which is supplied to the Warehouse whose location is ‘Herts South / West’.

6. Set up a query merging two SELECT statements, one of which gives the Names of all Suppliers who supply Products which are marketed by Sales Rep Jo Shakespeare and the other gives the Names of all Suppliers who supply Products which are delivered to Warehouse Warehouse_No D4.

7. List the Locations and Addresses of all Warehouses which do not stock product ‘4 cube case’.
8. Who (apart from herself) has ordered any product that customer Cox has ordered? Use a self join to perform this query.
9. List the warehouse numbers of any warehouses that stock all the products in the database. (Hint: this is an example of relational algebra divide.)
10. Run the query below and show the results. Give the English meaning (as in questions 1-9).

some PLSQL code examples (from the text book) on OasisPlus to use as templates to help you with this part of the coursework.

1. Product P101 has been selling very well. Write a PL/SQL block which includes a procedure get_salesrep which accepts a product number and returns the sales rep name who is responsible for marketing this product and his/her yearly bonus. The main block should call the procedure with product number ‘P101’ and output this sales rep’s name and the yearly bonus increased by 5%.

2. The Managing Director of OSC is feeling generous. Write a PL/SQL block, which displays all sales reps’ information with their yearly bonus increased by 10%.

Note that in questions 1 and 2 the database is not updated – only the information displayed.

3. Write a stored function called get_warehouse_location. This function takes as input a customer name and returns a warehouse location from where that customer was delivered. Call the function from within an SQL statement to select the customer number and warehouse location for customer ‘Finch’.

4. OSC has a rule that states that a sales rep’s yearly bonus may not exceed 0.1% of the total value of all stock held in warehouses covered by that sales rep. Create a trigger ‘
ORDER THIS ESSAY HERE NOW AND GET A DISCOUNT !!!

 

 

You can place an order similar to this with us. You are assured of an authentic custom paper delivered within the given deadline besides our 24/7 customer support all through.

 

[order_calculator]