Everything is organized into test cases • There are clearly marked “fill in the blank” exercises • Type in the answer and run the test • Immediate feedback! 6
structured set of data. DBMS: Usually people refer to database management systems (DBMS) as databases. Examples include MySQL, PostgreSQL, MongoDB, etc. (This talk focuses on Relational DBMSes)
every row in the “users” table. $ python -m unittest exercises.E.test_exercise_1 . ---------------------------------------------------------------------- Ran 1 test in 0.170s OK
rows in the “users” table. $ python -m unittest exercises.E.test_exercise_2 . ---------------------------------------------------------------------- Ran 1 test in 0.155s OK
Each object for a class should have its own row ◦ That means you should have a table for each class ◦ Examples: users, products, widgets, … • It’s a good idea to have an “ID” column for classes ◦ Easier to reference a row
people and cars. We’ll have two tables. people and cars • If a person can have a single car, add a column in the people table for a car ID • If a car can have a single person, add a column in the cars table for a person ID • Otherwise, use a separate table storing (car, person) rows.
give you all possible combinations of the rows 27 Joins State ID Abbrev 1 VA 2 MD User ID State 1 1 2 1 JOIN => State ID Abbrev User ID State 1 VA 1 1 1 VA 2 1 2 MD 1 1 2 MD 2 1
give you all possible combinations of the rows 28 Joins State ID Abbrev 1 VA 2 MD User ID State 1 1 2 1 JOIN => State ID Abbrev User ID State 1 VA 1 1 1 VA 2 1 2 MD 1 1 2 MD 2 1
“State” columns refer to the same thing. 29 Joins Users JOIN => State ID Abbrev User ID State 1 VA 1 1 1 VA 2 1 2 MD 1 1 2 MD 2 1 States ON states.id = users.state
the following tables. How many JOINs do you need? 32 id code 1 AL 2 AK id username first_name last_name 1 mreynolds0 Michelle Reynolds 2 rbishop1 Ralph Bishop state user 32 1 48 2 states users locations
id INTEGER enrolled INTEGER capacity INTEGER } enrollment { student VARCHAR class INTEGER } id enrolled capacity 1 10 20 2 20 20 student class A 2 B This class is full.
to switch from class 2 to class 1. ◦ We need change the “class” column in the enrollment table, and then update the “enrolled” columns in the class table. • B wants to enter class 2. ◦ We need to check if there is space using the class table, and then update the enrollment table. id enrolled capacity 1 10 20 2 20 20 student class A 2 B
to switch from class 2 to class 1. ◦ We need change the “class” column in the enrollment table, and then update the “enrolled” columns in the class table. Something bad happens. The rest doesn’t complete. • B wants to enter class 2. ◦ We need to check if there is space using the class table, and then update the enrollment table. B can’t enroll in class 2, because it looks filled! id enrolled capacity 1 10 20 2 20 20 class 1 student class A 2 B
operations that have the following properties: • Atomicity: all of them happen or none of them do • Consistency: constraints hold true after the transaction is committed or rolled back • Isolation: transactions don’t overlap • Durability: committed transactions last 39
2.) 41 “A” “B” “A + B” 1 ? 3 Transaction 1 Transaction 2 (I want to set A = 2, but keep B the same.) SET “A” = 2 SET “A + B” = 4 (Something went wrong!) ROLLBACK (I want to get the value of B.) GET “A + B” => 3 GET “A” => 2 => “B” = 1
“A + B” 1 ? 3 Transaction 1 Transaction 2 (I want to add 1 to “B”.) GET “A + B” => 3 SET “A + B” = 4 COMMIT (I want to get the value of B.) GET “A + B” => 3 GET “A” => 1 => “B” = 2 (This is wrong!)
“A + B” 1 ? 3 Transaction 1 Transaction 2 (I want to add 1 to “B”.) GET “A + B” => 3 SET “A + B” = 4 ROLLBACK (We decide to undo.) (I want to get the value of B.) GET “A + B” => 4 GET “A” => 1 => “B” = 3 (This is wrong!)
“A + B” 1 ? 3 Transaction 1 Transaction 2 (I want to add 1 to “B”.) GET “A + B” => 3 SET “A + B” = 4 COMMIT (The databases crashes!) (I want to get the value of B.) GET “A + B” => 3 GET “A” => 1 => “B” = 2 (This is wrong!)
total number of rows in the table, and then undo your changes. Here’s the INSERT example as a reminder: INSERT INTO my_table (colA, colB) VALUES (1, 2) 49 id username first_name last_name 1 mreynolds0 Michelle Reynolds users
number in the following set? (Note that you don’t see the entire set!) You can’t answer that question! 7 1 5 2 18 4 21 -10 85 6 14 19 20 8 25 64 71 1 ... ...
number in the following set? (Note that you don’t see the entire set!) We don’t have to look at every value. -91 -14 -4 1 5 7 8 9 10 11 12 13 18 19 24 27 36 39 ... ...
schema. How many indexes are there? CREATE TABLE "users" ( "id" INTEGER PRIMARY KEY, "username" VARCHAR, "first_name" VARCHAR, "last_name" VARCHAR ); There is one index. 57
answer the following questions: 1. Which classes did a specific student sign up for? 2. Which students are signed up for a specific class? 61 student class C 3 A 2 B 1 C 1 B 2
answer the following questions: 1. Which classes did a specific student sign up for? 2. Which students are signed up for a specific class? We need two indexes to efficiently answer both of these questions. 62
answer the following questions: 1. Which classes did a specific student sign up for? 2. Which students are signed up for a specific class? 63 student class A 2 B 1 B 2 C 1 C 3 class student 1 B 1 C 2 A 2 B 3 C
answer the following questions: 1. Which classes did a specific student sign up for? 2. Which students are signed up for a specific class? 64 student class A 2 B 1 B 2 C 1 C 3 class student 1 B 1 C 2 A 2 B 3 C
SiteGround - Reliable hosting with speed, security, and support you can count on.
preetamjinka
geekplus_tech
minodriven
d0riven
guaca
suikabar
handlename
simas
kajitack
hollycummins
yukukotani
ochtum
16bitidol
holman
frankvandijk
kimpetersen
mclloyd
portentint
leimatthew05
tenderlove
axbom
nikkihalliwell
mongodb
aleyda
danielanewman
