Question

Lab 7

In the “Care One” hospital each patient is assigned to just one doctor. Each doctor can visit many or zero patients. The following image illustrates a small part of this hospital. Please create a schema with name of “careone”, then add these two tables. You should insert 4 doctors and 6 patients in this database (just mockup information). All of the creations and insertions should be done by writing queries. The queries should contain the Data Definition, constraints, primary key, foreign key, Data manipulation, etc. ➢ The relationship type should be applied as constraints Please submit your script as a txt file.

CSIT 154. Database

Answer 1

-- creating schema careone :
create user careone identified by careone;

-- creating table doctors:

CREATE TABLE DOCTORS
(  
DOCTOR_ID NUMBER,
DOCTOR_NAME VARCHAR2(100),
CONSTRAINT DOCTOR_PK PRIMARY KEY (DOCTOR_ID)
);

-- inserting data into DOCTORS table
Insert into DOCTORS (DOCTOR_ID,DOCTOR_NAME) values (101,'JOHN DOE');
Insert into DOCTORS (DOCTOR_ID,DOCTOR_NAME) values (102,'LEWIS DELL');
Insert into DOCTORS (DOCTOR_ID,DOCTOR_NAME) values (103,'MATHEW HARDY');
Insert into DOCTORS (DOCTOR_ID,DOCTOR_NAME) values (104,'JOE FROST');
commit;

-- creating table patients:
CREATE TABLE PATIENTS
(  
PATIENT_ID NUMBER,
PATIENT_NAME VARCHAR2(100 ),
DOCTOR_ID NUMBER,
CONSTRAINT PATIENT_PK PRIMARY KEY (PATIENT_ID),
CONSTRAINT PATIENT_DOCTOR_FK FOREIGN KEY (DOCTOR_ID) REFERENCES DOCTORS (DOCTOR_ID)
);

-- inserting data into PATIENTS table

Insert into PATIENTS (PATIENT_ID,PATIENT_NAME,DOCTOR_ID) values (201,'SAM MATHEW',101);
Insert into PATIENTS (PATIENT_ID,PATIENT_NAME,DOCTOR_ID) values (202,'JOHN PEARSON',101);
Insert into PATIENTS (PATIENT_ID,PATIENT_NAME,DOCTOR_ID) values (203,'MATT JOHNSON',102);
Insert into PATIENTS (PATIENT_ID,PATIENT_NAME,DOCTOR_ID) values (204,'SAIRA LISBETH',103);
Insert into PATIENTS (PATIENT_ID,PATIENT_NAME,DOCTOR_ID) values (205,'KATE JOUNER',104);
Insert into PATIENTS (PATIENT_ID,PATIENT_NAME,DOCTOR_ID) values (206,'ELVIS HOARDING',104);

commit;