ICT200 Database Design and Development | My Assignment Tutor

ICT200 Assessment 4 Group ReportT3 2020: ICT200 Database Design and DevelopmentAssignment 4 – Group ReportSubmission Requirements:This is a group assessment task with 3-4 students in a group. Students will submit one groupreport (1000 words) detailing the tasks carried out in the assignment. Report must be wellwritten 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 area multi-generational family business running for last 50 years focussing on providing everydayfresh and quality flowers to customers through different cities in Australia. They also providethe option to add accessory items such as teddy bears, chocolates or a vase along with theflower purchase. Recently they are planning to replace their current manual system bydesigning a new database system that will make the online flower ordering experience moreconvenient and hassle-free.A simplified database should support procedures such as checking the inventory of allcomponents for a particular arrangement in different store locations, determining popularitems, fulfilling customer needs more efficiently, and allowing staff to generate customisedreports as requested by the management. To support these functionalities the databaseshould contain data about a particular store, customer, order, flower arrangementscomponents, 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) wherethat order is placed. An order can include one or many order items. Orders can be ofthree 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) alongwith 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.ICT200 Assessment 4 Group Report• Customers can buy flowers with different arrangements along with a number ofaccessories. Accessory information includes accessory id, name, and price.Sample data for the proposed database is provided below:Customer custNumberCustNamecustEmailcustPhonecustCityC01David [email protected] student1nameenter student1email34653874MelbourneC03enter student2nameenter student2email53687632PerthC04enter student3nameenter student3email47123798AdelaideC05enter student4nameenter student4email23782834Sydney Complete customer table with names and emails for each group member as requested.Retail_Order orderNumberstoreNumberstoreCitystorePhoneManagerorderMonthorderYearorderTypeorderTotal15001Sydney27747234KimDec2019InPerson$17015013Perth45343623LenJan2020InPerson$40015022Melbourne23687326BillFeb2020Online$45015031Sydney64264525ShenFeb2020Online$65015045Adelaide76348235JillMar2020Phone$250 ICT200 Assessment 4 Group ReportOrderItem OrderNumberItemIDquantity1501F0121501A0111502F0131502A0221503F0331504F0331504F043 FlowerArrangement arrangeIDarrangeNamestyleoccassionarrangePriceF01IrisVaseAnnivaersary$80F02OrchidscentrepieceCongratulations$150F03LiliesHamperThank you$100F04RainbowBouquetWedding$110F05SunflowersBoxedGet Well$60 Accessory accessoryIDaccessoryNameaccessoryPriceA01Teddy bear$50A02Chocolate$20A03Basket$30A04Plant$25 ICT200 Assessment 4 Group ReportTask 1: Introduction 2 marksa. Introduce the important aspects of the case study. This section should includeintroduction to the case study in your own words and details of tasks that need to becompleted for this report.Task 2: Develop logical data model (ERD and Normalisation) 3 marksa. Identify all entity types, attributes, relationships for above case study and develop an ERDto showing minimum and maximum cardinalities for each relationship. Resolve many tomany relationships, if any.b. Convert the ERD in to tables with all attributes and keys.c. Identify anomalies in the tables and apply normalisation rules to convert tables intoBCNF. Describe the process of normalising the tables at each stage for the givendatabase.Task 3 : SQL (DDL and DML Scripts) 8 marks1. Write a database script with CREATE statements to create all tables identified in abovetasks. Make sure primary key, foreign key, not null and other constraints are included intable creation scripts.2. Write a database script with INSERT statements to populate these tables with the sampledata following the relational requirements.For customer table, you must fill in the details for customer name and email as the nameand email for each group member.3. Write SQL statements to fulfil the following requirements and provide the screenshotof the output:I. Display details of all flower arrangements that are sold at Fragrance Flowers group. II.III.IV.V.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 for orders that are placed at Sydney stores.Write an SQL statement to display details of all stores that have been managed by eitherLen, 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 thathave 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 $100to $300. This view should display the order number, order month and order year for theseorder items. Remove duplicate records.VI.VII.VIII.IX. X. Write a procedure that displays flower arrangement details such as name, style occasion andprice that have been ordered online and quantity ordered is more than 2.ICT200 Assessment 4 Group ReportTask 4: Conclusion and Recommendation 2 marksa. Clearly define and describe your findings in your own words related to the given casestudy.b. Provide recommendations for backing up and securing database for Fragrance FlowersGroup.Task 5 : Group and Individual Presentation 5 marksa. 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 slidewith their student id and name along with the task details completed by them.


Leave a Reply

Your email address will not be published.