Hospital Database Management Project: ER Diagram, Create Table Statements, and SQL Queries, Schemes and Mind Maps of Data Structures and Algorithms

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 Doctors VALUES (‘charlie@gmail’, ‘Male’, ‘Charlie’, 37st59j); INSERT INTO Doctors VALUES (‘rachel@gmail’, ‘Female’, ‘Rachel’, 465klm); INSERT INTO Doctors VALUES (‘adam@gmail’, ‘Male’, ‘Adam’, abdf14); INSERT INTO Patient VALUES (‘Bob’, ‘bob@gmail’, ‘Male’, ‘Uskudar’, 59sjq76); INSERT INTO Patient VALUES (‘John’, ‘john@gmail’, ‘Male’, ‘Besiktas’, 4810tr9); INSERT INTO Patient VALUES (‘Lily’, ‘lily@gmail’, ‘Female’, ‘Kadikoy’, lps9378); INSERT INTO Schedules VALUES (TO_DATE(‘05/09/2022’, ’mm/dd/yyyy’), ’12.30-13.30’, 847395, ’15.00’, ’10.00’); INSERT INTO Schedules VALUES (TO_DATE(‘12/03/2022’, ’mm/dd/yyyy’), ’10.30-12.30’, 531321, ’17.00’, ’09.00’); INSERT INTO Schedules VALUES (TO_DATE(‘04/30/2022’, ’mm/dd/yyyy’), ’13.30-14.30’, 894252, ’16.00’, ’08.00’); INSERT INTO Medical History VALUES (‘Joint Replacement’, ‘Morphine’, ‘Broken’, 728139, ’05.04.2023 / 15.00’, ‘bob@gmail’); INSERT INTO Medical History VALUES (‘Angioplasty’, ‘Clopidogrel’, ‘Intensive Care’, 123145, ’03.01.2022 / 10.00’, ‘john@gmail’); INSERT INTO Medical History VALUES (‘Heart Bypass, ‘Antiplatelet’, ‘Intensive Care’, 920349, ’09.12.2021 / 08.00’, ‘lily@gmail’); INSERT INTO Appointments VALUES (’14.00’, 123412, TO_DATE(‘03/14/2022’, ’mm/dd/yyyy’), ’12.00’, ‘Not risky’, ‘Nothing’, ‘Arthralgia’, ‘Broken joint’, ‘Painkiller’, ‘bob@gmail’, ‘charlie@gmail’); INSERT INTO Appointments VALUES (’15.00’, 912382, TO_DATE(‘07/15/2022’, ’mm/dd/yyyy’), ’13.00’, ‘Risky’, ‘ Death’, ‘Chest Pain’, ‘Might Heart Attack’, ‘Clopidogrel’, ‘john@gmail’, ‘rachel@gmail’); 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);