ITM 111: Introduction to Databases

W06 Project: University Database

• Purpose: To review database principles you learned in previous weeks.
• Tasks:
  1. Forward engineer a database.
  2. Load data into database.
  3. Query data.

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 organized into entities (tables). This data represents samples of what query result sets might look like, not necessarily just tables themselves. Carefully choose proper data types for each attribute.

• Make sure the length of varchar fields are large enough for the long college and department names.
• Attributes that represent 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. 

• 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.

   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.

   

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

   

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

   

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

   

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

   

7. The number of students enrolled for Fall 2019

   

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

   

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

   

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. 

    

Submission:

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

Useful Links:

• Go to: Week Index • Course Home • Canvas