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.

Task 2: Develop logical data model (ERD and Normalisation) 3 marks

1. 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.
2. Convert the ERD in to tables with all attributes and keys.
3. 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.