17.Business Scenario Best Electronics is a large online retailer of electronic goods. On average there are 1,000 sales recorded per day from their customers. They have a combination of new and repeat customers and currently they hold details of about 10,000 customers in their system. Customer details may be updated but are not deleted. Sales orders are rarely updated or deleted. They have a stable product list of about 100 electronic products that seem to be very popular. The Despatch table is updated with some information as soon as a sales order is received and later updated to complete the order despatch.

The initial database schema above gives you some more information about the tables and the relationships between the tables. The structure of each table is given on the next page. SalesOrder Customer Product Despatch OrderLine 3 An initial schema for the tables is specified below.

Physical Design Considerations During design, conversion from 2NF to 3NF would normally result in the derived totals being removed from the Sales_Order and Order_Line tables. Notice however that during physical design, it was decided that these totals need to be stored for performance improvement of queries. The Despatch table is typical of the way in which businesses operate in practice. Its purpose is to assist the warehouse staff in quickly locating products that are needed for the daily delivery run to customers/mail centre. It contains the quantity (number of units needed) for delivery for each Order_Line. The warehouse shelf location is also stored to facilitate quick retrieval of the product. Thus, the table redundantly stores product name, shelf location and quantity required for despatch. Note that the product name is stored, in addition to the product code, as the name is much more meaningful to the warehouse staff than a code. In order to optimise delivery, the Despatch table is preloaded with the product ID, product name and quantity as soon as the order details are saved. The shelf location for each product is added later by warehouse staff. 4 The business enterprise is migrating to an Oracle-based platform and you are required to set up the necessary infrastructure. The business enterprise operates on a thirteen-week processing cycle whereby Sales records are retained during this period and archived thereafter. The migration will take place at the start of the next scheduled cycle and you will be required to load and maintain the tables described above. You are required to provide a small sample of the data that each table will hold. Appropriate entity integrity and referential integrity constraints must be defined for all tables and a detailed ER Diagram for the system should be provided. In addition, you will need to specify the likely activities (inserts, updates and deletes) that will affect the data. This is to be described before you decide on and present the PCTFree and PCTUsed values for each table.

Part 1B. Calculate the size of the tablespace(s) required to hold the data. Parameters such as SIZE, INITIAL, NEXT, PCTFREE and PCTUSED need to be defined, based on your description above. Show clearly the steps involved and give reasoning for these calculations.

Task 1 - Design of the System Part 1A. In this assignment you will use the database schema given below and table specifications that follow. Customer Product SalesOrder OrderLine Despatch Suggested Schema Customer Table Cust_id Cust_name Cust_add Phone Email Number(6) Varchar2(30) Varchar2(30) Varchar2( 10) Varchar2( 30) Sales Order Table Order_num Sale_date Sale_total Cust_id Number(8) Date Number(6,2) Number(6) Orderline Table Order_num Prod_id Quantity Sale__price Line_total Number(8) Number(5) Number(5) Number(4,2) Number(5,2) Product Table Prod_id Prod_name Prod_Price QuantityOn Hand Number(5) Varchar2(30) Number(4,2) Number(5) Despatch Table Desp_id Order_num Prod_id Prod_name Quantity Shelfloc Number(4) Number(8) Number(5) Varchar2(30) Number(5) Varchar2(6) Table of Contents 1. Overview of the Assignment (1 paragraph or / Page) 2. Table Definitions a. ER Diagram of system explained with sample data b. Table Structures C. Choice/Explanation Of Table Parameters And Values d. Sample Data 3. Tablespace a. Calculation of Tablespace Size b. Choice/Explanation of Tablespace Parameters and Values C. Tablespace Creation Script 4. Creation of Users, Roles, Profiles a. Security Policy And Matrix b. Users, Roles, Profiles Defined in Creation Scripts, and Explained 5. Table Creation Scripts 6. Procedure to populate Customer table with Data 7. Procedures to populate Product table with Data 8. Procedures to populate Despatch table with Data 9. Procedure to 'Add A New Sale' 10. Trigger to Update Despatch Table (or a Procedure if you are unable to write a trigger) Task 1 - Design of the System Part 1A. In this assignment you will use the database schema given below and table specifications that follow. Customer Product SalesOrder OrderLine Despatch Suggested Schema Customer Table Cust_id Cust_name Cust_add Phone Email Number(6) Varchar2(30) Varchar2(30) Varchar2( 10) Varchar2( 30) Sales Order Table Order_num Sale_date Sale_total Cust_id Number(8) Date Number(6,2) Number(6) Orderline Table Order_num Prod_id Quantity Sale__price Line_total Number(8) Number(5) Number(5) Number(4,2) Number(5,2) Product Table Prod_id Prod_name Prod_Price QuantityOn Hand Number(5) Varchar2(30) Number(4,2) Number(5) Despatch Table Desp_id Order_num Prod_id Prod_name Quantity Shelfloc Number(4) Number(8) Number(5) Varchar2(30) Number(5) Varchar2(6) Table of Contents 1. Overview of the Assignment (1 paragraph or / Page) 2. Table Definitions a. ER Diagram of system explained with sample data b. Table Structures C. Choice/Explanation Of Table Parameters And Values d. Sample Data 3. Tablespace a. Calculation of Tablespace Size b. Choice/Explanation of Tablespace Parameters and Values C. Tablespace Creation Script 4. Creation of Users, Roles, Profiles a. Security Policy And Matrix b. Users, Roles, Profiles Defined in Creation Scripts, and Explained 5. Table Creation Scripts 6. Procedure to populate Customer table with Data 7. Procedures to populate Product table with Data 8. Procedures to populate Despatch table with Data 9. Procedure to 'Add A New Sale' 10. Trigger to Update Despatch Table (or a Procedure if you are unable to write a trigger)