INFS1200/7900 Information Systems
Assignment 2 (10 Marks)
Due 5.00 PM Friday, 28 Oct 2016
(Minor changes in Green Text on 16 Oct 2016)
In this assignment, the designed schema of Assignment 1 will be implemented by using MySQL. The implementation will include creating queries and views for the system. For the convenience and consistency of assignment marking, you must use the following ERD and the relational schema.
1 IMPLEMENTATION REQUIREMENTS 1.1 The ERD
Page 1 of 6
1.2 The Relational Schema
There are 12 relations/tables:
HOME_USER (UID, Phone, eMail, FName, LName, Address, OID) ONLINE_CLINICIAN (OID, Phone, eMail, FName, LName, Address) QUESTION (QID, QText, Type, QTimestamp, UID)
ANSWER (AID, AText, Type, ATimestamp, QID, OID) TREATMENT (TID, Text, Name, Type, Pref-Order, UID, OID) CONSULTATION (CID, UID, OID, Time)
DIAGNOSIS (UID, OID, DTimestamp, DText)
DISEASE (ICD10CODE, Description)
USER_SIGNS_SYM (UID, Time, Severity, Description) TEST_RECOMENDATION (UID, OID, Time, Description, Type, TESTID) TEST_RESULT (TRID, Result, Description, Time, UID, TESTID)
HCHT (TESTID, Condition, Process, Toolname, Method) DIAG_DISEASE (UID, OID, DTimestamp, ICD10CODE)
The Primary Keys are specified in the tables as bold and unlined text. There are 16 Foreign Keys, which are shown as follows:
HOME_USER.OID ONLINE_CLINICIAN.OID
QUESTION.UID HOME_USER.UID
ANSWER.QID QUESTION.QID
ANSWER.OID ONLINE_CLINICIAN.OID TREATMENT.UIDHOME_USER.UID TREATMENT.OIDONLINE_CLINICIAN.OID CONSULTATION.UIDHOME_USER.UID CONSULTATION.OIDONLINE_CLINICIAN.OID
DIAGNOSIS.UID HOME_USER.UID
DIAGNOSIS.OID ONLINE_CLINICIAN.OID
DIAG_DISEASE.(UID, OID, DTimestamp)DIAGNOSIS.(UID, OID, DTimestamp) DIAG_DISEASE.ICD10CODE DIESEASE.ICD10CODE USER_SIGNS_SYM.UID HOME_USER.UID TEST_RECOMENDATION.UIDHOME_USER.UID TEST_RECOMENDATION.OIDONLINE_CLINICIAN.OID TEST_RECOMENDATION.TESTIDHCHT.TESTID
TEST_RESULT.UID HOME_USER.UID TEST_RESULT.TESTID HCHT.TESTID
Note that the above tables are for storing application data. You must create these tables and enforce the constraints over them. The sample data must also be entered.
The following describes the scope and requirements of the system to be developed in MySQL. Page 2 of 6
1.3 Queries
Your system should include at least the following SQL queries that allow the user to add, remove, find and modify the records:
1. Add a new user to the system. A user can be either a home user or an online clinician.
2. List healthcare consultation requests (symptoms, questions, diagnostic test results) for
home users.
3. List home users for an online clinician. For a given online clinician, all home users
should be retrieved from the database.
4. List all clinical opinions for a given home user (diagnosis, recommendation of
treatments, or answer questions).
5. Update a user record in the system. A user can be either a home user or an online
clinician.
6. Remove a clinician and records associated with this clinician in all tables from the
system. You need to decide the strategy on how the Foreign Key values are to be handled in the system. For example, you can replace the removed Foreign Key values by NULL values (called Nullified strategy).
Note: Multiple SQL Queries statements can be used with intermediate variables and explanations)
1.4 Views
The following views are to be created:
1. Home User view: This view is to be generated for every home user. List the activities of healthcare consultations. The listing should appear in the order of date, and then by matters that the user consulted (symptoms, questions).
2. Clinician Summary View: For every online clinician, generate a list of his/her home users. Show all details of home users who relate to the clinician. Provide also a count of home users per clinician as summary.
3. Consultation View: Generate a view that lists all the questions and answers asked by home users and answered by online clinicians. The view should firstly sort on the dates of questions asked, then sort on the questions.
Note: Multiple SQL Views can be used with intermediate variables and explanations)
1.5 Constraints
The system should enforce basic constraints, such as:
1. Referential integrity. Multiple referential integrity constraints can be extracted from
the specification.
2. Domain. Attribute values are restricted to the allowed data types.
3. Key and Entity integrity constraints.
4. Semantic constraints (if any) given in the Universe of Discourse (i.e., if there is anyone
identified in Assignment 1).
Page 3 of 6
2 SUBMISSION MATERIALS
In your online submission, the following documents should be included:
1. The Assignment Submission Template.
2. The evidence of your database schema created by you including all constraints and the
3 3.1
sample data (a complete executable .SQL file for MySQL of your Assignment 2 and a document with the printed text of CREAT TABLE statements and the print out of the tables’ contents.
3. The evidence of the successful execution of each queries (i.e. the queries and their results) in MySQL.
4. The evidence of the successful execution of each views in MySQL (i.e. the CREAT VIEW statements and the print out of the views’ contents).
NOTES Sample Data
Populate the database with enough meaningful sample data (10-20 tuples per table) to allow us to test the functionality offered by your information system. Please compact your database using the MySQL utility before submission.
3.2 Backups
Students are advised to create back-up copies of their assignments. Hardware and software can fail (or be lost) for many reasons and it happens quite frequently. Students should also retain a back-up file in the event that the original version is lost or damaged.
3.3 Modifications to this assignment
There may be changes made to this specification and the assignment requirements in general. Any clarifications and announcements relating to the assignment will be posted on the course web site.
3.4 Tutorial Support
You may seek tutor support to clarify your understanding of the assignment requirements. You may also discuss the how-to techniques in general, with your tutor.
4 SUBMISSION
Late submissions will incur a penalty of 20% reduction each day late. Students who believe they have sound reasons for late submission should have the formal approval from the school of ITEE. Please consult the ECP for more details on how to apply for an extension.
Your submission should be a single file. If you have more than one file to submit, you need to compress them into a single .ZIP file for the submission. The single file name for the submission should be named as “s3345678-a2.zip”, where s3345678 is an example of your student email account name.
The Assignment 2 work is to be submitted through the INFS1200/INFS7900 (Blackboard) course website.
5 ASSESSMENT Individual Work
Page 4 of 6
This assignment is individual. You are encouraged to discuss the MySQL techniques necessary to construct an arbitrary form or a report. However, you must not discuss the construction of forms or reports for this assignment. Every student must submit individual work for the implementation part of this assignment.
Page 5 of 6
6 PLANNING THE WORK
Below are guidelines for you to follow, so that the work required in this assignment can be accomplished effectively in the available time. You are encouraged to complete before.
Work
Complete by (latest!)
1. Setting up the database (Tables, Relationships, Constraints) and Enter meaningful sample data
14 Oct 2016
2. Queries
21 Oct 2016
3. Views
27 Oct 2016
4. Testing in the lab and online submission
28 Oct 2016
Late submissions will not be accepted at all, or if accepted, will incur a penalty of 20% reduction each date late. Students who believe they have sound reasons for late submission should see the course coordinator as soon as possible.
7 COLLABORATION
The University has strict policies with regard to plagiarism. Penalties for engaging in unacceptable behaviour can range from cash fines or loss of grades in a course, through to expulsion from the University.
You are required to read and understand the School Statement on Misconduct, available on the ITEE website. This Statement includes advice on acceptable levels of collaboration.
If you have any questions regarding collaboration, please see either the lecturer or your tutor for guidance. Remember that ignorance is not a defence!
END OF ASSIGNMENT TWO Page 6 of 6