Question 1: Relational algebra 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 time, 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 held 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 (SessionID, MemberID) Provide relational algebra (NOT SQL) queries to find the following information.
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 classes ‘Aqua Deep’, ‘Aqua Fit’, or both. d. List the names of all members who participated in a class held in Studio 5 on 19/9/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 30. f. List the details of all sessions running on 19/9/2018, and the names of the members who have signed up for each of them (if any). g. List the names of any instructors who run sessions in both Studio 1 and Studio 2. 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 participated in a ‘Zumba’ class. j. List the names of members who have participated in all of the different classes offered. Question 2: SQL – SELECT queries 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, TreatmentDate) ACCOUNTLINE (AccountNo, ItemNo)
(Primary key, foreign key)
The PATIENT table contains 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 of 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 ACCOUNT LINEs, 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 from 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.
a. Family name and suburb of patients who live in the State named ‘SA’. b. Family name and suburb of patients who live in the State named ‘NSW’ or the State named ‘SA’, in alphabetical order of family name. c. Name and suburb of patients who live in the State named ‘WA’ and have been treated by Dr Brian or Dr Barbara. d. Name and suburb of patients treated by Dr Brian but not Dr Ima. e. Number of different suburbs covered by each doctor. f. 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) g. The name of each doctor, and the total fees collected from visit to each of them in each year. Your answer should be presented in order of doctor name followed by year. 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 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 demonstrate that it works correctly, and paste in the statements used plus the output from Oracle. INSTRUCTOR (InstructorName, InstructorEmail, Biography, Specialty) TIMETABLE (SessionID, DayAndTime, NumberOfPlaces, ClassName, VenueName, InstructorName) Based on the table specifications provided, answer the following questions. 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 TIMETABLE table. Use appropriate data types, and include the primary key and foreign key constraints. Referential integrity 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 entries for your Biography and Specialty.
d. Give the SQL to create a constraint to the TIMETABLE 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 CycleMax 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.) Question 4: Normalisation The following question is based upon the GRAND SLAM relation below that lists details of ‘Grand Slam’ tennis tournaments and their winners over the last few years. You can assume that the data is representative. 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 modified in order to work effectively. Answer the following questions. a. What is the candidate key(s) of the relation? What normal form is the relation currently in? Explain your reasoning. b. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit. 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 create 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 (b). Also demonstrate that your set of relations has the dependency preserving and lossless join properties
Question 5: Conceptual Design Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the GardenShare 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, Astrid Winterblossom has been swapping gardening chores with her neighbours. She maintains her neighbour Bill’s reticulation in return for him doing tree-lopping for her 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 expertise is shared among the neighbours. Astrid 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 for
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/tasks involved must be to do with gardening. (This rule allows her to include Jen’s famous carrot cake.) Astrid has drafted the following requirements for the database: Gardeners who want to participate must all register as members on the GardenShare website, listing their name, street address, suburb, email and contact number. They also 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 “any Sunday” or “by arrangement”. Members who are looking for someone to undertake a service for them post a request to the GardenShare database indicating what they need. They include the broad category, the task name (e.g. ‘pruning’), 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 offering, 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 Astrid has found that some members of the GardenShare 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 GardenShare as well. Members can post an event to GardenShare, 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 GardenShare database must be able to support. There may well be many others as Astrid and her neighbours analyse their venture and plan for the future; therefore, 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 offered 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 GardenShare 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 GardenShare 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 tool 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 your 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 should not contradict any of the information already provided.
● Part of understanding a system at sufficient 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.