Modern Database Management
13th Edition
ISBN: 9780134773650
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 9, Problem 9.36PAE
Program Plan Intro
(a)
To construct:
Table for the given transactions assuming the data as transient.
Program Plan Intro
(b)
To construct:
The tables for the given transactions if the data is periodic.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
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…
The database contains the following data items and their values:
A = 1000B = 1500C = 3000
Log file is empty at first (contains no entries). x, y, z, t - transaction local variables (not stored in DB).
The following transaction execution schedule is given:
T0
T1
T2
START TRANSACTION
WRITE(2000, B)
Start TRANSACTION
READ (x, A)
x=x+n1n2n3n4n5
WRITE (x, A)
CHECKPOINT
START TRANSACTION
COMMIT TRANSACTION
READ (y, B)
y = y-n3n4n5
READ (z, C)
z=z+n1n2n3n4
WRITE (z, C)
WRITE (y, B)
CHECKPOINT
COMMIT TRANSACTION
Read (t, C)
SYSTEM FAIL
The activity logging is performed (in the database). Log file entries are not buffered and are saved to disk when each new log file entry is inserted (Immediate Database Modification scheme is used). No fuzzy checkpointing is used. Any of Early lock release and Logical Undo Loging are not used.
a) What will be the contents of…
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?
Chapter 9 Solutions
Modern Database Management
Ch. 9 - Prob. 9.1RQCh. 9 - Prob. 9.2RQCh. 9 - Prob. 9.3RQCh. 9 - List the five major trends that necessitate data...Ch. 9 - Prob. 9.5RQCh. 9 - Prob. 9.6RQCh. 9 - Prob. 9.7RQCh. 9 - Prob. 9.8RQCh. 9 - Prob. 9.9RQCh. 9 - Prob. 9.10RQ
Ch. 9 - List four objectives of derived data.Ch. 9 - Prob. 9.12RQCh. 9 - Prob. 9.13RQCh. 9 - Explain the pros and cons of logical data marts.Ch. 9 - Prob. 9.15RQCh. 9 - What is a helper table, and why is it often used...Ch. 9 - Describe the characteristics of a surrogate key as...Ch. 9 - Prob. 9.18RQCh. 9 - What is the purpose of conformed dimensions for...Ch. 9 - Prob. 9.20RQCh. 9 - Prob. 9.21RQCh. 9 - Prob. 9.22RQCh. 9 - Prob. 9.23RQCh. 9 - Prob. 9.24RQCh. 9 - Prob. 9.25RQCh. 9 - Describe the current key trends in data...Ch. 9 - Discuss the benefits of offering data warehousing...Ch. 9 - Prob. 9.28RQCh. 9 - List six typical characteristics of reconciled...Ch. 9 - Prob. 9.30RQCh. 9 - Prob. 9.31RQCh. 9 - Prob. 9.32RQCh. 9 - Prob. 9.33RQCh. 9 - Describe some field-level and record-level data...Ch. 9 - Prob. 9.35PAECh. 9 - Prob. 9.36PAECh. 9 - Prob. 9.37PAECh. 9 - Prob. 9.38PAECh. 9 - You are to construct a star schema for Simplified...Ch. 9 - Simplified Automobile Insurance Company would like...Ch. 9 - Prob. 9.41PAECh. 9 - Prob. 9.42PAECh. 9 - Prob. 9.43PAECh. 9 - Prob. 9.45PAECh. 9 - Prob. 9.46PAECh. 9 - Prob. 9.47PAECh. 9 - Prob. 9.48PAECh. 9 - Prob. 9.49PAECh. 9 - Prob. 9.50PAECh. 9 - Prob. 9.51PAECh. 9 - Prob. 9.52PAECh. 9 - Prob. 9.53PAECh. 9 - Prob. 9.54PAE
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
- The following tables provide a very small portion of the data that will be kept in the database. These data need to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that were created in Problem 65. RENTAL RENT_NUM RENT_DATE MEM_NUM 1001 01-MAR-13 103 1002 01-MAR-13 105 1003 02-MAR-13 102 1004 02-MAR-13 110 1005 02-MAR-13 111 1006 02-MAR-13 107 1007 02-MAR-13 104 1008 03-MAR-13 105 1009 03-MAR-13 111 VIDEO VID_NUM VID_INDATE MOVIE_NUM 54321 18-JUN-12 1234 54324 18-JUN-12 1234 54325 18-JUN-12 1234 34341 22-JAN-11 1235 34342 22-JAN-11 1235 34366 02-MAR-13 1236 34367 02-MAR-13 1236 34368 02-MAR-13 1236 34369 02-MAR-13 1236 44392 21-OCT-12 1237 44397 21-OCT-12 1237 59237 14-FEB-13 1237 61388 25-JAN-11 1239 61353 28-JAN-10 1245 61354 28-JAN-10 1245 61367 30-JUL-12 1246 61369 30-JUL-12 1246arrow_forward14. Rollback of transactions is normally used to : a. recover from transaction failure b. update the transaction c. retrieve old records d. repeat a transactionarrow_forwardFormulate the following queries in Structured Query Language SQL Produce a list of the names of all employees who work on the Biometric project. Produce a report of all projects under the IT department. The following tables form part of a database held in an RDBMS: Employee (empNo, fName, lName, address, DOB, sex, position, salary, deptNo) Department (deptNo, deptName, mgrEmpNo) Project (projNo, projName, deptNo) WorksOn (empNo, projNo, dateWorked, hoursWorked) where Employee contains employee details and empNo is the key. Department contains department details and deptNo is the key. mgrEmpNo identifies the employee who is the manager of the department. There is only one manager foreach department. Project contains details of the projects in each department and the key is projNo (notwo departments can run the same project). WorksOn contains details of the hours worked by employees on each project, and empNo/ projNo/dateWorked form the key. Formulate the following queries in…arrow_forward
- Consider the following relations for a university: Student (sid: integer, sname: string(100 chars), GPA: real, dateOfBirth: date, Did: integer, FirstYear: integer)Department (did: integer, dname: string(30 chars), capacity: integer)Course (cid: integer, cname: string(50 chars), credit: integer)Enroll (studentID: integer, courseID: integer, departmentID: integer, enrollmentDate: date, finalGrade: real) studentID, courseID, and departmentID in Enroll are foreign keys referencing the primary keys of the student, course, and department relations, respectively. Did in Student is a foreign key referencing the primary key of the department relation. 1) For each student with GPA between 2.5 (included) and 3.5 (included), display the student’s name, GPA,and his/her department ID. Sort the results by the students’ IDs in descending order. 2) For each student who joined the Computer Science department between 2018 (included) and 2020(included), display the…arrow_forwardFormulate the following queries in: Relational Algebra. The following tables form part of a database held in an RDBMS: Employee (empNo, fName, lName, address, DOB, sex, position, salary, deptNo) Department (deptNo, deptName, mgrEmpNo) Project (projNo, projName, deptNo) WorksOn (empNo, projNo, dateWorked, hoursWorked) where Employee contains employee details and empNo is the key. Department contains department details and deptNo is the key. mgrEmpNo identifies the employee who is the manager of the department. There is only one manager foreach department. Project contains details of the projects in each department and the key is projNo (notwo departments can run the same project). WorksOn contains details of the hours worked by employees on each project, and empNo/ projNo/dateWorked form the key. Formulate the following queries in: Relational Algebra. Produce a list of the names of all employees who work on the Biometric project. Produce a report of all projects under the IT…arrow_forwardFormulate the following queries in: Relational Algebra. The following tables form part of a database held in an RDBMS: Employee (empNo, fName, lName, address, DOB, sex, position, salary, deptNo) Department (deptNo, deptName, mgrEmpNo) Project (projNo, projName, deptNo) WorksOn (empNo, projNo, dateWorked, hoursWorked) where Employee contains employee details and empNo is the key. Department contains department details and deptNo is the key. mgrEmpNo identifies the employee who is the manager of the department. There is only one manager foreach department. Project contains details of the projects in each department and the key is projNo (notwo departments can run the same project). WorksOn contains details of the hours worked by employees on each project, and empNo/ projNo/dateWorked form the key. Formulate the following queries in: Relational Algebra. List all the details of employees who are female and born after 1990. List employee numbers and names of all employees who are not…arrow_forward
- 75. ______________ rolls back all transactions that were incomplete at the time of crash. a. Analysis pass b. Redo pass c. Undo pass d. None of the mentionedarrow_forwardUsing MySQL to create a database with six tables. You need to set up primary key andforeign key each table. Make two lines of contents for each table. In Tape table, set foreign key C_number with on update cascade, on delete set null. Set foreign keyM_number with on update cascade and on delete cascade. Remember don’t set customer number andmovie number as required (not null) if you want to set them as null when needed.In Movie table, set the foreign key D_number with on update cascade, on delete set nullIn Played_In, set two foreign keys with on update cascade and on delete cascade.Add one row of data in each table.arrow_forwardFor 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 UPDATE…arrow_forward
- Part A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.arrow_forwardPart A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: Find the sids of suppliers who supply only red parts. Find the sids of suppliers who supply a red part and a green part. Find the sids of suppliers who supply a red part or a green part. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies. For every supplier that supplies a green part and a…arrow_forwardPart A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: Find the pnames of parts supplied by Acme Widget Suppliers and no one else. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part). For each part, find the sname of the supplier who charges the most for that part. Find the sids of suppliers who supply only red parts. Find the…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr