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
Database Systems: Design, Implementation, & Management
- Task 1: The InstantRide Management team founded a new team for car maintenance. The new team is responsible for the small maintenance operations for the cars in the InstantRide system. The main idea is to take actions faster and minimize the time spent for the maintenance. Therefore, the Car Maintenance team wants to store MAINTENACE_TYPE_ID (char(5)) and a MAINTENANCE_TYPE_DESCRIPTION (varchar(30)) in the database. Using MAINTENANCE_TYPE_ID as the PRIMARY KEY, create a new table, MAINTENANCE_TYPES, and send the table description with the column names and types to the Car Maintenance team.arrow_forwardTask 1:The InstantRide Management team founded a new team for car maintenance. The new team is responsible for the small maintenance operations for the cars in the InstantRide system. The main idea is to take actions faster and minimize the time spent for the maintenance. Therefore, the Car Maintenance team wants to store MAINTENACE_TYPE_ID (char(5)) and a MAINTENANCE_TYPE_DESCRIPTION (varchar(30)) in the database. Using MAINTENANCE_TYPE_ID as the PRIMARY KEY, create a new table, MAINTENANCE_TYPES, and send the table description with the column names and types to the Car Maintenance team. THIS IS FOR CENGAGE MYSQLarrow_forwardA city college would like to maintain their academic information in a MySql database system. The following are the interested information: Courses: Catalog # (CIT 170 for instance) Credit Hours; Name of the Course (Database Design Fundamentals for instance) Course Descriptions Prerequisite Components (Lecture, or Laboratory for instance) Classes: Class # Class Name (usually the course name, CIT 170 for instance) Start Date End Date Meeting time (9:30 am ~ 12:15 pm, for instance) Days in a week (Monday) Term (Spring 2018, for instance) Location (TIE Building for instance) Room (UB 301, for instance) Instructor Online status (online, or in-person) Faculty: Employee Id (001880301, for instance) First Name Last Name Title (professor, for instance) Email Phone Office Location (TIE building, for instance) Room (UB303, for instance) Student: Student ID First Name Last Name Plan Sub Plan Advisor Current GPA Grade of each course The Term you got the grade for a course. The class number…arrow_forward
- You are asked to design a database for an University. This particular university has multipledepartments. Each department in that university offers one or more courses to the students. All thestudents are part of a certain department and they take courses in that department. A department hasa name, unique number and chairman. The courses have a name, a course number such as ‘311’ , acredit point value, and the year it commenced. A course cannot be identified uniquely with thecourse number, but the course number is unique for each department. Students have given names,surname, unique Student ID, Date of Birth and the year they enrolled. When a student takes a coursethe year and the semester he or she took that particular course is recorded. When he finishes thecourse , the grade and total marks are recorded. A student can take multiple courses but they are alsoallowed to drop a semester in that university by taking no courses. Construct an ER diagram usingthe requirements given here.…arrow_forwardConsider the following schema for a company database and answer the following questions: DEPARTMENT Dname Dnumber Mgr_ssn Mgr start date M EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Dno M WORKS ON Essn Pno Hours M PROJECT Pname Pnumber Plocation Q1//create all tables of this schema and apply any necessary constraints Q2// Write SQL statements that carry out the following tasks: 1-alter the employee table to add column city of 20 characters. 2- alter the employee table to apply the following business rule The minimum salary is 3000 and maximum salary is 10000 3- insert sample data in each table. 4- display a list of each employee SSN , Name and Salary. 5- display a list of each employee SSN, Name and Salary for employees working in department no 2.arrow_forwardConsider the following schema for a company database and answer the following questions: DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date M EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Dno WORKS ON Essn Pno Hours M PROJECT Pname Pnumber Plocation Q1//create all tables of this schema and apply any necessary constraints Q2// Write SQL statements that carry out the following tasks: 1-alter the employee table to add column city of 20 characters. 2- alter the employee table to apply the following business rule The minimum salary is 3000 and maximum salary is 10000 3- insert sample data in each table . 4- display a list of each employee SSN, Name and Salary. 5- display a list of each employee SSN , Name and Salary for employees working in department no 2.arrow_forward
- A database at a college must support the following requirements: a. For a department, store its number and name. b. For an advisor, store his or her number, last name, first name, and the department number to which the advisor is assigned. c. For a course, store its code and description (for example, DBA210, SQL Programming). d. For a student, store his or her number, first name, and last name. For each course the student takes, store the course code, course description, and grade earned. Also, store the number and name of the student's advisor. Assume that an advisor might advise any number of students but that each student has just one advisor. Design the database for the preceding set of requirements. Determine any functional dependencies. List the tables, columns, and relationships. In addition, represent your design with an E-R diagram.arrow_forwardPLZ HELP WITH THE FOLLOWING: We will assume that the Computer Science Department at a university has an agreement with the publishers to allow faculty and students to have free exclusive access to the e-books online through their website. They need you to design a database for them using the following business rules: The faculty and students (we refer to them in our system as clients) can electronically check out any book at any time. A book must have at least one author but can have more authors. An author must have written at least one book or more to be included in the system. A book may have never been checked out, but can be checked out many times by the same client or different clients over time. A client may have never checked out a book or they may have checked out many books over time. To simplify determining which client currently has a given book checked out, a M:N relationship between BOOK and CLIENT should be maintained. The information recorded for each book is book…arrow_forwardWe need to create a database for the Circulation Department of a public library. The database will store information about the patrons, books, and circulation. It contains the following: 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows: PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the…arrow_forward
- We need to create a database for the Circulation Department of a public library. The database will store information about the patrons, books, and circulation. It contains the following: 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows: PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the…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_forwardWe need to create a database for the Circulation Department of a public library. The database will store information about the patrons, books, and circulation. It contains the following: 4 tables for the database: PATRON, BOOK_COPY, BOOK, and CHECKOUT. (Since a book may have multiple copies that may be purchased by the library at different time, it is better to have a BOOK_COPY table to avoid unnecessary data redundancy.) The data in the tables are as follows: PATRON table records a patron’s ID, name, address, phone number, and email address. BOOK table contains information such as author, title, publication date, subject, language, and a unique identifier (It can be the ISBN of the book) for each book. BOOK_COPY table records a unique identifier for each copy of a book, the date of purchase, and the identifier of the book from the BOOK table. CHECKOUT table records the date of check-out, patron’s ID, the…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