only question 5

ICT28 5 Databases S2 201 8 Assignment 1 Worth : 20% of your final grade. Due : Sunday, 16 September 2018, 11:55 PM Submit to : LMS, via the Assignments tool. Submit as a SINGLE Word document including all parts of the assignment . Ensure you complete the declaration that is part of the submission process. You do not need to include a separate cover sheet but you should include your name and student number as part of your document filename. Your name and student number should als o be included within in the assignment document. Late assignments that do not have an extension will be penalised at the rate of 5% per day or part of day . For example, if your original mark was 75% and your assignment was 2 days late, your final mark wi ll be 65%. This is an INDIVIDUAL assignment. Th is assignment requires you to answer a number of questions on relational database principles and SQL, and to design a database based on a case study. The assignment addresses the following learning outcomes for the unit: LO 2. Demonstrate an understanding of relational database principles and theory LO 3. Demonstrate practical skills in using SQL LO 4. Demonstrate practical skills in data modelling using entity -relationship modelling LO 5. Demonstrate practical skills in normalisation and convert a conceptual database design to a logical design in 3NF (partly) Marks are distributed as follows: Question 1: Relational algebra 20 Question 2: SQL Select queries 20 Question 3: Further SQL 15 Question 4: Normalisation 20 Question 5: Conceptual design 25 Total 10 0 Question 1: Relational algebra (20 marks) A fitness centre maintains a database of information about the various classes that are available for members of the centre to take . Each class (Zumba, Pilates, Aqua Fit, etc) may offer several sessions per week. Each session is led by a qualified instructor at a particular day and tim e, and is held in one of the centre’s venues. Each session has a limited number of places available and members must sign up for a session . Basic Information about members, instructors, classes and venues are also h eld in the database. The schema for this database is as follows : (note that primary keys are shown underlined , foreign keys in bold ). MEMBER ( MemberID , MemberName, MemberEmail) CLASS (ClassName , Description, SuitableFor ) INSTRUCTOR ( InstructorName , InstructorEmail, Specialty ) VENUE ( VenueName , Capacity) SESSION (SessionID , DayAndTime, NumberOfPlaces, ClassName , VenueName , InstructorName ) PARTICIPANT (Session ID, MemberID ) Provide relational algebra (NOT SQL) queries to find the following information . Each question is worth 2 marks. NOTE:  You can use the symbols , , *, etc or the words ‘RESTRICT’ , PROJECT’ , etc as you prefer .  Use nested brackets or intermediate relations, as you prefer.  You do not need to try to make efficient queries – just correct ones.  Where you use a join, always show the join condition. a. List the name and specialty of all instructors . b. List the class name and description of all classes suitable for ‘Over 60s’ . c. List the names of instructors who run sessions in the class es ‘Aqua Flex’ , ‘Aqua Deep’ , or both. d. List the names of all members who participated in a class held in Studio 5 on 28/8/2018 , and the name of the class they participated in. e. List the names of members who participated in any class with a venue capacity greater than 20. f. List the details of all sessions running on 28/8/2018 , and the names of the members who have signed up for each of them ( if any ). g. List the name s of any instructors who run sessions in both Studio 2 and the Warm Water Pool. h. List the names of members who have either participated in classes for the Over 60s, classes held in the Warm Water Pool, or both. i. List the names of the members who have NOT participate d in a ‘Yoga’ class. j. List the name s of members who have participated in all of the different classes offered. Question 2: SQL – SELECT queries (20 marks) This question is based on the tables listed below, which describe a simple medical billing system . PATIENT ( PatientID , FamilyName, GivenName, Address, Suburb, State, PostCode) DOCTOR ( ProviderNo , Name) ITEM ( ItemNo , Description, Fee) ACCOUNT ( AccountNo , ProviderNo , PatientID , Treatment Date) ACCOUNTLINE ( AccountNo , ItemNo ) (Primary key , foreign key ) The PATIENT table co ntains data about patients treated at a typical medical surgery. The details of the Doctors are contained in the DOCTOR table . ProviderNo is a unique code allocated to each registered medical practitioner in Australia. The ITEM table contains the details o f treatment items, including the ItemNo which is a unique code allocated to each treatment item. When a patient visits the Doctor, an ACCOUNT is created. A Patient can only be treated by one Doctor on a particular visit. An Account can have several ACCOUN T LINE s, each of which lists the item number of the treatment provided. A Patient can have more than one account on a day. The tables have been created by dtoohey and you will be able to access them on Oracle. If you prefer, you can create your own copies of these tables under your own account to work with.

If you do so, you should ensure you use the same sample data as in dtoohey’s tables.  Provide SQL AND result tables for the following queries. Use only the information provided in the question in your solutions.  Paste the queries and the result tables fro m either your SSH client or SQL Developer into your assignment document. You can use a screen dump for the result tables, but NOT for the SQL. Each question is worth 2 marks. a. Family name and suburb of patients who live in the State named ‘WA’ . b. Family name and suburb of patients who live in the State named ‘WA’ or the State named ‘SA’, in alphabetical order of family name. c. Name and suburb of pa tients who live in the State named ‘WA’ and have been treated by Dr Ima or Dr Barbara . d. Name and suburb of patients treated by Dr Brian but not Dr Barbara e. Number of different suburbs covered by each doctor. f. f. The name of each doctor, and the total fees c ollected from visit to each of them in each year. Your answer should be presented in order of doctor name followed by year. g. Item Description and the treatment date of all treatments for any patients named Betty Eggert (i.e., Given name is Betty , family name is Eggert ) h. Doctors who have had more than the average number of consultations i. Total amount of fees collected for each type of consultation in each state, in alphabetical order of state. j. Patient ID and family name of patients who have had all types of treatments Question 3: Further SQL (15 marks) You have been given the following specifications of a simple database for keeping track of exercise sessions and their instructors at a fitness centre (note that primary keys are shown underlined , foreign keys in bold ). You should run your SQL to demon strate that it works correctly, a nd paste in the statements used plus the output from Oracle. INSTRUCTOR ( InstructorName , InstructorEmail, Biography, Specialty) SESSION S (SessionID , DayAndTime, NumberOfPlaces, ClassName , VenueName , InstructorName ) Based on the table specifications provided, answer the following questions. Each question is worth 3 marks. a. Give the SQL to create the INSTRUCTOR table. Choose appropriate data types. None of the attributes should be allowed to be null. Include the primary key constraint. b. Give the SQL to create the SESSION S table. Use appropriate data types, and include the primary key and foreign key constraint s. Referential integri ty should be set such that if an Instructor is deleted from the database, any sessions that she or he is running will also be deleted. c. Give the SQL to add your own record to the INSTRUCTOR table. Include your name and email, and make up appropriate entrie s for your Biography and Specialty. d. Give the SQL to create a constraint to the SESSION S table to restrict the possible venues to the following 5: Main Group Fitness Studio , Indoor Cycle Studio , Mind and Body Studio , 25m Lap Pool, Warm Water Pool . e. Give the SQL to record the fact that all the sessions of the GymFit class have increased their number of places by 5. ( Note : you don’t need to add any actual data to run the query, although you may do so if you wish.) (Note: by convention table names are singular, but as SESSION is an Oracle reserved word, we use SESSIONS here.) Question 4: Normalisation (20 marks) The following question is based upon the FINA relat ion below that lists details of world records in men’s and wom en’s individual swimming events, as ratified by the sport’s governing body. You can assume the data is consistent . You have been asked to design a relational database based on this design. You know that there are problems with the current design and that it will need to be modif ied in order to work effectively. Answer the following questions . Each question is worth 5 marks a. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit. b. What is the candidate key(s) of the relation? What normal form is the relation currently in? Explain your reasoning. c. 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 cre ate any new attributes. Give each of your new relations an appropriate name. Show all primary keys and foreign keys. d. Explain how your new design addresses the problems you identified in (a). Also d emonstrate that it your set of relations has the dependenc y preserving and lossless join properties. FINA Name Class Event Laps Time RecordDate Nationality Location Country Meet MeetDate Sarah Sjöström Women 50m freestyle 1 23.67 29 Jul 2017 Sweden Budapest Hungary World Championships 23-30 Jul 2017 Sarah Sjöström Women 100m freestyle 2 51.71 23 Jul 2017 Sweden Budapest Hungary World Championships 23-30 Jul 2017 Federica Pellegrini Women 200m freestyle 4 01:53.0 29 Jul 2009 Italy Rome Italy World Championships 17 Jul -2 Aug 2009 Katie Ledecky Women 400m freestyle 8 03:56.5 7 Aug 2016 United States Rio de Janeiro Brazil Olympic Games 5-21 Aug 2016 Katie Ledecky Women 800m freestyle 16 08:04.8 12 Aug 2016 United States Rio de Janeiro Brazil Olympic Games 5-21 Aug 2016 Katie Ledecky Women 1500m freestyle 30 15:20.5 16 May 2018 United States Indianapolis United States TYR Pro Swim Series 16 -19 May 2018 Zhao Jing Women 50m backstroke 1 27.06 30 Jul 2009 China Rome Italy World Championships 17 Jul -2 Aug 2009 Kathleen Baker Women 100m backstroke 2 58 .0 28 Jul 2018 United States Irvine United States USA Championships 25 -29 Jul 2018 Missy Franklin Women 200m backstroke 4 02:04.1 3 Aug 2012 United States London United Kingdom Olympic Games 27 Jul -12 Aug 2012 Lilly King Women 50m breaststroke 1 29.4 30 Jul 2017 United States Budapest Hungary World Championships 23-30 Jul 2017 Lilly King Women 100m breaststroke 2 01:04.1 25 Jul 2017 United States Budapest Hungary World Championships 23-30 Jul 2017 Rikke Møller Pedersen Women 200m breaststroke 4 02:19.1 1 Aug 2013 Denmark Barcelona Spain World Championships 1–2 Aug 2013 Sarah Sjöström Women 50m butterfly 1 24.43 5 Jul 2014 Sweden Boras Sweden Swedish Championships 5-6 Jul 2014 Sarah Sjöström Women 100m butterfly 2 55.48 7 Aug 2016 Sweden Rio de Janeiro Brazil Olympic Games 5-21 Aug 2016 Liu Zige Women 200m butterfly 4 02:01.8 21 Oct 2009 China Jinan China Chinese National Games 16 -28 Oct 2009 Katinka Hosszú Women 200m individual medley 4 02:06.1 3 Aug 2015 Hungary Kazan Russia World Championships 2-3 Aug 2015 Katinka Hosszú Women 400m individual medley 8 04:26.4 6 Aug 2016 Hungary Rio de Janeiro Brazil Olympic Games 5-21 Aug 2016 César Cielo Men 50m freestyle 1 20.91 18 Dec 2009 Brazil Sao Paulo Brazil Brazilian Championships 18 -19 Dec 2009 César Cielo Men 100m freestyle 2 46.91 30 Jul 2009 Brazil Rome Italy World Championships 17 Jul -2 Aug 2009 Paul Biedermann Men 200m freestyle 4 01:42.0 28 Jul 2009 Germany Rome Italy World Championships 17 Jul -2 Aug 2009 Paul Biedermann Men 400m freestyle 8 03:40.1 26 Jul 2009 Germany Rome Italy World Championships 17 Jul -2 Aug 2009 Zhang Lin Men 800m freestyle 16 07:32.1 29 Jul 2009 China Rome Italy World Championships 17 Jul -2 Aug 2009 Sun Yang Men 1500m freestyle 30 14:31.0 4 Aug 2012 China London United Kingdom Olympic Games 27 Jul -12 Aug 2012 Kliment Kolesnikov Men 50m backstroke 1 24 .0 4 Aug 2018 Russia Glasgow United Kingdom European Championships 3-4 Aug 2018 Ryan Murphy Men 100m backstroke 2 51.85 13 Aug 2016 United States Rio de Janeiro Brazil Olympic Games 5-21 Aug 2016 Aaron Peirsol Men 200m backstroke 4 01:51.9 31 Jul 2009 United States Rome Italy World Championships 17 Jul -2 Aug 2009 Adam Peaty Men 50m breaststroke 1 25.95 25 Jul 2017 Great Britain Budapest Hungary World Championships 23-30 Jul 2017 Adam Peaty Men 100m breaststroke 2 57.1 4 Aug 2018 Great Britain Glasgow United Kingdom European Championships 3-4 Aug 2018 Ippei Watanabe Men 200m breaststroke 4 02:06.7 29 Jan 2017 Japan Tokyo Japan Kosuke Kitajima Cup 28 -29 Jan 2017 Andriy Govorov Men 50m butterfly 1 22.27 1 Jul 2018 Ukraine Rome Italy Sette Colli Trophy 29 Jun -1 Jul 2018 Michael Phelps Men 100m butterfly 2 49.82 1 Aug 2009 United States Rome Italy World Championships 17 Jul -2 Aug 2009 Michael Phelps Men 200m butterfly 4 01:51.5 29 Jul 2009 United States Rome Italy World Championships 17 Jul -2 Aug 2009 Ryan Lochte Men 200m individual medley 4 01:54.0 28 Jul 2011 United States Shanghai China World Championships 27 -28 Jul 2011 Michael Phelps Men 400m individual medley 8 04:03.8 10 Aug 2008 United States Beijing China Olympic Games 9-10 Aug 2008 Data adapted from https://en.wikipedia.org/wiki/List_of_world_records_in_swimming Question 5: Conceptual Design (25 marks) Use the case study description and list of requirements below to create an entity -relationship diagram showing the data requirements of the GardenSwap database. Your ERD should be able to be implemented in a relational DBMS. Everyone loves a garden, but not everyone loves gardening (or all aspects of it anyway). For the last couple of years, Zamia Orangeblossom has been swapping gardening chores with her neighbours.

She maintains her neighbour Bill’s reticulation in return for him doing tree -lopping for h er garden, and mows her other neighbour Jen’s lawn in return for home -grown tomatoes. Bill and Jen also have their own arrangements: Bill lends Jen his trailer in return for Jen pruning his roses. In this way everyone gets their gardening jobs done, and e xpertise is shared among the neighbours. Zamia is now thinking of expanding this network of swapping so that more garden enthusiasts in her local area can participate. She needs a database (which will be available from the web) to enable people to search f or others offering particular services, and to indicate the services they offer in return. The only rules are that no money is to be involved, and all the skills/equipment/services involved must be to do with gardening. (This rule allows her to include Je n’s famous carrot cake.) Zamia has drafted the following requirements for the database: Gardeners who want to participate must all register as members on the GardenSwap website, listing their name, street address, suburb, email and contact number. They als o write a brief biography about their gardening interests. They must post at least one service they are prepared to offer before they can request a service. Services may be anything gardening related: the main categories are maintenance jobs such as mowing, pruning, weeding, mulching, watering, planting and sweeping; pickup and delivery services such as collecting plants from the nursery, taking clippings to the council composting centre or taking rubbish to the tip; equipment loan (such as chainsaws, lawnmowers and mulchers); and landscaping services such as garden design or paving. Members also indicate when the service they offer is available: this could be quite specific such as “only in September -October” or more general such as “an y Sunday” or “by arrangement”. Members who are looking for someone to undertake a task for them post a request to the GardenSwap database indicating what they need. They include the broad category and a more detailed description of the task (e.g. “I have 6 overgrown citrus trees that need pruning”) and an approximate date (“by 15 August” or “as soon as possible”). The database then returns a shortlist of suitable people. The requester selects someone from the list who needs a skill they themselves are off ering, contacts them via email or phone, and, if both parties agree, logs the swap on the database. The members and tasks involved in the swap and the dates they are to be carried out are recorded. (It is likely that the two swapped tasks would be done on different days.) When each task is completed, the member for whom the task was done logs it as closed, and when both tasks in the swap are closed the swap is completed. Sometimes there is nobody in the database who has listed suitable skills for a posted request, but there is someone who could do the requested task. Members often scan through the database looking for open requests and approach the requester directly. A swap is arranged and the tasks logged in the usual way by the parties involved. A swap is always between only two parties, but Zamia has found that some members of the GardenSwap community have skills or other services that could be offered to groups of members, such as demonstrating how to set up a worm farm, or holding ‘open garden’ days. She would like to advertise these through GardenSwap as well. Members can post an event to GardenSwap, giving a title, description, date, location, and number of places available. Other members can then sign up for the event, up to the limit of places. No swapping is required for participating in the events. Below are several queries and reports that the GardenSwap database must be able to support. There may well be many others as Zamia and her neighbours analyse their venture and plan for the future; ther efore, you should design for flexibility as well as ensuring your ERD could answer these questions. 1. All the members represented in the database , and the skills they offer. 2. The number of members offering lawn mowing . 3. The number of different services offer ed in each category . 4. All members wanting to hire a chainsaw in September 2018. 5. Members who have completed tasks that aren’t in their recorded categories of skills offered. 6. All currently open requests. 7. All members registered with GardenSwap who haven’t posted any requests yet. 8. All the gardening jobs logged as completed in September 2018 . 9. A list of members who have signed up for Bill’s workshop on composting. 10. Members who have gone to every event held in 2018. What you have to do:  Use the case study description and querying requirements to create an entity -relationship diagram (ERD) for the GardenSwap database . Your ERD should be able to be implemented in a relational DBMS.  List and explain any assumptions you have made in creating the data model.  You should use the crow’s feet ERD notation we have been using in the lectures, and should include a legend to explain the notation. You should include attributes in the ERD, and indicate primary and foreign keys . The use of a drawing to ol such as Visio will make this task easier.  Whichever tool you use, you must copy and paste the ERD into a word -processed document. This is because your tutor might not have access to the tools you have used. Please make sure the labels and symbols in y our ERD are readable . Enlarge them from the default setting if necessary.  Please note that hand -drawn ERDs are not acceptable. Some important things to note:  You don’t have to create the database or any of the reports /queries at this point . However, Assignment 2 will involve creating the database from your design, so you should be satisfied that it will work with at least the queries shown .  You should make any assumptions that are required, but must state them clearly. Obviously, your assumptions sho uld not contradict any of the information already provided.  Part of understanding a system at suffici ent enough detail to model well involves asking questions . If you are not sure about some detail of the case study, you should ask on the Discussion Forum in LMS. You can subscribe to the discussion forums so that you don’t miss any messages.