a)
Explanation of Solution
Number of
The number of database request based on the SQL query of the transaction.
- If the user gives the query to add the product “ABC” by “1”, reducing each parts “A”, “B”, and “C” individually means, the number transaction request will be “4”.
- If the user add the product “ABC” by “1”, reducing each parts “A”, “B”, and “C” in a single statement using “OR” condition means, the number transaction request will be “2”.
b)
Explanation of Solution
SQL statement for each database requests that identified in “Step a”:
Four SQL statements:
SQL Query:
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH + 1
WHERE PROD_CODE = ‘ABC’
Explanation:
The above SQL query is to update the “PROD_QOH” field using “UPDATE” statement that adds the new product by “1” to “PRODUCT” table where the product code is “ABC”.
SQL Query:
UPDATE PART
SET PART_QOH = PART_QOH - 1
WHERE PART_CODE = ‘A’
Explanation:
The above SQL query is to update the “PART_QOH” field using “UPDATE” statement to reduce the parts inventory by “1” from “PART” table where the product code is “A”.
SQL Query:
UPDATE PART
SET PART_QOH = PART_QOH - 1
WHERE PART_CODE = ‘B’
Explanation:
The above SQL query is to update the “PART_QOH” field using “UPDATE” statement to reduce the quantity by “1” from “PART” table where the product code is “B”.
SQL Query:
UPDATE PART
SET PART_QOH = PART_QOH - 1
WHERE PART_CODE = ‘C’
Explanation:
The above SQL query is to update the “PART_QOH” field using “UPDATE” statement to reduce the parts inventory by “1” from “PART” table where the product code is “C”.
Two SQL statements:
The following SQL UPDATE statement to add the new product by “1” to “PRODUCT” table where the product code is specified as “ABC”.
SQL Query:
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH + 1
WHERE PROD_CODE = ‘ABC’
Explanation:
The above SQL query is to update the “PROD_QOH” field using “UPDATE” statement to reduce the parts inventory by “1” from “PRODUCT” table where the product code is “ABC”.
SQL Query:
UPDATE PART
SET PART_QOH = PART_QOH - 1
WHERE PART_CODE = ‘A’ OR PART_CODE= ‘B’ OR PART_CODE= ‘C’
Explanation:
The above SQL query is to update the “PART_QOH” field using “UPDATE” statement to reduce the parts inventory by “1” from “PART” table where the product code is either “A” or “B” or “C”.
c)
Explanation of Solution
Complete SQL transaction statements:
Four SQL statements:
BEGIN TRANSACTION
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH + 1
WHERE PROD_CODE = ‘ABC’
UPDATE PART
SET PART_QOH = PART_QOH - 1
WHERE PART_CODE = ‘A’
UPDATE PART
SET PART_QOH = PART_QOH - 1
WHERE PART_CODE = ‘B’
UPDATE PART
SET PART_QOH = PART_QOH - 1
WHERE PART_CODE = ‘C’
COMMIT;
Explanation:
The above SQL transaction is to update the tables “PRODUCT” and “PART” by adding and removing the value “1” from “PART_QOH” and “PROD_QOH” field.
- Add the value of “PROD_QOH” field by “1” where “PROD_CODE” is “ABC”.
- Reduce the value by “1” from “PART_QOH” field in “PART” table where the “PART_CODE” either “A”, “B”, or “C”.
Two SQL statements:
BEGIN TRANSACTION
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH + 1
WHERE PROD_CODE = ‘ABC’
UPDATE PART
SET PART_QOH = PART_QOH - 1
WHERE PART_CODE = ‘A’ OR
PART_CODE = ‘B’ OR
PART_CODE = ‘C’
COMMIT;
Explanation:
The above SQL transaction is to update the tables “PRODUCT” and “PART” by adding and removing the value “1” from “PART_QOH” and “PROD_QOH” field.
- Add the value of “PROD_QOH” field by “1” where “PROD_CODE” is “ABC”.
- Reduce the value by “1” from “PART_QOH” field in “PART” table where the “PART_CODE” either “A”, “B”, or “C”.
d)
Transaction log:
It is a feature used by the DBMS software to keep track all of the information that contains a description of all database transactions executed by the DBMS. This transaction plays the major role for database maintenance.
d)
Explanation of Solution
Transaction log for the transaction that was mentioned in subpart “c”:
The product of the ‘ABC’ has a PROD_QOH = 1,205 at beginning of the transaction and that the transaction is specified the addition of one new product.
The PART components “A”, “B” and “C” have a PROD_QOH equal to 567, 98, and 549 respectively.
Trans_ ID |
Trans_ NUM |
Prev_ptr | Next_ptr |
Operation |
Table |
Value_ID |
Attribute |
Before_ trans |
After_ trans |
1 | T1 | NULL | 2 | START | **START TRANSACTION | ||||
2 | T1 | 1 | 3 | UPDATE | PRODUCT | ‘ABC’ | PROD_QOH | 1025 | 1026 |
3 | T1 | 2 | 4 | UPDATE | PART | ‘A’ | PART_QOH | 567 | 566 |
4 | T1 | 3 | 5 | UPDATE | PART | ‘B’ | PART_QOH | 98 | 97 |
5 | T1 | 4 | 6 | UPDATE | PART | ‘C’ | PART_QOH | 549 | 548 |
6 | T1 | 5 | NULL | COMMIT |
** END TRANSACTION |
e)
Explanation of Solution
Trace out of transaction log mentioned in sub part “d”:
The above transaction log has transaction ID(Trans_ID), transaction number(Trans_NUM), and other fields used to recover the transaction.
The trace out of transaction log from beginning of the transaction is as follows:
Trans_ID 1: Beginning of the transaction.
Trans_ID 2: Update the table “PRODUCT” by adding the attribute value from “1025” to “1026”.
Trans_ID 3: Update the table “PART” by removing the attribute value from “567” to “566”.
Trans_ID 4: Update the table “PART” by removing the attribute value from “98” to “97”.
Trans_ID 5: Update the table “PART” by removing the attribute value from “549” to “548”.
Trans_ID 6: End of the transaction.
Want to see more full solutions like this?
Chapter 10 Solutions
EP DATABASE SYSTEMS-MINDTAP
- Consider a database that consists of the following relations.SUPpLIER(Sno. Sname)PART(Pno, Pname)PROJECT(Jno, Jname)SUPPLY(Sno, Pno, Jno)The database records information about suppliers, parts, and projectsandincludes a ternary relationship between suppliers, parts, and projects.Thisrelationship is a many-many-many relationships. Specify and execute thefollowingqueries using the relational algebraRetrieve the part numbers that are supplied to exactly two projects.b. Retrieve the names of suppliers who supply more than two parts toproject J11c. Retrieve the part numbers that are supplied by every supplier.d. Retrieve the project names that are supplied by supplier S1' only.e. Retrieve the names of suppliers who supply at least two different partseach to at least two different projects.arrow_forwardConsider the relational database below, where the primary keys are underlined.employee (person-name, street, city)works (person-name, company-name, salary)company (company-name, city)manages (person-name, manager-name)Give a query for each of the following:a Give all managers in database a 10 percent raise.b Give managers in the database a 10 percent raise, unless the salary would be greaterthan $100,000. In such cases, give only 3 percent raise.c Delete all tuples in the works relation for employees of Small Bank Corporationarrow_forwardSuppose you are given the following requirements for a simple database for the football League (FL): the FL has many teams, each team has a name, a city, a coach, a captain, and a set of players; 24 players, each player belongs to only one team, each player has a name, a position (such as left wing or goalie), a skill level, and a set of injury records, · a team captain is also a player, a game is played between two teams (host team and guest team) and has a date (such as May 11th, 2020) and a score (such as 4 to 2). Construct a Database Conceptual Model using UML ER diagram for the FL database. Use mapping rules to convert the design to Logical database model using UML ER model. Check the entities to be in the 3NF normal form. Write the needed SQL Statements to create the database.arrow_forward
- Suppose you are given the following requirements for a simple database for the football League (FL): the FL has many teams, each team has a name, a city, a coach, a captain, and a set of players; 24 players, each player belongs to only one team, each player has a name, a position (such as left wing or goalie), a skill level, and a set of injury records, a team captain is also a player, a game is played between two teams (host team and guest team) and has a date (such as May 11th, 2020) and a score (such as 4 to 2). Construct a Database Conceptual Model using UML ER diagram for the FL database. Use mapping rules to convert the design to Logical database model using UML ER model. Check the entities to be in the 3NF normal form. Write the needed SQL Statements to create the database.arrow_forwardStart a database transaction. Next time we meet, we'll be optimistic.arrow_forwardWRITE THE OF MYSQL IN PYTHON, JUST ANSWER THE 2ND QUESTION BY LOOKING AT THE FIRST QUESTION Create database and tables as given below by using Python. (a) Market database (b) Vendor: vendor id integer, vendor name varchar, license start date, license end date (c) Item:itemidinteger,itemnamevarchar,itemtypevarchar, previous price float, last sold date date (d) Auction: auction id integer, vendor id integer, item id integer, price float Choose primary and foreign keys depending on the relations in Market database design. Create arrays of at least 5 di↵erent records. Use these arrays to populate each table in the database.arrow_forward
- Think about a student database with the StudentID as the main key and the Major, Age, MaritalStatus, and HomeZipCode indexes (all secondary keys). If the institution requested a list of MIS or computer science majors who were over 25, married, and from the 45462 zip code OR computer engineering majors who were single and from the 45462 zip code, then let's pretend they were both over 25 and from the 45462 postcode. How can indexes be used to guarantee that only the data that satisfies this criteria is accessible to users?arrow_forwardWRITE THE CODE OF MYSQL IN PYTHON Create database and tables as given below by using Python. (a) Market database (b) Vendor: vendor_id integer, vendor_name varchar, license_start date, license_end date (c) Item: item_id integer, item_name varchar, item_type varchar, previous_price float, last_sold_date date (d) Auction: auction_id integer, vendor_id integer, item_id integer, price float Choose primary and foreign keys depending on the relations in Market database design.arrow_forwardFollowing on your BIG break to make a database for an… organization that organizes table tennis tournaments (also known as “Tourney”s) at their premises on behalf of clubs, you’ve written down the business rules as follows: One Match belongs to One Tourney, and not none. One Tourney has Many Matches, at least one and a maximum of 128. One Match takes place on One Table, and not none. One Table is used in Many Matches, and possibly none. One Match belongs to one Category, and not none. One Category appears in Many Matches, and possibly none and a max of 32. One Match involves Many Players, minimum 2 and maximum 4. One Player plays Many Matches, and possibly none. You have also been given a small sample of their records (which is not in the least in relational format), which may prove useful. It is below. Match Number Tourney Tourney Start Date Day Time Slot In Day Player Names(Team 1) Player Names(Team 2) Player IDs (Team 1) Player IDs(Team 2)…arrow_forward
- Suppose you are given the following requirements for a simple database for the football League (FL): the FL has many teams, each team has a name, a city, a coach, a captain, and a set of players; 24 players, each player belongs to only one team, each player has a name, a position (such as left wing or goalie), a skill level, and a set of injury records, • a team captain is also a player, • a game is played between two teams ( 2020) and a score (such as 4 to 2). host team and guest team) and has a date (such as May 11th, 1- Construct a Database Conceptual Model using UML ER diagram for the FL database. 2- Use mapping rules to convert the design to Logical database model using UML ER model. 3- Check the entities to be in the 3NF normal form. 4. Write the needed SQL Statements to create the database.arrow_forwardYou are given the following database schema:Sailors(sid, sname, rating, age)Boats(bid, bname, color)Reserves(sid, bid, day)Consider the join on Sailors ▷◁ Reserves and assume that you have the following statistics andlimitations: * You have available 9 pages in main memory.* Sailors: 100000 tuples, 1000 pages; Boats: 2000 tuples, 10 pages; Reserves: 110000 tuples,550 pages 1. Calculate the I/O cost of this join using the Block Nested Loop algorithm?arrow_forwardHere's a situation to mull over: two students are interested in enrolling in the same class, but there's only room for one of them. What is it about a database that makes it so they can't sit together?arrow_forward
- 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 LearningA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr