INFS1200/7900 Information Systems Assignment 1 – Relational Database Modelling
(10 Marks)
Due 5.00 PM Friday, 23 September 2016
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).
Copyright By PowCoder代写 加微信 powcoder
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 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)
We identify two types of users in this system: home users and online experts. Home users can register with the system in order to receive the consultation services of the system and become members of this Home-Healthcare Information System. User information is about contact details, personal details, disease history, current medication/therapy, and allergies. A home user may have a designated online expert responsible for providing them with healthcare
Page 1 of 7
consultations. Home users can use this system to register their personal details and enter the status of their personal health conditions, including complaints, symptoms, home-collectable diagnostic test results, or healthcare questions.
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.
An online expert may be responsible for consultations with many home users. When online experts are browsing the home users’ health statuses, they may give instructions to home users on how to conduct home-collectable diagnostic tests. Upon seeing the test results, online experts may either enter their diagnostic opinions (diagnoses) into the system, recommend treatments, or initiate further investigations on possible healthcare solutions for home users.
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.
Diagnoses are the opinions given by clinicians/specialists based on their professional judgement when they see the evidences. When home users report their health status, they may describe signs or symptoms that could be associated to a certain disease. A diagnosis should be recorded as a written description and include the time that the diagnosis was given. The disease that is contracted by a home user may also be identified in the diagnosis.
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 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.
Healthcare questions are the written messages asked by home users based on their health conditions. A healthcare question should be recorded with timestamp, type of question (Private or Open), title of the question, and the text of the question etc. One home user may ask many questions. An open question asked by a home user can be seen by all online experts. A private question asked by a home user can only be seen and answered by their designated online expert.
Healthcare questions may be answered by online experts based on their professional knowledge and experiences. The healthcare questions and answers should be recorded with timestamps, together with the type of answers (Private or Open), and the text of the answer. One online expert may answer many questions. One question may have many answers.
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.
Different users may have different consultation processes. A home user uses this system to enter their health status (e.g., signs and symptoms), home-collectable diagnostic test results (if any), or ask questions to online experts. Online experts may use this system to enter diagnoses,
Page 2 of 7
recommendations of treatments, or answer questions asked by home users. Online experts should be able to browse the list of home users and their questions.
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.
Create an ER diagram to represent the conceptual schema described by the Universe of Discourse. For cardinality and participation dependency, you may use either standard notation, or the alternate (min, max) notation, but not both.
State any assumptions you make regarding your design. Assumptions are to be submitted together with the ER diagram as a part of your design document. Please note that you cannot make assumptions to simplify or compromise the completeness of the Universe of Discourse. If there are any points that need clarification, in the specification of the given Universe of Discourse, you must first attempt to clarify them with your tutor and/or lecturer. You should refrain from making your own assumptions.
2.2 Mapping
Map the ER diagram created in 2.1 to a relational database schema. Document the mapping steps. The final schema should be given in the notation as given in your lecture notes, e.g. R1 (key_attr, attr1, attr2, …). In order to identify referential integrity constraints, be sure to either use the same names of the referenced/referencing attributes, or write this separately e.g. R1(attr1) references R2(some_key_attr). You can also use a schema diagram that identifies referential integrity constraints through arrows.
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 ER- Relational 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).
Page 3 of 7
Here is an example of what you may document in this part:
R1 (A, B, C, D)
No partial or transitive dependencies found. R1 is in 3NF.
2.4 Tutorial Support
You may seek tutor support to clarify your understanding of the Universe of Discourse. You may also discuss the concepts behind ER modelling and mapping, in general, with your tutor.
3 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.
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 ER- to-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.
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
You are encouraged to discuss the concepts behind ER modelling and mapping, in general, with your classmates (including the Course Website Discussion Board). You may work in a group of no more than 3 people to discuss the creation of the ER diagram or the relational schema for this assignment. However, all discussions should be confined to within the group, and all group members must be identified in the submission as given in the file Assignment1-
Page 4 of 7
SubmissionTemplate.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
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 the sections earlier than the suggested due dates.
Complete by
3. Mapping and Normalization
6 Collaboration
2 September 9 September 16 September
The University has strict policies about plagiarism. Penalties for engaging in unacceptable behaviour can range from 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!
Page 5 of 7
APPENDIX A:
Colorectal cancer
Prostate Cancer Anaemia Diabetes
High cholesterol HIV
Hepatitis C Influenza Thyroid problems Kidney disease
Osteoporosis
Home Collectable-Healthcare Tests i
Test Process
quantitative screening test
occult blood screen
PSA Screening Test
Anemia Haemoglobin Meter Glucose Diabetes test
Home Cholesterol Test
HIV-1 Test
Hepatitis C Test
Influenza A/B Test
Thyroid Stimulating Hormone test Albumin in urine
key bone marker, deoxypyridinium (D-Pyd)
MyAllergyTest kit
EZ DETECTTM
CHEMCARDTM VENTURETM
Home Access®
Home Access®
Quick Diagnostics FlexSite At Home Kidney Screen
Skin needle prick with visible result
Test pad is placed in the toilet bowl along with a bowel movement with visible result
Skin needle prick with visible result Skin needle prick with visible result Skin needle prick with visible result Skin needle prick with visible result Skin needle prick with visible result Skin needle prick with visible result Nasal specimen swab sent to pathologist Skin needle prick with visible result Mail urine sample to pathologist
Mail urine sample to pathologist
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
Breast cancer
Breast Self-Examination Pad and Kit
Reduces friction between the fingers and the breast tissue thereby facilitating and enhancing the early detection of suspicious abnormalities or lumps during routine breast self-examination
Skin cancer
Skin monitoring system
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.
Osteo Check Page 6 of 7
Hormone balance estradiol, progesterone, 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
Heart disease C-Reactive Protein
Heart disease Lipid test
Acute ear infection Middle Ear Infection Monitor Genetic Health Risks DNA
Radon Home Test Kit CRP
EarCheck EC-2
Premium Home DNA Test
Mail home air samples to pathologist Skin needle prick sent to pathologist Skin needle prick sent to pathologist Ear monitor with visible result
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
check if elements in the blood match genetic information about the diseases
Digital Bio Disc
Skin needle prick with visible result
breast, ovarian, lung, uterine, prostate, testicle, colorectal, pancreas, liver, stomach, thyroid
Cancersafe®test
Blood sample sent to pathologist
Ovarian cancer ovarian cancer test Bladder cancer Urine test
i This list is provided by Dr Lisa PlexTM BTA stat test
Blood sample sent to pathologist
Urine on screening test pad with visible result
Page 7 of 7
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com