CSE 562

Database Systems

Oliver Kennedy

[email protected]

133A Ketter Hall

Why are databases awesome?

They're everywhere

They combine everything

They're $$

7 out of the largest 10 software companies ($698.8bn out of $771.9bn)
make their money in databases (and most of the others are data-adjacent)

Ok... so what's "databases"?

Databases

Techniques

• Data Modeling
• Cost-Based Optimization

Recipes

• Join Algorithms
• Index Data Structures

Knowledge

• The Memory Hierarchy
• Equivalence Rules

Which tools to use

... and when

This Course in Short

For X, the best, correct choice is Y, at least when Z

• How do you define 'correct' and 'best'?
• What correct alternatives are available?
• How do you find the best available alternative?

What is 'Better'?

• Declarative Queries: 'Easy to think about' vs 'Fast'
• Data Layouts: Space vs Fast Updates vs Fast Queries
• Parallel Updates: Reactive vs Proactive Concurrency

Today

• Logistics: What you need to know
• Project Outline: A relational query engine
• Ways to Fail: What not to do and why
• Intro: So what is a database anyway?
• SQL: Recapping everyone's favorite language

People

• Oliver Kennedy ([email protected])
  Ketter 133A; Thu 2:00-3:00
• Liuyi Yao ([email protected])
  Davis 338H; Mon 4:00-5:00
• Alex Stachnik ([email protected])
  Davis 338H; Wed 3:00-4:00

Syllabus & Website

https://odin.cse.buffalo.edu/teaching/cse-562

Course Project

http://dubstep.odin.cse.buffalo.edu

Course Structure

• Programming Assignment (50% of overall grade)
  3-person Groups Build a Relational Query Engine
• Theoretical Content (50% of overall grade)
  • Midterm on March 4 (15% of overall grade)
  • Midterm on Apr 8 (15% of overall grade)
  • Comprehensive Final Exam (20% of overall grade)

Optional Textbook 1

Optional Textbook 2

Hardcover	Softcover
$160	$25
has Index, ToC	no Index, ToC

Optional Textbook 3

Course Project

Data µBases

Embedded Databases

• SQLite is in your browser, computer, phone, ...
• Declarative data management is awesome!
• Tech needs small data: Mobile, IoT, Web

Your team's goal:
build (part of) an embedded database

The Project

• I give you data (CSV Files + A Schema)
• I ask you questions about the data (SQL)
• You give me an answer

Real world challenge: You start with...

• ... an empty GIT repository
• ... a few open-source libraries

Real world challenge: You get graded on...

... Correctness

Do you produce the right answer?
1/3 grade for computing the correct answer

... Speed

How fast did you produce the answer?
2/3 grade for meeting or beating the reference implementation

Submission

Query Processor

DµBStep

Checkpoint 0: "Hello World" due Feb 10

SELECT * FROM FOO

Checkpoint 1: "Expressions" due Mar 9

SELECT A*23 FROM FOO WHERE B > 10

Checkpoint 2: "Precomputation" due Apr 13

SELECT SUM(A) FROM FOO WHERE B > 10

Checkpoint 3: "Joins" due May 11

SELECT SUM(A) FROM FOO, BAR WHERE B > 10

Getting Started

A link to the submission system is on the syllabus.

There's a handy "create account" link.

Use your UBIT email address to create an account.

TODO By Feb 10

1. Create a group of up to 3 people
2. Register your group
3. Access your group's GIT repository
4. Download JSQLParser
5. Look at the JSQLParser Javadoc
6. Read the Checkpoint 0 Overview
7. Create a SQL "Hello World" program
8. Hit Submit

A SQL "Hello World"

1. Read SELECT * FROM [tablename] from System.in
2. Use JSQLParser to parse the query
   • You don't need to use JSQLParser... but it will make your life easier later.
3. Get the Table Name
4. Read in the file data/[tablename].csv
5. Write the contents out to System.out

Submit as many times as you need to

Your grade will never ever decrease because you decided to submit just one more time

Ways to Fail

(please do not do these things)

• Start your project the weekend before it's due.
• Avoid your TAs and their office hours.
• Stay quiet in class when your professor says something you don't understand.
• Wait until the deadline to submit for the first time.
• Cheat.

Academic Integrity

Cheating is submitting any work that you did not do yourself, as if you did

References (properly cited)

Wikipedia, Wikibooks: OK

Public Code

StackExchange, GitHub: NOT OK

Discussing concepts with classmates

"HashTables have O(1) lookups": OK (except exams)

Sharing code or answers with classmates

"Just look at how I did it": NOT OK

MOSS

MOSS

Academic Integrity

Zero Tolerance

If I catch you submitting someone else's code, you will fail the class.

Group Responsibility

If your teammate cheats on a group project, the entire group will be penalized.

Share Code, Share Blame

If someone else submits your code, you will be penalized as well.

Any Questions or Concerns?

What does a Database
Management System Do?

Data Management

Analysis: Answering user questions about a dataset

What kind of tools can we give users?

• Declarative Languages
• Organizational Datastructures (e.g., Indexes)

Transactions: Safely saving and sharing changing data

What kind of tools can we give users?

• Consistency Primitives
• Data Validation Primitives

Data

Files

Databases

{
  "firstName": "John", "lastName": "Smith","age": 25,
  "address": {
    "streetAddress": "21 2nd Street",
    "city": "New York", "state": "NY",
    "postalCode": 10021
  },
  "phoneNumbers": [
    {   "type": "home",
        "number": "212 555-1234"
    },{ "type": "fax",
        "number": "646 555-4567"
    } 
  ]
}

Databases understand and exploit the data's structure

Types

Primitives

Integer, String, Floating Point Number, Date

Collections

List/Array, Set, Bag

Tuple

Struct, Dictionary/Object

Type Glossary

Primitive

Basic Building Blocks

Tuple (aka Record)

Several 'fields' of different types
(a schema defines names/types for each field)

Set

Collection of unique records all of the same type.

Bag

Unsorted collection of records of the same type.

List

Sorted collection of records of the same type.

Relational Database Glossary

Your data is currently an unsorted set
of 100-attribute Tuples.

Tomorrow, you will be repeatedly asked for the value of 1 specific attribute from 5 specific rows, each identified by a 1 specific attribute.