Assessment item 4 - Normalisation Value: 10% Due Date: 17-May-2020 Return Date: 09-Jun-2020 Submission method options: Alternative submission method TASK BookingID CheckIn CheckOut RoomNo RoomClass...

i have attached the file.


Assessment item 4 - Normalisation Value: 10% Due Date: 17-May-2020 Return Date: 09-Jun-2020 Submission method options: Alternative submission method TASK BookingID CheckIn CheckOut RoomNo RoomClass RoomBeds GuestName GuestAddress MealType MealPref BookingRating 12398711 20/04/20 11:15 22/04/20 09:10 114 First 2 Bob Sydney Breakfast Cereal 3 12398711 20/04/20 11:15 22/04/20 09:10 114 First 2 Bob Sydney Lunch Chicken 3 12398821 17/06/20 13:10 21/06/20 08:05 812 Economy 1 Alice Ryde Dinner Eggs 4 11674055 02/03/20 16:45 07/03/20 10:35 114 First 2 Jun Mascot Breakfast Chips 5 14424853 11/05/20 09:05 18/05/20 11:00 520 Business 4 Darth Kogarah Breakfast Yogurt 2 14424853 11/05/20 09:05 18/05/20 11:00 520 Business 4 Darth Kogarah Lunch Steak 2 14424853 11/05/20 09:05 18/05/20 11:00 520 Business 4 Darth Kogarah Dinner Kebab 2 Using the above table 1. Draw a dependency diagram that shows all the dependencies that exist in this table. (5 marks) 2. Decompose the relation into a set of 3NF relations. (5 marks) 3. Draw a relational schema for your 3NF relations and show the referential integrity constraints. (5 marks) Notes: · For guidance on how to normalise database relations from a dependency diagram, watch the video How to normalise database relations in 3 quick steps. · Use Draw.io to create your ER Diagrams. · Manually drawing ER Diagrams is NOT acceptable. RATIONALE This assessment task will assess the following learning outcome/s: · be able to analyse a database design and apply normalisation theory and techniques.   MARKING CRITERIA AND STANDARDS Criteria HD DI CR PS FL be able to analyse a database design and apply normalisation theory and techniques. Analysis identifies all PKs and functional, partial and transitive dependencies. Accurately identifies all 3NF relations, including PKs, relevant attributes and referential integrity constraints. Analysis identifies PKs and functional, partial and transitive dependencies with minor omissions. Accurately identifies 3NF relations, including PKs, relevant attributes and referential integrity constraints with minor omissions. Analysis identifies key PKs and functional, partial and transitive dependencies. Identifies key 3NF relations, including PKs and relevant attributes and referential integrity constraints. Analysis identifies key PKs and functional dependencies and draws out relevant 3NF relations. Analysis does not identify key PKs or is missing functional dependencies and relevant 3NF relations. PRESENTATION Submit a single .docx, .odt or .pdf file containing your answers. All ER Diagrams need to be embedded into your submitted file.