DATABASE_EX
increase_up
·
updated
SQL
-- 병실
CREATE TABLE HospitalRoom (
RoomNumber VARCHAR2(10) PRIMARY KEY,
RoomType VARCHAR2(20),
RoomLocation VARCHAR2(50) NOT NULL,
BedCount NUMBER
);
-- 환자
CREATE TABLE Patient (
PatientID VARCHAR2(10) PRIMARY KEY,
Name VARCHAR2(30) NOT NULL,
BirthDate DATE NOT NULL,
Gender CHAR(1) NOT NULL,
Insurance VARCHAR2(20) DEFAULT 'N',
Address VARCHAR2(100),
Contact VARCHAR2(15),
RoomNumber VARCHAR2(10),
FOREIGN KEY (RoomNumber) REFERENCES HospitalRoom(RoomNumber) ON DELETE CASCADE
);
-- 진료과
CREATE TABLE Department (
DepartmentID VARCHAR2(10) PRIMARY KEY,
DepartmentName VARCHAR2(30) NOT NULL,
DepartmentLocation VARCHAR2(50)
);
-- 의사
CREATE TABLE Doctor (
DoctorID VARCHAR2(10) PRIMARY KEY,
Name VARCHAR2(30) NOT NULL,
Specialty VARCHAR2(30),
Contact VARCHAR2(15),
DepartmentID VARCHAR2(10) NOT NULL,
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID) ON DELETE CASCADE
);
-- 간호사
CREATE TABLE Nurse (
NurseID VARCHAR2(10) PRIMARY KEY,
Name VARCHAR2(30) NOT NULL,
Contact VARCHAR2(15),
DepartmentID VARCHAR2(10) NOT NULL,
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID) ON DELETE CASCADE
);
-- 진료기록
CREATE TABLE MedicalRecord (
RecordID VARCHAR2(10) PRIMARY KEY,
RecordDate DATE NOT NULL,
RecordDetails VARCHAR2(200) NOT NULL,
PatientID VARCHAR2(10) NOT NULL,
DoctorID VARCHAR2(10) NOT NULL,
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID) ON DELETE CASCADE,
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID) ON DELETE CASCADE
);
-- 처방
CREATE TABLE Prescription (
PrescriptionID VARCHAR2(10) PRIMARY KEY,
PrescriptionDate DATE NOT NULL,
RecordID VARCHAR2(10),
DoctorID VARCHAR2(10),
FOREIGN KEY (RecordID) REFERENCES MedicalRecord(RecordID) ON DELETE CASCADE,
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID) ON DELETE CASCADE
);
-- 약물
CREATE TABLE Medication (
MedicationID VARCHAR2(10) PRIMARY KEY,
MedicationName VARCHAR2(50) NOT NULL
);
-- 수술환자
CREATE TABLE SurgeryPatient (
SurgeryID VARCHAR2(10) PRIMARY KEY,
SurgeryDuration NUMBER,
SurgeryDate DATE,
PatientID VARCHAR2(10),
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID) ON DELETE CASCADE
);
-- 수술의사
CREATE TABLE SurgeryDoctor (
SurgeryID VARCHAR2(10),
DoctorID VARCHAR2(10),
LeadSurgeon CHAR(3) DEFAULT 'N' NOT NULL,
PRIMARY KEY (SurgeryID, DoctorID),
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID) ON DELETE CASCADE
);
-- 약물정보
CREATE TABLE MedicationInfo (
PrescriptionID VARCHAR2(10),
MedicationID VARCHAR2(10),
DosageCount INT,
PRIMARY KEY (PrescriptionID, MedicationID),
FOREIGN KEY (PrescriptionID) REFERENCES Prescription(PrescriptionID) ON DELETE CASCADE,
FOREIGN KEY (MedicationID) REFERENCES Medication(MedicationID) ON DELETE CASCADE
);
-- 예약
CREATE TABLE Reservation (
ReservationNumber VARCHAR(10) NOT NULL,
ReservationDate DATE NOT NULL,
ReservationStatus CHAR(1) DEFAULT 'N' NOT NULL,
PRIMARY KEY (ReservationNumber)
);
-- 진료예약
CREATE TABLE AppointmentReservation (
ReservationNumber VARCHAR(10) NOT NULL,
PatientID VARCHAR(10) NOT NULL,
DoctorID VARCHAR(10) NOT NULL,
PRIMARY KEY (ReservationNumber),
FOREIGN KEY (ReservationNumber) REFERENCES Reservation(ReservationNumber) ON DELETE CASCADE,
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID) ON DELETE CASCADE,
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID) ON DELETE CASCADE
);
-- 진료
CREATE TABLE Treatment (
PatientID VARCHAR(10) NOT NULL,
DoctorID VARCHAR(10) NOT NULL,
PRIMARY KEY (PatientID, DoctorID),
FOREIGN KEY (PatientID) REFERENCES Patient(PatientID) ON DELETE CASCADE,
FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID) ON DELETE CASCADE
);
-- 병원가족의사
CREATE TABLE HospitalFamily_Doctor (
Name VARCHAR2(30),
DoctorID VARCHAR2(10),
ID_card_number VARCHAR2(14),
PRIMARY KEY (Name, DoctorID)
);
-- 병원가족 간호사
CREATE TABLE HospitalFamily_Nurse (
Name VARCHAR2(30),
NurseID VARCHAR2(10),
ID_card_number VARCHAR2(14),
PRIMARY KEY (Name, NurseID)
);
-- 병실
INSERT INTO HospitalRoom VALUES ('301', '일반', '3층', 3);
INSERT INTO HospitalRoom VALUES ('302', '일반', '3층', 6);
INSERT INTO HospitalRoom VALUES ('701', 'VIP', '7층', 1);
INSERT INTO HospitalRoom VALUES ('401', '일반', '4층', 3);
INSERT INTO HospitalRoom VALUES ('501', '일반', '5층', 3);
INSERT INTO HospitalRoom VALUES ('502', '일반', '5층', 5);
INSERT INTO HospitalRoom VALUES ('702', 'VIP', '7층', 1);
-- 환자
INSERT INTO Patient VALUES ('P001', '김철수', '1985-02-14', 'M', 'N', '서울시 강남구', '010-1234-5678', '301');
INSERT INTO Patient VALUES ('P002', '이영희', '1990-05-20', 'F', 'N', '서울시 서초구', '010-2234-5678', '302');
INSERT INTO Patient VALUES ('P003', '박지훈', '2000-08-14', 'M', 'N', '부산시 해운대구', '010-3234-5678', '401');
INSERT INTO Patient VALUES ('P004', '최수민', '1995-12-10', 'F', 'N', '대전시 유성구', '010-4234-5678', '702');
INSERT INTO Patient VALUES ('P005', '정지훈', '1987-04-25', 'M', 'N', '서울시 송파구', '010-5234-5678', '701');
INSERT INTO Patient VALUES ('P006', '윤미라', '1992-07-17', 'F', 'N', '서울시 성북구', '010-6234-5678', '501');
INSERT INTO Patient VALUES ('P007', '송혜민', '1989-11-11', 'F', 'N', '광주시 남구', '010-7234-5678', NULL);
-- 진료과
INSERT INTO Department VALUES ('DEPT01', '신장내과', '1층');
INSERT INTO Department VALUES ('DEPT02', '신경외과', '2층');
INSERT INTO Department VALUES ('DEPT03', '일반외과', '3층');
INSERT INTO Department VALUES ('DEPT04', '흉부외과', '4층');
INSERT INTO Department VALUES ('DEPT05', '정형외과', '5층');
INSERT INTO Department VALUES ('DEPT06', '간담췌외과', '6층');
INSERT INTO Department VALUES ('DEPT07', '산부인과', '7층');
-- 의사
INSERT INTO Doctor VALUES ('D001', '한지민', '사구체 질환', '010-8888-1111', 'DEPT01');
INSERT INTO Doctor VALUES ('D002', '김수현', '뇌종양', '010-8888-2222', 'DEPT02');
INSERT INTO Doctor VALUES ('D003', '이병헌', NULL, '010-8888-3333', 'DEPT03');
INSERT INTO Doctor VALUES ('D004', '김대전', '폐암', '010-8888-4444', 'DEPT04');
INSERT INTO Doctor VALUES ('D005', '권혁민', '인대손상 및 파열', '010-8888-5555', 'DEPT05');
INSERT INTO Doctor VALUES ('D006', '박서준', '간이식', '010-8888-6666', 'DEPT06');
INSERT INTO Doctor VALUES ('D007', '김태리', '비파열성 뇌동맥류', '010-8888-7777', 'DEPT02');
-- 간호사
INSERT INTO Nurse VALUES ('N001', '최미라', '010-4444-1111', 'DEPT01');
INSERT INTO Nurse VALUES ('N002', '정수민', '010-4444-2222', 'DEPT02');
INSERT INTO Nurse VALUES ('N003', '박수연', '010-4444-3333', 'DEPT03');
INSERT INTO Nurse VALUES ('N004', '김도연', '010-4444-4444', 'DEPT04');
INSERT INTO Nurse VALUES ('N005', '이하나', '010-4444-5555', 'DEPT05');
INSERT INTO Nurse VALUES ('N006', '홍지수', '010-4444-6666', 'DEPT06');
INSERT INTO Nurse VALUES ('N007', '윤소희', '010-4444-7777', 'DEPT07');
-- 진료기록
INSERT INTO MedicalRecord VALUES ('R001', '2024-11-20', '신장병 진단', 'P001', 'D001');
INSERT INTO MedicalRecord VALUES ('R002', '2024-11-21', '뇌졸증', 'P002', 'D002');
INSERT INTO MedicalRecord VALUES ('R003', '2024-11-22', '급성 천공성 충수염', 'P003', 'D003');
INSERT INTO MedicalRecord VALUES ('R004', '2024-11-23', '폐암', 'P004', 'D004');
INSERT INTO MedicalRecord VALUES ('R005', '2024-11-24', '인대 파열', 'P005', 'D005');
INSERT INTO MedicalRecord VALUES ('R006', '2024-11-25', '간경변증', 'P006', 'D006');
INSERT INTO MedicalRecord VALUES ('R007', '2024-11-26', '뇌동맥류', 'P007', 'D007');
-- 처방
INSERT INTO Prescription VALUES ('PRES001', '2024-11-20', 'R001', 'D001');
INSERT INTO Prescription VALUES ('PRES002', '2024-11-21', 'R002', 'D002');
INSERT INTO Prescription VALUES ('PRES003', '2024-11-22','R003', 'D003');
INSERT INTO Prescription VALUES ('PRES004', '2024-11-23', 'R004', 'D004');
INSERT INTO Prescription VALUES ('PRES005', '2024-11-24', 'R005', 'D005');
INSERT INTO Prescription VALUES ('PRES006', '2024-11-25', 'R006', 'D001');
INSERT INTO Prescription VALUES ('PRES007', '2024-11-26', 'R007', 'D002');
-- 약물
INSERT INTO Medication VALUES ('M001', '타이레놀');
INSERT INTO Medication VALUES ('M002', '아스피린');
INSERT INTO Medication VALUES ('M003', '이부프로펜');
INSERT INTO Medication VALUES ('M004', '알레그라');
INSERT INTO Medication VALUES ('M005', '로페라미드');
INSERT INTO Medication VALUES ('M006', '암로디핀');
INSERT INTO Medication VALUES ('M007', '세로퀄');
-- 수술환자
INSERT INTO SurgeryPatient VALUES ('S001', 2, '2024-11-20','P001');
INSERT INTO SurgeryPatient VALUES ('S002', 3, '2024-11-21','P002');
INSERT INTO SurgeryPatient VALUES ('S003', 1, '2024-11-22','P003');
INSERT INTO SurgeryPatient VALUES ('S004', 4, '2024-11-23','P004');
INSERT INTO SurgeryPatient VALUES ('S005', 2, '2024-11-24','P005');
INSERT INTO SurgeryPatient VALUES ('S006', 5, '2024-11-25','P006');
INSERT INTO SurgeryPatient VALUES ('S007', 3, '2024-11-26','P007');
-- 수술의사
INSERT INTO SurgeryDoctor VALUES ('S001', 'D001', 'Y');
INSERT INTO SurgeryDoctor VALUES ('S002', 'D002', 'Y');
INSERT INTO SurgeryDoctor VALUES ('S003', 'D003', 'Y');
INSERT INTO SurgeryDoctor VALUES ('S004', 'D004', 'Y');
INSERT INTO SurgeryDoctor VALUES ('S005', 'D005', 'Y');
INSERT INTO SurgeryDoctor VALUES ('S006', 'D001', 'Y');
INSERT INTO SurgeryDoctor VALUES ('S007', 'D002', 'Y');
-- 약물정보
INSERT INTO MedicationInfo VALUES ('PRES001', 'M001', 3);
INSERT INTO MedicationInfo VALUES ('PRES001', 'M002', 2);
INSERT INTO MedicationInfo VALUES ('PRES002', 'M003', 1);
INSERT INTO MedicationInfo VALUES ('PRES002', 'M004', 1);
INSERT INTO MedicationInfo VALUES ('PRES003', 'M005', 2);
INSERT INTO MedicationInfo VALUES ('PRES004', 'M006', 1);
INSERT INTO MedicationInfo VALUES ('PRES004', 'M007', 1);
-- 예약
INSERT INTO Reservation VALUES ('R001', '2024-11-25', 'Y');
INSERT INTO Reservation VALUES ('R002', '2024-11-26','Y');
INSERT INTO Reservation VALUES ('R003', '2024-11-27', 'Y');
INSERT INTO Reservation VALUES ('R004', '2024-11-28','N');
INSERT INTO Reservation VALUES ('R005', '2024-11-29','Y');
INSERT INTO Reservation VALUES ('R006', '2024-11-30', 'Y');
INSERT INTO Reservation VALUES ('R007', '2024-12-01','Y');
-- 진료예약
INSERT INTO AppointmentReservation VALUES ('R001', 'P001', 'D001');
INSERT INTO AppointmentReservation VALUES ('R002', 'P002', 'D002');
INSERT INTO AppointmentReservation VALUES ('R003', 'P003', 'D003');
INSERT INTO AppointmentReservation VALUES ('R004', 'P004', 'D004');
INSERT INTO AppointmentReservation VALUES ('R005', 'P005', 'D005');
INSERT INTO AppointmentReservation VALUES ('R006', 'P006', 'D006');
INSERT INTO AppointmentReservation VALUES ('R007', 'P007', 'D007');
-- 진료
INSERT INTO Treatment VALUES ('P001', 'D001');
INSERT INTO Treatment VALUES ('P002', 'D002');
INSERT INTO Treatment VALUES ('P003', 'D003');
INSERT INTO Treatment VALUES ('P004', 'D004');
INSERT INTO Treatment VALUES ('P005', 'D005');
INSERT INTO Treatment VALUES ('P006', 'D006');
INSERT INTO Treatment VALUES ('P007', 'D007');
-- 병원가족의사
INSERT INTO HospitalFamily_Doctor VALUES ('김태형', 'D001','123456-1234567');
INSERT INTO HospitalFamily_Doctor VALUES ('박지현', 'D002','234567-2345678');
INSERT INTO HospitalFamily_Doctor VALUES ('이준호', 'D003','345678-3456789');
INSERT INTO HospitalFamily_Doctor VALUES ('한승희', 'D004','456789-4567890');
INSERT INTO HospitalFamily_Doctor VALUES ('송지아', 'D005','567890-5678901');
INSERT INTO HospitalFamily_Doctor VALUES ('윤세영', 'D006','678901-6789012');
INSERT INTO HospitalFamily_Doctor VALUES ('허진호', 'D007','789012-7890123');
-- 병원가족 간호사
INSERT INTO HospitalFamily_Nurse VALUES ('장하은', 'N001','890123-8901234');
INSERT INTO HospitalFamily_Nurse VALUES ('고은비', 'N002','901234-9012345');
INSERT INTO HospitalFamily_Nurse VALUES ('정예린', 'N003','012345-0123456');
INSERT INTO HospitalFamily_Nurse VALUES ('민경민', 'N004','112233-1122334');
INSERT INTO HospitalFamily_Nurse VALUES ('오채린', 'N005','223344-2233445');
INSERT INTO HospitalFamily_Nurse VALUES ('문서진', 'N006','334455-3344556');
INSERT INTO HospitalFamily_Nurse VALUES ('심다혜', 'N007','445566-4455667');
--의사 전문 분야
CREATE VIEW ReservesStat_Inquiry
AS SELECT AppointmentReservation.ReservationNumber, Patient.Name AS "PatientName", Reservation.ReservationDate, Reservation.ReservationStatus, Doctor.Name AS "DoctorName"
FROM AppointmentReservation, Reservation, Patient, Doctor
WHERE AppointmentReservation.ReservationNumber = Reservation.ReservationNumber
AND AppointmentReservation.PatientID = Patient.PatientID
AND AppointmentReservation.DoctorID = Doctor.DoctorID;
CREATE VIEW Prescription_INQUIRY
AS SELECT Patient.PatientID,Patient.Name, Patient.BirthDate, Doctor.Name, Medication.MedicationID, Medication.MedicationName
FROM Patient, Doctor, Medication, Prescription, MedicalRecord, MedicationInfo
WHERE Prescription.RecordID = MedicalRecord.RecordID
AND MedicalRecord.PatientID = Patient.PatientID
AND MedicalRecord.DoctorID = Doctor.DoctorID
AND Prescription.PrescriptionID = MedicationInfo.PrescriptionID
AND MedicationInfo.MedicationID = Medication.MedicationID;
--예약 승인 안된 환자 조회
INSERT INTO Patient VALUES ('P008', '임동규', '2002-02-14', 'M', 'N', '경기도 수원시', '010-1234-5678', '301');
--환자 정보
INSERT INTO AppointmentReservation VALUES ('R008', 'P008', 'D003');
--진료 예약
INSERT INTO Reservation VALUES ('R008', '2024-12-10', 'Y');
--예약 정보
SELECT * FROM ReservesStat_Inquiry where ReservationStatus = 'Y';
--예약된 환자 정보 조회
INSERT INTO MedicalRecord VALUES ('R009', '2024-12-10', '인대 파열', 'P008', 'D003');
--처방전
SELECT * FROM MedicalRecord WHERE PatientID = 'P008';
--진료기록 조회
INSERT INTO Prescription VALUES ('PRES008', '2024-12-10', 'R009', 'D003');
--
SELECT * FROM Prescription_INQUIRY WHERE PatientID = 'P008';
Output
Embed on website
To embed this program on your website, copy the following code and paste it into your website's HTML:
Comments
This comment belongs to a banned user and is only visible to admins.
This comment belongs to a deleted user and is only visible to admins.