Spring 2017 Schedule of Topics
Jump to week[n] ==> 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16
“Homework” 8 Normalization Self Study (not to be turned in)
psycopg2 (python api for postgresql)mysql connector (python api for mysql)querysetsimport mysql.connector
cnx = mysql.connector.connect(user='username', password='password', database='dbname')
cursor = cnx.cursor()
query = ("select * from users;")
cursor.execute(query)
# cursor contains an iterator of results
for i in cursor:
print iSQL to build and populate a small database: enroll-analytics-small.sql
Example queries using outer joins (updated after corrections at 3:15pm apr 17):
-- enrollment analytics small
-- a simple natural join of schedules and courses
create or replace view natural_join_view as
select schedules.semester_pk as semester_schedule, courses.title as course
from schedules left join courses
on schedules.course_pk = courses.pk;
-- a left outer join (keep the left table rows and show unmatched rows in right table)
-- shows all semesters that do not have scheduled classes...ever. Should NOT have nulls
-- because there are no unmatched semester pks in the schedule table (all 10 schedule records
-- are associated with a semester record).
create or replace view left_outer_view as
select schedules.semester_pk as semester_schedule, semesters.title as semester
from schedules left outer join semesters
on schedules.semester_pk = semesters.pk;
-- a right outer join (keep the right table rows and show unmatched rows in left table)
-- shows all semesters that have AND do not have an association with schedule table. Should
-- show those semesters that don't have a matching reference in the schedule table (they are null)
-- as well as those that DO HAVE a matching reference.
create or replace view right_outer_view as
select schedules.semester_pk as semester_schedule, semesters.title as semester
from schedules right outer join semesters
on schedules.semester_pk = semesters.pk
order by semester_schedule;
-- a right outer join (keep the right table rows and show unmatched rows in left table)
-- shows which courses that have and do not have a reference from the schedule table.
-- Indicates which courses that do not appear in the schedule table
create or replace view untaught_courses_view as
select schedules.course_pk as scheduled_course, courses.title
from schedules right outer join courses
on schedules.course_pk = courses.pk;Chapter 5 ==> (DBI) Databases Illuminated
using db in enroll-db-demo.zip
drop view course_history_view;
drop view enroll_avg_view;
-- groups courses by semester then title, shows total enrollment of aggregation
select sum(schedules.actual_enroll) as enroll_count, semesters.title, semesters.code as sem, courses.level, courses.title as crs_title
from schedules, courses, semesters
on schedules.course_pk = courses.pk and schedules.semester_pk = semesters.pk
-- where courses.title like "%machine learning%"
group by semesters.code, courses.title
order by semesters.code;
-- above query as a view
create view if not exists course_history_view as
select sum(schedules.actual_enroll) as enroll_count, semesters.title, semesters.code as sem, courses.level, courses.title as crs_title
from schedules, courses, semesters
on schedules.course_pk = courses.pk and schedules.semester_pk = semesters.pk
-- where courses.title like "%machine learning%"
group by semesters.code, courses.title
order by semesters.code;
-- calculates average enrollment by course in entire db
create view if not exists enroll_avg_view as
select avg(enroll_count) as a, crs_title
from course_history_view
group by crs_title
order by a;Midterm Exam Wednesday 11:00-12:15
A. Use sqlite3 to create a db:
sqlite3 car_db.db
B. Load the tables from external sql file cars.sql
sqlite3> .read cars.sql
C. Load data from csv fixtures cars.csv and makes.csv
sqlite3> .separator ","
sqlite3> .import makes.csv makes
sqlite3> .import cars.csv cars
/* Sample sql statements for the cars demo db. Setup the DB before running these.*/
/*SELECT (single table)*/
select * from cars;
/*SELECT THEN PROJECT (single table)*/
select cars.year, cars.odom from cars;
/*BINARY PRODUCT JOIN (cartesian product)*/
select * from cars inner join makes;
/*THETA JOIN (product then select)*/
select makes.make, cars.year from cars inner join makes;
/*EQUI JOIN or NATURAL JOIN (product then select predicate on col in A == col in B) */
select cars.year, makes.make from cars inner join makes on makes.pk = cars.make;A online testing app displays multiple choice tests. All tests must contain 20 multiple choice questions. Each question contains up to 5 possible choices. The testing app allows any number of students to take any number of tests. The test score for each student must be recorded for eternity.
import itertools
# roll your own ascii character generator.
alpha = [chr(i) for i in range(65, 65+26)] + [chr(i) for i in range(97, 97+26)] + [chr(i) for i in range(48, 58)]
##### Schema Example ==> (attr1, attr2, attr3)
# generate the cartesian product of sets of ascii characters (D1 * D2 * D3)
sets = list(itertools.product(alpha, alpha, alpha))
# a "relation" is a subset of sets:
rel = [i for i in sets if i[0] == 'A' and i[2] == 'Z']
##### Another Schema Example ==> (make, year, odometer)
domain_make = ['ford', 'dodge', 'gmc', 'chevy']
domain_year = range(2000, 2018)
domain_odom = range(50000, 200000, 50000)
# generate the cartesian product of sets of ascii characters (D1 * D2 * D3)
sets = list(itertools.product(domain_make, domain_year, domain_odom))
# a "relation" is a subset of sets:
rel = [i for i in sets if i[0] == 'ford' and i[2] == 2017]unary, binary, ternary, n-arycardinality