ER exercise: University
er-in-class3.png

Above is an ER model for a university. There are some additional assumptions of which you should be aware:

  • Data is maintained for all past and current classes.
  • Need to know student's name and age.
  • Room numbers are unique within a building
  • Need to track room capacity.

Exploration questions

Answer the following questions without looking at the other pages. After you are done answering these questions, you can find the answer on this page.

  1. Should the relationship between the following be represented by a table?
    1. Room and Section?
    2. Prof and Dept (has chair person/chair person of)?
    3. Student and Prof?
    4. position between Prof and Dept?
  2. In what tables is the primary key of the Prof table embedded as a foreign key?
  3. How many foreign keys does advisor have embedded in it? Position? Enroll?
  4. What's in the prereq table?
  5. What's the fewest number of students who can be enrolled in a course?
  6. What's the greatest number of colleges a professor can have a chair in?
  7. What's the great number of professors who can have a chair in a college?
  8. What's the fewest number of professors who can have a position in a college?
  9. What's the fewest number of courses that a department can offer?
  10. What's the fewest number of sections that a department can offer?

Questions to be answered by database queries

Provide an outline of how the following questions would be answered by a database query. To do this you need to indicate 1) which tables are needed to answer the query, 2) on which fields constraints are placed, and 3) which fields would be printed out. A diagram that would help you write the SQL queries is found on this page. Answers to these questions are found on this page.

  1. What are the names of the courses offered by the department whose ID=25?
  2. What are the names of the students who are registered for the course called “E-commerce”?
  3. What are the names of the courses that Lindsey (prof.name = “Lindsey”) is qualified to teach?
  4. What are the names of the courses that professors who have a position in a department in LS&A (college.name = “LS&A”) are qualified to teach?
  5. What are the names of the courses that professors who are chair of a department in LS&A are qualified to teach?
  6. What are the names of the courses that professors who are chair of a department in LS&A and have a position in a department in LS&A are qualified to teach?
  7. What are the names of students advised by Lindsey?
  8. What are the names of students who are enrolled in courses taught by Lindsey ?
  9. What are the names of students who are both advised by Lindsey and enrolled in courses taught by Lindsey?
  10. What are the names of students who are both advised by Lindsey and enrolled in courses that are both taught by Lindsey and offered by departments in LS&A?

The table structures for this problem can be found in this page

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License