database !

Project Description

You have been asked to design a database for Healthway Hospital. Healthway depends on a large number of persons for its continued success. There are four groups of people on whom the hospital is most dependent: employees, physicians, patients, and volunteers. Of course, some common attributes are shared by all of these persons: Person_ID (identifier), Name, Birth_Date, and Address (City/ Zip code/ Phone). Each of the four groups has at least one unique attribute of its own. Employees have a Date_Hired, volunteers have a skill, physicians have a specialty and mobile#, and patients have a contact_date. Additional personnel in the hospital community do not belong to one of these four groups (their numbers are relatively small). However, a particular person may belong to two (or more) of these groups at any given time (for example, Patient and Volunteer). Each patient has one (and only one) physician responsible for that patient. A given physician may not be responsible for a patient at a given time or may be responsible for one or more patients. Patients are divided into two groups: resident and outpatient. Each resident has a date_admitted attribute. Each outpatient is scheduled for zero or more visits. The entity visit has two attributes: date (partial identifier) and comments. Notice that an instance of the visit cannot exist without an outpatient owner entity. Employees are subdivided into three groups: nurse, staff, and technician. Only nurses have the attribute certificate, which indicates the qualification. Only staff has the attribute job_class, and only technicians have the attribute skill. Each nurse is assigned to one (and only one) care center. Examples of care centers are Maternity, Emergency, and Cardiology. Attributes of the care center are Name (identifier) and Location. A care center may have one or more nurses assigned to it. Also for each care center, one of the nurses assigned to that care center is appointed nurse_in_charge. A nurse cannot be appointed nurse_in_charge of a care center unless she or he has an RN certificate. Each technician is assigned to one or more laboratories. Attributes of the laboratory include Name (identifier) and Location. A laboratory must have at least one technician assigned to it and may have any number of technicians assigned. There may be no beds assigned to a care center, or a care center may have one or more beds (up to any number) assigned to it. The only attribute of bed is Bed_Id (identifier). Bed_Id is a composite attribute, with components Bed# and Room#. Each resident patient must be assigned to a bed. A bed may or may not have a resident patient assigned to it at a given time.

-------------------------------------------

Phase 1: Conceptual Data Model - Read the project specification, carefully, and draw a conceptual design data model to accurately represent this set of requirements. Specify any additional assumptions that you are making. most notably you should use any tool (Data Modeling Tool) to draw the diagram. - Use (Phase1_Template.docx) and submit a soft copy of your conceptual entity-relationship diagram.

Phase 2: Relational Database Schema - Map your EER model to a relational database schema diagram. Submit this schema (with key and referential integrity constraints indicated in the usual way). Feel free to change your conceptual model (Phase#1) if needed. - Use (Phase2_Template.docx) and submit a hard copy that includes both the last version of your conceptual EER model with the Relational Database RDB schema diagram.

Phase 3 Schema Definitions and Query Implementations - Use the Oracle express as the DBMS for implementing your project. You need to check (Installing Oracle Database Express Edition 11g R.2 Guide.pdf) a) Define your database: - Use appropriate naming conventions for all of your relations and attributes. Database Term Project Page 3 of 4 - Write SQL (DDL) statements to create database, tables and all other structures. - Define attributes and for each one, specify if NULL is permitted, or its value is UNIQUE. - Primary key and foreign keys must be defined as appropriate. - Explain where and how referential integrity constraints have been incorporated. b) Populate your database. Insert at least 6 rows into each table (unless you have cardinality constraints). The data values should be reasonable. c) Create two views and eight queries. Choose them from the list in (Phase3_Template.docx). Give SQL translations of them, and indicate their implementation and solutions. - Incorporate some (but not all!) of these features: Sqlplus formatting; forms, reports, view, triggers, and other helpful or spiffy features. - Use (Phase3_Template.docx)

Phases 4 Presentation and Final Term Project Reports

1- the report file. The final report to have the followed parts: b. The goal of this project. c. Problem description (Copy it from here). d. EER diagram with all assumptions (Solution for Phase 1). e. Relational database schema (Solution for Phase 2). f. Implementation (DDL, and DML) statements, views, and SQL queries statements (Solution for Phase 4). g. Extra (if any)

=======

Phase 1_Template.doc

Date Modeling Tool:

Name the Data Modeling Tool that you used to draw the diagram and its reference

Assumptions

List your assumptions if any

Description: ER Data Model component:

Write adscription that list and describe each entity type along with its attributes and the relationships between the entity types.

Conceptual Data Model

Insert the ER/EER design here

===============================

Phase 2_Template.doc

Assumptions

List your assumptions if any

Conceptual Data Model

Insert the ER/EER design here

====================

Phase 3_ Schema Definitions and Query Implementations

Note:

In this template, we just give one example of creation and insertions as follows:

Table Creation:

Using SQL statement, we created XX tables as follows:

PHARMACY: (This is an Example)

CREATE TABLE Pharmacy

PRIMARY KEY (PNumber));

Database State:

We insert some values into the database in order to test our SQL create view and query statement.

INSERTION OF TABLE Pharmacy: (This is an Example)

--------------------------------------------------------------------------------------------------------

insert into Pharmacy

values ('003', 2148759, 'Building#3, King Fahd road, Olaya ',

--------------------------------------------------------------------------------------------------------

insert into Pharmacy

values ('020', 2146336, 'King Abduallah bin Abdualziz road ',

---------------------------------------------------------------------------------------------------------------------------

Table Screenshot:

Here list all your table instance/data, after the insertion process take screenshot from each table.

Table 1 shows the states for Pharmacy Database Schema. (This is an Example) Pharmacy

Query Implementation:

Here give out the SQL views and queries. For each one; write the view/query statement that you selected then write its SQL solution.

1- Retrieve the names of companies who haven't manufactured any drugs. (This is an Example)

SELECT Cname

FROM Company

WHERE NOT EXISTS ( SELECT *

FROM Drug

WHERE Cname=PCname);

You have to create two views and eight queries. Choose them from the list then indicate their implementation and results. Note: The values on the blue color can be change according to the entered data

Use the Create View statement to create the following views:

Employees-Hired: This view returns the First Name, Last Name, and Date Hired of all Hospital Employees.

NursesInCharge: This view returns the name of the Nurse in Charge for each Care Center along with the phone number of the Nurse.

GoodTechnician: This view returns all the Technicians how have at least one skill.

CareCenter-Beds: This view returns the name for each Care Center along with the number of beds that are assigned to patients (occupied beds), the number of beds not assigned to patients (free beds), and the total number of beds.

OutPatientsNotVisited: This view returns all OutPatients who have not been visited by a Physician yet.

Answer the following Queries. Feel free to use any of the views that you will create in part (a):

For each Job Class list all the staff members belonging to this class.

Find all Volunteers who do not have any skills.

List all Patients who are also Volunteers at the Hospital.

Find each Outpatient who has been visited exactly once.

For each Skill list the total number of volunteers and technicians that achieve this skill.

Find all Care Centers where every bed is assigned to a Patient (i.e. no beds are available).

List all Nurses who have an RN certificate but are not in charge of a Care Center.

List all Nurses that are in charge of a Care Center to which they are also assigned.

List all Laboratories, where all assigned technicians to that laboratory achieve at least one skill.

List all Resident patients that were admitted after the most current employee hire date.

Find all Patients who have been admitted within one week of their Contact_Date.

List all Physicians who have made more than 3 visits on a single day.