# Database: University Example: Exercises: DB Design

Name __________________________________________
These questions refer to the University database.

1. Diagram modification.
   On the University database relational diagram, draw the appropriate crow's foot next to each of the Access connectors. Use the crow's foot that indicated optional or mandatory (a 0 or 1), not the simple 1 or many crow's foot.
2. Committee extension
   The university has decided to use the database to record the participation of instructors in university committees. The university management expects that no instructor will belong to more than 3 committees. Consider whether that number 3 is important. Only instructors may be members and the head. The head is considered to be a member. Instructors are not required to be on any committee. An instructor can be the head of more than one committee. A committee need not have any members or a head (eg, if it has just been formed). Each committee has a name.
   2. Show the necessary table modifications and/or additions to the relational schema diagram to save the committee membership information.
   3. Write a SELECT command to show which committees each instructor is a member of. Show instructor and names, not just IDs. The idea of this request is to use this output to easily look up which committees an instructor is on, so arrange the output appropriately.
   4. Write a SELECT command to show who's a member of the 'Audit Fraud' committee. Show the names in alphabetical order.
   5. Write a SELECT command to show those instructors who are members of more than 3 committees. Show the number of committees the instructor is on and their name. Order the list, starting with the instructor who is on the greatest number of committees over three, and continue down.