database design

Case Study: Scenario - Global Trading PLC

Global Trading PLC is a mail order company that operates a number of different catalogues. Each catalogue addresses a specific market segment and the company is noticing a drop of in the sales through its higher end products and socio - economic customer group, typified by its 'life style' catalogue offerings.

Research has shown that this is because these groupings are now typically buying from companies offering similar services and products on the World Wide Web.

Whilst Global recognise the need to maintain its more traditional agent based catalogue business, they have now decided that they need to embrace e-commerce and develop a Web based service. The Finance Director has a PC at home and has recently bought Dreamweaver.

The existing database system, which has built up piecemeal over a number of years, has been poorly designed and is inadequate. This new development gives the company the opportunity to redesign their system.

It must be recognised that the 'traditional' business will continue for some years and that data stored for that may differ from that needed by the web based business. For example, the agent based catalogue customers must buy through the agent. Indeed, they are identified by a combination of their customer number and the agent ID. This style of catalogue maintains the traditional periodic payments system, so customer records need to include credit rating and transaction history data. By contrast, the 'life style' catalogue customers mostly pay by using credit cards, a system which is used by the vast majority of e-commerce transactions.

Information is maintained for customers so that marketing mailing can be targeted. It has been recognised that an individual could be both a credit card customer AND an agent supported customer. This currently leads to duplication of data storage and mailing, some of which can be contradictory and confusing.

The company headquarters are in Leeds with depots in strategic regional locations from where the company’s own fleet of delivery vans operate. Large regions can have more than one depot. There is a central depot near Birmingham that supplies the regional depots.

The structure of the Agents Organisation is as follows:

General Sales Manager

Midland Northern Southern Scottish

Division Division Division Division

Each division is split into a number of regions.

Each region has a number of agents, working on commission based on sales to their customers, who promote goods and take customer orders. A monthly report of the orders taken by product, with summaries at all levels of the Sales Organisation is produced for management. Sales statistics are required for each product category to monitor the effectiveness of different discount strategies.

Orders are received form their customers by agents, summarised and posted to head office over night. The orders are then validated, priced and checked centrally for availability at the appropriate depots. Each depot services customers according to their Agents ID. Stocks may be transferred between depots to satisfy incoming orders – this practice, however, is not approved of by the accounts department, but is carried out so that part orders can be shipped, so that customer deliveries are not delayed.

Each week, estimates produced by the Head Office are used to update a three-month sales forecast, which shows expected sales of each product in each of the following thirteen weeks. The forecast is used as the basis for ordering stock which may be ordered from a number of possible suppliers, each having quoted a unique lead time and price. A purchase order sent to a supplier may be for several different products, each due for delivery in a different week. Price and lead-time quotations are updated from time to time from suppliers’ revised quotes. Purchase orders are placed at the price prevailing at the time of order.

Pricing and Availability consists of:

Identifying/validating customer codes,

Identifying/validating product numbers,

Placing sales orders and order items on files,

Pricing each order item,

Checking product stock at depots and allocating if available. This process is confused by the fact that, for historical reasons, each depot maintains its own stock location codes, which means that these can be duplicated.

Discount structures are based on the product categories, examples of which are :-

Clothing - Male

Clothing - Female

Household electrical

Toys

Communications

The discount categories are used as follows

A : Discount given to agents

B : Additional discount given to agents who achieve sales thresholds. These thresholds vary.

C : Used for targeted discounting for old stock

D : Used for targeted discounting for regional promotions.

Invoices are produced daily for from the details of consignments shipped. They are posted to the Invoice History file and copies are sent to the customers. When payments are received these are also posted to the above file. Customer balances are maintained. Credit notes for faulty goods or short deliveries are generated where necessary and sent to the customer.

The company’s current order processing system operates on a centralised computer. In the two years since purchasing this machine several new systems have been designed and implemented. These include a monthly and industrial payroll system, several accounting systems, and a sophisticated sales product costing system.

Volumes and Frequencies (statistics are only currently known for traditional business)

· Customers 125,000

· New Customers 150 per month

· Deleted Customers 140 per month

· Products 1,000

· Orders 5,000 per day ave.

(80% are for 20% of product codes)

· Items per Agent order 10 ave.

(10% less then 10,

10% more then 10)

· Invoices/credit notes 5,125 per day avg.

· Cash items daily 2,000 ave.

· Products that have pending orders 10% ave.

At any time (ALL depots will have Pending orders)

Current Head Office enquiry screens include:

Customer order

Customer

Assignment Overview

You will be undertaking a database development project for a client (in this case your tutor) presenting the findings of the analysis stage, based on the given requirements. Using feedback from this stage you will go on to design, implement and test your system using appropriate database techniques and technologies. You will also be expected to evaluate your development processes relating to the System Development Life Cycle, as well as considering emerging technologies that could be used to improve the organisation’s data management and decision making processes.

Database Design

· Produce an Extended Entity Relationship Model (EERM) using top-down approach for the given case study organisation. Use QSEE to produce your EERD and the notation taught on the module. List Validation Questions (6-10) used to validate your EER Diagram.

· Normalise the Form in Appendix 3 (apply bottom- up approach)

· (Use the pro-forma given in Appendix 2)

· Produce a Composite EER Diagram (based on outputs from bottom up and top down approaches) and derive this Composite EERD to produce final list of relations/tables and for each relation/table produce a full list of attributes, attribute definition and occurrences (data values), clearly defining attributes that are Primary Key(s) and any derived Foreign Key(s) attributes.

Implementation and Testing

You are required to implement only a sub-system of the produced Composite model in previous task.

You will be asked to demonstrate your implemented and tested sub-system.

· Tables and DataImplementation in Apex using SQL Script environment

The Implementation of a sub-system (4-6 related tables).

As a guideline you should populate each table with at least 10 rows. The data should be designed to provide appropriate results from the queries, and should reflect the requirements of the organisation.

· SQL queries (min of 5 to be produced)

To test your system, you are asked to produce a set of queries which will be specified in plain English (business statement) and implemented using SQL statements. Your SQL statements will need to be complex in order to achieve high marks.

· Import your Apex tables into SAS Enterprise guide and

· Produce 3 programs to manipulate data using SAS programing

· produce 3 reports using SAS program

Appendix 3: Normalisation Template (proforma)

UNF 1. List all Attributes from document/table. 2. Identify key field underlined or in bold.(May need an artificial key) 3. Put repeating groups in (….)*	1NF 1. Break out repeated group(s) if any to form new group. 2. Include original key as a foreign key in new group. 3. Identify additional column(s) in new group to form compound key including foreign key.	2NF 1. Break out any part key dependencies. 2. Identify keys for each new group and include any foreign key links to link back to original group.	3NF 1. Further data groups created by breaking out any non-key dependencies. 2. All groups have a key and foreign key links shown.	Table / Relation

Appendix 3: Normalisation Form (using bottom up approach)

List al attributes, and normalise as one single task

Customer Order Enquiry Screen

Order no. 287614

Customer no. 147922

Agent ID S231

Date received 7.10.00

Due date 31.10.00

Items outstanding 2

Special instructions

Delivery must not be made on

Tuesday and Thursday afternoons

Product Code	Quantity	Discounted Price	Date Sent
149714	2	27.95	25.10.00
292812	1	39.99	25.10.00
601642	2	15.99	-

Product Enquiry Screen

Product Code N123za Date Added

Product Description Nookia Phone

STD Weight (grammes)

Code Description

Bundle/Associated n123za1 Battery

n123za2 Handset

n123za3 SIM Card Pay2Go

n123za4 SIM Card Contract

Packing Type

Mailing Code

Unit Price

VAT Class

Discount Code a : 10%

b :5%

c :10%

d :15%

Depot No.	Stock Location No.	Quantity in Stock	Daily Usage	Re-order level	Maximum stock level	Valuation

Customer History Screen

Customer No. S231-147922

Customer Name ____________

Agent Name ____________

Credit Limit £ 500.00

Balance £ 472.16

Date	Reference	Debit (£)	Credit (£)	Trans Type *
2.2.00	414292	250.00	-	INV
5.3.00	524293	-	250.00	CN
12.3.00	534596	252.70	-	INV
6.4.00	604692	-	25.27	PMT
6.4.00	609211	300.00	-	INV
9.5.00	736244	-	55.27	PMT

* Key

INV - Invoice

CN - Credit Note

PMT - Payment