Week 3: Advanced SQL and Data Integration

Question 1

REVERSED

CREATE TABLE Students(sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa REAL)

Answer 1

How do you create a table in SQL?

Question 2

REVERSED

DROP TABLE Students

Answer 2

How do you delete an entire table including schema in SQL?

Question 3

REVERSED

DELETE FROM Students

Answer 3

How do you delete the contents of a table but keep the relation in SQL?

Question 4

REVERSED

DELETE FROM Students
WHERE …

Answer 4

How do you delete certain rows from a table in SQL?

Question 5

REVERSED

ALTER TABLE Students
ADD COLUMN firstYear integer

Answer 5

How do you add an empty column to an SQL table?

Question 6

REVERSED

ALTER TABLE Students
DROP firstYear

Answer 6

How do you delete an attribute from an SQL table?

Question 7

REVERSED

INSERT INTO Students
VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2)

INSERT INTO Students(name, Sid, login, age, gpa)
VALUES (‘Smith’, 53688, ‘smith@ee’, 18, 3.2)

Answer 7

How do you add a row into an SQL table? (2 ways depending on the order)

Question 8

REVERSED

UPDATE Students
SET grade = grade*1.2
WHERE …

Answer 8

How do you change the value in an attribute for all or some tuples?

Question 9

REVERSED

CREATE TABLE test (sid INTEGER PRIMARY KEY, name VARCHAR(30), major VARCHAR(30))

CREATE TABLE test (sid INTEGER, name VARCHAR(30), major VARCHAR(30), PRIMARY KEY (sid))

Answer 9

How do you set a primary key when creating a table in SQL? (2 ways)

Question 10

REVERSED

CREATE TABLE Works_In(ssn CHAR(11), did INTEGER, since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn)
REFERENCES Employees)

Answer 10

How do you set a foreign key when creating a table in SQL?

Question 11

REVERSED

organise a group of entity sets into a parent/child hierarchy

Answer 11

What are entity hierarchies?

Question 12

REVERSED

specify that the children of an entity do/don’t overlap

Answer 12

What are overlap constraints?

Question 13

REVERSED

instances of the children of an entity include all instances of their parent (ie cover it)

Answer 13

What are covering constraints?

Question 14

REVERSED

A view is just a relation, but we store a definition, rather than a set of tuples
Views can be used to present necessary information (or a summary) while hiding details in underlying relations

Answer 14

What is a view in SQL and what is it used for?

Question 15

REVERSED

CREATE VIEW YoungActiveStudents (name, grade)
AS SELECT S.name, E.grade
FROM Students S, Enrolled E
Where S.Sid = E.Sid and S.age <21

Answer 15

How do you create a view in SQL?

Question 16

REVERSED

DROP VIEW

Answer 16

How can you delete a view in SQL?

Question 17

REVERSED

a constraint is a relationship among data elements that the DBMS is required to enforce

Answer 17

What is an integrity constraint?

Question 18

REVERSED

Integrity constraints guard against accidental damage to the database, by ensuring that authorised changes do not result in a loss of data consistency

Answer 18

What is the purpose of integrity constraints?

Question 19

REVERSED

one that satisfies all specified ICs

Answer 19

What is a legal instance of a relation?

Question 20

REVERSED

• Primary key
• Foreign key (referential integrity)
• Value-based
• Tuple-based

Answer 20

What are the 4 types of integrity constraints?

Question 21

REVERSED

Means the primary key cannot be null

Answer 21

What is entity integrity?

Question 22

REVERSED

a key that could be the primary key but is not classified as the primary key

Answer 22

What is an alternate key?

Question 23

REVERSED

• NO ACTION - delete/update is rejected
• CASCADE - make the same changes to all tuples that refer to the updated/deleted tuple
• SET NULL / SET DEFAULT - sets foreign key value of referencing tuple to NULL or a default value

Answer 23

What are the three ways of enforcing referential integrity in SQL?

Question 24

REVERSED

CREATE TABLE Enrolled(sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY(sid,cid), FOREIGN KEY(sid) REFERNCES Students
ON DELETE CASCADE
ON UPDATE SET DEFAULT)

Answer 24

How do you set the method of enforcing referential integrity when creating an SQL table?

Question 25

# REVERSED it will store the three characters followed by 2 spaces

Answer 25

What happens if you enter 3 characters into an attribute with CHAR(5)?

Question 26

# REVERSED defines constraints on the values of a particular attributes

Answer 26

What is the check clause?

Question 27

# REVERSED CREATE TABLE section( semester VARCHAR(6) CHECK(semester IN (‘Fall’, ‘Winter’, ‘Spring’, ‘Summer’)), year NUMERIC(4,0) CHECK(year\>1990))

Answer 27

How do you add a check into an SQL table?

Question 28

# REVERSED CHECK(semester IN (‘Fall’, ‘Winter’, ‘Spring’, ‘Summer’) AND (year\>1990))

Answer 28

What is a tuple based check?

Question 29

# REVERSED CHECK(time\_slot\_id IN (SELECT time\_slot\_id FROM time\_slot))

Answer 29

What is a complex check clause?

Question 30

# REVERSED name VARCHAR(20) NOT NULL

Answer 30

How do you declare that an attribute can't be null?

Question 31

# REVERSED - 1 table for each entity - 1 table for each relationship - Each cell contains a single value - If you have big relations, decompose them

Answer 31

What is the normalisation theory for designing a good database? (4)

Question 32

# REVERSED means when you decompose a table and then join you get the original table back

Answer 32

What is lossless decomposition?

Question 33

# REVERSED let R a relation schema (with constraints). A decomposition R1, R2, … Rn of R is called lossless iff. for each valid relation (instance) r(R): r = piR1(r) join piR2(r) join …

Answer 33

What is the official definition of a lossless decomposition?

Question 34

# REVERSED Attribute(R1) UNION Attribute(R2) = Attribute(R) Attribute(R1) INTERSECTION Attribute(R2) is not empty Attribute(R1) INTERSECTION Attribute(R2) -\> Attribute(R1) or Attribute(R1) INTERSECTION Attribute(R2) -\> Attribute(R2)

Answer 34

What are the 3 rules to identify lossless decomposition?

Question 35

# REVERSED - the values of a set of attributes X determine the values of another set of attributes Y - Denoted by X -\> Y (X determines Y)

Answer 35

What is functional dependence?

Question 36

# REVERSED K is a superkey for relation R iff. K -\> R

Answer 36

What is a superkey in terms of functional dependence?

Question 37

# REVERSED K is a candidate key for R iff. K -\> R AND for any X proper subset of K, X -/-\> R

Answer 37

What is a candidate key in terms of functional dependence?

Question 38

# REVERSED functional dependencies allow us to express constraints that cannot be expressed using superkeys

Answer 38

What is the purpose of functional dependencies?

Question 39

# REVERSED The set of functional dependencies logically implied by F is the closure of F, denoted F+ ie. to find the closure B+, find all the attributes that can be determined starting with B

Answer 39

What is the closure of a set of functional dependencies?

Question 40

# REVERSED - If X subset of Y, then Y -\> X (reflexivity) - If X -\> Y, then AX -\> AY (augmentation) (A is a set of attributes) - If X -\> Y and Y -\> W, then X -\> W (transitivity)

Answer 40

What are Armstrongs axioms? (3)

Question 41

# REVERSED The minimal set of attributes for which their closure is all the set of attributes in a relation

Answer 41

What is a candidate key in terms of closure?

Question 42

# REVERSED if X -\> Y and X -\> W, then X -\> YW if X -\> YW, then X -\> Y and X -\> W if X -\> Y and WY -\> Z, then XW -\> Z If X is a candidate key, then X -\> Y for all Y

Answer 42

What functional dependencies can be derived from armstrongs axoims? (4)

Question 43

# REVERSED For relational schema R(XYZ), the following holds: If X -\> Y then the decomposition R1(XY) R2(XZ) is lossless.

Answer 43

How do you check if a decomposition is lossless using functional dependencies?

Question 44

# REVERSED A functional dependency that holds only under some contraint | ([country = UK, zip] -\> street)

Answer 44

What is a conditional functional dependency? How is it expressed?

Question 45

# REVERSED - personalisation: content adapted to user - Customisation: structure adapted to user

Answer 45

What are personalisation and customisation in terms of data?

Question 46

# REVERSED - a set of local databases, each with a local schema and a local instance - A global integrated schema - A set of mappings between the global and local schema

Answer 46

What is an information integration system

Question 47

# REVERSED -leave the data in the sources -When a query comes in: \* Determine their relevant sources to the query \* Break down the query into sub-queries for the sources \* Get the answers from the sources, filter them if needed and combine them appropriately

Answer 47

what is visual integration architecture / on demand integration?

Question 48

# REVERSED -extension of jaro-similarity that gives higher weight to matching prefix Jw(S1, S2) = JaroSim + P\*L\*(1-JaroSim) - P is a scaling factor, 0.1 by default - L is length of common prefix up to max 4

Answer 48

What is jaro-winkler similarity?

Question 49

# REVERSED Text variations, local knowledge, evolving nature of data, new functionalities

Answer 49

What are reasons for discrepancies between entities? (4)

Question 50

# REVERSED identify the different structures/records that model the same real-world object

Answer 50

What is entity resolution?

Question 51

# REVERSED improves data quality and integrity, fosters re-use of existing data sources, optimises space

Answer 51

Why is entity resolution useful? (3)

Question 52

# REVERSED - number of operations to convert from 1st string to 2nd string - delete and insert character with cost 1 - substitute character with cost 2

Answer 52

What is the levenshtein edit distance?

Question 53

# REVERSED - overcome limitation of edit distance with shortened strings (abbreviations) - Considered two extra operations: open gap and extend gap (with small cost) Cost = insert + open + extend

Answer 53

What is the gap distance?

Question 54

# REVERSED JaroSim(S1, S2) = (1/3)\*(C/|S1| + C/|S2| + (C-T)/C) C=common/matching characters in S1 and S2 T=transpositions/2 (transposition=matching but different sequence order) characters are considered matching when they are the same and not further than [max(|S1|, |S2|)/2]-1 apart

Answer 54

What is jaro-similarity?

Question 55

# REVERSED Represent document as a set of its k-shingles Use jaccard set similarity

Answer 55

What is a similarity metric for documents?

Question 56

# REVERSED - algorithm that indexes names by their sounds when pronounced in English - consists of the first letter of the name followed by three numbers - Remove all W, H - B,F,P,V encoded as 1 - C,G,J,K,Q,S,X,Z encoded as 2 - D,T encoded as 3 - L encoded as 4 - M,N encoded as 5 - R encoded as 6 - Remove vowels

Answer 56

What is the phonetic algorithm/sound encoding algorithm?

Question 57

# REVERSED -The Jaccard similarity of two sets is the size of their intersection divided by the size of their union: sim(C1,C2) = |C1 intersection C2| / |C1 union C2|

Answer 57

What is jaccard similarity?

Question 58

# REVERSED d(C1,C2) = 1 - sim(C1,C2)

Answer 58

What is jaccard distance?

Question 59

# REVERSED Jaccard similarity but counts repetition of elements Max is 0.5

Answer 59

What is jaccard bag similarity?

Question 60

# REVERSED -a sequence of k-tokens (characters or words) that appear in the document E.g. for document D1 = abcab, Set of 2-shingles S(D1) = {ab, bc, ca}

Answer 60

What is a k-shingle / k-gram?

Question 61

# REVERSED 1. Shingling: convert documents to sets 2. Min-hashing: convert large sets to short signatures, while preserving similarity 3. Locality-sensitive hashing: focus on pairs of signatures likely to be from similar documents (candidate pairs)

Answer 61

What are the 3 steps for finding similar documents?

Question 62

# REVERSED - get set of shingles - represent as a matrix where rows are shingles and columns are documents - matrix has 1 if the shingle is in the document

Answer 62

What is the process of shingling?

Question 63

# REVERSED no. of rows where both columns are 1 / no. of rows where either column is 1

Answer 63

How do you find the similarity of two sets(documents) from the shingle matrix?

Question 64

# REVERSED find similar columns while computing small signatures

Answer 64

What is the goal of hashing?

Question 65

# REVERSED “hash”each column C to a small signature h(C), such that: 1. H(C) is small enough that the signature fits in RAM 2. sim(C1,C2) is the same as the “similarity” of signatures h(C1) and h(C2)

Answer 65

What is the key idea of hashing?

Question 66

# REVERSED - start with a permutation for each row of the boolean matrix - get the first occurring row in the permuted order that correlates to a 1 in the document and store this as the new column value in the Signature matrix - repeat for many permutations

Answer 66

How do you perform min-hashing?

Question 67

# REVERSED Define a minhash function h\_pi(C) = the index of the first(in the permuted order pi) row which column C has value 1

Answer 67

How do you define a minhash function?

Question 68

# REVERSED Pr[h\_pi(C1) = h\_pi(C2)] = sim(C1, C2) the probability that the minhash function for a random permutation of rows produces the same value for two sets equals the Jaccard similarity of those sets

Answer 68

What is the property of the minhash function?

Question 69

# REVERSED the fraction of the hash functions in which they agree (for which signatures have the same value)

Answer 69

What is the similarity of two signatures?

Question 70

# REVERSED Smaller expected error

Answer 70

What happens as the signatures get longer?

Question 71

# REVERSED generate from the collection of all elements (signatures) a small list of candidate pairs: pairs of elements whose similarity must be evaluated

Answer 71

What is locality sensitive hashing?

Question 72

# REVERSED Divide the signature matrix into b bands, consisting of r rows. When rows are the same in the band, they are hashed to the same bucket. They have b opportunities to be hashed to the same bucket -signatures hashed to the same bucket are compared

Answer 72

How do you perform locality sensitive hashing?

Question 73

# REVERSED 1. Select the most appropriate attribute name(s) with respect to noise and distinctiveness 2. Transform the corresponding value(s) into a blocking key (BK) 3. For each BK, create one block that contains all entities having this BK in their transformation \* works as a hash function - blocks on the equality of BKs

Answer 73

What is the standard blocking algorithm?