Question

I am doing a project and need an ER diagram, as well as some, create table statements in SQL.

Here is what I have so far.

My database will be a hospital management system. It will be able to keep track of patients and doctors, as well as each of their attributes, to better help organize the hospital's data. Each patient and doctor will be searchable via an id, and the user will be able to run reports as well. The goal of this database is to eliminate hard records and free up physical space.

There will be a homepage with information about the hospital along with a contact tab, and a login tab. Inside the login tab, there will be a new customer, new doctor, patient lookup, doctor lookup, and reports, allowing the user to pinpoint the exact data they need to locate at any given time.

User Requirements:

           The user will be able to search for the id or other attribute of the patient or doctor. The user will also be able to add, edit, and remove entries. They will be able to run reports and sort by type, I.D.s, alphabetically, or other. Each doctor, patient, or will have its own id, PID for patients and DID for doctors.

Data:

1.     Accumulating Data:

This data will be for things like how many beds have been used at the time the patient is admitted, how many visits the patient has had, how many patients the doctor has seen, etc.

2.     Unchangeable Data:

This data will be used for ensuring each patient and doctor has their own unique identifying number

3.     User Specified Data:

This data will be what the user types in that will be unique to each entry. For the patients, the user will have to enter things like: Patient name, the room number they are being admitted to, admission date, discharge date, contact phone number for the patient, DOB for the patient, their address, a description of their problem, gender, and other relevant information. Doctors will each have their own information too, such as name, what they specialize in, what kind of doctor they are (ENT, pediatrician, etc.), their contact information (email and phone number), and their physical address.

4.     Computer Calculated Data:

Lastly, this data will be what will be calculated based on certain factors. An example of something that will be able to be calculated is how long a patient has been in the hospital.

Answer 1

The ER diagram is attached:

The create table statements (in mySQL, some small syntactical changes may be required for other dbs) is below:

create table if not exists patient (
pid INT auto_increment primary key,
pname VARCHAR (50),
dob DATE,
phone VARCHAR (15),
email VARCHAR (50),
address VARCHAR (150),
gender VARCHAR (1),
symptoms VARCHAR (500)
);
create table if not exists doctor (
did INT auto_increment primary key,
dname VARCHAR (50),
email VARCHAR (50),
phone VARCHAR (15),
address VARCHAR (150),
specialization VARCHAR (30)
);
create table if not exists hospital_rooms (
rid INT primary key,
allocated VARCHAR(1)
);
create table if not exists admissions (
pid INT,
FOREIGN KEY (pid) REFERENCES patient(pid),
did INT,
FOREIGN KEY (did) REFERENCES doctor(did),
admitdate DATE,
room INT,
FOREIGN KEY (room) REFERENCES hospital_rooms(rid),
dischargedate DATE,
PRIMARY KEY (pid)
);