- 作业标题：FIT9132 Introduction to Databases - Assignment 2B Database - Monash New Smile
- 课程名称：Monash University - FIT9132 Introduction to Databases
- 完成周期：1天
Purpose
Students will be asked to use SQL and NoSQL to write queries to produce
specified output. This task covers learning outcomes:
- Apply the theories of the relational database model.
- Manage data that meets user requirements, including queries and
transactions. - Contrast the differences between non-relational database models and the
relational database model.
Your task
This is an open book, individual task. The final output for this task will be a set of
relational (RA and Oracle) and non relational (MongoDB) queries which meet the
user requirements.
Value
35% of your total marks for the unit
Due Date
Monday, 30th October 2023, 23:55
Submission
● Via Moodle Assignment Submission
● FIT GitLab check ins will be used to assess history of development
Assessment Criteria
● Application of relational algebra operations to produce outputs that meet
user requirements
● Application of SQL select statements to produce outputs that meet user
requirements.
● Mapping of relational database data into non relational database data
structure.
● Application of MongoDB operations to produce outputs that meet user
requirements.
Late Penalties
● 10% deduction per calendar day or part thereof for up to one week
● Submissions more than 7 calendar days after the due date will receive a
mark of zero (0) and no assessment feedback will be provided.
Support Resources
See Moodle Assessment page
Feedback
Feedback will be provided on student work via:
● general cohort performance
● specific student feedback ten working days post submission
● a sample solution
INSTRUCTIONS
Monash New Smile (MNS) provides dental services such as fillings, scalings, extractions etc to its
patients. For each service, MNS records a service code, service description and the MNS standard
fee for this service. The actual fee charged to a patient for a particular service may be varied from
this standard fee. These services are provided by a range of professionals (providers) that MNS
employs. Each provider is assigned a provider code. MNS records the provider’s title, name, the
specialisation, and the room number where they normally treat patients. Each provider may have a
particular specialisation (one only), but some providers may not have any specialisation. Some
services require a particular provider while other services are able to be provided by a number of
providers.
MNS patients are assigned a patient number. The company records the patient’s name, date of
birth, the residential address, contact phone number and contact email address (all patients are
required to provide a contact number (mobile phone) and an email address for the purpose of
confirming appointments). Patients must also provide another person’s name and phone number
for emergency purposes, which is also recorded.
Patients contact Monash New Smile and make appointments to see a provider. Each appointment
is booked with only a single provider, the provider is assigned when the appointment is first
recorded. The system also ensures that patients, providers, and rooms are not double booked (e.g.
a patient cannot have two scheduled appointments at the same date time). There is a possibility
that a patient needs more than one appointment per day (e.g. having an X-Ray procedure in the
morning and coming back in the afternoon for re-evaluation). An appointment can be a follow up of
a prior appointment. MNS records the prior appointment number for each follow up appointment.
For example, appointment number 4 is a follow up of appointment number 1, and appointment
number 31 is a follow up of appointment 4.
An appointment will require one or more services depending on the work required. For an
appointment, MNS records the appointment date and time and the room number in which the
consultation (appointment) will take place - in some circumstances this room may not be the
provider’s normal room, for example, due to the need to use specialist equipment. The reception
staff, based on the patient’s requirements, set an appointment length as either short (30 minutes),
standard (60 minutes) or long (2 hours). The staff who schedule appointments will also ensure that
the allocated provider is available and can provide the services which will be required during this
appointment, your design is not required to enforce this, although these staff must be able to look
up which providers provide which services if required.
Providers require the assistance of one dental nurse during an appointment. Each nurse is
assigned a nurse number - MNS also records the nurse’s name and their contact number. One
nurse is assigned to each appointment. Nurses employed by MNS are hired based on three
employment status: Casual (C), Contract (T), and Fulltime (F).
Each service provided at a particular appointment may require several items, for example fillings
require Porcelain Etch and Silane. MNS records the quantity of items needed in each service
provided at a particular appointment. Each item is assigned a unique identifier. MNS also records
the item’s description, standard cost and the number of items that are currently on hand. This
figure is needed to make decisions about placing item purchase orders.
Based on these requirements a data model has been created for MNS:
。。。(omit)。。。
TASKS
ENSURE your id and name are shown at the top of any file you submit.
GIT STORAGE
Your work for these tasks MUST be saved in your individual local working directory (repo) in the
Assignment 2B folder and regularly pushed to the FIT GitLab server to build a clear history of
development of your approach. A minimum of twelve pushes to the FIT GitLab server is required
(3 pushes per file). Please note twelve pushes is a minimum, in practice we would expect
significantly more. All commits must include a meaningful commit message which clearly
describes what the particular commit is about and must be correctly assigned to your valid
GitLab author.
You must regularly check that your pushes have been successful by logging in to the web interface
of the FIT GitLab server; you must not simply assume they are working. Before submission, via
Moodle, you must log in to the web interface of the GitLab server and ensure your submission files
are present on the GitLab server and that their names are unchanged.
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 as 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.
。。。(omit)。。。
