CS 431 homework 5

DUE: Monday Mar 6 at 5pm. Submit to Desire2Learn Dropbox.

Super Simple Database: Enrollment Analytics Solution

Materials ==> hw05-enrollment-db.zip

Description

You will be implementing a small database containing enrollment records for a small university. Each record contains when a class was taught, how many students enrolled, etc… Contained in the zip file above are three fixtures for loading your database with real data from csv. Your task is:

A. Examine the database design E-R diagram (pdf)

B. Based on the design write the sql statements for creating the necessary tables (there will be 3). The sql file is provided but it is empty so edit! Run the sql statements in using sqlite3.

C. Once you have successfully created the tables, import the csv fixtures to load the tables with data.

D. After you’ve done C you can compose and run the following queries:

Show all schedule records where actual enrollment is greater than 10. For each record also show which semester is associated with the record. (e.g., Fall Semester 2009).
Show all schedule records where course level is 300 or above. For each record also show the title of the course..

These queries will require the use of inner join. What do you need join?

What to Turn In:

You will be turning your sqlite db file, an sql file containing your create table statements, and an sql file containing your two queries.

Solution

-- enroll-analytics.sql homework 5 DDL statements

-- place your sql statements for creating course, semester, and schedule tables below.


-- csv column order: PK,SUB,NUM,TITLE
create table courses (
	pk int not null,
	dept varchar(6),
	level int,
	title varchar(512),
	primary key(pk)
);

-- csv column order: PK,TERM_CODE,TERM_TITLE
create table semesters (
	pk int not null,
	code int,
	title varchar(48),
	primary key(pk)
);


-- csv column order: PK,CRS_ID,SEM_ID,CRN,TYPE,SECT,MAX,ACT
create table schedules (
	pk int not null,
	course_pk int not null,
	semester_pk int not null,
	crn int not null,
	type varchar(48) not null,
	section int not null,
	max_enroll int not null,
	actual_enroll int not null,
	primary key(pk),
	foreign key(course_pk) references courses(pk),
	foreign key(semester_pk) references semesters(pk)
);

Queries

-- enrollment analytics queries hw 5 cs4/531

-- Show all schedule records where actual enrollment is greater than 10. For each record also show which semester is associated with the record. (e.g., Fall Semester 2009).

select semesters.title, schedules.crn, schedules.actual_enroll 
from semesters inner join schedules on semesters.pk = schedules.semester_pk 
where schedules.actual_enroll > 10;

-- results should return 178 rows. E.g.,

-- 'Fall Semester 2005',2509,39
-- 'Fall Semester 2005',2510,37
-- 'Fall Semester 2005',2511,19
-- 'Fall Semester 2005',2512,17
-- ...

-- Show all schedule records where course level is 300 or above. For each record also show the title of the course.

select courses.dept, courses.level, courses.title, schedules.crn
from schedules inner join courses on schedules.course_pk = courses.pk 
where courses.level > 299;

-- results should return 637 rows. E.g.,

-- 'CS',516,'Adv Comp Prg Data Structure',4538
-- 'CS',535,'ST:  Computer Science',4483
-- 'CS',543,'Operating Systems',3359
-- 'CS',551,'Software Engineering',3361
-- 'CS',574,'Machine Learning Algorithms',4540
-- ...