<?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>
Â