Assignment 1 – Relational Database Modelling
This assignment is to perform the logic design of an information system. It involves the construction of a DFD, an ER diagram, mapping to the relational schema, and the normalization of the resulting relational schema. The necessary information for schema design is given in the Universe of Discourse (UoD).
This assignment can be completed as a group. The maximum size of a group is three people. Each group is allowed to submit only one file. For any student whose name appears within two or more groups, the result will be whichever is the lowest.
1 A Home-Healthcare Information System
1.1 Background
Aged care is important to a society. Many elderly people live alone at home. The existence of broadband communication networks in Australia enables home-healthcare information systems to play an important role in people’s lives.
You are required to create a central database that is able to record remote health monitoring data and to provide home healthcare information for people. Nowadays, with the in-house Bluetooth/Wireless technology and broadband Internet connections, healthcare experts sitting in a remote/central office are able to provide consultation services and expert professional advices to people through tele-medicine technologies and software systems.
This information system Add should be designed to provide services that allow healthcare stakeholders to interact with each other and share healthcare information. The system will record personal information as well as the healthcare-related diagnostic data collected at home.
In addition to diagnostic data, such as heartbeats and blood pressure that can be submitted online, healthcare-related physiological data can also be collected via sensors without supervision in people’s homes. Appendix A shows a list of diagnoses that can be performed at home by using sensors without personal training or can be performed with remote supervision.
We assume that people who will subscribe to this remote home-healthcare system can handle all tests required in health monitoring, e.g., Breast Cancer self-tests. The home-collected data, including samples, can be physically transported to the central office either by a courier or by Internet-connected electronic sensors installed in the home.
1.2 The Universe of Discourse (UoD)
Online experts are the professionals who will log into the system and browse home users’ information, provide their opinions about the symptoms reported by home users, answer questions asked by home users, or instruct home users to take home-collectable (self) diagnostic tests. Online experts should have their contact details and professional credentials recorded. Online experts can play different roles including but not limited to clinicians, specialists, GP (General Practitioners), therapists, or medical/healthcare consultants. Online experts give opinions on what diseases that the home users might have contracted, what treatments should be recommended to the home users, and how those healthcare activities can be performed at home.
Signs and symptoms are the phenomena reported by home users or observed by online experts. Signs and symptoms should be recorded with timestamps, severity, and worded descriptions.
Diseases should be recorded with the type of disease, name, and their text descriptions.
Home-collectable diagnostic tests should be recorded with data described in Add Appendix A. When a home-collectable diagnostic test is performed by a home user, its results (with time, description, positive/negative etc.) should be recorded.
Treatments are the recommendations that clinicians give in their consultations with home users. Treatments can be recorded as name, type, and descriptions in words.
All users enter the system through a registration process, wherein, they are given a unique login. After joining, users can access the system by entering the required information.
2 Schema Design
Quality and accuracy of schema design is important in this assignment. The following describes what is expected in this assignment.
2.1 DFD and ER Diagram
Create a context level DFD. You should take your DFD to your tutorial for feedback before starting on the ER.
2.2 Mapping
2.3 Normalization
All relations resulting from ER-to-Relational mapping should be normalized up to the third normal form (3NF). Document your reasons for normalization, clearly stating the normal form of the original relation, and of the resulting relation(s).
Tip: It is highly likely that the relations in the schema you come up with at the end of the ERRelational mapping (see section 2.2) are already in 3NF. However, you still need to write the (non-trivial) FDs between the attributes of each of the relations you designed, and demonstrate that the relation is actually in 3NF by running the 3NF test on it.
For each relation given in the final schema from 2.2 show the following
1. Identify non-obvious functional dependencies,
2. Determine if there are any partial/transitive dependencies,
3. If necessary, decompose the relation to make 3NF relations,
4. Clearly state the normal form of the original relation, and of the resulting relation(s).
R1 (A, B, C, D)
FD: A BCD
No partial or transitive dependencies found. R1 is in 3NF.
2.4 Tutorial Support
3 Submission
A template for the submission of this assignment is given on the course web site. You are to download this (Assignment1-SubmissionTemplate.doc) file and prepare your assignment using the template document.
When completed, you are to submit your DFD, ER diagram and documented steps of the ERto-Relational mapping process and normalization as a text file (in MS WORD or PDF format).
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-a1.zip”, where s3345678 is an example of your student email
account name. Add
The Assignment 1 work is to be submitted through the INFS1200/INFS7900 (Blackboard) course website.
4 Requirement
This assignment targets the following assessment criteria of this course:
Extract (simplified) specifications to create basic conceptual data models
Map basic conceptual data models to relational database schema
Reason with the logical foundation of the relational data model
Understand the fundamental principles of correct relational database design
Construct simple computer-based information systems given a complete specification
Group Work
Assignment1SubmissionTemplate.doc. Students working in a group will submit only ONE copy of the solution, which can be done through the account of any chosen member of the group. All students working in a group will be given the same mark.
5 Planning the Work
Work Complete by
6 Collaboration
Add
APPENDIX A: Home Collectable-Healthcare Tests i
Condition Test Process Tool Name Method
Allergy quantitative screening test MyAllergyTest kit Skin needle prick with visible result
Glucose, Protein, pH (Uri-3) and seven other diagnostic chemical panels (Uri-10) in human urine urinalysis reagent strips Uri-Strip K Dip strip in urine with visible result
Colorectal cancer occult blood screen EZ DETECT™ Test pad is placed in the toilet bowl along with a bowel movement with visible result
Breast cancer Breast Self-Examination Pad and
KitAssignment
P roject Exam AWARE™ Reduces friction between the fingers and the breast
Helptissue thereby facilitating and
enhancing the early detection of suspicious abnormalities or lumps during routine breast self-examination
Prostate Cancer PSA Screening
Testhttps://po wcoder.comLANDMARK Skin needle prick with visible result
Anaemia Anemia Haemoglobin Meter BioSafe® Skin needle prick with visible result
Diabetes Glucose Diabetes test CHEMCARD™ Skin needle prick with visible result
High cholesterol Home Cholesterol TestAdd
WeC hat
powcodeVENTURE™ rSkin needle prick with visible result
HIV HIV-1 Test Home Access® Skin needle prick with visible result
Hepatitis C Hepatitis C Test Home Access® Skin needle prick with visible result
Influenza Influenza A/B Test QuickVue Nasal specimen swab sent to pathologist
Thyroid problems Thyroid Stimulating Hormone test Landmark Diagnostics Skin needle prick with visible result
Kidney disease Albumin in urine FlexSite At Home Kidney Screen Mail urine sample to pathologist
Skin cancer Skin monitoring system Visiderm Each transparent Visiderm sheet becomes a dated record of a mole. Subsequent recordings create a method of comparing the present mole to prior recordings and documenting and noticing even subtle changes.
Osteoporosis key bone marker, deoxypyridinium (D-Pyd) Osteo Check Mail urine sample to pathologist
Page 6 of 7
Hormone balance estradiol, progesterone, and testerone levels Female Check Mail saliva sample to pathologist
Minerals and toxins MINERALS: Calcium,
Chromium, Cobalt, Copper, Iodine,
Magnesium, Manganese,
Selenium, Strontium, Sulfur, Zinc
TOXIC ELEMENTS: Aluminum, Antimony, Arsenic, Bismuth, Cadmium, Lead, Mercury, Nickel, Tin Mineral Check by Body
Balance Mail hair sample to pathologist
Cancer causing radon Radon Radon Home Test Kit Mail home air samples to pathologist
Heart disease C-Assignment
PReactive Protein roject Exam CRP HelpSkin needle prick sent to pathologist
Heart disease Lipid test Lipids Skin needle prick sent to pathologist
Acute ear infection Middle Ear Infection Monitor EarCheck EC-2 Ear monitor with visible result
Genetic Health Risks DNA https://po wcoder.comPremium
Home DNA Test Skin needle prick sent to pathologist
Liver cancer check if elements in the blood match genetic information about the diseases Digital Bio Disc Skin needle prick with visible result
Diabetes check if elements in the blood
Add WeC match
genetic information about the diseases
hat
powcodeDigital
Disc
Bio rSkin needle prick with visible result
Cancer breast, ovarian, lung, uterine, prostate, testicle, colorectal, pancreas, liver, stomach, thyroid Cancersafe®test Blood sample sent to pathologist
Ovarian cancer ovarian cancer test OvPlex™ Blood sample sent to pathologist
Bladder cancer Urine test BTA stat test Urine on screening test pad with visible result
i This list is provided by Dr Lisa New
Page 7 of 7
