CMU SCS
Carnegie Mellon Univ.
Dept. of Computer Science
15-415/615 - DB Applications
C. Faloutsos – A. Pavlo
Lecture#1: Introduction
CMU SCS
Today’s Agenda
• Course Overview & Logistics
• Introduction of Databases
Faloutsos/Pavlo CMU SCS 15-415/615 2
CMU SCS
15-615 Wait List
• We are capped at 90 students.
• We do not follow CMU S3’s position list.
• You are not allowed to swap with
somebody already enrolled in the course.
Faloutsos/Pavlo CMU SCS 15-415/615 3
CMU SCS
Course Objective
• Students will learn the fundamentals of
database management systems.
– When to use them
– How to model data with them
– How to store and retrieve information
– How to search quickly for information
– System internals & key algorithms
Faloutsos/Pavlo CMU SCS 15-415/615 4
CMU SCS
Course Logistics
• Course Policies + Schedule:
– Refer to course web page.
• Academic Honesty:
– Refer to CMU policy page.
– If you’re not sure, ask the professors.
– Don’t be stupid.
Faloutsos/Pavlo CMU SCS 15-415/615 5
CMU SCS
Course Rubric
• Homework Assignments
• Midterm Exam
• Final Exam
Faloutsos/Pavlo CMU SCS 15-415/615 6
CMU SCS
Homework Assignments
• All assignments are due at the beginning of
the lecture (3:00pm), on the due date.
• All assignments are to be done individually.
• Late policy: Four “Slip” Days
• HW3 & HW7 require more programming
than the other assignments.
Faloutsos/Pavlo CMU SCS 15-415/615 7
CMU SCS
Exams
• Midterm: Wednesday October 19th
– In-class (this room)
– Materials up to October 12th & HW4 (inclusive)
• Final: TBA
– Comprehensive
Faloutsos/Pavlo CMU SCS 15-415/615 8
CMU SCS
Grading
• Assignments: 30%
– See course web page for HW weights.
• Midterm: 30%
• Final: 40%
• No extra credit is offered.
Faloutsos/Pavlo CMU SCS 15-415/615 9
CMU SCS
Special Accommodations
• Please contact the professors if you need
special accommodations for the homework
or exams before the due dates.
• Refer to CMU Accommodations Page
Faloutsos/Pavlo CMU SCS 15-415/615 10
CMU SCS
Office Hours
• Christos (GHC 8019)
– Tuesdays @ 2pm-3pm
• Andy (GHC 9019)
– Mondays @ 12pm-1pm
• Also available by appointment.
• See course website for TA hours.
Faloutsos/Pavlo CMU SCS 15-415/615 11
CMU SCS
Course Message Board
• On-line discussion through Blackboard:
– http://cmudb.io/15415-f16-blackboard
• If you have a technical question about
homework, please use Blackboard.
– Don’t email profs or TAs directly.
• All non-project questions should be sent to
the professors.
Faloutsos/Pavlo CMU SCS 15-415/615 12
CMU SCS
Lecture Questions
• Ask questions during the lecture.
• If you are unsure about something, then
somebody else might have the same question.
• Don’t run up to talk to the professors
immediately after the lecture.
Faloutsos/Pavlo CMU SCS 15-415/615 13
CMU SCS
Course Topics
• Introduction to Databases
• Data Models
• Query Language (SQL)
• Database Design
• Query Optimization & Indexing
• Transaction Management
• Advanced Topics
Faloutsos/Pavlo CMU SCS 15-415/615 14
CMU SCS
Spring 2017
• 15-721 – Database Systems
– High-performance in-memory system internals
– Programming intensive
• 15-826 – Multimedia DBs & Data Mining
– Graph mining, time-series analysis, databases
for machine learning.
– Non-relational data
Faloutsos/Pavlo CMU SCS 15-415/615 15
CMU SCS
Database Talks (Optional)
• The CMU DB group hosts research/industry
talks throughout the semester.
• More information:
– http://db.cs.cmu.edu/events/
Faloutsos/Pavlo CMU SCS 15-415/615 16
CMU SCS
Research Positions (Optional)
• We are looking for students to help build
CMU’s new flagship DBMS (Peloton)
– Database Internals (C++11)
– Autonomous Operation (TensorFlow)
• Come to the project info meeting:
– Friday Sept 9th @ 12:30 (GHC 9115)
– http://cmudb.io/fall2016-positions
Faloutsos/Pavlo CMU SCS 15-415/615 17
CMU SCS
What is a Database?
Faloutsos/Pavlo CMU SCS 15-415/615 18
CMU SCS
Database Example
• Or why should you take this course?
• Let’s build a simple application…
Faloutsos/Pavlo CMU SCS 15-415/615 19
CMU SCS
Database Example
• Create a database to keep track of the music
that is available in our application.
ARTIST ALBUM TRACK
name name name
description year number
country
Faloutsos/Pavlo CMU SCS 15-415/615 20
CMU SCS
Flat File Strawman
• Store the data in comma-separated value
(CSV) files.
– Use a separate file per entity.
– The application has to parse the files each time
they want to read/update records.
for
for line
line in
in file:
file:
record
record == parse(line)
parse(line)
if
if searchKey
searchKey in
in record:
record:
//
// Do
Do something!
something!
Faloutsos/Pavlo CMU SCS 15-415/615 21
CMU SCS
Database Example
• Create a database to keep track of the music
that is available in our application.
ARTIST ALBUM TRACK
name name name
description year
artist number
album
country year number
Faloutsos/Pavlo CMU SCS 15-415/615 22
CMU SCS
Flat Files: Data Integrity
• How do we ensure that the artist is the same
for each album entry?
• What if somebody overwrites the album
year with an invalid string?
• What if there are multiple artists on an
album?
Faloutsos/Pavlo CMU SCS 15-415/615 23
CMU SCS
Flat Files: Implementation
• How do you find a particular record?
• What if we now want to create a new
application that uses the same database?
• What if two threads try to write to the same
file at the same time?
Faloutsos/Pavlo CMU SCS 15-415/615 24
CMU SCS
Flat Files: Security
• What if only want some people to see some
of the records data in a file?
• What about all of the records but only some
of their attributes?
Faloutsos/Pavlo CMU SCS 15-415/615 25
CMU SCS
Flat Files: Durability
• What if the machine crashes while we’re
updating a record?
• What if we want to replicate the database
on multiple machines for high availability?
Faloutsos/Pavlo CMU SCS 15-415/615 26
CMU SCS
Database
Database ≠ Management
System
Faloutsos/Pavlo CMU SCS 15-415/615 27
CMU SCS
Database Management System
• A DBMS is software that allows
applications to store and analyze
information in a database.
• A general-purpose DBMS is designed to
allow the definition, creation, querying,
update, and administration of databases.
Faloutsos/Pavlo CMU SCS 15-415/615 28
CMU SCS
DBMS
15-415/615
Types: Data Models
• Relational Traditional / NewSQL
• Key/Value
• Graph
NoSQL
• Document
• Column-family
• Array/Matrix
• Obsolete: Hierarchical, Network
Faloutsos/Pavlo CMU SCS 15-415/615 29
CMU SCS
DBMS Types: Target Workload
• On-line Transaction Processing (OLTP)
– Fast operations that only read/update a small
amount of data each time.
• On-line Analytical Processing (OLAP)
– More complex read-only queries that read a lot
of data all at once to compute aggregate data.
Faloutsos/Pavlo CMU SCS 15-415/615 30
CMU SCS
Relational Database Example
• Declare the attributes of each table.
– Name / Value Types / Constraints
CREATE ARTIST
CREATE TABLE
TABLE artist
artist (( CREATE ALBUM
CREATE TABLE
TABLE album
album (( TRACK
CREATE
CREATE TABLE
TABLE track
track ((
name
name VARCHAR(32)
VARCHAR(32) NOT
NOT NULL,
NULL, name
name VARCHAR(64)
VARCHAR(64) NOT
NOT NULL,
NULL, name
name VARCHAR(64)
VARCHAR(64) NOT
NOT NULL,
NULL,
genre name
genre VARCHAR(32),
VARCHAR(32), artist name
artist VARCHAR(32)
VARCHAR(32) NOT
NOT NULL
NULL name
album
album VARCHAR(64)
VARCHAR(64) NOT
NOT NULL
NULL
country
country CHAR(3),
CHAR(3), ↪REFERENCES
↪REFERENCES artist(name),
artist(name), ↪REFERENCES
↪REFERENCES artist(name),
artist(name),
PRIMARY
description
PRIMARY KEY
KEY (name)
(name) year
artist
year INT
INT CHECK(year
CHECK(year >> 0),
0),
album
tracknum
tracknum SMALLINT
SMALLINT
);
); country year
PRIMARY
PRIMARY KEY
KEY (name)
(name) number
↪CHECK(tracknum
↪CHECK(tracknum >> 0),
0),
);
); PRIMARY
PRIMARY KEY
KEY (name,
(name, tracknum)
tracknum)
);
);
Faloutsos/Pavlo CMU SCS 15-415/615 31
CMU SCS
DBMS: Fundamental Concepts
• Three-level Architecture
• Logical Data Independence
• Physical Data Independence
Faloutsos/Pavlo CMU SCS 15-415/615 32
CMU SCS
Three-level Architecture
View Level View 1 … View n
What information is exposed to users,
what are they allowed to see…
Logical Level
What tables are there, what attributes do
Table
they have, what constraints should the
DBMS enforce…
Physical Level
How data is stored, where it is located,
how many bytes, what type of indexes… Storage
Faloutsos/Pavlo CMU SCS 15-415/615 33
CMU SCS
Logical Data Independence
• We can modify our table definitions
without having change our application’s
views.
• Example:
– Add/drop/rename attributes for a table.
– Rename a table.
Faloutsos/Pavlo CMU SCS 15-415/615 34
CMU SCS
Physical Data Independence
• We can change how/where database objects
are represented in the physical storage.
• Examples:
– Use 32-bits instead of 64-bits for integers.
– Convert an index from a B+Tree to a SkipList.
– Compress a table when it is stored on disk.
– Move a table to another disk/machine.
Faloutsos/Pavlo CMU SCS 15-415/615 35
CMU SCS
Course Topics
• Introduction to Databases
• Data Models
• Query Language (SQL)
• Database Design
• Query Optimization & Indexing
• Transaction Management
• Advanced Topics
Faloutsos/Pavlo CMU SCS 15-415/615 36
CMU SCS
Next Class
• Application Modeling
– Entities
– Relationships
– Attributes
Faloutsos/Pavlo CMU SCS 15-415/615 37
