158.247 Database Design

Assignment 1, 2020

Consider the following SQL table definitions:
CREATE TABLE course (
code text not null
, title text not null
);
CREATE TABLE offering (
offer_id int not null
, course_code text not null
, semester text not null
);

CREATE TABLE offered_at (
offer_id int not null
, location text not null
);
CREATE TABLE student (
student_id int not null
, name text not null
);

On Stream you will find a file with some sample data.

1. UsingALTER TABLEstatements, define sensible key and foreign key constraints for these tables. Do not add any new columns. [4 marks] Hint:UseUNIQUEconstraints to define secondary keys as needed.
2. Add a constraint restrictinglocationvalues toALB,MANorDIS. [1 mark]
3. Create the SQL definition for a tableprereq, storing course prerequisites. Define sensible key and foreign key constraints. [2 marks] Note:Prerequisites can change, so offerings in different semesters may have different prerequisites.
4. Create the SQL definition for a tableenroled, recording student enrolments. Define sensible key and foreign key constraints. [3 marks]
5. Write an SQL query returning all enrolments (studentid, coursecode, semester) that have at least one unmet prerequisite. [4 marks] Note:A prerequisite is met if a student is enroled for some offering of the prerequisite course.
6. Write an SQL query that returns for each distance (DIS) offering the number of enrolled students that are also enrolled in some Manawatu internal (MAN) offering in the same semester. [6 marks] Note:Return (coursecode, semester, studentcount). Hint:Use WITH clauses. Make sure to return cases where the result is 0.

Submit your answersas pdf or plain text filevia stream. Include yourname and student ID.