|
CTIS254
-
Fundamentals
Of
Database Systems
INPATRAS
Project Group 1
Section 03
Abdullah Arslan : 20102348
Erman Ebren : 20200826
Emrah Sağlık : 20200991
Rasim Yutcan : 20102416
Intelligent Patient Tracking System
(INPATRAS)
INPATRAS is a patient tracking system to be used in a hospital by the doctors, test laboratory personnel and the hospital management staff.
It is a very efficient system that is used to keep the data for events which is occuring in hospital.
Our system is a small example of real hospital database. (Most of the details are not included).
This system based on two main parts. The first part is the randezvous class between the doctor and patient. The second part is based on personal records by using person class.
We have PERSON class which is parent class of Employee and Patient classes. The Doctor and Laborant classes are child classes of the Employee class.
The other part consist of aplication information such as anlysis, randezvous. Anlysis can be two type : Blood and Radiology .
Data Dictionary :
Table of Entity Classes
ENTITY
DESCRIPTION
Person
All the users, including workers and patients
Employee
People who work in the hospital including laborants and doctors
Department
Departments of the hospital
Doctor
The doctor in the hospital
Laborant
The laboratory assists in the hospital
Patient
The patients who come to the hospital
AnalysisInfo
Information about lab results of the patients
RandezvousInfo
Appointment information about the patients and doctors
Blood
Keeps the results of the blood test
Radiology
Keeps the results of the radiology test
Table of Attributes
ENTITY CLASS
ATTRIBUTE
CONSTRAINTS
Person
identityNo
Key
name
String ,Not Null
surname
String ,Not Null
addresses
Multivalued composite with components; city , street , zipCode, no
gender
‘M’,’F’
birthDate
Date, Not Null
phoneNumbers
Multivalued
Employee
roomNo
Number
roomPhoneNumber
Number
StartingDate
Date, Not Null
Department
id
Key
name
String, Not null
Doctor
title
String
specification
String, Not Null
Laborant
specification
String, Not Null
Patient
applicationDate
Date, Not Null
ssnNo
Number
h_ins_company
String
AnalysisInfo
analysisId
Key, Number
analysisType
String, Not Null
analysisDate
Date
Blood Results
type
String
result
String
Radiology
type
String ,Not Null
bodyPart
String, Not Null
diagnosis
String, Not Null
Randezvous Info
randezvousDate
Date, Not Null, partial key
diagnosis
String
complaints
String
prescription
String
Relationship types
Relationship types
Entity class
Entity class
Cardinality Ratio
Attribute
Needs
Randezvous info
Analysis Info
One to Many
Has randezvous
Doctor
Randezvous Info
One to many
Has randezvous
Patient
Randezvous Info
One to many
Makes
Laborant
Analysis Info
One to Many
Works
Doctor
Department
One to Many
manages
Doctor
Department
One to One
Schemas:
Person: (identityNo number, name string, surname string, gender string, birthDate date)
Addresses: (identityNo number references Person, city string, street string, no number, zipCode string)
Phonenumbers: (identityNo number references Person, phoneNumber string)
Employee: (identityNo number references Person, startingDate date, roomNo number, roomPhoneNumber string)
Doctor: (identityNo number references Employee, departmentId references Department, title string, specification string)
Department: (id number, name string, managerNumber number references Doctor)
Patient: (identityNo number references Person, h_ins_company string, ssnNo number, applicationDate date)
Laborant: (identityNo number references Employee, specification string)
Analysisinfo: (analysisId number, patientId number references Patient, randezveousdate date references Randezveousinfo, laborantId number references Laborant, analysisDate date, analysisType string)
BloodResults: (analysisId number references Analysisinfo, type string, result string)
Radiology: (analysisId number references Analysisinfo, type string,bodypart string,diagnosis string)
Randezveousinfo: (patientId number references Patient, doctorId number references doctor, randezveousDate date, complaints string, diagnosis string, prescription string)
Behavioral Model
· A doctor may not have more than one randezvous at the same time.
· A doctor may not have more than 15 randezvous at a day.
· Randezvous Informations are kept for 50 years and then deleted.
· A department may not have more then 100 randezvous in a day.
· A laborant may not have more than 50 analysis at same day.
QUERIES;
Department-Doctor List
By using this query, we can reach our doctor list by viewing their departments in any time. In addition to these by showing their room phone numbers and room numbers we can reach to them easily.
Employee Locations in Hospital;
This query helps us abour reaching to our employees in a needed situation. In addition to these we can easily see our emplyees position in hospital building.
Employees Who Live in Ankara Query;
This query helps us to learn about our employees which are living in Ankara. By showing their addresses we can easşky reach them whenever we want.
List of Child Patients;
By this query we can easily see our child patients.
Note for instructor: We made a calculated field, normally it was working. But when we change our database to Access 97 format. It began not to working. Then we understood that “Date()” function gives error in our comp. According to our database we have to use “Date()” function to make a calculated field. We left it in the working situation. I hope it doesn’t give error in your computer.
List of Female Patients;
By this query we can easily see our female patients.
List of Male Patients;
By this query we can easily see our male patients.
Patient List;
This query helps us to see our patients. By showing their gender and Birth Dates we can learn more about them.
Patients with Blood type "A+";
By using this query in an emergency situation we can easily find needed blood from our patients.
Radiology Analysis;
This query shows us patients’ name and surnames who had radiology analysis with their analysis types.
The Randezvouses of Doctor Suleyman Demirel in May 2004;
This query shows us randezvouses dates of our Doctor Suleyman Demirel in May 2004.
Reports;
Patient List Report;
By this report we can easily see our patients with their gender and Birth Dates in a proper printable format.
Department-Doctor Report;
By this report we can see our doctors according to their departments in a proper and printable format;
Queries in Relational Algebra Form;
List of Female Patients;
p Patient.IdentityNo,Person.name,surname,gender (Patient ><identitynoPerson); where gender=”f”
List of Child Patients;
p Patient.IdentityNo,Person.name,surname (Patient ><identitynoPerson) where Age: ((Date()-Person.BirthDate)/365 ) < 18;
List of male Patients;
p Patient.IdentityNo,Person.name,surname,gender (Patient ><identitynoPerson); where gender=”m”
SQL QUERIES
Emrah Main query:
This query is used to show records in our Person class with the name Emrah;
Patient Phone number query:
It is used to learn patients’ telephone numbers as a list.
Patient age query:
By this query we can learn the ages of our patients and can take as a list.
Number male records in our database query:
This query shows us the number of male records in our database.
Doctor randezvous count query:
This query shows us the number of randezvous of each doctor according to grouping doctor id.
Ssk patients query:
We use this query to learn which are our patients are registered to SSK.
|
warnerblade.com 