FIT9132 Introduction to Databases
Introduction to Databases
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
FIT9132 Introduction to Databases - Assignment 2A
Creating, Populating and Manipulating Database - Monash New Smile
Purpose Students will be asked to implement, via SQL, a small database in the Oracle
RDBMS from a provided logical model case study, followed by the insert of
appropriate data to the created tables. Once populated the database will be used
to carry out specified DML commands and make specified changes to the
database structure via SQL. This task covers learning outcomes:
1. Apply the theories of the relational database model.
3. Implement a relational database based on a sound database design.
4. Manage data that meets user requirements, including queries and
transactions.
Your task This is an open book, individual task. The final output for this task will be a set
of tables and data implemented in the Oracle RDBMS
Value 25% of your total marks for the unit
Due Date Thursday, 12th October 2023, 4:30 PM
Submission ● Via Moodle Assignment Submission
● FIT GitLab check ins will be used to assess history of development
Assessment
Criteria
● Application of relational database principles.
● Handling of transactions and the setting of appropriate transaction
boundaries.
● Application of SQL statements and constructs to create and alter tables
including the required constraints and column comments, populate
tables, modify existing data in tables, and modify the "live" database
structure to meet the expressed requirements (including appropriate use
of constraints).
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
Page 1 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
INSTRUCTIONS
Your task for this assignment is to create, populate and manipulate a database which can be used
to support the activities of a dental clinic called Monash New Smile (MNS).
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).
Page 2 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
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:
The schema/insert file for creating this model (mns_schema_insert.sql) is available in the archive
ass2a_student.zip - this file partially creates the Monash New Smile tables and populates several
of the tables (those shown in purple on the supplied model) - you should read this schema carefully
and be sure you understand the various data requirements. You must not alter the schema file
in any manner, it must be used as supplied. Please note the yellow tables will not be used in
any manner in this assignment and should be ignored (they will be used in Assignment 2B).
Steps for working on Assignment 2A
1. Download the Assignment 2A Required Files (ass2a_student.zip) archive from Moodle
2. Extract the zip archive and place the contained files in your local repository in the folder
/Assignments/Ass2A. Do not add the zip archive to your local repo. Then add, commit and
push them to the FITGitLab server.
3. Run mns_schema_insert.sql
4. Write your answer for each task in its respective file (e.g. write your answer for Task 1 in
T1-mns-schema.sql and so on).
5. Save, add, commit and push the file/s regularly while you are working on the
assignment
Page 3 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
6. Finally, when you have completed all tasks, upload all required files from your local
repository to Moodle. Check that the files you have uploaded are the correct files (download
them from Moodle into a temporary folder and check they are correct). After you are sure
they are correct, submit your assignment. Note that the filenames must not be changed -
you must submit files with exactly the same names as those supplied in the provided
archive (ass2a_student.zip).
The final SQL scripts you submit MUST NOT contain SPOOL or ECHO commands (you may
include them as you work but must comment them out before submission). Please carefully read
the Marking Guide on pages 16 and 17.
************************************************************************************************************
In arriving at your solutions for assignment 2A you are ONLY permitted to use the SQL structures
and syntax which have been covered within this unit:
● Week 6 Workshop and Week 7 Applied - Creating & Populating the Database
● Week 7 Workshop and Week 8 Applied - Insert, Update, Delete (DML) and Transaction
Management
● 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/marked.
Views must not be used in completing these tasks.
You must also keep up to date with the Ed Assignment 2A forum where further clarifications may
be posted. Please be careful to ensure you do not publicly post anything which includes your
reasoning, logic, or any part of your work to this forum, doing so violates Monash
plagiarism/collusion rules and has significant academic penalties. Attend a consultation session,
use a private Ed post, or email your allocated tutor to raise such questions.
************************************************************************************************************
Page 4 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
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 2A folder and regularly pushed to the FIT GitLab server to build a clear history of
development of your approach. A minimum of ten pushes to the FIT GitLab server is required (2
pushes per file). Please note ten 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: DDL (16 marks):
For this task you are required to add to T1-mns-schema.sql, the CREATE TABLE and
CONSTRAINT definitions for the EMERGENCY_CONTACT, PATIENT and APPOINTMENT tables
in the positions indicated by the comments in the script.
The table below provides details of the meaning of the attributes in the missing three tables. You
MUST use identical table and attribute names as shown in the data model above to name the
tables and attributes which you add. The attributes must be in the same order as shown in the
model. You must use delete RESTRICT/NO ACTION for all FK constraints. These new DDL
commands must be hand-coded, not generated in any manner (generated code will not be
marked).
Table name Attribute name Meaning
EMERGENCY_
CONTACT
ec_id Emergency contact identifier
ec_fname Emergency contact first name
ec_lname Emergency contact last name
ec_phone Emergency contact phone number
PATIENT
patient_no Patient number (unique for each patient)
patient_fname Patient first name
patient_lname Patient last name
patient_street Patient residential street address
patient_city Patient residential city
patient_state Patient residential state - NT, QLD, NSW, ACT, VIC,
Page 5 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
TAS, SA, or WA
patient_postcode Patient residential postcode
patient_dob Patient date of birth
patient_contactmobile Patient contact mobile number
patent_contactemail Patient contact email address
ec_id Patient emergency contact identifier
APPOINTMENT
appt_no Appointment number (identifier)
appt_datetime Date and time of the appointment
appt_roomno Room in which appointment is scheduled to take
place
appt_length Length of appointment - Short, Standard or Long (S,
T or L)
patient_no Patient who books the appointment
provider_code Provider who is assigned to the appointment
nurse_no Nurse who is assigned to the appointment
appt_prior_apptno Prior appointment number which leads to this
appointment (this is required to be unique)
To test your code you will need to first run the provided script mns_schema_insert.sql to create the
other required tables. mns_schema_insert.sql, the head of this schema file, contains the drop
commands for all tables in this model. If you have problems with Task 1 and/or Task 2 simply rerun
mns_schema_insert.sql which will cause all tables to be dropped (including any you have created
as part of task 2) and correct the issues in your script. Do not add DROP TABLE statements to
either of your Task 1 or Task 2 scripts.
TASK 2: Populate Sample Data (24 marks):
Before proceeding with Task 2, you must ensure you have successfully run the file
mns_schema_insert.sql (which must not be edited in any way) followed by the extra definitions
that you added in Task 1 above (T1-mns-schema.sql). Note that the mns_schema_insert SQL
commands nor any of your task 1 SQL code may be added to/reproduced in your Task 2 script.
Load the EMERGENCY_CONTACT, PATIENT and APPOINTMENT tables with your own
test data using the supplied T2-mns-insert.sql script file, and SQL commands which will
insert as a minimum (at least), the following sample data:
○ 5 EMERGENCY_CONTACT entries
○ Involve at least 2 people being the emergency contact for more than two
patients
○ 10 PATIENT entries
○ Involve at least 5 adult patients and 5 under age (under 18 years old) patients
Page 6 of 17
FACULTY OF
INFORMATION
TECHNOLOGY
○ 15 APPOINTMENT entries
○ All appointments which you add must be scheduled on three specific days in
2023. You may pick any three dates between 1 May 2023 and 31 August 2023
○ Involve some parallel appointments (ie. two or more appointments scheduled
at the same date and time)
○ Involve at least 5 different providers
○ Involve at least 5 different nurses
○ Involve at least 3 follow up appointments
In adding this data, you must ensure that the data you insert will make full use of the various
features you have coded in Task1. For example, if you have coded a check clause with permitted
values of 1, 2 and 3 then your inserted data must use all three of these permitted values.
Your inserted data must conform to the following rules:
1. You may treat all the data that you add as a single transaction since you are setting up the
initial test state for the database.
2. The primary key values for this data should be hardcoded values (i.e. NOT make use of
sequences) and must consist of values below 100.
3. The data added must be sensible (e.g. the patient data must mimic the real data; the
patient, provider, nurse and room must not be double booked; the following appointment for
a provider must not be scheduled before the previous appointment for that provider is
completed; and other real scenario constraints).
For this task ONLY, Task 2, you may look up and include values for the loaded tables/data
directly where required. However, if you wish, you can still use SQL to get any non-key
values.