Assignment -
This is the assignment. Attached the Statement of Work and the ERDs Models. Based on that these 2 questions needs to be done.
1. Develop Data Distribution Plan Describe the database distribution, updates, retrieval strategy and distributed queries. Identify tables for fragmentation. Provide a rational for the fragmentation. Describe 5 distributed queries based on the plan.
2. Implement the Distribution Plan Use database links to create the table(s) chosen for the fragmentation.
Statement of Work -
Introduction - The office of MediCall has requested for a database to be built that would meet several requirements for the clinic. This would be the first high performance database implemented in the environment as the office did not require it in the past due to low volume of patients. The number of patients and walk-ins has increased, which in turn requires the office to provide better application with high performing OLTP database. Currently, the clinic is using an outdated, crudely designed web application hosted on Microsoft access in the backend for data storage and retrieval. Microsoft access is limited when it comes to complexity, scalability, high availability, and high performance. Microsoft access is also limited when it comes to hosting applications and running complicated queries. The end goal would be to design the database on an Oracle 11g platform. Oracle 11g will provide scalability in terms of future growth, and high availability in terms of disaster recovery plans and backup strategy.
Requirements
A functional database system that allows inputs and output of data, and room for growth
The database should include data about different aspects of the business
Relationships between tables should be allowed
The database should be able to allow regular reports to be generated
- How many patients are seen per day
- Which patients are due for yearly checkup
- Which type of doctor was seen the most
Billing and payments
Information about all the doctors
Information about any patient that comes to the clinic
Constraints & Assumptions - The assumption being made is that the clinic has a limited budget set aside for the technology upgrade. The budget constraint and lack of physical storage space would affect the decision of building a scalable and high availability database system. The option of utilizing cloud infrastructure over physical servers will be offered as a solution.
This portion of the project will concentrate on the implementation of a new database for MediCall, a medical clinic. The purpose of the database will be for gathering patient data to use for business insights and as a backend storage for the company web application. This project will not delve into the creation and management of the web application, nor will it delve into the offerings of cloud storage in place of physical storage.
Schedule
- SOW established
- Logical model ERD created using Data Modeler
- Presentation of logical design
- Logical design optimized into relational model with any changes
- Relational model submitted for second review
- Final review, if accepted then implementation will begin
- Implement the design on Oracle 11g platform
Resources
- Oracle Data Modeler: used for the ERD design phase
- Database Version- Oracle 11g
- Hardware and Software: UMUC (VDA) Intel(R) Xeon(R). Operating system is Linux
- Sql Developer & Putty: used to translate the ERD into sql language for DDL and DML usage
Attachment:- Assignment Files.rar