Instructions HW1
CS 4320/5320 Homework 1
Fall 2014
Due September 17, 2014
This assignment is due on Wednesday, 9/17/2014 at 11:59pm. It is out of 65 points and counts for 10% of your overall grade.
You must work on this homework in groups of 2 or 3 students. You should make one CMS submission for your entire group, and everyone in the group will receive the same grade.
To complete the code portions of this assignment, you will need to install both MySQL
(http://dev.mysql.com/downloads/mysql/) and PostgreSQL
(http://www.postgresql.org/download/). By default we will grade your code on the current release
(stable) versions of the respective platforms; however, the functionality we are using is pretty fundamental and stable, so typically minor version differences do not lead to unexpected behavior differences.
1
SQL and Relational Algebra queries (26 points)
Consider the schema given by the following two SQL CREATE statements.
CREATE TABLE Enrolled (sid INTEGER NOT NULL, cid INTEGER NOT NULL, semesterid INTEGER NOT NULL,
PRIMARY KEY (sid, cid, semesterid));
CREATE TABLE CourseOffering (cid INTEGER NOT NULL, semesterid INTEGER NOT NULL, profid INTEGER, roomid INTEGER,
PRIMARY key (cid, semesterid));
This schema describes a simple university setting. The first table keeps track of which students are enrolled in which courses in a given semester, and each (sid, cid, semesterid) triple states that student sid takes course cid in semester semesterid. The second table keeps track of course offerings – a course offering has a course id (cid), semester id and associated info such as the profid of the professor teaching the course and the roomid of the classroom where the course meets.
Write the following queries in SQL. Each of your answers must be a single SQL query. Your SQL queries will be graded by running them in MySQL on test cases; queries that return the wrong answer will receive
1
0 points, i.e. there is no partial credit. In particular, please
Fall 2014
Due September 17, 2014
This assignment is due on Wednesday, 9/17/2014 at 11:59pm. It is out of 65 points and counts for 10% of your overall grade.
You must work on this homework in groups of 2 or 3 students. You should make one CMS submission for your entire group, and everyone in the group will receive the same grade.
To complete the code portions of this assignment, you will need to install both MySQL
(http://dev.mysql.com/downloads/mysql/) and PostgreSQL
(http://www.postgresql.org/download/). By default we will grade your code on the current release
(stable) versions of the respective platforms; however, the functionality we are using is pretty fundamental and stable, so typically minor version differences do not lead to unexpected behavior differences.
1
SQL and Relational Algebra queries (26 points)
Consider the schema given by the following two SQL CREATE statements.
CREATE TABLE Enrolled (sid INTEGER NOT NULL, cid INTEGER NOT NULL, semesterid INTEGER NOT NULL,
PRIMARY KEY (sid, cid, semesterid));
CREATE TABLE CourseOffering (cid INTEGER NOT NULL, semesterid INTEGER NOT NULL, profid INTEGER, roomid INTEGER,
PRIMARY key (cid, semesterid));
This schema describes a simple university setting. The first table keeps track of which students are enrolled in which courses in a given semester, and each (sid, cid, semesterid) triple states that student sid takes course cid in semester semesterid. The second table keeps track of course offerings – a course offering has a course id (cid), semester id and associated info such as the profid of the professor teaching the course and the roomid of the classroom where the course meets.
Write the following queries in SQL. Each of your answers must be a single SQL query. Your SQL queries will be graded by running them in MySQL on test cases; queries that return the wrong answer will receive
1
0 points, i.e. there is no partial credit. In particular, please