A database administrator wants to know the details of the materials used on site A (job tag) and site B (job tag) on two days of the week. Given the work on site B was done on the two days and completed and site A is yet to be completed. Create the required query and triggers needed to give the administrator the current update on both sites. please refer below for more information: CREATE TABLE Transaction ( Transaction_no SERIAL, Product_no INT NOT NULL PRIMARY KEY, Product_name VARCHAR(250) NOT NULL, TxAmount_used Float NOT NULL, Job_tag VARCHAR(250) NOT NULL, TxStatus Status, Uses jsonb NOT NULL, Created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, TxAmount FLOAT, RefNbr INT); Created_at indicates the date and time when the “transaction” was created. It is automatically added every time a trigger is added, with the current time stamp as the value. Updated_at is similar to created_at, except that it is updated every time a “transaction” is updated (e.g, uses, status change etc ). To enable updated_at to be updated as explained above, a trigger needs to be created on the database. Uses is a json object which logs each individual use of the material. Every time technicians log a material’s use on the sites, the name of the technician, the product no, and the amount used are logged as an object in the form of {“name” : “amount”} Status indicates whether the material is still being used on-site, or whether it has already been used. job_tag is the site name for which the material will be used. The user will be prompted to input the job_tag when checking out a material to go the site. amount_used is the total amount of material used on site. Its initial value is 0, and is incremented every time a technician logs its use on the app. The amount specified by the technician is appended to amount_used’s current value TxAmount represents the initial material taken to site at the start of the job and is only updated to show what is left after the job is completed. For site A, it shows what was taken to the site as the job is not complete, and for site B the balance of the material is left. transaction_no is a counter which automatically increases and is automatically added to every row created in the table.
A
CREATE TABLE Transaction (
Transaction_no SERIAL,
Product_no INT NOT NULL PRIMARY KEY,
Product_name VARCHAR(250) NOT NULL,
TxAmount_used Float NOT NULL,
Job_tag VARCHAR(250) NOT NULL,
TxStatus Status,
Uses jsonb NOT NULL,
Created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
TxAmount FLOAT,
RefNbr INT);
Created_at indicates the date and time when the “transaction” was created. It is automatically added every time a trigger is added, with the current time stamp as the value.
Updated_at is similar to created_at, except that it is updated every time a “transaction” is updated (e.g, uses, status change etc ). To enable updated_at to be updated as explained above, a trigger needs to be created on the database.
Uses is a json object which logs each individual use of the material. Every time technicians log a material’s use on the sites, the name of the technician, the product no, and the amount used are logged as an object in the form of {“name” : “amount”}
Status indicates whether the material is still being used on-site, or whether it has already been used.
job_tag is the site name for which the material will be used. The user will be prompted to input the job_tag when checking out a material to go the site.
amount_used is the total amount of material used on site. Its initial value is 0, and is incremented every time a technician logs its use on the app. The amount specified by the technician is appended to amount_used’s current value
TxAmount represents the initial material taken to site at the start of the job and is only updated to show what is left after the job is completed. For site A, it shows what was taken to the site as the job is not complete, and for site B the balance of the material is left.
transaction_no is a counter which automatically increases and is automatically added to every row created in the table.
Step by step
Solved in 2 steps
