Database Questions | Online Assignment Help

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.

Don't use plagiarized sources. Get Your Assignment on
Database Questions | Online Assignment Help
Just from $13/Page
Order Now

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.

Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose Us

Quality Papers

At Myhomeworkwriters.com, we always aim at 100% customer satisfaction. As such, we never compromise o the quality of our homework services. Our homework helpers ensure that they craft each paper carefully to match the requirements of the instruction form.

Professional Academic Writers

With Myhomeworkwriters.com, every student is guaranteed high-quality, professionally written papers. We ensure that we hire individuals with high academic qualifications who can maintain our quality policy. These writers undergo further training to sharpen their writing skills, making them more competent in writing academic papers.

Affordable Prices

Our company maintains a fair pricing system for all academic writing services to ensure affordability. Our pricing system generates quotations based on the properties of individual papers.

On-Time delivery

My Homework Writers guarantees all students of swift delivery of papers. We understand that time is an essential factor in the academic world. Therefore, we ensure that we deliver the paper on or before the agreed date to give students ample time for reviewing.

100% Originality

Myhomeworkwriters.com maintains a zero-plagiarism policy in all papers. As such, My Homework Writers professional academic writers ensure that they use the students’ instructions to deliver plagiarism-free papers. We are very keen on avoiding any chance of similarities with previous papers.

Customer Support 24/7

Our customer support works around the clock to provide students with assistance or guidance at any time of the day. Students can always communicate with us through our live chat system or our email and receive instant responses. Feel free to contact us via the Chat window or support email: support@myhomeworkwriters.com.

Try it now!

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Homework Writing Services

My Homework Writers holds a reputation for being a platform that provides high-quality homework writing services. All you need to do is provide us with all the necessary requirements of the paper and wait for quality results.


Essay Writing Services

At My Homework Writers, we have highly qualified academic gurus who will offer great assistance towards completing your essays. Our homework writing service providers are well-versed with all the aspects of developing high-quality and relevant essays.


Admission and Business Papers

With Myhomeworkwriters.com, we will help you secure a position at your desired institution. Our essay writing services include the crafting of admissions papers. We will still help you climb your career ladder by helping you write the official papers that will help you secure a job. We will guide you on how to write an outstanding portfolio or resume.


Editing and Proofreading

Myhomeworkwriters.com has a professional editorial team that will help you organize your paper, paraphrase it, and eliminate any possible mistakes. Also, we will help you check on plagiarism to ensure that your final paper posses quality and originality.


Technical papers

My Homework Writers harbors professional academic writers from diverse academic disciplines. As such, we can develop homework writing services in all academic areas. The simplicity or complexity of the paper does not affect the quality of homework writing services.