1. Briefly explain what this model tells you about the University. Consider 
   all entities and relationships in your answer.

   Soln: Since minimum cardinality is not provided, assume that it is 1 in
   each case.
  
      Each COLLEGE consists-of one or more DEPT but each DEPT is associated 
      with one COLLEGE
      Each DEPT contains one or more PROFESSOR's but each PROFESSOR is 
      associated with one DEPT
      Each DEPT is-administered-by one or more ADMIN-PERSONNEL but each 
      ADMIN-PERSONNEL is associated with one DEPT
      Each PROFESSOR teaches one or more STUDENT's but each STUDENT
      is taught by one PROFESSOR
      Each STUDENT earns one or more GRADE's but each GRADE is earned
      by one STUDENT

2. The University is very small and is housed in a single building. The 
   following attributes (unique identifiers are underlined) have been 
   proposed for the ER model:

         COLLEGE: CollegeName, PnoneNumber, SuiteNumber, URL
         DEPT: DeptName, SuiteNumber
         PROFESSOR: SSN, FirstName, LastName, TenureStatus, Salary
         ADMIN-PERSONNEL: SSN, FirstName, LastName
         STUDENT: SSN, FirstName, LastName, Major
         GRADE: ClassNumber, ClassName, Grade 

   a. Given these details, derive a relational database schema from this 
      model. 

      Soln: Primary key in quotes:
         COLLEGE("CollegeName", PnoneNumber, SuiteNumber, URL)
         DEPT("DeptName", SuiteNumber, CollegeName)
             foreign key CollegeName
         PROFESSOR("SSN", FirstName, LastName, TenureStatus, Salary, DeptName)
             foreign key DeptName
         ADMIN-PERSONNEL("SSN", FirstName, LastName, DeptName)
             foreign key DeptName
         STUDENT("SSN", FirstName, LastName, Major, ProfSSN)
             foreign key ProfSSN (i.e. to PROFESSOR)
         GRADE("ClassNumber", ClassName, Grade, StudentSSN) 
             foreign key StudentSSN (i.e. to STUDENT)

   b. You are asked to modify the ER model to accommodate the multi-valued 
      attribute AcademicQualification (i.e. refers to each earned academic 
      degree, such as BA, MA, PhD) for the entity PROFESSOR. Explain how 
      you would accommodate this new attribute. Illustrate your answer by 
      indicating how the picture of the ER model would be affected.

      Soln: May be accommodated by adding a weak entity AcademicQualification
      with a 1:N relationship (i.e. called 'has') with PROFESSOR as the
      parent.

   c. You are also asked to accommodate the fact that Professors may be 
      classified as Full-Time and Part-Time. You discover that the attribute 
      TenureStatus applies to Full-time Professors only. Also, Full-time 
      Professors are paid a fixed salary but Part-time Professors are paid 
      an hourly rate that may be different for each individual. Explain how 
      you would accommodate this new fact. Illustrate your answer by 
      indicating how the picture of the ER model would be affected. 
 
      Soln: May be accommodated by adding subtypes FULL-TIME, PART-TIME
      to PROFESSOR. These are disjoint subtypes (or generalization
      hierarchy). The attributes TenureStatus and Salary would be 
      removed from PROFESSOR and added to to FULL-TIME and PART-TIME 
      would have HourlyRate as an attribute.