Question 1: Let us suppose that XYZ University's Administration Office needs a database to handle student’s transcript data. The following are the requirements:

• The database keeps track of each student's name, enrollment no. (E. No.), communication address and phone no., permanent address and phone no., date of birth, sex, class, department, college, major subjects (a 4-char code).
  
  
• Some applications need to refer individually to the city, state (2-char abbrv.) and zip code (some use 5-digit, some use 9) of the student’s address. Some applications need to refer to student’s first and last names separately. Enrollment no. should be unique number for each student.
  
  
• Each department has a name, a 3-char code, location (main building and room number), office phone, college, and a list of instructors. Many colleges are affiliated to the University. Department names and code are unique, within the university.
  
  
• Each instructor has his/her enrolment no., first name and last name.
  
  
• The course catalog contains the list of courses that XYZ University offers. Each course has a number (e.g. 543), name, description, credit hours and department. Course numbers are unique within a department, but not unique across the university (so taken together, they are unique). Not every course is offered every semester.
  
  
• Each section has a single instructor (first and last names - no team-taught class), semester (1-4) year (2-digit), course number and section number.
  
  
• A grade report contains the student's name, E. No, course number and section, letter grade (A, B,C, D, E). Note: If the above user requirements leave out any important detail, assume that it is handled the way XYZ does things in the real world. Answer the following questions based on the above requirements and specifications:
  
  
• Draw an E-R diagram of the above.
  
  
• List all the accompanying attributes/domains (including constraints).
  
  
• Convert your E-R diagram to a relational schema:
  
  
• Use naming as specified in the E-R diagram.
  
  
• Use the format as shown in the example below, including:
  
  
• Table name in caps
  
  
• List of attributes with primary key underlined
  
  
• Foreign key specified with table/attribute it references (with .. cascade, it appropriate)