Question

PROBLEM 3: Given the following relational database table: Patients(ID, name, symptom, days_in_hospital) The following insertions are performed on the table Patients: Insert record <20, Johnson, cough, 3> Insert record <10, Black, fever, 5> Insert record <30, Miller, fever, 10> Insert record <70, Brown, fatigue, 2> Insert record <60, Grant, headache, 4> Insert record <50, Miller, nausea, 15> Insert record <90, Brown, cough, 8 > Assume each block in the Patients file can store up to 2 patient records. Do the following: 1. Assuming that Patients is organized as a heap file, show the contents of the file after the last insertion. 2. Assuming that Patients is organized as a sequential file with days_in_hospital as the ordering field, show the contents (i.e. the data values as well as the associated block/bucket/record addresses) of the file after the last insertion. 3. Assuming that Patients is organized as an index-sequential file on the search key days_in_hospital and assuming that the primary index, the secondary index on ID, and the secondary index on name have been created, show the contents of Patients, the primary index, and the two secondary indices after the last insertion. 4. Given the index-sequential file organization as described in (3), explain step-by-step how the DBMS would conduct search on this file organization to answer the following SQL query: select name from Patients where ID between 30 and 60

Answer 1

1) INSERT INTO PATIENTS (ID, name, symptoms, days_in_hospital)

values (20, Johnson, cough, 3)

INSERT INTO PATIENTS (ID, name, symptoms, days_in_hospital)

values (10, Black, fever, 5)

INSERT INTO PATIENTS (ID, name, symptoms, days_in_hospital)

values (30, miller, fever, 10)

INSERT INTO PATIENTS (ID, name, symptoms, days_in_hospital)

values (70,Brown , fatigue, 2)

INSERT INTO PATIENTS (ID, name, symptoms, days_in_hospital)

values (60, Grant, headache, 4)

INSERT INTO PATIENTS (ID, name, symptoms, days_in_hospital)

values (50, miller, nausea, 15)

INSERT INTO PATIENTS (ID, name, symptoms, days_in_hospital)

values (90, Brown, cough, 8) ;

table PATIENTS

ID	name	symptoms	days_in_hospital
20	Johnson	cough	3
10	Black	fever	5
30	Miller	fever	10
70	Brown	fatigue	2
60	Grant	headache	4
50	Miller	nausea	15
90	Brown	cough	8

SELECT NAME

FROM PATIENTS

WHERE ID BETWEEN 30 AND 60;

NAME

Miller

the sql query selects name of patient from patient table whose ID ranges between 30 and 60 even after going through the assumptions given.