2023 Please I need the answer as soon as possible if you can by tomorrow | Assignment Collections

Please I need the answer as soon as possible if you can by tomorrow.

Scenario:

Alain polyclinic is a medical clinic situated in AL-Ain. Assume that the chief operating officer of

the clinic has contacted you to create a conceptual model whose application will meet the expected

database requirements for the clinic. The officer gives you the description of the clinic’s operating

environment. Any patient can make any number of appointments with one or more doctors in the clinic,

and a doctor can accept appointments with many patients. However, each appointment is made with

only one doctor, and each appointment references a single patient. Emergency cases do not require an

appointment. However, an emergency is entered into the appointment book as “unscheduled” for

appointment management purposes. If kept, an appointment yields a visit with the doctor specified in

the appointment. The visit yields a diagnosis and, when appropriate, treatment. Each visit updates the

patient’s records to provide a medical history. Each patient visit creates a bill. Each patient visit is billed

by one doctor, and each doctor can bill many patients. Each bill must be paid. However, a bill may be

paid off in many installments, and a payment may cover more than one bill. A patient may pay the bill

directly, or the bill may be the basis for a claim submitted to an insurance company. It is expected that if

the bill is paid by an insurance company, the deductible is submitted to the patient for payment.

Propose a conceptual model after an in-depth research on clinic information management system’s

operations and processes. In order to come up with an appropriate design/solution, you must study the

requirements, issues and best practices in this problem context. You can make relevant assumptions

required to formulate business rules, security, administration and modelling.

Based on the above information execute Task 1, Task 2 and Task 3. You may have to make additional assumptions to execute Task 1, Task 2 and Task 3.

Task 1:

Design an Entity Relationship Diagram (ERD) to model the above scenario. Identify the different entities and attributes of each entity. (A minimum of 8 entities need to be identified). Suggest and justify the choice of relationship and cardinality of the relationship. State the assumptions (if any) made by you to develop the ERD based on your research. Provide detailed analysis and justification for the selection of entities and attributes based on your research.

Task 2:

Derive a set of relational tables from the ER diagram (Task 1), using appropriate choices for the table

attributes. List the integrity rules and business rules, which you would recommend to ensure the quality

of data. Indicate all necessary key constraints. Critically analyze the constraints and business rules based

on your study of the clinic.

Task 3:

Outline the kinds of anomalies that can arise in the scenario given above by using un-normalized tables.

Show how the table created in task 2 could be re-organised into separate tables to avoid anomalies

(Decompose the table structures to a set of 3NF tables).

Task 4: As a database administrator identification of specific roles for users, authentication policy,

storage management and database recovery are critical. The efficiency and complexity of database

administration depends on the identified strategy and tools. Propose and critically review user

management, backup/recovery strategy, storage management/techniques formulated for the system

discussed in Task 1-3.