ITDB 3208 Higher College of Technology SQL Concepts & Syntax Questions

User Generated

Nye2fuqv

Programming

ITDB 3208

Higher College of Technology

ITDB

Description

see file

..............................................................................................

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

At...


Anonymous
Just the thing I needed, saved me a lot of time.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags