Managing Uncertain Data Anish Das Sarma Stanford University

Managing Uncertain Data Anish Das Sarma Stanford University 04 June 2021 Anish Das Sarma 1

What is Uncertain Data? (Certain) Data Uncertain Data Temperature is 74. 634589 F Sensor reported 75 ± 0. 5 F Bob works for Yahoo Bob works for either Yahoo or Microsoft Mary sighted a Finch Mary sighted either a Finch (80%) or a Sparrow (20%) It will rain in Stanford tomorrow There is a 60% chance of rain in Stanford tomorrow Yahoo stocks will be at 100 in a month Yahoo stock will be between 60 and 120 in a month John’s age is 23 John’s age is in [20, 30] 04 June 2021 Anish Das Sarma 2

Why Does It Arise? (Certain) Data Uncertain Data Temperature is 74. 634589 F Sensor reported 75 ± 0. 5 F Precision of devices Bob works for Yahoo Bob works for either Yahoo or Microsoft Lack of information Mary sighted a Finch Mary sighted either a Finch (80%) or a Sparrow (20%) It will rain in Stanford tomorrow There is a 60% chance of rain in Stanford tomorrow Yahoo stocks will be at 100 in a month Yahoo stock will be between 60 and 120 in a month John’s age is 23 John’s age is in [20, 30] 04 June 2021 Anish Das Sarma Uncertainty about the future Anonymization 3

Applications: Information Extraction Restaurant Zip Hard Rock Cafe 4 Anish Das Sarma 94111 94133 94109 04 June 2021

Applications: Information Integration name, h. Phone, o. Phone, h. Addr, o. Addr name, phone, address Combined View 5 Anish Das Sarma 04 June 2021

Applications: Deduplication Name John Doe J. Doe 6 ? Anish Das Sarma 80% match 04 June 2021

Applications: Scientific & Medical Experiments Probably not cancer 7 Anish Das Sarma 04 June 2021

How Do Database Management Systems (DBMS) Handle Uncertainty? They don’t 04 June 2021 Anish Das Sarma 8

What Do (Most) Applications Do? • Clean: turn into data that DBMSs can handle Observer Bird-1 Mary Finch: 80% Sparrow: 20% Finch Susan Dove: 70% Sparrow: 30% Dove Jane Hummingbird: 65% Sparrow: 35% Hummingbird (1) Loss of information (2) Errors compound insidiously 04 June 2021 Anish Das Sarma 9

Outline of The Talk • Part 1: Managing Uncertainty in a DBMS theory systems • Part 2: Handling Uncertainty in Data Integration systems theory • Other Research (trailer) • Future Plans 04 June 2021 Anish Das Sarma 10

Part 1: Managing Uncertain Data • Primarily in the context of the Trio project 1) Data 2) Uncertainty 3) Lineage • Today’s focus: how lineage helps 04 June 2021 Anish Das Sarma 11

Uncertain Data • An uncertain database represents a set of possible instances (or, possible worlds) Uncertain Data Sensor reported 75 ± 0. 5 F Bob works for either Yahoo or Microsoft Mary sighted either a Finch (80%) or a Sparrow (20%) There is a 60% chance of rain in Stanford tomorrow • Our work: finite sets of possible instances 04 June 2021 Anish Das Sarma 12

Representing Uncertain Data • 20+ years of work (mostly theoretical) • Appears to be fundamental trade-off between expressiveness & intuitiveness • We spent some time exploring the space of models for uncertainty 04 June 2021 Anish Das Sarma 13

Hierarchy of Models [ICDE 06] + Expressive - Complex + Intuitive - Inexpressive R relations A or-sets Next maybe-tuples ? M 1. Consider a model 2. Isolate inexpressiveness 2 2 -clauses 3. Solve problem with. Fulllineage propositional prop logic sets tuple-sets 04 June 2021 Anish Das Sarma 14

Running Example: Crime-Solver • Saw (witness, color, car) // may be uncertain • Drives (person, color, car) // may be uncertain • Suspects (person) = πperson(Saw ⋈ Drives) 04 June 2021 Anish Das Sarma 15

Simple Model M 1. Alternatives: uncertainty about value 2. ‘? ’ (Maybe) Annotations Saw (witness, color, car) Amy red, Honda ∥ red, Toyota ∥ orange, Mazda Three possible instances 04 June 2021 Anish Das Sarma 16

Simple Model M 1. Alternatives 2. ‘? ’ (Maybe): uncertainty about presence Saw (witness, color, car) Amy red, Honda ∥ red, Toyota ∥ orange, Mazda Betty blue, Acura ? Six possible instances 04 June 2021 Anish Das Sarma 17

Review: Relational Queries D Q Saw (witness, color, car) Amy, red, Honda πperson(σcolor=red) S W (witness) Amy Betty, blue, Acura 04 June 2021 Anish Das Sarma 18

Queries on Uncertain Data D direct implementation D′ possible instances I 1, I 2, …, In rep. of instances Q on each instance Closure: up-arrow always exists J 1, J 2, …, Jm Completeness: All sets of possible instances can be represented 04 June 2021 Anish Das Sarma 19

Model M is Not Closed Saw (witness, car) Cathy Honda ∥ Mazda Drives (person, car) Jimmy, Toyota ∥ Jimmy, Mazda Billy, Honda ∥ Frank, Honda Hank, Honda Suspects = πperson(Saw ⋈ Drives) Suspects Jimmy Billy ∥ Frank Hank 04 June 2021 ? ? ? CANNOT Does not correctly capture possible instances in the result Anish Das Sarma 20

Lineage to the Rescue Model M + Lineage = Completeness 04 June 2021 Anish Das Sarma 21

Example with Lineage ID 11 Saw (witness, car) Cathy Honda ∥ Mazda ID Drives (person, car) 21 Jimmy, Toyota ∥ Jimmy, Mazda 22 Billy, Honda ∥ Frank, Honda 23 Hank, Honda Suspects = πperson(Saw ⋈ Drives) 04 June 2021 ID Suspects 31 Jimmy 32 Billy ∥ Frank 33 Hank ? ? ? Anish Das Sarma 22

Example with Lineage ID 11 Saw (witness, car) Cathy Honda ∥ Mazda ID Drives (person, car) 21 Jimmy, Toyota ∥ Jimmy, Mazda 22 Billy, Honda ∥ Frank, Honda 23 Hank, Honda Suspects = πperson(Saw ⋈ Drives) ID Suspects 31 Jimmy 32 Billy ∥ Frank 33 Hank ? ? ? λ(31) = (11, 2) Λ (21, 2) λ(32, 1) = (11, 1) Λ (22, 1); λ(32, 2) = (11, 1) Λ (22, 2) λ(33) = (11, 1) Λ 23 Correctly captures possible instances in the result 23

Trio’s Data Model Uncertainty-Lineage Databases (ULDBs) 1. 2. 3. 4. Alternatives ‘? ’ (Maybe) Annotations Confidence values (next) Lineage Theorem: ULDBs are closed and complete [VLDB 06] Formally studied properties like minimization, equivalence, approximation and membership. [VLDB 06, VLDB J. 08] 04 June 2021 Anish Das Sarma 24

Confidence Values in Trio • Confidence values supplied with base data – Default probabilistic interpretation • Problem: Compute confidence values on result data [ICDE 08] • 5 -minute DBClip – Search “confidence computation” on You. Tube. 04 June 2021 Anish Das Sarma 25

Problem Description ID Saw (witness, car) 11 (Amy, Honda) : 0. 5 12 (Betty, Acura) : 0. 6 Cars = πcar(Saw ID 04 June 2021 ID Drives (person, car) 21 (Jimmy, Honda) : 0. 9 22 (Billy, Honda) : 0. 8 23 (Hank, Acura) : 1. 0 ⋈ Drives) Cars 41 Honda : ? 42 Acura : ? Anish Das Sarma 26

Operator-by-Operator ID Saw (witness, car) 11 (Amy, Honda) : 0. 5 12 (Betty, Acura) : 0. 6 Saw ID Drives (person, car) 21 (Jimmy, Honda) : 0. 9 22 (Billy, Honda) : 0. 8 23 (Hank, Acura) : 1. 0 Drives 31 ⋈ πcar 04 June 2021 (Amy, Jimmy, Honda) : 0. 45 0. 5*0. 9 (Amy, Billy, Honda) : 0. 4 32 (Betty, Hank, Acura) : 0. 6 33 ID Cars Wrong!! 41 Honda : 0. 45 0. 67 + 0. 4 - (0. 45*0. 4) 42 Acura Anish Das Sarma 27

Operator-by-Operator ID Saw (witness, car) 11 (Amy, Honda) : 0. 5 12 (Betty, Acura) : 0. 6 pe de t in o N n n de t! ID Drives (person, car) 21 (Jimmy, Honda) : 0. 9 22 (Billy, Honda) : 0. 8 23 (Hank, Acura) : 1. 0 31 32 (Amy, Jimmy, Honda) : 0. 45 (Amy, Billy, Honda) : 0. 4 33 (Betty, Hank, Acura) : 0. 6 ID 04 June 2021 Cars 41 Honda 0. 45 + 0. 4 - (0. 45*0. 4) 42 Acura Anish Das Sarma 28

Database Query Processing 101 Execution Plans Query Pick and execute best plan Q Statistics, indexes 04 June 2021 Anish Das Sarma 29

Operator-by-Operator Confidence Computation Plans Query Can be much smaller or empty Q 04 June 2021 Anish Das Sarma 30

Decouple Data and Confidence Computation Plans 1. Compute data 2. Use lineage to compute confidences (on demand) Query Q Theorem: Arbitrary improvement. [ICDE 08] 04 June 2021 Anish Das Sarma 31

Our Approach ID Saw (witness, car) 11 (Amy, Honda) : 0. 5 12 (Betty, Acura) : 0. 6 ID Drives (person, car) 21 (Jimmy, Honda) : 0. 9 22 (Billy, Honda) : 0. 8 23 (Hank, Acura) : 1. 0 Correct!! ID Cars 41 ? Honda : 0. 49 42 Acura : 0. 6 ? 04 June 2021 λ(41) = 11 Λ (21 V 22) λ(42) = 12 Λ 23 Anish Das Sarma 0. 5 * (0. 9 + 0. 8 - 0. 9*0. 8) 32

Algorithm 0. 9 0. 7 1. 0 t 5 t 6 t 4 0. 4 t 7 0. 4 1. Expand lineage to base data 2. Get confidence of base data 3. Evaluate the probability λ(t) Detecting independence t 1 t 2 Memoization Batch computation R 04 June 2021 t λ(t) = f(t 4, t 5, t 6, t 7) 0. 823 Anish Das Sarma 33

Some Other Trio Work Modifications and Versioning [TR 08] -Stored derived relations -Modifications versions Indexes and Statistics [MUD 08] -Specialized indexes, histograms Functional Dependencies & Schema Design [TR 07] -Definitions, sound and complete axiomatization of FDs -Lossless decomposition -FD testing, finding, and inference 04 June 2021 Anish Das Sarma 34

Related Work (sample) • Modeling Uncertainty: Plenty, covered in textbooks • Systems: Avatar, Bayes. Store, May. BMS, MYSTIQ, ORION, Pr. DB, Prob. View, Trio, others? 04 June 2021 Anish Das Sarma 35

Part 2: Data Integration • Reboot! or, wake up! 04 June 2021 Anish Das Sarma 36

Traditional Data Integration: Setup Mapping SELECT P. title AS title, A. name AS author, NULL AS conf, P. year AS year, FROM Author AS A, Paper AS P, Authored. By AS B WHERE A. aid=B. aid AND P. pid=B. pid Who authored the most SIGMOD papers in the 90’s? Publication(title, author, conf, year) 1. Mediated Schema 2. Schema Mappings 3. Query Answering Mediated Schema nt a c i nif nt g i S -fro up fort ef Mike Carey D 1 D 5 D 2 Author(aid, name) Paper(pid, title, year) Authored. By(aid, pid) D 3 D 4 Bib(title, authors, conf, year) 37

“Pay-As-You-Go” Data Integration 1. Automated best-effort integration from the outset 2. Further improve the system over time with feedback How advanced a starting point can we provide? 04 June 2021 Anish Das Sarma 38

Uncertainty to the Rescue >90% accuracy in automatically integrating 50 -800 data sources for several domains [SIGMOD 08] • Automatic integration ÞMake guesses ÞModel probabilities • Specifically – Probabilistic schema mappings – Probabilistic mediated-schema 04 June 2021 Anish Das Sarma 39

Next 1. Probabilistic mediated schemas 2. Probabilistic schema mappings 3. Experimental results 04 June 2021 Anish Das Sarma 40

Mediated Schema {name, person-name} {email} {phone-num, phone} {address, mailing-addr} Med-S (name, email, phone, addr) S 1(name, email, phone-num, address) n S 2(person-name, phone, mailing-addr) A mediated schema is a clustering of a subset of the set of all attributes appearing in source schemas. 04 June 2021 Anish Das Sarma 41

Example Med 1 ({name}, {phone, h. Phone, o. Phone}, {address, h. Addr, o. Addr}) ? S 1(name, h. Phone, o. Phone, h. Addr, o. Addr) Q: SELECT name, h. Phone, o. Phone FROM Med S 2(name, phone, address) 42

Example Med 1 ({name}, {phone, h. Phone, o. Phone}, {address, h. Addr, o. Addr}) Med 2 ({name}, {phone, h. Phone}, {o. Phone}, {address, o. Addr}, {h. Addr}) S 1(name, h. Phone, o. Phone, h. Addr, o. Addr) Q: SELECT name, phone, address FROM Med S 2(name, phone, address) 43

Example Med 1 ({name}, {phone, h. Phone, o. Phone}, {address, h. Addr, o. Addr}) Med 2 ({name}, {phone, h. Phone}, {o. Phone}, {address, o. Addr}, {h. Addr}) Med 3 ({name}, {phone, h. Phone}, {o. Phone}, {address, h. Addr}, {o. Addr}) S 1(name, h. Phone, o. Phone, h. Addr, o. Addr) Q: SELECT name, phone, address FROM Med S 2(name, phone, address) 44

Example Med 1 ({name}, {phone, h. Phone, o. Phone}, {address, h. Addr, o. Addr}) Med 2 ({name}, {phone, h. Phone}, {o. Phone}, {address, o. Addr}, {h. Addr}) Med 3 ({name}, {phone, h. Phone}, {o. Phone}, {address, h. Addr}, {o. Addr}) Med 4 ({name}, {phone, o. Phone}, {h. Phone}, {address, o. Addr}, {h. Addr}) S 1(name, h. Phone, o. Phone, h. Addr, o. Addr) Q: SELECT name, phone, address FROM Med S 2(name, phone, address) 45

Example Med 1 ({name}, {phone, h. Phone, o. Phone}, {address, h. Addr, o. Addr}) Med 2 ({name}, {phone, h. Phone}, {o. Phone}, {address, o. Addr}, {h. Addr}) Med 3 ({name}, {phone, h. Phone}, {o. Phone}, {address, h. Addr}, {o. Addr}) Med 4 ({name}, {phone, o. Phone}, {h. Phone}, {address, o. Addr}, {h. Addr}) Med 5 ({name}, {phone}, {h. Phone}, {o. Phone}, {address}, {h. Addr}, {o. Addr}) S 1(name, h. Phone, o. Phone, h. Addr, o. Addr) Q: SELECT name, phone, address FROM Med S 2(name, phone, address) 46

Example Med 1 ({name}, {phone, h. Phone, o. Phone}, {address, h. Addr, o. Addr}) Med 2 ({name}, {phone, h. Phone}, {o. Phone}, {address, o. Addr}, {h. Addr}) Med 3 ({name}, {phone, h. Phone}, {o. Phone}, {address, h. Addr}, {o. Addr}) Med 4 ({name}, {phone, o. Phone}, {h. Phone}, {address, o. Addr}, {h. Addr}) Med 5 ({name}, {phone}, {h. Phone}, {o. Phone}, {address}, {h. Addr}, {o. Addr}) S 1(name, h. Phone, o. Phone, h. Addr, o. Addr) Q: SELECT name, phone, address FROM Med S 2(name, phone, address) 47

Probabilistic Mediated Schema Med 3 ({name}, {phone, h. Phone}, {o. Phone}, {address, h. Addr}, {o. Addr}) Pr=0. 5 Med 4 ({name}, {phone, o. Phone}, {h. Phone}, {address, o. Addr}, {h. Addr}) Pr=0. 5 S 1(name, h. Phone, o. Phone, h. Addr, o. Addr) S 2(name, phone, address) • Probabilistic Mediated Schema (p-med-schema) is a set M = {(M 1, Pr(M 1)), …, (Mk, Pr(Mk))} where • Mi is a med-schema; i≠j => Mi≠ Mj • Pr(Mi)ϵ(0, 1]; ΣPr(Mi) = 1 04 June 2021 Anish Das Sarma 48

P-Mappings PM 1 PM 2 Med 3 (name, h. PP, o. P, h. AA, o. A) S 1(name, h. P, o. P, h. A, o. A) Pr=. 64 S 1(name, h. P, o. P, h. A, o. A) Pr=. 16 Med 3 (name, h. PP, o. P, h. AA, o. A) S 1(name, h. P, o. P, h. A, o. A) Pr=. 16 S 1(name, h. P, o. P, h. A, o. A) Pr=. 04 Med 4 (name, o. PP, h. P, o. AA, h. A) S 1(name, h. P, o. P, h. A, o. A) Pr=. 64 S 1(name, h. P, o. P, h. A, o. A) Pr=. 16 Med 4 (name, o. PP, h. P, o. AA, h. A) S 1(name, h. P, o. P, h. A, o. A) Pr=. 16 S 1(name, h. P, o. P, h. A, o. A) Pr=. 04 04 June 2021 Anish Das Sarma 49

Expressive Power of P-Med-Schema & P-Mapping Theorem 1. For one-to-many mappings: (p-med-schema + p-mappings) = (mediated schema + p-mapping) > (p-med-schema + mappings) Theorem 2. When restricted to one-to-one mappings: (p-med-schema + p-mappings) = (p-med-schema + mappings) > (mediated schema + p-mapping) 04 June 2021 Anish Das Sarma 50

Next • • • Creating p-med-schemas (briefly) Creating p-mappings (briefly) Experimental Results 04 June 2021 Anish Das Sarma 51

P-med-schema Creation 1. Certain/uncertain edges name address 1 . 6 S 1 . 6 email-address. 2 S 2 pname home-address 52 04 June 2021

P-med-schema Creation 2. Clustering S 1 name email-address S 2 pname S 1 address name S 2 pname home-address email-address home-address 53

P-med-schema Creation 3. Assign probabilities Pr=1/6 S 1 name address email-address S 2 pname home-address Pr=1/3 S 1 name S 2 pname address email-address home-address Pr=1/6 name S 1 email-address S 2 pname Pr=1/3 S 1 address name S 2 pname home-address email-address home-address 54

P-mapping Creation Goal: find a p-mapping that is consistent with a set of weighted correspondences S=(num, pname, home-addr, office-addr) 0. 2 0. 8 0. 9 T=(name, mailing-addr) Theorem: There exists a p-mapping consistent if and only if for every source/target attribute a, the sum of the weights of all correspondences that involve a is at most 1. 55

Experiments n Data: tables extracted from HTML tables on the web Domain #Sources Movie 161 movie, year Car 817 make, model People 49 job/title, organization/company/employer Course 647 course/class, instructor/teacher/lecturer, subject/department/title Bib 649 author, title, year, journal/conference 04 June 2021 Search Keywords Anish Das Sarma 56

Experiments • Gold standard: manual Approximate standard: semi-automatic • Precision, recall, F-measure for several SQL queries varying attributes, selectivities 57

Quality of Query Answering Domain Precision Recall F-measure Golden Standard People 1 . 849 . 918 Course 1 . 852 . 92 Approximate Golden Standard Movie . 95 1 . 924 Car 1 . 917 . 957 People . 958 . 984 . 971 Course 1 1 1 Bib 1 . 955 . 977 58

Comparison with Other Approaches We obtained highest F-measure in all domains. Keyword search obtained low precision and low recall. Querying the sources directly or considering only the highest probability mapping obtained low recall. 59

Comparison with Other Mediated-Schema Generation Methods Using p-medschema obtained highest F-measure in all domains. 60

System Setup Time (one domain) 61

Brief Related Work • Approximate schema mappings [Magnani et. al. 2007], [Gal 2007], [Dong. et. al. 2007] • Automatic generation of mediated schemas [He et. al. 2003], • More (see paper) 04 June 2021 Anish Das Sarma 62

Finally… • Other Research – Data Integration (2) – Deduplication (2) – Quality Estimation of Sensor/RFID Streams [IQIS 06] • Future Plans 04 June 2021 Anish Das Sarma 63

Data Integration Problem: Foundations for integration of uncertain data Solution [TR 08]: -Define open- and closed-containment for uncertain data -Algorithms, complexity of consistency checking and finding maximally-correct query answers Problem: Dependencies in web-data integration (e. g. , deep-web, plagiarism) Solution [TR 08]: Algorithms, complexity of fundamental problems: Coverage estimation, cost minimization and coverage maximization, and source ordering 04 June 2021 Anish Das Sarma 64

Deduplication [SIGMOD 07] -Leveraging real-world constraints for deduplication -Tractable optimal solution and experiments over DBLP and ACM publication data [WWW 07] -Detecting near-duplicate web-pages for crawling -Efficient indexing scheme supporting crawling speeds over web-scale data 04 June 2021 Anish Das Sarma 65

Future Work Short & Medium-Term 1. View management over uncertain databases: materialized view updates, versioning, partial materialization, … 2. More applications of uncertain data 3. More on lineage: internal/external lineage, approximate lineage, uncertain lineage, … 04 June 2021 Anish Das Sarma 66

Future Work Long-term 1. Applying uncertainty to other data management problems: query optimization? cloud computing? 2. Improve quality of data through conflict resolution and feedback 3. Web-data management: Handling huge amounts of data that is conflicting, uncertain, redundant, dependent, … 04 June 2021 Anish Das Sarma 67

Thanks! Anish Das Sarma anish@cs. stanford. edu http: //i. stanford. edu/~anishds (or search “Anish Das Sarma”) 04 June 2021 Anish Das Sarma 68