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: Question:
List the service code and service description for all services which have a standard fee greater than $200. Answer: R = πservice_code, service_desc (σservice_stdfee>200 SERVICE) OR R1 = σservice_stdfee>200 SERVICE R = πservice_code, service_desc (R1)
List of symbols for copying/pasting as you enter your answers below: project: π, select: σ, join: ⨝, intersect: ⋂, union: ⋃, minus: –
(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 (45 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: – SQL Part II- 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. You must ONLY use the data as provided in the text of the questions. Failure to adhere to this requirement will result in a mark of 0 for the relevant question.
In the 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 may 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. [3 marks]
(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. [4 marks]
(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. [5 marks]
(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.
(e) Given a provider may charge more or less than the standard fee for a service carried out during an appointment, the MNS administration is interested in finding out what variations on the fee have been charged across all appointments. The clinic terms the difference between the average actual charged service fee and the service standard fee as the “Service Fee Differential”. For all services which have been carried out during an appointment, determine the service fee differential. The list should show the service code, description, standard fee and the service fee differential in service code order. For example service D001 Oral Examination has a standard fee of $65.00, it may have been charged on average across all appointments for $75.00 – the fee differential here will be 75 – 65 that is a fee differential +10.00. If the average charge had been say 57.50 the fee differential will be -7.50. [8 marks]
(f) List for all patients the percentage of their appointments which are follow up appointments. For example if a patient has made three appointments, of which two were follow up appointments then this percentage will be 66.7%. Display the patient number, full name, current age in years, number of appointments which have been made and the percentage of those appointments which were follow up appointments. The image below shows the output you are required to produce, you must use the same column headings, data formats and alignment.
List for ALL providers the number of appointments they have had, the total fees they have charged and the total quantity of all items that they have used for appointments which took place between 9 AM on the 10th September 2023 and 5 PM on the 14th September 2023 (inclusive). If a particular provider has had no appointments in this period, or did not collect fees, or used no items the output columns must show a dash (-) The image below shows the output you are required to produce, you must use the same column headings, data formats and alignment (all output is right aligned)
TASK 3: Non Relational Database Queries –
MongoDB (18 marks): Your answers for this task (Task 3) must be placed in the supplied sql file T3-mns-json.sql and the supplied MongoDB script file T3-mns-mongo.mongodb.js
Before attempting this task, drop all tables under your account, then run the supplied mns_simplified_schema_insert.sql to create and populate these seven simplified MNS tables. Write your answers for this task
(Task 4) in the supplied SQL script T4-mns-plsql.sql
(a) Write a stored procedure called prc_insert_app_serv which handles the insert of a new appointment service (i.e. service which is scheduled for an appointment). The procedure only handles one appointment service insertion at a time. The procedure requires:
The procedure must check if the inputted appointment number and service code are valid. Once these values are checked, the procedure must check whether the provider assigned to the appointment is able to provide the service. The structure of the procedure has been provided in the T4-mns-plsql.sql. You must not change this structure (i.e. you must not change the parameter names and order). [9 marks]
For each of these PL/SQL questions, as part of your answer, you must create a set of SQL commands which will demonstrate the successful operation of your trigger/stored procedure (test harness) – these tests are part of the awarded marks for each question. Place these commands below your trigger/stored procedure definition for each of the tasks. You may do manual look up when writing the test harness. Ensure your trigger/stored procedure definition finishes with a slash(/) followed by a blank line as detailed in the week 10 workshop and week 11 applied class. In addition, when coding your triggers/procedure, you must provide output messages where appropriate.
Seeking top-notch assistance for your SQL Assignment help at Taylor's University (TU) in Malaysia? Malaysian students, look no further! We're here to provide you with expert guidance and support. Our tailored services include assignment assistance in Malaysia, offering comprehensive solutions for summative assignments and Research Paper Writing Help In Malaysia. Don't let the intricacies of Relational Database Queries and SQL overwhelm you. You can now pay our experts to navigate your coursework successfully, ensuring your triumph in your SQL Assignment at TU, Malaysia. Take the next step toward academic excellence with our dedicated support!
