Registration Database
Introduction
In this exercise you will practice
- basic relational data modeling,
- writing SQL create table statements,
- writing SQL schema and data insertion scripts, and
- writing basic SQL queries.
Problem Description
You work for the registrar of a major university and have been tasked with creating a database to hold courses and semester schedules.
Solution Description
Part 1: Creating the database
Write a SQL script named registration-schema.sql that creates a database with the following tables:
- student(student_id, name, gpa)
- dept(dept_id, name, dean, building, room)
- course(dept_id, course_id, name, hours), where dept_id is a foreign key referencing the dept(dept_id)
- enrolled(dept_id, course_id, student_id), where (dept_id, course_id) is a foreign key referencing course(dept_id, course_id) and student_id is a foreign key referencing student(student_id)
Part 2: Populating the database
Write a SQL script that populates the following data:
Student
|------------+---------+-----| | student_id | name | gpa | |------------+---------+-----| | 11 | Bush | 3.0 | | 12 | Cruz | 3.2 | | 13 | Clinton | 3.9 | | 22 | Sanders | 3.0 | | 33 | Trump | 3.8 | |------------+---------+-----|
Enrolled
| dept_id | coruse_id | student_id | |---------+-----------+------------| | CS | 101 | 11 | | Math | 101 | 11 | | CS | 101 | 12 | | CS | 201 | 22 | | Math | 201 | 33 | | EE | 102 | 33 | | Math | 301 | 22 |
Department
| dept_id | name | dean | building | room | |---------+------+------+----------+------| | CS | Computer Science |Rubio | Ajax | 100 | | Math | Mathemagics | Carson | Acme | 300 | | EE | Electrical Engineering | Kasich | Ajax | 200 | | IE | Industrial Engineering | Cruz | | 200 | | Music | Musicology | Costello | North | 100 |
Course
| dept_id | course_id | name | hours |
|---|---|---|---|
| CS | 101 | Programming | 4 |
| CS | 201 | Algorithms | 3 |
| CS | 202 | Systems | 3 |
| Math | 101 | Algebra | 3 |
| Math | 201 | Calculus | 4 |
| Math | 301 | Analysis | 4 |
| Music | 104 | Jazz | 3 |
| EE | 102 | Circuits | 3 |
| IE | 101 | Proabability | 3 |
| IE | 102 | Statistics | 3 |
Part 3: Updating the database
Once the database has been populated, make the following updates:
- Change the name of the Math dept to 'Mathematics'
- Change the name of the Music dept to 'Rock n Roll'
- Add an IE course, IE 202 Simulation, 3 hours.
Part 4: Querying the database
Write a sql script with queries that answer the following questions.
Easy Queries:
- What are all the departments?
- What are the names of all the department deans?
- What is the name of the dean of the CS dept?
- What are all the course numbers, e.g., 'CS 2316'?
- What are the course numbers of all the first-year courses?
- What are the course numbers of all the CS courses?
- What are all the CS and IE courses?
Intermediate queries
- What are the names of the students enrolled in CS 101?
- What is the total enrollment of each department?
- What are the names of the deans of departments that have zero enrollment?
- Which department(s) have the greatest enrollment?
Sample Solutions
Don't peek until you've tried the exercise on your own!