PLEASE ANSWER ONLY TASK 5 ( PLEASE PROVIDE 5 TO 7 SLIDES OF PRESENTATION HANDWRITTEN)

Task 5: Group and Individual Presentation 5 marks

1. Prepare a presentation with 5-7 slides summarising the tasks completed for this report. To complete this presentation, each group member needs to prepare at least one slide with their student id and name along with the task details completed by them. )

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. The 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 the 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 customized 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 detail.

• 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.
• flower arrangement 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 several accessories. Accessory information includes accessory id, name, and price.

Sample data for the proposed database is provided below:

Customer

cucumber	CustName	just email	just phone	causticity
C01	David Smith	@l.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.

Task 1: Introduction 2 marks

1. Introduce the important aspects of the case study. This section should include an 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

1. Identify all entity types, attributes, relationships for the above case study and develop an ERD to showing the minimum and maximum cardinalities for each relationship. Resolve many to many relationships, if any.
2. Convert the ERD into tables with all attributes and keys.
3. Identify anomalies in the tables and apply normalization rules to convert tables into BCNF. Describe the process of normalizing the tables at each stage for the given database.

Task 3: SQL (DDL and DML Scripts) 8 marks

1. Write a database script with CREATE statements to create all tables identified in the above tasks. Make sure primary key, foreign key, not null, and other constraints are included in table creation scripts.
2. Write a database script with INSERT statements to populate these tables with the sample data following the relational requirements.

Fo

3. Write SQL statements to fulfill the following requirements and provide the screenshot of the outpuT

Task 4: Conclusion and Recommendation 2 marks

Task 5: Group and Individual Presentation 5 marks

1. Prepare a presentation with 5-7 slides summarising the tasks completed for this report. To complete this presentation, each group member needs to prepare at least one slide with their student id and name along with the task details completed by them.