Convincing Features
Assignment Type
Subject
Uploaded by Malaysia Assignment Help
Date
TASK 1: Relational Database Queries – Relational Algebra (9 marks):
Your answers for this task (Task 1) must be written in an MS Word document or Google document. Once you have completed all questions, download or print the document as a pdf file and name the file T1-mns-ra.pdf. For this task, you are required to write the relational algebra operations for the following queries (your answer must show an understanding of query efficiency). Example:
(a) List the id and description of all items which have never been used in any appointment service.
(b) List the patient number, patient first name, patient last name, emergency contact first name, emergency contact last name and emergency contact phone number of all patients who live in a city named Mooroolbark and had appointment/s on 08 September 2023.
[3 marks]
(c) List the number, first name, last name and email address of all patients who have been attended by endodontists (i.e. providers who specialise in ENDODONTICS).
TASK 2: Relational Database Queries – SQL (63 marks):
Before attempting this task, drop all tables under your account.
In arriving at your solutions for Task 2 you are ONLY permitted to use the SQL structures, syntax and functions which have been covered within this unit:
● Week 8 Workshop and Week 9 Applied – SQL Part I – Basic
● Week 9 Workshop and Week 10 Applied – SQL Part II- Intermediate
● Week 10 Workshop and Week 11 Applied – SQL Part III – Advanced
SQL syntax and commands outside of the covered work, as detailed above, will NOT be accepted or marked. You are NOT permitted to:
● manually lookup an attribute/s in the database to obtain any value,
● manually calculate values (including dates/times) external to the database, e.g. on a calculator and then use such values in your answers. ALL necessary calculations must be carried out as part of your SQL code,
● assume any contents in the database – rows in a table are potentially in a constant state of change, or
● use views and/or PL/SQL
Your answers must recognise the fact that you have been given only a small sample snapshot of a multiuser database, as such you must operate on the basis that there will be more data in all the tables of the database than you have been given. Your answers must work regardless of the extra quantity of this extra “real” data and the fact that multiple users will be operating in
the tables at the same time. You must take this aspect into consideration when writing SQL statements.
In the Monash Oracle database, this Monash New Smile set of tables has been created under the user “MNS”. To use these tables you need to add the prefix “MNS” to the table names that you use in an SQL statement. So, if you want to retrieve data from PROVIDER table you need to write
select * from mns.provider;
Your answers for this task (Task 2) must be placed in the supplied SQL script T2-mns-select.sql. You are only permitted to code a single select statement for each question below. Where you have been supplied with a string contained in italics, such as Orthodontics you must search in the database using the provided string exactly as supplied. Where you need to show a full name, you must not have any extra spaces (e.g. leading space, extra space in the middle of the name).
Sample output showing the form of what you are required to produce is provided. Note this is the form of the output ONLY i.e. the appearance, the data you return will be different.
(a) List the id, description, standard cost and stock of all items which have a stock of at least 50 items (a minimum of 50) and include the word composite in their item description. Show the items with the highest stock first. If two items have the same number of items on hand (stock), order them by their respective item id.
(b) List the provider code and provider name in the form of Title. FirstName LastName (e.g. Dr. Bruce Striplin) for all providers who specialise in the area of PAEDIATRIC DENTISTRY (this is the specialisation description). Order the list by the providers’ last name and within this, if two providers have the same last name, order them by their respective first names, then by their provider codes.
(c) List the service code, description, and standard fee where the service is more expensive than the average standard fee for all services. The output must show the most expensive service first. The service standard fee must be right aligned and displayed with two decimal digits and a leading $ symbol, for example as $250.50. Where two or more services have the same standard fee, order the output by service code.
(d) List the appointment number, appointment date time, patient number, patient full name and appointment total cost (service fee/s + item fee/s) for the most expensive appointment/s. The appointment total cost must be right aligned and displayed with two decimal digits and a leading $ symbol, for example as $2575.50. Where two or more appointments have the same appointment total cost, order the output by appointment number.
"Malaysian students, seeking academic excellence in FIT9132 Introduction to Databases at MUM, Malaysia? We offer top-notch Assignment Assistance and Final Year Project Help to make your journey smoother. Tackling relational database queries and relational algebra in Task 1? Let our experts guide you to success. Pay for expert assistance and focus on your coursework, while we handle the rest. Elevate your academic performance with our support today!"
