Case Study 10: MS SQL SERVER 2005 – Management Studio – Create tables via SQL Script RHA EXAMPLE

MS SQL SERVER 2005 - Management Studio

Regional Health Authority Database EXAMPLE

TASKS

The following Case Study

  1. Derive an Entity Relationship Diagram (ERD) showing the entities of interest and associated attributes, relationships, dependencyand indicate primary and foreign keys
  2. Implement the tables in MS SQL Server 2005 by draw the ERD in MS SQL Server 2005
  3. Populate the tables with the sample data.
  4. Provide sample SQL code to demonstrate and understanding of DDL and DML SQL statements.

You may decide to modify the data requirements or generate additional data attributes to assist in your design of the database. Write down details of any assumptions you have made that you feel are important.

You may decide to follow the ERD’s in Answer Points.

CASE STUDY:REGIONAL HEALTH AUTHORITY.

The Regional Health Authority (RHA) was formed recently to operate the admissions and in patients monitoring of each hospital within the area of its control.

The Admissions System

A consultant with a patient requiring a stay in hospital could make enquiries of the admissions system. Available beds in the hospital could be booked, or alternatively a patient could be put on the waiting list. The length of the wait would depend on the type of treatment required. The patients details, name of consultant and the type of treatment required would be recorded so that the patient could be informed when a bed became available. The name, address and telephone number of the patient’s GP would also be recorded.

The In-Patient System.

The hospital is organised into a number of wards, each of which may be empty or may include one or more patients. Each ward has a ward number and name. The number of patients occupying a ward cannot exceed the number of beds in the ward. Each ward is staffed by one or more nurses, one of whom is designated to supervise the others on the ward. Each nurse is identified by a staff number and name and is assigned to a single ward. An Id. Number and name identify each patient. Their name, address, date of birth and religion are recorded on admission to the ward.

While in hospital the patient is assigned to a single ward and is under the care of a consultant. The consultant has a particular specialist and heads a team of junior doctors. Every team has an Id. Code (generally the initials of the consultant in charge of the team). A junior doctor may be a registrar or houseman and every doctor is identified by a staff code as well as his/her name.

Each doctor may treat several patients and each patient may be treated by a number of doctors, all of whom must be members of the same team as the consultant responsible for the patient.

The treatment a patient receives includes the prescription of certain dosage of drugs such as paracetamol, morphine, codeine, etc. Each drug has a code. Records are kept of the date and dosage of each drug treatment and who prescribed it. Only doctors are allowed to prescribe treatment.

MODEL SOLUTIONS

CASE STUDY :REGIONAL HEALTH AUTHORITY.

This section documents a MS SQL based solution.

An online MS SQL solution is also available dd.cs.EM.rha.doc

Identified Entities & Attributes within the case study

Patient(Patient#, Name, DOB, Address, Town, Postcode, Phone#, GP, TeamName)

Religion(Patient#, Practicing faith)

Treatment(Patient#, Condition, Procedure’s Req, Med1, Med2, Med3)

GP(GP#, GPName, Patient#)

Waiting Lists(Pat#, ReferalDate)

Admissions (Patient#, AdmissionDate, Ward#, Bed#)

Wards(Ward#, Patient#)

Bed(Bed#, Ward#, Patient#)

Out(Patient#, DateOut)

Staff(Staff# , Name, Position, Specialist Area, Address, Town, Postcode, Phone#)

Teams(ID#, TeamName, Staff#)

Junior Doctors(Staff#, TeamName)

Consultant(Staff#, TeamName,)

Specialist(Staff#, TeamName)

Nurse(Staff#, TeamName)

1

Mansha NawazRegional Health Authority EXAMPLECase Study 10:

Case Study 10: MS SQL SERVER 2005 – Management Studio – Create tables via SQL Script RHA EXAMPLE

Entity Relationship Matrix for RHA

Partial solution has been provided to show essential features.

Patient / Religion / Treatment / GP / Wait /

Admit

/ Wards / Beds / Out / Staff / Team / J Docs / Ctants / Splist / Nurse
Patient / Belongs / Given to / Sees / Goes on / Admits / Assigned / Leaves / Treat / Treat / Consult / sees
Religion / Practices
Treatment / Given
GP / Sees
Wait / Held on
Admit / Admitted
Wards / Assigns / On
Beds / Assigns / Has
Out / Leaves
Staff / Part of
Team / Treated by / Given by / Part of / Part of / Head / Part of / Part of
Drugs / Receives / Prescribe / Gives
J Docs / Treated / Part of
Ctants / Consults / Refers / Part of
Splist / Sees / Part Of
Nurse / Cares / Part of

1

Mansha NawazRegional Health Authority EXAMPLECase Study 10:

CASE STUDY:REGIONAL HEALTH AUTHORITY.

Entity Relationships Diagram

ASCENT ERD SOLUTIONS

reg.hel.auth
outpatients
prescription

The following ERD was implemented in the script files provided. It is a simplified version of the ASCENT2 ERD provided earlier. Used to demonstrate MS SQL Server – QA implementation.

Database Tables

Script file : dd.cs.rha.schema.sql

StaffRegister (StaffNo, StaffGrade, ShiftRotaNo, StaffName, StaffAddress,StaffTelephoneNo, StaffPagerNo, StaffRoomNo)

Consultant(ConsultantNo, ConsultantName, ConsultantId)

GP (GPNo, GPName, GPAddress, GPTelephoneNo)

Treatment(TreatmentNo, PatientTypeOfTreatment, TreatmentDate)

Patient(PatientNo, PatientName, PatientAddress, PatientDateof Birth, PatientTypeofTreatment, PatientGender, PatientReligion, Patient_ConsultantNo, GPNo, TreatmentNo)

Prescription(PrescriptionNo, Prescription Date, Drug Dosage, PatientNo, DoctorNo)

Ward (WardNo, WardName, WardTelephoneNo, NumberOfBeds)

Admissions(PatientNo, AdmissionDate, WardNo)

Waiting List(PatientNo, RegistrationDate)

Beds(BedNo, WardNo, PatientNo,)

Nurses (NurseNo, NurseName, WardNo)

Consulting Team (TeamNo, ConsultantNo, ConsultantName, SpecialistNo, SpecialistName)

Junior Doctors(DoctorNo, TeamNo)

Drug(DrugNo, DrugName, PresriptionNo)

Database Table Schemas

Implemented Database Tables with sample data
Script file to create tables / dd.cs.rha.schema.sql
Script file to drop tables / dd.cs.rha.schema.delete.sql

SQL Database Tables

Admissions

select *

from admission

Bed

select *

from bed

CONSULTANT

select *

from consultant

CONSULTINGTEAM

select *

from consultingteam

DRUG

select *

from drug

GP

select *

from gp

JuniorDoctors

select *

from juniordoctors

Nurses

select *

from nurses

Patient

select *

from patient

Prescription

select *

from prescription

StaffRegister

select *

from staffregister

Treatment

select *

from treatment

WaitingList

select *

from waitinglist

Ward

select *

from ward

DDL - SQL Code Samples

Provide sample code as covered in MS Powerpoint Lecture Slide (Section 02)

SQL Overview

1

CASE STUDY: REGIONAL HEALTH AUTHORITY.