Assignment
Case Study 1
Cessnock Community Hospital is a not for profit general hospital with 100 beds. The community is about 25,000 with a growth rate of 5 % per year. It attracts a lot of retirees and hence is planning to expand by adding another 100 beds in the next 5 years. There is also a plan to for assisted living facilities.
The hospital not only admits patients but also has an out-patient department (OPD). Laboratory procedures, deliveries and emergency services are also provided. Hospital employs full-time, part-time staff of nurses, physicians, specialists and super specialists. There are a lot of volunteers as well who help out at the hospital. Hospital provides general medical and surgical care, intensive care and many other diagnostic services.
Some of the entities identified are:
Facility, Physician, Patient, Diagnostic Unit, Ward, Staff, Prescription, Service/Drug, Medical/Surgical Item, Supply Item and Vendor Business rules governing relationships amongst these entities are:
1. FACILITY maintains one or more DIAGNOSTIC UNITs (radiology, cardiac, clinical etc)
2. FACILITY contains a number of WARDs (Gynaecology, Obstetrics, Oncology etc)
3. Each WARD is assigned a number of STAFF members (nurses, secretaries etc); a STAFF member may be assigned to multiple WARDs
4. FACILITY staffs its medical team with a number of PHYSICIANs. PHYSICIAN may be staff of more than one FACILITY.
5. PHYSICIAN treats PATIENTs, PATIENT can be treated by a number of PHYSICIANs.
6. PATIENT is diagnosed by any number of PHYSICIANs, PHYSICIAN diagnoses PATIENTs
7. PATIENT may be assigned to a WARD (except outpatients)
8. PATIENT uses MEDICAL/SURGICAL ITEMs supplied by VENDORs, VENDOR also provides SUPPLY ITEMs used for housekeeping and maintenance purposes(disinfectants, cleaning chemicals etc)
9. PHYSICIAN writes one or more PRESCRIPTIONs for a PATIENT. Each PRESCRIPTION is for one PATIENT only and a PATIENT may have many PRESCRIPTIONs
10. PRESCRIPTION can be for diagnostic test (lab test, imaging - X-ray, MRI etc )or a drug
Questions:
1. Create a data model.
2. Create an ER Diagram.
3. Convert the relation to BCNF, for this you have to show the normalization starting from 1 NF
4. Create tables, add constraints (PK, FK)
5. Create Views based on these tables to show:
a. Details of staff members working in wards
b. Patients being treated by Physicians
c. Supply Items that have reached below threshold (for this assume quantity < 10) and display results accordingly
d. Diagnosis and Prescription of patient
e. Ward's requirement of Medical/Surgical Items (Hint: Find the patient in the ward and join with prescription)
Case Study 2
The manager of a consulting firm has asked you to evaluate a database that contains the following table structure:
Attribute Name Sample Value
Client_Num 289
Client_Name James D. Smith
Contract_Date 12-Mar-14
Contract_Num 5842
Class_1 Database
Class_2 Networking
Class_3
Class_4
Region SE
Cons_Num_1 25
Cons_Name_1 Angela Jameson
Cons_Num_2 34
Cons_Name_2 Gerald Ricardo
The table was created to enable the manager to match clients with consultants. The objective is to match a client within a given region with a consultant in that region, and to make sure that the client's need for a specific consulting service is properly matched to the consultant's expertise. For example, if the client is in the banking business and is located in the South East, the objective is to make a match with a consultant who is located in the South East and whose expertise is in the banking business. The following basic business rules are maintained.
• Each client is located in one region, but the region contains many clients.
• Each consultant can serve many clients, and each client can be served by many consultants.
• A client can sign more than one contract, but each contract is signed by one client.
• Each contract covers one classification. For example, if a client requires consulting work on database and networking, then two contracts must be executed and signed.
• Each consultant is located in one region, but a region can contain many consultants.
• Each consultant has one or more areas of expertise (class), and each area of expertise (class) can have many consultants in it. For example, a consultant might be classified as an expert in database and networking, and the consulting company might employ many consultants who are network experts.
• A client might need consulting in more than one classification. For example, a client might require help in database and networking.
Questions:
1. Create data model
2. Create ER diagram
3. Convert the relation to BCNF, for this you have to show the normalization starting from 1 NF
4. Create tables, add constraints (PK, FK)
5. Create Views based on these tables to show:
Detail Views
a. Consultant serving clients region-wise
b. Classifications of contracts client-wise
Summary Views (Hint: Use group by having)
c. Group consultants in region by expertise
d. All clients having contracts with more than one classification
e. Consultants serving multiple clients in one region
Group report should be 1000 words and should clearly mention the assumptions or any other entities, business rules that you may wish to add.
Paste all the queries and screenshots along with data entered in table in the report.
Create the data model and ER diagram in MS Visio, Lucid Chart, Draw.io or any other tool of your choice.
Each group will be required to demonstrate the DB they create along with tables, data contained therein and display the results fetched by view/query execution.
Each question is worth 4 marks and 5 marks are for the report accompanying the project.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Database-Design-and-Development.rar