CIS 330代做、代写To-Be System、代写SQL语言、代做SQL设计

日期：2018-12-02 10:29

Enhancing the As-Is Hospital Database

Creating an Improved To-Be System

For this assignment, you will enhance the Hospital database (the as-is system) based on the results of

requirements elicitation, which is described below. You will also submit an ER diagram based on your

modified database design. You are already familiar with the as-is system, which you used in the first two

SQL assignments. You will be designing, implementing, and modeling a to-be system, based on client

requirements.

Imagine that you are a consultant for the company using this database. Your client has been using the

database for some time, and wants some improvements to the as-is system. Your task is to take the results

of a requirements elicitation process (this takes place mostly during the analysis and planning phases of

the SDLC) and create both the conceptual model and the implemented database that satisfies the users’

requirements. The following section includes notes from requirements elicitation. A systems analyst (a

fellow consultant) took these notes based on interviews with the client, as well as studying HospitalDDL

and HospitalDML script files, and existing SQL query results from SQL_1 and SQL_2 assignments. All of

these contribute to your understanding of the as-is system.

Requirement Elicitation Notes

The following are notes taken by your colleague, based on interviews with the client:

Client wants to expand the database by including information about physicians. A new table should be

created, and populated with the following data (please be exact):

Physicians make diagnoses on patients, and also prescribe medicine. Client wants to keep track of each

diagnosis that any physician does for any patient. Also, client wants to keep track of all prescriptions done

by a physician for a patient.

For this you must create intersection tables connecting physicians to patients. These tables should be

populated in order to show the following specific information about diagnoses and prescriptions.

Diagnoses data (be exact):

Prescriptions data (be exact):

Client wants all meds given to be associated with the correct prescription. This requires modifying both the

structure and the content of the MEDSGIVEN table. This table will need a foreign key to the prescription.

Of course, you don’t want to lose existing data, so you should not drop and re-create the table. Instead, you

will need to alter the table to accomplish this.

After doing this, you will need to find the most efficient way, using the least possible number of update

statements, to associate the meds to the correct prescription, by matching the text of the medication to the

prescription’s text. When this is done, a query of the medsgiven table that shows the correct prescriptionto-medication

matchup would look like this:

NOTE: I accomplished the task of putting proper foreign key values using only two update statements.

Your grade on this task depends both on getting the correct values in prescription ID and doing it with the

fewest possible update statements.

After changes have been done to the database, client wants the following new queries:

1. Show details about each med given. This includes information about the prescription, the physician

who gave the prescription, the nurse and patient involved, and the details about the med given. In

addition, for any medication that was administered after the prescription’s end date, you should mark

this as Late. The results should like below:

A couple notes about this query. For the most part it’s a simple join of five tables. The LATE?

column should conditionally show “Late” based on whether or not the prescription end date is

before the date given. For this, you can make use of a CASE expression. SQL CASE expressions

are explained on page 308 in chapter 7 of the textbook.

Secondly, you can control the width of a column using the column format command in SQL Plus.

For example if you precede your query with a command like this: “column fieldname format

A25”, this will make the width of the column called fieldname to have a width of 25 characters. I

encourage you to experiment with this for improving the look of your output.

2. Client wants a query that shows details of the highest paid nurse for each unit in the hospital. This

query will involve a correlated subquery, and should produce these results:

3. Client wants to see all patients whose first or last name is in another patient’s reason for

hospitalization. In addition, client wants to see all patients who refer to another patient in their own

reason for hospitalization. Results should look like below. This query also involves a correlated

subquery.

4. Client wants to see a breakdown of nurse visits and of meds given based on the time period (AM vs.

PM). This also involves a subquery, and produces this result. Note, yours may vary based on when you

first created your database, but make sure the results are correct for your database.

Of course, you will need to test the validity of your new database implementation, so you’ll also do other

queries and database update attempts to verify data integrity of the design and correct values in certain

fields.

Required Database Script

I’ve provided a script file that contains Prompt commands displaying what is expected at each step along

the way. If you run the script file in Oracle SQL*Plus you will see an output that gives seventeen (17)

prompts. Each of these prompts is a step for you to take as you work on this project.

Note: the SQL*Plus Prompt command is useful in that it displays as output any text you’d like to present

to the user.

You will modify the provided script file and turn this is in to Canvas as part of your final deliverable. For

each Prompt in the provided script, you should do the necessary DDL or DML statement (or SQL*Plus

command) that gives the desired results.

You should place the statements and commands for each Prompt between the Prompt line and the

following Prompt with asterisks. For example, here’s what you do with the first Prompt:

Prompt 1) CREATE PHYSICIANS TABLE

Your CREATE TABLE statement goes here

Prompt *************

Prompt

By doing these tasks you are performing database activities usually associated with the design and

implementation phases of the SDLC. Via this script, you use the results of requirements elicitation in order

to transform the as-is system into the desired to-be system. Some of the tasks involve creating or altering

tables. Some involve displaying table metadata. Some involve inserting new data or changing existing data.

Some involve validating the integrity and content of the data. And some involve providing additional

information (via queries) that the client desires.

I suggest that you do these tasks in the order I give them (i.e. the order given by the Prompt statements).

Complete one task before moving on to another. Each task builds on another.

To fully test out your script, you should start with the database built from HospitalDDL and HospitalDML.

Then start the script file.

I’ve included a text file showing the results of my script file’s execution in SQL*Plus. If your script runs

successfully, the results on the SQL*Plus screen should look just like mine.

One more note. You may need to completely remove your hospital database and re-create it. To remove

your database, you can do this SQL statement in the SYSTEM account:

DROP USER HOSP CASCADE;

Then you can rebuild it.

Final Deliverables

You will turn two files in to Canvas:

1) A Visio or draw.io ER diagram for the changed database, based on your redesign. You can

capture the image of this and upload it to Canvas. I don’t want the Visio file itself. But use a

software tool for this, don’t hand-draw it. Be as precise as possible with your entities, attributes,

and relationships in this diagram.

2) Your completed HospitalToBeScript file with all your SQL statements and SQL*Plus

commands.