/* DROP TABLE STATEMENTS */ DROP TABLE timetabled_slot CASCADE CONSTRAINTS; DROP TABLE specialisation CASCADE CONSTRAINTS; DROP TABLE subject CASCADE CONSTRAINTS; DROP TABLE room CASCADE CONSTRAINTS;...

I need to do this in oracle 11g version


/* DROP TABLE STATEMENTS */ DROP TABLE timetabled_slot CASCADE CONSTRAINTS; DROP TABLE specialisation CASCADE CONSTRAINTS; DROP TABLE subject CASCADE CONSTRAINTS; DROP TABLE room CASCADE CONSTRAINTS; DROP TABLE building CASCADE CONSTRAINTS; DROP TABLE teacher CASCADE CONSTRAINTS; /* CREATE TABLE STATEMENTS */ CREATE TABLE Building ( B_No CHAR (1), B_Name VARCHAR2 (15), CONSTRAINT B_No_PK PRIMARY KEY (B_No)); CREATE TABLE Room ( RoomNo CHAR (2), B_No CHAR (1), Facilities VARCHAR2 (40), CONSTRAINT B_No_FK FOREIGN KEY (B_No)REFERENCES Building (B_No), CONSTRAINT RoomNo_PK PRIMARY KEY (B_No, RoomNo) ); CREATE TABLE Teacher ( TeacherID CHAR(4) NOT NULL, Fname VARCHAR2 (40), Lname VARCHAR2 (40), Address VARCHAR2 (50), RoomNo CHAR(2), B_No CHAR(1), Salary Number (6), CONSTRAINT TeacherID_PK PRIMARY KEY (TeacherID), CONSTRAINT Room_FK FOREIGN KEY (RoomNo, B_No) REFERENCES Room(RoomNo, B_NO) ); CREATE TABLE subject ( SubjID NUmber(2,0), SubjName VARCHAR2(20), HeadOfSubject CHAR(4), CONSTRAINT SubjID_PK PRIMARY KEY (SubjID), CONSTRAINT HeadOfSubj_FK FOREIGN KEY (HeadOfSUbject) REFERENCES Teacher(TeacherID) ); CREATE TABLE specialisation ( SpecID CHAR (2), SpecLevel VARCHAR2 (2), TeacherID CHAR(4), SubjID Number(2,0), CONSTRAINT specID_PK PRIMARY KEY (SpecID), CONSTRAINT subjectID_FK FOREIGN KEY (SubjID) REFERENCES Subject (SubjID), CONSTRAINT TeacherID_FK2 FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID) ); CREATE TABLE timetabled_slot ( ID number(10,0), slotID CHAR (5), slotDate DATE, slotStartTime NUMBER(2), SubjID number(2,0), RoomNo CHAR (2), B_No CHAR (1), TeacherID CHAR(4), CONSTRAINT slotID_PK PRIMARY KEY (ID), CONSTRAINT SubjID_fk FOREIGN KEY (SubjID) REFERENCES subject (SubjID), CONSTRAINT roomNo_fk2 FOREIGN KEY (RoomNo,B_No)REFERENCES room (RoomNo,B_No), CONSTRAINT TeacherID_fk3 FOREIGN KEY (TeacherID)REFERENCES Teacher(TeacherID) ); /* INSERT DATA STATEMENTS */ INSERT INTO building VALUES ('M', 'Main'); INSERT INTO building VALUES ('L', 'Laboratories'); INSERT INTO building VALUES ('H', 'Hall'); INSERT INTO room VALUES ('23', 'M', 'Seats 30 with fixed tables'); INSERT INTO room VALUES ('1', 'H', 'Sports Hall'); INSERT INTO room VALUES ('6', 'L', 'Science benches for 30 students'); INSERT INTO room VALUES ('27', 'M', '2 person office'); INSERT INTO room VALUES ('28', 'M', '1 person office'); INSERT INTO room VALUES ('29', 'M', '3 person office'); INSERT INTO teacher VALUES ('0016', 'Tom', 'Wright', '6 North St','27','M',58000); INSERT INTO teacher VALUES ('0029', 'Dick', 'Jones', '7 South St','27','M',45000); INSERT INTO teacher VALUES ('0063', 'Harriet', 'Drury', '8 West St','28','M',79000); INSERT INTO teacher VALUES ('0027', 'Ron', 'Simpson', '9 Worthing St','29','M',75000); INSERT INTO teacher VALUES ('0046', 'Karen', 'Woods', '9 Worthing St','29','M',63000); INSERT INTO subject VALUES (12, 'Physics', '0016'); INSERT INTO subject VALUES (13, 'Chemistry', '0016'); INSERT INTO subject VALUES (14, 'Biology', '0016'); INSERT INTO subject VALUES (15, 'French', '0063'); INSERT INTO subject VALUES (16, 'Geography','0029'); INSERT INTO subject VALUES (17, 'Maths','0027'); INSERT INTO subject VALUES (18, 'English','0046'); INSERT INTO specialisation VALUES ('01', '7', '0016', 12); INSERT INTO specialisation VALUES ('02', '8', '0029', 17); INSERT INTO specialisation VALUES ('03', '7', '0016', 13); INSERT INTO specialisation VALUES ('04', '9', '0016', 14); INSERT INTO specialisation VALUES ('05', '9', '0029', 17); INSERT INTO specialisation VALUES ('06', '7', '0016', 14); INSERT INTO timetabled_slot VALUES (1,'1', TO_DATE ('04/04/2020', 'dd/mm/yyyy'), 8.30, 12, '23', 'M','0016'); INSERT INTO timetabled_slot VALUES (2,'2', TO_DATE ('04/04/2020', 'dd/mm/yyyy'),9.30, 14, '6', 'L','0029'); INSERT INTO timetabled_slot VALUES (3,'3', TO_DATE ('04/04/2020', 'dd/mm/yyyy'),11.00, 17, '23', 'M','0016'); INSERT INTO timetabled_slot VALUES (4,'1', TO_DATE ('05/04/2020', 'dd/mm/yyyy'), 9.30, 12, '23', 'M','0016'); INSERT INTO timetabled_slot VALUES (5,'2', TO_DATE ('05/04/2020', 'dd/mm/yyyy'),11, 14, '6', 'L','0027'); INSERT INTO timetabled_slot VALUES (6,'3', TO_DATE ('05/04/2020', 'dd/mm/yyyy'),12, 17, '23', 'M','0016'); BIT358: Advance Database Assessment 3 – Database Application Semester 2, 2020 Due Date: Week 12 - 20/10/2020 Total Assignment Tasks: 2 Tasks (A and B) Assessment Weightage: 25% Total marks = 120 This assessment contains a HURDLE TASK – Question Answer session – this task that must be passed to pass the whole assessment. Assignment Instructions: 1. PLEASE DOWNLOAD THE A2SCHEMA DATABASE FROM MOODLE TO IMPORT DATABASE IN ORACLE OR SAS STUDIO. 2. HURDLE TASK – Question Answer session – the task that must be passed in order to pass the whole assessment. 3. Answer all questions. Answers to all questions must be supplied in electronic form 4. Submit the Word document TWICE on Moodle– Submit the document on Turnitin link and on assessment link 5. Add appropriate comments to explain your logic/code. All answers to the questions below for Part A and B must be fully documented i.e. they must contain comments explaining the main features of each program. 6. For B and C, a. Take screen shots, copy, and paste screen shots into MS word file as evidence successful operation of the procedure, function, trigger or SQL query. 7. For Part B, a. Use Oracle to answer the questions b. there should be a separate PL/SQL block as an individual piece of code on individual page (use page break). NO CODE NO MARKS/NO EVIDENCE(Screen shots) NO MARKS. You should create a table of contents with the name of each PL/SQL block and its purpose. All code should be uploaded to Moodle as one Word file. 8. For Part C, a. Use Oracle/SAS Studio to write SQL code, according to the instructions given in part C b. Screen shot of all SQL code with evidence should be in MS Word file. Also, upload a separate .sql or .txt file containing all code without errors. https://webems.rmit.edu.vn/exchweb/bin/redir.asp?URL=http://blackboard.rmit.edu.vn/webapps/portal/tab/_1_1/%2520/webapps/portal/frameset.jsp?tab_id=_2_1%26url=%252fwebapps%252fblackboard%252fexecute%252flauncher%253ftype%253dCourse%2526id%253d_922_1%2526url%253d BIT358 Advance Databases – Assessment 3 – Database Application Melbourne Polytechnic Page 2 of 8 9. All answers to the questions below for Part B must be fully documented i.e. they must contain comments explaining the main features of each program. 10. All work should be submitted to Moodle. Your submission to must contain Two(2) files MS Word document and SQL/txt file. Please submit the Word document TWICE on Moodle– Submit the document on Turnitin link and on assessment link Part A – PL/SQL – 6 questions – 90 marks Due Date: Week 12 (Use Oracle to answer the questions) 1. (15 marks) Write a procedure that is passed a teacher’s ID and returns the Name, Office Location and Specialisms. Also write an interactive anonymous block with the procedure call. The anonymous block must print all the teacher’s information. Test the program and print out your results to show that the program is working correctly. 2. (10 marks) Write a function which will determine whether a teacher exists. If the teacher table does not contain that teacher ID, return a FALSE value, otherwise return a TRUE value as Boolean. Print the appropriate message in the interactive calling program (anonymous block) based on the result. Test the program and print out your results through anonymous block. 3. (20 marks) Write a procedure that takes a teacher’s ID and dates details and outputs the information given in the following report (in text format on command prompt). Write an interactive anonymous block with the procedure call. Test the program (show that the program is working correctly). 4. (15 marks) Create a database trigger entitled TIMETABLE_SECURITY_TIME_CHECK_1234 (where 1234 is your student id). This trigger should fire before any UPDATE or INSERT statement occurs on the TIMETABLED_SLOT table. This trigger will check the date and time attributes and if the day is a BIT358 Advance Databases – Assessment 3 – Database Application Melbourne Polytechnic Page 3 of 8 Saturday or a Sunday or if the time is outside the school business hours i.e. between 8am and 3pm a message “Out of school hours – this transaction has not completed by 1234” (where 1234 is your student id) should be displayed. Test this trigger. Hint: Create a user defined exception to handle incorrect date/time entries. 5. (Self-Learning – 15 marks) Alter table Teacher and add an additional attribute to the Teacher table to store details of the teacher’s salary and run the following update commands. UPDATE teacher SET SALARY = 48500 WHERE TeacherID IN ('0029','0063','0027'); UPDATE teacher SET SALARY = 63500 WHERE TeacherID IN ('0016','0046'); Create a PL/SQL block to increase the salary of all teachers’s following an annual pay rise. The increase is 15% for those teachers whose salary is less than $50,000 and 10% for those who earn $50,000 or more. You may use a Cursor FOR loop in this problem. Test and show evidence of all features of your answer. Hints: Ensure your table contains sufficient and suitable data to demonstrate that this block works correctly. You will need to use the SELECT … FOR UPDATE command . Also you will need to use the WHERE CURRENT OF clause 6. (15 marks) Write a procedure that takes a date and outputs details of the sessions running on that day.The details include the name of teacher teaching, Room no, Subject being taught and session number as shown below in the following report format: Timetable for date: 04/04/2020 Session 1: TeacherName RoomNo SubjectTitle Session 2: TeacherName RoomNo SubjectTitle Session 3: Free Null Session 4: TeacherName RoomNo SubjectTitle Session 5: TeacherName RoomNo SubjectTitle Session 6: TeacherName RoomNo SubjectTitle Write an interactive anonymous block with the procedure call. Test your program works correctly. BIT358 Advance Databases – Assessment 3 – Database Application Melbourne Polytechnic Page 4 of 8 Part B SQL QUERIES – 6 Questions – 30 marks
Oct 24, 2021
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here