” BISM7206程序 辅导、data留学生程序 写作BISM7206 Information and RetrievalUQ Business SchoolApril 2020BISM7206 Information Retrievaland ManagementCase Study – Queensland Organics:Description and SpecificationsSEMESTER 1 2020BISM7206 Information and RetrievalUQ Business SchoolApril 2020Queensland Organics Case Study: Description and Assignment Specifications 1PurposeThis document provides the case study and assignment specificationsfor the assessment Item2 – Case Study: Database analysis, design and development. After you have read thefollowing case study description and assignment specifications, you should refer to theassociated Database Design Case Study Formatting and Submission Requirements document.If you have any questions after you have read both of these documents, please post yourquestionsto the Discussion Board under the relevant forum Assessment Item 2 – Case study.BackgroundSarah James grew up on the Atherton Tablelands in far north Queensland on her familysAvocado farm. On completion of her Business degree, Sarah realised that her businessmanagement knowledge could be applied within the Queensland horticultural industry.Queensland horticulture is worth almost $3 billion per year and is Queenslands secondlargest primary industry. Sarahs passion for the land and the subtropical climate led her backto the Atherton Tablelands where she invested in an organic fruit and vegetable farm andstarted trading as Atherton Organics Ltd.Initially, Sarahs business plan was directed at farm fresh organics for the home deliverymarket but subsequently expanded to cater to the needs of the local restaurant market. ThereBISM7206作业 辅导、data留学生作业 写作、 写作Java作业、Python/c++程序作业 辅导was increasingly high demand for organic fruit and vegetables both locally and nationally.Similarly, there was a growing number of Queensland suppliers interested in joining the coop.Sarah decided to form a Queensland based cooperative that would provide a commonplatform to promote the supply of organically grown fruit and vegetables and a centralmarketplace for customers throughout Australia. The newly formed co-op was renamed toQueensland Organics (QO).Prior to establishing QO, Sarahs business relied primarily on spreadsheets to keep track ofher produce, expenditure, orders and invoices. This system was proving to be inadequate forthe growing and somewhat complex needs of QO. To address these needs, Sarah enlisted thehelp of a local web developer (Jack) to build a common web platform for QO. Jack did a greatjob and Sarah was very pleased with the new QO website. It was intuitive to navigate andprovided helpful information for customers as well as the basic functionality that enabledprocessing and recording customer purchases and co-op member supplies. The QO websitealso provided a platform for its members to collaborate and share ideas online with theircommunity.While Sarah was extremely pleased when organic fruit and vegetable growers from all overQueensland asked to join QO, it was becoming apparent that the web system was not copingBISM7206 Information and RetrievalUQ Business SchoolApril 2020Queensland Organics Case Study: Description and Assignment Specifications 2with the growth in members and customers. Various system-related issues were beingreported to Sarah including data anomalies, slow information retrieval time, missing ordersand difficulties entering new information.Sarah decided to talk to Jack to discuss how these system issues could be resolved as theywere having a negative impact on the business operations of QO. Jack admitted that whilsthe was an experienced web developer, he had less skill and experience at designing andimplementing databases. As a result, Sarah and Jack have asked you, as a database designconsultant, to make recommendations for a way forward. Following several consultations andinvestigation of the current set-up, you realise that Jack had created a flat file database andentity relationships were not logically defined.Based on your analysis, your recommendation is to create a basic Customer RelationshipManagement style database which could be adapted for future enhancements and additionalfunctionality. As an experienced database designer, you are fully aware that the functionalitythat Sarah would like, will not be possible to achieve in the first phase of the project. Youexplain the need to define a core database system which will provide a foundation for addedfunctionality in the future.User RequirementsA joint agreement was formed to design and implement the following core systemrequirements as the first phase of the QO database project: The core database entities are co-op members, customers, produce items (fruit andvegetables) and customer orders. You generally agree, however, you also know that therewill be other regular and composite entities, and business rules that will determine theentity relationships. Co-op members will have a unique member id, a member name, a contact name, a datewhen they joined the co-op and an end date (should they leave the co-op it will be leftblank by default), the name of the business, its address, phone number, and email addressand a member description where they can describe themselves and their specialty. A customer will register on the QO website and must provide their name, email address,delivery address including State and Postcode. The database must also allocate acustomer id and a start date. Each produce item has an item id, an item name, weight and a description.BISM7206 Information and RetrievalUQ Business SchoolApril 2020Queensland Organics Case Study: Description and Assignment Specifications 3 Each produce item may be stocked by many co-op members and each co-op member willstock many different produce items. Each co-op member has their own price for eachproduce item although weights are standard across the co-operative. This weak entity willhave the QO co-op member id and the item id, the price, price date, unit shipping cost,and an in stock attribute. Customers may order produce items from any QO member. The order will have an orderid, customer id, QO member id, order date, order status, shipping date, courier name andshipping reference number. Each order must include one or more order items. Each orderitem is for a produce item and has an item id, order id, item cost, quantity and unitshipping cost. While each member has their own unit shipping costs for their produceitems, to simplify this phase of the database project, these unit shipping costs do not varyacross customer delivery locations. When a customer submits an order through the QO website, the order is saved into thedatabase and an alert message is sent to the QO member automatically through themessaging system. Jack described the messaging system he had built into the QO website for customers toask questions and send alerts including notifying QO members of new customer orders,etc. However, you have decided to adjust the messaging system in the database tosimplify and make it more efficient as follows: a message entity will have a uniquemessage id, customer id, and QO member id. The message entity will also include a datestamp field and a message field. This simple format will give Jack the flexibility to use it inmany different ways on the website.BISM7206 Information and RetrievalUQ Business SchoolApril 2020Queensland Organics Case Study: Description and Assignment Specifications 4Required DataIMPORTANT NOTE the following data MUST be used in your Part B DatabaseImplementation. Zero marks will be awarded for Part B if the following data is not used aspart of your SQL implementation.Table 1 Members of Queensland OrganicsBusiness Name Contact Name AddressFabulous Fruit Veg Shelly Adams Bluebell Road, Tinana, QLD 4650Organic Green Grocer Gary Ward Brook Rd, Kumbia, QLD 4610Bellthorpes Best Berries Jessica Stockdale Brandons Road, Bellthorpe, QLD 4514Stanthorpe Strawberries Anthony Johnstone Mount Banca Road, Stanthorpe, QLD 4380Atherton Organics Sarah James 142 Channel Road, Tinaroo, QLD 4882Organic Fruit Emporium Nick Andrews 190 Green Rd, Wamuran, QLD 4512Table 2 Produce ItemsItem name Description Weight Item priceUnitshippingcostVegetable Box Fresh seasonal vegetables 15kg $50 $18Salad Box Fresh seasonal salad vegetables 10kg $40 $15Fruit Box Fresh seasonal fruit 10kg $45 $15Mixed Box Fresh seasonal fruit and vegetables 15kg $70 $18Vegetable Herb Box Household staples plus garden fresh herbs 15kg $60 $18Berry Mix Seasonal mix of strawberries, blueberriesand raspberries 1 kg $19 $9Capsicum Organic red 220g $6.95 $4Mushrooms Flat brown mushrooms 500g $8.95 $5Turmeric Fresh turmeric roots 200g $11.95 $4Asian Greens Random bok/choisum/kailan 500g $9.95 $5Tomatoes Organic grape/cherry 250g $5.95 $4Ginger Fresh ginger roots 200g $6.95 $4BISM7206 Information and RetrievalUQ Business SchoolApril 2020Queensland Organics Case Study: Description and Assignment Specifications 5Please note: The item prices and unit shipping costs listed in Table 2 are a guide only. Eachco-op member has their own price and unit shipping cost for each of their produce itemsThese costs can vary among members although the weights for each produce item are thesame for all co-op members.Assignment Specifications and DeliverablesPart A: Database Analysis and DesignThe first part of this assignment requires you to analyse the database requirements anddesign the first phase of the database project in accordance with the case study backgroundand requirements. The following documentation is required to be submitted as a MS WordDocument: Entity Relationship Diagram using Crows Foot Notation (preferably using draw.io); Relational Schema – including Primary and Foreign Keys (preferably using draw.io); Supplementary Design Requirements formatted as a data dictionary. The followinginformation can be used as a guide for the elements to be included:o Table names and their data attributes and descriptions;o Information on data type, length, format, acceptable values;o The compulsory/optional nature of the attribute and if nulls are valid. AssumptionsRegardless of the drawing software you use, each of your drawings must be saved as animage (e.g. png) and then pasted into your Word document. Do not embed the link to yourdrawing as this will not be marked and you will receive zero marks for your models.Part B: Database ImplementationYou are required to implement a MySQL database using the database design developed inPart A. The following requirements are to be implemented: CREATE TABLE statements for all tables including integrity constraints; CREATE TRIGGER statement to automatically insert a message to the Message table whena customer order is placed; INSERT INTO statements for populating the database:o Incorporate the exact 6 member names given in Table 1 (make up email addressesand phone numbers);o Incorporate the exact 12 items and their names given in Table 2;o Create your own member item pricing for produce items and their unit shippingcosts (at least 3 produce items for 3 members);BISM7206 Information and RetrievalUQ Business SchoolApril 2020Queensland Organics Case Study: Description and Assignment Specifications 6o Create at least 3 customer entries;o Create at least 3 customer orders; Remember that data may need to be inserted in a particular order to comply withintegrity constraints.Part C: Database QueriesWhen you have implemented the MySQL database (Part B above), you are required to writeSELECT statements to provide the following user requirements:1) SELECT statement/s that will display the following data for a sample customer order: customer name and account number; order number/id and the total amount for the order; order date; At least three order items:o item name;o quantity;o price;o shipping cost;o item total cost.2) SELECT statement that will produce a report of the ordersfrom a particular co-op memberwithin a specified period of time: List all orders between a start-date and an end-date for a particular co-opmember such that:o the report will be grouped by customer;o each line in the report will include the customer name, customer state,order number/id and total for that order; ando will be ordered by the oldest order to the most recent order.Please refer to the associated Case Study Assessment Guidelines forsubmission instructions and the marking rubric.Prepared by: Dr Sherrill CooperCourse CoordinatorBISM7206 Information Retrieval and ManagementApril 2020如有需要，请加QQ：99515681 或邮箱：99515681@qq.com
“
添加老师微信回复‘’官网 辅导‘’获取专业老师帮助，或点击联系老师1对1在线指导。
