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

Related questions

Q: Display the list of students who are involved in the IT club Display the details of all the…

A: First get the studid from Club table and then get those Student details This can be done using inner…

Q: Syntax for drop table cascade constraints drop table with restrict syntax for On delete/update…

A: A SQL query can be defined in such a way that it is a command that is basically used to interact…

Q: Normalize the following unnormalized table using the three normalization rules and describe each…

A: Dear Student, The three normalization rules are nothing but the normal form conversion of the table,…

Q: Given the default schema of a database: Orders 123 Orderld OrderDate ADC OrderNumber 123 Customerld…

A: Please refer to the following step for the complete solution to problem above.

Q: Create a table based on EMP and DEPT tables such that it list each employee’s name, job, hire date,…

A: Given two tables: emp- empno, ename, job, mgr, hiredate, sal, comm, deptno dept- deptno, dname,…

Q: access databases Normalization Exercise Represent this table as a collection of tables in 3NF:…

A: The key for the given relation is (StudentNumber, CourseNumber) The partial functional dependency…

Q: One of the TAL Distributors database's denormalized tables is as follows: The Customer table is no…

A: The answer is given below step.

Q: Given two database table i_users(userid,username,email address,isActive,gender) stores the users…

A: Structured Query Language is abbreviated as SQL or also pronounced as See-Qwell. It is developed for…

Q: the HR department needs the department name, manager first name, manager last name, and job title.…

A: SQL: SQL stands for Structuref Query Language. It is used to access and manipulate the database…

Q: Write stored procedure in SQL Find all employees whose salary is larger than the salary of at least…

A: Answer: I have done code and also I have attached code as well as code screenshot.

Q: Write a simple PL/SQL function which has the following definition. Refer to the "EMP" below for…

A: PL/SQL which refers to the one it is the purpose of PL/SQL in order to combine database language…

Q: how would I create a query that lists ORDNO and MONTH for all orders in the ORDER table. Then how…

A: ORDER BY is used to sort the results and we can specify the condition with which column reference we…

Q: insert the tasks in a batch into the database. In other words, they want to insert the rows all…

A: According to given answer , value will inserted one by one. So below i write code to insert two rows…

Q: Write SQL code for the following design schema of three tables. The tables should have the following…

A: - We need to design three tables with the requirements of constraints as provided.

Q: Consider the following table in a relational database. Last Name Rank Room Shift Smith Manager 234…

A: A candidate key is an attribute or set of attribute which can identify all tuple uniquely.

Q: How would I create a Update statement in SQL Lite for the following question: The student ‘100004’…

A: UPDATE Tablename SET PhoneNumber = 14896572315 , PhoneType= 'alternate ' WHERE StudentID= 100003;

Q: What does this statement return? C SELECT TOP 1 ProductName FROM Products ORDER BY UnitPrice DECS

A: Here SQL query is given, what output it will produce based on database schema, that is given in…

Q: Convert the following record to a normalized data structure up to 3NF showing all attributes…

A: The ask is to normalize the data structure to 3NF for the following data: Grade-Report Number…

Q: PLZ help with the following: Using Oracle SQL Translate your ER diagram into a relational schema…

A: E-R diagram is given where different entities and attributes are given from this we will create SQL…

Concept explainers

Fundamentals of Datawarehouse

A data warehouse is a data repository which is used to store large quantities of historical data that are mostly used for creating reports that help businesses to identify their weaknesses and strengths. For example, demographics data about people of a region can help businesses identify the needs and preferences of the region’s population. A data warehouse acts as a central storage for storing data that comes from one or more heterogenous sources. It also consists of data which can be old and of historical value. One of the most important applications of this type of system is that it is used for supporting business intelligence and data analytics applications.

Question

The following tables (Table 2 and Table 3) with the following values are stored in Oracle
database.
PROD CODE
PROD_QOH
PART CODE
PART_QOH
АВС
205
A
567
DEF
234
98
Table 2: PRODUCT
C
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.
(ii)
State and explain ONE property of transactions that is observed with the rollback
action in (i) of this question.

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

||| > 4G Students table Write a SQL statement to create the following tow tables: 23 STD_ID STD_NAME COURSE_NO LEV 21 15 33 12 11 Bandar Khalid Naif 102 Ims.kku.edu.sa Yasser Ziyad Courses table 101 231 271 Assignments 231 102 271 101 Mohammad 101 4 Data Science 4 3 6 COURSE NO COURSE_NAME COURSE_TEAC Programming Abdulaziz Computing Abdulaziz Database Mohammad 5 4 ۱۰:۲۲ - Query1: Display all Student names who already registered in a course. - Query2: Display students names and course names from both students and courses tables for only students whose level is higher than 4. Ĵ
The following database will be used in this question: SALESREP SalesRepNo 654 734 345 434 RepName Jones Smith Chen Johnson HireDate 01/02/2005 02/03/2007 01/25/2004 11/23/2004 CUSTOMER CustNo 9870 8590 7840 4870 CustName Winston Gonzales Harris Miles Balance 500 350 SalesRepNo 345 434 654 345 800 100
write SQL statement to search for employees who is the manager of the department specified by the cboDept
List all products with their highest unit price, i.e. not discounted.the database is
Write the appropriate SQL DDL statements to declare the relational libraryDatabase. Diagram of Figure 4.6. Select the keys and reference actions that are triggered.
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
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
Use your ERD to define the table structures in the database using SQL DDL commands to build your schema.
My other instruction for SQL says: Create and test a stored procedure called checkin_transaction that processes a “checkin” transaction. When a book is checked in, the corresponding CHECKOUT record is updated to show the date it was checked in, and the Pat_ID attribute in the BOOK table is updated to NULL. The entire transaction (starting with START TRANSACTION; and ending with COMMIT;) should be in between the BEGIN and END statements of the stored procedure. You should be able to call the procedure with a statement like this, passing the values of the Book_Num, Pat_ID, and Check_In_Date to the procedure: CALL checkin_transaction(5243, 1170, ‘2017-04-30’) Do you guys know how?