ICT285 - Databases - Relational Algebra - Select Queries - Further Sol - IT Assignment Help

Assignment Task:

ICT285: Databases IT 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. 
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 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.

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

This ICT285: IT Assignment has been solved by our IT experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.