ICT285 Databases Assignment 1 | Sample Questions & Answers – A fitness centre maintains a database of information about the various classes, Singapore

Assignment Details:

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 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. Each question is worth 2 marks.

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 (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, 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. Provider No 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.

Each question is worth 2 marks.
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 (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 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. 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 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 (20 marks)
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. Each question is worth 5 marks
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.