<?xml:namespace prefix=”o” ns=”urn:schemas-microsoft-com:office:office”?></o:p>

</o:p>

SMITH’s GALLERY OUTDOOR AND LEISURE ORDER PROCESSING SYSTEM</o:p></o:p>

Introduction</o:p>

</o:p>

Smith’s Gallery of Outdoor and Leisure is a Catalogue mail order company specialising in good quality outdoor and leisure furniture. Fast order processing is essential for mail order firms to compete effectively, and Smith’s Gallery is no exception.</o:p>

</o:p>

A system is to be set up to manage a computerised mail order system for Smith’s Gallery out of the Company’s existing manual mail order processing system.</o:p>

</o:p>

After initial consultation with Company employees, the following data items have been identified as essential.</o:p>

</o:p>

Customer Identification Number, Customer Surname, Customer Name, Customer Address, City, State, Zip, Phone, Credit Card Type, Credit Card Number, Order Identification Number, Order Date, Order Shipment Date, Item Identification Number,</o:p>

Item Quantity Ordered, Catalogue Identification Number, Catalogue Description, Catalogue Price, and Catalogue Quantity On Hand.</o:p>

</o:p>

This is the minimum set of data attributes required.</o:p>

</o:p>

The following business rules have also been established:</o:p>

· One customer places one or more orders.</o:p>

· Each order can have one or more items.</o:p>

· Ordered Items are selected from the Company’s Mail Order Catalogue</o:p>

</o:p>

Other business rules may need to be defined.</o:p>

</o:p> </o:p></o:p>

</o:p>

</o:p>

</o:p>

</o:p>

</o:p>

</o:p>

</o:p>

Task</o:p></o:p>

Your task is to design and install a database for the Smith’s Gallery Mail Order Processing System using ORACLE DBMS.</o:p>

</o:p>

Other business rules may need to be defined.</o:p>

</o:p>

This will involve the following subtasks:</o:p>

· Identify the entities required and draw an ERD using Select Yourdon or another appropriate tool.</o:p>

· Identify some of the systems development methods which are available for this software development project. </o:p>

· Produce a DFD 0 and DFD 1 (following SSADM terminologies) for the organisation using a software drawing tool.</o:p>

· Identify and justify any additional data fields.</o:p>

· Identify and justify any additional business rules.</o:p>

· Use normalisation to design the database.</o:p>

· Ensure that you have the minimum number of tables for controlled redundancy.</o:p>

· Indicate the primary and foreign keys for each table.</o:p>

· Write a script to create the tables, including appropriate constraints and optimisation.</o:p>

· Write a script to insert meaningful test data into the relevant tables.</o:p>

</o:p>

Deliverables</o:p>Task 1 Written report to include:</o:p>

(A). An introduction and assumptions, including new business rules.</o:p>

</o:p>

(B). Identify some of the systems development methods which are available for this software development project. Recommend, with justifications, the method you would select for the Smith’s Gallery.</o:p>

</o:p>

</o:p>

Task 2. DFD 0 and DFD 1 (following SSADM terminologies) using a software drawing tool. (Two diagrams on visio)</o:p>

</o:p>

Task 3. The Entity Relationship Diagram (ERD). (((Business Rules)))</o:p>

</o:p>

Task 4. Normalisation (1NF, 2NF, 3NF)</o:p>

</o:p>

Task 5. A hard and working soft copy of the working script used to create the tables.</o:p>

</o:p>

Task 6. A hard and working soft copy of the working script used to insert test data.</o:p>