1. Exercise 2.4.1 where you modify the data in Fig 2.20 and 2.21 as follows: halve...

Transcribed Image Text:

1. Exercise 2.4.1 where you modify the data in Fig 2.20 and 2.21 as follows: halve and round up all the model numbers, add fifty, if that number already exists. i.e., 1001 becomes 501, 1002 becomes 551. Subtract 0.05 from each entry in the speed column, make all prices 20% higher, and if a color value was true make it false and vice-versa. For queries involving numbers reduce the number by 10%, in part (c) change the maker from B to C. for part (e) change to sell PC's, but not Laptops. 2. Exercise 2.4.2 for the modified queries as modified above in the first problem. In at least one case come up with an alternative tree that computes the same query. 3. Using the tables from problem (1) express the following constraints using the constraint formalism from class: a. Printer model numbers must be model numbers of Products using the constraint mechanism from class. b. A laptop with a screen size less than 15.7 inches must have at least a 110 gigabyte hard disk or sell for at most $900. 4. Make a relation with types representing the data on a California ID/Driver's License, including Licence No, Expiration, Last Name, First Name, etc. What FD's would you expect to hold for this relation? What are the keys for the relation? maker A A A A A A B B B B с DDDDDEEEEEEEEEFFGH H model 1001 рс 1002 рс 1003 рс 2004 2005 2006 1004 1009 рс 1005 рс рс 1006 2007 1007 1008 1010 3004 3005 1011 1012 1013 2001 2002 2003 3001 3002 3003 type 2009 2010 laptop laptop laptop 3006 laptop рс рс рс рс printer printer 2008 laptop рс рс рс laptop laptop laptop printer printer printer laptop laptop printer 3007 printer Figure 2.20: Sample data for Product model speed ram hd price 1001 2.66 1024 250 2114 1002 2.10 512 250 1003 1.42 512 80 1004 2.80 1024 250 1005 3.20 512 250 1006 3.20 1024 320 1049 1007 2.20 1024 200 510 1008 2.20 2048 250 770 1009 2.00 1024 250 650 2048 300 1010 2.80 770 1011 1.86 2048 160 959 1024 160 649 529 1012 2.80 1013 3.06 512 80 (a) Sample data for relation PC 995 478 649 630 model speed ram hd screen price 3673 2001 2.00 2048 240 20.1 2002 1.73 1024 80 17.0 949 2003 1.80 512 60 549 1150 2500 1700 1429 900 15.4 13.3 17.0 2004 2.00 512 60 2005 2.16 1024 120 2006 2.00 2048 80 15.4 2007 1.83 1024 120 13.3 2008 1.60 1024 100 15.4 2009 1.60 512 80 14.1 2010 2.00 2048 160 15.4 (b) Sample data for relation Laptop model color type 3001 true ink-jet 3002 false laser 3003 true laser 3004 true ink-jet 3005 false laser 680 2300 price 99 239 899 120 120 3006 true ink-jet 3007 true laser (c) Sample data for relation Printer Figure 2.21: Sample data for relations of Exercise 2.4.1 100 200 1. Exercise 2.4.1 where you modify the data in Fig 2.20 and 2.21 as follows: halve and round up all the model numbers, add fifty, if that number already exists. i.e., 1001 becomes 501, 1002 becomes 551. Subtract 0.05 from each entry in the speed column, make all prices 20% higher, and if a color value was true make it false and vice-versa. For queries involving numbers reduce the number by 10%, in part (c) change the maker from B to C. for part (e) change to sell PC's, but not Laptops. 2. Exercise 2.4.2 for the modified queries as modified above in the first problem. In at least one case come up with an alternative tree that computes the same query. 3. Using the tables from problem (1) express the following constraints using the constraint formalism from class: a. Printer model numbers must be model numbers of Products using the constraint mechanism from class. b. A laptop with a screen size less than 15.7 inches must have at least a 110 gigabyte hard disk or sell for at most $900. 4. Make a relation with types representing the data on a California ID/Driver's License, including Licence No, Expiration, Last Name, First Name, etc. What FD's would you expect to hold for this relation? What are the keys for the relation? maker A A A A A A B B B B с DDDDDEEEEEEEEEFFGH H model 1001 рс 1002 рс 1003 рс 2004 2005 2006 1004 1009 рс 1005 рс рс 1006 2007 1007 1008 1010 3004 3005 1011 1012 1013 2001 2002 2003 3001 3002 3003 type 2009 2010 laptop laptop laptop 3006 laptop рс рс рс рс printer printer 2008 laptop рс рс рс laptop laptop laptop printer printer printer laptop laptop printer 3007 printer Figure 2.20: Sample data for Product model speed ram hd price 1001 2.66 1024 250 2114 1002 2.10 512 250 1003 1.42 512 80 1004 2.80 1024 250 1005 3.20 512 250 1006 3.20 1024 320 1049 1007 2.20 1024 200 510 1008 2.20 2048 250 770 1009 2.00 1024 250 650 2048 300 1010 2.80 770 1011 1.86 2048 160 959 1024 160 649 529 1012 2.80 1013 3.06 512 80 (a) Sample data for relation PC 995 478 649 630 model speed ram hd screen price 3673 2001 2.00 2048 240 20.1 2002 1.73 1024 80 17.0 949 2003 1.80 512 60 549 1150 2500 1700 1429 900 15.4 13.3 17.0 2004 2.00 512 60 2005 2.16 1024 120 2006 2.00 2048 80 15.4 2007 1.83 1024 120 13.3 2008 1.60 1024 100 15.4 2009 1.60 512 80 14.1 2010 2.00 2048 160 15.4 (b) Sample data for relation Laptop model color type 3001 true ink-jet 3002 false laser 3003 true laser 3004 true ink-jet 3005 false laser 680 2300 price 99 239 899 120 120 3006 true ink-jet 3007 true laser (c) Sample data for relation Printer Figure 2.21: Sample data for relations of Exercise 2.4.1 100 200

Answer rating: 100% (QA)

Since this task involves modifying data provided in the figures and then applying certain constraints and queries based on those modifications I will provide guidance on how you can approach each step of the exercise 1 Lets start with Exercise 241 The instructions ask you to modify the data as follows Halve and round up all the model numbers then add fifty If a number already exists increment it until it is unique Subtract 005 from the speed column Increase prices by 20 If a color value is true make it false and viceversa To modify these values youd need to go through each table and apply these transformations Lets look at an example from the PC relation Original model number 1001 Modified model number 1001 2 rounded up 50 501 50 551 Original speed 266 Modified speed 266 005 261 Original price 2114 Modified price 2114 120 25368 Rounded up or down based on the standard rounding rule Continue to apply these transformations to every relevant value in the tables provided in Figures 220 and 221 2 Exercise 242 involves modifying the queries based on the changes youve made Depending on the queries youll need to adjust attributes values and conditions to reflect the new data 3 Express the following constraints using the constraint ... View the full answer