Download Hospital Database Management Project: ER Diagram, Create Table Statements, and SQL Queries and more Schemes and Mind Maps Data Structures and Algorithms in PDF only on Docsity!
Project Title : Hospital Database Management Definition: This project helps to manage hospital database. The project contains 4 entity sets and relationship sets between them. This project has Medical Record, Patient, Hospital, Doctor as entity sets and their attributes. Project Members & Task Responsibilities: Name Id Member Task Responsibilities 1 Mahmoud Al Khatib 2019672 Designing E-R Diagram (b), Writing Create Table Statements (d) Writing SQL Queries (f) 2 Oğuz Karaca 1902257 Drawing Database Schema Diagram (c), Writing INSERT INTO Statements (e),
E-R Diagram:
Create Table: Create table Patient(name varchar2(20), pa_email varchar2(20) primary key,gender varchar2(6),adress varchar2(20),password number(6)); Create table Medical History(surgeries varchar2(50), medication varchar2(50), conditions varchar2(50), md_id number(6)primary key, Date/time varchar2(20), pa_email varchar2(20) references patient(pa_email)); Create table Doctors(dc_email varchar2(20) primary key, gender varchar2(6), name varchar2(20), pasword number()); Create table Schedules(Day date, breaks varchar2(20), sc_id number(6) primary key, start time varchar2(20),end time varchar2(20)); Create table Appointments(end time varchar2(20), ap_id number(6) primary key, date date, start time varchar2(20), status varchar2(20), concerns varchar2(70),symptoms varchar2(70),diagnosis varchar2(70),perscription varchar(70),pa_email varchar2(20) references patients,dc_email varchar2(20) references Doctors); Create table Open/Update(md_id number(6) references Medical History(md_id number),dc_email varchar2(20) references Doctors(dc_email), open_update_pk primary(md_id ,dc email)); Create table Correspond(sc_id number(6) references Schedules,ap_id number(6) references Appoinments,correspond_pk primary(sc_id,ap_id)); Create table Follow(dc_email varchar2(20) references Doctors, sc_id number(6) references Schedules, follow_pk primary (dc_email,sc_id)); Insert Into:
INSERT INTO Appointments VALUES (’12.00’, 839285, TO_DATE(‘11/29/2020’, ’mm/dd/yyyy’), ’09.00’, ‘Risky’, ‘ Death’, ‘Chest Pain’, ‘Might Heart Attack’, ‘Antiplatelet’, ‘lily@gmail’, ‘adam@gmail’); INSERT INTO Open/Update VALUES (728139, ‘charlie@gmail’); INSERT INTO Open/Update VALUES (123145, ‘rachel@gmail’); INSERT INTO Open/Update VALUES (920349, ‘adam@gmail’); INSERT INTO Correspond VALUES (847395, 123412); INSERT INTO Correspond VALUES (531321, 912382); INSERT INTO Correspond VALUES (894252, 839285); INSERT INTO Follow VALUES (‘charlie@gmail’, 847395); INSERT INTO Follow VALUES (‘rachel@gmail’, 531321); INSERT INTO Follow VALUES (‘adam@gmail’, 894252);
