Murach's MySQL 3. Write a script that creates and calls a stored procedure named test. This procedure should use a transaction that includes the statements necessary to combine two musicians. These statements should do the following: Select a row from the Musicians table for the musician with a musician_id value of 6. This statement should lock the row so other transactions can’t read or modify it until the transaction commits, and it should fail immediately if the row is locked from another session. Update the Orders table so any orders for the selected musician are assigned to the musician with a musician_id value of 3. Update the Addresses table so any addresses for the selected musician are assigned to the musician with a musician_id value of 3. Delete the selected musician from the Musicians table. If these statements execute successfully, commit the changes. Otherwise, rollback the changes

Murach's MySQL 3. Write a script that creates and calls a stored procedure named test. This procedure should use a transaction that includes the statements necessary to combine two musicians. These statements should do the following: Select a row from the Musicians table for the musician with a musician_id value of 6. This statement should lock the row so other transactions can’t read or modify it until the transaction commits, and it should fail immediately if the row is locked from another session. Update the Orders table so any orders for the selected musician are assigned to the musician with a musician_id value of 3. Update the Addresses table so any addresses for the selected musician are assigned to the musician with a musician_id value of 3. Delete the selected musician from the Musicians table. If these statements execute successfully, commit the changes. Otherwise, rollback the changes

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter6: Additional Database Objects

Section: Chapter Questions

Problem 8MC

See similar textbooks

Similar questions

Each Semuarasa restaurant branch has a mainstay menu that is very popular with customers, so information on the availability of the number of servings is needed to be able to serve orders. Here are two transactions that are ordering a food menu at the same time. T1: Start the transaction at time t1, read 10 servings of food stock at t2. Because ordering one portion, the stock is reduced by 1 at t3. On t6, update the stock count, and commit to t7. T2: Transaction T2 starts a transaction on t2, reads the number of stocks in t3 as many as 10. At t4 orders 3 portions. On t5 it updates the stock to 7 and then on t6 commits it. As a result of the T1 and T2 transactions above, there was an incorrect amount of food stock. Make a solution with 2PL. What kinds of problems happened? Give a reason why choosing this type of problem! How much food is still available?
Database Systems Each branch of the Jaja restaurant has a mainstay menu that is very popular with customers, so information on the availability of the number of servings is needed to be able to serve orders. Here are two transactions that are ordering a food menu at the same time. T1: Start the transaction at time t1, read 10 servings of food stock at t2. Because ordering one portion, the stock is reduced by 1 at t3. On t6, update the stock count, and commit to t7. T2: Transaction T2 starts a transaction on t2, reads the number of stocks in t3 as many as 10. At t4 orders 3 portions. On t5 it updates the stock to 7 and then on t6 commits it. As a result of the T1 and T2 transactions above, there was an error in the amount of food stocks. Make a solution with 2PL. What kinds of problems happened? Give a reason why choosing this type of problem! How much food is still available?
There are three tables in this database (see image). Write MYSQL code for: (1) Create a trigger “insert_inventory” on table “Inventory”. The trigger is fired after a row is inserted in table “Inventory”. After a row is inserted in table “inventory”, the “itemid”, the insertion time, and the action is inserted in table “Inventory_history”. The action is set to ‘add an item’. The oldprice is set to null. Test your trigger by inserting a row into Inventory and displaying the contents of Inventory_history. (2) Create a trigger “change_quantity” on table “Transaction”. The trigger is fired after a row is inserted in table “Transaction”. After a row is inserted in table “Transaction”, update the “quantity” in table “Inventory”. For example, if 3 iWatch are sold, then the quantity of iWatch in table “Inventory” is decreased by 3. Test your trigger by inserting a row into Transaction and displaying the contents of the relevant row in Inventory. (3) Create a trigger “change_price” on table…
48. Which of the following is not a property of a transaction? a. Atomicity b. Simplicity c. Isolation d. Durability
I got a question on this problem. It says: Create a transaction inside a stored procedure to record a traffic stop – that includes code similar to what you wrote for #13. Pass the details about the traffic stop to the procedure through its parameters. Then call the procedure with a statement similar to this:CALL traffic_stop_transaction (“Jane”, “Jones”, “Waller”, “TX”, “VNR 971”, 1039, “2014-12-25”,”Speeding”) How do I create a transaction?
Use AverageIF to calculate the average dollar amount of the paid in full transactions in J4.
The recurring transaction type that generates an alert when we should use a recurring transaction to enter a new transaction is
The follow questions refer to the transaction schedule below, that records bookings of tickets for a harbour cruise. TIME ID TRANSACTION ID OPERATION 1 A Read Tickets left = 10 2 B Read Tickets left = 10 3 B Purchase 8 Tickets 4 A Purchase 6 Tickets 5 6 Using the transaction schedule above, explain the lost update problem Using the transation schedule above, explain how the two-phase locking protocol would prevent the lost update from occurring Using the transaction schedule above, explain the concept of Isolation as it relates to transaction management
When a transaction is rolled back in timestamp order, it gets a new timestamp. Why can't it just keep its old time stamp?
co5ds13h.Ch10.02 or the following two transactions and the initial table values as shown complete the missing blanks in the transaction log below: Part_ID Desrption OnHand OnOrder 57 Assembled Foo 5 0 987 Foo Fastener 12 7 989 Foo Half 7 0 BEGIN TRANSACTION; UPDATE Part SET OnHand = OnHand + 7, OnOrder = OnOrder – 7 WHERE Part_ID = 987; COMMIT; BEGIN TRANSACTION; UPDATE Part SET OnHand = OnHand - 4 WHERE Part_ID = 987; UPDATE Part SET OnHand = OnHand - 2 WHERE Part_ID = 989; UPDATE Part SET OnHand = OnHand + 1 WHERE Part_ID = 57; COMMIT TRL_ID TRX_ID PREV_PTR NEXT_PTR OPERATION TABLE ROW ID ATTRIBUTE BEFORE VALUE AFTER VALUE 1787 109 NULL START **** 1788 109 1787 UPDATE PART 987 OnHand 12 1789 109 UPDATE PART 987 OnOrder 7 1790 109 NULL COMMIT **** 1791 110 NULL START **** 1792 110…
Assignment 4: Question#1: Give any three examples of transaction where next operations are dependent upon previous and focus on importance of transaction. Question#2: Execute the given transaction left to right and fill “local variable” and “Database variable” columns. Initial Database Values: x=30, y=50,z=80
This is the sort of recurring transaction that sends us an alert whenever we need to input a new one: