1 of 36

CSE 414 MIDTERM REVIEW

By Jack Khuu

Co-author: Caffeine, Ibuprofen, Boba

2 of 36

MAJOR TOPICS ON THE EXAM

  • SQL (SQL Server)
  • Relational Data Model
  • Relational Algebra
  • Datalog (Souffle)
  • NoSQL, SQL++

​

​

3 of 36

MAJOR TOPICS ON THE EXAM

  • SQL (SQL Server)
  • Relational Data Model
  • Relational Algebra
  • Datalog (Souffle)
  • NoSQL, SQL++

​

​

4 of 36

SQL (SQL SERVER)

  • SQL Server Syntax
  • Table Creation
  • Joining
  • Aggregate
  • Subquery

5 of 36

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

https://i.imgflip.com/1iio06.jpg

6 of 36

SQL SERVER SYNTAX

  • FWGHOS

​

  • Specifying Keys

​

  • Group By
    • SELECT only aggregates or items in the group by

​

7 of 36

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

8 of 36

TABLE CREATION

  • Create a table

​

  • Insert into a table

​

  • Delete from a table

​

  • Change existing tuples

https://i.pinimg.com/originals/b3/d8/6f/b3d86fe6fd72da31297e1342fe46a88c.png

9 of 36

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

10 of 36

JOINING

  • Self Joins

​

  • Inner Joins

​

  • (Left, Full, Right) Outer Join

​

https://i.kym-cdn.com/photos/images/newsfeed/000/954/924/bd1.jpg

JOIN

11 of 36

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

12 of 36

AGGREGATE

  • SELECT only aggregates/attribute being grouped
    • HAVING has the same restrictions

​

  • SUM,
  • MAX, MIN,
  • COUNT, AVG

​

  • Not Monotonic!

13 of 36

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

14 of 36

SUBQUERY

  • Syntax

​

  • Witnessing

​

  • Correlated/Uncorrelated

​

  • WITH

​

15 of 36

MAJOR TOPICS ON THE EXAM

  • SQL(SQL Server)
  • Relational Data Model
  • Relational Algebra
  • Datalog (Souffle)
  • NoSQL, SQL++

​

​

16 of 36

RELATIONAL DATA MODEL

  • DBMS

​

  • Schema

​

  • First Normal Form

​

  • Primary/Foreign Key Constraints

​

17 of 36

RELATIONAL DATA MODEL

  • DBMS

​

  • Schema

​

  • First Normal Form

​

  • Primary/Foreign Key Constraints

​

18 of 36

KEY CONSTRAINTS

  • Key Constraint
    • No duplicate keys
    • Can be multiple attributes

​

  • Foreign Key Constraint
    • Corresponding value must exist

​

19 of 36

KEY CONSTRAINTS

  • Key Constraint
    • No duplicate keys
    • Can be multiple attributes

​

  • Foreign Key Constraint
    • Corresponding value must exist

​

20 of 36

MAJOR TOPICS ON THE EXAM

  • SQL (SQL Server)
  • Relational Data Model
  • Relational Algebra
  • Datalog (Souffle)
  • NoSQL, SQL++

​

​

21 of 36

RELATIONAL ALGEBRA

  • Tree and Inline Forms

​

  • Symbols for RA

​

  • Theta Join

​

22 of 36

RELATIONAL ALGEBRA

  • Tree and Inline Forms

​

  • Symbols for RA

​

  • Theta Join

​

23 of 36

MAJOR TOPICS ON THE EXAM

  • SQL (SQL Server)
  • Relational Data Model
  • Relational Algebra
  • Datalog (Souffle)
  • NoSQL, SQL++

​

​

24 of 36

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

https://souffle-lang.github.io/img/logo-2x.png

25 of 36

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

https://souffle-lang.github.io/img/logo-2x.png

26 of 36

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

27 of 36

QUERY SAFETY

  • Use every variable in a positive atom

​

  • “NonAns”
    • Change ∀ to ¬∃¬

https://i.pinimg.com/originals/78/de/f9/78def9a07ce608e8f60b54707bcff840.jpg

28 of 36

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

29 of 36

AGGREGATES

  • <var> = <aggr>: {<condition>}

​

  • count:

​

  • max/min <var> :

​

  • sum <var> :

https://assets.imksb.net/sites/14/2017/03/big-snowball.jpg

30 of 36

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

31 of 36

RECURSION

  • Base Case(s) + Recursive Case(s)

​

  • Foo(x, 0)�Foo(x, n + 1)�
  • Compute IDBs in iterations

​

​

32 of 36

MAJOR TOPICS ON THE EXAM

  • SQL (SQL Server)
  • Relational Data Model
  • Relational Algebra
  • Datalog (Souffle)
  • NoSQL, SQL++

​

​

33 of 36

NoSQL, SQL++

34 of 36

NoSQL

OLAP VS OLTP

Data Models

Partition VS Replication

​

35 of 36

NoSQL: Partition VS Replication

Partition to optimize writes. OLTP can be write heavy.

Replication to optimize reads OLAP can be read heavy.

Remember: no one size fits all!

​

36 of 36