Mark: 20 Question 3 - SOL Practical Computer Work: The answer to this question should be submitted electronically Using

[answerhappygod]

Mark 20 Question 3 Sol Practical Computer Work The Answer To This Question Should Be Submitted Electronically Using 1 (36.38 KiB) Viewed 31 times

Mark 20 Question 3 Sol Practical Computer Work The Answer To This Question Should Be Submitted Electronically Using 2 (47.83 KiB) Viewed 31 times

Mark 20 Question 3 Sol Practical Computer Work The Answer To This Question Should Be Submitted Electronically Using 3 (49.37 KiB) Viewed 31 times

Mark: 20 Question 3 - SOL Practical Computer Work: The answer to this question should be submitted electronically Using Mysol, create a single Structured Query Language (SQL) script that answers the below a questions. Include comments to indicate which part of the script answers which question. The script must execute correctly using MySQL to get fullmarks Make use of the following Entity Relationship Diagram (ERD) and Duta Dictionary Author 1. w Paper Paper PC Author F AuthorID (PK) Name Title Surname Description Dale Table: Author Emple Fame Cate| Type Data format 1 Yes Author (PK) Desert bemarley that is the author Name of the other NO Name Surname varchar(100 varcharo BB Smith 300 100 Table: Paper Dute type Duta format R? 3 Yes Albemarley Paperto PRO int 1 Yes AuthorID) int paper For the the wthor of the paper The title of the paper Butter 255 Yes Title varchar(255 155 No Abrasion of the Description varchar(255) Amudy of how butterflies migrates Africa 2020/01/06 No dute YYYY/mm/dd The data that the paper was published Date Pages The Independent battute of Education 2022

e netwo Question 1 - Entity Relationship Diagrams (Marks20) Answer this question in your answer script/answer booklet provided. Draw an Entity Relationship Diagram (ERD) using Unified Modelling Language (UML) notation according to the below business rules. Your design should be at the logical level - include primary and foreign key fields and remember to remove any many-to-many relationships. Tip: Pay attention to the mark allocation shown below. 1. Business rules of a scientific research organisation that wants to keep track of butterfly sightings reported by volunteers on their website: All entities should have surrogate primary keys 2 The name and surname of each user of the website must be stored in the database. 3. When a user signs up, they must provide at least one location where they will be observing butterflies. The user can add as many locations as they want, but each location is specific to the user that adds it For each location, a description as well as the latitude and longitude of each location must be stored in the database. Users report sightings on the website. A sighting of a species of butterfly is reported by a specific user at a specific named location. The date and time of the sighting must also be stored in the database The English name as well as Latin name of each butterfly species must be stored in the database 4. 5. 6. Hint: Think carefully about the number of entities involved in each relationship 2 marks Marks will be awarded as follows: Entities 5 marks Relationships 4 marks Multiplicities 4 marks Primary keys 2 marks Foreign keys Other attributes Correct UML Notation 2 marks 1 mark Total 20 marks The Independent (PMI

Question 2 - Normalisation Answer this question in your answer script/answer booklet provided. Marie The scientific research organisation has already collected a lot of data in a spreadsheet (an extract from the spreadsheet is shown below). The data has been normalised to first normal form already - underlined column names indicate composite primary key columns. User ID User Name User Surname Species Species English Name ID Bob Date Sam 2 1 1 3 5 5 Bob Smith Ngwe kazi Smith Smith Bhebhe Main Colour ID 1 1 2 Bob Sarah African grass blue African grass blue Brown-veined white African monarch African migrant 1 2 Main Colour Name Blue Blue White Orange White 19 Note: The data reflects when a specific user first sighted a specific species. do ad rodi 3 2022/01/28 2022/01/29 2022/02/03 2022/03/01 2022/03/06 2 Normalise the above data to the second normal form (2NF). Show all steps as well as the final answer in the form of dependency diagrams. vd baho od alle toon