Transporting Efficiency Query

You will a query to calculate the number of days between the date an order was placed and the date the order was shipped for each order. The result of your work will be a list of orders that took more than 30 days to ship. The salespeople will be required to review the records and report the source of the delay for each order. The CEO feels there may be issues with one of the shipping companies, and would like data to back that up.

aery using Query Design. From the Customers table, include the fields CompanyName, ContactName, ContactTitle, and Phone. From the Orders table, include the fields OrderID, OrderDate, and ShippedDate.

b.Run the query and examine the records. Save the query as Shipping Efficiency.

c.Add a calculated field named DaysToShip to calculate the number of days taken to fill each order. (Hint: The expression will include the OrderDate and the ShippedDate; the results will not contain negative numbers.)

d.Run the query and examine the results. Does the data in the DaysToShip field look accurate? Save the query.

e.Add criteria to limit the query results to include only orders that took more than 30 days to ship.

f.Add the Quantity field from the Order Details table and the ProductName field from the Products table to the query. Sort the query by ascending OrderID. When the sales reps contact these customers, these two fields will provide useful information about the orders.

g.Add the caption Days to Ship to the DaysToShip field. Switch to Datasheet view to view the results. Adjust column widths as necessary.

h.Save and close the query.

Order Summary Query

The CEO is considering the financial impact of discounts. She asked for a query showing the employee name, number of orders they have taken, and the total discount amount they have given customers. She hopes to see if there is a correlation between the discount offered and the number of sales.

a.uery using Query Design and add the Orders, Order Details, Products, and Customers tables. Add the fields OrderID and OrderDate from the Orders table. Set both fields' Total row to Group By.

b.Add a calculated field in the third column. Name the field ExtendedAmount. This field should multiply the quantity ordered (from the Order Details table) by the unit price for that item (from the Products table). This will calculate the total amount for each order. Format the calculated field as Currency and change the caption to Total Dollars. Change the Total row to Sum.

c.Add a calculated field in the fourth column. Name the field DiscountAmount. The field should multiply the quantity ordered, the unit price for that item, and the discount field (from the Customers table). This will calculate the total discount for each order. Format the calculated field as Currency and add a caption of Discount Amt. Change the Total row to Sum.

d.Run the query. Examine the results. Most customers should have a discount of 10% of the total dollars, but some customers will have no discount. Save the query as Order Summary. Return to Design view.

e.Add criteria to the OrderDate field so only orders made between 1/1/2016 and 12/31/2016 are displayed. Change the Total row to Where. This expression will display only orders that were placed in 2016.Software engineering pros properlgate the QRS column and widths, solve as set below:open the sp_home_txt.html and sp_layout_txt.css files from the html03 > case1 folder. Enter your name and the date in the comment section of each file, and save them as sp_home.html and sp_layout.css respectively.

2. Go to the sp_home.html file in your editor. Within the document head, create links to the sp_base.css, sp_styles.css, and sp_layout.css style sheet files. Study the content and structure of the file and then save your changes to the document.

3. Go to the sp_layout.css file in your editor. Go to the Window and Body Styles section. style rule for the html element that sets the height of the browser window at 100%.

4. style rule for the page body that sets the width to 95% of the browser window ranging

from 640 pixels up to 960 pixels. Horizontally center the page body within the browser window.

Finally, Karen wants to ensure that the height of the page body is always at least as high as the

browser window itself. Set the minimum height of the browser window to 100%.

5.style rule to apply the Border Box model to all header, ul, nav, li, and a elements in

the document.

6. Go to the Row Styles section. Karen has placed all elements that should be treated as grid rows

in the row class. For every element of the row classstyle rule that expands the element

to cover any floating content within the element. (Hint: Use the technique shown in the tutorial

that employs the after pseudo-element.)

7. Go to the Page Header Styles section. In this section, you will create styles for the content of

the body header. tyle rule for the logo image within the body header that displays the

image as a block with a width of 70% of the header, floated on the left margin.

8. The header also contains a navigation list that Karen wants to display vertically. C style

rule for the nav element within the body header that: a) floats the navigation list on the left,

b) sets the size of the left and right padding to 2%, and c) sets the width of the navigation list

to 30% of the width of the header.

9. The hypertext links in the navigation list should be displayed as blocks. Cyle rule for every a

element in the header navigation list that displays the element as a block with a width of 100%.

10. Go to the Horizontal Navigation List Styles section. Karen has added a second navigation list that

she wants to display horizontally. For all list items within the horizontal navigation list,

style rule that displays the items as blocks with a width of 12.5% floated on the left margin.

11. Go to the Topics Styles section. This section sets the styles for a list of four topics describing what

the company is offering. Karen wants this list to also be displayed horizontally on the page. For

list items within the ul element with the id topics,a style rule to: a) display the items as

blocks with a width of 20%, b) float the items on the left margin, and c) set the size of the left

margin space to 0% and the right margin space to 1.5%.

12. Karen wants the topics list to be well away from the left and right edges of the page body. In the

same section, c rule that sets the size of the left margin of the first item in the topics list to

7.75% and sets the right margin of the last item to 7.75%.

13. In the same section, ca rule that displays the image within each list item in the topics list as

a block with a width of 50% and centered within the list item block. (Hint: Set the left and right

margins to auto.)

14. Go to the HR Styles section. The hr element is used to display a horizontal divider between

sections of the page. Add a style rule that sets the width of the hr element to 50%.

15. Go to the Customer Comment Styles section. In this section, you will create style rules for the

customer comments displayed near the bottom of the page. For the ul element with the id

comments, a style rule that sets the width to 75% and centers the element by setting the

top/bottom margin to 40 pixels and the left/right margin to auto.

16. Karen wants the list items to appear in two columns on the page. In the same section, c

style rule for every list item in the comments list that: a) displays the item as a block with a width

of 50% floated on the left and b) sets the size of the bottom margin to 30 pixels.

17. Every customer comment is accompanied by an image of the student. Karen wants these

images displayed to the left of the comment. style rule to display the image within

each comment list item as a block with a width of 20%, floated on the left, and with a left/right

margin of 5%.

18. a style rule for every paragraph nested within a customer list item that floats the

paragraph on the left margin with a width of 70%.

19. Go to the Footer Styles section anda style rule that displays the footer only when both

margins are clear of floating objects.

20. Save your changes to the file and then open the sp_home.html file in your browser. Verify that

the layout and appearance of the page elements resemble that shown in Figure 3-66.

Shipping Efficiency Query

You will an inquiry to work out the quantity of days between the date a request was put in and the date the request was delivered for each request. The consequence of your work will be a rundown of orders that required over 30 days to transport. The sales reps will be expected to audit the records and report the wellspring of the postponement for each request. The CEO feels there might be issues with one of the transportation organizations, and would like information to back that up.

aery utilizing Query Design. From the Customers table, incorporate the fields CompanyName, ContactName, ContactTitle, and Phone. From the Orders table, incorporate the fields OrderID, OrderDate, and ShippedDate.

b.Run the inquiry and inspect the records. Save the inquiry as Shipping Efficiency.

c.Add a determined field named DaysToShip to work out the quantity of days taken to take care of each request. (Here's a clue: The articulation will incorporate the OrderDate and the ShippedDate; the outcomes won't contain negative numbers.)

d.Run the inquiry and look at the outcomes. Does the information in the DaysToShip handle look precise? Save the inquiry.

e.Add rules to restrict the question results to incorporate just requests that required over 30 days to transport.

f.Add the Quantity field from the Order Details table and the ProductName field from the Products table to the inquiry. Sort the question by rising OrderID. At the point when the agents contact these clients, these two fields will give valuable data about the orders.

Evil Robot's dream of lasting romance remains, alas, just a dream. His latest obsession is a toasted sandwich-maker called SN00005833. In order to win her affections he plans to buy her a chocolate muffin from her favourite shopFat Finbar's World of Cakebefore stealing a bunch of flowers from the local cemetery, giftwrapping the presents, and presenting both gifts to her. Evil Robot's internal systems have been constructed using the situation calculus and a theorem prover. (a) Describe the situation calculus, concentrating on the fundamental elements that you would expect to see independently of any specific problem. [5 marks] (b) Suggest two logical formulae that might appear in Evil Robot's knowledge base in order to describe the initial state for the above problem. [2 marks] (c) Give two examples of a possibility axiom that might appear in the knowledge base. [4 marks] (d) Give two examples of a successor-state axiom that might appear in the knowledge base. One of these should in addition address the ramification problem. Explain how it does this. [6 marks] (e) Give one example of a unique names axiom and one example of a unique actions axiom that might appear in Evil Robot's knowledge base for this problem. Explain why such axioms are required. [3 marks] 2 CST.2014.4.3 2 Artificial Intelligence I This question relates to binary constraint satisfaction problems (CSPs). A CSP has a set X = {x1, . . . , xn} of variables, each having a domain Di = {v1, . . . , vni } of values. In addition, a CSP has a set C = {C1, . . . , Cm} of constraints, each relating to a subset of X and specifying the allowable combinations of assignments to the variables in that subset. (a) Give a general definition of a solution to a CSP. [1 mark] (b) Given a binary CSP, define what it means for a directed arc xi xj between variables xi and xj to be arc consistent. [2 marks] (c) Give an example of how a directed arc xi xj can fail to be arc consistent. Explain how this can be fixed. [2 marks] (d) Describe the AC-3 algorithm for enforcing arc consistency. [5 marks] (e) Prove that the time complexity of the AC-3 algorithm is O(n 2d 3 ) where d is the size of the largest domain. [3 marks] (f ) Suggest a way in which the concept of arc consistency, also known as 2-consistency can be extended to sets of three, rather than two variables. In the remainder of the question we will refer to this as 3-consistency. [1 mark] (g) Give an example of how a set of three variables might fail to be 3-consistent, and show how 3-consistency might then be imposed. [2 marks] (h) Suggest a modified version of the AC-3 algorithm that can be used to enforce 3-consistency. [4 marks] 3 (TURN OVER) CST.2014.4.4 3 Computer Graphics and Image Processing Given a sequence of points (Vi) n i=0 on a plane, consider the problem of interpolating a smooth curve through all of the points in order by constructing a sequence of polynomial parametric functions, one for each interval [Vi , Vi+1] n1 i=0 . (a) What is meant by Ck continuity at the junction between two curve segments? [2 marks] (b) Explain how the degree of the polynomial function for a curve segment constrains the continuity at its two ends. What continuity can be achieved at each end of a cubic segment? [4 marks] (c) Derive a cubic parametric function for the interval [Vi , Vi+1] where 0 < i < n1. [10 marks] (d) What special provision would have to be made for the segments [V0, V1] and [Vn1, Vn]? [4 marks] 4 CST.2014.4.5 4 Computer Graphics and Image Processing Given a model of a scene represented as a set of triangles in three-dimensional space defining its surfaces, consider the problem of rendering it on a raster display. Write brief notes on: (a) the data that would be stored for each triangle; [2 marks] (b) perspective projection from an arbitrary viewpoint; [5 marks] (c) clipping the data to a suitable viewing frustrum; [5 marks] (d) identifying pixels on the screen within a triangle; [3 marks] (e) resolving hidden surfaces using a z -buffer. [5 marks] 5 (TURN OVER) CST.2014.4.6 5 Databases Suppose that an Entity-Relationship model has been constructed that contains two entities S(A, B) and T(C, Amount), where A, B, C and Amount are attributes and the underline indicates a key. Suppose that we also have a many-to-many relationship R between S and T. We might expect that this model would be implemented in a relational schema such as S(A, B), T(C, Amount), and R(A, C). However, the database implementor has noticed that a very common and expensive query is this: given an A-value a, find the sum of all Amount values for records in T related to this a value in S. Therefore, the implementor has decided to "optimise" the database and replace table S with S 0 having schema S 0 (A, B, Sum), where the records in table S 0 will contain the precomputed values for this query. In this way the common and expensive query can be answered by a single key-based read. (Note: Sum should be 0 if no matching records exist.) (a) Explain how the operation insert (a, b) into S can be correctly implemented in the {S 0 , R, T} database. [4 marks] (b) Explain how the operation insert (c, v) into T can be correctly implemented in the {S 0 , R, T} database. [4 marks] (c) Explain how the operation insert (a, c) into R can be correctly implemented in the {S 0 , R, T} database. [4 marks] (d) For an OLTP database, discuss the performance implications of this so-called optimisation. [4 marks] (e) This example illustrates a fundamental trade-off in the design and implementation of database applications. Discuss. [4 marks] 6 CST.2014.4.7 6 Databases (a) We are given a relational schema R(A, B, C, D, E) and told that the following table represents a legal instance of R. A B C D E tuple number 1 2 5 4 3 (#1) 1 4 5 4 4 (#2) 2 4 5 4 5 (#3) 2 5 5 4 3 (#4) Which of the following sets of functional dependencies may hold in R? If a set of dependencies cannot hold, then explain why. You can refer to tuple numbers in your explanation. (i) F1 is the set {A D}. [2 marks] (ii) F2 is the set A, B C E B D, E A [2 marks] (iii) F3 is the set A, B C D, E C A D [4 marks] (b) We are given a relational schema R(Z, W, Y). Suppose that in some (correct) instance of R the query (Z,W(R) Z,Y(R)) R is not empty. What can we conclude about the functional dependency Z W? Explain your answer. [4 marks] (c) In the process of using functional dependencies to normalise a schema, what is meant by a lossless join decomposition and how is such a decomposition guaranteed? [4 marks] (d) In schema normalisation, is Boyce-Codd Normal Form (BCNF) always to be preferred over 3rd Normal Form (3NF)? Explain your answer. [4 marks] 7 (TURN OVER) CST.2014.4.8 7 Economics, Law and Ethics (a) Describe the provisions of the Data Protection Act. [8 marks] (b) You are designing and are about to launch a mobile phone app which will seek to understand the emotional condition of the user, using multiple inputs such as motion sensing, facial expression recognition, voice stress measurement and the analysis of entered text. Its declared purpose is to enable services to interact more empathically with users. You propose to monetize it by serving ads at times when the user is more likely to buy. Your "backers" have raised a concern that this app will be able to diagnose depression, and that in consequence you may be storing substantial amounts of sensitive personal information. Discuss this problem from the viewpoints of both data protection law and ethics. [12 marks] 8 CST.2014.4.9 8 Security I (a) Windows implements static inheritance for the access-control lists of NTFS files and folders. (i) What does static inheritance mean here and how does it differ from dynamic inheritance? [4 marks] (ii) Five flag bits (ci,oi,np,io,i) in each NTFS access-control entry (ACE) manage how it is inherited. Briefly describe the purpose of each bit. [5 marks] (iii) User mike gives his folder project the following access-control list: project AllowAccess mike: full-access (oi,ci) AllowAccess alice: read-execute (ci,np) AllowAccess bob: read-only (oi) It contains one folder and two text files, none of which have any noninherited access-control entries: project\doc.txt project\src project\src\main.c For each of these three objects, list all inherited access-control entries, showing in parentheses the inheritance-control flag bits that are set (using the same notation as above). [5 marks] (b) Describe the purpose and four typical functions of a root kit. [6 marks] 9 (TURN OVER) CST.2014.4.10 9 Security I Block ciphers usually process 64 or 128-bit blocks at a time. To illustrate how their modes of operation work, we can use instead a pseudo-random permutation that operates on the 26 letters of the English alphabet: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 m A B C D E F G H I J K L M N O P Q R S T U V W X Y Z EK(m) P K X C Y W R S E J U D G O Z A T N M V F H L I B Q As the XOR operation is not defined on the set {A, . . . , Z}, we replace it here during encryption with modulo-26 addition (e.g., C D = F and Y C = A). (a) Encrypt the plaintext "TRIPOS" using: (i) electronic codebook mode; [2 marks] (ii) cipher-block chaining (using IV c0 = K); [4 marks] (iii) output feedback mode (using IV c0 = K). [4 marks] (b) Decrypt the ciphertext "BSMILVO" using cipher-block chaining. What operation should replace XOR? [4 marks] (c) Your opponent is allowed to send you two plaintext messages M0 and M1, each n letters long. You now pick a new private key K, resulting in a new pseudo-random permutation EK : {A, . . . , Z} {A, . . . , Z}. You also pick uniformly at random a private bit b {0, 1} and return a ciphertext C = c0c1 . . . cn, namely the message Mb encrypted with cipher-block chaining using the fresh EK. Finally, your opponent has to guess your bit b. Approximately how large must n be at least for your opponent to have a greater than 75% chance of guessing b correctly? Outline a strategy that your opponent can use to achieve this.

In the context of multithreaded algorithms, define work and span, and state the work law and the span law. [3 marks] (b) Prove that the performance of a greedy scheduler is optimal to within a factor of 2. (Proving all intermediate theorems is not required if you state them correctly.) [4 marks] (c) Version A of a multithreaded algorithm takes 500 seconds on a uniprocessor machine and 50 seconds on a 32-processor machine. Version B takes the same time as A on a single processor but only 24 seconds on the 32-processor machine. (i) Define the parallelism of a computation and compute the parallelism of algorithms A and B. Which of the two has higher parallelism, and by how much? (Hint: use one of the greedy scheduler theorems to derive an approximation for one of the unknowns.) [6 marks] (ii) Estimate the running times of algorithms A and B on a 4-processor and on a 1024-processor machine, explaining how you obtain them. [3 marks] (iii) Sketch possible computation DAGs for algorithms A and B and use them to discuss the results obtained. As the number of processors in the host machine varies, is A or B faster? [4 marks] 2 CST.2014.3.3 2 Algorithms II (a) Consider van Emde Boas (vEB) trees. (i) On its own page for legibility, draw the smallest vEB tree storing keys 0, 3, 6, 7. The correctness of the structure and the accuracy of all fields of all nodes are important. Once done, write each of the keys under the cluster in which it is logically stored. [8 marks] (ii) vEB trees store the minimum and maximum key of a subtree in the root node, but do not store the minimum key in any of the descendent clusters. Explain all the reasons why this provides a performance advantage compared with proto-vEB trees. [4 marks] (b) Consider proto-vEB trees. The following pseudocode attempts to implement a method to delete a key from a proto-vEB node. Give a clear explanation of the strategy that it uses.