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)

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter6: Additional Database Objects

Section: Chapter Questions

Problem 1HOA

See similar textbooks

Similar questions

Q2: A-Given the following tables: Student Subject Std_ID 001 002 003 004 Fname Smith Smith Beal Thomson Lname Jolin Susan Fred Marie Std_ID 001 001 002 002 003 Semester 004 Falls03 Spro4 Falls03 C_ID Eng01 Ger01 Eng01 Ger01 Ger01 CSection Spro4 Spro4 Spro4 1. For each table in the database, identify the primary key and the foreign key(s). 2. Do the tables exhibit entity integrity? Explain your answer? 3. Do the tables exhibit referential integrity? Explain your answer? Ita01 ZZZZZZ IN 2N IN 2N IN CName English IN German English German German Italian Grade A B A B A B
In Sql These are some database from a set of tables insert into AccountHolder(AccountID, CustomerID, AccountType)values (55003, 115, 'Savings'),(44001, 101, 'Chequing'),(55006, 109, 'Savings'),(44002, 102, 'Chequing'),(44003, 113, 'Chequing'),(55007, 108, 'Savings'),(44004, 103, 'Chequing'),(44005, 110, 'Chequing'),(55005, 112, 'Savings'),(44006, 114, 'Chequing'),(55001, 111, 'Savings'),(44007, 107, 'Chequing'),(44008, 104, 'Chequing'),(55004, 106, 'Savings'),(44009, 105, 'Chequing'),(44010, 113, 'Chequing'),(55002, 107, 'Savings'),(44011, 110, 'Chequing'),(44012, 104, 'Chequing') select * from AccountHolder insert into Branch(LocationID, BranchName, Address, Type, DepositSum, LoanSum)values (1, 'Boulevard Branch', '3268 Wade Boulevard', 'Rural', 171848.88, 37681.72),(2, 'Avenue Branch', '4814 Dare Avenue', 'Rural', 232707.42, 68324.56),(3, 'Street Branch', '208 Ferry Street', 'Commercial', 375010.75, 44665.08),(4, 'Road Branch', '468 Zulauf Road', 'Commercial', 496001.41,…
Considering Vaccination Database, write queries to: Display the Subject CNIC, Name, Contact, Dose1 Center, and Dose2 Center as shown below: CNIC Name Contact Dose1 Center Dose2 Center 22401-6645321-1 Nasir 3409991112 Lachi Kohat 2 14301-6045321-5 Shahab 3409991112 kohat 3 Dara The subjects who have been vaccinated different types in Dose1 and Dose2. For instance, subjects who were vaccinated ‘Sinovac’ in the first dose, while Pfizer in the second dose.How many Viles of each type have been consumed so far. How many subjects are vaccinated from expired viles. Suggest the name of Incharge for the best performance award based on the highest number of subjects vaccinated on a single day
Write Mysql code for: The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results. Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.
Using the WideWorldDistributors database and the Sales schema create a function that takes InvoiceID as an input parameter and returns all the rows from the table InvoiceLines for the InvoiceID value passed into the function. If the result is a NULL return a zero
Assume you have StudentsDB database stored in MYSQL server that contains the following 2 tables as follows: Table 1: Students Table 2: Grades int, Primary Key, Auto Increment Sid Gid int, Primary Key, Auto Increment UniversityID varchar(8), Unique Sid int, Foreign Key Name varchar(50) CourseCode varchar(10) Мajor varchar(50) Credits int CourseGrade varchar(4) *** Note: Students must use PDO to access MYSQL DB, MYSQLI isn't allowed *** Implement the following PHP scripts as required below: 1. displayStudentGrades.php: the script should display student's name and major. In addition, all courses codes with their grades should be displayed in HTML table followed by its Cumulative GPA, Total Credits Passed and Total Credits Registered at the bottom of the TABLE. Highlight the course row by red if it's F grade. The studentlD is received using HTTP GET method from an HTML form. 2. addnewStudentwithgrades.php: the script should obtain all the required student details using HTML form including…
In OES4 database, we can verify the values stored in subtotal field by using the following 1: col gross_sales for 99999999 col subtotal for 99999999 select o.order_no,sum(qty*Unit_cost) Gross_Sales, sum(o.subtotal) subtotal from orders o, orderline ol, product p where o.order_no=ol.order_no and ol.product_no=p.product_no group by o.order_no; II: col gross_sales for 99999999 col subtotal for 99999999 select o.order_no,sum(qty*Unit_cost) Gross_Sales, sum(o.subtotal) subtotal from orders o, orderline ol, product p where o.order_no=ol.order_no and ol.product_no=p.product_no group by o.order_no; I & II: are correct I & II: are not correct | || I & II: are not correct I & II: are correct
Write a simple PL/SQL function which has the following definition. Refer to the "EMP" below for appropriate column names CREATE TABLE emp ( empno number(4) CONSTRAINT pk_emp PRIMARY KEY, ename varchar2(10) NOT NULL, job varchar2(10), mgr number(4) referneces emp ON DELETE CASCADE, hiredate date, sal number(6), comm number(6), deptno number(3), CONSTRAINT fk_emp_deptno FOREIGN KEY (deptno) REFERENCE dept ON DELETE CASCADE) ------------------------------------------------------------------- Function name: Count_Emp Purpose: To count the number of managers from the EMP table Input parameter: employee job type ( is the employee an emp or mgr) Output parameter: None Input/Output parameters: None Return Value: an integer value representing the total number of managers. Precondition: None Postcondition: the employee table is unchanged. The output of the function is the number of managers in the emp table Use % TYPE where possible.
USE this part to write SQL statements Branch(branch_id:integer, branch_name:varchar(50), branch_location:varchar(40), money_on_hand:numeric(15,2) create table Branch (branch_id integer, branch_name varchar(50), branch_location varchar(40), money_on_hand numeric(15,2), primary key (branch_id)); Loan(loan_number:integer, branch_id:integer, amount:numeric(8,2)) foreign key branch_id references Branch(branch_id) create table Loan (loan_number integer, branch_id integer, amount numeric(8,2), primary key (loan_number), foreign key (branch_id) references Branch (branch_id)); Customer(customer_id:integer, customer_last_name:varchar(35),customer_first_name:varchar(25), customer_street:varchar(30), customer_zip:integer) create table…
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?
Write a PL/SQL block that uses a searched case statement to classify students based on their scores in a certain test according to the following table. Score Action 90 80-89 A certificate is given 70-79 A training course is recommended <70 A scholarship is given The course should be repeatcd NB: Write the searched CASE structure only. Use the editor to format your answer
Problem: JMS TechWizards is a local company that provides technical services to several small businesses in the area. The company currently keeps its technicians and clients’ records on papers. The manager requests you to create a database to store the technician and clients’ information. The following table contains the clients’ information. Client Number Client Name Street City State Postal Code Telephone Number Billed Paid Technician Number AM53 Ashton-Mills 216 Rivard Anderson TX 78077 512-555-4070 $315.50 $255.00 22 AR76 The Artshop 722 Fisher Liberty Corner TX 78080 254-555-0200 $535.00 $565.00 23 BE29 Bert's Supply 5752 Maumee Liberty Corner TX 78080 254-555-2024 $229.50 $0.00 23 DE76 D & E Grocery 464 Linnell Anderson TX 78077 512-555-6050 $485.70 $400.00 29 GR56 Grant Cleaners 737 Allard Kingston TX 78084 512-555-1231 $215.00 $225.00 22…