T3 2020: ICT200 Database Design and Development Assignment 4 Group Report Submission Requirements: This is a group assessment task with 3-4 students in a group. Students will submit one group report (1000 words) detailing the tasks carried out in the assignment. Report must be well-written and should have appropriate referencing. This report is due by the end of week 11. Complete all tasks based on the given case study. Case Study: The Fragrance Flowers Group operates extensively in the Australian flower industry. They are a multi-generational family business running for last 50 years focussing on providing everyday fresh and quality flowers to customers through different cities in Australia. They also provide the option to add accessory items such as teddy bears, chocolates or a vase along with the flower purchase. Recently they are planning to replace their current manual system by designing a new database system that will make the online flower ordering experience more convenient and hassle-free. A simplified database should support procedures such as checking the inventory of all components for a particular arrangement in different store locations, determining popular items, fulfilling customer needs more efficiently, and allowing staff to generate customised reports as requested by the management. To support these functionalities the database should contain data about a particular store, customer, order, flower arrangements components, and accessory items. The following list describes the data requirements in more details. Customer data includes unique customer number, first name, last name, email, phone, and city. One customer can place one or many orders. Order data includes unique order number, order description, order month, order year, order type, order total and store details (number, city, phone and manager) where that order is placed. An order can include one or many order items. Orders can be of three types - Online, phone, and in-person. Order Item data includes various items purchased by the customer for a specific order. It can store information regarding order number, item (Flower arrangement) along with the qty of that item purchased. FlowerArrangement information may include unique arrangement Id, name, style, occasion, and price. A store can sell specific types of flower arrangements. Customers can buy flowers with different arrangements along with a number of accessories. Accessory information includes accessory id, name, and price.

Sample data for the proposed database is provided below: Customer custNumber CustName custEmail custPhone custCity C01 David Smith dsmith.ho.com 12345678 Sydney C02 enter student1 name enter student1 email 34653874 Melbourne C03 enter student2 name enter student2 email 53687632 Perth C04 enter student3 name enter student3 email 47123798 Adelaide C05 enter student4 name enter student4 email 23782834 Sydney

Complete customer table with names and emails for each group member as requested. Retail_Order orderNumber storeNumber storeCity storePhone Manager orderMonth orderYear orderType orderTotal 1500 1 Sydney 27747234 Kim Dec 2019 InPerson $170 1501 3 Perth 45343623 Len Jan 2020 InPerson $400 1502 2 Melbourne 23687326 Bill Feb 2020 Online $450 1503 1 Sydney 27747234 Kim Feb 2020 Online $650 1504 5 Adelaide 76348235 Jill Mar 2020 Phone $250

OrderItem OrderNumber ItemID quantity 1501 F01 2 1501 F02 1 1502 F01 3 1502 F03 2 1503 F03 3 1504 F03 3 1504 F04 3

FlowerArrangement arrangeID arrangeName style occassion arrangePrice F01 Iris Vase Anniversary $80 F02 Orchids centrepiece Congratulations $150 F03 Lilies Hamper Thank you $100 F04 Rainbow Bouquet Wedding $110 F05 Sunflowers Boxed Get Well $60

Accessory accessoryID accessoryName accessoryPrice A01 Teddy bear $50 A02 Chocolate $20 A03 Basket $30 A04 Plant $25

Task 1: Introduction 2 marks Introduce the important aspects of the case study. This section should include introduction to the case study in your own words and details of tasks that need to be completed for this report. Task 2: Develop logical data model (ERD and Normalisation) 3 marks Identify all entity types, attributes, relationships for above case study and develop an ERD to showing minimum and maximum cardinalities for each relationship. Resolve many to many relationships, if any. Convert the ERD in to tables with all attributes and keys. Identify anomalies in the tables and apply normalisation rules to convert tables into BCNF. Describe the process of normalising the tables at each stage for the given database. Task 3 : SQL (DDL and DML Scripts) 8 marks Write a database script with CREATE statements to create all tables identified in above tasks. Make sure primary key, foreign key, not null and other constraints are included in table creation scripts. Write a database script with INSERT statements to populate these tables with the sample data following the relational requirements. For customer table, you must fill in the details for customer name and email as the name and email for each group member. Write SQL statements to fulfil the following requirements and provide the screenshot of the output: Display details of all flower arrangements that are sold at Fragrance Flowers group. Display details of all customers from Melbourne whose first name starts with P. Display details of all orders that have been placed online. Display order number and description (type, total etc.) for orders that are placed at Sydney stores. Write an SQL statement to display details of all stores that have been managed by either Len, Kim or Shen. Display number of customers for all cities. Display sum of order total for February month. Display the order number, flower arrangement ID (arrangeID) and total price for orders that have been placed by customers in 2020. Display the result in ascending order of order number. Create a view which shows a list of all order items that have order total in the range of $100 to $300. This view should display the order number, order month and order year for these order items. Remove duplicate records. Write a procedure that displays flower arrangement details such as name, style occasion and price that have been ordered online and quantity ordered is more than 2.

Task 4: Conclusion and Recommendation 2 marks Clearly define and describe your findings in your own words related to the given case study. Provide recommendations for backing up and securing database for Fragrance Flowers Group.