In a school sports day, a fun activity is being planned for the parents. Any parent can participate in the activity but they must first register. They must provide their name, their child's name (only one) and class in which the child studies. Once they register, they are provided with a unique participant number. The participants are divided into 12 different teams. There could be any number of participants in a team. The participant is also assigned a team number at the time of registration. He/ She is also informed about his team slogan which is stored in the database. The fun activity is a timed competition among the teams. The details of the competition activities and final results are also stored in the database. The description of what the competition is all about is as follows: There is a circular track with 12 activity points, spread out like hours in a clock. The name and description of each activity is stored. Also, each activity point is randomly, but uniquely numbered. So, it has to be recorded alongside the activity details, which activity is to be performed next. The activities are divided into 3 categories: land, water and air. For the land activity, the size of the plot needed is stored in square meters. Also, if sand is required or not. For the water activity, it is recorded, if the activity needs a tank, buckets or cups. Also the size of the tank and number of buckets/ cups needed is stored. For the air activity, the height of the equipment required is stored in the database. Each team starts at one activity point. It is recorded which team starts at which point. They get a maximum of 10 minutes to complete the activity. If a team finishes earlier, their actual time is recorded. After 10 minutes, each team must move on to the next activity point. The final results are determined on the basis of minimum total time spent to finish all the activities by a team. The prizes are also given for each activity point separately. aw an ER diagram for the case described above. The ER diagram should at least contain: - all entities - all attributes described in the case (also mention identifiers that you introduced) - all relationships - maximum (and minimum if applicable) cardinalities applicable, write down any assumptions that you made. An assumption may not be in contradiction with given text. Exercise 2 Database design/ reading the ERD [ 6+19 points] Given below is an ER diagram. This ERD is for a database that will be used by the competition leader of the national soccer association. He will use the database to store all the details of competitions, teams, players, group matches and knockout matches. The given ERD represents only a part of the whole system. a) Write 'TRUE' if the statement is true and 'FALSE' if the statement is false. Base your answer on the ERD only. 1. It is possible to schedule a team for 2 matches in one CompetitionWeek. 2. For a GroupMatch a ScoreHome is not recorded in the database. 3. A player can play at the same time for different teams. 4. A team can meet a team from another competition in a GroupMatch 5. A KnockOutMatch is always followed by a next match. 6. Teams with the same TeamColors can't meet each other in a Match. b) Give the Relational Model (Database Design) of the given ER diagram. Use the example format below to specify all details for all tables of the design: xercise 3 Constraints [ 5 points] When implementing this information system a few business rules must be taken into account. Indicate each constraint with its type (Attribute, Tuple or Row, Table, Database, Dynamic). I. A Team cannot meet itself in a game. (It has to be a different Team) II. TeamColors is any combination of 2 colours out of: Red, White, Blue, Orange, Black, Yellow, Green, Purple. III. Only players of 16 years old and above can be selected for a team. IV. Groupmatches are between 2 teams of the same competition V. Each competition has its own unique name. Exercise 4 Normalisation [21 points] Given below is one of the recipes from a recipe book. Design a normalized database (in 3NF), to store all the recipes.. Give the 3NF. Optionally you can also give the ONF, 1NF and 2NF. For the steps to get to the 3NF, see the appendix. If you need to make assumptions, write them down on the exam paper. RECIPE 23 Molokhia Alternative names: 1. Mulukhiyah 2. Molokheyya 3. Mulukhivvah Prepared by: 23097 Medhat Riad Molokhia is a very popular dish in Egypt. It's a viscous (even slimy) green soup that is commonly eaten with rice and chicken (originally with rabbit). Ingredients: - Chicken broth (Vegan alternative: vegetable broth) - Frozen Molokhia - Finely chopped (not whole leaves) (in Moroccan/Turkish supermarket) - Dry coriander - Garlic (a lot - 4-8 big cloves if not more) - Ghee (butter/oil is also ok) - Broken rice (or any non-long grain rice) - Salt and pepper to taste - Chicken or rabbit (optional) - Bouillon cube (optional) Preparation: 1. Finely cut your garlic (or if you're lazy, just use a garlic press. Don't tell my mother!) 2. Melt ghee/butter in a deep pot 3. Add garlic and saut until light golden. You will probably smeil it! 4. Add coriander and stir it in well 5. Add broth (1 liter) 6. Add bouillon cube and dissolve it (optional) 7. Turn heat down to medium and add the frozen block of molokhia 8. Stir every now and then until it completely melts 9. In the meantime, cook your rice according to instructions 10. Cook chicken/rabbit in any way you prefer (optional) 11. Optional: Add more water/broth if you think it's too thick. (Officially, it has to be thick enough to be stringy) 12. DO NOT let it boil. That one is true. Turn the heat off when it starts to bubble. 13. Add salt and pepper to taste 14. Serve with rice (and chicken/rabbit) Describe the tables you found using the following format: (next page) As part of the work to implement a database we need to create a table with following characteristics: ORDERLINE (, LineNumber, Quantity, PartNumber>\} All fields are mandatory and of type integer. Amongst others, the tables and PART (PartNumber, Description, Unitprice, SupplierNumber>) have been already created. When you need to make assumptions to answer the questions below, write them down along with your solutions! a) Give the SQL statement(s) to create the table ORDERLINE including all constraints etc. b) Give the SQL statement(s) to add todays' order 234 for client 567 to the database. We are opening a second location of our store. From now on we need to add the location-id ( V ' or 'W') to all recorded orders. All existing orders are serviced from location ' V. To accomplish this we added the following table to our database: LOCATION Name, Address, Phone) We also added the records for locations ' V ' and 'W'. c) Give the SQL statement(s) which enable us to record the location-id of an order. d) Give the SQL statement(s) to add all already recorded orders to location 'V