database system for medical practice based on scenario

Scenario AJS is a small, medical practice. They have two offices located in Barking and Beckton in London. Over the next five years they are looking to grow their practice to four offices. The business currently uses an old legacy system which is file-based. Due to various difficulties, the management have decided to modernise the system by having an online system with a backend database. As a group of consultants, you are required to produce a database system to upgrade the existing system that handles the patient data, appointments and inventory but is not efficient enough to manage all the data. The practice maintains a stock of medical equipment and obtains medical equipment from 5 suppliers. When items are ordered in bulk (typically more than 50 pieces), they are eligible for a 20% discount. Patients register with the practice and various health information must be supplied and securely stored as part of this process. Each patient is assigned to a doctor who manages their various treatments. Patients usually visit this doctor when they visit the practice unless the required Doctors are unavailable. Each office employs 10 staff, which includes, doctors, nurses, cleaners and admin staff. Each office has a manager who organises the rota for the staff. Staff details, such as, contact telephone numbers, speciality, qualification and awarding institutions and previous employment must be recorded. End of every week, a report must be produced which states the total number of patients who attended the practice and a list of all inventory used so that stocks can be maintained. In order to achieve the business needs and ensure consistency of data in the database you are to develop integrity and referential integrity constraints that will ensure the correctness of the data when it is entered. Below is a list of checks you should implement. 1. The staff gender should be recorded as ‘M’ for male or ‘F’ for female. 2. The name of a staff should not contain any numeric characters. 3. The staff rota should be no more than 6 hours and should not exceed 8 rotas per week. 4. The staff salary should be greater than £0 and not exceed £150,000. 5. Supplier email address should have an @ symbol present to be valid. 6. When scheduling an appointment, it should not be possible to schedule it in the past 7. When updating stock it should not be possible to have negative stock to ensure that accurate stock levels are maintained. 8. Staff numbers should be generated automatically using a sequence and a trigger.