Please answer all 3 questions. Please provide answer in 3 hrs

Assignment Specification

In this assignment, you need to perform the following tasks for the given case study: 1. Draw an Entity Relationship (ER) diagram 2. Perform logical design 3. Create 3

Please answer all 3 questions

Assignment Specification

In this assignment, you need to perform the following tasks for the given case study:

1. Draw an Entity Relationship (ER) diagram

2. Perform logical design

3. 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 patients 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 patients 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 specialists 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

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

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 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)

NF 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 patients 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 patients 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 specialists 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 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 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 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)