Assessment item 1—Assignment 1
Due date: Friday of Week 5 (April 7, 2017 - 11:45 pm) Weighting: 30% Length: N/A Objectives
•
• To develop a conceptual data model diagram
To perform logical design Assignment Specification
In this assignment, you need to perform the following tasks for the given case study:
•
•
• Draw an Entity Relationship (ER) diagram
Perform logical design
Create 3NF Relations Case Study
Many medical specialists offer their medical consultations and perform special medical
procedures to the patients at CQ Specialist Medical Center (hereafter called as CQMC).
Patients generally book their appointments through a telephone call or by personal visits to
the center. Sometimes, the General Medical Practitioners (GPs) contact CQMC directly on
behalf of their patients for the specialist appointments.
The patient details, such as name, gender, etc., are collected and stored at CQMC. Since
the name of the patient is not going to be unique, each patient is identified by a unique
patient ID. The details of patient’s medical history (past surgeries undertaken), details of
current medications (medicines and dosages) and other medication-allergies, if any, have to
be maintained by CQMC. The referral letter from a GP is not a requirement at the time of
booking for an appointment with the specialist but it is compulsory at the time of actual
patient’s consultation. Just prior to the consultation, CQMC collects the referral letter from
the patient and notes down the details of date of referral and the GP (identified by unique
Medical Provider Number) who issued that referral letter. Based on the necessity, the same
GP may refer the same patient to the same specialist many times over a period of time.
During the consultation, a specialist may conduct various medical tests and procedures on
the patient; may prescribe multiple prescriptions to the patient. After each appointment, the
name of the standard tests and procedures performed along with the medical prescription
details are stored and maintained by CQMC.
The following table provides the details of consultation and other charges applicable for the
specialist’s appointments. The charges can be assumed to be constant values. Type Fees/Charges Consultation $120 Biopsy $75 Excision $175 Laser $250 After each appointment, the patient pays the entire appointment charges to CQMC. Each
appointment charges include the consultation fees and additionally may include multiple
other charges from the above table. Sometimes, some specialists offer discount amount to
some appointments on special grounds such as Pensioner, Veteran etc.,
Some GPs and patients often would like to know the details of qualifications and number of
years of experience of the specialists. Hence, CQMC maintains and updates those details
periodically. You may note that a medical specialist may have multiple qualifications.
The attributes of the possible entities have not been mentioned clearly. Hence, you need to
assume at least three and at most seven relevant attributes for each of the entities that you
will identify for your ER diagram. Assignment Requirements
In this assignment, you need to create an Entity Relationship (ER) diagram relevant to the
above case study and perform logical design to produce appropriate 3NF Relations.
The attributes for various entities have not been purposely described in the case study so
that the students are encouraged to further research and list the pertinent attributes in
addition to the required identifier for each and every entity in their ERD. 1 Draw an appropriate ER diagram Use the symbols as prescribed in your unit-textbook to draw the ER diagram (ERD) for the
above case study.
Your ERD must
• show all necessary entities, attributes and relationships • show identifiers/identifying attributes as necessary • show multi-valued attributes, if any • show participation and cardinality • show associative entities, if appropriate • show weak entities, if any • use the notation described in the set text

• use consistent and appropriate naming for entities and attributes as specified in the
unit text book Some business rules or other aspects of the case study may not be clear to you when
you read the case study. If this is the case, then you should either approach your lecturer
or tutor for clarification, or you may simply make an assumption and then develop your
ERD accordingly. For example, the case study might not mention all relevant participation
information (also called minimum cardinalities). If so, you may make an assumption about
what the minimum cardinalities might reasonably be, and then show them in your ERD
accordingly. You should justify each assumption in terms of the business, for example: it
is assumed that each customer must have at least one order because it is assumed
that the business does not record customer details until the customer places an order.
To get yourself started, ask yourself, 'If I were running this business, what things I need to
keep a list of?' Write those things down. For each thing, what information would you need
to record about it? How can it be identified? The answers to these questions will help you to
develop your ERD. 2 Assumptions and Business rules Provide assumptions and business rules relevant to your ERD. 3 Create Logical Design: Map your Entity Relationship diagram (ERD) into relations and make sure that all the
relations are in 3NF. Provide all the relations in the following format:
Customer (CustomerId, customerName, Street, Suburb, State, PostCode, Email)
Invoice (InvoiceNumber, invoiceDate, Total, CustomerId)
foreign key (CustomerId) references Customer(CustomerId) How and what to submit:
You must make an electronic submission for this assignment using the appropriate
assignment submission link in the unit web site.
You should submit a Microsoft Word document containing:
• A copy of your ERD. • A list of business rules and assumptions including their justifications. • 3NF Relations Your document should contain appropriate identifying information (your student number,
name, unit number, etc.) and should contain appropriate headings for each section. Important points
• You must work individually on this assignment. • The model solution to this assignment will be released approximately TWO (2)
weeks after the due date. • Assignments that are submitted after the release of the model solution will receive
zero marks. • You must state in your assignment (at the end under the heading
Acknowledgements is a good place) all materials and sources you used. This may
be as simple as 'This assignment was completed using only the COIT20247 unit
materials' to mention that you have used only the items provided by the unit
COIT20247. • Before submitting your assignment, review the marking criteria that are available
in the assignment specification. Ensure that all the criteria are addressed in your
submission. • After submitting your assignment, please verify that your submission was
successful, i.e. download your submitted files to ensure that they are correct. • Penalty related to late submission and plagiarism is handled as per the university
policies. -------------------------------------------------------------------------------------------------------------Marking Criteria:
Item
Entities have been identified, named and drawn
correctly including super type and sub type entities;
1 associative entities; weak entities (if any).
2 Provided appropriate attributes for all the entities.
3 Provided identifiers for the required entities Drawn correct relationships between entities and
4 named them appropriately.
Used proper symbols and shown min and max
cardinalities for the relationships. Appropriate
symbols have been used for super type and sub
5 type relationships (if any)
Provided appropriate justification for assumptions
6 and business rules.
7 Created correct 3NF Relations Primary keys and foreign keys have been shown in
8 relations correctly including foreign key references.
Penalty due to plagiarism/copying
Late Penalty: Less 5% of available marks per
calendar day.
Total Maximum Marks
5
5
2
3 5 3
5
2 30