In lab 4 you set up your own database, and Set up the tables you think are appropriate for an organization that takes orders and sells or distributes something. (Or something else of comparable complexity.) It could be a store selling fruits and vegetables, a food bank, a business selling computer parts, pottery, space ships, towels, or digital watches; it could even be a group distributing open-source software. Not into the profit motive? How about a food bank that distributes donated groceries to clients, who have expressed a need for certain items (not everyone likes broccoli.) Or assigning tasks, such as lawn mowing, to condominium residents, and keeping a count of assignments made. Or making observations on trees in your woodlot. In any case, you have some clients, or customers, who order things. Often they will order multiple quantities of several items (For example, 10 apples, 7 oranges, and 1 bottle of milk). You have an inventory of your stock, and naturally, you can't sell or give away what you don't have, and also you don't want to be caught short. Thus, you should not accept an order for 10 apples unless you have at least that many, and if your stock goes below some threshold, you should get some more from your supplier (or make some more.) So, for each item in your inventory, you will need to know a short description, perhaps a part number, how many you have, the threshold amount (if you go below, time to reorder) amount to reorder, and (if you are selling them) the price. Each order by a client should include the date, and several "line items" one for each different kind of thing. Each line item must identify the kind of thing and the amount (quantity) ordered. An "order" can have an explicit order number, or consist of all items for that client and date. Documentation links PostgreSQL documentation https://www.postgresql.org/docs/current/index.html. Look for: V. Server Programming - Triggers -the Rule System -PL/pgSQL procedural Language VI. Reference I. SQL Reference Checks, constraints, triggers and rules: Now that you have your basic tables (4 at least), the fun begins. In the first place, we don't want any negative amounts in this database. After all, you can't have -34 oranges, or give away -2 snowballs. Please prevent this from happening in your database. The date on an order should be 'now' (current_date). Whenever a line item is created (INSERT statement), use a postgres RULE to subtract the amount from inventory. Or, when a new observation is entered for a tree, add 1 to the number of observations for that tree. When an inventory item goes below its threshold, a TRIGGER (or RULE) should insert a row in the MUST ORDER table (ha, yet another table). The attributes of this table should be the date, key of the item, and the quantity to order (from the inventory table) Note: a RULE works silently. A trigger can RAISE INFO, NOTICE, WARN or EXCEPTION (aborts the transaction), informing you of the low inventory, for example. Somebody else's problem: We will assume that somebody else in your business periodically looks at the MUST ORDER table, orders the stuff, and when I comes in, they add to the inventory and remove the must-order entry. A function please I want a function. If you create a trigger, that requires that a function be created (in language 'plpgsql') The trigger should take some action, such as inserting a row in a "Must restock" table, and perhaps raising a NOTICE. But if you use a RULE for restocking, then write some other function in plpgsql. Please do the following with your function (to be called directly, for instance select arrived ('snow shovel', 16);, or as a trigger, your choice): We said that ordering more stock was "Somebody else's problem." Well, let's assume that the shipment of Snow Shovels has arrived. We count them, and there are not necessarily exactly the 25 we ordered. We have to either delete or modify the 'snow shovel' row of the "Must order" table, and we have to increase the quantity in the "Inventory" table by however many snow shovels actually arrived. It may or may not be necessary to order more snow shovels, this might be handled by your previous rules or triggers, if not, the function can insert a new "must order". An appropriate return value might be the new quantity. Suppose we make a new observation on a tree. Compare it with the last observation. Something is wrong if it is smaller, or now alive after being reported dead. A notice would be in order. So, Do either of the following: 1. A TRIGGER to insert a row in "Must Restock," calling a Trigger function that raises a NOTICE, -- OR 2. A RULE to insert a row in "Must Restock" and a function such as arrived (that can be called from a SELECT statement). Back up your database The utility pg_dump writes text file that will capture everything about a database, as SQL statements that will completely recreate it, with all its data. This file can be used in the future, perhaps even on other hardware. Use terminal commands to save your database (same name as your username) Do this at the end of today's lab, and submit the file What to submit. ⚫ A brief description of your business, Do the Assignment if you haven't yet done so! (Else it's done.) ⚫ A brief account of what you did and accomplished in this week's lab. In particular, what rules you defined, and what your function is named, and what it does. submit using form below. • A pg_dump of your database, as of after you have done all the lab work (use .sql extension) Submit as usual, with submit cs307 you.sql What I expect to find there: Constraints, triggers and rules for tables of inventory, orders and must-order. (Command to show all of this for table mesa is: \d mesa) 1. Constraint on quantity, cannot be negative 2. foreign keys must be present in related table 3. A rule for insert of an order 4. Any trigger you created.

In lab 4 you set up your own database, and Set up the tables you think are appropriate for an organization that takes orders and sells or distributes something. (Or something else of comparable complexity.) It could be a store selling fruits and vegetables, a food bank, a business selling computer parts, pottery, space ships, towels, or digital watches; it could even be a group distributing open-source software. Not into the profit motive? How about a food bank that distributes donated groceries to clients, who have expressed a need for certain items (not everyone likes broccoli.) Or assigning tasks, such as lawn mowing, to condominium residents, and keeping a count of assignments made. Or making observations on trees in your woodlot. In any case, you have some clients, or customers, who order things. Often they will order multiple quantities of several items (For example, 10 apples, 7 oranges, and 1 bottle of milk). You have an inventory of your stock, and naturally, you can't sell or give away what you don't have, and also you don't want to be caught short. Thus, you should not accept an order for 10 apples unless you have at least that many, and if your stock goes below some threshold, you should get some more from your supplier (or make some more.) So, for each item in your inventory, you will need to know a short description, perhaps a part number, how many you have, the threshold amount (if you go below, time to reorder) amount to reorder, and (if you are selling them) the price. Each order by a client should include the date, and several "line items" one for each different kind of thing. Each line item must identify the kind of thing and the amount (quantity) ordered. An "order" can have an explicit order number, or consist of all items for that client and date. Documentation links PostgreSQL documentation https://www.postgresql.org/docs/current/index.html. Look for: V. Server Programming - Triggers -the Rule System -PL/pgSQL procedural Language VI. Reference I. SQL Reference Checks, constraints, triggers and rules: Now that you have your basic tables (4 at least), the fun begins. In the first place, we don't want any negative amounts in this database. After all, you can't have -34 oranges, or give away -2 snowballs. Please prevent this from happening in your database. The date on an order should be 'now' (current_date). Whenever a line item is created (INSERT statement), use a postgres RULE to subtract the amount from inventory. Or, when a new observation is entered for a tree, add 1 to the number of observations for that tree. When an inventory item goes below its threshold, a TRIGGER (or RULE) should insert a row in the MUST ORDER table (ha, yet another table). The attributes of this table should be the date, key of the item, and the quantity to order (from the inventory table) Note: a RULE works silently. A trigger can RAISE INFO, NOTICE, WARN or EXCEPTION (aborts the transaction), informing you of the low inventory, for example. Somebody else's problem: We will assume that somebody else in your business periodically looks at the MUST ORDER table, orders the stuff, and when I comes in, they add to the inventory and remove the must-order entry. A function please I want a function. If you create a trigger, that requires that a function be created (in language 'plpgsql') The trigger should take some action, such as inserting a row in a "Must restock" table, and perhaps raising a NOTICE. But if you use a RULE for restocking, then write some other function in plpgsql. Please do the following with your function (to be called directly, for instance select arrived ('snow shovel', 16);, or as a trigger, your choice): We said that ordering more stock was "Somebody else's problem." Well, let's assume that the shipment of Snow Shovels has arrived. We count them, and there are not necessarily exactly the 25 we ordered. We have to either delete or modify the 'snow shovel' row of the "Must order" table, and we have to increase the quantity in the "Inventory" table by however many snow shovels actually arrived. It may or may not be necessary to order more snow shovels, this might be handled by your previous rules or triggers, if not, the function can insert a new "must order". An appropriate return value might be the new quantity. Suppose we make a new observation on a tree. Compare it with the last observation. Something is wrong if it is smaller, or now alive after being reported dead. A notice would be in order. So, Do either of the following: 1. A TRIGGER to insert a row in "Must Restock," calling a Trigger function that raises a NOTICE, -- OR 2. A RULE to insert a row in "Must Restock" and a function such as arrived (that can be called from a SELECT statement). Back up your database The utility pg_dump writes text file that will capture everything about a database, as SQL statements that will completely recreate it, with all its data. This file can be used in the future, perhaps even on other hardware. Use terminal commands to save your database (same name as your username) Do this at the end of today's lab, and submit the file What to submit. ⚫ A brief description of your business, Do the Assignment if you haven't yet done so! (Else it's done.) ⚫ A brief account of what you did and accomplished in this week's lab. In particular, what rules you defined, and what your function is named, and what it does. submit using form below. • A pg_dump of your database, as of after you have done all the lab work (use .sql extension) Submit as usual, with submit cs307 you.sql What I expect to find there: Constraints, triggers and rules for tables of inventory, orders and must-order. (Command to show all of this for table mesa is: \d mesa) 1. Constraint on quantity, cannot be negative 2. foreign keys must be present in related table 3. A rule for insert of an order 4. Any trigger you created.