DATABASE

Cashier (identity, name, phone, email)

Supplier (taxID, name, phone, e-mail, number of invoices, invoice total, invoice average)

Product (barcode, name, inventory quantity, productTotal Profit, SalesPriceTrend)

invoice (invoiceNumber, date, total, taxID) // order or purchases

invoiceProducts (invoiceNumber, barcode, quantity, unit, unitPrice)

receipt (receiptNumber, date, hour, total, identity) // sales

receiptProducts (receiptNumber, barcode, quantity, unit, unitPrice)

REQUESTED

PostgreSQL

Create the above database with CREATE TABLE in PostgreSQL and insert the following into each table.

INSERT enough data to do the questions.

Write down the following triggers and procedures and test them. Table records before and after testing

Make sure that triggers / procedures work correctly by querying them.

For each question, write the trigger / procedure source code and test data (i.e. used for testing).

Share the insert / update / delete command, data of records before and after the command).

QUESTIONS

1-)Write the trigger (s) that update the total area in the receipt table when necessary. Tip: to the receipt products table

when a record is added, deleted, or in a record (barcode or quantity or unitPrice fields)

This trigger slip sum value, which will run when updated, is set as "quantity x unit price" in the receiptProducts.

finds by summing its values.

2-) Write the trigger (s) that update the Inventory Amount field in the product table when necessary. Tip: Stock Amount

the sum of quantities from all purchases (invoices) of a product to the sum of quantities in all sales

can be calculated by subtracting. Unit area is not used from this calculation.

3-) Write the trigger (s) that update the SalesPriceTrend field in the product table when necessary. Hint:

The field of SalePriceTrend can be either "ascending" or "descending". This value is in the last two sales of the product according to the date and time.

(receiptProducts) unit is found by comparing the price values: The price in the last sale is before

if greater, the trend will be "increasing", otherwise "descending".

4-) Write the trigger (s) that update the total field in the invoice table when necessary. Tip: invoice products

A record is added to, deleted, or in a record (barcode or quantity or unit

fields), this trigger will run when it is updated.

unit price "by adding up the values.

5-) Updating the InvoiceNumber, InvoiceTotal, InvoiceAverage fields in the supplier table when necessary

Write the trigger (s). Tip: add, delete, or add records to the invoice table

This trigger, which will work if its value is updated, should use the trigger in question 4 indirectly.

Because in the case of adding, deleting or updating a record in the invoiceProducts table, the supplier

The InvoiceTotal and Invoice Average values in the table will need to be recalculated.

6-) A product with a barcode defined as "... sales (barcode, dateTime) returns table ..."

stored function, which returns all sales records (records in the fissures table) after the hour

Write it down and use it in an SQL command to show that this function works. Hint:

Sales (barcode, dateTime) returns table

Table creation--

Create table [IF NOT EXISTS] Cashier (identity serial primary key, name varchar not null, phone integer not null, email varchar not null) ;

Create table [IF NOT EXISTS] Supplier (TAXID integer primary key, name varchar not null, phone integer not null , email varchar unique, number of invoice integer not null, invoice total integer not null, invoice average integer not null) ;

Create table [ID NOT EXISTS] Product (barcode integer , name varchar not null, inventory quantity integer not null, productTotal profit integer not null, salesPriceTrend integer not null) primary key (barcode) ;

Create table [ID NOT EXISTS ]invoice (invoiceNumber integer not null, date date,total integer not null, taxID) Primary key invoice Number, Foreign key supplier (taxID) ;

Create table invoiceProduct (invoiceNumber integer, barcode integer, quantity integer, unit integer not null, unitPrice integer not null) foreign key product (barcode) ;

Create table receipt (receiptNumber integer primary key, date date not null, hours timestamp, total integer not null, identity) foreign key cashier (identity) ;

Create table receipt Product (receiptNumber integer foreign key references receipt Number, barcode integer, quantity integer, unit integer not null, unit Price integer not null) foreign key receiptNumber references receipt (receiptNumber);

please answer the questions write the codes of the questions(data is not important queries that matter)