2. Parametric Function

Task 2 Background A major multi-national corporation has employed you to research and implement SQL statements to support their data management operations. The company consists of several departments located in the cities all over the world. The company's database also contains information about the present and past jobs of its employees and about the present managerial structure. A conceptual schema of the database is given below.

1. Basic association rule creation manually. The 'database' below has four transactions. What association rules can be found in this set, if the minimum support (i.e. coverage) is 60% and the minimum confidence (i.e. accuracy) is 80%? Trans_id Itemlist T1 {K, A, D, B} T2 {D, A C, E, B} T3 {C, A, B, E) T4 {B, A, D} Show each step of your calculation in details (i.e. item sets containing one item, two items like this), as each step will be graded individually.

2. 2.1. What is the result of executing the following XQuery? for $m in 1 to 10 let Sn = $m + 1 where $m > 4 order by $m descending return $m*$n 2.2. Using the XML Document below (library with books), define the following queries in XQuery: (a) Give the titles of all Books sorted by Price. (b) Give the sorted list of titles of all Books that have a price with a value that is higher than 70. (c) Give the list of titles of all Books published by Addison-Wesley. (d) How many books were written by Abiteboul? (e) Give for each author, the number of books he has written.

1. Defined below are three relations' schemas and their sets of functional dependencies. R₁(A, B, C, D) A → BCD C → AB R₂(E, F, G, H, I, J, K) E → FG EFG H R₁(L, M, N, O, P, R) L MN LN → OPR R → LM a) For each of the relations R₁, R₂, and R3, check which of the following are candidate keys (can be more than one for each relation, or none). R₁: A, B, C, D, AB, BC, ABC R₂: E, FG, EFG, EIJK R₁: L, N, R, LN, MR, NMR b) For each of the relations R₁, R₂, and R, determine if it is in 3NF. c) For each of the relations R₁, R2, and R3 determine if it is in BCNF.

2. Decompose relation R₂ from problem 1 into two tables R21 and R22 so that they are both in BCNF decomposition with a lossless and dependency-preserving

5. Find the workers from the birthcity that includes 'city' [20]. a. Hint: use the expression "like"

mcqs

9. Write a SELECT statement based on the tables in the third normal form from your response to Question 2. Include at least 1 join and 1 filter clause.

Creating Your First ERD There are some tricks and must do's when creating an ERD. Click the following link as we walk through some of the pertinent steps before starting your lab. Creating Your First ERDB