CSE 232 A Graduate Database Systems Arun Kumar

CSE 232 A Graduate Database Systems Arun Kumar Review Discussion 1

Review Question Rating. ID Stars Date UID MID 20 m pages Page size 8 KB; Buffer memory 8 GB; 8 B for each field SELECT COUNT(DISTINCT Date) FROM Ratings Propose an efficient physical plan and compute its I/O cost. COUNT Aggregate # buffer pages = 1 m Read NT = 4 m Pipeline COUNT with deduplication phase Hash-based Project on Date Write T to disk NT = 4 m Cannot fit hash table on T entirely; partition it Read NR = 20 m Total I/O = 28 m pages 2 Ratings

Review Question Rating. ID Stars Date UID MID 20 m pages Page size 8 KB; Buffer memory 8 GB; 8 B for each field SELECT COUNT(DISTINCT Date) FROM Ratings tered B+ tree index on Date. Propose an efficient physical plan and c COUNT Aggregate Sort-based Project on Date Index on Date (Assume Record. ID is 8 B too) Projection. List is prefix of Index. Key No GROUP BY; trivial pipelining Overall, just one scan of leaf level Leaf level size = 2 / 5 * NR = 8 m pages 3

Review Question Rating. ID Stars Date UID MID 1 b pages Example: UID Name Age MID Name Year Director 10 m pages 100 k pages Page size 8 KB; node has 256 GB RAM SELECT FROM WHERE GROUP BY M. Year, AVG(R. Stars) Ratings R, Movies M, Users U R. MID = M. MID AND R. UID = U. UID AND U. Age <= 30 M. Year Propose an efficient physical plan that does not materialize any intermediate data (fully pipelined) and compute its I/O cost. 4

Review Question SELECT FROM WHERE GROUP BY M. Year, AVG(R. Stars) Ratings R, Movies M, Users U R. MID = M. MID AND R. UID = U. UID AND U. Age <= 30 M. Year Logical Query Plan Should we rewrite the LQP? How? Why? Users Ratings Movies 5

Review Question What about this group by? LQP with right deep join tree What physical op to use for each logical op? Pipelined? Can hash tables fit in RAM? Movies 100 k pages Users 10 m pages Ratings 1 b pages Page size 8 KB = F*100 k*8 k. B < F*10 m*8 KB 256 GB RAM ~ 1. 2 GB < 120 GB Both hash tables fit in RAM together; fully pipelined join! 6

Review Question What about this group by? Can the incremental statistics of all groups fit in RAM? How many groups are there? Need not bother; definitely smaller than Movies! Movies 100 k pages = F*100 k*8 k. B ~ 1. 2 GB Users 10 m pages < F*10 m*8 KB < 120 GB Ratings 1 b pages Page size 8 KB 256 GB RAM So, hash-based group by aggregate; partitioning not needed 7

Review Question Final PQP Hash-based group by Fully pipelined! No intermediate data materialized Hash join What is the total I/O cost? Hash join Just 1 pass over each table = 1010. 1 million pages Filescan Movies 100 k pages Filescan; select Age <= 30 Users 10 m pages Filescan Ratings 1 b pages 8

Review Question Movies URL Name Year Director Movies MID MName Year Directors DID Name MID You are tasked with integrating the above two databases. Which of the following issues need not be tackled? A Information extraction B Schema alignment C Entity matching D Data cleaning 9

Review Question Perform information extraction (IE) on this piece of text to populate the given relation schema without any errors. CSE 232 A was taught by Arun Kumar in Fall 2018. It was taught by Yannis Papakonstantinou in Spring that year. Victor Vianu did not teach CSE 232 A in Fall 2018 but rather CSE 132 A. Course. Info Course Instructor Quarter Year Course CSE 232 A Instructor Arun Kumar Quarter Fall Year 2018 CSE 232 A Yannis Papakonstantinou CSE 132 A Victor Vianu Spring 2018 Fall 2018 What type of IE is the above? Closed-world? Closed? Open? 10

Review Question ema alignment approaches offer the most flexibility/ease of use for a A Global-As-View (GAV) B Local-As-View (LAV) C Manual schema alignment D All of the above 11

Review Question Full. Name Alice Liddell Age Gross. Salary 27 115, 000 Net. Salary Last. Name First. Name 80, 000 Williams Aisha S 1 MI Age R 30 Salary 120, 000 S 2 Which of the following is a mediated schema for LAV? A Full. Name B Full. Name First. Name MI Age Gross. Salary Net. Salary C Last. Name First. Name MI Age Gross. Salary Net. Salary D Full. Name First. Name MI Age Gross. Salary Net. Salary 12

Review Question Which stage of a typical entity matching workflow helps avoid comparison of all pairs of records? A Blocking B Pairwise check with ML classifier C Clustering D None of the above 13

Review Question Which attribute in this dataset surely needs data cleaning? Full. Name Age City Country Aisha Williams 30 San Diego USA Alice Liddell 27 New York City United States Anand Vishwanathan 48 Chennai India Ao Ma 42 Shanghai China A Full. Name B Age C City D Country 14