W06 Project: University Database
- Purpose: To review database principles you learned in previous weeks.
- Tasks:
- Forward engineer a database.
- Load data into database.
- 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.
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.
-
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.
fname lname Sept Birthdays Preston Larsen September 22, 1996 Julia Madsen September 22, 1998 -
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.
lname fname Years Days Years and Days Smith Katie 21 173 21 - Yrs, 173 - Days Miller Paul 20 323 20 - Yrs, 323 - Days Larsen Preston 20 110 20 - Yrs, 110 - Days Murdock Mandy 20 49 20 - Yrs, 49 - Days Adams Alece 19 233 19 - Yrs, 233 - Days Carlson Bryce 19 49 19 - Yrs, 49 - Days Jones Kelly 18 202 18 - Yrs, 202 - Days Sorensen Susan 18 154 18 - Yrs, 154 - Days Madsen Julia 18 110 18 - Yrs, 110 - Days Merrill Devon 16 171 16 - Yrs, 171 - Days -
Students taught by John Jensen. Sorted by student's last name
fname lname Bryce Carlson Devon Merrill Mandy Murdock -
Instructors Bryce will have in Winter 2018. Sort by the faculty's last name.
fname lname John Jensen Marty Morring Nate Norris -
Students that take Econometrics in Fall 2019. Sort by student last name.
fname lname Kelly Jones Mandy Murdock Katie Smith -
Report showing all of Bryce Carlson's courses for Winter 2018. Sort by the name of the course.
department_code course_num name HUM 376 Classical Heritage ITM 111 Intro to Databases ECON 150 Micro Economics -
The number of enrollments for Fall 2019
term year Enrollment Fall 2019 7 -
The number of courses in each college. Sort by college name.
Colleges Courses College of Business and Communication 2 College of Language and Letters 1 Physical Science and Engineering 1 -
The total number of students each professor can teach in Winter 2018. Sort by that total number of students (teaching capacity).
fname lname TeachingCapacity Marty Morring 30 John Jensen 30 Bill Barney 35 Nate Norris 100 -
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.
lname fname Credits Miller Paul 6 Murdock Mandy 6 Smith Katie 4 Jones Kelly 4
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