Reference no: EM133035305

Assessment

Objectives

- To develop a conceptual data model diagram
- To perform logical design (optional)

Assignment Specification

In this assignment, youneed to perform the followingtasks for the given case study:
- Draw an Entity Relationship (ER) diagram
- Perform logical design(optional)
- Create3NF Relations(optional)

Please note that the above-mentionedoptional components,logical design and 3NF relations, will not be marked, however these tasks are expected to enrich your skills in the creation of databases in future.

Case Study
CQSpecialty Centre (CQSC)successfully runs amedical centre from Sydney CBD. During the pandemic period, CQSC has beenleasing the spare operation-theatres and other medical support staff services to private surgeons on daily-lease basis.When anew surgeon requestsfora lease, CQSCcollectshis/her personal details such as name, Medicare provider number, address, contact phone, email address, specialty, etc. For each lease booking by a surgeon,the date and any other support staff facilities that are required in addition to the operation theatre are recorded. Currently the available medical support staffservices are: General Nurses, specially-trained nurses and nursing assistants.All the lease booking details are being maintained.

Once the lease details areagreed by the surgeon, an invoice is generated by CQSC.For each such invoice, currently 70% of the invoice amount is collected as advance payment. The invoice amount is based on the cost of daily-lease rate of operation theatre and medical support staff services (charges)that have been requested. Even though the cost of daily-lease rate and medical supportservices (charges)varyin future, you can assume that these rates are constant for data modelling purpose.

For your information, CQSC does not allow any postponing or cancellation of the lease that has been agreed upon earlier. Hence, CQSC collects the complete payment for every lease that has been agreed/finalized. The details of patient and administration staff, who are indirectly involved in the leases are not currently recorded in the System butwill be included in the future.

Assignment Requirements

In this assignment,you need to create an Entity Relationship (ER) diagram relevant to the above case study and optionallyperform logical design to produce appropriate3NF Relations.

1 Draw an appropriate ER diagram
Use the symbols as prescribed in your unittextbook to draw the ER diagram (ERD) for the above case study.

Your ERDmust
• show all necessary entities, attributes andrelationships
• show identifiers/identifying attributes as necessary
• show multi-valued attributes (ifany)
• show participation andcardinality
• show associative entities (ifappropriate)
• show weak entities(if any)
• use the notation described in the unittextbook
• use consistent and appropriate naming for entities and attributes as specified in the unittextbook.

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 a surgeon might not have any lease so far, because it is assumed that the business records potential surgeons' details for future leases. 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.

Data dictionary
Provide the details of attributes for all the entities. You can use a suitable tabular format to provide these details. The details must include the following properties of attributes wherever appropriate:

name
size/length
minimum and maximum value and/or permitted value
required/not required for a new instance
brief notes that are relevant.

Assumptions and Business rules
Provide assumptions and business rules that are relevant to your ERD.

Create Logical Design (Optional):
If you would like to perform this optional component of this assignment, you can 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)

Attachment:- Conceptual data model diagram.rar