CIT 111: Introduction to Databases

Assignment: Review Project

Design the University database

Watch the following video. Note: this video was created for the 14 week course. The week numbers for your course will be different.

(12:11 mins, “Project Part 1 Intro” Transcript)

Design a University ERD using the University Data The data in this file is not already set up into entities (tables). These are actually what query result sets might look like, not necessarily just tables themselves. Watch the data types you give each attribute. Make sure you have enough varchar length for the long college and department names and that attributes that can hold numbers that you might do math on later like credits, capacity, etc. are int and not varchar. Don't use a date datatype when only a year is needed. There is a year datatype. And remember dates are inserted with quotes.

One assumption: Only one faculty member teaches a section of a course. 

Hints:
  • You will need a 'section' table. This table will be a central table in the design. A section is defined as a given instance of a course (imagine your enrollment in this very class, at this time, with this teacher). Each section can have a different capacity, set by the teacher.
  • Technically, a student is enrolled in the section. So, the student relationship should be with the section table, not the course table. The same is true for the teacher. The course table would be information about a course like you'd see in a student course catalog.
  • As for the term, consider our class section. It is only assigned to one year/term. But a given year/term can have many sections.

Create the University database

Forward engineer the University database and copy the code used during that forward engineer into an sql file named University.sql. This is the same file you will be adding the insert statements to.

Load the University database

Insert the University Data. Use Insert statements and save as one file with the forward engineer code.

Query the University database

(04:27 mins, “Project Querying the University database” Transcript)

Write the queries listed below and add them your University.sql file. Use comments to number your queries.

  1. Students, and their birthdays, of students born in September. Format the date to look like it is shown in the result set. Sort by the student's last name.

    Query 1 result set
    Query 1 result se
    t

  2. Student's age in years and days as of Jan. 5, 2017. Sorted from oldest to youngest. (You can assume a 365 day year and ignore leap day.) Hint: Use modulus for days left over after years. The 5th column is just the 3rd and 4th column combined with labels.

    Query 2 result set
    Query 2 result set

  3. Students taught by John Jensen. Sorted by student's last name

    Query 3 result set
    Query 3 result set

  4. Instructors Bryce will have in Winter 2018. Sort by the faculty's last name.

    Query 4 result set
    Query 4 result set

  5. Students that take Econometrics in Fall 2019. Sort by student last name.

    Query 5 result set
    Query 5 result set

  6. Report showing all of Bryce Carlson's courses for Winter 2018. Sort by the name of the course.

    Query 6 result set
    Query 6 result set

  7. The number of students enrolled for Fall 2019

    Query 7 result set
    Query 7 result set

  8. The number of courses in each college. Sort by college name.

    Query 8 result set
    Query 8 result set

  9. The total number of students each professor can teach in Winter 2018. Sort by that total number of students (teaching capacity).

    Query 9 result set
    Query 9 result set

  10. Each student's total credit load for Fall 2019, but only students with a credit load greater than three.  Sort by credit load in descending order. 

    Query 10 result set
    Query 10 result set

Submission:

Submit the University.sql file, which includes forward engineering code, inserts, and queries in Canvas

Useful Links: