Database Development and Design Case Study Research - UniPharma
The UniPharma chain of pharmacies has offered to give you a free lifetime supply of medicine if you design its database. UniPharma is a small medium enterprise (SME) in terms of company structure and has four (4) pharmacies in the Auckland region. Being an SME, they would like to keep their costs down but also to have an efficient solution to manage their back office administration. Here is some more information to help you out with the case study:
- Patients are identified by a Social Security Number (SSN), and their names, addresses, and ages must be recorded.
- Doctors are identified by an SSN. For each doctor, the name, speciality, and years of experience must be recorded.
- Each pharmaceutical company is identified by name and has a phone number.
- For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company, and the trade name identifies a drug uniquely from among the products of that company. If a pharmaceutical company is deleted, you need not keep track of its products any longer.
- Each pharmacy has a name, address, and phone number.
- Every patient has a primary physician. Every doctor has at least one patient.
- Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.
- Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and a quantity associated with it. You can assume that, if a doctor prescribes the same drug for the same patient more than once, only the last such prescription needs to be stored.
- Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical company can contract with several pharmacies, and a pharmacy can contract with several pharmaceutical companies. For each contract, you have to store a start date, an end date, and the text of the contract.
- Pharmacies appoint a supervisor for each contract. There must always be a supervisor for each contract, but the contract supervisor can change over the lifetime of the contract.
Task 1:
1. As the Database Administrator (DBA) write down the relational schema for at least 5 tables of the database in terms of the company's structure.
2. Draw an ER diagram that captures the preceding information. Identify any constraints not captured by the ER diagram.
3. How would your design change if each drug must be sold at a fixed price by all pharmacies?
4. As a part of the UniPharma change management plan how would you administer changes if the design requirements are as follows: If a doctor prescribes the same drug for the same patient more than once, several such prescriptions may have to be stored.
5. UniPharma is concerned about weekly data backup. Formulate and briefly describe a data backup plan.
Task 2:
Each of the 4 UniPharma pharmacies have a Pharmacy Manager, a data entry operator, an accountant, 2 pharmacists and database administrator. Keeping this company hierarchy in mind please answer the following questions-
1. Describe access privileges level of the five (5) employee types of UniPharma in the database? and who is the person deciding the access privilege levels?
2. Being the DBA you have to provide different access levels to the employees; mention any three (3) access related.
3. Recently 100 tables were updated in the database after new products came in. As a Database Administrator, what tasks will you perform to ensure consistency of the database?
4. DBMS is a highly complex system with hundreds of transactions being executed every second. UniPharma is concerned if it fails or crashes in the middle of transactions, it is expected that the system would follow some sort of technique to recover lost data. Discuss categories of failures and methods to formulate a recovery plan.
Application Design and Development Case Study Research - Auckland EZ hotel
Auckland EZ hotel located near Britomart is a 3-star hotel with 10 floors and each floor has 20 rooms. The management of the hotel has hired your company (BBC Software Ltd) to develop an attendance and payroll management system. This software takes attendance of their staff member, help the payroll department to calculate the number hours worked, leaves and takes into account other parameters that might be necessary for deciding the pay made to an employee. Security is of utmost concern to the hotel and they would like to have different access level for different employee roles. The different employee roles are as follows -
- A hotel manager who oversees the functioning of the entire hotel from all aspects including payroll and attendance
- Two receptionists who deal with reservation, check-in, check-out, scheduling of housekeeping and room service
- One accountant dealing with the finances of the hotel like payroll
- Twenty housekeeping staff will only use the system to register their attendance
- One chef and ten kitchen and restaurant staff
The employees can perform the following actions - apply for leave and choose to work in a particular shift. The hotel has three shifts - morning shift, evening shift and night shift. Additionally, the manager can assign shifts or apply for leave on behalf of any employee. An employee not on leave must be allocated to one and only one of the three kinds of shift in one day. Each shift must at least have one employee. The hotel has the following work zones- rooms, restaurant, lounge and toilet. For each shift (i.e. morning, evening or night), there must be an allocation for each of the work zones. Every working employee must be allocated to one and only work zone in his or her shift. Each work zone must have at least one employee.
The hotel is also concerned about the safety of their data and would like to have policies and strategies in place to protect the integrity and safety of their data.
Write a report to cover the following tasks. Use APA referencing.
Task 1: Requirement Gathering and Analysis
Your company BBC Software Ltd being a start-up has a team of 7 software professionals excluding the management. Keeping the project and your company structure in mind answer the following:
a. From a security perspective, identify the two types of requirements, with each type having three (3) examples for this project.
b. Keeping in mind the importance given to security by the hotel, make a comparison between four (4) software development methodologies and choose an appropriate methodology that is suitable for this project clearly justifying your choice.
c. Identify and explain five threats to the hotel's business that need to be considered for the success of this system.
d. Write down three elements of risk with two examples each that relate to the project's scenario.
Task 2: System Design
Base on the requirement gathered in Task 1, answer the following:
a. Draw a Use Case Diagram that highlights the security related interactions of the system amongst other interactions.
b. Keeping the case study in mind draw a UML Class Diagram focussing on the different access levels for each employee type. The diagram must contain the following - employee first name and last name, ID no, telephone no, address, date(s) present, date(s) on leave, hotel work zone and shift allocated".
c. Since physical security is an important aspect of the security Auckland EZ hotel is highly concerned about each employee's entry and exit from the building. To help develop such a secure system, map out a State Transition Network (STN) for the daily functioning of the attendance management part of the software. To scope the STN use the following assumptions
1. The starting state is when the employee enters the company first and the finish state is when the employee leaves finally for the day.
2. The attendance system is electronic and the employees have to tap their card on the card reader to register their entry and exit from the company.
3. Once the employee taps, an entry is made in a database table, this entry can be called 'Employee In'
4. If the employee taps a second time the database entry is 'Employee Out'.
5. An employee can tap in and out multiple times during the work hours of 9 am to 5 pm. The final exit for the day is considered at 5pm and total hours worked is calculated from the first tap to the last tap before or at 5pm.
6. If an employee has a gap of more than 1 hr between a tap in and out, they are reported to the HR
Comment and justify the STN.