hi, thanks for helping! if possible could you type instead of writing so it\'s e
ID: 3747877 • Letter: H
Question
hi, thanks for helping! if possible could you type instead of writing so it's easier to understand? and answer all questions please, thank you!
The following question is based upon the FINA relation below that lists details of world records in men's and women's individual swimming events, as ratified by the sport's governing body. You can assume the data is consistent. Yau have been asked to desigr a relational database based on this design. You know that there are problems with the current design and that it will need to be modified in order to work effectively. Answer thwng questions. Explain the problems with the existing design, in terms of the potential madification anomalies that t might exhibit. What is the candidate key(s] of the relation? What normal form is the relation currently in? Explain your reasoning. Convert the relation to a set of relations in at least Third Normal Form (3NF). You only need to show the schema, not the data. Do not create any new attributes. Give each of your new relations an appropriate name. Shows all primary keys and foreign keys. Explain how your new design addresses the problems you identified in (a). Also demonstrate that it your set of relations has the dependency preserving and lossless join properties. a. b. c. d. Class Laps Ti RecordDate Nationality Location World Championshilps 23-30 Jul 2017 World Championships 23 3D Jul 2017 World Championships 17 Jul-2 Aug 2009 23.67 29 Jul 2017 weden Sarah Sjosträm Women 100m freestyle 2 51723 Jul 2017 Sweden Women200m freestyle 401-53.0 29 Jul 2009 tsly Katie Ledecky Women 400m freestyle0356.5Aug 2016 United States Rio de Janeliro Braz Katie Ledecky Women 300m freestyle | 16 | 08:04.8 | 12 Aug 2016 | United States | Rio de Janeiro Brazil Katie Ledecky Women 1500m freestyle 3015-20.516 May 2018 United States Indianapolis United States TYR Pro Swim Series 16-19 May 2018 Zhao JnR Kathleen Daker Women100m beckstroke58.028 Jul 2018 United States Irvine Missy Frankin Women200m backstroke 4 02:04.13A 2012 United States London Lilly King S-21 AuR 2016 27.06 30 Jul 2009 China World Champlonshlps 17 Jul-2 Aug 2009 United States USA Championships 25-29 Jul 2018 27 Jul-12 Au Women50m breaststroke 129.430 Jul 2017 United States Budapest World Chempionships 23-50 Jul 2017 World Championships 23-30 Jul 2017 World Chempionships 1-2 Aug 2013 100m 201:04.1 25 Jul 2017Unted StatesBudapest Rikke Moller Women 200 4 02 19.1 1 Aug 2013 Denmark breaststroke Sarah Sjostram Women 0m butterfly 2443 5 Jul 2014 Boras Sarah Sjostram Women 100m butterfy 2 55.4B Aug 2016 Sweden Rio de Janeiro Braz Olympic Games 5 21 Aug 2016 Women200m butterfly4 02 01.8 21 Oct 2009 China China Chinese National 16-28 Oct 2009 Katinka Hosszu Women 200 individual 4 02 06,1 Katinka Hz Women400m individualB César Cielo Aue 2015 Hungary World Championships -3 Aue 2015 0426.4Aug 2016 Hungary Braz S-21 Aug 2016 1 20.91 18 De 2009 Drazi 18-19 Dec 200 Céssr Cielo 100m freestyle | 2 | 46.91 30 Jul 2009 | Brazil 200m freestyle | 4 01:42.0 | 28 Jul 2009 | Germany 400m freestyle03:40.1 26 Jul 2009 Germany 800m freestyle16 07:32.129 Jul 2009 Chins 1500m freestyle 30 14:31.0 4 Aue 2012 China 50m backstroke 124.0 4 Aue 201Russia 00m backstroke 5185 13 Aug 2016 United States Rio de laneiro Brazil World Championships 17 Jul-2 Aug 2009 World Championships 17 Jul-2 Aug 2009 World Championships 17 Jul-2 Aug 2009 World Championships 17 Jul-2 Aug 2009 Olympic Games Sun Yane Men London 27 Jul-12 Aue 3-4 Aug 2018 5-21 Aue 2016 17 Jul-2 Aug 2009 Ryan Murphy Men 01:51931 Jul 2009 United States Rome 50m breaststroke | 1 25 95 | 25 Jul 2017 | Great Britain | Budapest World Championships 23-30 Jul 2017 2 57.1 Aug 201Great Britain Glasgow 3-4 Aug 2018 Cha Kosuke Kitsjima Cup 28-29 Jan 2017 Sette Colll Trophy World Championships 17 Jul-2 Aug 2009 World Championships 17 Jul-2 Aug 2009 World Championships 27-28 Jul 2011 Olympic Gemes 02:05.729 Jan 2017 Japan 29 Jun-1 Jul 2018 Michael Phelps Men Michel Phelps | Men Ryan Lochte Michsel Phelps Men 100m butterfly 49.82 1 Aug 2009 United States Rome 200m butterly 4 01:51.5 29 Jul 2009 United States Rome 200m indidu 4 01:54.0 28 Jul 2011 United States Shanghsi 400m indiid 04:03.810 Aug 2008 United States Beijing 9-10 Aug 2008Explanation / Answer
a. Anomalies
Update Anomaly ---- The attributes Name,Class,Nationality,Location ,Country are repeated in the table. So if sports person needs to be in different Lacation,all places need to be updated. if not done,data inconsistency appears.
Delete Anomaly --- If a world record is broken by new sports person, the old record needs to be deleted. So along with the sportsperson data, all event data will also get deleted which is not desired.
b. Candidate Key --- Name,Event
The relation is in First Normal Form as all the attributes are atomic.
c. 3NF
Functional Dependencies:
Name -> Class,Nationality
Event -> Laps,Time,RecordDate,Location,Country
Country,Meet -> MeetDate
So tables in 3NF are :
SWIMMER(Name , Class,Nationality)
EVENT(Event , Laps,Time,RecordDate,Location,Country,Name)
MEET(Country,Meet , MeetDate)
underlined are primary leys and italicised are foreign keys.
d.
After normalization , the problem with updation and deletion will be removed. A SWIMMER,EVENT or MEET can be updated independent of each other.
Also the set of normalized relations has the dependency preserving and lossless join properties.
we can join the set of relations using primary and foreign keys and no data will be lost.
Do ask if any doubt. Please upvote.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.