Problem 2. Trigger Specification in SQL [50 pts) Consider the relational schema below with primary key constraints as specified. However, the designer forgot to define any foreign keys Product(model PRIMARY KEY, manufacturer, type) PC(model PRIMARY KEY, speed, ram, hd, rd, price) Laptop(model PRIMARY KEY, speed, ram, hd, screen, price) Now determine how many triggers you need, what events you need to monitor, and lastly what actions are the most meaningful to take for each of the examples below. Test out your triggers, once designed, on the data set that we provide. Turn in your script illustrating the success of your tests. a) Write one or more triggers to enforce overlap constraints, namely, to specify that when inserting a new laptop, the model number should not also appear in the PC table, and vice versa. b) Write one or more triggers to specify that for any tuple in the PC table the hard disk of the PC is at least 100 times greater than its RAM. (Note that the hard disk is in GB, while RAM is in MB). c) Write the needed triggers to enforce that whenever the prices of a Product model are being modified, that then there is a "log tuple" inserted into a special relational table (call it Product- Monitoring) that indicates the model number of the modified product listing, the type of the product (pc, printer, etc.), the old price, and the new price, the time of the modification. Note that a command such as "to_char(sysdate,'dd-mm-yyyy: hh24:mi')" could be used to produce a date value. Also, remember that to first create the product-Monitoring table with the appropriate attributes. Note: if copy-pasting the "to char" function, check that your single quotes are actually single quotes and not apostrophes. The results of copy-pasting may cause an error, so delete and re- type the single quotes. d) Write one or more triggers to enforce the constraint that at all times the Product table is consistent with the other two tables. This is an extension of the foreign key constraint semantics. So now assume here that you did not have access in your DBMS to any direct support for foreign keys. That is, if in the Product table, a product row is specified as being of PC type then its model number also appears in the corresponding PC table. Similarly, if a product is of type laptop, then its model number must also appear in the laptop table. If the type VALUE is "NULL", then it should appear in none of the other tables. Or, vice-versa, check that any tuple that is being inserted into the Laptop or the PC table, either already exists in the Product table, or if not then you also will add it into the Product table as part of the current update. Problem 2. Trigger Specification in SQL [50 pts) Consider the relational schema below with primary key constraints as specified. However, the designer forgot to define any foreign keys Product(model PRIMARY KEY, manufacturer, type) PC(model PRIMARY KEY, speed, ram, hd, rd, price) Laptop(model PRIMARY KEY, speed, ram, hd, screen, price) Now determine how many triggers you need, what events you need to monitor, and lastly what actions are the most meaningful to take for each of the examples below. Test out your triggers, once designed, on the data set that we provide. Turn in your script illustrating the success of your tests. a) Write one or more triggers to enforce overlap constraints, namely, to specify that when inserting a new laptop, the model number should not also appear in the PC table, and vice versa. b) Write one or more triggers to specify that for any tuple in the PC table the hard disk of the PC is at least 100 times greater than its RAM. (Note that the hard disk is in GB, while RAM is in MB). c) Write the needed triggers to enforce that whenever the prices of a Product model are being modified, that then there is a "log tuple" inserted into a special relational table (call it Product- Monitoring) that indicates the model number of the modified product listing, the type of the product (pc, printer, etc.), the old price, and the new price, the time of the modification. Note that a command such as "to_char(sysdate,'dd-mm-yyyy: hh24:mi')" could be used to produce a date value. Also, remember that to first create the product-Monitoring table with the appropriate attributes. Note: if copy-pasting the "to char" function, check that your single quotes are actually single quotes and not apostrophes. The results of copy-pasting may cause an error, so delete and re- type the single quotes. d) Write one or more triggers to enforce the constraint that at all times the Product table is consistent with the other two tables. This is an extension of the foreign key constraint semantics. So now assume here that you did not have access in your DBMS to any direct support for foreign keys. That is, if in the Product table, a product row is specified as being of PC type then its model number also appears in the corresponding PC table. Similarly, if a product is of type laptop, then its model number must also appear in the laptop table. If the type VALUE is "NULL", then it should appear in none of the other tables. Or, vice-versa, check that any tuple that is being inserted into the Laptop or the PC table, either already exists in the Product table, or if not then you also will add it into the Product table as part of the current update