[SOLVED] database MACQUARIE UNIVERSITY INTERNATIONAL COLLEGE ASSESSMENT

MACQUARIE UNIVERSITY INTERNATIONAL COLLEGE ASSESSMENT
Introduction to Business Information Systems (WISY104) ASSESSMENT DETAILS
Unit Name:
Assessment Task:
Total # Pages: 6 Total Marks: 50
Unit Code: Term & Year: Weighting:
WISY104 T2, 2019 10%
FIELD-NAME
SPECIALISTID SPECIALISTNAME SPECIALISTTYPE
DATA TYPE
SHORT TEXT SHORT TEXT SHORT TEXT
DESCRIPTION PROPERTIES
Table Name: PATIENT [5 MARKS]
FIELD-NAME
PATIENTID PATIENTNAME PATIENTADDRESS PATIENTDOB
SPECIALISTID
DATA TYPE
SHORT TEXT SHORT TEXT SHORT TEXT DATE/TIME
SHORT TEXT
DESCRIPTION
This is the unique ID for a patient
This is the name of a patient
This is the address of a patient
This is the date of birth of a patient
This is the ID of the specialist who first checked up the patient
Introduction to Business Information Systems
Assignment 2
Advanced Databases
Due:
CASE BACKGROUND
The HealthFirst is a private endocrinology clinic that provides comprehensive care for all endocrine conditions such as diabetes, thyroid disorders and osteoporosis. To fulfil their vision, the team at HealthFirst clinic has been working hard to implement systems to improve the services they provide to the local community. A partially created database called AppointmentScheduling_DB exists in MS Access. You are hired as a data architect to complete the database to prototype the appointment scheduling aspect of the business.
SETUP:
Save a copy of the Database file AppointmentScheduling _DB.accdb (available on iLearn) in your preferred storage location.
TASK 1: TABLE CREATION (8 MARKS)
Create the following TWO tables with the specified fields and general properties. Identify appropriate primary keys.
Table Name: SPECIALIST [3 MARKS]
Week 6-Lesson 2, before your class time (Online Submission)-5% Week 6-Lesson 2, (In-class Test)-5%
This is the unique ID of a specialist doctor This is the name of a specialist
This is the specialisation area of a doctor
SIZE: 3, FORMAT: > SIZE: 20, FORMAT: > SIZE: 30, FORMAT: >
PROPERTIES
SIZE: 4, FORMAT: > SIZE: 20, FORMAT: > SIZE: 50, FORMAT: > SHORT DATE
SIZE: 3, FORMAT: >
Page 1 of 6

TASK 2: DATA INSERTION (4 MARKS)
Populate the tables with values given below. Data is CASE-SENSITIVE. SPECIALIST TABLE: [2 MARKS]
SPECIALIST ID
SPECIALIST NAME
SPECIALIST TYPE
S1
ALAINA MURILLO
ENDOCRINOLOGIST
S2
KAYNE KEITH
ENDOCRINOLOGIST
S3
EMAM SINCLAIR
DIABETES EDUCATOR
S4
MARIYAM CRANE
DIETITIAN
S5
EDGAR BRAUN
DIABETES EDUCATOR
S6
EVE HIGGS
ENDOCRINOLOGIST
S7
SPIKE KINNEY
DIETITICAN
PATIENT TABLE: [2 MARKS]
TASK 3: RELATIONSHIP CREATION (2 MARKS)
Create relationships among the THREE tables such as SPECIALIST, PATIENT, and APPOINTMENT. Enforce referential integrity for each relationship created.
TASK 4: SIMPLE QUERIES (17 MARKS)
Task 4.1 [3 MARKS]
Display all details of specialists. Sort the result by specialists name in ascending order. Save the result as Query1.
Task 4.2 [3 MARKS]
Display specialists type (area of their specialisation) with their name if the specialists name has AM somewhere in it. Do not show SPECIALISTID in your answer. Save the result as Query2.
Task 4.3 [3 MARKS]
Display all details of patients. Apply multi-level sort to the results. First sort the records by patients name in ascending order, and then by patients date of birth in descending order. Save the result as Query3.
Task 4.4 [3 MARKS]
Display appointment details (APPOINTMENTID, PATIENTID and SPECIALISTID) booked in May, 2018. Save the result as Query4.
Task 4.5 [5 MARKS]
Display all details of appointments. Display consultation rooms B and C where the specialists S2 and S4 provided the consultation. Sort the result in ascending order by consultation fee. Save the result as Query5.
PATIENT ID
PATIENT NAME
PATIENT ADDRESS
PATIENT DOB
SPECIALIST ID
P1
CELYN WU
58 BENSON STREET
12-JUNE-1977
S1
P2
ARNOLD COSTA
3955 GLENWOOD AVENUE
31-MAY-1995
S2
P3
SIMONA TODD
86 FRAGGLE DRIVE
28-APR-1964
S2
P4
ALAYA WAGNER
23 KIMBERLY WAY
1-JAN-1954
S3
Page 2 of 6

TASK 5: ADVANCED QUERIES (8 MARKS)
Note: For both advanced queries, use EXPRESSION BUILDER and rename any NEW fields with the appropriate field names.
Task 5.1 [5 MARKS]
Calculate and display the total income for the specialist with SPECIALISTID as S2 with the following specialists attributes such as SPECIALISTID, SPECIALISTNAME and SPECIALISTTYPE. Save the result as ADVANCED_Q.
Task 5.2 [3 MARKS]
Write an UPDATE query to increase the consultation fees of all specialists by 2%. Execute the query once to update the consultations fees. Save the result as UPDATE_FEE_BY2%.
TASK 6: FORM CREATION (4 MARKS)
Using Form Wizard, create a form for PATIENT table.
Select Tabular layout.
Select all fields except SPECIALISTID.
Add a logo to the header section of the form.
You can use any colours and formatting you like.
Lock the PATIENTID field.
Save this form as PATIENT FORM.
TASK 7: REPORT CREATION (4 MARKS)
Using Report Wizard, create a report for the fields: APPOINTMENT ID, PATIENT NAME, SPECIALIST NAME, APPOINTMENT DATE, CONSULTATION ROOM and FEE.
View must be based on APPOINTMENT table.
Group data by SPECIALIST NAME.
Sort data by APPOINTMENT ID in descending order.
Select Block layout and Landscape orientation.
Include a title on the report as APPOINTMENTS SUMMARY REPORT 2018.
Choose the same colours and formatting as that of the form in Task 6.
Save this report as APPOINTMENT REPORT.
TASK 8: NAVIGATION FORM (3 MARKS)
Create a navigation form with Horizontal Tab.
Set First Tab as PATIENT FORM created in Task 6.
Set Second Tab as APPOINTMENT REPORT created in Task 7.
Create a button to link to Query1.
Save this form as NAVIGATION FORM.
Page 3 of 6

NATURE OF THE TASK
This is an individual assignment. HURDLE ASSESSMENTS This is NOT a hurdle assessment. MATERIALS REQUIRED Microsoft Access.
Note for Mac Users: Microsoft Access is not available for Mac. Mac users can use iLab to do their assignment 2. iLab is Macquarie Universitys personal computer laboratory on the Internet, enabling students to use the Microsoft Windows applications they require to do their university work from anywhere, anytime, on anything.
To use iLab you must download, install and launch the client known as VMWare Horizon View (old name was VMWare View client) from the following link. https://wiki.mq.edu.au/display/iLab/Downloading+the+iLab+Client
More step-by-step installation process with pictures can be found at the following link. https://wiki.mq.edu.au/display/iLab/Access+a+Microsoft+Windows+7+desktop+from+ Mac+OS+X
SUGGESTED RESOURCES
Lesson 2 (practical class) materials from Week 4 and 5 uploaded on iLearn.
HOW TO PRESENT YOUR ASSIGNMENT
Download the file AppointmentScheduling _DB.accdb provided under the Assignment 2: Advanced Databases in your Assessments section on iLearn. This will be the template for you to perform all your tasks.
SUBMISSION INSTRUCTIONS
As mentioned above in the assessment details table, the assignment will be submitted and marked in two parts.
Part 1 (Online Access (.accdb) File Submission 5%)
There will be a submission link on iLearn for Access file submission. Please note the due date and time for your group is Week 6-Lesson 2, BEFORE your class time. Please submit your work on iLearn as a MS Access file by the deadline. You need to re-name the excel file as your Family Name (e.g., for the student Bob Jane the file should be Jane.xlsx or Jane.xls).
Hard copies will NOT be accepted or marked.
Part 2 (In-class Test 5%)
The Assignment 2 in-class test will be in Week 6-Lesson 2. You have to attend the practical class in Week 6-Lesson 2 to sit the test.
INFORMATION ABOUT HOW AND WHEN FEEDBACK WILL BE PROVIDED
Marks and feedback will be provided via iLearn.
Page 4 of 6

LATE SUBMISSIONS AND PENALTIES
Late submissions are possible but they will be penalised unless the student has been granted an approved extension (refer to the Special Consideration Policy). Late penalties will be calculated based on the marks allocated to the specific assessment task. The penalty for late submission is as follows:
5% of the total possible marks will be deducted if it is late by up to 30 minutes
10% of the total possible marks will be deducted if it is more than 30 minutes late and up
to 24 hours late
A further 10% of the total possible marks will be deducted for each 24-hour period up to
3 days (including weekends)
100% of the marks will be deducted after 3 days and zero marks will be awarded
Please note that online submissions are electronically tracked, and the electronic record of submission will be used to determine late penalties. This means that submitting your work even a few seconds after the allocated deadline will result in a late submission which will attract the penalty noted above. There is no flexibility with the applying of penalties as they must be applied fairly and consistently to all students. It is your responsibility to allow sufficient time for submission of your work and any uploading of documents so try to avoid submitting your work just prior to the deadline. For example scenarios, read the unit guide.
RETENTION OF ORIGINALS
It is the responsibility of the student to retain a copy of any work submitted. Students must produce these documents upon request. Copies should be retained until the end of the grade appeal period each term. In the event that a student is asked to produce another copy of work submitted and is unable to do so, they may be awarded zero (0) for that particular assessment task.
ACADEMIC HONESTY
Using the work or ideas of another person, whether intentionally or not, and presenting them as your own without clear acknowledgement of the source is called Plagiarism.
Macquarie University promotes awareness of information ethics through its Academic Honesty Policy. This means that:
All academic work claimed as original must be the work of the person making the claim.
All academic collaborations of any kind must be acknowledged.
Academic work must not be falsified in any way.
When the ideas of others are used, these ideas must be acknowledged appropriately.
All breaches of the Academic Honesty Policy are serious and penalties apply. Students should be aware that they may fail an assessment task, a unit or even be excluded from the University for breaching the Academic Honesty Policy.
Page 5 of 6

EXPECTATIONS OF STUDENTS
Students are responsible for their learning and are expected to:
Actively engage with assessment tasks, including carefully reading the guidance provided,
understanding criteria, spending sufficient time on the task and submitting work on time;
Read, reflect and act on feedback provided;
Actively engage in activities designed to develop assessment literacy, including taking the
initiative where appropriate (e.g. seeking clarification or advice, negotiating learning
contracts, developing grading criteria and rubrics);
Provide constructive feedback on assessment processes and tasks through student
feedback mechanisms (e.g. student surveys, suggestions for future offerings, student
representation on committees);
Ensure that their work is their own; and
Be familiar with University policy and faculty procedures and act in accordance with those
policy and procedures.
MARKING CRITERIA
You will be assessed for Part 1 (Online Submission) according to the criteria below.
Criterion
What the marker will be looking for?
Marks
Task 1
Tables successfully created in the right order of execution
/8
Task 2
Tables populated correctly with the right values
/4
Task 3
Creating relationships between the THREE tables
/2
Task 4
Correctness of the simple queries
/17
Task 5
Correctness of the advanced queries
/8
Task 6
Correctness of the form
/4
Task 7
Correctness of the report
/4
Task 8
Correctness of the navigation form
/3
Total
/50
Page 6 of 6