Please follow instruction and the attatched markiing rubric
ICT211 Database Design Task 2 ATMC Semester 1, 2019 ATMC ICT211 Database Design Task 2 Page 2 of 12 Assessment and Submission Details Marks: 30 % of the Total Assessment for the Course Due Date: Midnight Friday, Week 12 2019 Assignments are to be submitted by SafeAssign. DO NOT SUBMIT THE ASSIGNMENT TO THE COURSE CO-ORDINATOR OR TUTOR. Submit your assignment to the link under Assessment -> Task 2 on Blackboard. The submission link will be open a week before the due date. Please follow the submission instructions provided. The assignment will be marked out of a total of 100 marks and forms 30% of the total assessment for the course. ALL assignments will be checked for plagiarism and/or collusion between individuals. Refer to your Course Outline or the Course Web Site for a copy of the “Student Misconduct, Plagiarism and Collusion” guidelines. Academic Integrity Information. Note: Each student MUST be able to produce a copy of their assignment and this copy MUST be produced within 24 hours of it being requested by the Course Co-ordinator. Failure to produce the second copy of the assignment when requested may result in loss of marks or a fail grade for the assignment. Requests for an extension to an assignment extension MUST be made prior to the date of submission and requests made on the day of submission or after the submission date will only be considered in exceptional circumstances. https://www.usc.edu.au/learn/student-support/academic-and-study-support/online-study-resources/academic-integrity ATMC ICT211 Database Design Task 2 Page 3 of 12 Case Study: Australian Native Plants Ltd – Online CRM Database Background Australian Native Plants Ltd (ANP) is a national plant nursery cooperative specialising in native Australian plants. From its beginnings four years ago in Greenock South Australia, the ANP cooperative now includes six independent plant nurseries around Australia, all specialising in different types of Australian native plants local to their area. Anna Zikov, the owner of Greenock Australian Native Nursery and founder of ANP, started the cooperative to provide a common national web platform to promote, provide advice and sell Australian native plants. A horticulturalist by profession, Anna enjoys using and experimenting with technology in her nursery and uses sensors and electronic device controllers to automate some of the environment control in her green houses. Anna enlisted the services of local web developer Josh to build a platform for ANP. Josh had done a great job on the Greenock nursery website and Anna is very pleased with the new ANP website. It is easy to navigate and provides a lot of local information to which other members of the ANP cooperative contribute. She was very surprised that her ANP cooperative idea proved popular with other native plant nurseries and clients. However as more nurseries joined the cooperative, clients and nursery owners started to notice data anomalies such as wrong or out-of-place information, slow retrieval of information, plant order information not reaching the ANP coop member, and random difficulties entering new information. Anna had a chat with Josh who admitted that, although he is a good web developer, his database design skills were not quite as good. Anna and Josh called on you as a database design consultant to make some recommendations. After some consultation and looking at the database, you realise that Josh had created a flat file database and entity relationships were not logically defined. Your recommendation is to create a Customer Relationship Management (CRM) style database to which nursery industry specific adaptations could be added later. As an experienced database designer, you know that you will not be able to deliver all the database functionality Anna is hoping for in the first project. You explain the need to define a core database system which will provide a foundation for added functionality in the future. User Requirements The following is what you, Josh and Anna agree would be a good core system for this first database design project: Anna believes the core database entities are coop members, clients, plants and plant orders. You generally agree, but you also know that there will be other regular and composite entities, and business rules which will determine the entity relationships. Anna explained that coop members will have a unique member id, member name, contact name, date of start, date of end (for when they leave the coop – it will be blank by default), name of nursery, address of nursery, phone, email, and a member description where the ANP member can describe themselves and their specialty. https://www.stateflora.sa.gov.au/buy-plants/how-to-order/catalogue ATMC ICT211 Database Design Task 2 Page 4 of 12 Josh explained that a client will register on the ANP website and must provide their name, email address, and location. The database must also allocate a client id and start date. The client may order plants so there must be a delivery address attribute as well. When asked about plants as a database entity Anna explained that each plant entity has a plant id, botanical name, it may have a common name, and a description. Each plant variety may be stocked by many coop members and each coop member will stock many plants. Each coop member has their own price for each plant variety. This weak entity will have the ANP coop member id and the plant id, price, price date, unit shipping cost (described below for use in a stored function), and an ‘in stock’ attribute. Anna went on to explain that clients may order plants from any ANP member. The order will have an order id, client id, ANP member id, order date, order status, shipping date, courier name, shipping cost multiplier, and shipping reference number. Each order must include one or more order items. each order item is a plant with has a plant id, order id, plant cost, quantity, and unit shipping cost. Because plants can be of various sizes and clients can order plants from any ANP member, shipping costs can vary (for example a client in Queensland may order a rare native plant from Western Australia). To allow the client to get a rough estimate of total costs, including shipping, a stored database function must be created as described below. When a client submits an order through the ANP website, the order is saved into the database and an alert message is sent to the ANP member automatically through the messaging system. Josh described the messaging system he had built into the ANP website for clients to ask questions and send alerts including notifying ANP members of new client orders, etc. You decide to adjust the messaging system in the database to simplify and make it more efficient as follows: a message entity will have a unique message id, client id, and ANP member id. The message entity will also include a date stamp field and a message field. This simple format will give Josh the flexibility to use it in many different ways on the web site. ATMC ICT211 Database Design Task 2 Page 5 of 12 Important requirements Josh would like to use the sample code that you create. For this reason you MUST: • Incorporate MySQL database and its corresponding SQL and procedural language, • Sequence your script so Josh can run (and re-run) it as one sequential script without error Required native plant cost calculation stored procedure / function Clients can purchase native plants from any ANP member. However shipping costs for shipping plants within that ANP member’s state is much cheaper than sending them interstate. Anna has a shipping cost agreement among ANP coop members as follows: • If a client purchases plants from ANP members located within the client’s state or territory, the total price for the plant delivered to the client will be the listed price plus the unit shipping cost. • for all interstate purchases the client will multiply the corresponding shipping multiplier (table below) by the listed price. For example: • Harry from Parks in New South Wales (NSW) has ordered a quantity of 2 ‘graceful wattle’ from an ANP member nursery in Armadale NSW. The nursery listed price for each ‘graceful wattle’ is $65 plus $120 shipping cost. The total cost to Harry would be (1 * (2 * $65)) + (2 * $120) = $370 total cost. Shipping Multiplier = 1. • Margaret from Perth in Western Australia (WA) has ordered a quantity of 2 ‘graceful wattle’ from the same ANP member nursery in Armadale NSW. The shipping multiplier from NSW to WA is 2.4 so the total cost to Margaret would be (2.4 * (2 * $65)) + (2 * $120) = $552 total cost. Shipping Multiplier = 2.4. The general calculation is as follows: Plant cost = shipping multiplier * plant quantity * plant price Shipping cost = plant quantity * unit shipping cost Total order cost = shipping cost + plant cost ATMC ICT211 Database Design Task 2 Page 6 of 12 Required data IMPORTANT NOTE – the following data MUST be used in your Part B SQL implementation. A ZERO (0) mark will be awarded for Part B if the following data is not used as part of your SQL implementation. Table 1 - ANP Coop Members Nursery name member name Address Greenock Australian Native Nursery Anna Zikov 37 Greenock Road Greenock 5360 SA Kimberly Native Plants Brenda Coen 45 Frederick Street Broome WA 6725 Native to Kingston Jaden Bess 22 Summerleas Road Kingston Tas 7050 Highland Native plants Adam Ross 34 Lowe Street Queanbeyan NSW 2620 Australian Capital Native Plants Susan Gahn 13 Sternberg Cresent 2903 ACT Dalby native plants Peter Back 158 Bunya Highway Dalby Qld 4405 Table 2 – Plants Botanical Name Common Name Description Eutaxia obovata eggs and bacon Native shrub grows in WA Eremophila glabra common emu bush Native bush grows in SA Acacia floribunda gossamer wattle Large native tree grows in Qld, NSW and Vic Goodenia amplexans clasping goodenia Native shrub grows in SA and Vic