Assignment #1 CSC-671

DateofBirth

Alan K. Dippel
CSC-671
August 31,1998
Professor: Fred Sadri
Department of Mathematical Sciences
University of North Carolina at Greensboro

Assignment 1
http://www.uncg.edu/~akdippel/csc671/assignment1.htm

Question 1

Using E-R Model design a database system for a hospital.

I have taken the original suggested enitites and relationships of a hospital and added some others. The hospital is named "General Hospital" and the following diagram and charts define the database for the hospital. Below are listed a few ideas affecti ng the relationships and attributes used.

The patients are all assigned a MedRecNo that is used for all of their records. This number is unique and is used as the patients primary key.

The hospital employees are all assigned EmployeeNo a sequential number that is unique to the employee. For ease of identifying the doctors a 3 digit number is assigned to the doctor DrID as a secondary key that can be used in all of the medical records etc. The Pharmacist also has an assigned number.

Due to legal reasons and the fact that the hospital wants to make sure that no patient is given the incorrect medication, a record is made of all drugs ordered, filled and given to the patients. All of this information is kept in one table representing a tertiary relationship of doctors, patients, drug-order.

Entity and Relationship sets for the hospital called
"General Hospital".

patients, hospemployees, doctors, nurses, pharmacists, rooms-beds, visits, bed-assignments, operates, orders-prescrip, fills-prescript , gives-prescrip, drug-orders.

patients, entity set with attributes MedRecNo, LastName, FirstName, sex, DateofBirth, age, SSNo.

hospemployees, entity set with attributes EmployeeNo, LastName, FirstName, DateofHire, TimeEmployeed, BirthDate, Sex, SSNo.

doctors, entity set with attributes EmployeeNo, DrID, Specialty, Title, DateResidencyCompleted.

nurses, entity set with attributes EmployeeNo, Title, StationAssigned.

pharmacists, entity set with attributes EmployeeNo, LicenseNo, PharNo.

rooms-beds, entity set with attributes RoomNumber, BedNumber, Status.

visits, relational set with attributes Date, Time, Diagnosis.

bed-assignments, relational set with attributes Date, Time.

operates, relational set with attributes DateScheduled, TimeScheduled, OpProcCodes, Room.

orders-prescrip, relational set with no attributes.

fills-prescrip, relational set with no attributes.

gives-prescrip, relational set with no attributes.

drug-orders, entity set with attributes OrderNo, MedRecNo, DrID, DrugID, PrescriptionNo, PharNo, DateTimeOrdered, DateTimeFilled, DateTimeGiven, QuantityOrdered, QuantityFilled,

General Hospital Tables

The following tables are required for the database for "General Hospital"

patients

hospemployees

EmployeeNo	Lastname	FirstName	Sex	DateofHire	BirthDate	Phone	SSNo

doctors

EmployeeNo	DrID	Title	Specialty	DateResidencyCompleted

nurses

EmployeeNo	Title	StationAssigned

pharmacists

EmployeeNo	PharNo	LicenseNo	SSNo

rooms-beds

RoomNumber	BedNumber	Status

visits

MedRecNo	DrID	Date	Time	Diagnosis

bed-assignments

RoomNumber	BedNumber	MedRecNo	Date	Time

operates

DrID	MedRecNo	DateScheduled	TimeSchedules	Room	OpProcCodes

No table is required for orders-prescrip because data is in drug-orders table.

Fills-prescrip

MedRecNo	PharNo

drug-orders

OrderNo	DrID	MedRecNo	DrugID	DateTime Ordered	Quantity Ordered	DateTime Filled	Quantity Filled

Prescription No	DateTime Given

gives-prescrip

MedRecNo	EmployeeID

Question 2

Create the following tables (choose appropriate types for the attributes):

Patients (p-id, p-name, address, phone)
doctors (d-name, phone, specialty)
visits (date, d-name, p-id)

See attached listing of DDL and table data.