Why Are Databases Awesome?

They're Everywhere

$$$

Rank		Organization	Sales (B$)	FY	Market cap (B$)	Headquarters
1		Microsoft	86.6	2017	601	Redmond, WA, US
2		Oracle	37.2	2017	205	Redwood City, CA, US
3		SAP	23.2	2017	117	Walldorf, Germany
4		Salesforce.com	8.4	2017	69	San Francisco, CA, US
5		VMware	6.7	2017	48	Palo Alto, CA, US
6		Fiserv	5.3	2017	26	Brookfield, WI, US
7		Adobe Systems	5	2017	84	San Jose, CA, US
8		Symantec	5.4	2017	19	Mountain View, CA, US
9		Amadeus IT Holdings	4.3	2017	25	Madrid, Spain

5 of 9 Forbes Top Software Companies
Have a Focus on Data Management Systems

Interesting Problems

What is "Databases"?

How do we ask and answer questions about data?

• Efficiency
• Accuracy
• Summarization
• Data Curation

How do we manipulate and persist data?

• Consistency
• Correctness
• Parallelism

Databases

Which Tools To Use

And When?

Template for 90% of Database Systems

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

1. How do you define Correct and Best?
2. What correct alternatives are available?
3. How do you find the best available alternative

General Course Information

People

• Oliver Kennedy (me)
• Gokhan Kul (Practicum Lead)
• William Spoth (Project TA)
• Saurav Singhi (Concept TA)
• Carl Nuessle (Ninja)
• [ TBD ]

Syllabus and Projects

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

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

(same link)

Course Forum

https://piazza.com/buffalo/spring2018/cse4562/home

Course Structure

Concepts (50% of Grade; Lectures on Mon/Wed)

• Homework (10%; 12-15 Assignments, Keep Best 10)
• Midterm (20% or 15%)
• Comprehensive Final (20% or 25%)

Practicum (50% of Grade; Lectures on Fri)

• Build a Relational Query Engine
• 3-Person Group Project
• 5 "Checkpoints"

Embedded Databases

• SQLite (In your browser, computer, phone, fridge...)
• Simple, Easy-To-Use Declarative Data Management
• Critical for future tech: Part of Mobile, IoT, Web

Your Startup: Build the next great Embedded Database

We give you...

Data (CSV Files)

Schema Information (CREATE TABLE)

Questions (SQL Queries)

Real World Challenge

You get graded on your code's...

Correctness

~1/3 credit for getting the right answer.

Performance

~2/3 credit for getting it reasonably fast.

Checkpoint 0: "Hello World"

5/50 pts

• Form groups
• Submit a simple Java program
• Make sure that the submission workflow works for you.

Checkpoint 1: "Intro to CSV"

10/50 pts

• Parse SQL with JSQLParser
• Load CSV Files
• Project (Map) Data
• Select (Filter) Data

Checkpoint 2: "Real SQL"

10/50 pts

• Joins
• Order By
• Limit
• Nested Queries
• Interactive Prompt

Checkpoint 3: "Optimization"

15/50 pts

• Aggregation
• Actual Data (no naive algorithms)

Checkpoint 4: "The Real World"

10/50 pts

• Too much data for memory
• Time for precomputation

Ways to Fail

• Start your project at the last minute
• Don’t go to office hours
• Don’t ask questions on Piazza
• Wait until the deadline to submit for the first time
• Cheat

Academic Integrity

Cheating is submitting any work that you did not perform by yourself as if you did.

References (when cited)

Wikipedia, Wikibooks (or similar): OK

Public Code

Stack Exchange (or similar): Not OK

Discussing concepts/ideas with classmates

“A hash index has O(1) lookups”: OK (except during exams 😇 )

Sharing code or answers with anyone

“Just have a look at how I implemented it”: NOT OK

For-hire code: NOT OK

MOSS

MOSS

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 as their own, you will be penalized as well.

Questions/Concerns?

What does a Data Management System Do?

Analysis: Answering user-provided questions about data

What kind of tools can we give end-users?

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

Manipulation: Safely persisting and sharing data updates

What kind of tools can we give end-users?

• Consistency Primitives
• Data Validation Primitives

So let's talk structure...

Primitive

Basic building blocks like Int, Float, Char, String

Tuple

Several ‘fields’ of different types. (N-Tuple = N fields)

A Tuple has a ‘schema’ defining each field

Set

A collection of unique records, all of the same type

Bag

An unordered collection of records, all of the same type

List

An ordered collection of records, all of the same type

Your data is currently an Unordered Set
of Tuples with 100 fields each.

Tomorrow, you’ll be repeatedly asked for 1 specific attribute
of 5 specific rows identified by the first attribute

Can you do better?

Better Idea: Rewrite data into a 99-Tuple of Maps keyed on the 1st attribute

This representation is equivalent and better for your needs.

Declarative specifications make it easier to find equivalences.