CREATE TABLE SPECIALIZATIONS( SP_Code NUMBER(5)
CONSTRAINT SPECIALIZATION_SPcode_PK PRIMARY KEY, SP_Title VARCHAR2(20)); CREATE TABLE
Doctors ( DNo Number(2) CONSTRAINT Doctors_DNo_PK PRIMARY KEY, DName Varchar2(20),
SP_Code NUMBER(5) CONSTRAINT SP_Code REFERENCES SPECIALIZATIONS(SP_Code)); CREATE
TABLE Patients ( PCode Number(3), PName Varchar2(20), Nationality Varchar2(20), Room
Number(3) CONSTRAINT Patients_Room_ck CHECK(Room > 100), Bill Number(4), AdmitDate
Date, DNo Number (2)); INSERT INTO SPECIALIZATIONS
VALUES(11525,'Cardiologist'); INSERT INTO SPECIALIZATIONS
VALUES(21252,'Allergist' ); INSERT INTO SPECIALIZATIONS
VALUES(22153,'Colon and Rectal'); INSERT INTO SPECIALIZATIONS
VALUES(24210,'Cardiac'); INSERT INTO Doctors VALUES
(91,'Daisy',11525); INSERT INTO Doctors VALUES
(92,'Muneera',21252); INSERT INTO Doctors VALUES
(93,'Kawther',11525); INSERT INTO Doctors VALUES
(94,'Mohammed',21252); INSERT INTO Doctors VALUES
(95,'Helen',24210); INSERT INTO Patients VALUES
(631,'Jimmy','British',205,1500,TO_DATE('02/05/2019',
039;MM-DD-YYYY'),91); INSERT INTO Patients VALUES
(632,'Nathan','British',151,450,TO_DATE('10/10/2015',
039;MM-DD-YYYY'),91); INSERT INTO Patients VALUES
(633,'Mark','British',325,250,TO_DATE('09/15/2010',
9;MM-DD-YYYY'),92); INSERT INTO Patients VALUES
(634,'Amna','Omani',410,150,TO_DATE('04/20/2017','
MM-DD-YYYY'),92); INSERT INTO Patients VALUES
(635,'Hisham','Omani',210,375,TO_DATE('01/25/2016',
9;MM-DD-YYYY'),93); INSERT INTO Patients VALUES
(636,'Basim','Omani',305,NULL,TO_DATE('03/25/2014',
9;MM-DD-YYYY'),93); INSERT INTO Patients VALUES
(637,'Samira','Iraqi',412,1100,TO_DATE('09/10/2015',
39;MM-DD-YYYY'),93); INSERT INTO Patients VALUES
(638,'Yasser','Iraqi',203,650,TO_DATE('06/15/2018',
9;MM-DD-YYYY'),94); INSERT INTO Patients VALUES
(639,'Ghalya','Jordanian',324,NULL,TO_DATE('08/15/2011'
,'MM-DD-YYYY'),94); INSERT INTO Patients VALUES
(640,'Wael','Jordanian',308,700,TO_DATE('07/15/2013',
039;MM-DD-YYYY'),94);
HIGHER COLLEGE OF TECHNOLOGY
DEPARTMENT: Information Technology
Final Assignment
Semester: 2
A. Y.: 2019/2020
Assignment Posted
Submission Deadline
Time: 12:00 PM
Time: 12:00 PM
Date: 09-June-2020
Date: 11-June-2020
Platform
(e.g. E-Learning, Microsoft
Teams, etc.)
E-Learning
Course Code
Course Name
Level
ITDB3208
SQL Concepts and Syntax
Advanced Diploma
GENERAL COLLEGE GUIDELINES
1) All students are given 48 hours to complete and submit their assignment from the day, date and time the assignment
is uploaded.
2) Only one assignment is given to the students within the 48-hour period given. Student should contact their
respective department if more than one assignment is given within the same period (As indicated in point 7).
3) Assignment answer scripts will be uploaded through any of the following platforms or combinations of the
following: e learning, Moodle and or any course specific platform identify by the course tutor. However, students
may submit their assignment through HCT emails in case if they have encountered any technical issue with the
platform stated above.
4) Student’s submissions will be checked through Turnitin software to ensure academic integrity, if applicable.
"According to Turnitin article on interpreting Turnitin originality report, "a high percentage of plagiarism would
probably be anything over 25%".
5) Students who fail to submit their assignment as per the deadline given; will be granted “Zero” unless otherwise
justified.
6) If students encounter any problem in submitting their course assignment, they should communicate immediately
via email to their course lecturer. The student must attach proof of their inability to submit their course assignment.
7) Students will be allowed to submit an online appeal regarding failure of assignment submission issue within three
days from the date of submission deadline. The appeals will only be accepted if the student provides genuine excuse
for the submission failure with attached evidence.
8) Student may need to ask for support assistant with two related matters as following:
a) Technical issues; will be tackled by the specialized technicians from Educational Technology Center (ETC).
b) Course specific inquiries/support issue; students who require any clarifications on the assignments can
immediately contact their respective course tutors /lecturers through emails (within the 48-hour period given).
Course tutors/ lecturers should respond promptly to the student’s doubts and queries.
The following are the necessary contact details for technical and department specific support:
- For technical support please contact the below e mail as follow:
Any Issue Related: E-mails accounts and Microsoft TEAMS
Any Issue Related: E-Learning Moodle
Any Other IT Troubleshooting
Office365support@hct.edu.om
Support.elearning@hct.edu.om
Helpdesk2@hct.edu.om
-For Academic related support please contact the below e mail as per the academic department identify below:
Mr. Roby Surendran
Mr. Mohammed Mushtaq
Mr. Jagathprasad Shreedhar
Mr. Bhaskar Ponna
Ms. Rachana Marathe
Ms. Jacqueline Fat Yara
roby.surendran@hct.edu.om
md_mushtaq@hct.edu.om
jagath.sreedhar@hct.edu.om
ponna.bhaskar@hct.edu.om
rachana.marathe@hct.edu.om
jacqueline.yara@hct.edu.om
Page 1 of 9
Higher College of Technology
Information Technology Department
STUDENT DECLARATION FORM
FOR ASSIGNMENT
DEPARTMENT GUIDELINES: TERMS AND CONDITIONS
1. Never share or post the test questions or answer scripts for any purpose.
2. Starting from the posting of the question test papers online, the students are given exactly 48 hours to
submit their answers.
At the end of the 48 hours, the submission link shall be deleted or deactivated. NO EXTENSION
WILL BE GIVEN. Failure to submit the answer within the 48-hour period will result to ZERO mark.
3. In case of technical problems, inform the Lecturer immediately through the HCT Email or through
chat message in MS Teams, at least two (2) hours before the expiration of the 48-hour period.
4. For IT exams, there is a separate Answer Script Template where students should type their answers.
For Math exams, handwritten answers are allowed. Make sure to include Student Name on each page
and the question number to avoid confusion.
5. Do the proper citation in every answer taken from other sources, if applicable.
6. For handwritten answers, submit a SCANNED COPY of the answers IN SINGLE FILE. [You can
use any available mobile app scanner if in case you do not have a scanner device.]
7. Send the scanned SIGNED COPY of this Declaration Form along with the answer scripts to the
Lecturer. The Lecturer will NEVER mark any answer scripts without this signed Declaration Form. [If
printer is not available, write this Declaration Form in A4 paper and affix the signature.]
8. Upload the ANSWER SCRIPTS ALONG WITH THE DECLARATION FORM in the E-Learning
as written on the exam paper.
9. Always abide by the College Academic Integrity and Honesty
https://www.hct.edu.om/pdf/pms/academic-integrity-and-honesty-policy.pdf.
Policy
found
at
10. Abide by the guidelines mentioned above and any violation of the same will be subjected for a
disciplinary action or get ZERO mark, as the case may be.
STUDENT DECLARATION
I, (Student’s Name)…… …………….……………………………………………… with (Student’s ID): ……………. of
……………. (Level)………………….. and who belongs to (Section)……… of the (Course Code/Course
Title)………………………..…………………………………. offered by the (Department)………. department, hereby
declare that my submission of Assignment is a result of my own original work except for source materials explicitly
acknowledged by proper citations.
I also understand that plagiarism and cheating are offenses that can lead to disciplinary action and GRADE OF ZERO, as
the case may be.
Signature: …………………………………………..
Date (dd-mm-yyyy): …………………………….
Page 2 of 9
General Directions:
Open the Final Assignment in the SQL Concepts and Syntax Course in E-Learning.
Download the Final assignment pack and upload the script file named as “FinalScript_ITDB3208” in your
CLOUD account to retrieve the following tables: DOCTORS, PATIENTS and SPECIALIZATIONS.
Use the official Answer Script File to add all your answers, then upload it along with the DECLARATION
FORM in the E-Learning.
TABLE STRUCTURE for your reference only:
DOCTORS
Column Name
DNo
DName
SP_Code
SPECIALIZATIONS
Data Type
Number(2)
Varchar2(20)
Number(5)
Constraint Name
DOCTORS_DNo_Pk
Column Name
SP_Code
SP_Title
Data Type
Number(5)
Varchar2(20)
Constraint Name
SP_Code
PATIENTS
Column Name
PCode
PName
Nationality
Room
Bill
AdmitDate
DNo
Data Type
Number(3)
Varchar2(20)
Varchar2(20)
Number(3)
Number(4)
Date
Number(2)
Constraint Name
PATIENTS_Room_CK
RECORDS:
DOCTORS
DNo
91
92
93
94
95
SPECIALIZATIONS
DName
Daisy
Muneera
Kawther
Mohammed
Helen
SP_Code
11525
21252
11525
21252
24210
SP_Code
11525
21252
22153
24210
SP_Title
Cardiologist
Allergist
Colon and Rectal
Cardiac
PATIENTS
PCode
631
632
633
634
635
636
637
638
639
640
PName
Jimmy
Nathan
Mark
Amna
Hisham
Basim
Samira
Yasser
Ghalya
Wael
Nationality
British
British
British
Omani
Omani
Omani
Iraqi
Iraqi
Jordanian
Jordanian
Room
205
151
325
410
210
305
412
203
324
308
Bill
1500
450
250
150
375
1100
650
700
AdmitDate
2/5/2019
10/10/2015
9/15/2010
4/20/2017
1/25/2016
3/25/2014
9/10/2015
6/15/2018
8/15/2011
7/15/2013
DNo
91
91
92
92
93
93
93
94
94
94
Page 3 of 9
Read the following scenario and answer as following instructions
[40 Marks]
1. Mr. Anwar is a Database Administrator (DBA) in a Private Hospital. He wants to propose
an effective medical and health system at his hospital. This system will contain doctors
with their specializations details. At the same time, it would contain patients’ details
along with their admission bills. To get an idea of all these details, he took a random
sample of records from the three (3) available tables: Doctors, Specializations and
Patients. The samples that he has taken are not enough to complete his data analysis;
therefore, he needed to retrieve some of the saved data using the following
instructions:
[4 Marks]
i. First he needs to modify the Patients and Specializations tables by adding the following
constraints:
Table Name
Column Name
Constraint
a.
PCode
Should not be repeated and not empty
b. Patients
Bill
Should not exceed 600
c.
DNo
Foreign Key by maintaining the Referential Integrity
d. Specializations
SP_Title
Should not be repeated
ii. Then, he needs to manage the constraints of the Specializations table as following:
Table Name
e. Specializations
Column Name
SP_Title
Constraint
Remove the constraint
2. The DBA has been responsible of many users (Doctors) who require the same objects
privileges. Therefore, he assumed that Doctor 1 is ‘D1_01_AITDB3208’ schema and
Doctor 2 is ‘D2_15_AITD3208’ schema which might exchange the objects privileges of
Specializations table between each other.
The following diagram will assist the DBA to distribute the objects privileges between
the users (Doctors) which needs to be completed. Therefore, write the statements by
using the instructions which are showing inside the diagram:
[3 Marks]
Page 4 of 9
3. Simultaneously, the DBA has to manage the Transaction Control Language that used to
manage transactions in the database. These are used to manage the changes made on
tables by using several DML statements.
[4 Marks]
i. Create table named as Sports_StudentID e.g. Sports_16J123 as showing below:
SNumber
SName
Coach
Players
211
Football
Oliver
25
212
Basketball
Khalid
20
222
Tennis
James
15
Page 5 of 9
ii. Write the DML statements of each transactions as showing in the diagrams.
iii. Help the DBA to track what data will be saved after the following transactions are issued by displaying the table consistent state 2 output.
Page 6 of 9
4. One of the main responsibilities of the DBA is to manage the database object security,
thus by using the Patients table, he needs to create a view that can be modified anytime
and name it as “PAT_BILL_AVERAGE” to display the patients name, nationality and
room and an inline view in the FROM clause to return the doctor number and the latest
admission date in column “Latest_Admitdate” for each doctor number whose lowest
bill is less than four hundred. Display also the Latest_Admitdate in the main query and
use doctor number for the equi-join. Add the constraint to ensure that DML operations
performed on the view stay within the domain of the view and not affecting the base
table.
[5 Marks]
5. Considering the database schema relationship as displayed below, assume that you are
appointed as a database designer to support the Database team and required you to
create a query which display patient name and patient bill from Patients table and
doctor number and doctor name from Doctors table. Moreover, use the conditional
expressions to display a new column called “Admin Bill” by using the following
instructions:
[6 Marks]
Page 7 of 9
i. The patient bill is based on the following:
If the bill is less than 300, display Very Low
If the bill is greater than or equal to 350 and less than or equal to 600, display Moderate
If the bill is greater than 700, display Very High
Otherwise, display Considerable
ii. Include all patients even they are not assigned to any doctors.
iii. Restrict the output for all patients’ rooms except the range of 151 to 305.
iv. Make sure that you are arranging the output in alphabetical order of Patient Name.
6. In order to overcome the relational database issues, the DBA gain the ability to combine
multiple tables of data in one relationship. In that case, the Private Hospital stores these
campaign data on three several tables as showing in the following relationship:
[5 Marks]
Now as you have been appointed as a database designer to support the Database team,
then you need to generate a proper query according to the below instructions:
i. Find the first three (3) letters of doctor name.
ii. Find the remainder of bill divided by room in a column called “Remainder”.
iii. Display the admission date of the patients truncated by year from the selected tables.
iv. Use the relationship between the tables as common columns.
v. Display only those patients who is having the number of characters in their nationality
lower than or equal to six (6).
vi. Arrange the patients name on top of the alphabetical list and reverse the sort of the
remainder column.
Page 8 of 9
7. It’s the end of 2019 year and the owners of Private hospital wants to write an SQL query
that finds the minimum bill of the patients for each doctor within each nationality
whose minimum bill is greater than or equal to 450.
Using the WITH clause named as MIN_PAT_BILL, write a query that will help the owners
to find all patients whose bill is having a value and to restrict the output for the patient
code, which is greater than to any patient code whose room is greater than or equal to
325 and doctor number is the same as to any of the doctor number whose admission
date is greater than 01/01/2015.
[7 Marks]
8. At the end, the Private hospital would like to get the annual report of the total patient’s
bill, which is if the total of bill is greater than 1400, then it should display ‘Yes’.
Otherwise, display ‘No’. The annual report should be restricted in patient admitting date
which is less than any admitting date in room more than 205, but only for those patients
which have more than one bill?
[6 Marks]
*** End of Final Assignment ***
Note: After saving your Answer Script File, close it, and UPLOAD your file in the link provided in the
E-Learning portal. Only uploaded files will be marked accordingly.
Page 9 of 9
Purchase answer to see full
attachment