1 of 38

CSE 344 MIDTERM REVIEW

By Jack Khuu

Co-author: Caffeine, Ibuprofen, Boba

2 of 38

MAJOR TOPICS ON THE EXAM

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

​

​

3 of 38

MAJOR TOPICS ON THE EXAM

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

​

​

4 of 38

RELATIONAL DATA MODEL

  • DBMS

​

  • Schema

​

  • First Normal Form

​

  • Primary/Foreign Key Constraints

​

5 of 38

RELATIONAL DATA MODEL

  • DBMS

​

  • Schema

​

  • First Normal Form

​

  • Primary/Foreign Key Constraints

​

6 of 38

KEY CONSTRAINTS

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

​

  • Foreign Key Constraint
    • Corresponding value must exist

​

7 of 38

KEY CONSTRAINTS

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

​

  • Foreign Key Constraint
    • Corresponding value must exist

​

8 of 38

MAJOR TOPICS ON THE EXAM

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

​

​

9 of 38

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

10 of 38

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

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

11 of 38

SQL SERVER SYNTAX

  • FWGHOS

​

  • Specifying Keys

​

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

​

12 of 38

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

13 of 38

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

14 of 38

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

15 of 38

JOINING

  • Self Joins

​

  • Inner Joins

​

  • (Left, Full, Right) Outer Join

​

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

JOIN

16 of 38

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

17 of 38

AGGREGATE

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

​

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

​

  • Not Monotonic!

18 of 38

SQL (SQL SERVER)

  • SQL Server Syntax

​

  • Table Creation

​

  • Joining

​

  • Aggregate

​

  • Subquery

19 of 38

SUBQUERY

  • Syntax

​

  • Witnessing

​

  • Correlated/Uncorrelated

​

  • WITH

​

20 of 38

MAJOR TOPICS ON THE EXAM

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

​

​

21 of 38

RELATIONAL ALGEBRA

  • Tree and Inline Forms

​

  • Symbols for RA

​

  • Theta Join

​

22 of 38

RELATIONAL ALGEBRA

  • Tree and Inline Forms

​

  • Symbols for RA

​

  • Theta Join

​

23 of 38

MAJOR TOPICS ON THE EXAM

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

​

​

24 of 38

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

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

25 of 38

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

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

26 of 38

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

27 of 38

QUERY SAFETY

  • Use every variable in a positive atom

​

  • “NonAns”
    • Change ∀ to ¬∃¬

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

28 of 38

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

29 of 38

AGGREGATES

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

​

  • count:

​

  • max/min <var> :

​

  • sum <var> :

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

30 of 38

DATALOG

  • Souffle Syntax

​

  • Query Safety

​

  • Aggregates

​

  • Recursion

31 of 38

RECURSION

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

​

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

​

​

32 of 38

MAJOR TOPICS ON THE EXAM

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

​

​

33 of 38

NoSQL, SQL++

34 of 38

NoSQL

OLAP VS OLTP

Data Models

Partition VS Replication

​

35 of 38

NoSQL: Vocab

OLAP: Online Analytical Processing

OLTP: Online Transaction Processing

Key-Value Stores

Document Stores

​

36 of 38

NoSQL: Partition VS Replication

Writes?

Read?

OLTP or OLAP?

​

37 of 38

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!

​

38 of 38