Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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)

image text in transcribed

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. 1. 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. 2. 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). 3. 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. 4. 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. 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. 1. 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. 2. 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). 3. 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. 4. 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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Pro SQL Server Wait Statistics

Authors: Enrico Van De Laar

1st Edition

1484211391, 9781484211397

More Books

Students also viewed these Databases questions

Question

=+2 How does the preparation and support for each type of IE vary?

Answered: 1 week ago

Question

=+What is the extent of the use of each type of IE?

Answered: 1 week ago