The following tables (Table 2 and Table 3) with the following values are stored in Oracle database. PROD CODE PROD_QOH 205 PART CODE PART_QOH АВС A 567 DEF 234 98 Table 2: PRODUCT 549 Table 3: PART (a) The following transaction in Figure 2 is executed: Transaction 1 UPDATE PRODUCT SET PROD_QOH = PROD_OQH+1 WHERE PROD_CODE='ABC'; UPDATE PART SET PART_QOH = PART_OQH - 1 WHERE PART_CODE= 'A'; UPDATE PART SET PART_QOH = PART_OQH - 1 WHERE PART_CODE= 'B'; UPDATE PART SET PART_QOH = PART_OQH - 1 WHERE PART_CODE= 'C'; Figure 2: A Transaction in Oracle Database (i) Assume transaction logs are recorded when Transaction 1 is executed. State the steps for the database recovery according to the logs if a ROLLBACK command was issued at the end of the transaction. State and explain ONE property of transactions that is observed with the rollback action in (i) of this question. (ii)

The following tables (Table 2 and Table 3) with the following values are stored in Oracle database. PROD CODE PROD_QOH 205 PART CODE PART_QOH АВС A 567 DEF 234 98 Table 2: PRODUCT 549 Table 3: PART (a) The following transaction in Figure 2 is executed: Transaction 1 UPDATE PRODUCT SET PROD_QOH = PROD_OQH+1 WHERE PROD_CODE='ABC'; UPDATE PART SET PART_QOH = PART_OQH - 1 WHERE PART_CODE= 'A'; UPDATE PART SET PART_QOH = PART_OQH - 1 WHERE PART_CODE= 'B'; UPDATE PART SET PART_QOH = PART_OQH - 1 WHERE PART_CODE= 'C'; Figure 2: A Transaction in Oracle Database (i) Assume transaction logs are recorded when Transaction 1 is executed. State the steps for the database recovery according to the logs if a ROLLBACK command was issued at the end of the transaction. State and explain ONE property of transactions that is observed with the rollback action in (i) of this question. (ii)

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

In Oracle database, if we have any field such as Job_Title field in any of our table then we can use DECODE function to sort the result based on our own numerical value. This can be done by including the following in ORDER BY clause: 1: ORDER BY DECODE(job-title,'Manager',3,Assistant',2,'Sales Rep', 1) DESC II: ORDER BY DECODE(job-title,'Manager', 3,'Assistant',2, Sales Rep', 1) ASC III I and II are correct IV I and II are incorrect ||| IV || 1
All parts of this question use the following database schema: Flights(flno, from, to, distance) Aircraft(aid, aname, cruisingrange) Certified(eid, aid) Employees(eid, ename, salary) c) For each cruisingrange held by at least 3 aircraft, give the lowest salary of employees certified to fly one of those aircraft. Express in SQL only:
4. In the OrderItems table, display a count of the number of products, grouped by prod_id, but only for those groups having 3 or more of a given prod_id. Display the count with the alias shown. Sort by prod_id in ascending order. BNBG01 BNBG02 BNBG03 BR03 4 rows returned in 0.00 seconds PROD_ID Download 3 3 3 4 NUM_PROD_ID
Computer Science provide SQL code (Use the EBOOKS database) When a course is offered in several sections taught by more than one instructor, students have the ability to choose an instructor, otherwise they have no choice. The deans of each college would like to know how many courses have only one section in the schedule, how many courses have more than 1 section, but taught by the same instructor, how many courses have sections taught by more than one instructor. Sort the results alphabetically by College ID as shown below: COLLEGE SINGLE_SECTION MULTISECTION_ONE_INSTRUCTOR MULTIPLE_INSTRUCTORS CAS 83 21 22 CED 76 8 39 COB 72 16 13
write SQL queries to create procedures for a database called Saleco. The database contains the following tables: customer, invoice, line, product, and vendor. A procedure called get_customer_invoices that takes a customer ID as input and returns a list of all invoices for that customer, along with the total amount due for each invoice. A procedure called get_vendor_products that takes a vendor ID as input and returns a list of all products sold by that vendor, along with the number of units sold and the total revenue generated for each product. A procedure called get_invoice_details that takes an invoice ID as input and returns a list of all products purchased on that invoice, along with the quantity purchased, the unit price, and the total price for each product.
For table EP, the attribute is a foreign key referencing target table For table EP, the attribute is a foreign key referencing target table For table ER the attribute is a foreign key referencing target table For table ER the attribute is a foreign key referencing target table
Write a SQL statement to create a stored procedure called Calc_Average based on the Semester_Marks table created in Table 2: Semester_Marks table structure. The stored procedure should update the total marks, no of courses and calculate the average mark for each student. (Note: Average mark = total_mark divide by the number of courses for the student.). Control the flow of your code should an error occur (e.g. number of courses enter is zero). Use exception handling to avoid zero being entered for the number of courses. Table 2: Semester_Marks table structure Semester_Marks Column Name Datatype Allow Nulls PK Stud_Reg_No int First_Name char(20) Last_Name char(20) Total_marks int No_Courses int Student_Average_Markint
Find the Primary Keys and P3,6 for the database: Make Model Engine ID Ford Mustang Ford Fiesta Chevy Camaro Chevy Caprice Olds Cutlass Olds Cutlass Volvo P1800 Volvo 240 Volvo 760 Year 1972 A1222 1989 C54322 1991 754342AH 1989 442355CC 1992 ANDU33 1992 ANGH28 1969 44325XX 1986 53526PD 1992 578868R Vehicle ID FO13579 FO24245 CH172389 CH156738 OL64332 OL61998 V044526 VO64690 V083529 Color Black Yellow Black Yellow Blue White White Black Blue
Perform the following operation in sql for insertion, updating and deletion using the referential integrity constraints